Oracle8i Backup and Recovery Guide
Release 8.1.5
A67773-01

Library
Product
Contents
Index

Prev Next

10
Creating a Duplicate Database with Recovery Manager

This chapter describes how to use Recovery Manager to create a duplicate database for testing purposes, and includes the following topics:

Creating a Duplicate Database: Overview

The RMAN duplicate command allows you to use your target database backups to create a test database while still retaining your original database. The command takes image copies or backup sets of your target database's files and generates a new database. A duplicate database is especially useful if your production database must be up and running 24 hours per day, 7 days a week.

As part of the duplicating operation, RMAN manages the following:

When duplicating a database you can:

See Also: For duplicate command syntax, see "duplicate".

Obeying Restrictions

RMAN duplication has the following restrictions. You cannot:

Generating Files for the Duplicate Database

When duplicating a database, perform the following operations:

Creating the Control Files

The duplicate command creates the control files by using the names listed in the init.ora file of the duplicate database. When choosing names for the duplicate database control files, make sure that you do not overwrite the init.ora settings for the production files at the target database.

Creating the Online Redo Logs

You have these options for creating the names of the duplicate online redo logs, which are listed in the order of precedence:

Table 10-1 Order of Precedence for Redo Log Filename Creation
Order  Method  Result 

1  

Specify the logfile clause of duplicate command.  

Creates redo logs as specified.  

2  

Set LOG_FILE_NAME_CONVERT initialization parameter.  

Transforms target filenames, e.g., from log_* to duplog_*.

Note: This parameter allows the redo log to exist as long as the size matches, since it uses the reuse parameter when creating the logs.  

3  

Do none of the above.  

Reuses the target filenames. You must specify the nofilenamecheck option when using this method.  

The order of precedence determines how RMAN renames the online redo logs. For example, if you specify both the logfile clause and the LOG_FILE_NAME_CONVERT parameter, RMAN uses the logfile clause. If you specify all options, then RMAN uses the logfile clause and ignores the others.

WARNING:

If the target and duplicate databases are in the same host, do not use the name of an online redo log currently in use by the target database. Also, do not use the name of a redo log currently in use by the target database if the duplicate database is in a different host and the nofilenamecheck keyword is not used.  

Renaming the Datafiles

If you want to have different filenames in your duplicate datafile, then you must use parameters or commands to specify them. You have these options for renaming datafiles, listed in the order of precedence:

Table 10-2 Order of Precedence for Datafile Filename Creation
Order  Method  Result 

1  

Issue set newname command.  

Creates new datafile filenames. This command must be re-issued each time you want to rename files.  

2  

Issue set auxname command.  

Creates new datafile filenames. This setting stays in effect until disabled with a set auxname ... to null command.  

3  

Set DB_FILE_NAME_CONVERT initialization parameter.  

Transforms target filenames, e.g., from tbs_* to dupdbs_*. You can use this parameter for those files not renamed with either set newname and set auxname.  

4  

Do none of the above.  

Re-uses the target filenames. You must specify the nofilenamecheck option when using this method.  

The order of precedence determines how RMAN will name the datafiles. For example, if you specify all the commands and the initialization parameter, RMAN uses set newname. If you specify the set auxname command and DB_FILE_NAME_CONVERT, RMAN uses set auxname. If you do not specify any of the first three options, then RMAN uses the original target filenames for the duplicate file.

Skipping Read Only Tablespaces

When you specify skip readonly, RMAN does not duplicate the datafiles of these tablespaces. You will see the following values in the specified views or tables:

Table/View   Column   Value  

V$DATAFILE  

STATUS  

OFFLINE  

V$DATAFILE  

ENABLED  

READ ONLY  

V$DATAFILE  

NAME  

MISSINGxxx  

SYS.DBA_DATA_FILES  

STATUS  

AVAILABLE  

SYS.DBA_TABLESPACES  

STATUS  

READ ONLY  

Skipping Offline Clean Tablespaces

