Restore an Oracle database

Restoring 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.

Restore a database using the management console, overwriting the production database

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

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.
Right-click the application and select Access.
On the Access page, select the desired image and click Restore under the Mount menu.
On the Restore page, select Traditional (not Mount and Migrate).
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.
At Username enter Oracle software owner on the selected target server.
For Number of Channels, specify the number of RMAN channels to configure parallelism for database restore.
Click Submit.
A warning dialog appears. Read it and enter DATA LOSS to confirm.
The job is queued for the next available job slot. You can view progress from the Jobs Monitor.

Note: The backup plan options (Run Schedule, Expire Data) of the database are disabled.

Recover an Oracle database manually using RMAN

To manually recover an Oracle database, refer to the procedures below.

Non-RAC, Non-ASM, File System

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

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: /acttestdb

The protected archive log is mounted at: /acttestdb_Log
Set the Oracle environment and use sqlplus to shut down the database:
```
sqlplus / as sysdba
sql> shutdown immediate;
```
Verify the database is shut down. Kill any orphan process for the database.
```
ps -ef | grep <db name>
```
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

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

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

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<database
sid>.ora' from pfile='/acttestdb/<database sid>___backup.ora';

Restart the database with spfile in nomount state:

sql> shutdown immediate;
sql> startup nomount;

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 D-id_.ctl

or if more than one log image:

/acttestdb_Log1/cf-D-id_.ctl
```
rman target /
rman> restore controlfile from '/acttestdb_Log_1/cf-D_<sid>-id_<id>.ctl' ;
```
Mount the database:
```
rman> alter database mount;
```

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;}

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('<desired
time stamp>','yyyymmddhh24mi')";
}

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

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
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 <database name>

Verify the database is shutdown (all nodes). Kill any orphan process for the database.

ps -ef | grep <db name>
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

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

sqlplus / as sysdba

sql> startup nomount pfile='/acttestdb/<db name>___backup.ora';

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}/spfile{db name}.ora`
```
  sql> create spfile='+{preferred disk group}/{db
  name}/spfile{db name}.ora' from pfile='/acttestdb/{db name}___backup.ora';
```
Restart the database with spfile in nomount state:
```
  sql> shutdown immediate;

  sql> startup nomount;
```
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-D{db name}-id_{id}.ctl

or if more than one log image:
/acttestdb_Log1/cf-D{db name}-id_{id}.ctl
```
 rman target /

 rman> restore controlfile from '/acttestdb_Log_1/cf-D_<db
 name>-id_<id>.ctl' ;
```
Mount the database:

rman> alter database mount;

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;}

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('<desire
 time stamp>','yyyymmddhh24mi')";
 }
```
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 above, shutdown 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 the database across all nodes:
```
 srvctl start database -d <database name> 
```
- 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 <database name> -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

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/, for example:
  
  /act/touch/acttestdg/<db name>___backup.ora
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 <db name>
```
Verify the database is shut down (on all nodes). Kill any orphan process for the database.
```
ps -ef | grep <db name>
```
Start the database in nomount state using the backup parameter file copied under /act/touch/acttestdg.

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

```
sqlplus / as sysdba

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

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}/spfile{db name}.ora

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

