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.

Thursday, March 22, 2012

How to replace Oracle 10g RAC OCR, Voting and ASM spfile disks

Below is the procedure I followed and the errors when replacing Oracle 10g RAC OCR, Voting and ASM spfile disks with the new disks from the new storage system. The database version is Oracle 10gR2 (10.2.0.4) RAC with two nodes and it is running on IBM servers with IBM AIX v6.1 OS.

I followed the procedure explained in the below Metalink Oracle Support (MOS) note.

OCR / Vote disk Maintenance Operations: (ADD/REMOVE/REPLACE/MOVE) [ID 428681.1]

First thing you should do is analyze the current configuration and find what is the current disk sizes and the permissions of their defined devices.

You need to use root Unix user for the below operations.

Check ASM spfile device and hdisk, use the major and minor numbers of the device to find the corresponding hdisk which is "23, 3" below.
[root@srvdb01]:/home/root \> ls -l /dev/asmspf*
crw-rw----    1 oracle   dba          23,  3 Jul 28 2011  /dev/asmspf_disk
[root@srvdb01]:/home/root \> ls -l /dev/hdisk*|grep "23,  3"
brw-------    1 root     system       23,  3 Jul 28 2011  /dev/hdisk1

Check the corresponding hdisk size which is 100 MB below
[root@srvdb01]:/home/root \> bootinfo -s hdisk1
102


Check OCR disks, they are 300 MB each
[root@srvdb01]:/home/root \> ls -l /dev/ocr*
crw-r-----    1 root     dba          23,  4 Jul 28 2011  /dev/ocr_disk1
crw-r-----    1 root     dba          23,  5 Jul 28 2011  /dev/ocr_disk2
[root@srvdb01]:/home/root \> ls -l /dev/hdisk*|grep "23,  4"
brw-------    1 root     system       23,  4 Jul 28 2011  /dev/hdisk2
[root@srvdb01]:/home/root \> ls -l /dev/hdisk*|grep "23,  5"
brw-------    1 root     system       23,  5 Jul 28 2011  /dev/hdisk3
[root@srvdb01]:/home/root \> bootinfo -s hdisk2
307
[root@srvdb01]:/home/root \> bootinfo -s hdisk3
307


Check Voting disks, they are 300 MB each
[root@srvdb01]:/home/root \> ls -l /dev/vot*
crw-r--r--    1 oracle   dba          23,  6 Mar 20 10:55 /dev/voting_disk1
crw-r--r--    1 oracle   dba          23,  7 Mar 20 10:55 /dev/voting_disk2
crw-r--r--    1 oracle   dba          23,  8 Mar 20 10:55 /dev/voting_disk3
[root@srvdb01]:/home/root \> ls -l /dev/hdisk*|grep "23,  6"
brw-------    1 root     system       23,  6 Jul 28 2011  /dev/hdisk4
[root@srvdb01]:/home/root \> ls -l /dev/hdisk*|grep "23,  7"
brw-------    1 root     system       23,  7 Jul 28 2011  /dev/hdisk5
[root@srvdb01]:/home/root \> ls -l /dev/hdisk*|grep "23,  8"
brw-------    1 root     system       23,  8 Jul 28 2011  /dev/hdisk6
[root@srvdb01]:/home/root \> bootinfo -s hdisk4
307
[root@srvdb01]:/home/root \> bootinfo -s hdisk5
307
[root@srvdb01]:/home/root \> bootinfo -s hdisk6
307
[root@srvdb01]:/home/root \> 


Do the same check on the second node as root user
[root@srvdb02]:/home/root \> ls -l /dev/asmspf*
crw-rw----    1 oracle   dba          23,  3 Jun 29 2011  /dev/asmspf_disk
[root@srvdb02]:/home/root \> ls -l /dev/hdisk*|grep "23,  3"
brw-------    1 root     system       23,  3 Jun 30 2011  /dev/hdisk1
[root@srvdb02]:/home/root \> bootinfo -s hdisk1
102 

[root@srvdb02]:/home/root \> ls -l /dev/ocr*
crw-r-----    1 root     dba          23,  6 Mar 20 11:00 /dev/ocr_disk1
crw-r-----    1 root     dba          23,  7 Mar 20 11:00 /dev/ocr_disk2
[root@srvdb02]:/home/root \> ls -l /dev/hdisk*|grep "23,  6"
brw-------    1 root     system       23,  6 Jul 04 2011  /dev/hdisk2
[root@srvdb02]:/home/root \> ls -l /dev/hdisk*|grep "23,  7"
brw-------    1 root     system       23,  7 Jul 04 2011  /dev/hdisk3
[root@srvdb02]:/home/root \> bootinfo -s hdisk2
307
[root@srvdb02]:/home/root \> bootinfo -s hdisk3
307

[root@srvdb02]:/home/root \> ls -l /dev/vot*
crw-r--r--    1 oracle   dba          23,  8 Mar 20 11:01 /dev/voting_disk1
crw-r--r--    1 oracle   dba          23,  9 Mar 20 11:01 /dev/voting_disk2
crw-r--r--    1 oracle   dba          23, 10 Mar 20 11:01 /dev/voting_disk3
[root@srvdb02]:/home/root \> ls -l /dev/hdisk*|grep "23,  8"
brw-------    1 root     system       23,  8 Jul 04 2011  /dev/hdisk4
[root@srvdb02]:/home/root \> ls -l /dev/hdisk*|grep "23,  9"
brw-------    1 root     system       23,  9 Jul 04 2011  /dev/hdisk5
[root@srvdb02]:/home/root \> ls -l /dev/hdisk*|grep "23, 10"
brw-------    1 root     system       23, 10 Jul 04 2011  /dev/hdisk6
[root@srvdb02]:/home/root \> bootinfo -s hdisk4
307
[root@srvdb02]:/home/root \> bootinfo -s hdisk5
307
[root@srvdb02]:/home/root \> bootinfo -s hdisk6
307
[root@srvdb02]:/home/root \> 

New disks to be assigned as OCR, Voting and ASM spfile are below
brw------- 1 root system 21, 6 Mar 20 10:34 hdisk43 spfile
brw------- 1 root system 21, 2 Mar 20 10:34 hdisk44 ocr1
brw------- 1 root system 21, 1 Mar 20 10:34 hdisk45 ocr2
brw------- 1 root system 21, 3 Mar 20 10:34 hdisk46 vote1
brw------- 1 root system 21, 5 Mar 20 10:34 hdisk47 vote2
brw------- 1 root system 21, 4 Mar 20 10:34 hdisk48 vote3


Now start to configure the new hdisks

First check the sizes of the new disks by running the following command on each RAC node, as you can see from the below output we assigned 1 GB disks for each component.

for i in 43 44 45 46 47 48
do
bootinfo -s hdisk$i
done

[root@srvdb01]:/home/root \> for i in 43 44 45 46 47 48  
> do
> bootinfo -s hdisk$i
> done 
1024
1024
1024
1024
1024
1024
[root@srvdb01]:/home/root \> 

[root@srvdb02]:/home/root \> for i in 43 44 45 46 47 48  
> do
> bootinfo -s hdisk$i
> done
1024
1024
1024
1024
1024
1024
[root@srvdb02]:/home/root \> 

Now use the following command to set the "reserve_policy" of the new disks to the value "no_reserve", otherwise these disks can not be used in a RAC configuration. Also use the following second command to make sure that this operation is successful.
for i in 43 44 45 46 47 48
do
chdev -l hdisk$i -a reserve_policy=no_reserve
done

for i in 43 44 45 46 47 48
do
lsattr -El hdisk$i | grep reserve
done

[root@srvdb01]:/home/root \> for i in 43 44 45 46 47 48 
> do
> chdev -l hdisk$i -a reserve_policy=no_reserve
> done
hdisk43 changed
hdisk44 changed
hdisk45 changed
hdisk46 changed
hdisk47 changed
hdisk48 changed


As we can see they are set accordingly.
[root@srvdb01]:/home/root \> for i in 43 44 45 46 47 48  
> do
> lsattr -El hdisk$i | grep reserve
> done
reserve_policy  no_reserve                                          Reserve Policy                   True
reserve_policy  no_reserve                                          Reserve Policy                   True
reserve_policy  no_reserve                                          Reserve Policy                   True
reserve_policy  no_reserve                                          Reserve Policy                   True
reserve_policy  no_reserve                                          Reserve Policy                   True
reserve_policy  no_reserve                                          Reserve Policy                   True
[root@srvdb01]:/home/root \> 

Do the same thing in the second node
[root@srvdb02]:/home/root \> for i in 43 44 45 46 47 48 
> do
> chdev -l hdisk$i -a reserve_policy=no_reserve
> done
hdisk43 changed
hdisk44 changed
hdisk45 changed
hdisk46 changed
hdisk47 changed
hdisk48 changed
[root@srvdb02]:/home/root \> for i in 43 44 45 46 47 48  
> do
> lsattr -El hdisk$i | grep reserve
> done
reserve_policy  no_reserve                                          Reserve Policy                   True
reserve_policy  no_reserve                                          Reserve Policy                   True
reserve_policy  no_reserve                                          Reserve Policy                   True
reserve_policy  no_reserve                                          Reserve Policy                   True
reserve_policy  no_reserve                                          Reserve Policy                   True
reserve_policy  no_reserve                                          Reserve Policy                   True
[root@srvdb02]:/home/root \> 

To find the hdisk major and minor numbers, we use the following command on each node, since the major and minor numbers of the new assigned hdisk can be different on different RAC nodes.

for i in 43 44 45 46 47 48
do
ls -la /dev/hdisk$i
done

[root@srvdb01]:/home/root \> for i in 43 44 45 46 47 48 
> do
> ls -la /dev/hdisk$i
> done

brw-------    1 root     system       21,  1 Mar 20 10:34 /dev/hdisk43
brw-------    1 root     system       21,  2 Mar 20 10:34 /dev/hdisk44
brw-------    1 root     system       21,  4 Mar 20 10:34 /dev/hdisk45
brw-------    1 root     system       21,  5 Mar 20 10:34 /dev/hdisk46
brw-------    1 root     system       21,  3 Mar 20 10:34 /dev/hdisk47
brw-------    1 root     system       21,  6 Mar 20 10:34 /dev/hdisk48
[root@srvdb01]:/home/root \> 

[root@srvdb02]:/home/root \> for i in 43 44 45 46 47 48 
> do
> ls -la /dev/hdisk$i
> done

brw-------    1 root     system       21,  6 Mar 20 10:34 /dev/hdisk43
brw-------    1 root     system       21,  2 Mar 20 10:34 /dev/hdisk44
brw-------    1 root     system       21,  1 Mar 20 10:34 /dev/hdisk45
brw-------    1 root     system       21,  3 Mar 20 10:34 /dev/hdisk46
brw-------    1 root     system       21,  5 Mar 20 10:34 /dev/hdisk47
brw-------    1 root     system       21,  4 Mar 20 10:34 /dev/hdisk48
[root@srvdb02]:/home/root \> 

Start to create the virtual devices to be used that corresponds to the actual hdisk as below. As you can see above the major and minor numbers of the same hdisks can be different on each RAC node.

We prepare the following commands for each node

For the first node
mknod /dev/asmspf_disk_01 c 21 1

mknod /dev/ocr_disk_01 c 21 2
mknod /dev/ocr_disk_02 c 21 4

mknod /dev/voting_disk_01 c 21 5
mknod /dev/voting_disk_02 c 21 3
mknod /dev/voting_disk_03 c 21 6


For the second node
mknod /dev/asmspf_disk_01 c 21 6

mknod /dev/ocr_disk_01 c 21 2
mknod /dev/ocr_disk_02 c 21 1

mknod /dev/voting_disk_01 c 21 3
mknod /dev/voting_disk_02 c 21 5
mknod /dev/voting_disk_03 c 21 4

Run those commands on each corresponding RAC node
First node
[root@srvdb01]:/home/root \> mknod /dev/asmspf_disk_01 c 21 1
[root@srvdb01]:/home/root \> mknod /dev/ocr_disk_01 c 21 2
[root@srvdb01]:/home/root \> mknod /dev/ocr_disk_02 c 21 4
[root@srvdb01]:/home/root \> mknod /dev/voting_disk_01 c 21 5
[root@srvdb01]:/home/root \> mknod /dev/voting_disk_02 c 21 3
[root@srvdb01]:/home/root \> mknod /dev/voting_disk_03 c 21 6


Second node
[root@srvdb02]:/home/root \> mknod /dev/asmspf_disk_01 c 21 6
[root@srvdb02]:/home/root \> mknod /dev/ocr_disk_01 c 21 2
[root@srvdb02]:/home/root \> mknod /dev/ocr_disk_02 c 21 1
[root@srvdb02]:/home/root \> mknod /dev/voting_disk_01 c 21 3
[root@srvdb02]:/home/root \> mknod /dev/voting_disk_02 c 21 5
[root@srvdb02]:/home/root \> mknod /dev/voting_disk_03 c 21 4


Now check the new situation, the devices having major number 21 are the new disks configured recently

First node
[root@srvdb01]:/home/root \> ls -l /dev/asmspf*
crw-rw----    1 oracle   dba          23,  3 Jul 28 2011  /dev/asmspf_disk
crw-------    1 root     system       21,  1 Mar 20 11:26 /dev/asmspf_disk_01
[root@srvdb01]:/home/root \> ls -l /dev/ocr*
crw-r-----    1 root     dba          23,  4 Jul 28 2011  /dev/ocr_disk1
crw-r-----    1 root     dba          23,  5 Jul 28 2011  /dev/ocr_disk2
crw-------    1 root     system       21,  2 Mar 20 11:26 /dev/ocr_disk_01
crw-------    1 root     system       21,  4 Mar 20 11:26 /dev/ocr_disk_02
[root@srvdb01]:/home/root \> ls -l /dev/vot*
crw-r--r--    1 oracle   dba          23,  6 Mar 20 11:27 /dev/voting_disk1
crw-r--r--    1 oracle   dba          23,  7 Mar 20 11:27 /dev/voting_disk2
crw-r--r--    1 oracle   dba          23,  8 Mar 20 11:27 /dev/voting_disk3
crw-------    1 root     system       21,  5 Mar 20 11:26 /dev/voting_disk_01
crw-------    1 root     system       21,  3 Mar 20 11:26 /dev/voting_disk_02
crw-------    1 root     system       21,  6 Mar 20 11:26 /dev/voting_disk_03
[root@srvdb01]:/home/root \>

