Backup-based duplication uses an RMAN backup of the target (source) database as its source to create the data files in the auxiliary (destination) environment. There are two types of RMAN backup-based duplication:

1. No connection to the target database (or a recovery catalog) is required. This is referred to as targetless duplication. This technique only requires a connection to the auxiliary database. Targetless duplication is available in Oracle 11g release 2 and higher.
2. In some types of backup-based duplication, a connection to both the target database (or a recovery catalog) and the auxiliary database is required.

The main focus of this chapter is targetless duplication, where there is no requirement to be connected to the target (source) database. In other words, you only need a connection to the auxiliary (destination) database. The big advantage to backup-based duplication is that if you work in an environment where it’s not possible to have a simultaneous connection to both the target and the auxiliary database you can still duplicate a database provided you can copy an RMAN backup to the auxiliary database server (or provided the backup is on network-mounted storage readable from the auxiliary server). For example, in many environments, due to security rules, there is no network connectivity allowed from test environments to the production server.

The first section of this chapter outlines some basic troubleshooting techniques. If you’re already familiar with basic troubleshooting, then proceed directly to the second major section of this chapter, which deals with targetless duplication. The last section of this chapter details a few backup-based duplication scenarios that require a connection to both the target database (and/or recovery catalog) and the auxiliary database.

Basic Troubleshooting

Before getting started with examples of duplicating databases, it’s prudent to spend just a small amount of time going over some basic troubleshooting techniques, such as:

• Checking the syntax of an RMAN command
• Monitoring
• Logging RMAN output

Reviewing these techniques will save you a great deal of time when performing RMAN duplication operations. When you’re having issues and can’t get a command to run correctly, the first thing to do is check the syntax for accuracy. It may seem like a small thing, but one misplaced comma can cause hours of misplaced troubleshooting. If you’re not familiar with these basic troubleshooting techniques, now would be a good time to spend just a few minutes reviewing the material in this section.

Checking Syntax

As you’ll soon see, some of the RMAN DUPLICATE commands in coming examples can get quite lengthy, especially those using the SPFILE clause and specifying how to map different directory structures from the target to the auxiliary. With long DUPLICATE commands, it’s easy to miss a small detail and attempt to run a command that isn’t syntactically correct. In these situations, it’s handy to check first (before actually running the RMAN command) to see if the syntax is accurate. Fortunately, RMAN has a built-in CHECKSYNTAX clause that allows you to do this. You can use this clause in a couple of different ways.

From the Command Line

One method for using the CHECKSYNTAX clause is from the operating system command line. To do so, initiate RMAN with the CHECKSYNTAX clause:

$ rman checksyntax

While connected to RMAN in this mode, any commands that you issue aren’t executed; rather, RMAN only checks to see if the syntax is correct. For example

RMAN> duplicate database to TRG
backup location '/u01/rman/TRG'
nofilenamecheck;

If everything is correct, this line is displayed in the output:

The command has no syntax errors

The CHECKSYNTAX clause can be used for any RMAN command. Most RMAN commands aren’t very long, like BACKUP DATABASE. However, for lengthy DUPLICATE commands it’s prudent to first verify the syntax before attempting to run the command. This will eliminate a syntax error as being the cause of a problem.

From a Script

Another technique for invoking the CHECKSYNTAX functionality is to put the RMAN command in an operating system file and then use that file as input to the RMAN. For example, say you have a long RMAN command stored in a file named cmd.rc. You can quickly check for syntax issues like this:

$ rman checksyntax @cmd.rc

If successful, this message is displayed:

The cmdfile has no syntax errors

For lengthy RMAN commands, the CHECKSYNTAX clause provides you a quick way to determine if the issue is related to syntax of if it’s some other problem. Eliminating syntax-related issues quickly helps you focus on the root cause of the problem.

Monitoring Progress

Sometimes when duplicating a database invariably somebody will ask “Is it done yet?” This is especially true for large databases. In order to better report on RMAN operations, I recommend that before you start any job (DUPLICATE, BACKUP, RESTORE, and so on) you first set the NLS_DATE_FORMAT operating system variable, as this will provide RMAN timings down to the second:

$ export NLS_DATE_FORMAT='dd-mon-rrrr hh24:mi:ss'

Now RMAN will report down to the second when each operation took place. If you don’t set the prior variable then RMAN only reports the day, month, and year portion of the time. If RMAN operations are common in your environment then consider setting the NLS_DATE_FORMAT in an operating system startup file so that the variable is consistently set when you log on to a server.

Next to be covered are several methods for monitoring progress. First up is the operating system approach.

Operating System Approach

When a duplication operation is taking place, you can navigate to the directory (or directories) that contain the auxiliary database data files and use Linux/UNIX operating system utilities such as ls or du to manually view which data files have been restored and/or are currently being restored. For instance:

$ cd /u01/dbfile/TRG
$ ls -altr
ls -altr
total 2597754
drwxr-x---   4 oracle   dba              4 Dec 19 21:01 ..
drwxr-x---   2 oracle   dba             10 Jan  2 10:07 .
-rw-r-----   1 oracle   dba       19185664 Jan  2 10:07 control01.ctl
-rw-r-----   1 oracle   dba       19185664 Jan  2 10:07 control02.ctl
-rw-r-----   1 oracle   dba      524296192 Jan  2 10:07 system01.dbf
-rw-r-----   1 oracle   dba      524296192 Jan  2 10:07 sysaux01.dbf
-rw-r-----   1 oracle   dba      209723392 Jan  2 10:07 undotbs01.dbf
...

This approach is simple but very effective. This will give you an idea as to where RMAN is in the duplication process. If your target (source) database has hundreds of data files and you only see a handful of data files that are in the auxiliary destination directory, then you know there is a bit more work to be done.

