Views expressed here are solely that of my own. Please make sure that you test the code/queries that appear on my blog before applying them to the production environment.

Friday, February 19, 2010

Parallel index rebuild

You can use parallel index rebuild option whenever you have to rebuild an index since, most of the time, parallel rebuild option will be much faster than a noparallel operation.

I will try to demonstrate this situation below.

First create a tablespace and a database user
C:\uural\work_cmd\20081014>sqlplus sys@db01 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 19 15:16:33 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password: 

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SYS@db01> create tablespace tbs01;

Tablespace created.

SYS@db01> create user dbuser01 identified by dbuser01 default tablespace tbs01;

User created.

SYS@db01> grant connect, resource to dbuser01;

Grant succeeded.

SYS@db01> grant alter system to dbuser01;

Grant succeeded.

Connect to the database with the new user
C:\uural\work_cmd\20100219>sqlplus dbuser01/dbuser01@db01

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 19 15:32:56 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

DBUSER01@db01>

Create a test table
DBUSER01@db01> @cre_table01
DBUSER01@db01> create table table01 nologging pctfree 90
2  as
3  select dbms_random.value(1,1000000) id
4  , rpad('*',4000,'*') data
5    from dual
6  connect by level <= 1000000;

Table created.
Create an index on this test table
DBUSER01@db01> select blocks,bytes from user_segments where segment_name = 'TABLE01';

BLOCKS BYTES
---------- ----------
1007616 8254390272

1 row selected.

DBUSER01@db01> create index table01_01 on table01(id) nologging;
Index created.
Make this index unusable
DBUSER01@db01> alter index table01_01 unusable;
Clear the buffer cache of the database
SYS@db01> alter system flush buffer_cache;

System altered.
Set timing on
DBUSER01@db01> show timing
timing OFF
DBUSER01@db01> set timing on
Enable session trace
DBUSER01@db01> alter system set sql_trace=TRUE;

System altered.

Elapsed: 00:00:00.00
Run NOPARALLEL index rebuild
DBUSER01@db01> alter index table01_01 rebuild nologging;

Index altered.

Elapsed: 00:01:01.20
It took 1 minutes 1 seconds Check the trace file
********************************************************************************

alter index table01_01 rebuild nologging


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.03          1          1          0           0
Execute      1     20.11      59.45    1000005    1000174       6897           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2     20.11      59.49    1000006    1000175       6897           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 378
********************************************************************************  
Before trying the PARALLEL option clear the buffer cache again
SYS@db01> alter system flush buffer_cache;

System altered.
Rebuild the index with PARALLEL option this time
DBUSER01@db01> alter index table01_01 unusable;

Index altered.

Elapsed: 00:00:00.07
DBUSER01@db01> alter index table01_01 rebuild parallel nologging;

Index altered.

Elapsed: 00:00:30.62
DBUSER01@db01> 
It took 30 seconds Check the trace file again
********************************************************************************

alter index table01_01 rebuild parallel nologging


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          1          1          0           0
Execute      1      0.06      29.72    1005359    1003636       9010           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.06      29.73    1005360    1003637       9010           0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 378



******************************************************************************** 

As you can see parallel index rebuild is much faster than noparallel index rebuild.
Since, most of the time, parallel operations use the server CPU more efficiently, especially in data warehouse databases
we better use the parallel option in database operations whenever possible.

Regards,
Ural

How to kill a session which is connected to another RAC node in an Oracle 10g RAC database

If you want to kill a session which is connected to another node of an Oracle 10g RAC database, there is no one direct single command provided by Oracle which you can use, this became possible with the version 11g of Oracle RAC database.

For example, assume that you have a 2-node Oracle 10g RAC database and you are currently connected to the first node with SYS user. You want to kill a session that is connected to the second node of RAC database. This is not possible with one single command in 10g. You can use the database package that I developed below which uses database job method to kill a session that is connected on another node of Oracle 10g RAC database. I also tried to explain the situation below.

