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
- Second Control-file
- Multiplex redo log files
- Archive log files
- Backups
- Flashback logs
- 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