Restore an Oracle database

Restore a database using the management console

Restoring to the original database replaces the original production application data with the specified point-in-time image. This restoration results in the loss of all current application data as the application is restored to its status at the point-in-time when the image was created. This operation cannot be undone. The amount of time required to complete a restore operation depends on the amount of data involved. You can also restore a database to another target.

Use these instructions to restore an Oracle database using RMAN automatically from the management console, overwriting the original production database.

  1. Open the management console to the App Manager and enter the database application name or use the filters to find the database image that you need.

  2. Right-click the application and select Access.

  3. On the Access page, select an image and click Restore under the Mount menu.

  4. On the Restore page, select Traditional (not Mount and Migrate).

  5. Use the Restore Range slider to a select a specific point in time to restore the database. Slide the slider tool all the way to the left to restore only the database with no logs applied.

  6. At Username enter the Oracle software owner on the selected target server.

  7. For Number of Channels, specify the number of RMAN channels to configure parallelism for database restore.

  8. Click Submit.

  9. A warning dialog appears. Read it and enter DATA LOSS to confirm.

  10. The job is queued for the next available job slot. You can view progress from the Jobs Monitor.

Restore a database to any target using the management console

Use these instructions to restore an Oracle database to a new target, using RMAN from the management console.

(Preview)

  1. From the App Manager > Applications list, right-click the database and select Access.
  2. Select the backup image to recover and choose Restore.

  3. On the Restore page, select Traditional.

    • Target: For all configurations, all eligible Oracle database servers are available to choose from the drop-down. Select the target server for the restore from the drop-down.
    • Replace Original Application identity: This option is only available when restoring to a new server on the same appliance where the backup was originally generated.
    • Yes: This replaces the original application and has the same application ID, job history, backup images, and backup plan as the original application.
    • No: This does not replace the original application. The new database will be discovered as a new application as part of the Restore job.
    • Rollforward time: Choose a date and time for a database protected with logs to recover to the required point in time.

    • Application Options:

      • DATABASESID: This is pre-populated with the protected database SID name and is immutable.
      • USERNAME: This is pre-populated with source database Oracle software owner. Update the USERNAME if the Oracle software owner on the selected target server is different.
      • ORAHOME: This is pre-populated with source database Oracle Home path. Update the Oracle Home directory if the Oracle home is different on the selected target server.
      • Advanced Options: This is pre-populated and displays all the database parameter settings (from pfile) at the source at backup time. Make sure the parameter value is correct for the target database server (source or new target) for restore.

  4. Mapping Options:

    • Target Database Type: Select the database target type (ASM diskgroup or file system) for the database from the drop-down list. File system target type is not supported for backup images in ASM format.

      Source Database Backup image format Restore Target
      ASM ASM ASM
      ASM File system ASM
      File system File system File system

    • Select target ASM diskgroup and file system mappings for three file types: the data files, the control files and the redo files for the database from the drop-down list.

  5. Click Pre-Flight Check. If the pre-flight check fails, fix the issue and run the pre-flight check again.

  6. Click Submit to submit the restore job.

Recover an Oracle database manually using RMAN

To manually recover an Oracle database, refer to these procedures.

Non-RAC, Non-ASM, File System

Recover an Oracle database with data file on file system and protected under file-based staging disk format

  1. Mount the image from the protected source database.

    In the Mount window, Mapping Options, provide a mount location for the image, for example: /acttestdb. For instructions on how to mount a database image, see Mount an Oracle database image protected Under a file system for data access.

    The database backup image is mounted at: /acttestdb

    The protected archive log is mounted at: /acttestdb_Log

  2. Set the Oracle environment and use sqlplus to shut down the database:

    sqlplus / as sysdba
sql> shutdown immediate;

    Verify the database is shut down. End any remaining processes for the database.

    ps -ef | grep <var>database sid</var>

  3. Start the database in nomount state using the backup parameter file from the mounted volume. The backup parameter file is under top mounted folder, for example /acttestdb

  4. Set the Oracle environment and use sqlplus to start the database:

    sqlplus / as sysdba
