PRKP-1001 and CRS-0215 while starting 10g RAC database on 11gr2 cluster using srvctl

Recently we created 10g RAC database on 11gR2 cluster but could not able to start database with srvctl altough we can using sqlplus. Solution is to copy tnsnames.ora and listener.ora files from TNS_ADMIN location to ORACLE_HOME/network/admin:

problem:
srvctl start instance -d MYDB -i MYINST
PRKP-1001 : Error starting instance MYINST on node mynode
CRS-0215: Could not start resource 'ora.MYDB.MYINST.inst'.

Solution:
cp $TNS_ADMIN/listener.ora $ORACLE_HOME/network/admin/listener.ora
cp $TNS_ADMIN/tnsnames.ora $ORACLE_HOME/network/admin/tnsnames.ora

Using fked to restore ASM Disk Header (test)

Recently i tested kfed for restoring ASM disk header corruption. Here are the steps:


1-) Create a test Diskgroup,  test tablespace and test table
CREATE DISKGROUP TEST EXTERNAL REDUNDANCY
DISK
'/dev/rhdisk6',
'/dev/rhdisk7'
ATTRIBUTE
'compatible.asm' = '11.2',
'compatible.rdbms' = '11.2';

CREATE TABLESPACE TEST_TS DATAFILE '+TEST' SIZE 100M AUTOEXTEND ON NEXT 125M MAXSIZE 10240M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

create table SCOTT.TEST_TABLE (a int, b varchar2(1000)) tablespace TEST_TS;
insert into SCOTT.TEST_TABLE values (1, 'asddghmsddfuh');
insert into SCOTT.TEST_TABLE values (2, 'asddghmsddfuh');
insert into SCOTT.TEST_TABLE values (3,'sdfghfasdfgywbefg');

2-) Check ASM
SQL> select GROUP_NUMBER, NAME, ALLOCATION_UNIT_SIZE, TYPE, TOTAL_MB-FREE_MB USED_MB, TOTAL_MB, COMPATIBILITY, DATABASE_COMPATIBILITY from v$asm_diskgroup;
GROUP_NUMBER NAME                           ALLOCATION_UNIT_SIZE TYPE      USED_MB   TOTAL_MB COMPATIBIL DATABASE_C
------------ ------------------------------ -------------------- ------ ---------- ---------- ---------- ----------
           1 DATACRS                                     1048576 EXTERN       2123      10240 11.2.0.0.0 10.1.0.0.0
           2 TEST                                        1048576 EXTERN        163      10240 11.2.0.0.0 11.2.0.0.0

SQL> select GROUP_NUMBER, HEADER_STATUS, PATH,NAME, LIBRARY, FAILGROUP_TYPE,  trunc(OS_MB/1024*1024*1024/1024/1024) SUM_OS from V$ASM_DISK;
GROUP_NUMBER HEADER_STATU PATH                           NAME                           LIBRARY              FAILGRO     SUM_OS
------------ ------------ ------------------------------ ------------------------------ -------------------- ------- ----------
           0 CANDIDATE    /dev/rhdisk9                                                  System               REGULAR          5
           0 CANDIDATE    /dev/rhdisk8                                                  System               REGULAR          5
           1 MEMBER       /dev/rhdisk2                   DATACRS_0000                   System               REGULAR          5
           1 MEMBER       /dev/rhdisk3                   DATACRS_0001                   System               REGULAR          5
           2 MEMBER       /dev/rhdisk6                   TEST_0000                      System               REGULAR          5
           2 MEMBER       /dev/rhdisk7                   TEST_0001                      System               REGULAR          5

2-) Corrupt disk hdisk6 (with root):

