RMAN RESTORE

Oracle RMAN (Recovery Manager) provides powerful functionality for restoring databases, tablespaces, datafiles, or even individual blocks from backups. RMAN restores data from backup sets or image copies and recovers it using the necessary archived redo logs to bring the database to a consistent state.

Here’s a guide on performing RMAN restore operations:

1. Types of RMAN Restores

Complete Database Restore: Restores the entire database to the point of the most recent backup and applies necessary archived redo logs.
Tablespace Restore: Restores a specific tablespace instead of the entire database.
Datafile Restore: Restores one or more specific datafiles.
Control File Restore: Restores the control file, typically when it is lost or corrupted.
SPFILE Restore: Restores the Server Parameter File (SPFILE), needed if it's lost or corrupted.
Block Media Recovery: Recovers individual corrupted blocks without restoring an entire datafile.

2. General Steps for RMAN Restore

a. Restoring a Database

Start RMAN and connect to the target database:
bash
Copy code
$ rman target /

Mount the database (if it’s not already mounted):
- This is required because RMAN needs access to the control file.

sql
Copy code
RMAN> STARTUP MOUNT;

Restore the database:
- This will restore all the datafiles from the most recent backup.

sql
Copy code
RMAN> RESTORE DATABASE;

Recover the database:
- RMAN will apply all archived redo logs to bring the database to a consistent state.

sql
Copy code
RMAN> RECOVER DATABASE;

Open the database:
- After recovery, the database is opened.

sql
Copy code
RMAN> ALTER DATABASE OPEN;

Restoring a Specific Tablespace

Put the affected tablespace offline:
sql
Copy code
SQL> ALTER TABLESPACE users OFFLINE;

Restore the tablespace:
sql
Copy code
RMAN> RESTORE TABLESPACE users;

Recover the tablespace:
sql
Copy code
RMAN> RECOVER TABLESPACE users;

Bring the tablespace online:
sql
Copy code
SQL> ALTER TABLESPACE users ONLINE;

Restoring Specific Datafiles

Identify the datafile(s) to restore:
- You can query the V$DATAFILE view for file numbers and paths.

sql
Copy code
SQL> SELECT file#, name FROM v$datafile;

Take the datafile(s) offline:
sql
Copy code
SQL> ALTER DATABASE DATAFILE 3 OFFLINE;

Restore the datafile(s):
sql
Copy code
RMAN> RESTORE DATAFILE 3;

Recover the datafile(s):
sql
Copy code
RMAN> RECOVER DATAFILE 3;

Bring the datafile(s) online:
sql
Copy code
SQL> ALTER DATABASE DATAFILE 3 ONLINE;

Restoring the Control File

If your control file is lost or corrupted, you can restore it using RMAN:

Start RMAN and connect to the target database:
bash
Copy code
$ rman target /

Start the instance in nomount mode:
- You cannot mount the database since the control file is lost.

sql
Copy code
RMAN> STARTUP NOMOUNT;

Restore the control file:
sql
Copy code
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

Mount the database:
sql
Copy code
RMAN> ALTER DATABASE MOUNT;

Recover the database (if needed):
sql
Copy code
RMAN> RECOVER DATABASE;

Open the database:
sql
Copy code
RMAN> ALTER DATABASE OPEN RESETLOGS;

Restoring the SPFILE (Server Parameter File)

If the SPFILE is lost, RMAN can restore it from an autobackup or another backup.

Start RMAN and connect to the target database:
bash
Copy code
$ rman target /

Start the instance in nomount mode:
sql
Copy code
RMAN> STARTUP FORCE NOMOUNT;

Restore the SPFILE:
sql
Copy code
RMAN> RESTORE SPFILE FROM AUTOBACKUP;

Restart the database with the restored SPFILE:
sql
Copy code
RMAN> SHUTDOWN IMMEDIATE;

RMAN> STARTUP;

Block Media Recovery

If only specific blocks are corrupted, you can use RMAN to recover those blocks instead of restoring an entire datafile.

Identify corrupted blocks:
- You can use the DBMS_REPAIR package or check the alert log for corrupted block messages.

Recover the corrupted blocks:
sql
Copy code
RMAN> BLOCKRECOVER DATAFILE 4 BLOCK 12;

3. RMAN Restore Options

a. SET UNTIL TIME / SCN / SEQUENCE