Second node
[root@srvdb02]:/home/root \> ls -l /dev/asmspf*
crw-rw----    1 oracle   dba          23,  3 Jun 29 2011  /dev/asmspf_disk
crw-------    1 root     system       21,  6 Mar 20 11:26 /dev/asmspf_disk_01
[root@srvdb02]:/home/root \> ls -l /dev/ocr*
crw-r-----    1 root     dba          23,  6 Mar 20 11:27 /dev/ocr_disk1
crw-r-----    1 root     dba          23,  7 Mar 20 11:27 /dev/ocr_disk2
crw-------    1 root     system       21,  2 Mar 20 11:26 /dev/ocr_disk_01
crw-------    1 root     system       21,  1 Mar 20 11:26 /dev/ocr_disk_02
[root@srvdb02]:/home/root \> ls -l /dev/vot*
crw-r--r--    1 oracle   dba          23,  8 Mar 20 11:27 /dev/voting_disk1
crw-r--r--    1 oracle   dba          23,  9 Mar 20 11:27 /dev/voting_disk2
crw-r--r--    1 oracle   dba          23, 10 Mar 20 11:27 /dev/voting_disk3
crw-------    1 root     system       21,  3 Mar 20 11:26 /dev/voting_disk_01
crw-------    1 root     system       21,  5 Mar 20 11:26 /dev/voting_disk_02
crw-------    1 root     system       21,  4 Mar 20 11:26 /dev/voting_disk_03
[root@srvdb02]:/home/root \> 

Pay attention in this step : File ownership and device permissions should be the same as the old disks !!!!!!
Use the following commands on each RAC node

for i in 01 02
do
chown root:dba /dev/ocr_disk_$i
chmod 640 /dev/ocr_disk_$i
done

for i in 01 02 03
do
chown oracle:dba /dev/voting_disk_$i
chmod 644 /dev/voting_disk_$i
done

chown oracle:dba /dev/asmspf_disk_01
chmod 660 /dev/asmspf_disk_01

First node
[root@srvdb01]:/home/root \> for i in 01 02 
> do
> chown root:dba /dev/ocr_disk_$i
> chmod 640 /dev/ocr_disk_$i
> done
[root@srvdb01]:/home/root \> for i in 01 02 03  
> do
> chown oracle:dba /dev/voting_disk_$i
> chmod 644 /dev/voting_disk_$i
> done
[root@srvdb01]:/home/root \> chown oracle:dba /dev/asmspf_disk_01
[root@srvdb01]:/home/root \> chmod 660 /dev/asmspf_disk_01

Second node
[root@srvdb02]:/home/root \> for i in 01 02 
> do
> chown root:dba /dev/ocr_disk_$i
> chmod 640 /dev/ocr_disk_$i
> done
[root@srvdb02]:/home/root \> for i in 01 02 03  
> do
> chown oracle:dba /dev/voting_disk_$i
> chmod 644 /dev/voting_disk_$i
> done
[root@srvdb02]:/home/root \> chown oracle:dba /dev/asmspf_disk_01
[root@srvdb02]:/home/root \> chmod 660 /dev/asmspf_disk_01


Now check the file ownerships and the permissions of the new disks, they are the ones with major number 21, and the ownership and permissions should be the same as the old disks having the major number 23 below.

First node
[root@srvdb01]:/home/root \> ls -l /dev/asmspf*
crw-rw----    1 oracle   dba          23,  3 Jul 28 2011  /dev/asmspf_disk
crw-rw----    1 oracle   dba          21,  1 Mar 20 11:26 /dev/asmspf_disk_01
[root@srvdb01]:/home/root \> ls -l /dev/ocr*
crw-r-----    1 root     dba          23,  4 Jul 28 2011  /dev/ocr_disk1
crw-r-----    1 root     dba          23,  5 Jul 28 2011  /dev/ocr_disk2
crw-r-----    1 root     dba          21,  2 Mar 20 11:26 /dev/ocr_disk_01
crw-r-----    1 root     dba          21,  4 Mar 20 11:26 /dev/ocr_disk_02
[root@srvdb01]:/home/root \> ls -l /dev/vot*
crw-r--r--    1 oracle   dba          23,  6 Mar 20 11:37 /dev/voting_disk1
crw-r--r--    1 oracle   dba          23,  7 Mar 20 11:37 /dev/voting_disk2
crw-r--r--    1 oracle   dba          23,  8 Mar 20 11:37 /dev/voting_disk3
crw-r--r--    1 oracle   dba          21,  5 Mar 20 11:26 /dev/voting_disk_01
crw-r--r--    1 oracle   dba          21,  3 Mar 20 11:26 /dev/voting_disk_02
crw-r--r--    1 oracle   dba          21,  6 Mar 20 11:26 /dev/voting_disk_03
[root@srvdb01]:/home/root \> 

Second node
[root@srvdb02]:/home/root \> ls -l /dev/asmspf*
crw-rw----    1 oracle   dba          23,  3 Jun 29 2011  /dev/asmspf_disk
crw-rw----    1 oracle   dba          21,  6 Mar 20 11:26 /dev/asmspf_disk_01
[root@srvdb02]:/home/root \> ls -l /dev/ocr*
crw-r-----    1 root     dba          23,  6 Mar 20 11:37 /dev/ocr_disk1
crw-r-----    1 root     dba          23,  7 Mar 20 11:37 /dev/ocr_disk2
crw-r-----    1 root     dba          21,  2 Mar 20 11:26 /dev/ocr_disk_01
crw-r-----    1 root     dba          21,  1 Mar 20 11:26 /dev/ocr_disk_02
[root@srvdb02]:/home/root \> ls -l /dev/vot*
crw-r--r--    1 oracle   dba          23,  8 Mar 20 11:38 /dev/voting_disk1
crw-r--r--    1 oracle   dba          23,  9 Mar 20 11:38 /dev/voting_disk2
crw-r--r--    1 oracle   dba          23, 10 Mar 20 11:38 /dev/voting_disk3
crw-r--r--    1 oracle   dba          21,  3 Mar 20 11:26 /dev/voting_disk_01
crw-r--r--    1 oracle   dba          21,  5 Mar 20 11:26 /dev/voting_disk_02
crw-r--r--    1 oracle   dba          21,  4 Mar 20 11:26 /dev/voting_disk_03
[root@srvdb02]:/home/root \> 

Now you need to use the following command to set the "pv" parameter of each new hdisk to the value "clear" to sign them as new untouched devices in the system.

for i in 43 44 45 46 47 48
do
chdev -l hdisk$i -a pv=clear
done


First node
[root@srvdb01]:/home/root \> for i in 43 44 45 46 47 48
> do
> chdev -l hdisk$i -a pv=clear
> done

hdisk43 changed
hdisk44 changed
hdisk45 changed
hdisk46 changed
hdisk47 changed
hdisk48 changed
[root@srvdb01]:/home/root \> 

Second node
[root@srvdb02]:/home/root \> for i in 43 44 45 46 47 48
> do
> chdev -l hdisk$i -a pv=clear
> done

hdisk43 changed
hdisk44 changed
hdisk45 changed
hdisk46 changed
hdisk47 changed
hdisk48 changed
[root@srvdb02]:/home/root \> 

In this step you need to format the new virtual devices by running the following commands on each RAC node. You need to wait until the "Done" message displayed for each command that indicates that formatting is finished. Run those commands first in the first node wait until they complete ("Done" message) and then run them in the second node and wait for them to complete, this is ver important.

for i in 01 02
do
dd if=/dev/zero of=/dev/ocr_disk_$i bs=8192 count=25000 &
done

for i in 01 02 03
do
dd if=/dev/zero of=/dev/voting_disk_$i bs=8192 count=25000 &
done

dd if=/dev/zero of=/dev/asmspf_disk_01 bs=8192 count=25000 &

First node
[root@srvdb01]:/home/root \> for i in 01 02 
> 
> do
> dd if=/dev/zero of=/dev/ocr_disk_$i bs=8192 count=25000 &
> done
[1] 852170
[2] 848092
[root@srvdb01]:/home/root \> for i in 01 02 03 
> 
> do
> dd if=/dev/zero of=/dev/voting_disk_$i bs=8192 count=25000 &
> done
[3] 1274024
[4] 532694
[5] 1503258
[root@srvdb01]:/home/root \> 25000+0 records in.
25000+0 records out.
25000+0 records in.
25000+0 records out.
dd if=/dev/zero of=/dev/asmspf_disk_01 bs=8192 count=25000 &
[6] 852178
[1]   Done                    dd if=/dev/zero of=/dev/ocr_disk_$i bs=8192 count=25000
[2]   Done                    dd if=/dev/zero of=/dev/ocr_disk_$i bs=8192 count=25000
[root@srvdb01]:/home/root \> 
[root@srvdb01]:/home/root \> 25000+0 records in.
25000+0 records out.
25000+0 records in.
25000+0 records out.
25000+0 records in.
25000+0 records out.
25000+0 records in.
25000+0 records out.

[3]   Done                    dd if=/dev/zero of=/dev/voting_disk_$i bs=8192 count=25000
[4]   Done                    dd if=/dev/zero of=/dev/voting_disk_$i bs=8192 count=25000
[5]-  Done                    dd if=/dev/zero of=/dev/voting_disk_$i bs=8192 count=25000
[6]+  Done                    dd if=/dev/zero of=/dev/asmspf_disk_01 bs=8192 count=25000
[root@srvdb01]:/home/root \> 

Second node
[root@srvdb02]:/home/root \> for i in 01 02 
> 
> do
> dd if=/dev/zero of=/dev/ocr_disk_$i bs=8192 count=25000 &
> done
[1] 1040494
[2] 1507446
[root@srvdb02]:/home/root \> 
[root@srvdb02]:/home/root \> for i in 01 02 03 
> 
> do
> dd if=/dev/zero of=/dev/voting_disk_$i bs=8192 count=25000 &
> done
[3] 823530
[4] 975062
[5] 520304
[root@srvdb02]:/home/root \> 25000+0 records in.
25000+0 records out.
25000+0 records in.
25000+0 records out.
dd if=/dev/zero of=/dev/asmspf_disk_01 bs=8192 count=25000 &
[6] 581850
[1]   Done                    dd if=/dev/zero of=/dev/ocr_disk_$i bs=8192 count=25000
[2]   Done                    dd if=/dev/zero of=/dev/ocr_disk_$i bs=8192 count=25000
[root@srvdb02]:/home/root \> 
[root@srvdb02]:/home/root \> 
[root@srvdb02]:/home/root \> 25000+0 records in.
25000+0 records out.
25000+0 records in.
25000+0 records out.
25000+0 records in.
25000+0 records out.

[3]   Done                    dd if=/dev/zero of=/dev/voting_disk_$i bs=8192 count=25000
[4]   Done                    dd if=/dev/zero of=/dev/voting_disk_$i bs=8192 count=25000
[5]-  Done                    dd if=/dev/zero of=/dev/voting_disk_$i bs=8192 count=25000
[root@srvdb02]:/home/root \> 25000+0 records in.
25000+0 records out.

[6]+  Done                    dd if=/dev/zero of=/dev/asmspf_disk_01 bs=8192 count=25000
[root@srvdb02]:/home/root \> 

Now that the configuration of the new disks is completed.

---------------------------
1-) REPLACE OCR DISKS

We can now continue with the replacing the old OCR, Voting and ASM spfile disks with the new ones.

According to the MOS note id 428681.1, in Oracle 10g RAC, OCR disks can be replaced while the CRS is open, so we tried to do it without closing the CRS.

First we need to check if there is any OCR backup, normally there is a scheduled OCR backup, backing up the OCR disk automatically, so most probably you will see an output like below.

Check the OCR backup, there are some backups taken recently, so there is no problem if something goes wrong we can use the most recent backup before the operation to recover the OCR.
[root@srvdb01]:/home/root \> ocrconfig -showbackup

srvdb02     2012/03/20 08:57:32     /oracle/crshome1/cdata/crs

srvdb02     2012/03/20 04:57:32     /oracle/crshome1/cdata/crs

srvdb02     2012/03/20 00:57:31     /oracle/crshome1/cdata/crs

srvdb02     2012/03/18 20:57:30     /oracle/crshome1/cdata/crs

srvdb02     2012/03/07 16:57:14     /oracle/crshome1/cdata/crs
[root@srvdb02]:/home/root \> 

Check the current OCR info, we have two OCR disks configured already, first one is primary and the second one is just the OCR mirror.
[root@srvdb01]:/home/root \> ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          2
         Total space (kbytes)     :     314344
         Used space (kbytes)      :       3876
         Available space (kbytes) :     310468
         ID                       : 1673980957
         Device/File Name         : /dev/ocr_disk1
                                    Device/File integrity check succeeded
         Device/File Name         : /dev/ocr_disk2
                                    Device/File integrity check succeeded

         Cluster registry integrity check succeeded

[root@srvdb01]:/home/root \> 

Now we try to replace the primary OCR disk, but we got "PROT-16" error, this can be because of bug I do not know the reason but I was unable to do it.
Run these commands only on one node.
[root@srvdb01]:/home/root \> ocrconfig -replace ocr /dev/ocr_disk_01
PROT-16: Internal Error


Then I tried to replace the ocrmirror but it gave another error
[root@srvdb01]:/home/root \> ocrconfig -replace ocrmirror /dev/ocr_disk_01
PROT-21: Invalid parameter


