Gurpartap Singh's Blog: February 2013

Thursday, February 28, 2013

Post 47 of Series - Multiplex votedisk in oracle grid infrastructure on 11gR2

SHALOK:

God is lofty, unapproachable and infinite. He is indescribable ? He cannot be described. Nanak seeks the Sanctuary of God, who is all-powerful to save us. || 1 || CHHANT: Save me, any way You can; O Lord God, I am Yours. My demerits are uncountable; how many of them should I count? The sins and crimes I committed are countless; day by day, I continually make mistakes. I am intoxicated by emotional attachment to Maya, the treacherous one; by Your Grace alone can I be saved. Secretly, I commit hideous sins of corruption, even though God is the nearest of the near. Prays Nanak, shower me with Your Mercy, Lord, and lift me up, out of the whirlpool of the terrifying world-ocean.

Index of all the posts of Gurpartap Singh's Blog

13:10 : oracle : rac1.rac.meditate.com : @+ASM1 : /home/oracle

$ crsctl query css votedisk

## STATE File Universal Id File Name Disk group

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

1. ONLINE 09d3530bb83d4febbf7467484c487ef3 (/dev/sdc) [GRID]

Located 1 voting disk(s).

13:11 : oracle : rac1.rac.meditate.com : @+ASM1 : /home/oracle

SQL> set line 300

set pages 100

col label format a40

col path format a40

col HEADER_STATUS format a15

select group_number,disk_number,HEADER_STATUS,TOTAL_MB,LABEL,PATH,MODE_STATUS,STATE,name from v$asm_disk order by group_number

/SQL> SQL> SQL> SQL> SQL> 2

GROUP_NUMBER DISK_NUMBER HEADER_STATUS TOTAL_MB LABEL PATH MO

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

0 0 FORMER 0 /dev/sdu ON

0 1 FORMER 0 /dev/sdy ON

0 6 FORMER 0 /dev/sds ON

0 5 CANDIDATE 0 /dev/sdv ON

0 4 CANDIDATE 0 /dev/sdt ON

0 3 CANDIDATE 0 /dev/sdw ON

0 2 CANDIDATE 0 /dev/sdx ON

0 8 CANDIDATE 0 /dev/sdr ON

0 9 CANDIDATE 0 /dev/sdq ON

0 10 CANDIDATE 0 /dev/sdp ON

0 16 CANDIDATE 0 /dev/sdk ON

0 14 CANDIDATE 0 /dev/sdl ON

0 13 CANDIDATE 0 /dev/sdm ON

0 12 CANDIDATE 0 /dev/sdn ON

0 11 CANDIDATE 0 /dev/sdo ON

1 0 MEMBER 2048 /dev/sdb ON

1 3 MEMBER 2048 /dev/sdi ON

1 1 MEMBER 2048 /dev/sdd ON

1 2 MEMBER 2048 /dev/sde ON

1 4 MEMBER 2048 /dev/sdj ON

2 0 MEMBER 2048 /dev/sdf ON

2 1 MEMBER 2048 /dev/sdg ON

2 2 MEMBER 2048 /dev/sdh ON

3 0 MEMBER 2048 /dev/sdc ON

24 rows selected.

SQL>

ASMCMD> lsdg

State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED EXTERN N 512 4096 1048576 10240 4641 0 4641 0 N DATA/

MOUNTED EXTERN N 512 4096 1048576 6144 3757 0 3757 0 N FLASH/

MOUNTED EXTERN N 512 4096 1048576 2048 1652 0 1652 0 Y GRID/

ASMCMD>

Execute following as :

sqlplus / as sysasm

CREATE DISKGROUP SYSTEMDG1 HIGH REDUNDANCY

FAILGROUP DSK1 DISK '/dev/sdu'

FAILGROUP DSK2 DISK '/dev/sdy'

FAILGROUP DSK3 DISK '/dev/sds'

ATTRIBUTE 'compatible.asm' = '11.2.0.0.0' ;

SQL> CREATE DISKGROUP SYSTEMDG1 HIGH REDUNDANCY

FAILGROUP DSK1 DISK '/dev/sdu'

FAILGROUP DSK2 DISK '/dev/sdy'

FAILGROUP DSK3 DISK '/dev/sds'

ATTRIBUTE 'compatible.asm' = '11.2.0.0.0' ; 2 3 4 5

Diskgroup created.

SQL>

ASMCMD> lsdg

State Type Rebal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name

MOUNTED EXTERN N 512 4096 1048576 10240 4641 0 4641 0 N DATA/

MOUNTED EXTERN N 512 4096 1048576 6144 3757 0 3757 0 N FLASH/

MOUNTED EXTERN N 512 4096 1048576 2048 1652 0 1652 0 Y GRID/