You can restore a database to a specific point in time, SCN (System Change Number), or log sequence number.

Example for point-in-time restore:
sql
Copy code
RMAN> SET UNTIL TIME 'SYSDATE-1'; -- Restore to one day ago

RMAN> RESTORE DATABASE;

RMAN> RECOVER DATABASE;

RMAN> ALTER DATABASE OPEN RESETLOGS;

b. RESTORE PREVIEW

You can preview the restore operation to see which files RMAN will restore and which backups it will use.

Command:
sql
Copy code
RMAN> RESTORE DATABASE PREVIEW;

c. RESTORE VALIDATE

You can validate the backups that RMAN would use for a restore without actually performing the restore. This helps to check the integrity of the backups.

Command:
sql
Copy code
RMAN> RESTORE DATABASE VALIDATE;

d. SKIP INACCESSIBLE

This option is used if some files are inaccessible during the restore process, allowing RMAN to skip them.

Command:
sql
Copy code
RMAN> RESTORE DATABASE SKIP INACCESSIBLE;

e. NOREDO

You can specify NOREDO when you don’t want RMAN to apply archived redo logs after restoring.

Command:
sql
Copy code
RMAN> RESTORE DATABASE NOREDO;

4. RMAN Restore Best Practices

Always use VALIDATE: Before performing a restore, use the VALIDATE command to ensure that the backup is usable.
Test Recovery: Periodically test your recovery strategy by performing restore operations in a test environment.
Maintain Retention Policy: Make sure your RMAN retention policy is configured so that you always have a sufficient number of backups available for recovery.
Backup Control Files and SPFILE: Always configure RMAN to back up the control file and SPFILE automatically after every backup.

Restore Database from a full RMAN backup

Restoring a database to a new server from a full RMAN backup is a common requirement when migrating databases to new hardware or recovering from a disaster. This process involves copying the RMAN backups from the original server, restoring the control file, datafiles, and performing recovery on the new server. Below are the detailed steps to achieve this:

1. Prerequisites

Before starting the RMAN restore to a new server, ensure the following:

You have a full RMAN backup of the original database, including datafiles, archived redo logs, control file, and server parameter file (SPFILE).
The target server has Oracle software installed and configured.
The new server has the necessary storage and directories for the Oracle database files.

2. Prepare the New Server

Install the same version of Oracle Database as the source system on the new server.
Configure Oracle on the new server with the same directory structure or adjust paths as needed (if directory structures differ, you will need to update paths during restoration).
Set necessary environment variables like ORACLE_HOME, ORACLE_SID, and PATH.

3. Transfer Backup Files

Copy all RMAN backup files from the source server to the new server. These backup files should include:

Datafile backups
Archived redo log backups
Control file autobackups
SPFILE backups

Make sure the directory structure on the new server is consistent or update the paths in the restore process if needed.

4. Restore and Recover Database on New Server

Once the backup files are copied to the new server, follow these steps to restore and recover the database:

a. Start RMAN and Connect to the Target Database

On the new server, start RMAN.
bash
Copy code
$ rman target /

Set the DBID (Database Identifier) of the original database:
- You can find the DBID in the RMAN backup log, RMAN backup pieces, or from the original database.

sql
Copy code
RMAN> SET DBID <dbid_value>;

b. Restore the Control File

Start the instance in nomount mode:
- The control file is required to mount the database, but it hasn’t been restored yet.

sql
Copy code
RMAN> STARTUP NOMOUNT;

Restore the control file from the backup:
- Assuming the control file autobackup is available, use the following command to restore it:

sql
Copy code
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

- Alternatively, if you know the exact backup piece containing the control file:

sql
Copy code
RMAN> RESTORE CONTROLFILE FROM '/path/to/controlfile/backup';

Mount the database:
sql
Copy code
RMAN> ALTER DATABASE MOUNT;

c. Catalog the Backup Files

If the backup files are in a different location on the new server, catalog them to let RMAN know where they are:

sql

Copy code

RMAN> CATALOG START WITH '/new/path/to/backup/files/';

d. Restore the SPFILE (if needed)

If the SPFILE was backed up and you need to restore it:

Shutdown the instance:
sql
Copy code
RMAN> SHUTDOWN IMMEDIATE;

Restore the SPFILE:
sql
Copy code
RMAN> RESTORE SPFILE FROM AUTOBACKUP;