sql> startup nomount pfile='/acttestdb/<var>database_sid</var>___backup.ora';

  5. Create a new spfile from the existing pfile and restart the database.

    Create an spfile from the pfile:

    sql> create spfile='$ORACLE_HOME/dbs/spfile_<var>database sid</var>.ora' from pfile='/acttestdb/<var>database sid</var>___backup.ora';

    Restart the database with spfile in nomount state:

    sql> shutdown immediate;
sql> startup nomount;

  6. Restore the control file using RMAN from the Backup and DR mounted archive log image. Use the latest control file from Log mounted image, for example:

    /acttestdbLog/cf Dsid-id_id.ctl

    or if more than one log image:

    /acttestdb_Log1/cf-Dsid-id_id.ctl

    rman target /
rman> restore controlfile from '/acttestdb_Log_1/cf-D_<var>sid</var>-id_<var>id</var>.ctl' ;

  7. Mount the database:

    rman> alter database mount;

  8. Catalog the datafile and archivelog folder from Backup and DR mounted database image and archive log image to RMAN

    rman> run { catalog start with '/acttestdb/datafile' noprompt;
catalog start with '/acttestdb/archivelog' noprompt;
catalog start with '/acttestdb_Log' noprompt;}

  9. Restore and recover the database:

    rman> run { restore database ; recover database; }

    For a specific point in time recovery using the format yyyymmddhh24mi:

    rman> run
{
restore database;
recover database until time "to_date('<var>required timestamp</var>>','yyyymmddhh24mi')";
}

  10. Open the database with the reset log option.

    • For primary database recovery:

      rman> alter database open resetlogs;

    • For Data Guard standby database recovery, start the Data Guard managed recovery process:

      rman> alter database recover managed standby database nodelay disconnect;

    The database is available for read and write.

RAC or Standalone ASM, File System

Recover an Oracle database with data file on ASM disk group and protected under file-based staging disk format

  1. Mount the image from the protected source database.

    In the Mount window under Mapping Options, provide a mount location for the image, for example: /acttestdb. For instructions on how to mount a database image, see Mounting an Oracle Database Image Protected Under a File System for Data Access.

    The database backup image is mounted at the mount location: /acttestdb

    The protected archive log is mounted at:/acttestdb_Log

  2. Shut down the Oracle database. From node 1, su to Oracle OS user:

    su - oracle

    Set the Oracle environment and use srvctl to stop the database across all nodes:

    srvctl stop database -d <var>database_name</var>

    Verify the database is shutdown (all nodes). End any remaining processes for the database.

    ps -ef | grep <var>database_name</var>

  3. Start the database in no-mount state using the backup parameter file from the mounted volume. The backup parameter file is under the top mounted folder, for example at /acttestdb

  4. Set the Oracle environment and use sqlplus to start the database:

    sqlplus / as sysdba