MOUNTED HIGH N 512 4096 1048576 6144 5991 0 1997 0 N SYSTEMDG1/

ASMCMD>

13:17 : oracle : rac1.rac.meditate.com : @+ASM1 : /home/oracle

$ SQL> !srvctl status diskgroup -g SYSTEMDG1

Disk Group SYSTEMDG1 is running on rac1

SQL> srvctl start diskgroup -g SYSTEMDG1 -n rac2

SP2-0734: unknown command beginning "srvctl sta..." - rest of line ignored.

SQL> !srvctl start diskgroup -g SYSTEMDG1 -n rac2

SQL>

SQL> !srvctl status diskgroup -g SYSTEMDG1

Disk Group SYSTEMDG1 is running on rac2,rac1

SQL>

13:27 : oracle : rac1.rac.meditate.com : @+ASM1 : /home/oracle

$ crsctl replace votedisk +SYSTEMDG1

Failed to create voting files on disk group SYSTEMDG1.

Change to configuration failed, but was successfully rolled back.

CRS-4000: Command Replace failed, or completed with errors.

13:27 : oracle : rac1.rac.meditate.com : @+ASM1 : /home/oracle

Error in alertlogs is :

NOTE: Creating voting files in diskgroup SYSTEMDG1

Thu Feb 28 13:27:20 2013

NOTE: Voting File refresh pending for group 4/0x2a9882b9 (SYSTEMDG1)

NOTE: Attempting voting file creation in diskgroup SYSTEMDG1

NOTE: voting file allocation on grp 4 disk SYSTEMDG1_0000

NOTE: voting file allocation on grp 4 disk SYSTEMDG1_0001

NOTE: voting file allocation on grp 4 disk SYSTEMDG1_0002

ERROR: Voting file allocation failed for group SYSTEMDG1

Errors in file /u01/app/oracle/diag/asm/+asm/+ASM1/trace/+ASM1_ora_12749.trc:

ORA-15274: Not enough failgroups (5) to create voting files

NOTE: Attempting voting file refresh on diskgroup SYSTEMDG1

13:10 : oracle : rac1.rac.meditate.com : @+ASM1 : /home/oracle

$ crsctl query css votedisk

## STATE File Universal Id File Name Disk group

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

1. ONLINE 09d3530bb83d4febbf7467484c487ef3 (/dev/sdc) [GRID]

Located 1 voting disk(s).

13:11 : oracle : rac1.rac.meditate.com : @+ASM1 : /home/oracle

Remove this diskgroup as:

13:31 : oracle : rac1.rac.meditate.com : @+ASM1 : /home/oracle

$ srvctl status diskgroup -g SYSTEMDG1

Disk Group SYSTEMDG1 is running on rac1

13:31 : oracle : rac1.rac.meditate.com : @+ASM1 : /home/oracle

From asm alertlogs:

NOTE: Attempting voting file refresh on diskgroup SYSTEMDG1

Thu Feb 28 13:31:27 2013

Received detach msg from inst 2 for dom 4

drop diskgroup SYSTEMDG1;

SQL> drop diskgroup SYSTEMDG1;

Diskgroup dropped.

SQL>

From ASM alertlogs:

Thu Feb 28 13:32:30 2013

NOTE: diskgroup resource ora.SYSTEMDG1.dg is dropped

NOTE: diskgroup resource ora.SYSTEMDG1.dg is updated

Re-create with 5 disks:

CREATE DISKGROUP SYSTEMDG1 HIGH REDUNDANCY

FAILGROUP DSK1 DISK '/dev/sdu'

FAILGROUP DSK2 DISK '/dev/sdy'

FAILGROUP DSK3 DISK '/dev/sds'

FAILGROUP DSK4 DISK '/dev/sdv'

FAILGROUP DSK5 DISK '/dev/sdt'

ATTRIBUTE 'compatible.asm' = '11.2.0.0.0' ;

SQL> CREATE DISKGROUP SYSTEMDG1 HIGH REDUNDANCY

FAILGROUP DSK1 DISK '/dev/sdu'

FAILGROUP DSK2 DISK '/dev/sdy'

2 3 4 FAILGROUP DSK3 DISK '/dev/sds'

FAILGROUP DSK3 DISK '/dev/sdv'

FAILGROUP DSK3 DISK '/dev/sdt'

ATTRIBUTE 'compatible.asm' = '11.2.0.0.0' ; 5 6 7

Diskgroup created.

SQL>

From ASM alertlogs:

SQL> CREATE DISKGROUP SYSTEMDG1 HIGH REDUNDANCY

FAILGROUP DSK1 DISK '/dev/sdu'