If you’re in a Windows environment, you can view the file sizes from the Windows Explorer graphical tool, or use the DOS command-line DIR/O:-S command to view file sizes.

SQL Approach

The SQL*Plus script in this section will give you an idea of how long the duplication process has remaining. First, some background on how RMAN operates. RMAN will create at least one process, called a channel, when performing the duplication operation. You can also instruct RMAN to open more than one channel (see the section on “Parallelism” in Chapter 5 for details). Each channel has an associated database process that can be monitored through SQL*Plus. RMAN will open the channels, either on the target database or the auxiliary database depending on the type of duplication you're performing. Here are some guidelines regarding where RMAN will open the channels:

• When performing active duplication with the backup set format. Most of the work is done by the auxiliary database channels and therefore you should run the script in this section on the auxiliary database to view progress.
• When performing active duplication when image copies are specified. The work is done by target database channels, therefore run the script in this section on the target database to view progress. This is important to remember when building a standby from an active database using image copies; in this situation you’ll need to run the monitor script on the target database.
• When performing targetless duplication. All of the work is done on the auxiliary database and therefore the script should be run on the auxiliary database to view the progress of the duplication job.

Based on these listed rules, you’ll have to run the following SQL script to monitor RMAN progress either on the target or on the auxiliary, depending on where RMAN opens the channels:

SET LINES 132
COL opname        FORM A30 HEAD "Oper."
COL pct_complete  FORM 99.99 HEAD "% Comp."
COL start_time    FORM A15 HEAD "Start|Time"
COL hours_running FORM 9999.99 HEAD "Hours|Running"
COL minutes_left  FORM 999999 HEAD "Minutes|Left"
COL est_comp_time FORM A15 HEAD "Est. Comp.|Time"
--
SELECT sid, serial#, opname,
ROUND(sofar/totalwork*100,2) AS pct_complete,
TO_CHAR(start_time,'dd-mon-yy hh24:mi') start_time,
(sysdate-start_time)*24 hours_running,
((sysdate-start_time)*24*60)/(sofar/totalwork) - (sysdate-start_time)*24*60 minutes_left,
TO_CHAR((sysdate-start_time)/(sofar/totalwork) + start_time,'dd-mon-yy hh24:mi') est_comp_time
FROM  v$session_longops
WHERE opname LIKE 'RMAN%'
AND   opname NOT LIKE '%aggregate%'
AND   totalwork != 0
AND   sofar <> totalwork;

Here is some sample output that results when performing active duplication and running the script on the target database during an image copy–based duplication operation:

                                                  Start          Hours     Minutes Est. Comp.
 SID SERIAL#  Oper.                      % Comp.  Time           Running   Left    Time
---- -------  -------------------------- -------  -------------- --------- ------  --------------
  34   37827  RMAN: full datafile backup    5.60  31-dec-14 10:02      .01     11  31-dec-14 10:14
  51   10159  RMAN: full datafile backup   20.21  31-dec-14 10:02      .01      3  31-dec-14 10:05

The output doesn’t quite fit on the page, but if you have a wide terminal this will clearly show when the operation started, how long it’s been running, and how much time is remaining. The output also indicates two RMAN channels have been allocated on the target and that, as part of an image copy, RMAN is backing up the data files.

Capturing RMAN Output

The RMAN DUPLICATE command in particular can generate great volumes of output. Sometimes when troubleshooting it’s handy to capture all of the output in a file that can be used later to identify issues. You can either use the RMAN logging feature or use an operating system utility to capture the output.

RMAN Logging

RMAN logging can be enabled a couple of different ways. When connected to RMAN you can specify a log file to capture the output of your activities:

RMAN> spool log to '/u01/log/backup.log';
RMAN> backup database;
RMAN> spool log off;
Spooling for log turned off

You should now have a file in the /u01/log directory named backup.log that contains the output of the BACKUP command. Note that the directory /u01/log must exist on the server for this to work. You’ll have to modify this appropriately for your environment.

The other way to enable RMAN logging is from the command line:

$ rman target / log /u01/log/output.log

Now all output is captured for any subsequent RMAN commands in the /u01/log/output.log file.

Script Command

The Linux/UNIX script command enables the recording of all output printed to your terminal to additionally be captured in an operating system file. For instance, before connecting to RMAN and running a DUPLICATE command, first do this:

$ script dup.log
Script started, file is dup.log

Now connect to RMAN and run the DUPLICATE command:

$ rman target sys/foo@shrek:1521/TRG auxiliary sys/foo@shrek2:1521/TRG
RMAN> DUPLICATE TARGET DATABASE TO TRG
FROM ACTIVE DATABASE
USING COPY
NOFILENAMECHECK;

When finished, you can stop the script-capture process by pressing Ctrl+D or typing in exit, after which you should see this message:

Script done, file is dup.log

Now you can use an operating system utility such as vi or notepad to examine the contents of the script file. If you enlist the help of Oracle Support with an issue, a file such as this containing all the command output can be invaluable in troubleshooting problems.

Tee Command

The Linux/UNIX tee command is another useful way to record output displayed on the terminal into a file. Here’s an example of using tee:

$ rman target / | tee rman.log

Now any commands that you run will be captured in the rman.log file. When you exit RMAN, the rman.log file closes.

RMAN Command Output View

The V$RMAN_OUTPUT view contains messages recently reported by RMAN. It is an in-memory view that can hold a maximum of 32,768 rows. Information in this view is cleared out when you stop and start your database. Here’s a sample way to query this view:

set lines 132 pages 110
col output form a70
--
select sid, recid, output
from v$rman_output
order by recid;

Using V$RMAN_OUTPUT also has the advantage of being operating system agnostic. You can always query its output regardless of the operating system (Windows, Linux, Solaris, and so on).