When I checked the log file it gives the following error.
[root@srvdb01]:/oracle/crshome1/log/srvdb01/client \> vi ocrconfig_2027594.log
"ocrconfig_2027594.log" 5 lines, 442 characters 
Oracle Database 10g CRS Release 10.2.0.4.0 Production Copyright 1996, 2008 Oracle.  All rights reserved.
2012-03-20 12:10:20.964: [ OCRCONF][1]ocrconfig starts...
2012-03-20 12:10:21.019: [  OCRCLI][1]proac_replace_dev:[/dev/ocr_disk_01]: Failed. Retval [8]
2012-03-20 12:10:21.019: [ OCRCONF][1]The input OCR device either is identical to the other device or cannot be opened
2012-03-20 12:10:21.019: [ OCRCONF][1]Exiting [status=failed]...


Not to loose any time I left this error aside to search for a solution later and I decided to go on with the "to replace the OCR disk offline" procedure, since at least I needed to close the CRS stack in Oracle 10g RAC for replacing the voting this it is not so much important to replace the OCR disks while the CRS is open or not.

Just continue with the Offline replacement procedure.
First we need to stop the CRS on all nodes for this operation.

First node
[root@srvdb01]:/home/root \> crsctl stop crs
Stopping resources. This could take several minutes.
Successfully stopped CRS resources.
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.


Check the CRS if it's closed successfuly
[root@srvdb01]:/home/root \> crsctl check crs
Failure 1 contacting CSS daemon
Cannot communicate with CRS
Cannot communicate with EVM 


Second node
[root@srvdb02]:/home/root \> crsctl stop crs
Stopping resources. This could take several minutes.
Successfully stopped CRS resources.
Stopping CSSD.
Shutting down CSS daemon.
Shutdown request successfully issued.

[root@srvdb02]:/home/root \> crsctl check crs
Failure 1 contacting CSS daemon
Cannot communicate with CRS
Cannot communicate with EVM 


Run the following commands on all the RAC nodes
[root@srvdb01]:/home/root \> ocrconfig -repair ocr /dev/ocr_disk_01
[root@srvdb01]:/home/root \> ocrconfig -repair ocrmirror /dev/ocr_disk_02

[root@srvdb02]:/home/root \> ocrconfig -repair ocr /dev/ocr_disk_01
[root@srvdb02]:/home/root \> ocrconfig -repair ocrmirror /dev/ocr_disk_02

Run this command only on one node
[root@srvdb01]:/home/root \> ocrconfig -overwrite


Check the OCR for the latest state, as we can see the nold disks are replaced by the new ones
[root@srvdb01]:/home/root \> ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          2
         Total space (kbytes)     :     314344
         Used space (kbytes)      :       3884
         Available space (kbytes) :     310460
         ID                       : 1673980957
         Device/File Name         : /dev/ocr_disk_01
                                    Device/File integrity check succeeded
         Device/File Name         : /dev/ocr_disk_02
                                    Device/File needs to be synchronized with the other device 

         Cluster registry integrity check succeeded

[root@srvdb01]:/home/root \> 

[root@srvdb02]:/home/root \> ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          2
         Total space (kbytes)     :     314344
         Used space (kbytes)      :       3884
         Available space (kbytes) :     310460
         ID                       : 1673980957
         Device/File Name         : /dev/ocr_disk_01
                                    Device/File integrity check succeeded
         Device/File Name         : /dev/ocr_disk_02
                                    Device/File needs to be synchronized with the other device

         Cluster registry integrity check succeeded

[root@srvdb02]:/home/root \>

Now we can remove the virtual devices of old OCR disks on each node.
[root@srvdb01]:/home/root \> rm /dev/ocr_disk1
[root@srvdb01]:/home/root \> rm /dev/ocr_disk2

[root@srvdb02]:/home/root \> rm /dev/ocr_disk1
[root@srvdb02]:/home/root \> rm /dev/ocr_disk2

---------------------------
2-) REPLACE VOTING DISKS

Now we can continue replacing the old voting disks with the new ones.

Check current voting disk info
[root@srvdb01]:/home/root \> crsctl query css votedisk
 0.     0    /dev/voting_disk1
 1.     0    /dev/voting_disk2
 2.     0    /dev/voting_disk3


located 3 votedisk(s).


Now add the new voting disks, do this operation only on first node
[root@srvdb01]:/home/root \> crsctl add css votedisk /dev/voting_disk_01 -force
Now formatting voting disk: /dev/voting_disk_01
successful addition of votedisk /dev/voting_disk_01.
[root@srvdb01]:/home/root \> crsctl add css votedisk /dev/voting_disk_02 -force
Now formatting voting disk: /dev/voting_disk_02
successful addition of votedisk /dev/voting_disk_02.
[root@srvdb01]:/home/root \> crsctl add css votedisk /dev/voting_disk_03 -force
Now formatting voting disk: /dev/voting_disk_03
successful addition of votedisk /dev/voting_disk_03.


Check the current voting disk info
[root@srvdb01]:/home/root \> crsctl query css votedisk
 0.     0    /dev/voting_disk1
 1.     0    /dev/voting_disk2
 2.     0    /dev/voting_disk3
 3.     0    /dev/voting_disk_01
 4.     0    /dev/voting_disk_02
 5.     0    /dev/voting_disk_03

located 6 votedisk(s).


Delete the old voting disks.
[root@srvdb01]:/home/root \> crsctl delete css votedisk /dev/voting_disk1 -force 
successful deletion of votedisk /dev/voting_disk1.
[root@srvdb01]:/home/root \> crsctl delete css votedisk /dev/voting_disk2 -force 
successful deletion of votedisk /dev/voting_disk2.
[root@srvdb01]:/home/root \> crsctl delete css votedisk /dev/voting_disk3 -force
successful deletion of votedisk /dev/voting_disk3.


Check the current voting disk info
[root@srvdb01]:/home/root \> crsctl query css votedisk
 0.     0    /dev/voting_disk_01
 1.     0    /dev/voting_disk_02
 2.     0    /dev/voting_disk_03

located 3 votedisk(s).

Now we successfully replaced the old voting disks with the new ones.

Now we can remove the virtual devices of old OCR disks on each node.
[root@srvdb01]:/home/root \> rm /dev/voting_disk1
[root@srvdb01]:/home/root \> rm /dev/voting_disk2
[root@srvdb01]:/home/root \> rm /dev/voting_disk3

[root@srvdb02]:/home/root \> rm /dev/voting_disk1
[root@srvdb02]:/home/root \> rm /dev/voting_disk2
[root@srvdb02]:/home/root \> rm /dev/voting_disk3


---------------------------
3-) REPLACE ASM SPFILE DISK

We can continue with replacing the ASM spfile disk now.

Following operations should be performed on each RAC node.

Switch to the the ASM profile.

[oracle@srvdb01]:/oracle > . .profile_asm
[oracle@srvdb01]:/oracle > env|grep ORA
ORACLE_BASE=/oracle
ORACLE_SID=+ASM1
ORA_ASM_HOME=/oracle/asmhome1
ORA_CRS_HOME=/oracle/crshome1
ORACLE_HOME=/oracle/asmhome1

Go to init.ora file destination
[oracle@srvdb01]:/oracle > cd $ORACLE_HOME/dbs

Check this file for the current spfile setting
[oracle@srvdb01]:/oracle/asmhome1/dbs > more init+ASM1.ora
SPFILE='/dev/asmspf_disk'

Also before you close the CRS the ASM database should show the below output.
SYS@+ASM1 AS SYSDBA> sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /dev/asmspf_disk


We need to backup this file, create a new pfile form current spfile, create a new spfile from this pfile in the new ASM spfile disk, and arrange the new init.ora file to point to the new ASm spfile location.

Backup the old pfile
[oracle@srvdb01]:/oracle/asmhome1/dbs > cp init+ASM1.ora init+ASM1.ora-201203201041


In sqlplus, create a new pfile from spfile
SYS@+ASM1 AS SYSDBA> create pfile from spfile;

File created.

Check the new pfile
[oracle@srvdb01]:/oracle/asmhome1/dbs > more init+ASM1.ora
+ASM1.asm_diskgroups='DG_DB_ASM','DG_FRA_ASM'#Manual Mount
*.asm_diskgroups='DG_DB_ASM','DG_FRA_ASM'
*.asm_diskstring='/dev/ASM*'
*.background_dump_dest='/oracle/admin/+ASM/bdump'
*.cluster_database=true
*.core_dump_dest='/oracle/admin/+ASM/cdump'
+ASM2.instance_number=2
+ASM1.instance_number=1
*.instance_type='asm'
*.large_pool_size=12M
*.processes=150
*.remote_login_passwordfile='exclusive'
*.user_dump_dest='/oracle/admin/+ASM/udump'


In sqlplus, create the new spfile in the new ASM spfile disk location
SYS@+ASM1 AS SYSDBA> create spfile='/dev/asmspf_disk_01' from pfile;

File created.

Change the new init.ora file to point to the new ASm spfile location
[oracle@srvdb01]:/oracle/asmhome1/dbs > more init+ASM1.ora
SPFILE='/dev/asmspf_disk_01'

Do all the above operations in the second node also

On second node
Switch to the the ASM profile.

[oracle@srvdb02]:/oracle > . .profile_asm
[oracle@srvdb02]:/oracle > env|grep ORA
ORACLE_BASE=/oracle
ORACLE_SID=+ASM2
ORA_ASM_HOME=/oracle/asmhome1
ORA_CRS_HOME=/oracle/crshome1
ORACLE_HOME=/oracle/asmhome1

Go to init.ora file destination
[oracle@srvdb02]:/oracle > cd $ORACLE_HOME/dbs

Check this file for the current spfile setting
[oracle@srvdb02]:/oracle/asmhome1/dbs > more init+ASM2.ora
SPFILE='/dev/asmspf_disk'

Also before you close the CRS the ASM database should show the below output.
SYS@+ASM1 AS SYSDBA> sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /dev/asmspf_disk


We need to backup this file, create a new pfile form current spfile, create a new spfile from this pfile in the new ASM spfile disk, and arrange the new init.ora file to point to the new ASm spfile location.

Backup the old pfile
[oracle@srvdb02]:/oracle/asmhome1/dbs > cp init+ASM2.ora init+ASM2.ora-201203201041


In sqlplus, create a new pfile from spfile
SYS@+ASM1 AS SYSDBA> create pfile from spfile;

File created.

Check the new pfile
[oracle@srvdb02]:/oracle/asmhome1/dbs > more init+ASM2.ora
+ASM1.asm_diskgroups='DG_DB_ASM','DG_FRA_ASM'#Manual Mount
*.asm_diskgroups='DG_DB_ASM','DG_FRA_ASM'
*.asm_diskstring='/dev/ASM*'
*.background_dump_dest='/oracle/admin/+ASM/bdump'
*.cluster_database=true
*.core_dump_dest='/oracle/admin/+ASM/cdump'
+ASM2.instance_number=2
+ASM1.instance_number=1
*.instance_type='asm'
*.large_pool_size=12M
*.processes=150
*.remote_login_passwordfile='exclusive'
*.user_dump_dest='/oracle/admin/+ASM/udump'


In sqlplus, create the new spfile in the new ASM spfile disk location
SYS@+ASM1 AS SYSDBA> create spfile='/dev/asmspf_disk_01' from pfile;

File created.

Change the new init.ora file to point to the new ASm spfile location
[oracle@srvdb02]:/oracle/asmhome1/dbs > more init+ASM2.ora
SPFILE='/dev/asmspf_disk_01'

---------------------------

Now that all the configurations are finished and we can try to open the CRS and see the result.

[root@srvdb01]:/home/root \> crsctl start crs
Attempting to start CRS stack 
The CRS stack will be started shortly
[root@srvdb01]:/home/root \> 

[root@srvdb02]:/home/root \> crsctl start crs
Attempting to start CRS stack 
The CRS stack will be started shortly
[root@srvdb02]:/home/root \> 

[root@srvdb01]:/home/root \> crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy

[root@srvdb02]:/home/root \> crsctl check crs
CSS appears healthy
CRS appears healthy
EVM appears healthy

[root@srvdb01]:/home/root \> crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora....T1.inst application    ONLINE    ONLINE    srvdb01   
ora....T2.inst application    ONLINE    ONLINE    srvdb02   
ora.CORET.db   application    ONLINE    ONLINE    srvdb02   
ora....SM1.asm application    ONLINE    ONLINE    srvdb01   
ora....01.lsnr application    ONLINE    ONLINE    srvdb01   
ora....b01.gsd application    ONLINE    ONLINE    srvdb01   
ora....b01.ons application    ONLINE    ONLINE    srvdb01   
ora....b01.vip application    ONLINE    ONLINE    srvdb01   
ora....SM2.asm application    ONLINE    ONLINE    srvdb02   
ora....02.lsnr application    ONLINE    ONLINE    srvdb02   
ora....b02.gsd application    ONLINE    ONLINE    srvdb02   
ora....b02.ons application    ONLINE    ONLINE    srvdb02   
ora....b02.vip application    ONLINE    ONLINE    srvdb02   
[root@srvdb01]:/home/root \> 

[root@srvdb02]:/home/root \> crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora....T1.inst application    ONLINE    ONLINE    srvdb01   
ora....T2.inst application    ONLINE    ONLINE    srvdb02   
ora.CORET.db   application    ONLINE    ONLINE    srvdb02   
ora....SM1.asm application    ONLINE    ONLINE    srvdb01   
ora....01.lsnr application    ONLINE    ONLINE    srvdb01   
ora....b01.gsd application    ONLINE    ONLINE    srvdb01   
ora....b01.ons application    ONLINE    ONLINE    srvdb01   
ora....b01.vip application    ONLINE    ONLINE    srvdb01   
ora....SM2.asm application    ONLINE    ONLINE    srvdb02   
ora....02.lsnr application    ONLINE    ONLINE    srvdb02   
ora....b02.gsd application    ONLINE    ONLINE    srvdb02   
ora....b02.ons application    ONLINE    ONLINE    srvdb02   
ora....b02.vip application    ONLINE    ONLINE    srvdb02   
[root@srvdb02]:/home/root \> 

As a result everything looks good.
Lets check if the ASM spfile is also updated in the ASM instances.