FAILGROUP DSK2 DISK '/dev/sdy'

FAILGROUP DSK3 DISK '/dev/sds'

FAILGROUP DSK4 DISK '/dev/sdv'

FAILGROUP DSK5 DISK '/dev/sdt'

ATTRIBUTE 'compatible.asm' = '11.2.0.0.0'

NOTE: Assigning number (4,0) to disk (/dev/sdu)

NOTE: Assigning number (4,1) to disk (/dev/sdy)

NOTE: Assigning number (4,2) to disk (/dev/sds)

NOTE: Assigning number (4,3) to disk (/dev/sdv)

NOTE: Assigning number (4,4) to disk (/dev/sdt)

NOTE: initializing header on grp 4 disk SYSTEMDG1_0000

NOTE: initializing header on grp 4 disk SYSTEMDG1_0001

NOTE: initializing header on grp 4 disk SYSTEMDG1_0002

NOTE: initializing header on grp 4 disk SYSTEMDG1_0003

NOTE: initializing header on grp 4 disk SYSTEMDG1_0004

GMON updating for reconfiguration, group 4 at 93 for pid 27, osid 13350

NOTE: group 4 PST updated.

NOTE: initiating PST update: grp = 4

GMON updating group 4 at 94 for pid 27, osid 13350

NOTE: group SYSTEMDG1: initial PST location: disk 0000 (PST copy 0)

NOTE: group SYSTEMDG1: initial PST location: disk 0001 (PST copy 1)

NOTE: group SYSTEMDG1: initial PST location: disk 0002 (PST copy 2)

NOTE: group SYSTEMDG1: initial PST location: disk 0003 (PST copy 3)

NOTE: group SYSTEMDG1: initial PST location: disk 0004 (PST copy 4)

NOTE: PST update grp = 4 completed successfully

NOTE: cache registered group SYSTEMDG1 number=4 incarn=0x8e5882c5

NOTE: cache began mount (first) of group SYSTEMDG1 number=4 incarn=0x8e5882c5

NOTE: cache opening disk 0 of grp 4: SYSTEMDG1_0000 path:/dev/sdu

NOTE: cache opening disk 1 of grp 4: SYSTEMDG1_0001 path:/dev/sdy

NOTE: cache opening disk 2 of grp 4: SYSTEMDG1_0002 path:/dev/sds

NOTE: cache opening disk 3 of grp 4: SYSTEMDG1_0003 path:/dev/sdv

NOTE: cache opening disk 4 of grp 4: SYSTEMDG1_0004 path:/dev/sdt

* allocate domain 4, invalid = TRUE

kjbdomatt send to inst 2

NOTE: attached to recovery domain 4

NOTE: cache creating group 4/0x8E5882C5 (SYSTEMDG1)

NOTE: cache mounting group 4/0x8E5882C5 (SYSTEMDG1) succeeded

NOTE: allocating F1X0 on grp 4 disk SYSTEMDG1_0000

NOTE: allocating F1X0 on grp 4 disk SYSTEMDG1_0001

NOTE: allocating F1X0 on grp 4 disk SYSTEMDG1_0002

Thu Feb 28 13:37:31 2013

NOTE: Created Used Space Directory for 1 threads

NOTE: diskgroup must now be re-mounted prior to first use

NOTE: cache dismounting (clean) group 4/0x8E5882C5 (SYSTEMDG1)

NOTE: messaging CKPT to quiesce pins Unix process pid: 13350, image: oracle@rac1.rac.meditate.com (TNS V1-V3)

NOTE: lgwr not being msg'd to dismount

Thu Feb 28 13:37:33 2013

kjbdomdet send to inst 2

detach from dom 4, sending detach message to inst 2

freeing rdom 4

Thu Feb 28 13:37:33 2013

NOTE: detached from domain 4

NOTE: cache dismounted group 4/0x8E5882C5 (SYSTEMDG1)

Thu Feb 28 13:37:33 2013

GMON dismounting group 4 at 95 for pid 27, osid 13350

GMON dismounting group 4 at 96 for pid 27, osid 13350

NOTE: Disk in mode 0x8 marked for de-assignment

SUCCESS: diskgroup SYSTEMDG1 was created

NOTE: cache deleting context for group SYSTEMDG1 4/0x8e5882c5

NOTE: cache registered group SYSTEMDG1 number=4 incarn=0xaed882c7

NOTE: cache began mount (first) of group SYSTEMDG1 number=4 incarn=0xaed882c7

NOTE: Assigning number (4,0) to disk (/dev/sdu)

NOTE: Assigning number (4,1) to disk (/dev/sdy)

NOTE: Assigning number (4,4) to disk (/dev/sdt)