sql> startup nomount pfile='/acttestdb/<var>database_name</var>___backup.ora';

  5. Create a new spfile and restart the database.

    To get the path of original spfile under disk group:

    cat $ORACLE_HOME/dbs/init{database sid}.ora

    For example: spfile=+{preferred disk group}/db_name/spfiledb_name.ora`

      sql> create spfile='+{preferred disk group}/{db
  name}/spfile<var>db_name</var>.ora' from pfile='/acttestdb/<var>db_name</var>___backup.ora';

    Restart the database with spfile in nomount state:

      sql> shutdown immediate;

  sql> startup nomount;

  6. Restore the control file using RMAN from the Backup and DR mounted archive log image. Use the latest control file from the Log mounted image, for example:
    /acttestdbLog/cf-Ddbname-iddb_id.ctl

    or if more than one log image:
    /acttestdb_Log1/cf-Ddbname-iddb_id.ctl

     rman target /

 rman> restore controlfile from '/acttestdb_Log_1/cf-D_<var>db_name</var>-id_<var>db_id</var>.ctl' ;

  7. Mount the database:

    rman> alter database mount;

  8. Catalog the datafile and archivelog folder from Backup and DR mounted database image and archive log image to RMAN:

     rman> run { catalog start with '/acttestdb/datafile' noprompt;
 catalog start with '/acttestdb/archivelog' noprompt;
 catalog start with '/acttestdb_Log' noprompt;}

  9. Restore and recover the database:

    rman> run { restore database ; recover database; }

    For a specific point in time recovery using the format yyyymmddhh24mi:

     rman> run
 {
 restore database;
 recover database until time "to_date('<var>timestamp</var>','yyyymmddhh24mi')";
 }

  10. Open the database with the reset log option.

    • For primary database recovery:

    rman> alter database open resetlogs;

    RAC config requires another step after reset log: Shut down the database on node 1 and start the database across all nodes.

    Use sqlplus to shut down the database:

     sqlplus / as sysdba

 SQL> shutdown immediate;

    Use srvctl to start the database across all nodes:

     srvctl start database -d <var>database_name</var>

    • For Data Guard standby database recovery:

      • For non-RAC config: Start the Data Guard managed recovery process

        RMAN> alter database recover managed standby database nodelay disconnect;

      • For RAC config: Shut down the database on node 1 and start the database in mounted state across all nodes.

        1. Use sqlplus to shut down the database:

          sqlplus / as sysdba
SQL> shutdown immediate;

        2. Use srvctl to start the database in a mount state across all nodes:

          srvctl start database -d <var>database_name</var> -o mount

        3. Start the Data Guard managed recovery process:

          rman> alter database recover managed standby database nodelay disconnect;

The database is available for read and write.

RAC or Standalone ASM, ASM Disk Group

Recover an Oracle database with data file on ASM disk group and protected under ASM disk group staging disk format

  1. Mount the image from the protected source database. In the Mount window, provide a preferred disk group for the image mount under ASM on RAC Node 1. For details on how to mount a database image, see Mounting an Oracle Database Image Protected Under an ASM Disk Group for Data Access.

    For example, on the mount screen:

    • Select Host: RAC node 1 database server

    • Preferred disk group: acttestdg

    • RAC node list: IP of RAC node 1

      The backup parameter file is copied under /act/touch/<var>preferred_disk_group</var>/, for example:

      /act/touch/acttestdg/<var>database_name</var>___backup.ora

  2. Shut down the Oracle database.

    From node 1, su to Oracle OS user:

     su - oracle

    Set the Oracle environment. Use srvctl to stop database across all nodes:

     srvctl stop database -d <var>database_name</var>

    Verify the database is shut down (on all nodes). End any remaining processes for the database.

    ps -ef | grep <var>db_name</var>

  3. Start the database in nomount state using the backup parameter file copied under /act/touch/acttestdg.

  4. Set the Oracle environment. Use sqlplus to start the database:

    ```
sqlplus / as sysdba