First node
[oracle@srvdb01]:/oracle > . .profile_asm
[YOU HAVE NEW MAIL]
[oracle@srvdb01]:/oracle > env|grep ORA
ORACLE_BASE=/oracle
ORACLE_SID=+ASM1
ORA_ASM_HOME=/oracle/asmhome1
ORA_CRS_HOME=/oracle/crshome1
ORACLE_HOME=/oracle/asmhome1


In sqlplus
SYS@+ASM1 > sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /dev/asmspf_disk_01
SYS@+ASM1 > 

Second node
[oracle@srvdb02]:/oracle > . .profile_asm
[YOU HAVE NEW MAIL]
[oracle@srvdb02]:/oracle > env|grep ORA
ORACLE_BASE=/oracle
ORACLE_SID=+ASM2
ORA_ASM_HOME=/oracle/asmhome1
ORA_CRS_HOME=/oracle/crshome1
ORACLE_HOME=/oracle/asmhome1


In sqlplus
SYS@+ASM2 > sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /dev/asmspf_disk_01
SYS@+ASM2 > 

ASM spfile replacement is also successfull, now we can rm the old ASm spfile virtual device on both nodes

[root@srvdb01]:/home/root \> rm /dev/asmspf_disk
[root@srvdb02]:/home/root \> rm /dev/asmspf_disk


Now this is the end of the operation which was successfull. I hope everything will go fine if you will need to replace OCR, Voting and ASM spfile disks in your Oracle RAC systems one day.

Monday, March 19, 2012

How to create an ASM Cluster File System (ACFS) in Oracle 11g ASM

If you ever need a clustered file system in an Oracle 11g RAC configuration you can use ACFS (ASM Cluster File System) method.
You can create a clustered file system which is accessible by all the nodes of the Oracle RAC and managed by Oracle ASM.

Below example is using Oracle 11gR2 (11.2.0.3) RAC database with two nodes on IBM servers with IBM AIX v6.1 OS.

Oracle RAC clusterware stack (CRS) and ASM should be up before starting the below operation.

[root@nldevrac02]:/home/root > crsctl check cluster -all 
**************************************************************
nldevrac01:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************
nldevrac02:
CRS-4537: Cluster Ready Services is online
CRS-4529: Cluster Synchronization Services is online
CRS-4533: Event Manager is online
**************************************************************

[root@nldevrac01]:/home/root > crsctl stat res ora.asm
NAME=ora.asm
TYPE=ora.asm.type
TARGET=ONLINE              , ONLINE
STATE=ONLINE on nldevrac01, ONLINE on nldevrac02

We need to do the following operation on only one of the RAC nodes.
Switch to Oracle GI owner user in Unix which is "oracle" for me.

[root@nldevrac01]:/home/root > su - oracle
[oracle@nldevrac01]:/oracle > 

We need to switch to Oracle 11g Grid Infrastructure (GI) profile in Unix since we will use ASMCA (Automatic Storage Management Configuration Assistant) utility from GI home binaries.

[oracle@nldevrac01]:/oracle > . .profile_grid_11g    

Check again if the Oracle related environment variables are correct

[oracle@nldevrac01]:/oracle > env | grep ORA
ORACLE_BASE=/ora11/oracle
ORACLE_SID=+ASM1
ORACLE_UNQNAME=COREP
ORACLE_HOME=/ora11/11.2.0/grid
ORA_GRID_HOME=/ora11/11.2.0/grid

Check if ASMCA utility is included in the PATH environment variable and oracle user can execute it.
[oracle@nldevrac01]:/oracle > which asmca
/ora11/11.2.0/grid/bin/asmca
[oracle@nldevrac01]:/oracle > 

Set the Unix "DISPLAY" parameter to the IP address of your PC like below.
[oracle@nldevrac01]:/oracle > export DISPLAY=10.1.20.99:0.0

Check that it's been set correct
[oracle@nldevrac01]:/oracle > echo $DISPLAY
10.1.20.99:0.0

You need to use an X-Windows client software like "WRQ Reflection X" or any others on you PC to be able to see the GUI screens of ASMCA utility on your monitor during the operation.
Start the X-Windows client software in listening mode on your PC.

Continue with ASMCA utility.

[oracle@nldevrac01]:/ora11/11.2.0/grid/dbs > asmca

You will see the following screen on your PC monitor when you start ASMCA utility


First we need to create a new ASM diskgroup. I choose "External" for the Redundancy of the new ASM diskgroup called "DG_ACFS_DISK_01" since the redundancy of the disks are managed in the Storage System externally. As a member disk, I choose already configured "/dev/ASM_Disk_RAC_voting_02" (do not take into account the name since it was intended for a different purpose and now I use it for ACFS) which point to a raw "/dev/hdisk" assigned to both RAC nodes. In "ASM Compatibility" part below, it will bring "11.2.0.0.0" automatically and it is very important that to be able to create an ACFS on a diskgroup this value should be set to this value. I also manually set the "Database Compatibility" to the value "11.2.0.0.0".


When I press OK button, after some time the following message is displayed.


Final disk group list is as the following with the new diskgroup. Just check very carefully that in the "State" column it should show "MOUNTED(2 of 2)", this means the new ASM diskgroup was successfully mounted in ASMs of both of the RAC nodes otherwise you can have different problem in the further steps.


Check again that this new ASM diskgroup is mounted in both RAC nodes.
[root@nldevrac01]:/home/root > crsctl stat res ora.DG_ACFS_DISK_01.dg
NAME=ora.DG_ACFS_DISK_01.dg
TYPE=ora.diskgroup.type
TARGET=ONLINE              , ONLINE
STATE=ONLINE on nldevrac01, ONLINE on nldevrac02

[root@nldevrac01]:/home/root > 

The next step is creating a Volume that is to be used in ACFS. Click the "Volumes" tab and press the "Create" button in this window.


You need to choose a "Volume Name" for your new volume. Choose the disk group name that you created in the previous steps and just give a size to this volume for how much space you need to use.


In next steps you will see the following windows.




Now that the volume is created. In the next step we will create the ACFS. Just click the "ASM Cluster File System" tab and press the "Create" button.



In that screen, since we will use this file system for general purpose, we choose "General Purpose File System", you can also keep your database binaries on a ACFS. In the mount point field you see how your new file system will look in your Unix server. After pressing the OK button I got the below error.


"ASM Cluster File System creation on /dev/as/oraacfs-116 failed with the following message:" but the message is empty. This can be a bug, but what I suspect is since I run the ASMCA utility by "oracle" unix user, this user is not capable of running the commands needed for creating the ACFS and I need to get and run those command by a more privileged user which is "root" in Unix. There is also a note about this situation at the bottom of the "ASM Cluster File Systems" tab like that "Note : Some ACFS commands can be executed as privileged/root user only. If you choose any of these operations, ASMCA will generate the command that can be executed as privileged/root user manually.

So I go to the "Create ASM Cluster File System" window and press the button "Show Command" and it shows the below commands.


Create ACFS Command:
/usr/sbin/mkfs -V acfs /dev/asm/oraacfs-116

Register Mount Point Command:
/sbin/acfsutil registry -a -f /dev/asm/oraacfs-116 /ora11/oracle/acfsmounts/dg_acfs_disk_01_oraacfs

Then I run those commands by logging in the system as root user as before this should be run only on one of the RAc nodes.

[root@nldevrac01]:/home/root > /usr/sbin/mkfs -V acfs /dev/asm/oraacfs-116
mkfs: version                   = 11.2.0.3.0
mkfs: on-disk version           = 39.0
mkfs: volume                    = /dev/asm/oraacfs-116
mkfs: volume size               = 536870912
mkfs: Format complete.
[root@nldevrac01]:/home/root > 6 /ora11/oracle/acfsmounts/dg_acfs_disk_01_oraacfs                                                <
acfsutil registry: mount point /ora11/oracle/acfsmounts/dg_acfs_disk_01_oraacfs successfully added to Oracle Registry

After that to refresh the "ASM Cluster File Systems" window, switch to "Volumes" tab then click again to "ASM CLuster File Systems" tab and now you can see the newly creates ACFS file system.
Now you can see the newly created ACFS file system in both of the RAC nodes and start to use it.
[root@nldevrac01]:/home/root > df -g
Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on
/dev/hd4           0.25      0.17   31%     3245     8% /
/dev/hd2           7.00      5.04   28%    42355     4% /usr
/dev/hd9var        0.25      0.22   13%      635     2% /var
/dev/hd3           2.00      1.70   16%     1180     1% /tmp
/dev/hd1           0.25      0.23    8%       50     1% /home
/proc                 -         -    -         -     -  /proc
/dev/hd10opt       0.25      0.06   78%     4927    26% /opt
/dev/sysperflv      0.25      0.21   15%       54     1% /sysperf
/dev/hd11admin      0.25      0.24    4%       17     1% /admin
/dev/oraclelv     39.50      7.46   82%    66335     4% /oracle
/dev/ora11lv      20.00      1.84   91%    55117    12% /ora11
/dev/asm/oraacfs-116      0.50      0.43   15%   147928    15% /ora11/oracle/acfsmounts/dg_acfs_disk_01_oraacfs
[root@nldevrac01]:/home/root > 

[root@nldevrac02]:/home/root > df -g
Filesystem    GB blocks      Free %Used    Iused %Iused Mounted on
/dev/hd4           0.25      0.18   28%     3215     7% /
/dev/hd2           7.00      5.04   28%    42338     4% /usr
/dev/hd9var        0.25      0.22   13%      630     2% /var
/dev/hd3           2.00      0.98   52%     2810     2% /tmp
/dev/hd1           0.25      0.23    8%       46     1% /home
/proc                 -         -    -         -     -  /proc
/dev/hd10opt       0.25      0.06   78%     4927    26% /opt
/dev/sysperflv      0.25      0.22   14%       54     1% /sysperf
/dev/hd11admin      0.25      0.24    4%       17     1% /admin
/dev/oraclelv     39.50     11.65   71%    56435     3% /oracle
/dev/ora11lv      20.00      1.89   91%    55416    11% /ora11
/dev/asm/oraacfs-116      0.50      0.43   15%   147928    15% /ora11/oracle/acfsmounts/dg_acfs_disk_01_oraacfs
[root@nldevrac02]:/home/root > 
As you can see the ownership of the new cluster file system is root, if you want to use it with oracle user just change the ownership to oracle or whatever user you will use.
[root@nldevrac02]:/home/root > ls -l /ora11/oracle
total 0
drwx------    2 oracle   dba             256 Mar 12 11:06 Clusterware
drwxr-xr-x    4 root     system          256 Mar 15 17:13 acfsmounts
drwx------    3 oracle   dba             256 Mar 12 14:18 admin
drwx------    6 oracle   dba             256 Mar 15 15:33 cfgtoollogs
drwxrwxr-x   11 oracle   dba            4096 Mar 12 13:09 diag
drwx------    3 oracle   dba             256 Mar 12 11:06 nldevrac02
drwx------    3 oracle   dba             256 Mar 13 13:25 oradata
drwxr-----    3 oracle   dba             256 Mar 13 16:28 oradiag_oracle
drwxr-xr-x    3 root     system          256 Mar 15 12:46 oradiag_root
drwx------    3 oracle   dba             256 Mar 12 13:08 product

Wednesday, March 7, 2012

PRKP-1001 and CRS-0215 errors in an Oracle 10gR2 RAC database

I just installed a test Oracle 10gR2 RAC database and I am getting the below error when I try to start the instance on first node by using srvctl utility. I do not get any error when I try to startup the instance in SQL Plus.

This is the current state and I want to open instance on first node, first component in the below list.
[root@nldevrac01]:/oracle/crshome1/bin > crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora....P1.inst application    ONLINE    OFFLINE               
ora....P2.inst application    ONLINE    OFFLINE               
ora.COREP.db   application    ONLINE    OFFLINE               
ora....SM1.asm application    ONLINE    ONLINE    nldevrac01  
ora....01.lsnr application    ONLINE    ONLINE    nldevrac01  
ora....c01.gsd application    ONLINE    ONLINE    nldevrac01  
ora....c01.ons application    ONLINE    ONLINE    nldevrac01  
ora....c01.vip application    ONLINE    ONLINE    nldevrac01  
ora....SM2.asm application    ONLINE    OFFLINE               
ora....02.lsnr application    ONLINE    OFFLINE               
ora....c02.gsd application    OFFLINE   OFFLINE               
ora....c02.ons application    ONLINE    OFFLINE               
ora....c02.vip application    ONLINE    ONLINE    nldevrac01  
[root@nldevrac01]:/oracle/crshome1/bin >

When I try to start it by using srvctl, it gives the below error.
[root@nldevrac01]:/oracle/crshome1/bin > srvctl start instance -d corep -i corep1
PRKP-1001 : Error starting instance COREP1 on node nldevrac01
CRS-0215: Could not start resource 'ora.COREP.COREP1.inst'.
[root@nldevrac01]:/oracle/crshome1/bin > 

Then I searched for the solution to this error, I started by checking the related log file to this error. I found the below error in one of the log files.
[oracle@nldevrac01]:/oracle/orahome1/log/nldevrac01/racg > vi imonCOREP.log
...
Oracle Database 10g RACG Release 10.2.0.4.0 Production Copyright 1996, 2005, Oracle.  All rights reserved.
SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 6 17:21:34 2012
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Enter user-name: Connected to an idle instance.
SQL> ORA-00119: invalid specification for system parameter LOCAL_LISTENER
ORA-00132: syntax error or unresolved network name 'LISTENER_NLDEVRAC01'
ORA-01078: failure in processing system parameters
SQL> Disconnected

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Mar 6 17:23:31 2012
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
Enter user-name: Connected to an idle instance.
SQL> ORACLE instance shut down.
SQL> Disconnected

Searching in the internet for a solution to this error I found the below solution and it worked for me. The problem was that srvctl utility was not aware of the TNS_ADMIN parameter setting in Oracle owner user's ".profile" file.
It's actually set to the below value but srvctl is unaware of it.

[oracle@nldevrac01]:/oracle > echo $TNS_ADMIN
/oracle/asmhome1/network/admin
[oracle@nldevrac01]:/oracle >