node1
lquerypv -h /dev/rhdisk6
00000000   00820101 00000000 80000000 B8CC7719  |..............w.|
00000010   00000000 00000000 00000000 00000000  |................|
00000020   4F52434C 4449534B 00000000 00000000  |ORCLDISK........|
00000030   00000000 00000000 00000000 00000000  |................|
00000040   0B200000 00000103 54455354 5F303030  |. ......TEST_000|
00000050   30000000 00000000 00000000 00000000  |0...............|
00000060   00000000 00000000 54455354 00000000  |........TEST....|
00000070   00000000 00000000 00000000 00000000  |................|
00000080   00000000 00000000 54455354 5F303030  |........TEST_000|
00000090   30000000 00000000 00000000 00000000  |0...............|
000000A0   00000000 00000000 00000000 00000000  |................|
000000B0   00000000 00000000 00000000 00000000  |................|
000000C0   00000000 00000000 01F6B0CB 6422B400  |............d"..|
000000D0   01F6B0CB 64C47000 02001000 00100000  |....d.p.........|
000000E0   0001BC80 00001400 00000002 00000001  |................|
000000F0   00000002 00000002 00000000 00000000  |................|
#stop crs before. Other case ASM will protect disk from corruption
./crsctl stop crs  
chdev -l hdisk6 -a pv=yes
lquerypv -h /dev/rhdisk6 #corrupted!!!
00000000   C9C2D4C1 00000000 00000000 00000000  |................|
00000010   00000000 00000000 00000000 00000000  |................|
00000020   00000000 00000000 00000000 00000000  |................|
00000030   00000000 00000000 00000000 00000000  |................|
00000040   00000000 00000000 00000000 00000000  |................|
00000050   00000000 00000000 00000000 00000000  |................|
00000060   00000000 00000000 00000000 00000000  |................|
00000070   00000000 00000000 00000000 00000000  |................|
00000080   00C2E250 BB2CE1C7 00000000 00000000  |...P.,..........|
00000090   00000000 00000000 00000000 00000000  |................|
000000A0   00000000 00000000 00000000 00000000  |................|
000000B0   00000000 00000000 00000000 00000000  |................|
000000C0   00000000 00000000 00000000 00000000  |................|
000000D0   00000000 00000000 00000000 00000000  |................|
000000E0   00000000 00000000 00000000 00000000  |................|
000000F0   00000000 00000000 00000000 00000000  |................|

3-) TEST DG seem to be unmounted. Try to mount it:

ASMCMD> mount TEST
ORA-15032: not all alterations performed
ORA-15017: diskgroup "TEST" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "TEST" (DBD ERROR: OCIStmtExecute)

SQL> select * from SCOTT.TEST_TABLE ;
select * from SCOTT.TEST_TABLE
                    *
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '+TEST/mydb/datafile/test_ts.256.737033885'

4-) Restore disk header using kfed (with root)
kfed REPAIR /dev/rhdisk6
#now it seems to be ok!
lquerypv -h /dev/rhdisk6
00000000   00820101 00000000 80000000 B8CC7719  |..............w.|
00000010   00000000 00000000 00000000 00000000  |................|
00000020   4F52434C 4449534B 00000000 00000000  |ORCLDISK........|
00000030   00000000 00000000 00000000 00000000  |................|
00000040   0B200000 00000103 54455354 5F303030  |. ......TEST_000|
00000050   30000000 00000000 00000000 00000000  |0...............|
00000060   00000000 00000000 54455354 00000000  |........TEST....|
00000070   00000000 00000000 00000000 00000000  |................|
00000080   00000000 00000000 54455354 5F303030  |........TEST_000|
00000090   30000000 00000000 00000000 00000000  |0...............|
000000A0   00000000 00000000 00000000 00000000  |................|
000000B0   00000000 00000000 00000000 00000000  |................|
000000C0   00000000 00000000 01F6B0CB 6422B400  |............d"..|
000000D0   01F6B0CB 64C47000 02001000 00100000  |....d.p.........|
000000E0   0001BC80 00001400 00000002 00000001  |................|
000000F0   00000002 00000002 00000000 00000000  |................|

5-) Try to mount again:

ASMCMD> mount TEST #ok!!!

6-) Now do some tests (everything ok)
CREATE TABLESPACE TEST_TS2 DATAFILE '+TEST' SIZE 100M AUTOEXTEND ON NEXT 125M MAXSIZE 10240M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
create table SCOTT.TEST_TABLE2 (a int, b varchar2(1000)) tablespace TEST_TS;

insert into SCOTT.TEST_TABLE2 values (4, 'asddghmsddfuh');
insert into SCOTT.TEST_TABLE2 values (5, 'asddghmsddfuh');
insert into SCOTT.TEST_TABLE2 values (6,'sdfghfasdfgywbefg');
drop table SCOTT.TEST_TABLE;
drop tablespace TEST_TS;
alter diskgroup TEST add disk '/dev/rhdisk8','/dev/rhdisk9' drop disk 'TEST_0000','TEST_0001';


