Page tree
Skip to end of metadata
Go to start of metadata

Restore an Oracle Server

Oracle databases can be restored to the original location or to an alternate location. Oracle restores can be highly technical and require access to the SQL*Plus utility that is included with the Oracle database.

Additional Requirements

Oracle restores have additional requirements:

  • SQL*Plus must be installed and accessible to the user performing the restore.
  • The Oracle VSS Writer service must be started and functional.

Recovery of ARCHIVELOG databases to the Original Location

To restore all ARCHIVELOG databases to the original location:

  • Ensure that the instance is not started.
  • Navigate to the Restore page. Select the a Restore Point.
  • Select the database files to be restored. This includes the datafiles, server parameter file, etc.
    • If the redo logs are missing, you must also select the appropriate archived redo log files.
  • Select Restore to Original Location from the dropdown menu.
    • ZCB will restore the data to the same location from where the backup was made.
  • Choose Overwrite Original as your Name Conflict Policy.
  • Click Restore to start the restore process.
  • Once the database is successfully restored, open SQL*Plus and run the below commands.

cmd> sqlplus /nolog
sql> connect sys as sysdba
sql> shutdown immediate
sql> startup mount
sql> recover database using backup controlfile until cancel;

Note: In cancel-based recovery, recovery proceeds by prompting you with the suggested filenames of archived redo log files. Recovery stops when you specify CANCEL instead of a filename, or when all redo logs have been applied to the datafiles.

  • Continue applying redo log files until the last log has been applied to the restored datafiles, then cancel recovery by executing the following command:

sql> CANCEL;

  • To finish, enter the following SQL command string

sql> alter database open resetlogs;

  • To verify that the database is in the open state (read/write mode), use the following command:

sql> select name, open_mode from v$database;

To restore a single Oracle datafile to the Original location:

Ensure that the database is either mounted or open. If the database is open, run SQL*Plus from the Command Prompt and connect to the database.

cmd> sqlplus sys as sysdba

To recover the datafile, the tablespace needs to be taken offline. Lets consider an example where the tablespace ZTEST01 needs to be restored.

sql> ALTER TABLESPACE ZTEST01 OFFLINE IMMEDIATE;

  • Navigate to the Restore page in ZCB. Select the backup run you want to restore from.
  • Select the datafile to be restored
    • Our example will use a datafile named ZTEST01.dbf.
    • If the redo logs are missing, also select the appropriate archived redo log files.
  • Select Restore to Original Location from the dropdown menu.

    • ZCB will restore the data to the same location from where the backup was made.
  • Choose Overwrite Original as your Name Conflict Policy.
  • Click on Restore.
  • Once the database is successfully restored, open the SQL*Plus and run the below commands.

cmd> sqlplus /nolog
sql> connect sys as sysdba

  • If the restored data file is older than the redo log files, then a media recovery needs to be initiated using the below command:

sql> RECOVER TABLESPACE ZTEST01;
You will see the below message
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

  • Input the appropriate log file location. Type 'AUTO' if you want Oracle to automatically find the appropriate logs and apply it.

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
AUTO
Log applied.
Media recovery complete.

  • Once you have recovered all the tablespaces that you have lost, bring the offline datafile or tablespace back online using command:

sql> ALTER TABLESPACE ZTEST01 ONLINE;

To restore all ARCHIVELOG Control files:

Note: When Oracle database control files are backed up by Volume Shadow Copy Service, the backed-up files are referred to as Snapshot Control files. For example, a control file by the name of 'CONTROL01.CTL' is backed up under the name SNCF[DATABASE_SID].ORA. Let's consider an example where the database SID is ORCL. In this case the snapshot control file will have the name SNCFORCL.ORA.

  • Ensure that the database is in NOMOUNT state or can be started in NOMOUNT state by the Oracle VSS writer. You don't have to shut down the database manually.
  • Navigate to the Restore page on ZCB user interface. Select the backup run you want to restore from.
  • Select the snapshot Control File. In the above case, select 'SNCFORCL.ORA' since the SID of the database is ORCL.
  • Select Restore to Original Location from the dropdown menu.
    • ZCB will restore the data to the same location from where the backup was made.
  • Choose Overwrite Original as your Name Conflict Policy.
  • Click on Restore.

Note: ZCB automatically stops the Oracle instance while restoring and then starts it again when the restore operation is finished.

  • Make three copies of the SNCFDATABASE_SID.ORA called CONTROL01.CTL, CONTROL02.CTL, and CONTROL03.CTL.
  • Copy these three files to the control files install location. The default location is 'ORAHOME\oradata\(DATABASE_SID)\'. For the above example, copy the files to the 'C:\app\Administrator\oradata\ORCL\' directory.
  • Run sqlplus on Command Prompt Window

cmd> sqlplus sys as sysdba

  • Shut down the database and startup

sql> SHUTDOWN IMMEDIATE
sql> STARTUP MOUNT

  • Run the below command to get the database in consistent state.

sql> RECOVER database using backup controlfile until cancel;

  • Continue applying redo log files until the last log has been applied to the restored datafiles, then cancel recovery by executing the following command:

sql> CANCEL;

  • Open the database with the RESETLOGS option.

SQL> ALTER DATABASE OPEN RESETLOGS;

  • The Oracle database is now recovered. To verify that the database is in the open state (read/write mode), use the following command:

SQL> select name, open_mode from v$database;

Recovery of the server parameter file

  • Navigate to the Restore page in the ZCB. Select the backup run you want to restore.
  • Select the Server parameter file (spfile) to be restored.
    • Usually, it is named SPFILE[ORACLE_SID]".ORA .
      • For example, if the SID of the database is "ORCL", then the name of spfile will be "SPFILEORCL.ORA".
  • Select Restore to Original Location from the dropdown menu.
    • ZCB will restore the data to the same location from where the backup was made.
  • Choose Overwrite Original as your Name Conflict Policy.
  • Click on Restore.
  • Once the restore operation is done, run SQL*Plus

cmd> sqlplus sys as sysdba

  • Shutdown the database and startup again.

sql> SHUTDOWN immediate
sql> STARTUP

  • Check the database whether it's up and running

sql> SELECT name, open_mode from v$database;

Recovery of NOARCHIVELOG databases to the Original Location

To restore all NOARCHIVELOG datafiles

  • Navigate to the Restore page in the ZCB user interface. Select the backup run you want to restore from.
  • Select all the datafiles & control files. ZCB backs up one of the 3 control files i.e. CONTROL01.CTL.
  • Select Restore to Original Location from the dropdown menu.
    • ZCB will restore the data to the same location from where the backup was made.
  • Choose Overwrite Original as your Name Conflict Policy.
  • Click on Restore.
  • Make two extra copies of the CONTROL01.CTL file called CONTROL02.CTL and CONTROL03.CTL. For your information, CONTROL01.CTL file is located in the ORAHOME\ORADATA\DATABASE_SID directory.
  • To run the recovery, run sqlplus on command prompt:

cmd> sqlplus /nolog
sql> connect sys as sysdba
sql> shutdown immediate
sql> startup nomount
sql> ALTER DATABASE mount;
sql> RECOVER DATABASE;
sql> ALTER DATABASE open;

Recovery to an Alternate Location

When Restore to Alternate Location is chosen, ZCB will simply restore the selected Oracle files to disk. They must be imported manually into the Oracle database.

 

  • No labels