Targetless Duplication

You can replicate a target (source) database using an RMAN backup when duplicating to an auxiliary (destination) database. In the scenarios described in this section, you are not required to connect to the target database or a recovery catalog when issuing the RMAN DUPLICATE command (hence the moniker targetless duplication). The basic idea here is to copy an RMAN backup to an auxiliary server (or storage that the auxiliary server has read access to) and create the auxiliary database directly from the backup. This is a simple and powerful technique for replicating a database. It is especially applicable where there’s no direct network connection between the target database and the auxiliary.

Next, several targetless duplication scenarios are described. Let’s get started with the simplest scenario, one in which the directory structure and database name are the same from the target to the auxiliary environment.

Directory Structure and Database Name Remain Identical

It is easiest to use the RMAN DUPLICATE command when you have the following scenario:

• Target (source) and auxiliary (destination) servers have the same directory structure, meaning that the directory locations on the target for data files, control files, and online redo logs are identical to the directory locations on the auxiliary server.
• Target and auxiliary database names are the same, meaning you don’t require the name of the auxiliary database to be different from the target.

In this situation, the basic idea is that you copy the RMAN backup files from the target (source) server to the auxiliary (destination) server and then issue the DUPLICATE command to create a copy of the target database on the auxiliary server. As shown in Figure 3-1, there are five steps required for this scenario. Notice this configuration doesn’t require a listener or Oracle Net connectivity. The auxiliary database simply requires read access to an RMAN backup of the target database. In this situation the RMAN backup is copied to a directory on the auxiliary server.

The first three steps are executed on the target server, and the last two steps are executed on the auxiliary server.

1. On the target server, connect to RMAN and back up the target (source) database plus archivelog:

   $ rman target /
   RMAN> backup database plus archivelog;

2. On the target server, copy the target RMAN backups to the auxiliary (destination) server. First locate the RMAN backups and then copy them to the destination server:

   RMAN> list backup;

   Here’s a partial listing of the output for my target database showing that the RMAN backup pieces (physical backup files) are in the /u01/rman/TRG directory:

   Piece Name: /u01/rman/TRG/TRGrman1_07pqj1um_1_1.bk

   Next I use the Linux/UNIX scp command to copy the RMAN backups from the source server to the destination server (you’ll have to modify this per the location of your backups on the source and destination servers):

   $ cd /u01/rman/TRG
   $ scp *.bk oracle@shrek2:/u01/rman/TRG

   As mentioned at the beginning of the chapter, another method to make the RMAN backup available to the auxiliary server would be to place the backups on storage that is readable from the auxiliary host. If this option is available it would save you from having to copy the backups (which takes time and storage).

3. On the target server, copy the target init.ora to the auxiliary server (you’ll have to modify this per the location of your backups on the source and destination servers). You can use either a server parameter file (SPFILE) or an init.ora file; here I’m using an SPFILE:

   $ cd $ORACLE_HOME/dbs
   $ scp spfileTRG.ora oracle@shrek2:$ORACLE_HOME/dbs

4. Now log on to the auxiliary server, connect to SQL*Plus, and start up the auxiliary database in nomount mode:

   $ sqlplus / as sysdba
   SQL> startup nomount;
   SQL> exit;

5. On the auxiliary server, connect to the auxiliary instance via RMAN and issue the DUPLICATE command:

   $ rman auxiliary /
   RMAN> duplicate database to TRG
   backup location '/u01/rman/TRG'
   nofilenamecheck;

Here’s a very small snippet of the large amount of output for this operation:

Starting Duplicate Db at...
contents of Memory Script:
...
Finished Duplicate Db...

When finished, you should have a database restored on the destination server that is an identical copy of the source database.

One important note: In the DUPLICATE command the NOFILENAMECHECK was included. When the target database and auxiliary database are on different servers, but have the same directory structure, then always include the NOFILENAMECHECK clause. If you don’t include NOFILENAMECHECK, then you’ll receive the following error:

RMAN-05001: auxiliary file name ... conflicts with a
file used by the target database

The NOFILENAMECHECK instructs RMAN not to check that the data file names are identical from the target to the auxiliary. If your target and auxiliary are on the same server, then you should not include the NOFILENAMECHECK, as you want RMAN to ensure that you don’t attempt to restore an auxiliary data file over the top of a live target database data file.

Directory Structure Identical and Database Name Different

In this scenario the target (source) server and auxiliary (destination) server have the same directory structures. However, you want to rename the database as part of duplicating the target to the auxiliary. As shown in Figure 3-2, there are six steps required for this scenario.

The first three steps are executed on the target server, and the last several steps are executed on the auxiliary server:

1. On the target server, connect to RMAN and back up the target (source) database plus archivelog:

   $ rman target /
   RMAN> backup database plus archivelog;

2. On the target server, copy the target RMAN backups to the auxiliary (destination) server. Start by verifying the location of the backups:

   RMAN> list backup;

   Here’s a partial listing of the output for my target database that shows the RMAN backup pieces (physical backup files) are in the /u01/rman/TRG directory:

   Piece Name: /u01/rman/TRG/TRGrman1_0fpqj2f9_1_1.bk

   Next use the Linux/UNIX scp command to copy the RMAN backups from the source server to the destination server (you’ll have to modify this per the location of your backups on the source and destination servers):

   $ cd /u01/rman/TRG
   $ scp *.bk oracle@shrek2:/u01/rman/TRG

