Recover Database after losing FRA diskgroup

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

I had to go through the below steps to Recover Database after losing FRA asm 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 databases 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 oracle 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 the Disk group was set to external redundancy and we had no external mirror, we had to recreate the disk group and perform a 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 control file in the FLASH diskgroup

Create a 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 restore them as per the given example

rman target /
rman> restore controlfile to '+FLASH' from
'+DATA/TEST/controlfile/current.264.592570321';

Check the name control file created  and then reset  the control file parameter
– change the name of the second control file 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 if either change tracking is enabled or flashback is enabled. It will fail with ORA-38760 of Flashback Database is enabled.

ORA-19751: could not create the change tracking file

To 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 Log files:

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 control files, 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 losing 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