4 4 3:Oracle Restore

From SEPsesam
Jump to: navigation, search
This page contains changes which are not marked for translation.

Other languages:
Deutsch • ‎English

Copyright © SEP AG 1999-2021. All rights reserved.

Any form of reproduction of the contents or parts of this manual is allowed only with the express written permission from SEP AG. When compiling and designing user documentation SEP AG uses great diligence and attempts to deliver accurate and correct information. However, SEP AG cannot issue a guarantee for the contents of this manual.

This page is a draft. Treat the information on this page with caution as it may be incomplete.

Docs latest icon.png Welcome to the latest SEP sesam documentation version 4.4.3/4.4.3 Beefalo V2. For previous documentation version(s), check Oracle archive.


The procedures and features explained herein provide only information about the SEP sesam-specific parts of Oracle restore. For detailed information on Oracle-specific backup and recovery, see Oracle database documentation.

The SEP sesam backup module for Oracle integrates Oracle Recovery Manager (RMAN) with SEP sesam to efficiently back up and restore Oracle databases online. Oracle RMAN controls the Oracle server processes and is responsible for maintaining the information in the recovery catalog and control file for performing Oracle backups and restores.

RMAN can store backup data in a logical structure called a backup set. This contains the data from one or more datafiles, archived redo logs, control files, or server parameter file. Backup sets contain one or more binary files in an RMAN-specific format (so-called backup pieces). The restore from backup copy is performed using the RMAN, while SEP sesam represents a media management utility.

RMAN provides a full database restore which includes restoring all datafiles, control and server parameter files to a target database. It is possible to restore to the original source or a new location.

If you want to create a new copy of your target database for ongoing use on a new host, RMAN has the ability to duplicate (clone) a database from a previous backup. You can create a duplicate database on a remote server with the same file structure, a remote server will a different file structure, or on a local server with a different file structure. As the DUPLICATE command assigns a new DBID to the newly created database, it is registered in the same recovery catalog as the original database.

The RESTORE DATABASE command is typically used if your production database is damaged and needs to be restored (replaced). However, if you want to restore into a test environment and only make a copy of the production database, the DUPLICATE command should be used so that the production backups are not affected.

RMAN manages the target database (the database containing the control files, data files and optional archived redo logs that are being backed up or restored) and all Oracle server processes on the target database. In case of using ASM, keep in mind that this is a file system and volume manager that also need to be backed up regularly.

Executing backup

The backup configuration is described in Oracle Backup.

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. For details, see 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 control file (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, you must create a new and fresh setup instance 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:


Restoring an Oracle database

The procedure explains, how to restore an instance on the same system to a new instance. It describes a regular setup, without ASM or other special components.


  1. Reading the backup log: As the new instance will be restored to a different location, look at the backup log where data-files have been backed up:
  2. 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 data-file sections describ 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< 
  3. Setting up the database clone: The database clone must be in startup nomount mode with archive log enabled:
  4. sqlplus / as sysdba 
    shutdown immediate
    startup nomount
    alter database archivelog;
  5. Creating the RMAN restore script: Based on the information of the backup log, create a RMAN script (e.g., duplicate_SOURCE.rman), for example:
  6. run {
    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';
    set newname for tempfile 1 to '/opt/oracle/restore/RESTORE/tempfile01.dbf'; 
    duplicate target database to 'RESTORE'
    '/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). You must also set the set newname for tempfile clauses according to the report schema command. The syntax for the logfile options is the same as used in the create database command.

    SEP Tip.png Tip
    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}). For details, see The DUPLICATE command in Oracle Database Backup and Recovery Reference.
  7. Executing the RMAN script: Connect the source and new database using the following command:
  8. oracle@cefix:~$ rman target sys/sep@SOURCE auxiliary /
    Recovery Manager: Release - Production on Tue Mar 11 13:56:14 2014Copyright (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) 

    Execute the RMAN script:

    • 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 /

In case of failed restore, RMAN has already set the new DBID and SID for the new target database. Note that 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 the 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;

You have to close established RMAN sessions, which are discarded and restart the RMAN commands.