3. On the target server, copy the target initialization file to the auxiliary server (you’ll have to modify this per the location of your backups on the source and destination servers). For this scenario I’m using a text-based init.ora file:

   $ cd $ORACLE_HOME/dbs
   $ scp initTRG.ora oracle@shrek2:$ORACLE_HOME/dbs

   If your source database uses an SPFILE, then you can create a text-based init.ora file from SQL*Plus, as follows:

   SQL> create pfile from spfile;

   This command will place a text-based initialization file with the name of init<SID>.ora in the ORACLE_HOME/dbs directory. If you don’t want the text-based file to be placed in that directory you can override the default behavior as follows:

   SQL> create pfile='/tmp/initTRG.ora' from spfile;

4. Now log on to the auxiliary server, connect to SQL*Plus, and start up the auxiliary database in nomount mode:

   $ sqlplus / as sysdba
   SQL> startup nomount;
   SQL> exit;

5. On the auxiliary server, connect to the auxiliary instance via RMAN and issue the DUPLICATE command. Notice that the database is being duplicated and given the new name of DUP:

   $ rman auxiliary /
   RMAN> duplicate database to DUP
   backup location '/u01/rman/TRG'
   nofilenamecheck;

   Here’s a very small snippet of the large amount of output for this operation:

   Starting Duplicate Db at...
   contents of Memory Script:
   ...
   Finished Duplicate Db...

6. When finished, the database name in this example is DUP, but it still has an instance name of TRG. To set the instance name to DUP, shut down the auxiliary database:

   RMAN> shutdown immediate;
   RMAN> exit;

In this scenario, RMAN will automatically create an SPFILE for you with the name of spfileTRG.ora. You must rename that file:

$ cd $ORACLE_HOME/dbs
$ mv spfileTRG.ora spfileDUP.ora

To complete this operation you need to set your ORACLE_SID to reflect the new database name of DUP:

$ export ORACLE_SID=DUP
$ sqlplus / as sysdba
SQL> startup;

Directory Structures and Database Names Different, Using SPFILE Clause

In this scenario the directory structure is different from the target (source) host to the auxiliary (destination) host, and the auxiliary database will be renamed (to be different from the target). The SPFILE clause will be used to facilitate this operation. When performing targetless duplication, in order to use the SPFILE clause, two conditions apply:

• Ensure that the target database is using an SPFILE when the backup is created, otherwise you’ll receive this error when duplicating to the auxiliary:

  RMAN-05569: SPFILE backup not found in /u01/rman/TRG

• The auxiliary database must be started with an init.ora file (and not an SPFILE), otherwise you’ll receive this error when duplicating:

  RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is
  started with spfile cannot use SPFILE clause

Figure 3-3 depicts the steps used in this scenario.

1. The first step is to verify the target database is using an SPFILE-based initialization file:

   SQL> select value from v$parameter where name='spfile';
   VALUE
   ------------------------------------------------------------
   /orahome/app/oracle/product/12.1.0.2/db_1/dbs/spfileTRG.ora

   You can also verify the target database is using an SPFILE via:

   SQL> show parameter spfile;

2. On the target server, connect to RMAN and back up the target (source) database plus archivelog. Additionally, a full backup of the database will automatically include the SPFILE. The SPFILE must be in the backup when using the SPFILE clause in the DUPLICATE command (seen in a subsequent step of this scenario):

   $ rman target /
   RMAN> backup database plus archivelog;

3. On the target server, locate the RMAN backups and copy them to the destination server:

   RMAN> list backup;

   Here’s a partial listing of the output for my target database showing that the RMAN backup pieces (physical backup files) are in the /u01/rman/TRG directory. Ensure that the output shows an SPFILE was included:

   Piece Name: /u01/rman/TRG/TRGrman1_0spq30vi_1_1.bk
   Piece Name: /u01/rman/TRG/TRGrman2_umpu0r0e_1_1.bk
   SPFILE Included: Modification time...

   Next use the Linux/UNIX scp command to copy the RMAN backups from the source server to the destination server (you’ll have to modify this per the location of your backups on the source and destination servers):

   $ cd /u01/rman/TRG
   $ scp *.bk oracle@shrek2:/u01/rman/TRG

4. On the target server, copy the target initialization file to the auxiliary server. In this scenario you must use an init.ora file when starting the auxiliary database, otherwise RMAN will throw the following error:

   RMAN-05537, DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause.

   Create a text-based init.ora file and then copy it to the auxiliary server:

   $ sqlplus / as sysdba
   SQL> create pfile from spfile;
   SQL> exit;
   $ cd $ORACLE_HOME/dbs
   $ scp initTRG.ora oracle@shrek2:$ORACLE_HOME/dbs

5. Now log on to the auxiliary server, connect to SQL*Plus, and start up the auxiliary database in nomount mode:

   $ sqlplus / as sysdba
   SQL> startup nomount;
   SQL> exit;

6. Connect to the auxiliary database and issue the DUPLICATE command. Notice how the DUPLICATE command specifies the new locations for control files (PARAMETER_VALUE_CONVERT), data files (DB_FILE_NAME_CONVERT), and online redo log files (LOG_FILE_NAME_CONVERT). You’ll have to modify this statement to reflect the directory structures in your environment. These directories must exist on the auxiliary server (RMAN doesn’t create the directories for you):

   $ rman auxiliary /
   RMAN> DUPLICATE TARGET DATABASE TO DUP
   BACKUP LOCATION '/u01/rman/TRG'
   SPFILE
     PARAMETER_VALUE_CONVERT
   '/u01/dbfile/TRG', '/u01/dbfile/DUP'
     SET DB_FILE_NAME_CONVERT
   '/u01/dbfile/TRG', '/u01/dbfile/DUP'
     SET LOG_FILE_NAME_CONVERT
   '/u01/oraredo/TRG', '/u01/oraredo/DUP';

   When finished you should have a database with the new name of DUP, but the instance is currently running with the old name of TRG. You should also have an SPFILE that is named spfileTRG.ora.