SQL> select GROUP_NUMBER, HEADER_STATUS, PATH,NAME, LIBRARY, FAILGROUP_TYPE,  trunc(OS_MB/1024*1024*1024/1024/1024) SUM_OS from V$ASM_DISK;
GROUP_NUMBER HEADER_STATU PATH                           NAME                           LIBRARY              FAILGRO     SUM_OS
------------ ------------ ------------------------------ ------------------------------ -------------------- ------- ----------
           0 FORMER       /dev/rhdisk6                                                  System               REGULAR          5
           0 FORMER       /dev/rhdisk7                                                  System               REGULAR          5
           1 MEMBER       /dev/rhdisk2                   DATACRS_0000                   System               REGULAR          5
           1 MEMBER       /dev/rhdisk3                   DATACRS_0001                   System               REGULAR          5
           2 MEMBER       /dev/rhdisk8                   TEST_0002                      System               REGULAR          5
           2 MEMBER       /dev/rhdisk9                   TEST_0003                      System               REGULAR          5

CRS-0222 and CRS-2640 while removing RAC datagourp

Recently I tried to drop a datagroup from RAC cluster. It was previously registered with cluster while creating a RAC database:

HA Resource                                   Target     State
ora.FRA.dg                                    ONLINE     OFFLINE

I moved all database files from this datagroup to an other. Then when i tried to drop it, following error occured:

srvctl remove diskgroup -g FRA
PRCA-1002 : Failed to remove CRS resource ora.FRA.dg for ASM Disk Group FRA
PRCR-1028 : Failed to remove resource ora.FRA.dg
PRCR-1072 : Failed to unregister resource ora.FRA.dg
CRS-0222: Resource 'ora.FRA.dg' has dependency error.

The right thing was to remove datagroup as told in here.
1-) srvctl modify database -d MYDB -a “DATA,OTHER_DG,...”
2-) srvctl disable diskgroup -g FRA
3-) srvctl remove diskgroup -g FRA –f

However, I removed datagroup first:

srvctl remove diskgroup -g FRA -f

Then, when i tried to open database, It gave me fallowing error:

srvctl start database -d mydb
PRCR-1079 : Failed to start resource ora.mydb.db
CRS-2640: Required resource 'ora.FRA.dg' is missing.

That was because altough i removed datagroup with force option, dependency was remained. You can see it with similar command:

./crsctl status resource ora.mydb.db -f|grep FRA
START_DEPENDENCIES=hard(ora.DATA.dg,ora.FRA.dg) weak(type:ora.listener.type,global:type:ora.scan_listener.type,uniform:ora.ons,uniform:ora.eons) pullup(ora.DATA.dg)
STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.DATA.dg,shutdown:ora.FRA.dg)

I run fallowing command to remove FRA dependency:

srvctl modify database -d MYDB -a “DATA”  (step 1)
When i check dependency again, i saw that FRA was gone:

./crsctl status resource ora.mydb.db -f|grep FRA
(nothing)

./crsctl status resource ora.mydb.db -f|grep DATA
START_DEPENDENCIES=hard(ora.DATA.dg) weak(type:ora.listener.type,global:type:ora.scan_listener.type,uniform:ora.ons,uniform:ora.eons) pullup(ora.DATA.dg)
STOP_DEPENDENCIES=hard(intermediate:ora.asm,shutdown:ora.DATA.dg)

After that, i could able to open my database

srvctl start database -d mydb

How to Copy Files in ASM

cp command in ASMCMD actually not copies system generated the file but creates an alias for real file. Here is a procedure to copy system generated file (redo log) from one asm datagroup to an other:

1-) Login a database which has access to asm disk groups

2-) Create following directories

CREATE OR REPLACE DIRECTORY SOURCE_DIR AS '+DG1/DBNAME/ONLINELOG';
CREATE OR REPLACE DIRECTORY DEST_DIR AS '/..filesystem_path../ONLINELOG';

3-) Copy asm file to filesystem

BEGIN
  dbms_file_transfer.copy_file(source_directory_object =>
  'SOURCE_DIR', source_file_name => 'filename',
  destination_directory_object => 'DEST_DIR',
  destination_file_name => 'filename');
END;
/