Solution :
SRVCTL works from the Oracle Cluster Registry information and does not know the TNS_ADMIN environment setting for the database and instances. Solving this problem is adding the TNS_NAMES environment setting to the Oracle Cluster Registry for the database and instances. The SRVCTL setenv statements to add the attribute TNS_ADMIN to the Oracle Cluster Rergistry have only to be executed from one node only.

Set this parameter
[oracle@nldevrac01]:/oracle > srvctl getenv database -d corep
[oracle@nldevrac01]:/oracle > srvctl setenv db -d corep -t TNS_ADMIN=/oracle/asmhome1/network/admin
[oracle@nldevrac01]:/oracle > srvctl getenv database -d corep
TNS_ADMIN=/oracle/asmhome1/network/admin

Try to start the instance now
[oracle@nldevrac01]:/oracle > srvctl start instance -d corep -i corep1                   

Check the final stat and I saw that the instance on first node and database was started successfully after setting the above parameter, first and third component below.
[oracle@nldevrac01]:/oracle > crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora....P1.inst application    ONLINE    ONLINE    nldevrac01  
ora....P2.inst application    ONLINE    OFFLINE               
ora.COREP.db   application    ONLINE    ONLINE    nldevrac01  
ora....SM1.asm application    ONLINE    ONLINE    nldevrac01  
ora....01.lsnr application    ONLINE    ONLINE    nldevrac01  
ora....c01.gsd application    ONLINE    ONLINE    nldevrac01  
ora....c01.ons application    ONLINE    ONLINE    nldevrac01  
ora....c01.vip application    ONLINE    ONLINE    nldevrac01  
ora....SM2.asm application    ONLINE    OFFLINE               
ora....02.lsnr application    ONLINE    OFFLINE               
ora....c02.gsd application    ONLINE    OFFLINE               
ora....c02.ons application    ONLINE    OFFLINE               
ora....c02.vip application    ONLINE    ONLINE    nldevrac01  
[oracle@nldevrac01]:/oracle > 

This was a quick solution for my case, I hope it can help to other people who will get the same error in their configurations.

Tuesday, September 27, 2011

How to configure Oracle Streams for table replication

Below post is a sample case which explains how to configure Oracle Streams for table replication in an Oracle 10gR2 database. It will use asynchronous downstream capture method when replicating a table from one database to another database. This means archive log files generated in the source database will be implicitly copied to the target database asynchronously and they will be processed in the target database to capture the changes related to the replicated table and they will be applied there.

First you need to create a new user who is the Streams administrator in both source and target databases. You also create a separate tablespace for this new user's objects.

Below command should be executed in both the source and the target databases.

To create a new tablespace
If you are using ASM with OMF(Oracle Managed Files) and ASSM (Automatic Segment Space Management), use the below statement.
create tablespace streams_tbs;

if not, use the below statement.
CREATE TABLESPACE streams_tbs DATAFILE '/usr/oracle/dbs/streams_tbs.dbf'
SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Ceate the Streams admin user
CREATE USER strmadmin
IDENTIFIED BY "strmadminpwd"
DEFAULT TABLESPACE streams_tbs
QUOTA UNLIMITED ON streams_tbs;
GRANT DBA TO strmadmin;

The source database should be in archivelog mode to generate files containing the change records happened in this database.

You can check with the following sql whether the source database is in archivelog mode or not.

--@db1
select log_mode from v$database;
--ARCHIVELOG

Next you should create an ANYDATA queue in the target database to associate with the capture process.
If the specified queue table does not exist, then it is created. If the specified queue table exists, then the existing queue table is used for the new queue. If you do not specify any queue table when you create the queue, then, by default, streams_queue_table is specified.
You can use a single procedure, the SET_UP_QUEUE procedure in the DBMS_STREAMS_ADM package, to create an ANYDATA queue and the queue table used by the queue. For SET_UP_QUEUE to create a new queue table, the specified queue table must
not exist.

--strmadmin@db2
BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table => 'strmadmin.streams_queue_table',
queue_name => 'strmadmin.streams_queue',
queue_user => 'd_dba');
END;
/

"queue_user" parameter is the user who is privileged to use this queue, in our case it is "d_dba" database user which we will replicate a table of this user.

You can use the following sqls to check the newly created queue.
select * from dba_queue_tables where owner='STRMADMIN'; 
/*
OWNER,QUEUE_TABLE,TYPE,OBJECT_TYPE,SORT_ORDER,RECIPIENTS,MESSAGE_GROUPING,COMPATIBLE,PRIMARY_INSTANCE,SECONDARY_INSTANCE,OWNER_INSTANCE,USER_COMMENT,SECURE
STRMADMIN,STREAMS_QUEUE_TABLE,OBJECT,SYS.ANYDATA,COMMIT_TIME,MULTIPLE,TRANSACTIONAL,10.0.0,0,0,1,,YES
*/
select * from dba_queues where owner='STRMADMIN'; 
/*
OWNER,NAME,QUEUE_TABLE,QID,QUEUE_TYPE,MAX_RETRIES,RETRY_DELAY,ENQUEUE_ENABLED,DEQUEUE_ENABLED,RETENTION,USER_COMMENT,NETWORK_NAME
STRMADMIN,STREAMS_QUEUE,STREAMS_QUEUE_TABLE,133367,NORMAL_QUEUE,5,0, YES , YES ,0,,
STRMADMIN,AQ$_STREAMS_QUEUE_TABLE_E,STREAMS_QUEUE_TABLE,133366,EXCEPTION_QUEUE,0,0, NO , NO ,0,exception queue,
*/
select * from dba_rulesets where owner='STRMADMIN'; 
/*
OWNER,RULESET_NAME,RULESET_STORAGE_TABLE,BASE_TABLE,RULESET_COMMENT
STRMADMIN,STREAMS_QUEUE_N,,STRMADMIN.AQ$_STREAMS_QUEUE_TABLE_V,
STRMADMIN,STREAMS_QUEUE_R,,STRMADMIN.AQ$_STREAMS_QUEUE_TABLE_V,
*/

We should add the tns alias of the target database to the "tnsnames.ora" file in source database. So that we will use this name for implicit archivelog file transfer to the target database.

--@db1srv
vi $TNS_ADMIN/tnsnames.ora

--Add these lines
### STREAMS CONFIG ###
DB2 =
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=DB2SRV)
(PORT=1521)
)
(CONNECT_DATA=
(SERVICE_NAME=DB2)
)
)
### END STREAMS CONFIG ###

In the target database's ASM create the folder structure where the newly created archivelogs will be copied.
--@db2srv
asmcmp -p
ASMCMD [+DG_DB_ASM_DB2/DB2] > mkdir ARCHIVELOG
ASMCMD [+DG_DB_ASM_DB2/DB2] > cd ARCHIVELOG
ASMCMD [+DG_DB_ASM_DB2/DB2/ARCHIVELOG] > mkdir FROM_DB1
ASMCMD [+DG_DB_ASM_DB2/DB2/ARCHIVELOG] > cd FROM_DB1
ASMCMD [+DG_DB_ASM_DB2/DB2/ARCHIVELOG/FROM_DB1] > ls -l
ASMCMD [+DG_DB_ASM_DB2/DB2/ARCHIVELOG/FROM_DB1] > 

In the source database configure the log_archive_dest_2 parameter and enable it so that it will point to and send the generated archivelogs to the destination database.
alter system set LOG_ARCHIVE_DEST_2='SERVICE=DB2 ASYNC NOREGISTER 
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) 
TEMPLATE=+DG_DB_ASM_DB2/DB2/ARCHIVELOG/FROM_DB1/%t_%s_%r.dbf 
DB_UNIQUE_NAME=DB2'
scope=both;
alter system set LOG_ARCHIVE_DEST_STATE_2='ENABLE';

Try to generate a new archive in source database and check if it is copied to the destination database ASM or not.

--@db1
alter system switch logfile;

--@db2srv
asmcmp -p
ASMCMD [+DG_DB_ASM_DB2/DB2/ARCHIVELOG/FROM_DB1] > ls -l
Type Redund Striped Time Sys Name
N 1_200291_699577217.dbf => +DG_DB_ASM_DB2/DB2/ARCHIVELOG/2011_09_22thread_1_seq_200291.296.762521353

We should add the tns alias of the source database to the "tnsnames.ora" file in target database. So that we will use this name for implicit archivelog file transfer to the target database.

--@db2srv

vi $TNS_ADMIN/tnsnames.ora

--Add these lines
### STREAMS CONFIG ###
DB1 =
(DESCRIPTION=
(ADDRESS=
(PROTOCOL=TCP)
(HOST=DB1SRV)
(PORT=1521)
)
(CONNECT_DATA=
(SERVICE_NAME=DB1)
)
)
### END STREAMS CONFIG ###

--test this tns alias if it is reachable or not
--@db2srv
tnsping DB1
TNS Ping Utility for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production on 22-SEP-2011 15:48:03
Copyright (c) 1997, 2007, Oracle. All rights reserved.
Used parameter files:
/oracle/asmhome1/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=DB1SRV) (PORT=1521)) (CONNECT_DATA= (SERVICE_NAME=DB1)))
OK (0 msec)

Create a database link in strmadmin schema in target database, we will use this later.
--strmadmin@db2
CREATE DATABASE LINK db1
CONNECT TO strmadmin
IDENTIFIED BY "strmadminpwd"
USING 'DB1';

--Test it if it works or not
select sysdate from dual@db1;
--OK

While connected to the downstream database as the Streams administrator, run
the CREATE_CAPTURE procedure to create the capture process:
BEGIN
DBMS_CAPTURE_ADM.CREATE_CAPTURE(
queue_name => 'strmadmin.streams_queue',
capture_name => 'strm04_capture',
rule_set_name => NULL,
start_scn => NULL,
source_database => 'db1',
use_database_link => true,
first_scn => NULL,
logfile_assignment => 'implicit');
END;
/
SELECT CAPTURE_NAME, FIRST_SCN, MAX_CHECKPOINT_SCN FROM DBA_CAPTURE;
/*
CAPTURE_NAME,FIRST_SCN,MAX_CHECKPOINT_SCN
STRM04_CAPTURE,74313112953,0
*/

We created a capture process called "strm04_capture".

This step does not associate the capture process strm04_capture with any rule
set. A rule set will be created and associated with the capture process in the next
step.
First we need to create the table in source database which will be replicated to the destination database.
--sys@db1
create table d_dba.streams_test_uural(col1 number, col2 varchar2(100), a_date date);
insert into d_dba.streams_test_uural(col1, col2, a_date) values (1,'1',sysdate);
commit;

While connected to the downstream database as the Streams administrator, create
the positive rule set for the capture process and add a rule to it:
--strmadmin@db2
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'd_dba.streams_test_uural',
streams_type => 'capture',
streams_name => 'strm04_capture',
queue_name => 'strmadmin.streams_queue',
include_dml => true,
include_ddl => false,
include_tagged_lcr => false,
source_database => 'db1',
inclusion_rule => true);
END;
/
select * from dba_rules where rule_owner = 'STRMADMIN';
/*
RULE_OWNER,RULE_NAME,RULE_CONDITION,RULE_EVALUATION_CONTEXT_OWNER,RULE_EVALUATION_CONTEXT_NAME,RULE_ACTION_CONTEXT,RULE_COMMENT
STRMADMIN,STREAMS_TEST_UURAL1,(((:dml.get_object_owner() = 'D_DBA' and :dml.get_object_name() = 'STREAMS_TEST_UURAL')) and :dml.is_null_tag() = 'Y' and :dml.get_source_database_name() = 'DB1' ),SYS,STREAMS$_EVALUATION_CONTEXT,,
*/
select * from dba_rulesets where owner = 'STRMADMIN' order by ruleset_name;
/*
OWNER,RULESET_NAME,RULESET_STORAGE_TABLE,BASE_TABLE,RULESET_COMMENT
STRMADMIN,RULESET$_2,,SYS.STREAMS$_EVALUATION_CONTEXT,streams name is STRM04_CAPTURE
STRMADMIN,STREAMS_QUEUE_N,,STRMADMIN.AQ$_STREAMS_QUEUE_TABLE_V,
STRMADMIN,STREAMS_QUEUE_R,,STRMADMIN.AQ$_STREAMS_QUEUE_TABLE_V,
*/
select * from dba_rule_set_rules where rule_set_owner = 'STRMADMIN' order by rule_set_name;
/*
RULE_SET_OWNER,RULE_SET_NAME,RULE_OWNER,RULE_NAME,RULE_SET_RULE_ENABLED,RULE_SET_RULE_EVAL_CTX_OWNER,RULE_SET_RULE_EVAL_CTX_NAME,RULE_SET_RULE_COMMENT
STRMADMIN,RULESET$_2,STRMADMIN,STREAMS_TEST_UURAL1,ENABLED,,,"STRMADMIN"."RULESET$_2"
*/

You should set minimum supplemental log data in the source database (SUPPLEMENTAL_LOG_DATA_MIN), so that you can enable the table level supplemental logging in the source database.
--sys@db1
select SUPPLEMENTAL_LOG_DATA_MIN,
SUPPLEMENTAL_LOG_DATA_PK,
SUPPLEMENTAL_LOG_DATA_UI from v$database;
/*
SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI
NO,NO,NO
*/
alter database add supplemental log data;
select SUPPLEMENTAL_LOG_DATA_MIN,
SUPPLEMENTAL_LOG_DATA_PK,
SUPPLEMENTAL_LOG_DATA_UI from v$database;
/*
SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI
YES,NO,NO
*/

Now you should instantiate the source table
--strmadmin@db1
BEGIN
DBMS_CAPTURE_ADM.PREPARE_TABLE_INSTANTIATION(
table_name => 'd_dba.streams_test_uural',
supplemental_logging => 'keys');
END;
/
/*
DROP TABLE D_DBA.STREAMS_TEST_UURAL CASCADE CONSTRAINTS;
CREATE TABLE D_DBA.STREAMS_TEST_UURAL
(
COL1 NUMBER,
COL2 VARCHAR2(100 BYTE),
A_DATE DATE,
SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS,
SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS,
SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS
)
TABLESPACE TS_DBA
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;
*/