7. To start your database with the new name of DUP, shut down the database, rename the SPFILE, export your ORACLE_SID to reflect the new instance name, and then restart your database:

   RMAN> shutdown immediate;
   RMAN> exit
   $ cd $ORACLE_HOME/dbs
   $ mv spfileTRG.ora spfileDUP.ora
   $ export ORACLE_SID=DUP
   $ sqlplus / as sysdba
   SQL> startup;

Keep in mind when using the SPFILE clause that you can specify other initialization parameters for which you require different values from the target to the auxiliary. For example, this next bit of code also sets the values of MEMORY_TARGET, MEMORY_MAX_TARGET, and SHARED_POOL_SIZE. For instance:

RMAN> DUPLICATE TARGET DATABASE TO DUP
BACKUP LOCATION '/u01/rman/TRG'
SPFILE
  PARAMETER_VALUE_CONVERT
'/u01/dbfile/TRG', '/u01/dbfile/DUP'
  SET DB_FILE_NAME_CONVERT
'/u01/dbfile/TRG', '/u01/dbfile/DUP'
  SET LOG_FILE_NAME_CONVERT
'/u01/oraredo/TRG', '/u01/oraredo/DUP'
  SET MEMORY_TARGET '800M'
  SET MEMORY_MAX_TARGET '800M'
  SET SHARED_POOL_SIZE '100M';

After the duplication process is complete, you can verify the memory settings from SQL*Plus:

SQL> show parameter shared_pool_size;
NAME             TYPE        VALUE
---------------- ----------- -------
shared_pool_size big integer 100M

Directory Structures and Database Names Different, Not Using SPFILE

In this scenario, your target (source) and auxiliary (destination) servers have different directory structures, and the auxiliary database is renamed. Additionally, the target database does not use an SPFILE, so you don’t have the option of using the SPFILE clause of the DUPLICATE command.

1. On the target server, connect to RMAN and back up the target (source) database:

   $ rman target /
   RMAN> backup database plus archivelog;

2. On the target server, locate the RMAN backups and copy them to the destination server:

   RMAN> list backup;

   Here’s a partial listing of the output for my target database showing that the RMAN backup pieces (physical backup files) are in the /u01/rman/TRG directory:

   Piece Name: /u01/rman/TRG/TRGrman1_0fpqj2f9_1_1.bk

   On the target server, use the Linux/UNIX scp command to copy the RMAN backups from the source server to the destination server (you’ll have to modify this per the location of your backups on the source and destination servers):

   $ cd /u01/rman/TRG
   $ scp *.bk oracle@shrek2:/u01/rman/TRG

3. On the target server, copy the target database init.ora file to the auxiliary server. Notice that the init.ora file is renamed during the copying to initDUP.ora:

   $ cd $ORACLE_HOME/dbs
   $ scp initTRG.ora oracle@shrek2:$ORACLE_HOME/dbs/initDUP.ora

4. Now on the auxiliary database server, modify the initDUP.ora file to reflect the new directory structure. For this example, I’ve modified the CONTROL_FILES and DB_NAME parameters as follows:

   control_files='/u01/dbfile/DUP/control01.ctl','/u01/dbfile/DUP/control02.ctl'
   db_name='DUP'

5. Next, export the ORACLE_SID to reflect the new database name:

   $ export ORACLE_SID=DUP

   Now start up the auxiliary in nomount mode:

   $ sqlplus / as sysdba
   SQL> startup nomount;
   SQL> exit;

6. Connect to the auxiliary database via RMAN and issue the DUPLICATE command:

   $ rman auxiliary /
   RMAN> DUPLICATE TARGET DATABASE TO DUP
   BACKUP LOCATION '/u01/rman/TRG'
     DB_FILE_NAME_CONVERT '/u01/dbfile/TRG','/u01/dbfile/DUP'
                         ,'/u02/dbfile/TRG','/u02/dbfile/DUP'
                         ,'/u03/dbfile/TRG','/u03/dbfile/DUP'
   LOGFILE GROUP 1('/u01/oraredo/DUP/redo01a.rdo') SIZE 50m,
           GROUP 2('/u01/oraredo/DUP/redo02a.rdo') SIZE 50m;

When finished you should have a fully functioning copy of the target database on the auxiliary server. Note that even though the SPFILE clause wasn’t specified in this scenario, RMAN will still create an SPFILE for the auxiliary database. As a result, you may see this in the output:

Cannot remove created server parameter file

This is just an informational message and nothing to worry about.

Transforming Directory Names via Initialization File

Instead of specifying in the DUPLICATE command the transformation of the target directory structure to the auxiliary directory structure, you can place the necessary transformation in the auxiliary initialization file directly. For example, the following modifications to the auxiliary initialization file (initDUP.ora in this example) instruct RMAN where to place the control files (CONTROL_FILES), how to transform data file names (DB_FILE_NAME_CONVERT), and how to transform online redo log file names (LOG_FILE_NAME_CONVERT):

db_name=DUP
#
control_files='/u01/dbfile/DUP/control01.ctl',
'/u02/dbfile/DUP/control02.ctl'
#
db_file_name_convert= ('/u01/dbfile/TRG','/u01/dbfile/DUP','/u02/dbfile/TRG','/u02/dbfile/DUP',
'/u03/dbfile/TRG','/u03/dbfile/DUP')
#
log_file_name_convert=('/u01/oraredo/TRG','/u01/oraredo/DUP',
'/u02/oraredo/TRG','/u02/oraredo/DUP')

And now start up your database in nomount mode:

$ export ORACLE_SID=DUP
$ sqlplus / as sysdba
SQL> startup nomount;
SQL> exit;

Next, use RMAN to connect to the auxiliary database and issue the DUPLICATE command:

$ rman auxiliary /
RMAN> DUPLICATE TARGET DATABASE TO DUP
BACKUP LOCATION '/u01/rman/TRG';

When finished, you should see this at the bottom of the lengthy output:

Finished Duplicate Db at...

Now you should stop and start your duplicated database and inspect the alert.log file. You may see an error such as this:

ORA-01110: data file 201: '/u01/dbfile/DUP/temp01.dbf'

To resolve this either move or remove the existing temporary tablespace temp file using operating system commands:

$ mv /u01/dbfile/DUP/temp01.dbf /u01/dbfile/DUP/tempo01.old.dbf

Then stop and start the database. When starting, Oracle will detect that the temporary tablespace temp file is missing and recreate it.

Transforming Directory Names using SET NEWNAME

Another technique for instructing RMAN to transform directory names is with the SET NEWNAME command. This command must be encapsulated within an RMAN RUN{} block. Before performing this operation, first verify your target database data file numbers and corresponding names:

$ rman target /
RMAN> report schema;

In the following output, take note of the file number and data file name; this will provide the mapping used subsequently with the SET NEWNAME command:

File Size(MB) Tablespace  RB segs Datafile Name
---- -------- ----------  ------- -----------------------------
1    500      SYSTEM      YES     /u01/dbfile/TRG/system01.dbf
2    500      SYSAUX      NO      /u01/dbfile/TRG/sysaux01.dbf
3    200      UNDOTBS1    YES     /u01/dbfile/TRG/undotbs01.dbf
4    15       USERS       NO      /u01/dbfile/TRG/users01.dbf
5    10       REPDATA     NO      /u01/dbfile/TRG/repdata.dbf
6    10       REPIDX      NO      /u01/dbfile/TRG/repidx.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace  Maxsize(MB) Tempfile Name
---- -------- ----------  ----------  --------------------------
1    500      TEMP        500         /u01/dbfile/TRG/temp01.dbf

On the auxiliary host, set your ORACLE_SID to the name of the new database:

$ export ORACLE_SID=DUP

Next, modify the initialization file so that it reflects the new name of the database and the directories for the control files (in this example the initialization file name is initDUP.ora):

db_name=DUP
#
control_files='/u01/dbfile/DUP/control01.ctl','/u02/dbfile/DUP/control02.ctl'

And now start up the auxiliary database in nomount mode:

$ sqlplus / as sysdba
SQL> startup nomount;
SQL> exit;

Next, use RMAN to connect to the auxiliary database and issue the SET NEWNAME and DUPLICATE commands with a RUN{} block:

$ rman auxiliary /

Now run the following code. Notice the new directory location has been specified for each data file (TRG has been changed to DUP):

RMAN> RUN
{
SET NEWNAME FOR DATAFILE 1 TO '/u01/dbfile/DUP/system01.dbf';
SET NEWNAME FOR DATAFILE 2 TO '/u01/dbfile/DUP/undotbs01.dbf';
SET NEWNAME FOR DATAFILE 3 TO '/u01/dbfile/DUP/sysaux01.dbf';
SET NEWNAME FOR DATAFILE 4 TO '/u01/dbfile/DUP/users01.dbf';
SET NEWNAME FOR DATAFILE 5 TO '/u01/dbfile/DUP/repdata.dbf';
SET NEWNAME FOR DATAFILE 6 TO '/u01/dbfile/DUP/repidx.dbf';
SET NEWNAME FOR TEMPFILE 1 TO '/u01/dbfile/DUP/temp01.dbf';
DUPLICATE TARGET DATABASE TO DUP
BACKUP LOCATION '/u01/rman/TRG'
LOGFILE GROUP 1 ('/u01/oraredo/DUP/redo01.rdo') SIZE 50M,
        GROUP 2 ('/u01/oraredo/DUP/redo02.rdo') SIZE 50M;
}

When finished you should see the following line at the bottom of the lengthy output:

Finished Duplicate Db at...

Shell Scripting the Duplication Process

Oftentimes you’ll have the requirement to automate the duplication process. For example, in one environment at work the development team regularly requests that I refresh a development copy of the production database to the test environment. I use the Linux/UNIX cron utility to schedule the following tasks:

• Copy the latest RMAN target database backup from production to the auxiliary test server
• Copy an initialization file from the target to the auxiliary server
• Run a Bash shell script (on the auxiliary server) to refresh the database

Here are the contents of the Bash shell script that automate the duplication of the target database:

#!/bin/bash
#---------------------------------
export ORACLE_SID=TRG
#
sqlplus -s /nolog <<EOF
connect / as sysdba;
shutdown immediate;
startup nomount;
exit;
EOF
#---------------------------------
rman nocatalog <<EOF
connect auxiliary /
duplicate database to TRG
backup location '/u01/rman/TRG'
nofilenamecheck;
exit;
EOF
#---------------------------------
exit 0

The shell script needs to exist on the auxiliary (destination) database server. Ensure you make the shell script executable. Assuming the shell script name is dup.bsh, here’s an example of making the shell script executable:

$ chmod +x dup.bsh

And now you should be able to run the script:

$ dup.bsh

When RMAN is finished duplicating the database, you should see the following line:

Recovery Manager complete.

You should now have a database that has been duplicated from the target database RMAN backups.

Sometimes with large databases it can take a while for the shell script to complete. When executing the shell script from the command line, it’s useful to use the Linux/UNIX nohup command. This will allow the shell script to run in the background. Sometimes it’s advantageous to run a script in the background, as that will prevent the shell script from being terminated if the server is configured to automatically terminate what appears to be a process with no activity. Here’s an example of using nohup:

$ nohup dup.bsh &

Now you can monitor the progress of the duplication job via the Linux/UNIX tail command:

$ tail -f nohup.out