4-) Copy file on filesystem back to asm

CREATE OR REPLACE DIRECTORY SOURCE_DIR AS '/..filesystem_path../ONLINELOG';
CREATE OR REPLACE DIRECTORY DEST_DIR AS '+DG2/DBNAME/ONLINELOG';
BEGIN
  dbms_file_transfer.copy_file(source_directory_object =>
  'SOURCE_DIR', source_file_name => 'filename',
  destination_directory_object => 'DEST_DIR',
  destination_file_name => 'new_filename');
END;
/

AIX: ASM Metadata & DISKS

lspv: command used to get disk list (disk, assigned PVID, vg, status)


$lspv
hdisk0          00c2e2503cddd971                    rootvg          active
hdisk9          00c2e2508b71c3a9                    sw_vg           active
hdisk1          none                                None          
hdisk2          none                                None          
hdisk3          none                                None          
...

ASM disks should not have PVID assigned to them (Configuring Storage for Grid Infrastructure, 3.3.3 Configuring Disk Devices for Oracle ASM, step 7). They should be removed with fallowing command before assigned to ASM:

$/usr/sbin/chdev -l hdiskn -a pv=clear

Note: If you already assigned disks without clearing PVID, don't clear it using above command. It corrupts asm disks while clearing header. Because it is the same header where asm keeps its internal info. Actually ASM clears header and PVID and writes its own header when a disk assigned to a datagroup. However because OS odm (aix's internal database) is not updated, you can see PVIDs using lspv although it is not there.

By using following command you can query disk header in order to see ASM internal information written on disk (requires root permission)


$lquerypv -h /dev/rhdisk5
00000000 00820101 00000000 80000000 81AA935B |...............[|
00000010 00000000 00000000 00000000 00000000 |................|
00000020 4F52434C 4449534B 00000000 00000000 |ORCLDISK........|
00000030 00000000 00000000 00000000 00000000 |................|
00000040 0A100000 00000103 54455354 5F303030 |........TEST_000|
00000050 30000000 00000000 00000000 00000000 |0...............|
00000060 00000000 00000000 54455354 00000000 |........TEST....|
00000070 00000000 00000000 00000000 00000000 |................|
00000080 00000000 00000000 54455354 5F303030 |........TEST_000|


In 11g you can backup and recover corrupted disk headers using md_backup and md_restore. The corrupted diskgroup must be specified with the ‘-g’ flag for the restore to complete
successfully:

ASMCMD> md_restore -b /oracle/backup/asm_metadata020409.bkp -g 'TEST'

Ref:
11gRAC_ASM_1.pdf (doc link)

How to Drop ASM Datagroup while not mounted and disks corrupted

In a test environment we accidentally corrupt asm disks and reset their headers. When I query V$ASM_DISK view, I saw that disks were discovered as "CANDIDATE". It was like newly added disks available for ASM. The problem was, my datagroup which was using these disks, was exists but i could not drop it. That was because a diskgroup has to be mounted in order to be dropped, but its disks were corrupted so i could not mount it:

SQL> drop diskgroup DATA;
drop diskgroup DATA
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15001: diskgroup "DATA" does not exist or is not mounted


SQL> ALTER DISKGROUP DATA mount;
ALTER DISKGROUP DATA mount
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15017: diskgroup "DATA" cannot be mounted
ORA-15063: ASM discovered an insufficient number of disks for diskgroup "DATA"

How can i drop/delete datagroup which i can not mount?

Solution is to create a new datagroup, add all disks to new datagroup. At the end, old datagroup disappears... Then you may drop newly added diskgroup.

create diskgroup DATA_DROP external redundancy disk '/dev/rhdisk#' force;
alter diskgroup DATA_DROP add disk '/dev/rhdisk#';
...
DATA disappears when i add all its disks to DATA_DROP...
...
drop diskgroup DATA_DROP including contents;

now, DATA diskgroup has gone and all its disks are in "FORMER" status.

How to get process path on linux

$ps -eaf | grep pmon


oracle 24771 1 0 Aug14 ? 00:02:59 ora_pmon_INST1
oracle 28486 1 0 Sep05 ? 00:04:00 ora_pmon_INST2

$readlink -f /proc/24771/exe
/.../bin/oracle
$readlink -f /proc/28486/exe
/.../bin/oracle