NOTE: Assigning number (4,3) to disk (/dev/sdv)

NOTE: Assigning number (4,2) to disk (/dev/sds)

NOTE: GMON heartbeating for grp 4

GMON querying group 4 at 99 for pid 27, osid 13350

NOTE: cache opening disk 0 of grp 4: SYSTEMDG1_0000 path:/dev/sdu

NOTE: F1X0 found on disk 0 au 2 fcn 0.0

NOTE: cache opening disk 1 of grp 4: SYSTEMDG1_0001 path:/dev/sdy

NOTE: F1X0 found on disk 1 au 2 fcn 0.0

NOTE: cache opening disk 2 of grp 4: SYSTEMDG1_0002 path:/dev/sds

NOTE: F1X0 found on disk 2 au 2 fcn 0.0

NOTE: cache opening disk 3 of grp 4: SYSTEMDG1_0003 path:/dev/sdv

NOTE: cache opening disk 4 of grp 4: SYSTEMDG1_0004 path:/dev/sdt

NOTE: cache mounting (first) high redundancy group 4/0xAED882C7 (SYSTEMDG1)

* allocate domain 4, invalid = TRUE

kjbdomatt send to inst 2

NOTE: attached to recovery domain 4

NOTE: cache recovered group 4 to fcn 0.0

NOTE: redo buffer size is 256 blocks (1053184 bytes)

Thu Feb 28 13:37:40 2013

NOTE: LGWR attempting to mount thread 1 for diskgroup 4 (SYSTEMDG1)

NOTE: LGWR found thread 1 closed at ABA 0.10750

NOTE: LGWR mounted thread 1 for diskgroup 4 (SYSTEMDG1)

NOTE: LGWR opening thread 1 at fcn 0.0 ABA 2.0

NOTE: setting 11.2 start ABA for group SYSTEMDG1 thread 1 to 2.0

NOTE: cache mounting group 4/0xAED882C7 (SYSTEMDG1) succeeded

NOTE: cache ending mount (success) of group SYSTEMDG1 number=4 incarn=0xaed882c7

GMON querying group 4 at 100 for pid 18, osid 5637

Thu Feb 28 13:37:40 2013

NOTE: Instance updated compatible.asm to 11.2.0.0.0 for grp 4

SUCCESS: diskgroup SYSTEMDG1 was mounted

SUCCESS: CREATE DISKGROUP SYSTEMDG1 HIGH REDUNDANCY

FAILGROUP DSK1 DISK '/dev/sdu'

FAILGROUP DSK2 DISK '/dev/sdy'

FAILGROUP DSK3 DISK '/dev/sds'

FAILGROUP DSK4 DISK '/dev/sdv'

FAILGROUP DSK5 DISK '/dev/sdt'

ATTRIBUTE 'compatible.asm' = '11.2.0.0.0'

Thu Feb 28 13:37:40 2013

NOTE: diskgroup resource ora.SYSTEMDG1.dg is online

ERROR: failed to update diskgroup resource ora.SYSTEMDG1.dg

13:38 : oracle : rac1.rac.meditate.com : @+ASM1 : /home/oracle

$ srvctl status diskgroup -g SYSTEMDG1

Disk Group SYSTEMDG1 is running on rac1

13:38 : oracle : rac1.rac.meditate.com : @+ASM1 : /home/oracle

$ srvctl start diskgroup -g SYSTEMDG1 -n rac2

13:38 : oracle : rac1.rac.meditate.com : @+ASM1 : /home/oracle

$ srvctl status diskgroup -g SYSTEMDG1

Disk Group SYSTEMDG1 is running on rac2,rac1

13:38 : oracle : rac1.rac.meditate.com : @+ASM1 : /home/oracle

Now lets replace the votedisk as:

13:38 : oracle : rac1.rac.meditate.com : @+ASM1 : /home/oracle

$ srvctl status diskgroup -g SYSTEMDG1

Disk Group SYSTEMDG1 is running on rac2,rac1

13:38 : oracle : rac1.rac.meditate.com : @+ASM1 : /home/oracle

$ crsctl replace votedisk +SYSTEMDG1

Successful addition of voting disk 14366cb6a43b4f5cbf2fe1af3133fad8.

Successful addition of voting disk 304950c7ffc94fefbf1942c9f7efdb44.

Successful addition of voting disk 943b639654a34f54bf4d07cdab09d3b5.

Successful addition of voting disk 900115d54a994f3abf064854dfd4e72b.

Successful addition of voting disk 440afa008b3e4f2cbfe2c118f90df5bd.

Successful deletion of voting disk 09d3530bb83d4febbf7467484c487ef3.

Successfully replaced voting disk group with +SYSTEMDG1.