The -f switch instructs the tail command to continuously display the last several lines of the output on your terminal. To exit the continuous tailing process, enter a Ctrl+C.

Duplicating and Stopping Recovery at a Specific Time

It’s possible to specify a specific point-in-time recovery when duplicating a database. You may want to do this because you want the restoration point to stop at a specific point (like a baseline that you’ve established for testing). First, start up the auxiliary (destination) database in nomount mode:

$ sqlplus / as sysdba
SQL> startup nomount;
SQL> exit;

Next, connect via RMAN to the auxiliary:

$ rman auxiliary /

This next bit of code specifies that the DUPLICATE command should apply all transactions that occurred before the specified point in time:

RMAN> DUPLICATE TARGET DATABASE TO TRG
BACKUP LOCATION '/u01/rman/TRG'
db_file_name_convert '/u01/dbfile/TRG','/u01/dbfile/DUP'
                    ,'/u02/dbfile/TRG','/u02/dbfile/DUP'
                    ,'/u03/dbfile/TRG','/u03/dbfile/DUP'
logfile group 1
  ('/u01/oraredo/DUP/redo01a.rdo',
   '/u02/oraredo/DUP/redo01b.rdo') size 50m,
group 2
('/u01/oraredo/DUP/redo02a.rdo',
 '/u02/oraredo/DUP/redo02b.rdo') size 50m
UNTIL TIME "TO_DATE('16-dec-2014 09:00:00', 'dd-mon-rrrr hh24:mi:ss')";

Restarting Duplication

One handy aspect of RMAN is that if you’re restoring a large database and there’s some sort of failure (e.g., power outage) that causes the restore to abort, when you restart the restore operation RMAN will not restore files that have already been successfully restored. RMAN checks for files in the expected location and expected information in the data file header and if already present, RMAN will not restore those files. This is known as restore optimization.  For operations initiated with the RESTORE command, you can override restore optimization with the FORCE option. This forces RMAN to restore a file even if it exists in the expected location.

The restore optimization feature also applies to RMAN database duplication. If the duplication job unexpectedly aborts during the replication process you can simply rerun the DUPLICATE command and RMAN will not restore data files that were previously successfully restored. Thus, if you had a DUPLICATE job that was 90% complete, and the server crashed, when it comes back online, you can rerun the DUPLICATE command and RMAN will restore the remaining 10% of the data files.

When rerunning the DUPLICATE command, if RMAN detects there are data files that have already been restored, it will display a message similar to this in the output:

skipping datafile 1; already restored to file /u01/dbfile/TRG/system01.dbf
skipping datafile 4; already restored to file /u01/dbfile/TRG/users01.dbf

One caveat is that unlike operations initiated with the RESTORE command, with the DUPLCIATE command there is no way to force RMAN to restore files that have previously been successfully restored. If you need to force RMAN to restore a file, then first delete it.

Restricting Access after Duplication

By default, when you duplicate a database, as the last step RMAN will open the database. You may not want this behavior if you don't immediately want the duplicated database to be available for use. For example, you may first want to verify the duplication was successful before you open the database for use. In this situation use the NOOPEN clause. For example, first connect to the auxiliary database:

$ rman auxiliary /

Then issue the DUPLICATE command with NOOPEN:

RMAN> duplicate database to TRG
noopen
backup location '/u01/rman/TRG'
nofilenamecheck;

When finished, you have a duplicate of the target database, but the database is placed in mount mode:

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

When you’re ready you can open the database for use. Notice that you must use the RESETLOGS clause when you open the database:

SQL> alter database open resetlogs;

Another option you have is to have RMAN open the database in restricted mode. In this way, only users with the restricted session privilege (like users with the DBA role assigned to them) can connect to the database. First, connect to the auxiliary database:

$ rman auxiliary /

Then issue the DUPLICATE command with OPEN RESTRICTED clause:

RMAN> duplicate database to TRG
open restricted
backup location '/u01/rman/TRG'
nofilenamecheck;

In this mode, RMAN issues an ALTER SYSTEM ENABLE RESTRICTED SESSION command before opening the database. You can verify that logins are restricted via:

RMAN> select status, logins from v$instance;

STATUS       LOGINS
------------ ----------
OPEN         RESTRICTED

When desired you can enable normal logons via:

RMAN> alter system disable restricted session;

Scenarios Requiring Connections to Target

All of the previous examples in this chapter have only required a connection to the auxiliary database to perform backup-based duplication. Having said that, there are some types of backup-based duplication scenarios that require a connection to both the target database and the auxiliary database. An example of this is replicating and stopping the restoration process at a specific log sequence number or at a restore point. If you attempt to restore to a sequence number or restore point without a connection to both the target and the auxiliary database, RMAN will throw an RMAN-05542 error indicating that only UNTIL TIME can be used with DUPLICATE without a target (and/or recovery catalog) connection. Examples of sequence and restore-point duplication are detailed in the following sections.

UNTIL Sequence

Before performing a log sequence–based recovery, verify which archive redo logs are included in the backup and determine which archive redo log you want to recover up to (but not including). You can verify the archive redo logs included in a backup by issuing the following on the target database:

RMAN> list backup of archivelog all;

Here is some sample output:

List of Archived Logs in backup set 15
  Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
  ---- ------- ---------- --------- ---------- ---------
  1    28      351977     19-DEC-14 352064     19-DEC-14

To specify a log sequence number at which the restoration process should stop you must be connected to both the target (source) and the auxiliary (destination) databases. If you attempt to duplicate to a log sequence number with just a connection to the auxiliary database you’ll receive the following error message:

RMAN-05542: Only UNTIL TIME can be used with DUPLICATE
without TARGET and CATALOG connections

