This article describes the Database Duplication feature of Oracle 10g. The article will first describe the database duplication in detail. Afterwards, issues specific on Windows will be discussed and addressed.

The article is based on the following environment:

Source Database:

• Windows XP
• Oracle 10g XE
• Oracle Installation in D:\oraclexe

Destination Database:

• Windows XP
• Oracle 10g XE
• Oracle Installation in C:\oraclexe 

Using the Database Duplication Feature

The Database Duplicate feature is implemented with RMAN (Recovery Manager). The powerful backup and recovery utility of Oracle.

Because Oracle XE does not allow starting multiple instances on the same server, it is necessary to duplicate to another server. Oracle XE creates an instance called XE by default. It is completely OK to duplicate to this instance. However, in this article, an alternate instance, NBXE, will be created and duplicated to. This showed that even Oracle XE disallows to start multiple instance, it is allowed to create as many instance as one like. The procedures of creating an Oracle Instance is stated in the section “Oracle Instance Creation, on Windows”.

Preparing the Target Database

The source database, a little bit counter-intuitive, is called the Target Database. From now on, source and target mean the same thing in this article . The preparation is simple: you need a recoverable backup by RMAN. This means you need:

• a full database backup, or a INCREMENTAL level 0 backup and any higher level backup with RMAN
• backup of archivelogs with RMAN 
• make sure the instance is accessible as SYSDBA on the client workstation where RMAN is run 

I assume you know how to do the backup with RMAN. You may need to force archive log switch several times before backup the archivelog. This ensures that very recent updates are included in the backup.

SQL> alter system switch logfile;
System altered.

In RMAN, backup all archivelog

RMAN> backup as compressed backupset
2> archivelog all;

Preparing the Auxiliary Database

The destination database is called the Auxiliary Database. From now on, destination and auxiliary are the same. You need to:

• make sure all the backup of the Target Database are available in the same location
• prepare and start an Oracle instance — nomount is sufficient
• prepare for file name conversion in the instance
• make sure the instance is accessible as SYSDBA on the client workstation where RMAN is run

Except for the first point, see section “Specific Issues — Mostly Windows” if you don't understand.

Backup Availability in Auxiliary Database

Copy, transfer, share, export, mount, or use whatever method to make sure the backup is available to the target database. This can be disk file, or tape backup. Unless you are using Windows, this task is a piece of cake.

Database Duplication

It is possible to run RMAN anywhere, as long as it is possible to connect to both instances as SYSDBA. Anyway, it seems most people will normally like to connect from the source instance. So, on source machine, as Oracle, connect to both instance and execute the duplication command. This is easy:

C:\oraclexe\app\oracle\product\10.2.0\server\BIN>rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 4 12:24:03 2007

Copyright © 1982, 2005, Oracle.  All rights reserved.

connected to target database: XE (DBID=2484850100, not open)

RMAN> connect auxiliary sys@nbxe

auxiliary database Password:
connected to auxiliary database: NBXE (not mounted)

Now that you have connected to both instance. You can check the backup configuration if you like, and then you can run the backup.

RMAN> show all
2> ;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\SEVER\DATABASE\SNCFXE.ORA'; # default

RMAN> duplicate target database to nbxe;

Starting Duplicate Db at 04-MAY-07
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=35 devtype=DISK