Restart the database with spfile in nomount state:

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

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_<db name>-id_<id>.ctl or if more than one log image exists:
/acttestdb_Log_1/cf-D_<db name>-id_<id>.ctl
```
 ```
 rman target /
 rman> restore controlfile from '/acttestdg_Log_1/cf-D_<db name>-id_<id>.ctl' ;
 ```
```
Mount the database:
```
 rman> alter database mount;
```

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/<db name>/datafile' noprompt;
 catalog start with '+acttestdg/<db name>/archivelog' noprompt;
 catalog start with '/acttestdg_Log' noprompt;}
 ```

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('<desired
 time stamp>','yyyymmddhh24mi')";
 }
 ```

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 above, shutdown 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 <database name> 
```
- 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 <database name> -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:

Mount the latest database snapshot from the backup/recovery appliance back to the Oracle server.
Catalog the database backup snapshot to RMAN.
Restore and recover the tablespace using the backup snapshot as detailed below.

Recover a Single Tablespace of a Production Database on an ASM Disk Group

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

Mount the database point-in-time snapshot as detailed in Accessing, Recovering, or Restoring an Oracle Database.
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 ex: /acttestdb.
- For instructions on how to mount a database image protected under file system, see Mounting an Oracle Database Image Protected Under a File System for Data Access.
- For details on how to mount a database image protected under ASM Disk Group: see Mounting an Oracle Database Image Protected Under an ASM Disk Group for Data Access.
From the primary node, log into the database server as Oracle OS user.
Set the database environment and log into RMAN:
```
rman target /
```

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> catalog start with '+acttestdg/<db name>/datafile' noprompt;
rman> catalog start with '+acttestdg/<db name>/archivelog' noprompt;
rman>catalog start with &#8216;/<mountpoint_log>'; noprompt;</span>
(If archivelogs are protected by Backup and DR)

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

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:

Recovering a Tablespace
Recovering a Datafile
Recovering a Corrupt Database Block
Recovering Lost Control Files
Recovering an Oracle Pluggable Database

When finished, unmount and delete the image.

Recover a Tablespace

To recover a tablespace:

    rman> restore tablespace <tablespace name>;
    rman> recover tablespace <tablespace name>;

Recover a Datafile

To recover a datafile:

    rman> restore datafile <file#>;
    rman> recover datafile <file#>;

Recover a Corrupt Database Block

To recover a corrupt database block:

Mount the database point-in-time snapshot as detailed in Accessing, Recovering, or Restoring an Oracle Database.
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 ex: /acttestdb.
- For instructions on how to mount a database image protected under file system, see Mounting an Oracle Database Image Protected Under a File System for Data Access.
- For details on how to mount a database image protected under ASM Disk Group: see Mounting an Oracle Database Image Protected Under an ASM Disk Group for Data Access.
From the primary node, log into the database server as Oracle OS user.
Set the database environment and log into sqlplus, then query v$database_block_corruption to check the corrupt blocks:
```
sqlplus / as sysdba
sql> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
```
Login to RMAN to recover all corrupted blocks:
```
rman target /
rman> RECOVER CORRUPTION LIST;
```
After the blocks are recovered, the database removes them from V$DATABASE_BLOCK_CORRUPTION.
To recover an individual corrupt block (ex: datafile 8 and block 13):

From RMAN prompt
```
RMAN> recover datafile 8 block 13;
```

Recover Lost Control Files

To recover lost control files:

Mount the database point-in-time snapshot as detailed in Accessing, Recovering, or Restoring an Oracle Database.
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 ex: /acttestdb.
- For instructions on how to mount a database image protected under file system, see Mounting an Oracle Database Image Protected Under a File System for Data Access.
- For details on how to mount a database image protected under ASM Disk Group: see Mounting an Oracle Database Image Protected Under an ASM Disk Group for Data Access.
From the primary node, log into the database server as Oracle OS user.
Set the database environment and log into sqlplus, then shut down the database and start in nomount state:

For standalone database:
```
sqlplus / as sysdba
sql> shutdown immediate;
sql> startup nomount;
```
For RAC database from the mounted image node shutdown the database across all nodes:
```
srvctl stop database -d <dbname>
sql> startup nomount;
```

Restore the control file from Backup and DR mounted image as above.

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

rman target /
rman> restore controlfile
from '/acttestdb/cf-D_<db name>-id_<id>.ctl' ; (Filesystem
mount)
rman> restore controlfile
from '+acttestdb/cf-D_<db name>-id_<id>.ctl' ; (ASM
mount)

Mount and open the database from sqlplus:

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):

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 Mounting an Oracle Database Image Protected Under an ASM Disk Group for Data Access.
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 <Pluggable DB name> close;
```

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/<db name>/datafile' noprompt;
    catalog start with '+acttestdg/<db name>/archivelog' noprompt;
    catalog start with '/acttestdg_Log' noprompt;
    }

Restore and recover the pluggable database
```
    rman> run
    {
    restore pluggable database <Pluggable DB name>;
    recover pluggable database <Pluggable DB
    name> until time "to_date('<desired time stamp>';,';yyyymmddhh24mi')";
    }
```
Note: These steps are applicable only for Oracle 12.1.0.2 version and above. If Oracle version is 12.1.0.1 or below 12.1.0.2, then recovery of all datafiles belonging to the pluggable database must be performed before executing Step 5. For example: rman> recover datafile <PDB datafile number>;

Open the pluggable database:

    rman> alter pluggable database <Pluggable DB name> open;

The pluggable database is open for read and write.

Restore an Oracle database

Restore a database using the management console, overwriting the production database

Recover an Oracle database manually using RMAN

Non-RAC, Non-ASM, File System

RAC or Standalone ASM, File System

RAC or Standalone ASM, ASM Disk Group

Recover Oracle Tablespace and Data Files

Recover a Single Tablespace of a Production Database on an ASM Disk Group

Recover a Tablespace

Recover a Datafile

Recover a Corrupt Database Block

Recover Lost Control Files

Recover an Oracle Pluggable Database

The Oracle DBA Guide