Restart the instance with the restored SPFILE:
sql
Copy code
RMAN> STARTUP FORCE NOMOUNT;

e. Restore the Database

Restore the datafiles from the backup:
sql
Copy code
RMAN> RESTORE DATABASE;

RMAN will automatically restore the datafiles from the backups that were cataloged or are in the default location.

f. Recover the Database

After restoring the datafiles, perform media recovery to apply all necessary archived redo logs:
sql
Copy code
RMAN> RECOVER DATABASE;

RMAN will apply the necessary archived redo logs to bring the database to a consistent state.

g. Open the Database

Once recovery is complete, you can open the database. Since the database is restored to a new server, you must open it with the RESETLOGS option:
sql
Copy code
RMAN> ALTER DATABASE OPEN RESETLOGS;

5. Modify File Locations (if needed)

If the file paths on the new server differ from the original server (for example, if the file system layout is different), you can use RMAN’s SET NEWNAME command to modify the locations of datafiles, online redo logs, and control files during the restore process.

Example of Using SET NEWNAME

Before restoring the database, use SET NEWNAME to redirect the datafiles:
sql
Copy code
RUN {

SET NEWNAME FOR DATAFILE 1 TO '/newpath/system01.dbf';

SET NEWNAME FOR DATAFILE 2 TO '/newpath/sysaux01.dbf';

SET NEWNAME FOR DATAFILE 3 TO '/newpath/undotbs01.dbf';

SET NEWNAME FOR DATAFILE 4 TO '/newpath/users01.dbf';

RESTORE DATABASE;

SWITCH DATAFILE ALL;

RECOVER DATABASE;

}

This method ensures that RMAN restores the files to the new directory paths.

6. Update Database Configuration (Optional)

After restoring the database to the new server, you may need to perform additional tasks, such as:

Updating listener configuration (listener.ora).
Changing TNS configuration (tnsnames.ora).
Adjusting any environment-specific configurations (e.g., database directories, logs).

7. Post-Restoration Verification

Verify that the database is functioning correctly:
sql
Copy code
SQL> SELECT status FROM v$instance;

Check the alert log and RMAN log files for any errors during the restore process.

Example: RMAN Restore on New Server

sql

Copy code

-- Step 1: Set the DBID

RMAN> SET DBID 123456789;

-- Step 2: Startup the database in NOMOUNT mode

RMAN> STARTUP NOMOUNT;

-- Step 3: Restore the control file from autobackup

RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;

-- Step 4: Mount the database

RMAN> ALTER DATABASE MOUNT;

-- Step 5: Catalog backup pieces (if needed)

RMAN> CATALOG START WITH '/new/server/backup/';

-- Step 6: Restore the SPFILE (if required)

RMAN> RESTORE SPFILE FROM AUTOBACKUP;

-- Step 7: Restore the database

RMAN> RESTORE DATABASE;

-- Step 8: Recover the database

RMAN> RECOVER DATABASE;

-- Step 9: Open the database with RESETLOGS

RMAN> ALTER DATABASE OPEN RESETLOGS;

Key Considerations

Same Oracle Version: Ensure the new server has the same version of Oracle installed as on the source server.
Network Configuration: If you need remote access to the new database, ensure the listener is configured properly.
Password File: Copy the password file from the original server if using remote authentication for sysdba.

This method outlines the process for restoring a full RMAN backup to a new server, allowing for a smooth database migration or disaster recovery process.

Importance of DBID in RMAN

Unique Identification: The DBID ensures that RMAN associates backups with the correct database, preventing confusion when working with multiple databases.
Recovery Without a Control File: If the control file is lost or the database is being restored on a new server, RMAN can identify the correct backups for the database using the DBID.
Restore Control File from Autobackup: If you need to restore the control file and SPFILE, RMAN requires the DBID to locate the correct autobackup file.
Database Duplication: When duplicating or cloning a database, the DBID helps RMAN distinguish between the source and target databases.

The DBID is a vital identifier in RMAN restore and recovery operations, especially when restoring databases without a control file or when restoring to a new server. RMAN uses the DBID to locate the correct backups and ensure that the appropriate files are used during recovery. By setting the DBID in RMAN, you can easily restore the control file, SPFILE, and datafiles from autobackups, ensuring a smooth recovery process.

Page updated

Google Sites

Report abuse