Recover Database after loosing FRA diskgroup



Sometime back ,I  hit a problem where FLASH diskgroup of my ASM cluster went bad and it was set  to external  redundancy . My all database went down and it created lot of panic.

I had to go through the below steps to Recover Database after loosing FRA diskgroup

Let’s first see what all things are present in FLASH or FRA

  1. Second Control-file
  2. Multiplex redo log files
  3. Archive log files
  4. Backups
  5. Flashback logs
  6. Change tracking files

So we lost all of them and all the database on the ASM crashed with the following message

ORA-15080: synchronous I/O operation to a disk failed
ORA-27061: waiting for async I/Os failed
Linux-x86_64 Error: 5: Input/output error
Additional information: -1
Additional information: 16384
WARNING: failed to write mirror side 1 of virtual extent 0 logical extent 0 of file 321 in group 2 on disk 1 allocation unit 45746
ORA-00206: error in writing (block 4, # blocks 1) of control file
ORA-00202: control file: ‘+FLASH/test/controlfile/current.321.852654927’
ORA-15081: failed to submit an I/O operation to a disk
ORA-15081: failed to submit an I/O operation to a disk
Errors in file /u01/app/oracle/diag/rdbms/test/test/trace/test_ckpt_6406.trc:
ORA-00221: error on write to control file
ORA-00206: error in writing (block 4, # blocks 1) of control file
ORA-00202: control file: ‘+FLASH/test/controlfile/current.321.852654927’
ORA-15081: failed to submit an I/O operation to a disk
ORA-15081: failed to submit an I/O operation to a disk
Sat Jul 10 09:52:20 2014
System state dump requested by (instance=1, osid=6406 (CKPT)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/test/test/trace/test_diag_6378_20140712095220.trc
CKPT (ospid: 106): terminating the instance due to error 221

 

When we tried to start the database

SQL> startup mount
ORACLE instance started.

Total System Global Area 8756176640 bytes
Fixed Size 1253024 bytes
Variable Size 469765920 bytes
Database Buffers 1255638016 bytes
Redo Buffers 8119680 bytes
ORA-00205: error in identifying control file, check alert log for more info

It was found A disk in diskgroup FLASH failed. The diskgroup was automatically dismounted by the ASM instance.

Now When the  disk was fixed, and we tried to add it to the diskgroup again and remount diskgroup FLASH.

Using the command is failing with:

alter diskgroup flash mount;
fails with :
ORA-15032: not all alterations performed
ORA-15040: diskgroup is incomplete
ORA-15042: ASM disk “1” is missing

But it fails as the data has been wiped out. Since Disk group was set to external redundancy and we had no external mirror , we had to recreate the disk group and perform lot of steps on the database

  1. Re-create the diskgroup (you can use the same device names) with the FORCE option and mount it

Connect against first ASM instance and execute:
SQL> create diskgroup FLASH EXTERNAL REDUNDANCY
disk ‘/dev/rdsk/dev1’ size 129420M FORCE,
disk ‘/dev/rdsk/dev2’ size 129420M FORCE;

2)  Restore the second controlfile in the FLASH diskgroup

Create directory in FLASH diskgroup

export ORACLE_SID=+ASM1
asmcmd
ASMCMD> cd FLASH
ASMCMD> mkdir TEST
ASMCMD> cd TEST
ASMCMD> mkdir controlfile

Restore Control file from DATA diskgroup

export ORACLE_SID=TEST

sqlplus “/ as sysdba

startup nomount

show parameter controlfiles

Note down the controlfiles names and the restore them as per given example
rman target /
rman> restore controlfile to ‘+FLASH’ from
‘+DATA/TEST/controlfile/current.264.592570321′;

Check the name controlfile created  and then reset  the control file parameter
– change name of second controlfile in SPFILE
SQL> alter system set control_files=’+DATA/TEST/controlfile/current.264.592570321’,
‘+FLASH/TEST/controlfile/backup.256.598115913′ scope=spfile sid=’*’;

Now database mount should succeed

sqlplus / as sysdba

startup  mount

3) Opening the database will still fail of either change tracking is enabled or flashback is enabled

failed with ORA-38760, because Flashback Database is enabled.

ORA-19751: could not create the change tracking file

So disable them , we can enable them after opening the database

sqlplus “/ as sysdba”

ALTER DATABASE DISABLE BLOCK CHANGE TRACKING;

alter database flashback off;

Now open the database

sqlplus “/ as sysdba”

alter database open;

4)  Last step is to recreate 2nd Members of Online Logfiles:
SQL> select member from v$logfile;
-> Lists all members. All which are in FLASH needs to be dropped and added back in.
SQL> alter database drop logfile member ‘+FLASH/TEST/onlinelog/group_1.273.598118813’;
SQL> alter database add logfile member ‘+FLASH’ to group 1;
Verified that OMF has been generated as ‘+FLASH/rptest/onlinelog/group_1.276.598119425’.
-> Repeat this for all groups.

So we have restored the controlfiles, open the database and recreate the redo logs.

Now we are good to enable flashback logging and block tracking.

Hope you  like this post on Recover Database after loosing FRA diskgroup

Related Articles

Oracle Database Recovery various cases and solution

How to recover database using RMAN

How to create recovery catalog and register database in catalog

RMAN DUPLICATE DATABASE CLONING WITH ACTIVE DATABASE

RMAN DUPLICATE DATABASE CLONING


Leave a Reply