Monday, December 13, 2010

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
POSTED BY Oracle Log TIME: 4:31 AM 1 REPLY
KEY WORLDS:

Monday, December 6, 2010

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
POSTED BY Oracle Log TIME: 4:55 AM 0 REPLY

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
POSTED BY Oracle Log TIME: 1:20 AM 2 REPLY
KEY WORLDS: ,

Friday, December 3, 2010

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;
/
POSTED BY Oracle Log TIME: 3:54 AM 0 REPLY
Subscribe to: Posts (Atom)