sql> startup nomount pfile='/act/touch/acttestdg/<var>db_name</var>___backup.ora';
```

  5. Create a new spfile and restart the database.

    To get the path of original spfile under disk group:

     `cat $ORACLE_HOME/dbs/init(database sid).ora`

    For example: spfile=+preferred disk group/db_name/spfiledb_name.ora

         sql> create spfile='+<var>preferred disk group</var>/<var>db_name</var>/spfile<var>db_name</var>.ora' from pfile='/act/touch/acttestdg/<var>db_name</var>___backup.ora';

    Restart the database with spfile in nomount state:

     ```
 sql> shutdown immediate;
 sql> startup nomount;
 ```

  6. Restore control file using RMAN from the Backup and DR mounted archive log image. Use the latest control file from Log mounted image, for example:
    /acttestdb_Log/cf-D_<var>db_name</var>-id_<var>db_id</var>.ctl or if more than one log image exists:
    /acttestdb_Log_1/cf-D_<var>db_name</var>-id_<var>db_id</var>.ctl

     ```
 rman target /
 rman> restore controlfile from '/acttestdg_Log_1/cf-D_<var>db_name</var>-id_<var>db_id</var>.ctl' ;
 ```

  7. Mount the database:

     rman> alter database mount;

  8. Catalog the datafile and archivelog folder from Backup and DR mounted database image and archive log image to RMAN:

     ```
 rman> run { catalog start with '+acttestdg/<var>db_name</var>/datafile' noprompt;
 catalog start with '+acttestdg/<var>db_name</var>/archivelog' noprompt;
 catalog start with '/acttestdg_Log' noprompt;}
 ```

  9. Restore and recover the database:

     rman> run { restore database ; recover database ; }

    For a specific point in time recovery run the recover command as under:

     ```
 rman> run
 {
 restore database;
 recover database until time "to_date('<var>timestamp</var>','yyyymmddhh24mi')";
 }
 ```

  10. Open the database with the reset log option.

    • For primary database recovery:

      rman> alter database open resetlogs;

      RAC config requires another step: After reset log, shut down the database on node 1 and start the database across all nodes. Use sqlplus shutdown the database:

       sqlplus / as sysdba

 SQL> shutdown immediate;

      Use srvctl to start database across all nodes:

       srvctl start database -d <var>database_name</var>

    • For Data Guard standby database recovery:

      • For non-RAC config: Start the Data Guard managed recovery process:

        rman> alter database recover managed standby database nodelay disconnect;

      • For RAC config: Shut down the database on node 1 and start the database in mounted state across all nodes.

        1. Use sqlplus to shut down the database:

          sqlplus / as sysdba
SQL> shutdown immediate;

        2. Use srvctl to start the database in a mount state across all nodes:

          srvctl start database -d <var>database_name</var> -o mount

        3. Start the Data Guard managed recovery process:

          rman> alter database recover managed standby database nodelay disconnect;

    The database is available for read and write.

Recover Oracle tablespace and data files

To recover a single tablespace data file, for example, due to data corruption:

  1. Mount the latest database snapshot from the backup/recovery appliance back to the Oracle server.

  2. Catalog the database backup snapshot to RMAN.

  3. Restore and recover the tablespace using the backup snapshot as detailed here.

Recover a single tablespace of a production database on an ASM Disk Group

To recover a single tablespace of a production database to the primary node:

  1. Mount the database point-in-time snapshot as detailed in Mount an Oracle database as a standard mount.

  2. In the Mount window under Mapping Options, provide a mount location for the image. For example, for an image under ASM disk group provide a disk group name under Preferred Disk Group and for image under file system provide a mount location such as /acttestdb.

  3. From the primary node, log into the database server as Oracle OS user.

  4. Set the database environment and log into RMAN:

    rman target /

  5. At the RMAN prompt, catalog the backup data file and archive log folder:

    Example: A database image protected under ASM Disk Group: (Mounted ASM Disk Group name " + acttestdg"):

    ```rman
rman> catalog start with '+acttestdg/<var>db_name</var>/datafile' noprompt;
rman> catalog start with '+acttestdg/<var>db_name</var>/archivelog' noprompt;
rman>catalog start with &#8216;/<var>mountpoint_log</var>'; noprompt;
(If archivelogs are protected by Backup and DR)
```

    Example: A database image protected under file system (mounted file system name "/acttestdb"):

    ```rman