When tablespaces are taken offline with the OFFLINE NORMAL option, RMAN does not duplicate the datafiles of these tablespaces. After duplication, you can manually add or drop these tablespaces.

You will see the following values in the specified views or tables:

Table/View   Column   Value  

V$DATAFILE  

STATUS  

OFFLINE  

V$DATAFILE  

ENABLED  

DISABLED  

V$DATAFILE  

NAME  

MISSINGxxx  

SYS.DBA_DATA_FILES  

STATUS  

AVAILABLE  

SYS.DBA_TABLESPACES  

STATUS  

OFFLINE  

Note that when you take a tablespace offline with the IMMEDIATE option, RMAN duplicates rather than skips the tablespace. As with online tablespaces, RMAN requires a valid backup for duplication.

Preventing Filename Checking

It is possible for a set newname, set newname, or DB_FILE_NAME_CONVERT to generate a name that is already in use in the target database. In this case, specify nofilenamecheck to avoid an error. For example, assume that the host A database has two files: datafile 1 is named /oracle/data/file1.f and datafile 2 is named /oracle/data/file2.f. When duplicating to host B, you issue: 

run { 
     set newname for datafile 1 to /oracle/data/file2.f; # rename datafile 1 as file2.f
     set newname for datafile 2 to /oracle/data/file1.f; # rename datafile 2 as file1.f
     allocate ... 
     duplicate target database to newdb; 
}

Even though you issued set newname commands for all your datafiles, the duplicate command will fail because the duplicate filenames are still in use in the target database. Although datafile 1 in the target is not using /oracle/data/file2.f, and datafile 2 in the target is not using /oracle/data/file1.f, the target filename is used by one of the duplicate datafiles and so you must specify nofilenamecheck to avoid an error.

Note:

Only use DB_FILE_NAME_CONVERT without using either set newname or set auxname if all the datafiles will be converted by this parameter, i.e., all of the datafiles have the same suffix or prefix.  

Preparing the Auxiliary Instance for Duplication

Satisfy the following requirements before performing RMAN duplication:

Create an Oracle Password File for the Auxiliary Instance

For information about creating and maintaining Oracle password files, see the Oracle8i Administrator's Guide.

Create a Parameter File for the Auxiliary Instance

Create an init.ora file for the auxiliary instance and set the following required parameters:

Parameter   Specify:  

DB_NAME  

The same name that you use in the duplicate command.  

CONTROL_FILES  

See "Creating the Control Files".  

Optionally, set the following parameters:

Parameter   Specify:  

DB_FILE_NAME_CONVERT  

See "Renaming the Datafiles".  

LOG_FILE_NAME_CONVERT  

See "Creating the Online Redo Logs".  

Set other parameters, including the parameters that allow you to connect as SYSDBA through Net8, as needed. When duplicating to the same host or to a new host with a different filesystem, pay special attention to all parameters specifying pathnames.

Following are examples of the init.ora parameter settings for the duplicate database: 

DB_NAME=newdb
CONTROL_FILES=(/oracle/dup_prod/cf/cf1.f,/oracle/dup_prod/cf/cf2.log)
DB_FILE_NAME_CONVERT=(/oracle/prod/db,/oracle/dup_prod/db)
LOG_FILE_NAME_CONVERT=("/oracle/prod/log","/oracle/dup_prod/log")

See Also: For more information about Net8, see the Net8 Administrator's Guide.

Start the Auxiliary Instance

Before beginning RMAN duplication, use SQL*Plus to connect to the auxiliary instance and start it in NOMOUNT mode (specifying a parameter file if necessary). In this example, aux_pwd is the password for the user with SYSDBA authority and aux_str is the net service name for the auxiliary instance: 

SQL> connect sys/aux_pwd@aux_str
SQL> startup nomount pfile='/oracle/aux/dbs/initAUX.ora';

Because the auxiliary instance does not yet have a control file, you can only start the instance in NOMOUNT mode. Do not create a control file or try to mount or open the auxiliary instance.

Ensure Net8 Connectivity to the Auxiliary Instance

