Recovery from Loss of a Multiplexed Controlfile in Software

Draw Code 39 Full ASCII in Software Recovery from Loss of a Multiplexed Controlfile

As long as a surviving multiplexed copy of the controlfile exists, recovery from loss of a controlfile is simple Just replace it with a surviving copy of the controlfile To restore the damaged or missing controlfile copy from a backup would be useless in these circumstances, because all copies of the controlfile must be identical; clearly, a restored copy would not be synchronized with the surviving copies, nor with the rest of the database Virtually the moment the damage occurs, the instance will terminate As ever, the DBA s first reaction to a crashed instance should be to attempt a startup This will fail, in NOMOUNT mode, with an appropriate error message The alert log will state which controlfile copy is missing, and also in the section listing the nondefault initialization parameters how many controlfile copies there actually are, and where they are At this point, you have three options First, you could edit the parameter file to remove the reference to the missing or damaged controlfile copy, as shown in Figure 18-2

Figure 18-2

This is fine, but your database will now be running on one less multiplexed copy, which will presumably be in breach of your security guidelines A better option is therefore to replace the damaged file with a copy made from a surviving copy or indeed to change the CONTROL_FILES initialization parameter to replace the reference to the damaged file with a reference to a brand new file, which is a copy of one of the surviving controlfiles made when the instance is completely shut down EXAM TIP Recovering from loss of a controlfile will entail downtime It cannot be done online

Exercise 18-2: Recover from Loss of a Controlfile In this exercise, you will simulate the loss of a multiplexed controlfile and replace it with a copy 1 Connect to your database with SQL*Plus, and ensure that your controlfile is multiplexed with this query:

SQL> select * from v$controlfile;

This query must return at least two rows If it does not, multiplex your controlfile by following the instructions given in 14, illustrated in Figure 14-4 2 Simulate damage to a controlfile by crashing the database (shutdown abort) and renaming one of your controlfiles Note that on Windows you may have to stop the Windows service before Windows will let you rename the file, and start it again afterward 3 Issue a startup command The startup will stop in nomount mode, with an ORA-00205: error in identifying controlfile, check alert log for more info error message 4 Copy your surviving controlfile to the name and location of the file you renamed 5 Issue another startup command, which will be successful TIP Many DBAs do not like to copy a surviving controlfile over a damaged one, because it is all too easy to copy accidentally the damaged controlfile over the surviving one Do not laugh at this It is safer to copy the surviving controlfile to a new file, and edit the CONTROL_FILES parameter to change the reference to the damaged file to the new file

Provided that the online redo log files are multiplexed, loss of one member will not cause any downtime, but there will be messages in the alert log telling you that there is a problem If you can tolerate the downtime, you can shut down the database and copy a surviving member of the group over the damaged or missing member, but clearly this is not an option if the database must remain open For open recovery, use the ALTER DATABASE CLEAR LOGFILE command to delete the existing files (or at least, those that still exist) and create new ones This can be done only if the log file is inactive If you attempt to clear the current log file group, or the previous one that is still active, you will receive an error (as shown in Figure 18-3) Furthermore, if the database is in archivelog mode, the log file group must have been archived TIP Recovery from loss of a multiplexed online redo log file can be done while the database is open, and therefore does not entail any downtime