By doing this you enabled the primary key, unique key and foreign key supplemental logging on the source table, so that if any of these key exists on this table the supplemental log data can be captured by using one one these key columns in the destination database.
If your table does not have any key columns like here then you should enable the "all" columns supplemental log data for this table to be place in to the newly generated archivelog files.

Check the current supplemental log data activated for this table and then add the all columns supplemental log data to this table.

Check activated supplemental logging
select * from dba_log_groups order by owner, table_name, log_group_type;
/*
OWNER,LOG_GROUP_NAME,TABLE_NAME,LOG_GROUP_TYPE,ALWAYS,GENERATED
D_DBA,SYS_C0043369,STREAMS_TEST_UURAL,FOREIGN KEY LOGGING,CONDITIONAL,GENERATED NAME
D_DBA,SYS_C0043367,STREAMS_TEST_UURAL,PRIMARY KEY LOGGING,ALWAYS,GENERATED NAME
D_DBA,SYS_C0043368,STREAMS_TEST_UURAL,UNIQUE KEY LOGGING,CONDITIONAL,GENERATED NAME
*/

alter table d_dba.streams_test_uural add supplemental log data (ALL) columns;

select * from dba_log_groups order by owner, table_name, log_group_type;
/*
OWNER,LOG_GROUP_NAME,TABLE_NAME,LOG_GROUP_TYPE,ALWAYS,GENERATED
D_DBA,SYS_C0043423,STREAMS_TEST_UURAL,ALL COLUMN LOGGING,ALWAYS,GENERATED NAME
D_DBA,SYS_C0043369,STREAMS_TEST_UURAL,FOREIGN KEY LOGGING,CONDITIONAL,GENERATED NAME
D_DBA,SYS_C0043367,STREAMS_TEST_UURAL,PRIMARY KEY LOGGING,ALWAYS,GENERATED NAME
D_DBA,SYS_C0043368,STREAMS_TEST_UURAL,UNIQUE KEY LOGGING,CONDITIONAL,GENERATED NAME
*/

Now we should create the replicated table in the target database as an empty table.
Instantiate the table in DB2
create table d_dba.streams_test_uural nologging
as select /*+ parallel(t1, 4) */ * from d_dba.streams_test_uural@db1 t1;

Create a database link in source database pointing to the target database
--strmadmin@db1 
CREATE DATABASE LINK db2
CONNECT TO strmadmin
IDENTIFIED BY "strmadminpwd"
USING 'DB2';
select sysdate from dual@db2;
--OK

Set the instantiation SCN for the "d_dba.streams_test_uural" table at db2 by running the following procedure at the source database db1:
DECLARE
iscn NUMBER; -- Variable to hold instantiation SCN value
BEGIN
iscn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER();
DBMS_APPLY_ADM.SET_TABLE_INSTANTIATION_SCN@db2(
source_object_name => 'd_dba.streams_test_uural',
source_database_name => 'db1',
instantiation_scn => iscn);
END;
/

After the instantiation SCN has been set, you can configure an apply process to apply LCRs for the "d_dba.streams_test_uural" table from the streams_queue queue. Setting the instantiation SCN for an object at a database is required only if an apply process applies LCRs for the object. When all of the necessary propagations and apply processes are configured, start the capture process using the START_CAPTURE procedure in DBMS_CAPTURE_ADM.

--strmadmin@db2
select * from dba_apply_instantiated_objects;
/*
SOURCE_DATABASE,SOURCE_OBJECT_OWNER,SOURCE_OBJECT_NAME,SOURCE_OBJECT_TYPE,INSTANTIATION_SCN,IGNORE_SCN,APPLY_DATABASE_LINK
DB1,D_DBA,STREAMS_TEST_UURAL,TABLE,74313116531,0,
*/

select * from dba_capture;
/*
CAPTURE_NAME,QUEUE_NAME,QUEUE_OWNER,RULE_SET_NAME,RULE_SET_OWNER,CAPTURE_USER,START_SCN,STATUS,CAPTURED_SCN,APPLIED_SCN,USE_DATABASE_LINK,FIRST_SCN,SOURCE_DATABASE,SOURCE_DBID,SOURCE_RESETLOGS_SCN,SOURCE_RESETLOGS_TIME,LOGMINER_ID,NEGATIVE_RULE_SET_NAME,NEGATIVE_RULE_SET_OWNER,MAX_CHECKPOINT_SCN,REQUIRED_CHECKPOINT_SCN,LOGFILE_ASSIGNMENT,STATUS_CHANGE_TIME,ERROR_NUMBER,ERROR_MESSAGE,VERSION,CAPTURE_TYPE,LAST_ENQUEUED_SCN,CHECKPOINT_RETENTION_TIME
STRM04_CAPTURE,STREAMS_QUEUE,STRMADMIN,RULESET$_2,STRMADMIN,STRMADMIN,74313112953,DISABLED,,,YES,74313112953,DB1,0,0,0,1,,,0,0,IMPLICIT,22/09/2011 17:52:51,,,,DOWNSTREAM,,60
*/
--STATUS=DISABLED

Creating an Apply Process for Captured Messages with DBMS_APPLY_ADM
BEGIN
DBMS_APPLY_ADM.CREATE_APPLY(
queue_name => 'strmadmin.streams_queue',
apply_name => 'strm04_apply',
rule_set_name => 'strmadmin.ruleset$_2',
message_handler => NULL,
ddl_handler => NULL,
apply_user => 'd_dba',
apply_database_link => NULL,
apply_tag => NULL,
apply_captured => true,
precommit_handler => NULL,
negative_rule_set_name => NULL,
source_database => 'db1');
END;
/
select * from dba_apply_error;
--Nrr
select * from dba_apply_progress;
/*
APPLY_NAME,SOURCE_DATABASE,APPLIED_MESSAGE_NUMBER,OLDEST_MESSAGE_NUMBER,APPLY_TIME,APPLIED_MESSAGE_CREATE_TIME,OLDEST_TRANSACTION_ID
STRM04_APPLY,DB1,0,0,01/01/1988,01/01/1988,
*/
select * from sys.streams$_apply_progress; --holds historical data of the apply process

Starting an Apply Process
BEGIN
DBMS_APPLY_ADM.START_APPLY(
apply_name => 'strm04_apply');
END;
/

Starting a Capture Process
BEGIN
DBMS_CAPTURE_ADM.START_CAPTURE(
capture_name => 'strm04_capture');
END;
/

Check the views
select * from dba_capture;
/*
CAPTURE_NAME,QUEUE_NAME,QUEUE_OWNER,RULE_SET_NAME,RULE_SET_OWNER,CAPTURE_USER,START_SCN,STATUS,CAPTURED_SCN,APPLIED_SCN,USE_DATABASE_LINK,FIRST_SCN,SOURCE_DATABASE,SOURCE_DBID,SOURCE_RESETLOGS_SCN,SOURCE_RESETLOGS_TIME,LOGMINER_ID,NEGATIVE_RULE_SET_NAME,NEGATIVE_RULE_SET_OWNER,MAX_CHECKPOINT_SCN,REQUIRED_CHECKPOINT_SCN,LOGFILE_ASSIGNMENT,STATUS_CHANGE_TIME,ERROR_NUMBER,ERROR_MESSAGE,VERSION,CAPTURE_TYPE,LAST_ENQUEUED_SCN,CHECKPOINT_RETENTION_TIME
STRM04_CAPTURE,STREAMS_QUEUE,STRMADMIN,RULESET$_2,STRMADMIN,STRMADMIN,74313112953,ENABLED,74313112953,74313112953,YES,74313112953,DB1,2977989118,538113,699577217,1,,,74318832255,0,IMPLICIT,23/09/2011 11:23:51,,,10.2.0.4.0,DOWNSTREAM,0,60
*/

--STATUS=ENABLED

select * from dba_apply;
/*
APPLY_NAME,QUEUE_NAME,QUEUE_OWNER,APPLY_CAPTURED,RULE_SET_NAME,RULE_SET_OWNER,APPLY_USER,APPLY_DATABASE_LINK,APPLY_TAG,DDL_HANDLER,PRECOMMIT_HANDLER,MESSAGE_HANDLER,STATUS,MAX_APPLIED_MESSAGE_NUMBER,NEGATIVE_RULE_SET_NAME,NEGATIVE_RULE_SET_OWNER,STATUS_CHANGE_TIME,ERROR_NUMBER,ERROR_MESSAGE
STRM04_APPLY,STREAMS_QUEUE,STRMADMIN,YES,RULESET$_2,STRMADMIN,D_DBA,,,,,,ENABLED,,,,23/09/2011 11:22:19,,
*/
select * from d_dba.streams_test_uural;
/*
COL1,COL2,A_DATE
1,1,22/09/2011 17:49:55
*/

Now try to add more rows to the source table in the source database and check whether they will be replicated to the target database or not.

