Recover database after disk loss

Control Files

Normally, we have multiplexing of controlfiles and they are expected to be placed in different disks.

If one or more controlfile is/are lost,mount will fail as shown below:

....

ORA-00205: error in identifying controlfile, check alert log for more info

 

**If at least one copy of the controlfile is not affected by the disk failure, 

   When the database is shutdown cleanly:

   (a) Copy a good copy of the controlfile to the missing location

   (b) Start the database 

   Alternatively, remove the lost control file location specified in the nit parameter control_files and start the database.

   **If all copies of the controlfile are lost due to the disk failure, then: Check for a backup controlfile. Backup controlfile is normally taken using either of the following commands:

   (a) SQL> alter database backup controlfile to '/backup/control.ctl';

    -- This would have created a binary backup of the current controlfile --

    -->If the backup was done in binary format as mentioned above, restore the file to the lost controlfile locations using OS copying utilities.

    --> SQL> startup mount;

    --> SQL> recover database using backup controlfile;

    --> SQL> alter database open;

   (b) SQL> alter database backup controlfile to trace;

    -- This would have created a readable trace file containing create controlfile script --

    --> Edit the trace file created (check user_dump_dest for the location) and retain the SQL commands alone. Save this to a file say cr_ctrl.sql

è      Run the script

Redo logs

In normal cases, we would not have backups of online redo log files. But the inactive logfile changes could already have been checkpointed on the datafiles and even archive log files may be available.

SQL> 

     ORA-00313: open failed for members of log group 1 of thread 1

     ORA-00312: online log 1 thread 1: '/ORACLE/ORADATA/H817/REDO01.LOG'

     ORA-27041: unable to open file

     OSD-04002: unable to open file

     O/S-Error: (OS 2) The system cannot find the file specified.

** Verify if the lost redolog file is Current or not.

     SQL> select * from v$log;

     SQL> select * from v$logfile; 

     --> If the lost redo log is an Inactive logfile, you can clear the logfile:

     SQL> alter database clear logfile GROUP 1;

     Alternatively, you can drop the logfile if you have atleast two other logfiles:

     SQL> alter database drop logfile group 1;

     --> If the logfile is the Current logfile, then do the following:

     SQL> recover database until cancel;

     SQL>alter database open resetlogs;

If the database is in noarchivelog mode and if ORA-1547, ORA-1194 and ORA-1110 errors occur, then you would have restore from an old backup and start the database.

Note that all redo log maintenance operations are done in the database mount state

Parameter file

This is not a major loss and can be easily restored. Options are:

1.       If there is a backup, restore the file

2.       If there is no backup, copy sample file or create a new file and add the required parameters. Ensure that the parameters db_name, control_files,vdb_block_size, compatible are set correctly

3.    If the spfile is lost, you can create it from the init parameter file