Recovering Oracle DB from Online Backup
Below are the detailed steps to be performed for recovering a DB from a Online backup of another oracle system.
Below are the assumptions:
- You are using online backup of the Source system.
- Source and target system have the similar /oracle/<SID>/sapdata<n> structure
- The host is a UNIX server
- Restore of target oracle sapdata file system was triggered with the source oracle backup and all the target file
system and files have proper owner , group and permission.
5. Source and Target oracle software version is same
1.Take the control file backup of the Source System using below command:
ALTER DATABASE BACKUP CONTROLFILE TO TRACE AS ‘<location>/control_<SID>.trc’ ;.
(Name of this file can be anything with .trc as extension
Transfer this file to the target system.
2. Login to target system with ora<sid> user and open the transferred control_<SID>.trc.
3. Edit this file as per below steps
a. Replace all the source entries in this file with target system id .
b. Delete all the lines till “CREATE CONTROLFILE ..” including the line ‘STARTUP NOMOUNT ‘
c. Change the first line “CREATE CONTROLFILE” as below .
– The word “REUSE” should be replace with word “SET”
– The word “NORESETLOGS” should be replaced with the word “RESETLOGS”
d. Scroll down till you find a semicolon as shown below.
e. Don’t forget to delete any entries which is like “–STANDBY LOGFILE” as below which you
might find in between
CHARACTER SET UTF8
f. Delete all the entries that you find after the semicolon till the end .
g. Make note of any entries that lies in between the sentences
“— Other tempfiles may require adjustment.” and “— End of tempfile additions.” as below .
Note these entries should also be deleted
h. Now our control file is ready and save that control file as <name>.sql with owner as ora<sid>
4. Ensure that all the control file folders are in place. Ie, there should be a folder named “cntrl” in all the location
specified by the parameter *.control_files in init<SID>.ora inside $ORACLE/<SID>/dbs location
5. Goto the location where the previously specified <name>.sql file is saved .
Login to sqlplus and start the database in nomount state with the below command .(should have logged in as ora<sid> user )
6. Now execute the command @<name>.sql as shown below.
If there are no permission issues and all the expected folder structure are in place, then this creates the file named
cntrl.dbf in all the location specified in step number 4 without any issues after which the database turns to mount state.
7. Recovering the database.
A. Execute the command
SQL> RECOVER DATABASE USING BACKUP CONTROLFILE;
Now database asks for the sequence of oraarch files which is required for its recovery. It asks for input with various
options eg:AUTO etc.
So please ensure that the oraarch file system of target system has all the sequences of *.dbf files ,starting from
the sequence the system is asking for , till the last sequence that was generated in the source system , half an hour
after the online backup got completed in source system .(Only this backup has been restored in the target file system).
Once this sequence is placed in the target oraarch file system (/oracle/<Target SID>/oraarch, ensure to rename all
these *.dbf files as shown below .
Eg: mv <Source ID>arch1_219297_643929145.dbf <Target ID>arch1_219297_643929145.dbf
B.Type AUTO ( as a response to the command specified in step 1 above)
This option allows db to read the required oraarch file sequence from the oraarch directory itself ,till it reads the last file .
After this last file , the db comes out saying that the XVF_yff.dbf file is missing .
C. Now execute the below command .
SQL>RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL; .
And type CANCEL when system asks for the option as shown below and the system should say
“Media Recovery cancelled”
D. Now open the database as it is consistent with the command
SQL>alter database open resetlogs;
E. Create Temporary table space with the help of the command that we made note in step 3.f which starts as “ALTER TABLESPACE PSAPTEMP ADD TEMPFILE….”
Thus we have recovered the Oracle DB.
Do you really think that you document is something related to SCN Support forum ??
Thanks for pointing out . I have moved it to SAP on Oracle Solaris .
Rajarajeshwari... this is indeed a beaultiful document. Clearly understood the mechanism. But then I've some questions which I'll fire shortly.