Restore Oracle Database

This article will guide you through how to restore your Oracle Database backups. Choose the restoration method that best fits your needs.

Restore an entire database

You can restore an entire Oracle Database to a specific point on the original server using backed-up files.

1. Navigate to Machines > Virtual Machines or Physical Servers and select the machine you want to restore.
2. Click the More menu on the upper bar and select Open Recovery Portal.
3. In the Recovery Portal, click on the target version to access data.
4. On the Databases tab, select Oracle Database from the menu in the upper-left corner.
5. Select the instance you want to restore from the left panel. Click Download All to get the database files.
6. Launch SQL*Plus and connect to the database with SYSDBA privilege. Enter the following command, replacing {db_identifier} with the System Identifier of the database:
   set ORACLE_SID={db_identifier}
SQLPLUS / AS SYSDBA
7. Shut down the database:
   SHUTDOWN IMMEDIATE;
8. Open the database in NOMOUNT mode:
   startup nomount;
9. Use a text editor and locate the SPFILE{db_name}.ora file in the $ORACLE_HOME/database folder of the backup (e.g., SPFILERDBINSTANCE.ORA). This file contains control file path information. Copy these files from the backup to your machine.
10. Move the control files to the corresponding location:
    • Check the current location using this command:
      SELECT value FROM v$parameter WHERE name = 'control_files';
    • To avoid overwriting the original data, change the file location using this command:
      ALTER SYSTEM SET CONTROL_FILES='{first_path}', -
'{second_path}', -
…
'{nth_path}', -
…
'{last_path}' SCOPE=SPFILE;

      Example:

      ALTER SYSTEM SET CONTROL_FILES=
'G:\HAMMER2\CONTROLFILE\O1_MF_L0OSN515_.CTL', -
'G:\FAST_RECOVERY_AREA\HAMMER2\CONTROLFILE\O1_MF_L0OSN51O_.CTL'
SCOPE=SPFILE;
11. Mount the database:
    ALTER DATABASE MOUNT;
12. Check which datafiles and logfiles need restoration:
    SELECT Name FROM v$datafile;
SELECT Member FROM v$logfile;

    Example:

    NAME
--------------------------------------------------------------------------------
G:\HAMMER2\DATAFILE\O1_MF_SYSTEM_L0OSFKM0_.DBF
G:\HAMMER2\DATAFILE\O1_MF_SYSAUX_L0OSGBVW_.DBF
G:\HAMMER2\DATAFILE\O1_MF_UNDOTBS1_L0OSGT4Z_.DBF
G:\BIGFILE
G:\HAMMER2\DATAFILE\O1_MF_USERS_L0OSGVDF_.DBF
MEMBER
--------------------------------------------------------------------------------
G:\HAMMER2\ONLINELOG\O1_MF_3_L0OSNBC0_.LOG
G:\FAST_RECOVERY_AREA\HAMMER2\ONLINELOG\O1_MF_3_L0OSNFRO_.LOG
G:\HAMMER2\ONLINELOG\O1_MF_2_L0OSNB9K_.LOG
G:\FAST_RECOVERY_AREA\HAMMER2\ONLINELOG\O1_MF_2_L0OSNF6M_.LOG
G:\HAMMER2\ONLINELOG\O1_MF_1_L0OSNB9K_.LOG
G:\FAST_RECOVERY_AREA\HAMMER2\ONLINELOG\O1_MF_1_L0OSNF45_.LOG
13. Place the backed-up datafiles and logfiles in the corresponding locations. If needed, modify their file paths in database:
    ALTER DATABASE RENAME FILE '<old location/old file name>'
TO '<new location/new file name>';

    Example:

    ALTER DATABASE RENAME FILE 'G:\HAMMER2\DATAFILE\O1_MF_SYSTEM_L0OSFKM0_.DBF'
TO 'C:\User\Desktop\Oracle\O1_MF_SYSTEM_L0OSFKM0_.DBF';
14. Modify the file permissions if they are incorrect. Enter the following command, replacing "ORA_OraDB19Home1_SVCACCTS:(F)" with your Oracle group name:
    icacls "{new_path_file}" /grant ORA_OraDB19Home1_SVCACCTS:(F)
15. Restore the database from backup mode:
    RECOVER DATABASE;
16. Lastly, switch the database to open mode:
    ALTER DATABASE OPEN;

Restore to virtual machine

If you need to quickly recover specific tables or critical data from your Oracle Database backups, instant restore to VMware/Hyper-V or the built-in hypervisor is an ideal solution. This feature runs a backup image as a virtual machine, giving you easy access to locate and export the necessary data or tables.

Resume operations after restoration

After any of the following restoration processes, the database will remain in backup mode and isn't available for regular use:

• Instant restore to virtual machines
• Full restore to virtual machines
• Bare-metal restore of physical servers

To switch the database to open mode after restoration, follow these steps:

1. Connect to the Oracle Database server using SQL*Plus as a user with SYSDBA privilege.
   set ORACLE_SID={db_identifier}
SQLPLUS / AS SYSDBA
2. Run the following commands:
   RECOVER DATABASE;
ALTER DATABASE OPEN;
3. Enter the following command to check if the database is in backup mode:
   SELECT * FROM v$backup;

   Here is an output example of a device during an instant restore. ACTIVE indicates that the file is in backup mode, NOT ACTIVE indicates that the file is in a normal state.

   FILE# STATUS CHANGE# TIME CON_ID
---------- ------------------ ---------- --------- ----------
1 ACTIVE 4229743 05-MAY-23 0
3 ACTIVE 4229751 05-MAY-23 0
4 ACTIVE 4229759 05-MAY-23 0
5 ACTIVE 4229775 05-MAY-23 0
7 ACTIVE 4229767 05-MAY-23 0