The auxiliary instance must be accessible via Net8. Before proceeding, use SQL*Plus to ensure that you can establish a connection to the auxiliary instance. Note that you must connect to the auxiliary instance with SYSDBA privileges, so a password file must exist.

Open the Target Database

Before beginning RMAN duplication, open the target database (specifying a parameter file if necessary) if it is not already open. For example, enter: 

SQL> startup pfile='/oracle/dbs/initPROD1.ora';

Start the RMAN Command Line Interface

Use one of the following methods to start the RMAN command line interface:

Connect at the O/S Command Line

You must connect to the auxiliary instance with SYSDBA privileges, so you must use a password file. To connect to the auxiliary instance, target instance, and recovery catalog, supply the following information when starting up Recovery Manager: 

% rman target sys/target_pwd@target_str catalog rman/cat_pwd@cat_str auxiliary  \ 
> sys/aux_pwd@aux_str

Where:

target_pwd  

The password for connecting as SYSDBA specified in the target database's orapwd file  

target_str  

The net service name for the target database  

cat_pwd  

The password for user RMAN specified in the recovery catalog's orapwd file  

cat_str  

The net service name for the recovery catalog database  

aux_pwd  

The password for connecting as SYSDBA specified in the auxiliary database's orapwd file.  

aux_str  

The net service name for the auxiliary database.  

Connect at the RMAN Prompt

You can start the RMAN command line interface without a connection to the auxiliary instance, and then use the connect auxiliary command at the RMAN prompt to make the auxiliary connection: 

% rman
RMAN> connect auxiliary sys/aux_pwd@aux_str
RMAN> connect target sys/target_pwd@target_str

RMAN> connect catalog rman/cat_pwd@cat_str

Make Sure You Have the Necessary Backups and Archived Redo Logs

Make sure you have backups all the datafiles in your target database. If you do not have backups of everything, the duplicate operation will fail. The database backup does not have to be a whole database backup: you can use a mix of full and incremental backups of individual datafiles.

Make sure that you have enough backups of all the archived redo logs necessary to recover to the desired time, SCN, or log sequence number.

Allocate Auxiliary Channels

Before issuing the duplicate command, allocate at least one auxiliary channel within the same run command. The channel type (disk or 'sbt_tape') must match the media where the backups of the target database are located. If the backups reside on disk, then the more channels you allocate, the faster the duplication will be. For tape backups, limit the number of channels to the number of devices available for the operation. 

run {
     # to allocate a channel of type 'sbt_tape' issue:
     allocate auxiliary channel ch1 type 'sbt_tape';

     # to allocate three auxiliary channels for disk issue (specifying whatever channel 
     # id that you want):
     allocate auxiliary channel aux1 type disk;
     allocate auxiliary channel aux2 type disk;
     allocate auxiliary channel aux3 type disk;
     . . .
}

Creating a Duplicate Database on a Local or Remote Host

When you create your duplicate database, you have the following options:

Duplicating a Database on a Remote Host with the Same Directory Structure

The simplest case is to duplicate your database to a different host and to use the exact same directory structure. In this case, you do not need to change the init.ora file or set new filenames for the duplicate database datafiles.

To create a duplicate database on a different host with the same filesystem:

  1. Use an O/S utility to copy your parameter file from its location in the target host directory structure to the same location in the duplicate host directory structure.

  2. Use SQL*Plus to start the duplicate instance without mounting it. For example, enter: 

    SQL> startup nomount pfile=initDUPDB.ora;
  3. Use SQL*Plus to open the target database if it is not already open. For example, enter: 

    SQL> startup pfile=initPROD1.ora;
  4. The auxiliary instance must be accessible via Net8. Before proceeding, use SQL*Plus to ensure that you can establish a connection to the auxiliary instance. Note that you must connect to the auxiliary instance with SYSDBA privileges, so a password file must exist.

  5. Use RMAN to connect to the target database, the duplicate database, and (if you use one) the recovery catalog database. In this example, connection is established without a recovery catalog using O/S authentication: 

    % rman target / auxiliary sys/sys_pwd@dupdb

    In this example, user SCOTT has SYSDBA privileges and a net service name is used for the target: 

    % rman auxiliary scott/tiger@dupdb target sys/sys_pwd@prod

    In this example, connection is established to three databases, all using net service names: 

    % rman catalog rman/rman@rcat target sys/sys_pwd@prod1 auxiliary scott/tiger@dupdb
  6. Perform the following operations:

    • Allocate at least one auxiliary channel.

    • Specify the nofilenamecheck parameter.

    For example, enter the following: 

    run { 
     allocate auxiliary channel ch1 type 'sbt_tape';
     duplicate target database to dupdb 
     nofilenamecheck;
}
  7. RMAN uses all available archived redo logs and incremental backups to perform incomplete recovery and then opens the database with the RESETLOGS option to create the online redo logs.