rman> catalog start with '/acttestdb/datafile' noprompt;
rman> catalog start with '/acttestdb/archivelog'
noprompt;
rman> catalog start with '/acttestdb_log/archivelog' noprompt;
```

(If archivelogs are protected by Backup and DR)

    Now you can run all RMAN recovery commands, such as:

  6. When finished, unmount and delete the image.

Recover a tablespace

To recover a tablespace:

    ```rman
    rman> restore tablespace <var>tablespace name</var>;
    rman> recover tablespace <var>tablespace name</var>;
    ```

Recover a datafile

To recover a datafile:

    ```rman
    rman> restore datafile <var>file#</var>;
    rman> recover datafile <var>file#</var>;
    ```

Recover a corrupt database block

To recover a corrupt database block:

  1. Mount the database point-in-time snapshot as detailed in Mount an Oracle database as a standard mount.

  2. In the Mount window, Mapping Options, provide a mount location for the image. For example, for an image in an ASM disk group provide a disk group name under Preferred Disk Group and for image in a file system provide a mount location ex: /acttestdb.

  3. From the primary node, log into the database server as Oracle OS user.

  4. Set the database environment and log into sqlplus, then query v$database_block_corruption to check the corrupt blocks:

    ```sh
sqlplus / as sysdba
sql> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
```

  5. Login to RMAN to recover all corrupted blocks:

    ```rman
rman target /
rman> RECOVER CORRUPTION LIST;
```

    After the blocks are recovered, the database removes them from V$DATABASE_BLOCK_CORRUPTION.

  6. To recover an individual corrupt block (for example: datafile 8 and block 13):

    From RMAN prompt

    ```rman
RMAN> recover datafile 8 block 13;
```

Recover lost control files

To recover lost control files:

  1. Mount the database point-in-time snapshot as detailed in Mount an Oracle database as a standard mount.

  2. In the Mount window, Mapping Options, provide a mount location for the image. For example, for an image under ASM disk group provide a disk group name under Preferred Disk Group and for an image under file system provide a mount location such as /acttestdb.

  3. From the primary node, log into the database server as Oracle OS user.

  4. Set the database environment and log into sqlplus, then shut down the database and start in nomount state:

    For standalone database:

    ```sql
sqlplus / as sysdba
sql> shutdown immediate;
sql> startup nomount;
```

    For RAC database from the mounted image node shutdown the database across all nodes:

    ```sh
srvctl stop database -d <var>db_name</var>
sql> startup nomount;
```

  5. Restore the control file from Backup and DR mounted image.

    For example: /acttestdb (file system) and +acttestdg (for ASM)

    ```rman
rman target /
rman> restore controlfile
from '/acttestdb/cf-D_<var>db_name</var>-id_<var>db_id</var>.ctl' ; (Filesystem
mount)
rman> restore controlfile
from '+acttestdb/cf-D_<var>db_name</var>-id_<var>db_id</var>.ctl' ; (ASM
mount)
```

  6. Mount and open the database from sqlplus:

    ```sql
sqlplus / as sysdba
sql> alter database mount;
sql> recover database until cancel;
sql> alter database open resetlogs;
```

Recover an Oracle pluggable database

To recover an Oracle 12c or later pluggable database (PDB):

  1. Mount the image from the source database server to recover. In the Mount window, provide a preferred disk group for the image mount under ASM on RAC Node 1. For details on how to mount a database image, see Mount an Oracle database image protected under ASM Disk Group for data access.

  2. Close the pluggable database:

    1. From Node 1, su to Oracle User

      su - oracle

    2. Set the Oracle environment. Connect to the Oracle database as "sysdba" user:

         sqlplus / as sysdba
   SQL> alter pluggable database <var>pluggable_db_name</var> close;

  3. Catalog the datafile and archivelog folder from Backup and DR mounted database image and archive log image to RMAN:

        rman> run { catalog start with '+acttestdg/<var>db_name</var>/datafile' noprompt;
    catalog start with '+acttestdg/<var>db_name</var>/archivelog' noprompt;
    catalog start with '/acttestdg_Log' noprompt;
    }

  4. Restore and recover the pluggable database

        rman> run
    {
    restore pluggable database <var>pluggable_db_name</var>;
    recover pluggable database <var>pluggable_db_name</var> until time "to_date('<var>required time stamp</var>>';,';yyyymmddhh24mi')";
    }

  5. Open the pluggable database:

        rman> alter pluggable database <var>pluggable_db_name</var> open;

The pluggable database is open for read and write.

The Oracle DBA guide