Recovering Oracle DB from Online Backup

[Former Member]

Below are the detailed steps to be performed for recovering a DB from a Online backup of another oracle system.

Below are the assumptions:

1. You are using online backup of the Source system.
2. Source and target system have the similar /oracle/<SID>/sapdata<n> structure
3. The host is a UNIX server
4. 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

Steps:

     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.

          vi 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

                    "CREATE CONTROLFILE....

                          ........

                     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;

Database opened.

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.