This usually happens when the Primary Disk is damaged
Then to recover the database from this kind of failure we need to do disaster recovery or full database recovery
Assumption
◉ All datafiles and logfiles are lost
◉ The database was running in Archivelog mode
◉ Control file and SPFILE is also lost
◉ RMAN backups of the database is available
◉ We were not using RMAN with recovery catalog
To restore and recover the database do the following
Step 1
If you have lost the primary disk where O/s and Oracle was installed. Then, install a new disk and format and install the same operating system and same version of Oracle database software which was install before failure
Step 2
Make sure RMAN backups are available and also make sure archive log files of this database are also accessible
Start RMAN by typing the following command
$ export ORACLE_SID=test
$ rman target / nocatalog
Now the most important thing we need to do now is to set the database ID (DBID) at RMAN prompt. The DBID uniquely identifies the database.
If you have already noted down the DBID of the database then you can straight forward type the DBID at the RMAN prompt.
Otherwise if you have not noted down the DBID then the other ways you can get the DBIDx is
◉ If you have turned ON the autobackup of Controlfile and configured the AUTOBACKUP filename to contain the DBID then you can look into the directory where Controlfile AUTOBACKUP files are located. Usually the directory set with the path DB_RECOVERY_FILE_DEST parameter. The control files auto backup files will have the DBID in the format c-IIIIIIIIII-YYYYMMDD-QQ
Where
◉ IIIIIIIIII stands for the DBID.
◉ YYYYMMDD is a time stamp of the day the backup is generated.
◉ QQ is the hex sequence that starts with 00 and has a maximum of FF.
◉ If you have ever recorded the output of RMAN session previously then you can look into that output text file. The RMAN will always show the DBID when it is connected to the database as shown below
Once you got the DBID then set it by typing the following command
RMAN> set dbid=2195428530
Step 3
Since we don't even have SPFILE, we need to start the instance with default parameters
Start database instance with default parameters by typing the following command at RMAN prompt
RMAN> startup force nomount;
Step 4
Now restore SPFILE from autobackup by typing the following command
RMAN>restore spfile from autobackup;
When we give the above command RMAN will fail with the following output
To restore SPFILE we need to give the location of the autobackup file. You can view the location and name of the most recent autobackup files by looking into the flash_recovery_area/autobackup folder
In our case flash_recovery_area is set to /u02/oracle/testdb/flash/TEST
After going into this directory, go to autobackup directory and search for the latest autobackup file.
In our case the latest autobackup file is o1_mf_s_926930278_d1pq2srv_.bkp
Now we need to restore SPFILE from this file by typing the following command
RMAN> restore spfile from
'/u02/oracle/testdb/flash/TEST/autobackup/2016_11_03/o1_mf_s_926930278_d1pq2srv_.bkp'
Step 5
Now shutdown the instance as we have started with the default parameters and restart the instance with the just restored SPFILE
RMAN> shutdown
RMAN> exit
RMAN> startup nomount;
Step 6
Now restore Control file from autobackup by typing the following command
RMAN> restore controlfile from autobackup;
After restoring the controlfile we can now mount the database. To mount the database, type the following command
RMAN> alter database mount;
Step 7
We have now restored the SPFILE and CONTROLFILE and started and mounted the database. Next thing we need to do is to restore the datafiles.
To restore datafile give the following command
RMAN> restore database;
When we give the above command RMAN will take some time to restore all the files.
Step 8
Once the files are restored we now need to recover database by applying the transactions from archive log files
To perform recovery type the following command
RMAN> recover database;
When we give the recover database command RMAN will apply transaction from the archive log files and after applying the transaction from archive files it will try to apply transaction from the current log file. Since we have lost the current log file it will give an error as shown in the picture below.
At this point we can either open the database with RESETLOGS and we will lose the most recent transactions stored in the current log file
or
If you have at least one copy of the current log file (from a mirror location) then we can apply the transactions from this file so that we do not lose a singe transaction and database can be completely recovered
Here in our case we don't have a copy of current logfile so we will just open the database by resetting the log files
To open the database by resetting the logfiles give the following command
RMAN> alter database open resetlogs;
This finishes our disaster recovery. You can now go to SQL Plus and see the contents of the database.
0 comments:
Post a Comment