Duplicating a Database on a Remote Host with a Different Directory Structure

If you create your duplicate database on a host with a different filesystem, you need to change several init.ora file parameters and generate new filenames for the duplicate database datafiles.

Use LOG_FILE_NAME_CONVERT or the logfile clause to convert the online redo log filenames. Use DB_FILE_NAME_CONVERT, the set newname command, or the set auxname command for the datafile filenames.

See Also: For a table of the various datafile filename conversion options, see Table 10-2.

To duplicate a database with DB_FILE_NAME_CONVERT and LOG_FILE_NAME_CONVERT:

  1. Use an O/S utility to copy your parameter file from its location in the target host directory structure to the same location in the duplicate host directory structure. Make sure to set:

    • All *_DEST and *_PATH initialization parameters that specify a pathname.

    • DB_FILE_NAME_CONVERT so that it captures all the target datafiles and converts them appropriately, e.g., from tbs_* to duptbs_*.

    • LOG_FILE_NAME_CONVERT so that it captures all the online redo logs and converts them appropriately, e.g., log_* to duplog_*.

  2. Use SQL*Plus to start the duplicate instance without mounting it. For example, enter: 

    SQL> startup nomount pfile=initDUPDB.ora;
  3. Use SQL*Plus to open the target database if it is not already open. For example, enter: 

    SQL> startup pfile=initPROD1.ora;
  4. The auxiliary instance must be accessible via Net8. Before proceeding, use SQL*Plus to ensure that you can establish a connection to the auxiliary instance. Note that you must connect to the auxiliary instance with SYSDBA privileges, so a password file must exist.

  5. Use RMAN to connect to the target database, the duplicate database, and (if you use one) the recovery catalog database. In this example, connection is established without a recovery catalog using O/S authentication: 

    % rman target / auxiliary sys/sys_pwd@dupdb

    In this example, user SCOTT has SYSDBA privileges and a net service name is used for the target: 

    % rman auxiliary scott/tiger@dupdb target sys/sys_pwd@prod

    In this example, connection is established to three databases, all using net service names: 

    % rman catalog rman/rman@rcat target sys/sys_pwd@prod1 auxiliary scott/tiger@dupdb
  6. Issue the duplicate command. For example, enter the following: 

    run { 
     allocate auxiliary channel ch1 type 'sbt_tape';
     duplicate target database to dupdb;
}
  7. RMAN uses all available archived redo logs and incremental backups to perform incomplete recovery and then opens the database with the RESETLOGS option to create the online redo logs.

To duplicate a database with DB_FILE_NAME_CONVERT and the logfile clause:

Follow the same procedure for creating a duplicate database using the parameter LOG_FILE_NAME_CONVERT, but make the following substitutions:

To duplicate a database using the set newname command:

  1. Use an O/S utility to copy your parameter file from its location in the target host directory structure to the same location in the duplicate host directory structure. Set all *_DEST and *_PATH initialization parameters that specify a pathname.

  2. Use SQL*Plus to start the duplicate instance without mounting it: 

    SQL> startup nomount pfile=initDUPDB.ora;
  3. Use SQL*Plus to open the target database if it is not already open: 

    SQL> startup pfile=initPROD1.ora;
  4. The auxiliary instance must be accessible via Net8. Before proceeding, use SQL*Plus to ensure that you can establish a connection to the auxiliary instance. Note that you must connect to the auxiliary instance with SYSDBA privileges, so a password file must exist.

  5. Use RMAN to connect to the target database, the duplicate database, and (if you use one) the recovery catalog database. In this example, connection is established without a recovery catalog using O/S authentication: 

    % rman target / auxiliary sys/sys_pwd@dupdb

    In this example, user SCOTT has SYSDBA privileges and a net service name is used for the target: 

    % rman auxiliary scott/tiger@dupdb target sys/sys_pwd@prod

    In this example, connection is established to three databases, all using net service names: 

    % rman catalog rman/rman@rcat target sys/sys_pwd@prod1 auxiliary scott/tiger@dupdb
  6. Perform the following operations:

    • Allocate at least one auxiliary channel.

    • Specify the same number of redo log members and groups that are used in your target database.

    • Specify new filenames for the duplicate database datafiles.

    For example, enter the following: 

    run {  
     # allocate at least one auxiliary channel of type disk or tape 
     allocate auxiliary channel dupdb1 type 'sbt_tape'; 
     . . . 
     # set new filenames for the datafiles
     set newname for datafile 1 TO '$ORACLE_HOME/dbs/dupdb_data_01.f'; 
     set newname for datafile 2 TO '$ORACLE_HOME/dbs/dupdb_data_02.f'; 
     . . .
     # issue the duplicate command
     duplicate target database to dupdb 
     # create at least two online redo log groups
     logfile
       group 1 ('$ORACLE_HOME/dbs/dupdb_log_1_1.f', 
                '$ORACLE_HOME/dbs/dupdb_log_1_2.f') size 200K, 
       group 2 ('$ORACLE_HOME/dbs/dupdb_log_2_1.f', 
                '$ORACLE_HOME/dbs/dupdb_log_2_2.f') size 200K; 
}
  7. RMAN uses all available archived redo logs and incremental backups to perform incomplete recovery and then opens the database with the RESETLOGS option to create the online redo logs.

To duplicate a database using the set auxname command:

  1. Use an O/S utility to copy your parameter file from its location in the target host directory structure to the same location in the duplicate host directory structure. Set all *_DEST and *_PATH initialization parameters that specify a pathname.

  2. Use SQL*Plus to start the duplicate instance without mounting it: 

    SQL> startup nomount pfile=initDUPDB.ora;
  3. Use SQL*Plus to open the target database if it is not already open: 

    SQL> startup pfile=initPROD1.ora;
  4. The auxiliary instance must be accessible via Net8. Before proceeding, use SQL*Plus to ensure that you can establish a connection to the auxiliary instance. Note that you must connect to the auxiliary instance with SYSDBA privileges, so a password file must exist.

  5. Use RMAN to connect to the target database, the duplicate database, and (if you use one) the recovery catalog database. In this example, connection is established without a recovery catalog using O/S authentication: 

    % rman target / auxiliary sys/sys_pwd@dupdb

    In this example, user SCOTT has SYSDBA privileges and a net service name is used for the target: 

    % rman auxiliary scott/tiger@dupdb target sys/sys_pwd@prod

    In this example, connection is established to three databases, all using net service names: 

    % rman catalog rman/rman@rcat target sys/sys_pwd@prod1 auxiliary scott/tiger@dupdb
  6. Set the auxiliary names for your datafiles. For example, enter the following: 

    # set auxiliary names for the datafiles     