CRS-4266: Voting file(s) successfully replaced

13:40 : oracle : rac1.rac.meditate.com : @+ASM1 : /home/oracle

ASM alertlogs on node 1:

Thu Feb 28 13:40:26 2013

NOTE: [crsctl.bin@rac1.rac.meditate.com (TNS V1-V3) 13572] opening OCR file

Thu Feb 28 13:40:26 2013

NOTE: updated gpnp profile ASM diskstring: /dev/sd*

Thu Feb 28 13:40:26 2013

NOTE: Creating voting files in diskgroup SYSTEMDG1

Thu Feb 28 13:40:26 2013

NOTE: Voting File refresh pending for group 4/0xaed882c7 (SYSTEMDG1)

NOTE: Attempting voting file creation in diskgroup SYSTEMDG1

NOTE: voting file allocation on grp 4 disk SYSTEMDG1_0000

NOTE: voting file allocation on grp 4 disk SYSTEMDG1_0001

NOTE: voting file allocation on grp 4 disk SYSTEMDG1_0002

NOTE: voting file allocation on grp 4 disk SYSTEMDG1_0003

NOTE: voting file allocation on grp 4 disk SYSTEMDG1_0004

Thu Feb 28 13:40:28 2013

NOTE: Deleting voting files in diskgroup GRID

NOTE: Voting File refresh pending for group 3/0x24a88272 (GRID)

NOTE: Attempting voting file refresh on diskgroup GRID

NOTE: Voting file relocation is required in diskgroup GRID

NOTE: Attempting voting file relocation on diskgroup GRID

NOTE: voting file deletion on grp 3 disk GRID_0000

NOTE: Attempting voting file refresh on diskgroup SYSTEMDG1

ASM alertlogs on node 2:

Thu Feb 28 13:40:26 2013

NOTE: Voting File refresh pending for group 4/0xaed880e8 (SYSTEMDG1)

NOTE: Voting File refresh pending for group 3/0x24a880dd (GRID)

NOTE: Attempting voting file refresh on diskgroup GRID

NOTE: Attempting voting file refresh on diskgroup SYSTEMDG1

Cluster Alertlogs node 1:

2013-02-28 13:40:28.416

[cssd(5337)]CRS-1605:CSSD voting file is online: /dev/sdu; details in /u01/app/11.2.0.3/grid/log/rac1/cssd/ocssd.log.

2013-02-28 13:40:28.416

[cssd(5337)]CRS-1605:CSSD voting file is online: /dev/sdy; details in /u01/app/11.2.0.3/grid/log/rac1/cssd/ocssd.log.

2013-02-28 13:40:28.416

[cssd(5337)]CRS-1605:CSSD voting file is online: /dev/sds; details in /u01/app/11.2.0.3/grid/log/rac1/cssd/ocssd.log.

2013-02-28 13:40:28.416

[cssd(5337)]CRS-1605:CSSD voting file is online: /dev/sdv; details in /u01/app/11.2.0.3/grid/log/rac1/cssd/ocssd.log.

2013-02-28 13:40:28.416

[cssd(5337)]CRS-1605:CSSD voting file is online: /dev/sdt; details in /u01/app/11.2.0.3/grid/log/rac1/cssd/ocssd.log.

2013-02-28 13:40:28.416

[cssd(5337)]CRS-1604:CSSD voting file is offline: /dev/sdc; details at (:CSSNM00069:) in /u01/app/11.2.0.3/grid/log/rac1/cssd/ocssd.log.

2013-02-28 13:40:28.416

[cssd(5337)]CRS-1626:A Configuration change request completed successfully

2013-02-28 13:40:28.433

[cssd(5337)]CRS-1601:CSSD Reconfiguration complete. Active nodes are rac1 rac2 .

Cluster log on node 2 :

2013-02-28 13:40:28.416

[cssd(5302)]CRS-1605:CSSD voting file is online: /dev/sdu; details in /u01/app/11.2.0.3/grid/log/rac2/cssd/ocssd.log.

2013-02-28 13:40:28.422

[cssd(5302)]CRS-1605:CSSD voting file is online: /dev/sdy; details in /u01/app/11.2.0.3/grid/log/rac2/cssd/ocssd.log.

2013-02-28 13:40:28.423

[cssd(5302)]CRS-1605:CSSD voting file is online: /dev/sds; details in /u01/app/11.2.0.3/grid/log/rac2/cssd/ocssd.log.

2013-02-28 13:40:28.423

[cssd(5302)]CRS-1605:CSSD voting file is online: /dev/sdv; details in /u01/app/11.2.0.3/grid/log/rac2/cssd/ocssd.log.

2013-02-28 13:40:28.423