In this scenario, on the auxiliary server initiate a connection to both the target database and the auxiliary database. This requires that Oracle Net connectivity exists between the two servers (see Chapter 6 for details on Oracle Net):

$ rman target sys/foo@TRG auxiliary sys/foo

Next, issue the DUPLICATE command and specify a log sequence to restore up to (but not including). This example restores up to but not including the specified log sequence number:

DUPLICATE TARGET DATABASE TO TRG
BACKUP LOCATION '/u01/rman/TRG'
db_file_name_convert '/u01/dbfile/TRG','/u01/dbfile/DUP'
                    ,'/u02/dbfile/TRG','/u02/dbfile/DUP'
                    ,'/u03/dbfile/TRG','/u03/dbfile/DUP'
logfile group 1
  ('/u01/oraredo/DUP/redo01a.rdo',
   '/u02/oraredo/DUP/redo01b.rdo') size 50m,
group 2
('/u01/oraredo/DUP/redo02a.rdo',
 '/u02/oraredo/DUP/redo02b.rdo') size 50m
UNTIL SEQUENCE 28;

After the job is finished, you should see this at the bottom of the output:

Finished Duplicate Db at...

If you have a recovery catalog in place, it’s also possible to restore until a sequence while connected to the recovery catalog and the auxiliary database. For example:

$ rman auxiliary / catalog rcat/foo@cat

Next, issue the DUPLICATE command. Notice that the syntax here does not use the keyword TARGET:

RMAN> DUPLICATE DATABASE TRG TO TRG
BACKUP LOCATION '/u01/rman/TRG'
db_file_name_convert '/u01/dbfile/TRG','/u01/dbfile/DUP'
                    ,'/u02/dbfile/TRG','/u02/dbfile/DUP'
                    ,'/u03/dbfile/TRG','/u03/dbfile/DUP'
logfile group 1
  ('/u01/oraredo/DUP/redo01a.rdo',
   '/u02/oraredo/DUP/redo01b.rdo') size 50m,
group 2
('/u01/oraredo/DUP/redo02a.rdo',
 '/u02/oraredo/DUP/redo02b.rdo') size 50m
UNTIL SEQUENCE 28;

The key here is that the UNTIL clause requires a connection to the target (or recovery catalog) database along with a connection to the auxiliary database.

UNTIL Restore Point

This example uses a restore point to recover to a point in time. A restore point is a pointer to a system change number (SCN). Recall that an SCN is an internal counter that Oracle uses to assign a sequential number to every change that occurs in the database.

1. First, on the target (source) database, take an RMAN backup as follows:

   RMAN> backup database
   include current controlfile
   plus archivelog;

2. Next, create a restore point on the target database.

   SQL> create restore point my_rp;

3. Now back up all of the archive redo logs again, which will ensure that you have any archive redo logs required to restore up to the SCN recorded by the restore point:

   RMAN> backup archivelog all;

4. Then use the Linux/UNIX scp command to copy the RMAN backups from the target server to the auxiliary server (you’ll have to modify this per the location of your backups on the source and destination servers). This is initiated from the target server:

   $ cd /u01/rman/TRG
   $ scp *.bk oracle@shrek2:/u01/rman/TRG

5. Copy the initialization file from the target to the auxiliary. You can use either an SPFILE or a text-based init.ora file. Here’s an example of using the Linux/UNIX scp command for my environment (initiated from the target server):

   $ cd $ORACLE_HOME/dbs
   $ scp $ORACLE_HOME/dbs/initTRG.ora oracle@shrek2:$ORACLE_HOME/dbs

6. Then on the auxiliary server, start up the auxiliary database in nomount mode:

   $ sqlplus / as sysdba
   SQL> startup nomount;

7. On the auxiliary server, connect to the target and the auxiliary databases. When using an RMAN backup as the source and performing an UNTIL SEQUENCE duplication, a connection to both the target and the auxiliary is required:

   $ rman target sys/foo@shrek:1521/TRG auxiliary sys/foo

   This connection can be made from either the target, the auxiliary, or a remote client.

8. Next issue the DUPLICATE command specifying a restore point. In this example the restore point is my MY_RP:

   RMAN> DUPLICATE TARGET DATABASE TO TRG
   BACKUP LOCATION '/u01/rman/TRG'
   UNTIL RESTORE POINT my_rp
   NOFILENAMECHECK;

At this point you may see the following errors:

RMAN-03002: failure of Duplicate Db command at ...
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-06026: some targets not found - aborting restore
RMAN-06024: no backup or copy of the control file found to restore

This output indicates that RMAN can’t retrieve the control file required or archive redo logs required from the backups. For example, if you create a restore point before you take a backup of the control file and data files, RMAN can’t find a control file to restore that is prior to when the restore point was created.

In this situation, ensure that you have taken the RMAN backup before you created the restore point, and also have backed up any archive redo logs required to restore up to the SCN recorded in the restore point.

To further troubleshoot this issue, run the following on the target database:

RMAN> run {
set until restore point my_rp;
restore controlfile preview;
}

Inspect the output of the previous command. Ensure that the backups required to restore the data files and archived redo logs exist on the auxiliary server.

Summary

This chapter detailed using an RMAN backup as the source for the duplication operation. There are two types of backup-based duplication:

• Targetless duplication, which requires no connection to the target database
• Non-targetless duplication, which does require a target connection (and/or recovery catalog)

Most of the chapter discussed various targetless duplication scenarios. This is the simplest method for duplicating a database. This type of duplication relies only on an RMAN backup for the source of the database being copied. There is no need to connect to the target and/or a recovery catalog while performing targetless duplication.

This type of duplication is especially handy in environments where it’s not possible to connect to the target and the auxiliary at the same time. This could be due to security requirements, and therefore no direct Oracle Net connection is possible between the target and the auxiliary.