First connect to the first node of 2-node Oracle 10g RAC database.

C:\Documents and Settings\user01>sqlplus sys@server01_inst1 as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 19 09:59:03 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

Bring some information about this RAC database
SQL> set lines 200
SQL> col host_name format a30
SQL> r
1  select inst_id, instance_number, instance_name, host_name, version, status
2* from gv$instance

INST_ID INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME                      VERSION           STATUS
---------- --------------- ---------------- ------------------------------ ----------------- ------------
1               1 INST1            server01                       10.2.0.4.0        OPEN
2               2 INST2            server02                       10.2.0.4.0        OPEN

I am connected to the fist node

SQL> select instance_number, instance_name, host_name, version, status
2  from v$instance;

INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME                      VERSION           STATUS
--------------- ---------------- ------------------------------ ----------------- ------------
1 INST1            server01                       10.2.0.4.0        OPEN

Create a schema in which I will create my package
SQL> create user dbuser01 identified by dbuser01;

User created.

SQL> grant create session to dbuser01;

Grant succeeded.

SQL> grant select on gv_$session to dbuser01;

Grant succeeded.

SQL> grant alter system to dbuser01;

Grant succeeded.

SQL> grant create job to dbuser01;

Grant succeeded.

Create the package
SQL> CREATE OR REPLACE PACKAGE dbuser01.DBA_OPERATION IS
2
3  PROCEDURE Kill_Session_User(p_username VARCHAR2);
4  PROCEDURE Kill_Session_Job(p_sid     NUMBER,
5                             p_serial  NUMBER,
6                             p_inst_id NUMBER);
7  PROCEDURE Kill_Session(p_sid     NUMBER,
8                         p_serial  NUMBER);
9  END DBA_OPERATION;
10  /

Package created.