[cssd(5302)]CRS-1605:CSSD voting file is online: /dev/sdt; details in /u01/app/11.2.0.3/grid/log/rac2/cssd/ocssd.log.

2013-02-28 13:40:28.423

[cssd(5302)]CRS-1604:CSSD voting file is offline: /dev/sdc; details at (:CSSNM00069:) in /u01/app/11.2.0.3/grid/log/rac2/cssd/ocssd.log.

2013-02-28 13:40:28.423

[cssd(5302)]CRS-1626:A Configuration change request completed successfully

2013-02-28 13:40:28.435

[cssd(5302)]CRS-1601:CSSD Reconfiguration complete. Active nodes are rac1 rac2 .

======================================================================================================================================================

13:40 : oracle : rac1.rac.meditate.com : @+ASM1 : /home/oracle

$ crsctl query css votedisk

## STATE File Universal Id File Name Disk group

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

1. ONLINE 14366cb6a43b4f5cbf2fe1af3133fad8 (/dev/sdu) [SYSTEMDG1]

2. ONLINE 304950c7ffc94fefbf1942c9f7efdb44 (/dev/sdy) [SYSTEMDG1]

3. ONLINE 943b639654a34f54bf4d07cdab09d3b5 (/dev/sds) [SYSTEMDG1]

4. ONLINE 900115d54a994f3abf064854dfd4e72b (/dev/sdv) [SYSTEMDG1]

5. ONLINE 440afa008b3e4f2cbfe2c118f90df5bd (/dev/sdt) [SYSTEMDG1]

Located 5 voting disk(s).

13:42 : oracle : rac1.rac.meditate.com : @+ASM1 : /home/oracle

ONE UNIVERSAL CREATOR GOD. BY THE GRACE OF THE TRUE GURU:

Wherever You seat me, there I sit, O my Lord and Master; wherever You send me, there I go. In the entire village, there is

only One King; all places are sacred. || 1 || O Baba, while I dwell in this body, let me sing Your True Praises, that I may

intuitively merge with You. || 1 || Pause || He thinks that good and bad deeds come from himself; this is the source of all

evil. Whatever happens in this world is only by the Order of our Lord and Master. || 2 || Sexual desires are so strong and

compelling; where has this sexual desire come from? The Creator Himself stages all the plays; how rare are those who realize this. || 3 || By Guru?s Grace, one is lovingly focused on the One Lord, and then, duality is ended. Whatever is in harmony with His Will, he accepts as True; the noose of Death is loosened from around his neck. || 4 || Prays Nanak, who can call him to account, when the egotistical pride of his mind has been silenced? Even the Righteous Judge of Dharma is intimidated and afraid of him; he has entered the Sanctuary of the True Lord. || 5 || 1 ||

Tuesday, February 26, 2013

Post 46 of Series - Parallel select query spanning on multiple RAC instances dies if one of the instance its running on crashes or stops

Shalok, Fifth Mehl :

Those who do not remember the lord while they are alive, shall mix with the dust when they die. Nanak, the foolish and filthy cynic passes his life engrossed in the world.

Index of all the posts of Gurpartap Singh's Blog

Here I will execute a select query on a two node RAC using a service which spans on two nodes on the RAC cluster and while the query is running one node goes down and the parallel query instead on failing over on to the surviving node, just errors out.