contents of Memory Script:
{
   set until scn  1904253243;
   set newname for datafile  1 to
 ”C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF”;
…

RMAN will generate “Memory Scripts” which will be executed automatically for database duplication. As you can see, it also performs data file name conversion as instructed in the auxiliary database spfile. The whole process is lengthy and will not be copied and pasted here. When the execution completed, the auxiliary database becomes a duplicate of the target.

Please note that RMAN will shutdown and restart the auxiliary instance in normal mode — make sure you have no connection to that. This is more possible than you are thinking, because you would most likely have a connection on the destination machine for starting the instance .

Specific Issues — Mostly Windows

In this section, some specific issues will be discussed. This are mostly windows related, but not 100%.

Oracle Instance Creation, on Windows

Windows Specific Tasks

Windows gives services a very special status: they are called Services. They can controlled from a Services applet in the “Microsoft Management Console”, and they are normally run by a special “System” account… All these mean that it there is an additional task if you want to create a new Oracle instance on Windows.

You can configure two aspects of how the Oracle service behaves: whether it starts on boot, and whether to start the instance. Because I won't always use Oracle, I would say NO to both. In order to create the Oracle service for an instance NBXE, execute:

c:> oradim -new -sid NBXE -startmode manual -srvcstart demand
Instance created.

I would also like to disable the default XE instance

C:> oradim -edit -sid xe -startmode manual -srvcstart demand

Prepare an SPFILE

Since there is an existing instance on the XE installation (SID also called XE), it is most natural to create the SPFILE out of the XE instance. In SQLPLUS:

SQL> create pfile from spfile='C:\oraclexe\app\oracle\product\10.2.0\server\database\spfileXE.ora'

File created. 

(Yes, it is no longer in the $ORACLE_HOME/dbs directory by default, although you can still put it there.)

• Rename the file according to the instance name. In this example, it is called initNBXE.ora.
• Change the content of the file by replacing those XE and xe which means the instance name with NBXE and nbxe, respectively.
• Create the folders needed to store your files.

In order to generate to an updated spfile, you can execute the following command. But you don't have to do this now, because we are adding parameters for file name conversion.

SQL> create spfile from pfile='C:\oraclexe\app\oracle\product\10.2.0\server\database\initNBXE.ora'

File Name Conversion

You may want to place the data files and log files differently in the target and auxiliary database. For example, the files are in D: drive in target. However, there is no D: drive in auxiliary. I must put them in C:.

File name conversion uses two parameters. The values are key-value pairs of conversion pattern, corresponding to the file name pattern in source and destination respectively. In my case, I want:

*.db_file_name_convert=('D:', 'C:')
*.log_file_name_convert=('D:', 'C:') 

It seems the parameters  cannot be modified using 'ALTER SYSTEM' statement, even for scope=spfile. I am not sure whether this is my fault or a bug or something else…

After adding this to initNBXE.ora, it is time to convert it back to an spfile.

Backup File Availability

The backup files are stored in D: in source, but I don't have a D: in target. You may think that the easiest solution is to map the drive on target. You are right … and wrong.

Mapping a drive is easy. But it will NOT be available for the Oracle instance! Windows has a strange feature: mapped drives are login session specific. Therefore, whatever drives you map from an login session is not accessible from Oracle, which is run under the System account.

(Oracle Service Account Setup - 74949.jpg ) 

Fortunately, from the General tab, the command line to start the Oracle service is found.

(Oracle Service Command Line - 74996.jpg ) 

Therefore, it is possible to start the Oracle service from the command line. Now that it is running in the same login session. It can see whatever drives you map.

(Starting from Command Line - 74951.jpg ) 

Note that at this point, only the service has started. The instance is NOT. You still have to start it in nomount mode using SQLPLUS.

It's now therotically possible to perform the duplication. However, the 'connect auxiliary' command hanged in my case. With unknown reason, it is impossible to connect through listener if the Oracle service is started from command line. BEQueath connection, however, is OK.

(Cannot Connect… - 74952.jpg ) 

This means that you must run RMAN on the destination server and connect to the source using SQLPLUS. Looks a bit strange but works anyway.

C:\oraclexe\app\oracle\product\10.2.0\server\BIN>rman target sys@xe as sysdba

Recovery Manager: Release 10.2.0.1.0 - Production on Fri May 4 12:24:03 2007

Copyright © 1982, 2005, Oracle.  All rights reserved.

target database Password:
connected to target database: XE (DBID=2484850100, not open)

RMAN> connect auxiliary /

connected to auxiliary database: NBXE (not mounted)

RMAN> duplicate target database to nbxe;

 

That's all for this article!!