5 1 0:Oracle Restore
Overview
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:
- 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.
- 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.
- 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)
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:
/opt/oracle/restore/RESTORE/
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.
Steps
- 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:
- Setting up the database clone: The database clone must be in startup nomount mode with archive log enabled:
- Creating the RMAN restore script: Based on the information of the backup log, create a RMAN script (e.g., duplicate_SOURCE.rman), for example:
- Executing the RMAN script: Connect the source and new database using the following command:
- 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 /
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<
sqlplus / as sysdba shutdown immediate startup nomount alter database archivelog;
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'; set 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). 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.
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. |
oracle@cefix:~$ rman target sys/sep@SOURCE auxiliary / Recovery Manager: Release 11.2.0.1.0 - 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:
@duplicate_SOURCE.rman
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.
See also
Oracle Duplicate Database – Oracle Requirements and Configuration – Oracle Backup