--sys@db1
insert into d_dba.streams_test_uural(col1, col2, a_date) values (2,'2',sysdate);
commit;
alter system switch logfile;
select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual;
--74318845118
select max(sequence#) from v$archived_log where next_change# <= 74318845118; 
--200551

--strmadmin@db2
select * from v$streams_capture;
/*
SID,SERIAL#,CAPTURE#,CAPTURE_NAME,LOGMINER_ID,STARTUP_TIME,STATE,TOTAL_PREFILTER_DISCARDED,TOTAL_PREFILTER_KEPT,TOTAL_PREFILTER_EVALUATIONS,TOTAL_MESSAGES_CAPTURED,CAPTURE_TIME,CAPTURE_MESSAGE_NUMBER,CAPTURE_MESSAGE_CREATE_TIME,TOTAL_MESSAGES_CREATED,TOTAL_FULL_EVALUATIONS,TOTAL_MESSAGES_ENQUEUED,ENQUEUE_TIME,ENQUEUE_MESSAGE_NUMBER,ENQUEUE_MESSAGE_CREATE_TIME,AVAILABLE_MESSAGE_NUMBER,AVAILABLE_MESSAGE_CREATE_TIME,ELAPSED_CAPTURE_TIME,ELAPSED_RULE_TIME,ELAPSED_ENQUEUE_TIME,ELAPSED_LCR_TIME,ELAPSED_REDO_WAIT_TIME,ELAPSED_PAUSE_TIME,STATE_CHANGED_TIME
506,230,1,STRM04_CAPTURE,1,23/09/2011 11:23:51,CAPTURING CHANGES,6360288,0,6360289,157710,23/09/2011 11:25:49,74314650119,23/09/2011 06:55:23,157797,43,44,23/09/2011 11:24:35,74313260014,23/09/2011 00:24:42,74318845076,23/09/2011 10:46:45,11105,0,22,95,0,0,23/09/2011 11:25:49
*/
select * from d_dba.streams_test_uural;
/*
COL1,COL2,A_DATE
1,1,22/09/2011 17:49:55
2,2,23/09/2011 10:46:36
*/
As a result we see that newly inserted row in source table is replicated to the target database. We can check the apply errors, if there are any, by using the below sql in the target database.
select * from dba_apply_error;
--No rows returned
Above example only replicates the data changes in the source table to the destination table, we can also capture and apply the DDL changes happening on the source table to the destination table. You can use the following commands to achive this. Add DDL capture rule to existing table ruleset :
--strmadmin@db2
select * from dba_rules where rule_owner = 'STRMADMIN';
/*
RULE_OWNER,RULE_NAME,RULE_CONDITION,RULE_EVALUATION_CONTEXT_OWNER,RULE_EVALUATION_CONTEXT_NAME,RULE_ACTION_CONTEXT,RULE_COMMENT
STRMADMIN,STREAMS_TEST_UURAL1,(((:dml.get_object_owner() = 'D_DBA' and :dml.get_object_name() = 'STREAMS_TEST_UURAL')) and :dml.is_null_tag() = 'Y' and :dml.get_source_database_name() = 'DB1' ),SYS,STREAMS$_EVALUATION_CONTEXT,,
*/
select * from dba_rulesets where owner = 'STRMADMIN' order by ruleset_name;
/*
OWNER,RULESET_NAME,RULESET_STORAGE_TABLE,BASE_TABLE,RULESET_COMMENT
STRMADMIN,RULESET$_2,,SYS.STREAMS$_EVALUATION_CONTEXT,streams name is STRM04_CAPTURE
STRMADMIN,STREAMS_QUEUE_N,,STRMADMIN.AQ$_STREAMS_QUEUE_TABLE_V,
STRMADMIN,STREAMS_QUEUE_R,,STRMADMIN.AQ$_STREAMS_QUEUE_TABLE_V,
*/
select * from dba_rule_set_rules where rule_set_owner = 'STRMADMIN' order by rule_set_name;
/*
RULE_SET_OWNER,RULE_SET_NAME,RULE_OWNER,RULE_NAME,RULE_SET_RULE_ENABLED,RULE_SET_RULE_EVAL_CTX_OWNER,RULE_SET_RULE_EVAL_CTX_NAME,RULE_SET_RULE_COMMENT
STRMADMIN,RULESET$_2,STRMADMIN,STREAMS_TEST_UURAL1,ENABLED,,,"STRMADMIN"."RULESET$_2"
STRMADMIN,STREAMS_QUEUE_R,STRMADMIN,STREAMS_TEST_UURAL1,ENABLED,SYS,STREAMS$_EVALUATION_CONTEXT,adding to queue default ruleset
*/
Now we add a new DDL rule to the existing table rule by using the parameter "include_ddl => true"
BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => 'd_dba.streams_test_uural',
streams_type => 'capture',
streams_name => 'strm04_capture',
queue_name => 'strmadmin.streams_queue',
include_dml => false,
include_ddl => true,
source_database => 'db1',
inclusion_rule => true);
END;
/
Running this procedure performs the following actions: ¦ Creates two rules. One rule evaluates to TRUE for DML changes to the d_dba.streams_test_uural table, and the other rule evaluates to TRUE for DDL changes to the d_dba.streams_test_uural table. The rule names are system generated. ¦ Adds the two rules to the positive rule set associated with the capture process because the inclusion_rule parameter is set to true. ¦ Prepares the d_dba.streams_test_uural table for instantiation by running the PREPARE_TABLE_INSTANTIATION procedure in the DBMS_CAPTURE_ADM package. ¦ Enables supplemental logging for any primary key, unique key, bitmap index, and foreign key columns in the d_dba.streams_test_uural table. When the PREPARE_TABLE_INSTANTIATION procedure is run, the default value (keys) is specified for the supplemental_logging parameter. If the capture process is performing downstream capture, then the table is prepared for instantiation and supplemental logging is enabled for key columns only if the downstream capture process uses a database link to the source database. If a downstream capture process does not use a database link to the source database, then the table must be prepared for instantiation manually and supplemental logging must be enabled manually.
select * from dba_rules where rule_owner = 'STRMADMIN';
/*
RULE_OWNER,RULE_NAME,RULE_CONDITION,RULE_EVALUATION_CONTEXT_OWNER,RULE_EVALUATION_CONTEXT_NAME,RULE_ACTION_CONTEXT,RULE_COMMENT
STRMADMIN,STREAMS_TEST_UURAL1,(((:dml.get_object_owner() = 'D_DBA' and :dml.get_object_name() = 'STREAMS_TEST_UURAL')) and :dml.is_null_tag() = 'Y' and :dml.get_source_database_name() = 'DB1' ),SYS,STREAMS$_EVALUATION_CONTEXT,,
STRMADMIN,STREAMS_TEST_UURAL3,(((:ddl.get_object_owner() = 'D_DBA' and :ddl.get_object_name() = 'STREAMS_TEST_UURAL')or (:ddl.get_base_table_owner() = 'D_DBA' and :ddl.get_base_table_name() = 'STREAMS_TEST_UURAL')) and :ddl.is_null_tag() = 'Y' and :ddl.get_source_database_name() = 'DB1' ),SYS,STREAMS$_EVALUATION_CONTEXT,,
*/
select * from dba_rulesets where owner = 'STRMADMIN' order by ruleset_name;
/*
OWNER,RULESET_NAME,RULESET_STORAGE_TABLE,BASE_TABLE,RULESET_COMMENT
STRMADMIN,RULESET$_2,,SYS.STREAMS$_EVALUATION_CONTEXT,streams name is STRM04_CAPTURE
STRMADMIN,STREAMS_QUEUE_N,,STRMADMIN.AQ$_STREAMS_QUEUE_TABLE_V,
STRMADMIN,STREAMS_QUEUE_R,,STRMADMIN.AQ$_STREAMS_QUEUE_TABLE_V,
*/
select * from dba_rule_set_rules where rule_set_owner = 'STRMADMIN' order by rule_set_name;
/*
RULE_SET_OWNER,RULE_SET_NAME,RULE_OWNER,RULE_NAME,RULE_SET_RULE_ENABLED,RULE_SET_RULE_EVAL_CTX_OWNER,RULE_SET_RULE_EVAL_CTX_NAME,RULE_SET_RULE_COMMENT
STRMADMIN,RULESET$_2,STRMADMIN,STREAMS_TEST_UURAL3,ENABLED,,,
STRMADMIN,RULESET$_2,STRMADMIN,STREAMS_TEST_UURAL1,ENABLED,,,"STRMADMIN"."RULESET$_2"
STRMADMIN,STREAMS_QUEUE_R,STRMADMIN,STREAMS_TEST_UURAL3,ENABLED,SYS,STREAMS$_EVALUATION_CONTEXT,adding to queue default ruleset
STRMADMIN,STREAMS_QUEUE_R,STRMADMIN,STREAMS_TEST_UURAL1,ENABLED,SYS,STREAMS$_EVALUATION_CONTEXT,adding to queue default ruleset
*/
Lets test the DDL capture :
--sys@db1
alter table d_dba.streams_test_uural drop column col2;
select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER() from dual;
--74318863000
alter system switch logfile;
select max(sequence#) from v$archived_log where next_change# <= 74318863000; 
--200556

--strmadmin@db2
select * from v$streams_capture;
/*
SID,SERIAL#,CAPTURE#,CAPTURE_NAME,LOGMINER_ID,STARTUP_TIME,STATE,TOTAL_PREFILTER_DISCARDED,TOTAL_PREFILTER_KEPT,TOTAL_PREFILTER_EVALUATIONS,TOTAL_MESSAGES_CAPTURED,CAPTURE_TIME,CAPTURE_MESSAGE_NUMBER,CAPTURE_MESSAGE_CREATE_TIME,TOTAL_MESSAGES_CREATED,TOTAL_FULL_EVALUATIONS,TOTAL_MESSAGES_ENQUEUED,ENQUEUE_TIME,ENQUEUE_MESSAGE_NUMBER,ENQUEUE_MESSAGE_CREATE_TIME,AVAILABLE_MESSAGE_NUMBER,AVAILABLE_MESSAGE_CREATE_TIME,ELAPSED_CAPTURE_TIME,ELAPSED_RULE_TIME,ELAPSED_ENQUEUE_TIME,ELAPSED_LCR_TIME,ELAPSED_REDO_WAIT_TIME,ELAPSED_PAUSE_TIME,STATE_CHANGED_TIME
506,230,1,STRM04_CAPTURE,1,23/09/2011 11:23:51,WAITING FOR REDO: LAST SCN MINED 74318863003,18339223,0,18339230,224424,23/09/2011 17:15:01,74318863003,23/09/2011 17:14:50,224545,45,82,23/09/2011 17:15:33,74318863003,,74318863003,23/09/2011 17:14:58,25043,0,23,107,2087402,0,23/09/2011 17:16:03
*/
select * from d_dba.streams_test_uural;
/*
COL1,A_DATE
1,22/09/2011 17:49:55
3,23/09/2011 11:30:20
*/
OK , col2 column is dropped in destination db2 database also Other streams commands that you might need :
--Removing a Rule from a Rule Set for a Capture Process
BEGIN
DBMS_STREAMS_ADM.REMOVE_RULE(
rule_name => 'departments3',
streams_type => 'capture',
streams_name => 'strm01_capture',
drop_unused_rule => true,
inclusion_rule => true);
END;
/

--Removing a Rule Set for a Capture Process
BEGIN
DBMS_CAPTURE_ADM.ALTER_CAPTURE(
capture_name => 'strm01_capture',
remove_rule_set => true,
remove_negative_rule_set => true);
END;
/

--Setting a Capture Process Parameter
BEGIN
DBMS_CAPTURE_ADM.SET_PARAMETER(
capture_name => 'strm01_capture',
parameter => 'parallelism',
value => '3');
END;
/

--Removing a Streams Configuration
You run the REMOVE_STREAMS_CONFIGURATION procedure in the DBMS_STREAMS_ADM package to remove a Streams configuration at the local database.
To remove the Streams configuration at the local database, run the following
procedure:
EXEC DBMS_STREAMS_ADM.REMOVE_STREAMS_CONFIGURATION();
After running this procedure, drop the Streams administrator at the database, if
possible.
Attention: Running this procedure is dangerous. You should run
this procedure only if you are sure you want to remove the entire
Streams configuration at a database.

Friday, July 22, 2011

Why we should put index creation statements at the end of the table transfer scripts

Try to put the index creation statements at the end of the table transfer scritps, after the table data transfer part. It does not only increase the performance of the script in data insert part but also results in a better utilized index structure. Creating the index first and inserting table data causes different index structure to be created than inserting the table data and then creating the index, if your index key is not a unique value and inserted into the table in sequential order. Second option will be most probably faster and will cause the index blocks better utilized.

Below is a demonstration showing that case.

Lets start with creating a sample schema. You can find the contents of the "cre_user-U0001.sql" in my previous blog posts.
[oracle@localhost uural]$ sqlplus / as sysdba
 
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jul 21 01:02:47 2011
 
Copyright (c) 1982, 2008, Oracle.  All rights reserved.
 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> @cre_user-U0001
 
User dropped.
 

User created.
 

Grant succeeded.
 

User altered.
 

Grant succeeded.
 

Grant succeeded.
 
Connected.
USER is "U0001"
SQL> set lines 120
SQL> !vi check_index_stats.sql
 
set lines 120
select index_name, blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key, num_rows, clustering_factor
from user_indexes
where index_name = '&&index_name';
validate index &&index_name;
select name, height, blocks, lf_rows, lf_blks, lf_rows_len, lf_blk_len, br_rows, br_blks, br_rows_len, br_blk_len, del_lf_rows, del_lf_rows_len, distinct_keys, most_repeated_key, b
tree_space, used_space, pct_used, rows_per_key, blks_gets_per_access
from index_stats
where name = '&&index_name';
undefine index_name
--set lines 80
 
~
~

Now create an empty table and an index on its id column.
SQL> create table t1 tablespace ts_no_assm
  2  as
  3  select rownum id
  4  from dual
  5  where null is not null;
 
Table created.
 
SQL> create index t1_01 on t1(id) tablespace ts_no_assm;
 
Index created.

Then insert some 100,000 rows into that table, with id column values are not unique and sequential. You can think of that operation like you are transferring the data from another database table to "t1" table.
SQL> insert into t1
  2  select mod(rownum,1000) id
  3  from dual
  4  connect by level <= 100000;
 
100000 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> exec dbms_stats.gather_table_stats('','t1',estimate_percent=>null, cascade=>true, method_opt=>'for all columns size 1');
 
PL/SQL procedure successfully completed.

Check the index stats
SQL> @check_index_stats
Enter value for index_name: T1_01
old   3: where index_name = '&&index_name'
new   3: where index_name = 'T1_01'
 
INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
------------------------------ ---------- ----------- ------------- ----------------------- -----------------------
  NUM_ROWS CLUSTERING_FACTOR
---------- -----------------
T1_01                                   1         252          1000                       1                     100
    100000            100000
 

old   1: validate index &&index_name
new   1: validate index T1_01
 
Index analyzed.
 
old   3: where name = '&&index_name'
new   3: where name = 'T1_01'
 
NAME                               HEIGHT     BLOCKS    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS
------------------------------ ---------- ---------- ---------- ---------- ----------- ---------- ---------- ----------
BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED
----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ----------
ROWS_PER_KEY BLKS_GETS_PER_ACCESS
------------ --------------------
T1_01                                   2        256     100000        252     1489000       7996        251          1
       3314       8028           0               0          1000               100     2023020    1492314         74
         100                 52.5
As you can see in the index stats above, index has grown to 252 leaf blocks which are %74 full on average and index size is around 2 MB.

Now try the second case by first inserting the rows than creating the index.
SQL> drop table t1;
 
Table dropped.

SQL> create table t1 tablespace ts_no_assm
  2  as
  3  select rownum id
  4  from dual
  5  where null is not null;
 
Table created.
 
SQL> insert into t1
  2  select mod(rownum,1000) id
  3  from dual
  4  connect by level <= 100000;
 
100000 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> create index t1_01 on t1(id) tablespace ts_no_assm;
 
Index created.
 
SQL> exec dbms_stats.gather_table_stats('','t1',estimate_percent=>null, cascade=>true, method_opt=>'for all columns size 1');
 
PL/SQL procedure successfully completed.
 
SQL> @check_index_stats
Enter value for index_name: T1_01
old   3: where index_name = '&&index_name'
new   3: where index_name = 'T1_01'
 
INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
------------------------------ ---------- ----------- ------------- ----------------------- -----------------------
  NUM_ROWS CLUSTERING_FACTOR
---------- -----------------
T1_01                                   1         208          1000                       1                     100
    100000            100000
 

old   1: validate index &&index_name
new   1: validate index T1_01
 
Index analyzed.
 
old   3: where name = '&&index_name'
new   3: where name = 'T1_01'
 
NAME                               HEIGHT     BLOCKS    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS
------------------------------ ---------- ---------- ---------- ---------- ----------- ---------- ---------- ----------
BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED
----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ----------
ROWS_PER_KEY BLKS_GETS_PER_ACCESS
------------ --------------------
T1_01                                   2        256     100000        208     1489000       7996        207          1
       3066       8028           0               0          1000               100     1671196    1492066         90
         100                 52.5

As you can see now, index has grown to 208 leaf blocks which are %90 full on average and index size is around 1.7 MB.

As a result, putting the index creation statements at the end of a table transfer script, does not only makes the data insert operation faster, since the database will not have to deal with creating the index tree, but also makes the index well organized by utilizing the leaf blocks better with %90 average usage because of 90-10 index block splits.

The reason for this result is, when you first create the index and then insert the rows, the indexed column values are inserted into the table not in sequential order because of the "mod(rownum, 1000)" function because the same value was inserted again for every 1,000 rows causing 50-50 index leaf block splits. This caused the index allocate more blocks for its leaf blocks making the index larger. Although in the second scenario, since all the rows were already inserted to the table, when we created the index on id column, Oracle selected the id column values from table and sort them before inserting into the index tree causing only 90-10 index leaf block splits.

If the id column were unique and inserted into table in sequential order, it would not make much difference creating the index first from the index structure point of view, but anyway insert operation will be faster if you create the index as a last step.

At least, if you can not drop the existing index on an existing table and then create it after the insert operation, you can use the method of making the existing index "UNUSABLE" then "REBUILD" it after the insert operation which will be faster and more effective related the index structure.

In an OLTP system, which the insert, update, delete operations will start to happen after you transferred the table, index structure will soon do some 50-50 index leaf block splits and return to a structure with less utilized leaf blocks, but in a datawarehouse-like database where mostly insert and select statements happen, you can use this method more effectively.

Tuesday, July 19, 2011

Will index compression help us or not ?

Below are two demonstrations related to the index compression in Oracle. In the first test, index compression will make situation worse because the indexed column values are unique, in the second test we will see how much we will get by using index compression in a specific case.

Lets begin the first test by creating a sample schema and a sample table.
[oracle@localhost uural]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Tue Jul 19 01:45:22 2011

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> !vi cre_user-U0001.sql

drop user u0001 cascade;
create user u0001 identified by u0001;
grant create session to u0001;
alter user u0001 quota 5G on users;
grant create table to u0001;
grant dba to u0001;

conn u0001/u0001
sho user
~
~
~
"cre_user-U0001.sql" 9L, 207C written

SQL> @cre_user-U0001

User dropped.


User created.


Grant succeeded.


User altered.


Grant succeeded.


Grant succeeded.

Connected.
USER is "U0001"

I created a script to query the index related information form "user_indexes", validate the index and then query the "index_stats" view.
SQL> !vi check_index_stats.sql

set lines 120
select index_name, blevel, leaf_blocks, distinct_keys, avg_leaf_blocks_per_key, avg_data_blocks_per_key, num_rows, clustering_factor
from user_indexes
where index_name = '&&index_name';
validate index &&index_name;
select name, height, blocks, lf_rows, lf_blks, lf_rows_len, lf_blk_len, br_rows, br_blks, br_rows_len, br_blk_len, del_lf_rows, del_lf_rows_len, distinct_keys, most_repeated_key, btree_space, used_space, pct_used, rows_per_key, blks_gets_per_access
from index_stats
where name = '&&index_name';
undefine index_name
set lines 80
~
~
~
"check_index_stats.sql" 11L, 558C written


SQL> create table t1 as
  2  select
  3          rownum                          id,
  4          dbms_random.string('U',6)       v1,
  5          lpad(rownum,10)                 v2,
  6          rpad('x',100)                   padding
  7  from
  8  dual
  9  connect by level <= 1000000
 10  ;
/

SQL> create index t1_01 on t1(id);

Index created.

Elapsed: 00:00:02.02
SQL> exec dbms_stats.gather_table_stats('', 't1', estimate_percent => null, cascade => true, method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:16.47
SQL> @check_index_stats
Enter value for index_name: T1_01
old   3: where index_name = '&&index_name'
new   3: where index_name = 'T1_01'

INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
------------------------------ ---------- ----------- ------------- ----------------------- -----------------------
  NUM_ROWS CLUSTERING_FACTOR
---------- -----------------
T1_01                                   2        2226       1000000                       1                       1
   1000000             17858


Elapsed: 00:00:00.03
old   1: validate index &&index_name
new   1: validate index T1_01

Index analyzed.

Elapsed: 00:00:00.59
old   3: where name = '&&index_name'
new   3: where name = 'T1_01'

NAME                               HEIGHT     BLOCKS    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS
------------------------------ ---------- ---------- ---------- ---------- ----------- ---------- ---------- ----------
BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED
----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ----------
ROWS_PER_KEY BLKS_GETS_PER_ACCESS
------------ --------------------
T1_01                                   3       2304    1000000       2226    15979802       7996       2225          5
      26657       8028           0               0       1000000                 1    17839236   16006459         90
           1                    4


Elapsed: 00:00:00.19
As we can see from the above output, the index we created has 2226 leaf blocks and since the values in "id" column are unique it has 1,000,000 distinct keys, it took nearly 18 MB storage to keep this index.

Lets now try to query this table by using this index.
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.15
SQL> set lines 120
SQL> set autot trace
SQL> select * from t1 where id between 100000 and 200000;

100001 rows selected.

Elapsed: 00:00:03.25

Execution Plan
----------------------------------------------------------
Plan hash value: 2362378154

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   100K|    11M|  2013   (1)| 00:00:25 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |   100K|    11M|  2013   (1)| 00:00:25 |
|*  2 |   INDEX RANGE SCAN          | T1_01 |   100K|       |   226   (1)| 00:00:03 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID">=100000 AND "ID"<=200000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      15213  consistent gets
       2013  physical reads
          0  redo size
   13766300  bytes sent via SQL*Net to client
      73745  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100001  rows processed

SQL> set autot off
As a result it took 03.25 seconds, 15213 consistent gets and 2013 physical reads to get 100001 rows from the table.

Now drop and create this index as compressed
SQL> drop index t1_01;

Index dropped.

SQL> create index t1_01 on t1(id) compress;

Index created.

SQL> exec dbms_stats.gather_table_stats('', 't1', estimate_percent => null, cascade => true, method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

SQL> @check_index_stats
Enter value for index_name: T1_01
old   3: where index_name = '&&index_name'
new   3: where index_name = 'T1_01'

INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
------------------------------ ---------- ----------- ------------- ----------------------- -----------------------
  NUM_ROWS CLUSTERING_FACTOR
---------- -----------------
T1_01                                   2        3067       1000000                       1                       1
   1000000             17858


old   1: validate index &&index_name
new   1: validate index T1_01

Index analyzed.

old   3: where name = '&&index_name'
new   3: where name = 'T1_01'

NAME                               HEIGHT     BLOCKS    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS
------------------------------ ---------- ---------- ---------- ---------- ----------- ---------- ---------- ----------
BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED
----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ----------
ROWS_PER_KEY BLKS_GETS_PER_ACCESS
------------ --------------------
T1_01                                   3       3200    1000000       3067    11000000       7992       3066          6
      36702       8028           0               0       1000000                 1    24559632   22016504         90
           1                    4
As we can see from the above output, the compressed index we created has 3067 leaf blocks and it took nearly 25 MB storage to keep this index. These values are greater than the values that we got when we used non-compressed index.

Lets now try to query this table by using this compressed index.
SQL> alter system flush buffer_cache;

System altered.

SQL> set timing on
SQL> set autot trace
SQL> select * from t1 where id between 100000 and 200000;

100001 rows selected.

Elapsed: 00:00:03.31

Execution Plan
----------------------------------------------------------
Plan hash value: 2362378154

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   100K|    11M|  2097   (1)| 00:00:26 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |   100K|    11M|  2097   (1)| 00:00:26 |
|*  2 |   INDEX RANGE SCAN          | T1_01 |   100K|       |   310   (1)| 00:00:04 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID">=100000 AND "ID"<=200000)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      15292  consistent gets
       2097  physical reads
          0  redo size
   13766300  bytes sent via SQL*Net to client
      73745  bytes received via SQL*Net from client
       6668  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100001  rows processed

SQL> set autot off
SQL> drop index t1_01;

Index dropped.

Elapsed: 00:00:00.11

As a result, it took 03.31 seconds, 15292 consistent gets and 2097 physical reads to run the query by using this compressed index. These values are greater than the previous test which we used a non-compressed index.

In that case using a compressed index made situation worse. The reason for this is that, Oracle makes the compression by using an algorithm to tore the index key values only once in every index block in a special structure and put only the reference to this index key in index entries instead of using the index key itself.

Compressing an index on a column with unique values, causes the index to consume more storage space and it will make the performance worse. Because index will have to write all the key values in the block and keep an extra structure to manage the compressed index.

We should use the index compression on indexes with less selective columns, so that the number of repeating values for a index key in a block will increase making the index compression more efficient in terms of storage space and performance due to reading and fetching less number of index blocks to the database server memory.

Indeed, Oracle protects this kind of case to happen, if we had given it some information about this index column. If we try to create the index having only one column as unique and compressed, it will not allow us to do that, since the index compression in that case will not be efficient. It tries to protect us from making mistakes shortly.

Lets try this
SQL> create unique index t1_01 on t1(id) compress;
create unique index t1_01 on t1(id) compress
                                    *
ERROR at line 1:
ORA-25193: cannot use COMPRESS option for a single column key


-------------------

Lets demonstrate another case where index compression will help to decrease index size and improve the performance.
In this test, we will create a sample table which has a non-selective column with repeating values on which we will create our index.

SQL> set autot off
SQL> create table t1
  2  as
  3  with generator as (
  4      select  --+ materialize
  5          *
  6      from all_objects
  7  )
  8  select
  9      v1.*
 10  from
 11      generator   v1,
 12      generator   v2
 13  where
 14      rownum <= 1000000
 15  ;

Table created.

Elapsed: 00:00:23.85
SQL> create index t1_01 on t1(object_type);

Index created.

Elapsed: 00:00:02.80
SQL> exec dbms_stats.gather_table_stats('', 't1', estimate_percent => null, cascade => true, method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.38
SQL> @check_index_stats
Enter value for index_name: T1_01
old   3: where index_name = '&&index_name'
new   3: where index_name = 'T1_01'

INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
------------------------------ ---------- ----------- ------------- ----------------------- -----------------------
  NUM_ROWS CLUSTERING_FACTOR
---------- -----------------
T1_01                                   2        2391             3                     797                    3800
   1000000             11402


Elapsed: 00:00:00.02
old   1: validate index &&index_name
new   1: validate index T1_01

Index analyzed.

Elapsed: 00:00:00.54
old   3: where name = '&&index_name'
new   3: where name = 'T1_01'

NAME                               HEIGHT     BLOCKS    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS
------------------------------ ---------- ---------- ---------- ---------- ----------- ---------- ---------- ----------
BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED
----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ----------
ROWS_PER_KEY BLKS_GETS_PER_ACCESS
------------ --------------------
T1_01                                   3       2560    1000000       2391    17147394       7996       2390          7
      45746       8028           0               0             3            484121    19174632   17193140         90
  333333.333           166670.167


Elapsed: 00:00:00.19
This index has 2391 leaf blocks, takes nearly 19 MB storage space.

Lets try to query the table using this index.
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.18
SQL> set autot trace
SQL> select object_type, count(*) from t1 group by object_type order by object_type;

Elapsed: 00:00:00.35

Execution Plan
----------------------------------------------------------
Plan hash value: 3946799371

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    21 |  3168   (2)| 00:00:39 |
|   1 |  SORT GROUP BY     |      |     3 |    21 |  3168   (2)| 00:00:39 |
|   2 |   TABLE ACCESS FULL| T1   |  1000K|  6835K|  3139   (1)| 00:00:38 |
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      11397  consistent gets
      11395  physical reads
          0  redo size
        551  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed
Oracle chose Full Table Scan (FTS) because the index column "object_type" is not defined as a "NOT NULL" column and Oracle thinks that there maybe null values which are not in the index but in the table and do not use index. To force Oracle use index, we will make "object_type" column "NOT NULL".

SQL> alter table t1 modify object_type not null;

Table altered.

Elapsed: 00:00:00.29
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.06
SQL> select object_type, count(*) from t1 group by object_type order by object_type;

Elapsed: 00:00:00.35

Execution Plan
----------------------------------------------------------
Plan hash value: 2450666175

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     3 |    21 |   682   (5)| 00:00:09 |
|   1 |  SORT GROUP BY        |       |     3 |    21 |   682   (5)| 00:00:09 |
|   2 |   INDEX FAST FULL SCAN| T1_01 |  1000K|  6835K|   653   (1)| 00:00:08 |
-------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         74  recursive calls
          0  db block gets
       2473  consistent gets
       2415  physical reads
          0  redo size
        551  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          7  sorts (memory)
          0  sorts (disk)
          3  rows processed
Now Oracle uses this index. It takes 00.35 seconds, 2473 consistent gets and 2415 physical reads to get the final 3 rows.

Now try to recreate the index as compressed.
SQL> drop index t1_01;

Index dropped.

Elapsed: 00:00:00.13
SQL> create index t1_01 on t1(object_type) compress;

Index created.

Elapsed: 00:00:02.18
SQL> exec dbms_stats.gather_table_stats('', 't1', estimate_percent => null, cascade => true, method_opt => 'for all columns size 1');

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.07
SQL> set autot off
SQL> @check_index_stats
Enter value for index_name: T1_01
old   3: where index_name = '&&index_name'
new   3: where index_name = 'T1_01'

INDEX_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
------------------------------ ---------- ----------- ------------- ----------------------- -----------------------
  NUM_ROWS CLUSTERING_FACTOR
---------- -----------------
T1_01                                   2        1537             3                     512                    3800
   1000000             11402


Elapsed: 00:00:00.01
old   1: validate index &&index_name
new   1: validate index T1_01

Index analyzed.

Elapsed: 00:00:00.56
old   3: where name = '&&index_name'
new   3: where name = 'T1_01'

NAME                               HEIGHT     BLOCKS    LF_ROWS    LF_BLKS LF_ROWS_LEN LF_BLK_LEN    BR_ROWS    BR_BLKS
------------------------------ ---------- ---------- ---------- ---------- ----------- ---------- ---------- ----------
BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS MOST_REPEATED_KEY BTREE_SPACE USED_SPACE   PCT_USED
----------- ---------- ----------- --------------- ------------- ----------------- ----------- ---------- ----------
ROWS_PER_KEY BLKS_GETS_PER_ACCESS
------------ --------------------
T1_01                                   3       1664    1000000       1537    11000000       7992       1536          5
      29374       8028           0               0             3            484121    12323844   11048070         90
  333333.333           166670.167


Elapsed: 00:00:00.05
The new compressed index has 1537 leaf blocks, taking nearly 12 MB storage space and this is nearly half of the uncompressed index size. Also pay attention, how AVG_LEAF_BLOCKS_PER_KEY values got down from 797 to 512, which shows average index leaf blocks needed to store each index key.

Lets go on running the SQL by using this compressed index.
SQL> set autot trace
SQL> alter system flush buffer_cache;

System altered.

Elapsed: 00:00:00.07
SQL> select object_type, count(*) from t1 group by object_type order by object_type;

Elapsed: 00:00:00.28

Execution Plan
----------------------------------------------------------
Plan hash value: 2450666175

-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     3 |    21 |   451   (8)| 00:00:06 |
|   1 |  SORT GROUP BY        |       |     3 |    21 |   451   (8)| 00:00:06 |
|   2 |   INDEX FAST FULL SCAN| T1_01 |  1000K|  6835K|   422   (1)| 00:00:06 |
-------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1550  consistent gets
       1543  physical reads
          0  redo size
        551  bytes sent via SQL*Net to client
        419  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          3  rows processed

This time query took 00.28 seconds which is not too much faster than using a non-compressed index but still faster. Physical reads which are very expensive went down from 2415 to 1543. By using compressed index we saved some index storage and performance of some SQLs.