SQL>
SQL> CREATE OR REPLACE PACKAGE BODY dbuser01.DBA_OPERATION IS
2
3  PROCEDURE Kill_Session_User(p_username VARCHAR2) IS
4    d_stmt VARCHAR2(4000);
5  BEGIN
6    FOR c_stmt IN (
7      SELECT t1.sid, t1.serial#, t1.inst_id
8        FROM gv$session t1
9       WHERE t1.username = p_username
10     ) LOOP
11      Kill_Session_Job(p_sid     => c_stmt.sid,
12                       p_serial  => c_stmt.serial#,
13                       p_inst_id => c_stmt.inst_id);
14    END LOOP;
15    DBMS_OUTPUT.Put_Line('===== All the database sessions of user "'||p_username||'" killed successfully ! =====');
16
17  END Kill_Session_User;
18
19  PROCEDURE Kill_Session_Job(p_sid     NUMBER,
20                             p_serial  NUMBER,
21                             p_inst_id NUMBER) IS
22    JOB BINARY_INTEGER;
23    d_stmt VARCHAR2(4000);
24  BEGIN
25    d_stmt := 'begin dbuser01.DBA_OPERATION.Kill_Session(p_sid => '||p_sid||', p_serial => '||p_serial||'); end;';
26    SYS.DBMS_JOB.Submit(job => job, what => d_stmt, instance => p_inst_id);
27    COMMIT;
28    DBMS_OUTPUT.Put_Line(d_stmt);
29  END Kill_Session_Job;
30
31  PROCEDURE Kill_Session(p_sid     NUMBER,
32                         p_serial  NUMBER) IS
33    d_stmt VARCHAR2(4000);
34  BEGIN
35    d_stmt := 'ALTER SYSTEM KILL SESSION '''||p_sid||', '||p_serial||''' IMMEDIATE';
36    DBMS_OUTPUT.Put_Line(d_stmt);
37    EXECUTE IMMEDIATE d_stmt;
38  END Kill_Session;
39
40  END DBA_OPERATION;
41  /

Package body created.

SQL>

Create a second user which we will use in kill session test
SQL> create user dbuser02 identified by dbuser02;

User created.

SQL> grant create session to dbuser02;

Grant succeeded.

Connect to the second node of RAC with this user
C:\Documents and Settings\user01>sqlplus dbuser02/dbuser02@server02_inst2

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 19 10:22:58 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> 

Return to the first session on node1 and try to kill the session of dbuser02 which is on node2.
Query the session info of dbuser02
SQL> SELECT t1.sid, t1.serial#, t1.inst_id
2    FROM gv$session t1
3   WHERE t1.username = 'DBUSER02';

SID    SERIAL#    INST_ID
---------- ---------- ----------
260      28071          2

If we are on the same node as the session that we want to kill, we would use command below, but since the session to be killed is on another node you can not use the below command
SQL> alter system kill session '260, 28071';
alter system kill session '260, 28071'
*
ERROR at line 1:
ORA-00030: User session ID does not exist.

If the database version would have been 11g then you can could use the below command, but in 10g not
SQL> alter system kill session '260, 28071, @2';

So in that case we can use the package procedure which uses database job method and kills a specific session or all of the sessions of a specific user, no mather which node they are connected

To kill only one specific session
SQL> set serveroutput on size 1000000
SQL> exec dbuser01.DBA_OPERATION.Kill_Session_Job(p_sid => 260, p_serial => 28071, p_inst_id => 2);
begin dbuser01.DBA_OPERATION.Kill_Session(p_sid => 260, p_serial => 28071); end;

PL/SQL procedure successfully completed.

SQL> SELECT t1.sid, t1.serial#, t1.inst_id
2    FROM gv$session t1
3   WHERE t1.sid = 260
4     AND t1.serial# = 28071
5     AND t1.inst_id = 2;

no rows selected

To kill all of the sessions of a specific user no mather which node they are connected
SQL> set serveroutput on size 1000000
SQL> exec dbuser01.DBA_OPERATION.Kill_Session_User(p_username => 'DBUSER02');
begin dbuser01.DBA_OPERATION.Kill_Session(p_sid => 260, p_serial => 28071); end;
===== All the database sessions of user "DBUSER02" killed successfully ! =====

PL/SQL procedure successfully completed.

SQL> SELECT t1.sid, t1.serial#, t1.inst_id
2    FROM gv$session t1
3   WHERE t1.username = 'DBUSER02';

no rows selected

Regards,
Ural

Thursday, February 11, 2010

Oracle background processes still use and lock the dropped ASM disks and these disks can not be removed and assigned to another server without restart

Oracle background processes still use and lock the dropped ASM disks and these disks can not be removed and assigned to another server without restarting the databases which are locking this ASM disks.

Hi All,

Today, our Unix Admins group wanted to take back a storage LUN (/dev/hdisk19) which had been given temporarly to be used for a project and assigned to the the ASM of one of our Oracle databases.

We use Oracle Database Enterprise Edition and ASM version 10.2.0.4.

As a normal procedure I dropped the ASM disk which is corresponding to this LUN.
First I connected to the ASM instance and checked the ASM disk name of the related ASM device.

SQL> select name, substr(path, 1, 20) from v$asm_disk order by name;

NAME                           SUBSTR(PATH,1,20)
------------------------------ --------------------
DG_DB_ASM_DB01_0000            /dev/ASM_Disk1
DG_DB_ASM_DB01_0001            /dev/ASM_Disk2
DG_DB_ASM_DB01_0002            /dev/ASM_Disk3
DG_DB_ASM_DB01_0003            /dev/ASM_Disk4
DG_DB_ASM_DB01_0004            /dev/ASM_Disk5

NAME                           SUBSTR(PATH,1,20)
------------------------------ --------------------
DG_DB_ASM_DB01_0005            /dev/ASM_Disk6
DG_DB_ASM_DB01_0006            /dev/ASM_Disk13
DG_DB_ASM_DB01_0007            /dev/ASM_Disk14
DG_DB_ASM_DB01_0008            /dev/ASM_Disk15
DG_DB_ASM_DB01_0009            /dev/ASM_Disk17

10 rows selected.    

I used the following command to drop the ASM disk.
ALTER DISKGROUP DG_DB_ASM_DB01 DROP DISK DG_DB_ASM_DB01_0009;

After this command completes, Unix Admin group tried to remove the hdisk (/dev/hdisk19) corresponding to this device and they said that there were some processes in the Unix server (IBM AIX) which were using this hdisk and because of that they can not remove it.

I checked again to find out which processes use this ASM disk corresponding this ASM device.
[osuser01@os01]:/oracle > fuser /dev/ASM_Disk17
/dev/ASM_Disk17:   200796  430276  675882  712952  745630  749608  757766  782390  892942 1220622 1515754 1613864 1765430 1921252 1945674
[osuser01@os01]:/oracle > ps -ef|grep  200796
oracle  200796       1   0 16:52:57      -  0:00 oracle+ASM (LOCAL=NO)
oracle 1146924 1589428   0 17:56:00  pts/2  0:00 grep 200796
[osuser01@os01]:/oracle > ps -ef|grep  430276
oracle  430276       1   0   Oct 06      - 20:01 asm_gmon_+ASM
oracle 1589430 1146926   0 17:56:05  pts/2  0:00 grep 430276
[osuser01@os01]:/oracle > ps -ef|grep  675882
oracle  675882       1   0   Oct 06      - 26:20 ora_cjq0_DB01
oracle 1400942 1065140   0 17:56:09  pts/2  0:00 grep 675882
[osuser01@os01]:/oracle > ps -ef|grep  712952
oracle  712952       1   0   Oct 06      - 10:15 ora_smon_DB01
oracle 1065142 1400944   0 17:56:14  pts/2  0:00 grep 712952
[osuser01@os01]:/oracle > ps -ef|grep  745630
oracle  745630       1   0   Oct 06      - 207:01 ora_ckpt_DB01
oracle 1380554 1400950   0 17:56:19  pts/2  0:00 grep 745630
[osuser01@os01]:/oracle > ps -ef|grep  749608
oracle  749608       1   0   Oct 06      - 58:08 ora_lgwr_DB01
oracle 1065156 1380564   0 17:56:25  pts/2  0:00 grep 749608
[osuser01@os01]:/oracle > ps -ef|grep  757766
oracle  757766       1   0 09:27:29      -  0:11 ora_j000_DB01
oracle 1876066 1589452   0 17:56:31  pts/2  0:00 grep 757766
[osuser01@os01]:/oracle > ps -ef|grep  782390
oracle  782390       1   0   Oct 06      - 3793:04 ora_dbw0_DB01
oracle 1589454 1876068   0 17:56:42  pts/2  0:00 grep 782390
[osuser01@os01]:/oracle > ps -ef|grep  892942
oracle  892942       1   0   Oct 06      -  1:59 ora_rbal_DB01
oracle 1589456 1065172   0 17:56:48  pts/2  0:00 grep 892942
[osuser01@os01]:/oracle > ps -ef|grep  1220622
oracle 1065174 1589460   0 17:56:54  pts/2  0:00 grep 1220622
oracle 1220622       1   0   Oct 06      - 15:45 ora_mmon_DB01
[osuser01@os01]:/oracle > ps -ef|grep  1515754
oracle 1400968 1876080   0 17:57:02  pts/2  0:00 grep 1515754
oracle 1515754       1   0   Oct 06      -  2:55 asm_rbal_+ASM
[osuser01@os01]:/oracle > ps -ef|grep  1613864
oracle 1065184 1876088   0 17:57:13  pts/2  0:00 grep 1613864
oracle 1613864       1   0   Oct 06      -  4:20 asm_lgwr_+ASM
[osuser01@os01]:/oracle > ps -ef|grep  1765430
oracle 1765430       1   0   Oct 06      -  3:19 asm_dbw0_+ASM
oracle 1876090 1065186   0 17:57:18  pts/2  0:00 grep 1765430
[osuser01@os01]:/oracle > ps -ef|grep  1921252
oracle 1065188 1876092   0 17:57:25  pts/2  0:00 grep 1921252
oracle 1921252       1   0 15:34:14      -  0:00 oracle+ASM (LOCAL=NO)
[osuser01@os01]:/oracle > ps -ef|grep  1945674
oracle 1065192 1876098   0 17:57:30  pts/2  0:00 grep 1945674
oracle 1945674       1   0   Oct 06      - 49:47 ora_mmnl_DB01

As we can see, the locking processes belong to the Oracle database and ASM instance background processes.

I also checked the database side whether dropped disk is still there or not.

I connected to the ASM instance and run the following query.

SQL> select name, substr(path, 1, 20) from v$asm_disk order by name;

NAME                           SUBSTR(PATH,1,20)
------------------------------ --------------------
DG_DB_ASM_DB01_0000            /dev/ASM_Disk1
DG_DB_ASM_DB01_0001            /dev/ASM_Disk2
DG_DB_ASM_DB01_0002            /dev/ASM_Disk3
DG_DB_ASM_DB01_0003            /dev/ASM_Disk4
DG_DB_ASM_DB01_0004            /dev/ASM_Disk5

NAME                           SUBSTR(PATH,1,20)
------------------------------ --------------------
DG_DB_ASM_DB01_0005            /dev/ASM_Disk6
DG_DB_ASM_DB01_0006            /dev/ASM_Disk13
DG_DB_ASM_DB01_0007            /dev/ASM_Disk14
DG_DB_ASM_DB01_0008            /dev/ASM_Disk15
/dev/ASM_Disk17

10 rows selected.    

ASM device name still appears there but without any ASM disk name, this looks a bit strange because we successfully dropped this ASM disk and there are still Oracle background processes which does not release this ASM device.

After searching a bit about this problem, I found a Metalink note in Oracle Metalink website explaining that this problem occurs because of an Oracle bug which will be solved in Oracle version 10.2.0.5 (we use Oracle version 10.2.0.4). (Oracle Metalink note : Asm Devices Are Still Held Open After Dismount or Drop [ID 402526.1])

As a quick solution, since this database is not a production database, I restarted ASM and Oracle database instance to get rid of these background processes locking the ASM device and problem was solved.

Regards,
Ural

Saturday, February 6, 2010

How to add an Oracle related script to Unix crontab

Dear All,

After a long period of time not publishing any blog posts, I'm back again to share my Oracle knowledge with you and get your reviews and feedbacks about the published subjects.

I want to share a point with you, which most of you would already have been aware of it.

When you want to prepare an Oracle related script which will connect to an Oracle database and do some work and schedule it in Unix crontab (in my case IBM AIX), you should pay attention to set the environment variables related to database which will be connected to and running on the same server.

These environment settings are normally set in the profile of the Unix user, running the script in Unix, most of the time this Unix user is also the owner of the database running on this server.

In case of scheduling a shell script in crontab, the scheduled script does not have any profile setting automatically and you should prepare a separate script including these Oracle related environment settings or you can directly embed these setting to your database related Unix shell script.

Below are the examples of these kinds of Unix shell scripts.

Regards,
Ural

[user01@server01]:/oracle > crontab -l
00 00 * * * /oracle/scripts/script.sh > /oracle/scripts/script.log 2>&1

[user01@server01]:/oracle > more /oracle/scripts/script.sh
. /oracle/scripts/set_env.sh
/oracle/scripts/script_02.sh

[user01@server01]:/oracle > more /oracle/scripts/set_env.sh
export ORACLE_BASE=/oracle
export ORACLE_HOME=$ORACLE_BASE/orahome1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32
export LIBPATH=$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH
export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P9
cd /oracle/scripts

[user01@server01]:/oracle > more /oracle/scripts/script_02.sh
d_date=`date +"%Y%m%d%H%M"`
expdp parfile=script_02.par dumpfile=script_02_${d_date}.dmp logfile=script_02_${d_date}.log