14:57 : oracle : rac1.rac.meditate.com : @GSINGH1 : /home/oracle
$ . oraenv
ORACLE_SID = [GSINGH!] ? GSINGH1
The Oracle base has been set to /u01/app/oracle
14:57 : oracle : rac1.rac.meditate.com : @GSINGH1 : /home/oracle
$ srvctl status service -d GSINGH
14:58 : oracle : rac1.rac.meditate.com : @GSINGH1 : /home/oracle
$ srvctl add service -d GSINGH -s PARALLEL -r GSINGH1 -a GSINGH2
15:03 : oracle : rac1.rac.meditate.com : @GSINGH1 : /home/oracle
$
15:07 : oracle : rac1.rac.meditate.com : @GSINGH1 : /u01/app/oracle/product/11.2.0.3/network/admin
$ srvctl start service -d GSINGH -s PARALLEL
15:07 : oracle : rac1.rac.meditate.com : @GSINGH1 : /u01/app/oracle/product/11.2.0.3/network/admin
$ srvctl status service -d GSINGH -s PARALLEL
Service PARALLEL is running on instance(s) GSINGH1
15:08 : oracle : rac1.rac.meditate.com : @GSINGH1 : /u01/app/oracle/product/11.2.0.3/network/admin
$
15:08 : oracle : rac1.rac.meditate.com : @GSINGH1 : /u01/app/oracle/product/11.2.0.3/network/admin
$ srvctl modify service -d GSINGH -s PARALLEL -n -i GSINGH1,GSINGH2
15:09 : oracle : rac1.rac.meditate.com : @GSINGH1 : /u01/app/oracle/product/11.2.0.3/network/admin
$ srvctl stop service -d GSINGH -s PARALLEL
15:09 : oracle : rac1.rac.meditate.com : @GSINGH1 : /u01/app/oracle/product/11.2.0.3/network/admin
$ srvctl start service -d GSINGH -s PARALLEL
15:10 : oracle : rac1.rac.meditate.com : @GSINGH1 : /u01/app/oracle/product/11.2.0.3/network/admin
$ srvctl status service -d GSINGH -s PARALLEL
Service PARALLEL is running on instance(s) GSINGH1,GSINGH2
15:10 : oracle : rac1.rac.meditate.com : @GSINGH1 : /u01/app/oracle/product/11.2.0.3/network/admin
$
===================================================================================================================================
15:10 : oracle : rac1.rac.meditate.com : @GSINGH1 : /u01/app/oracle/product/11.2.0.3/network/admin
$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Tue Feb 26 15:10:58 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> show parameter parallel servers
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_limit                string      CPU
parallel_degree_policy               string      MANUAL
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     FALSE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     40
parallel_min_percent                 integer     0
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_min_servers                 integer     0
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     TRUE
parallel_server_instances            integer     2
parallel_servers_target              integer     16
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0
SQL>
SQL> conn gurpartap/gurpartap@parallel
Connected.
SQL>

=======================================================================================================
Open another window i.e. window 2 and execute the following query:
set pages 50
col SID_SERIAL format a15
col OSUSER format a12
col module format a25
col action format a20
col machine format a20
col username format a19
col QCINST_ID format 99
set lines 240
SELECT y.inst_id,a.qcinst_id, a.qcsid, a.qcserial# qcser, a.sid||','||a.serial# sid_serial, substr(y.osuser,1,13) osuser, y.username, substr(y.module,1,25) module, substr(y.action,1,20) action, substr(y.machine,8,20) machine, a.degree, a.req_degree, y.inst_id, y.sql_id, y.status, y.logon_time, y.last_call_et lc_et
    FROM gv$px_session a, gv$session y
    WHERE y.sid = a.sid
    and y.inst_id = a.inst_id
   order by a.qcsid, y.username, y.sql_id, y.inst_id, y.status
/

SQL> set line 300
SQL> set pages 50
col SID_SERIAL format a15
col OSUSER format a12
col module format a25
col action format a20
col machine format a20
col username format a19
col QCINST_ID format 99
set lines 240
SELECT y.inst_id,a.qcinst_id, a.qcsid, a.qcserial# qcser, a.sid||','||a.serial# sid_serial, substr(y.osuser,1,13) osuser, y.username, substr(y.module,1,25) module, substr(y.action,1,20) action, substr(y.machine,8,20) machine, a.degree, a.req_degree, y.inst_id, y.sql_id, y.status, y.logon_time, y.last_call_et lc_et
    FROM gv$px_session a, gv$session y
    WHERE y.sid = a.sid
    and y.inst_id = a.inst_id
SQL>    order by a.qcsid, y.username, y.sql_id, y.inst_id, y.status
/
SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3    4    5    6
   INST_ID QCINST_ID      QCSID      QCSER SID_SERIAL      OSUSER       USERNAME            MODULE                    ACTION               MACHINE                  DEGREE REQ_DEGREE    INST_ID SQL_ID        STATUS LOGON_TIM      LC_ET
---------- --------- ---------- ---------- --------------- ------------ ------------------- ------------------------- -------------------- -------------------- ---------- ---------- ---------- ------------- -------- --------- ----------
         1         1         76          7 29,71           oracle       SYS                 sqlplus@rac1.rac.meditate                      c.meditate.com                2          2          1 8wn8m9gpwnw3s ACTIVE 26-FEB-13          0
         1                   76            76,7            oracle       SYS                 sqlplus@rac1.rac.meditate                      c.meditate.com                                      1 8wn8m9gpwnw3s ACTIVE 26-FEB-13          0
         2         1         76          7 80,23           oracle       SYS                 sqlplus@rac1.rac.meditate                      c.meditate.com                2          2          2 8wn8m9gpwnw3s ACTIVE 26-FEB-13          0
SQL>
SQL> /
Now go back on the original window i.e. window 1 and execute the query in parallel as follows:
select /*+ parallel( a 33) parallel(b 33) */ count(*) from dba_objects a, dba_objects b;

SQL> l
1* select /*+ parallel( a 33) parallel(b 33) */ count(*) from dba_objects a, dba_objects b
SQL> /

