Archive:Oracle Restore in Instance

How to restore a Oracle Database to a new instance on the same system

This short manual describes how to restore a Instance on the same system to a new instance. This manual speaks about a regular setup, no ASM or other special components.

Note: An overview of the duplicate database procedure is given in the article ORACLE Duplicate Database.

Backup Execution

Backup configuration is done like described in the backup manual: SEP sesam Extension for Oracle

The following assumptions are done:

1. One database server with two instances: An original instance with name SOURCE is running on this system. This instance should be restored to instance RESTORE.
2. No rman catalog database is used (backup information is stored in control file). To work with a rman catalog the DUPLICATE database command must be executed with a connection to the rman catalog (see 'Note' under Executing the rman script).
3. The ORACLE_SID is set to 'RESTORE' to allow to connect to the new database without resolving via tnsnames (on UNIX: export ORACLE_SID=RESTORE, on WINDOWS: set ORACLE_SID=RESTORE). Otherwise the tnsnames.ora must be set up and the listener must be restarted lsnrctl reload.

The following backup tasks have to be executed:

• Full backup of datafiles with controlfile (rman 'CONFIGURE CONTROLFILE AUTOBACKUP ON')
• Archived redo log backup

Preparing the system for a restore

In order to be able to restore to a new database instance, a new and fresh setup instance is needed. This instance can be created on the oracle system using the "dbca" command. The location of the new database files is really important in this step, as this information is needed for the restore. The new location for the restored database is:

/opt/oracle/restore/RESTORE/

Restore

Reading the backup log

As the new instance will be restore to a different location, the first step is to look at the backup log which data-files have been backed up. This looks like this:

Starting backup at 11-MAR-14
channel SOURCE1: starting incremental level 0 datafile backup set
channel SOURCE1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oracle/app/oracle/oradata/SOURCE/system01.dbf
input datafile file number=00002 name=/opt/oracle/app/oracle/oradata/SOURCE/sysaux01.dbf
input datafile file number=00003 name=/opt/oracle/app/oracle/oradata/SOURCE/undotbs01.dbf
input datafile file number=00005 name=/opt/oracle/app/oracle/oradata/SOURCE/example01.dbf
input datafile file number=00006 name=/opt/oracle/oradata/SOURCE/sesamdata.dbf
input datafile file number=00004 name=/opt/oracle/app/oracle/oradata/SOURCE/users01.dbf
channel SOURCE1: starting piece 1 at 11-MAR-14

The input datafile sections describe the files which have been backed up. These files are referencing to the original location.

As the backup will not include any TEMPFILE databases, issue the following command on the ORIGINAL database in order to find out how many tempfiles are in place:

RMAN> connect target /
connected to target database: SOURCE (DBID=2960287951)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name SOURCE
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    680      SYSTEM               ***     /opt/oracle/app/oracle/oradata/SOURCE/system01.dbf
2    520      SYSAUX               ***     /opt/oracle/app/oracle/oradata/SOURCE/sysaux01.dbf
3    100      UNDOTBS1             ***     /opt/oracle/app/oracle/oradata/SOURCE/undotbs01.dbf
4    5        USERS                ***     /opt/oracle/app/oracle/oradata/SOURCE/users01.dbf
5    100      EXAMPLE              ***     /opt/oracle/app/oracle/oradata/SOURCE/example01.dbf
6    100      SESAMDATA            ***     /opt/oracle/oradata/SOURCE/sesamdata.dbf 

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    29       TEMP                 32767       /opt/oracle/app/oracle/oradata/SOURCE/temp01.dbf

Setting up the database clone

The database clone must be in "startup nomount" mode with archive log enabled:

sqlplus / as sysdba 
shutdown immediate
startup nomount
alter database archivelog;

Creating the RMAN restore script

Based on the information of the backup log, create a rman script (e.g. 'duplicate_SOURCE.rman') which looks something like this:

run {
ALLOCATE auxiliary channel T1 type 'SBT_TAPE' parms 'ENV=(SESAM_SERVER=qsstor,SESAM_TAPE_SERVER=qsstor,SESAM_JOB=CEFIX_ORACLE_SOURCE,SESAM_DRIVE=,
SESAM_POOL=diskstore-7days,SOB_TRACE=0,SOB_LOGFILE=/tmp/restore.log,SESAM_COM_TIMEOUT=)';
set newname for datafile 1 to '/opt/oracle/restore/RESTORE/system01.dbf';
set newname for datafile 2 to '/opt/oracle/restore/RESTORE/sysaux01.dbf';
set newname for datafile 3 to '/opt/oracle/restore/RESTORE/undotbs01.dbf';
set newname for datafile 4 to '/opt/oracle/restore/RESTORE/users01.dbf';
set newname for datafile 5 to '/opt/oracle/restore/RESTORE/example01.dbf';
set newname for datafile 6 to '/opt/oracle/restore/RESTORE/sesamdata.dbf';
ser newname for tempfile 1 to '/opt/oracle/restore/RESTORE/tempfile01.dbf'; 
duplicate target database to 'RESTORE'
logfile
'/opt/oracle/restore/RESTORE/redo01.log' SIZE 50M,
'/opt/oracle/restore/RESTORE/redo02.log' SIZE 50M,
'/opt/oracle/restore/RESTORE/redo03.log' SIZE 50M;
}

The 'set newname' clauses will set the restore location for the new files (the folder of the new database RESTORE). The 'set newname for tempfile' clauses must also be set according to the "report schema" command. The syntax for the logfile options is the same used in the create database command.

Hint: Instead of using 'set newname' for every datafile and tempfile other clauses are available, e.g. DB_FILE_NAME_CONVERT = ("{original path}","{new path}") and LOG_FILE_NAME_CONVERT = ("{original log path}","{new log path}") (see "The DUPLICATE entry in Oracle Database Backup and Recovery Reference").

Executing the rman script

Connect the source and new database using the following command:

oracle@cefix:~$ rman target sys/sep@SOURCE auxiliary /
Recovery Manager: Release 11.2.0.1.0 - Production on Tue Mar 11 13:56:14 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: SOURCE (DBID=2960287951)
connected to auxiliary database: RESTORE (not mounted) 

After this execute the rman script:

@duplicate_SOURCE.rman

It will take care about the rest.

Note:

• rman connects to the database clone given as 'auxiliary {newdb}'
• rman connects to the source database given with 'target {source}' to fetch the metadata from the controlfile. There are several ways to get the metadata depending on the backup strategy:
  • Target, but no catalog. Metadata comes from target database controlfile: $ rman TARGET sys/password@SOURCE AUXILIARY /
  • Catalog, but no target. Metadata comes from the RMAN catalog: $ rman CATALOG rman/password@rman-catalog AUXILIARY /
  • Target and catalog. Metadata can come from the target controlfile or the catalog: $ rman TARGET sys/password@SOURCE CATALOG rman/password@rman-catalog AUXILIARY /
  • No target or catalog. Metadata comes from backups: $ rman AUXILIARY /

If something goes wrong

If the restore goes wrong, rman has already set the new DBID and SID for the new target database, a second restore will not be possible! You either have to recreate the new database or reset the name of the target database to the old name using the following command in sqlplus:

alter system set db_name='RESTORE' scope=spfile;
alter system set db_unique_name='RESTORE' scope=spfile;

stop and restart the database afterwards:

shutdown immediate; 
startup nomount;

Established rman sessions are discarded and must be closed, the rman commands must be restarted.

References

The Oracle documentation describes the 'Duplicating a Database' in the user guides. Here is a short selection of some articles.

• "Chapter 12 Duplicating a Database with Recovery Manager" - Oracle9i Recovery Manager User's Guide - Release 2 (9.2)
• "Chapter 23 Duplicating a Database" - Oracle® Database Backup and Recovery User's Guide 11g Release 1 (11.1)
• "Chapter 24 Duplicating a Database" - Oracle® Database Backup and Recovery User's Guide 11g Release 2 (11.2)
• "The DUPLICATE entry in Oracle Database Backup and Recovery Reference" - Oracle® Database Backup and Recovery User's Guide 11g Release 2 (11.2) for a complete list of which files are copied to the duplicate database. See restrictions: Duplication with Oracle Managed Files (OMF) using ASM disk group name.

The creation of a Standby database ('DUPLICATE TARGET DATABASE' with clause 'FOR STANDBY') is described under

• Creating a Standby Database with Recovery Manager

A short description of database duplication under ORACLE-BASE

• Duplicate a Database Using RMAN in Oracle Database 11g Release 2