set auxname for datafile 1 to '/oracle/auxfiles/aux_1.f'; 
set auxname for datafile 2 to '/oracle/auxfiles/aux_2.f'; 
... 
set auxname for datafile n to '/oracle/auxfiles/aux_n.f';
  7. Perform the following operations:

    • Allocate at least one auxiliary channel.

    • Specify the same number of redo log members and groups that are used in your target database. 

      run {  
     # allocate at least one auxiliary channel of type disk or tape 
     allocate auxiliary channel dupdb1 type 'sbt_tape'; 
     . . . 
     # issue the duplicate command
     duplicate target database to dupdb 
     . . .
     # create at least two online redo log groups
     logfile
       group 1 ('$ORACLE_HOME/dbs/dupdb_log_1_1.f', 
                '$ORACLE_HOME/dbs/dupdb_log_1_2.f') size 200K, 
       group 2 ('$ORACLE_HOME/dbs/dupdb_log_2_1.f', 
                '$ORACLE_HOME/dbs/dupdb_log_2_2.f') size 200K; 
}
  8. RMAN uses all available archived redo logs and incremental backups to perform incomplete recovery and then opens the database with the RESETLOGS option to create the online redo logs.

  9. Un-specify the auxiliary names for your datafiles so that they will not be overwritten by mistake. For example, enter the following: 

    # un-specify auxiliary names for the datafiles
set auxname for datafile 1 to null; 
set auxname for datafile 2 to null; 
... 
set auxname for datafile n to null;

Creating a Duplicate Database on the Local Host

When creating a duplicate database on the same host as your target database, follow the same procedure as for duplicating to a remote host with a different directory structure ("Duplicating a Database on a Remote Host with a Different Directory Structure").

Note that you can duplicate your database to the same $ORACLE_HOME as your target, but you must convert the filenames using the same methods used for conversion on a separate host.

WARNING:

Do not use the nofilenamecheck option when duplicating to the same $ORACLE_HOME as your primary database. If you do, you may overwrite your target database files or cause the duplicate command to fail with an error.  

Duplication Scenarios

Following are some useful scenarios for creating a duplicate database:

Setting New Filenames Manually

This example assumes the following:

Resynchronizing the Duplicate Database with the Target Database

This example makes the same assumptions as in "Setting New Filenames Manually". Additionally, it assumes that you want to update your duplicate database daily so that it stays current with the target database. 

# start RMAN and then connect to the databases     
connect target /
connect catalog rman/rman@rcat
connect auxiliary sys/change_on_install@dupdb

# set auxiliary names for the datafiles only once     
set auxname for datafile 1 TO '$ORACLE_HOME/dbs/tbs_01.f'; 
set auxname for datafile 2 TO '$ORACLE_HOME/dbs/tbs_02.f'; 
set auxname for datafile 3 TO '$ORACLE_HOME/dbs/tbs_03.f'; 
set auxname for datafile 4 TO '$ORACLE_HOME/dbs/tbs_04.f'; 
set auxname for datafile 5 TO '$ORACLE_HOME/dbs/tbs_05.f'; 
set auxname for datafile 6 TO '$ORACLE_HOME/dbs/tbs_06.f'; 
set auxname for datafile 7 TO '$ORACLE_HOME/dbs/tbs_07.f'; 
set auxname for datafile 8 TO '$ORACLE_HOME/dbs/tbs_08.f'; 
set auxname for datafile 9 TO '$ORACLE_HOME/dbs/tbs_09.f'; 

# Create the duplicate database. Issue the same command daily
# to re-create the database, thereby keeping the duplicate 
# in sync with the target.
run { 
     # allocate auxiliary channels 
     allocate auxiliary channel dupdb1 type disk; 
     allocate auxiliary channel dupdb2 type disk; 
     allocate auxiliary channel dupdb3 type disk; 
     allocate auxiliary channel dupdb4 type disk; 
     duplicate target database to dupdb 
       logfile
         group 1 ('$ORACLE_HOME/dbs/log_1_1.f', 
                  '$ORACLE_HOME/dbs/log_1_2.f') size 200K reuse, 
         group 2 ('$ORACLE_HOME/dbs/log_2_1.f', 
                  '$ORACLE_HOME/dbs/log_2_2.f') size 200K reuse; 
}

Creating a Non-Current Duplicate Database

This example assumes the following:


Prev
Next
Oracle
Copyright © 1999 Oracle Corporation.
All Rights Reserved.

Library
Product
Contents
Index