Now go back to window 2 and check the parallel sessions from the query we ran earlier on views gv$px_session and gv$session and you will see 33 parallel sessions
on the database as given below and they will have be running on both node1 and node2 as:
SQL>
/
   INST_ID QCINST_ID      QCSID      QCSER SID_SERIAL      OSUSER       USERNAME            MODULE                    ACTION               MACHINE                  DEGREE REQ_DEGREE    INST_ID SQL_ID        STATUS LOGON_TIM      LC_ET
---------- --------- ---------- ---------- --------------- ------------ ------------------- ------------------------- -------------------- -------------------- ---------- ---------- ---------- ------------- -------- --------- ----------
         1         1         56          3 77,3            oracle       SYS                 sqlplus@rac1.rac.meditate                      c.meditate.com                2          2          1 8wn8m9gpwnw3s ACTIVE 26-FEB-13          0
         1                   56            56,3            oracle       SYS                 sqlplus@rac1.rac.meditate                      c.meditate.com                                      1 8wn8m9gpwnw3s ACTIVE 26-FEB-13          1
         2         1         56          3 82,9            oracle       SYS                 sqlplus@rac1.rac.meditate                      c.meditate.com                2          2          2 8wn8m9gpwnw3s ACTIVE 26-FEB-13          0
         1         2         74         53 63,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 64,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 65,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 66,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 67,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 68,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 69,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 70,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 71,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 72,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 73,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 74,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 75,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 62,3            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 61,31           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 53,15           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 55,25           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         2         2         74         53 45,111          oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 35,101          oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 52,5            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 58,189          oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 59,59           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 66,87           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 67,119          oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 68,21           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 69,37           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 70,29           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2                   74            74,53           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com                                      2 3z073sy7b8gfa ACTIVE 26-FEB-13          5
         2         2         74         53 75,45           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 78,7            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 79,5            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 80,37           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 81,15           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 51,5            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
37 rows selected.
SQL>
   INST_ID QCINST_ID      QCSID      QCSER SID_SERIAL      OSUSER       USERNAME            MODULE                    ACTION               MACHINE                  DEGREE REQ_DEGREE    INST_ID SQL_ID        STATUS LOGON_TIM      LC_ET
---------- --------- ---------- ---------- --------------- ------------ ------------------- ------------------------- -------------------- -------------------- ---------- ---------- ---------- ------------- -------- --------- ----------
         1         1         56          3 77,7            oracle       SYS                 sqlplus@rac1.rac.meditate                      c.meditate.com                2          2          1 8wn8m9gpwnw3s ACTIVE 26-FEB-13          0
         1                   56            56,3            oracle       SYS                 sqlplus@rac1.rac.meditate                      c.meditate.com                                      1 8wn8m9gpwnw3s ACTIVE 26-FEB-13          0
         2         1         56          3 82,13           oracle       SYS                 sqlplus@rac1.rac.meditate                      c.meditate.com                2          2          2 8wn8m9gpwnw3s ACTIVE 26-FEB-13          0
         1         2         74         53 63,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 64,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 65,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 66,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 67,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 68,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 69,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 70,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 71,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 72,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 73,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 74,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 75,1            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 62,3            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 61,31           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 53,15           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         1         2         74         53 55,25           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          1 3z073sy7b8gfa ACTIVE 26-FEB-13          1
         2         2         74         53 45,111          oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 35,101          oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 52,5            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 58,189          oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 59,59           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 66,87           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 67,119          oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 68,21           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 69,37           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 70,29           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2                   74            74,53           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com                                      2 3z073sy7b8gfa ACTIVE 26-FEB-13          5
         2         2         74         53 75,45           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 78,7            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 79,5            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 80,37           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 81,15           oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
         2         2         74         53 51,5            oracle       GURPARTAP           SQL*Plus                                       c.meditate.com               33         33          2 3z073sy7b8gfa ACTIVE 26-FEB-13          0
37 rows selected.

Now on this window just stop instance 1 and see that the query in window 1 will error out instead of failing over:
On window 2:
SQL> !srvctl stop instance -d GSINGH -i GSINGH1
SQL>

In window 1:
SQL> select /*+ parallel( a 33) parallel(b 33) */ count(*) from dba_objects a, dba_objects b;
select /*+ parallel( a 33) parallel(b 33) */ count(*) from dba_objects a, dba_objects b
                                                             *
ERROR at line 1:
ORA-12805: parallel query server died unexpectedly

SQL>
SQL>

==================================================================================================

Shalok, Fifth Mehl:

Within in my mind, I think thoughts of always rising early, and making the effort. O Lord, my Friend, please bless Nanak with the habit of singing the Kirtan of the Lord's Praises.