Oracle 11g active database duplication on Unix/Windows

In this post, let me take you through the step-by-step method of active database duplication, a new feature of Oracle 11g to create a duplicate database. Active database duplication can also be used to create a standby database, which is mentioned separately in another post.

Basics

Upto Oracle version 11g, RMAN ‘DUPLICATE’ command required a database backup for performing database duplication/cloning. This is called backup based database duplication. Starting from Oracle 11g, an active database can be cloned to the same host or another without using an RMAN backup and the recovery catalog and this is called active database duplication.This can also be used to create a physical standby database. RMAN copies the live database files over the network to create the duplicate/standby database.

Prerequisites

* The source database must be in ARCHIVELOG mode if it is open.
* If the source database is in NOARCHIVELOG mode, it should be mounted after a clean shut down.
* Connections to the auxiliary instance must be through a Net service name.
* The auxiliary instance must have a password file created, the password for the SYSDBA user used for the activity must be the same.

Practical issues and solutions

When the source database server and the target database server follow the same filesystem structure,size and standards, the duplication works fine without any issues. This may not be the case in most of the environments. The source database will have files laid across the mountpoints (say) ‘/oracle/oradata1/PROD’, ‘/oracle/oradata2/PROD’ etc. The target server may follow a totally different structure. In such cases, perform any of the following methods:

1) Use the ‘SET NEWNAME FOR DATAFILE’ and ‘SET NEW NAME FOR TEMPFILE’ in the run {} block while performing restore/duplication. When specified,  the ‘SET NEWNAME’  command will take precedence over the other methods of renaming the database files.

If the destination server follows a different filesystem layout and if the files needs to be restored to a separate set of directories different than that from the source database, ‘SET NEWNAME’ command needs to be used for each datafile and tempfile (‘SET NEW NAME FOR TEMPFILE’ command is introduced in Oracle 11g)

Note:

If ‘SET NEWNAME’ commands are used in a ‘run {}’  block while performing a restore operation, the ‘run {}’ block must contain the command ‘SWITCH DATAFILE ALL;’  and ‘SWITCH TEMPFILE ALL;’  (SWITCH TEMPFILE ALL; is available starting from Oracle 11g). If the ‘SWITCH; command is missed out during a restore operation,  ‘ALTER DATABASE RENAME FILE’  command must be used for each datafile before proceeding with the recovery. The details required to perform the manual execution of ‘ALTER DATABASE RENAME FILE’ can be found from the alert log. It is a tedious task to do so, so NEVER forget to add the SWITCH commands in the run block  if you are performing an RMAN restore. For database duplication using DUPLICATE command, ‘SWITCH DATAFILE/TEMPFILE’ commands are not required as this function is handled by RMAN automatically.

2) DB_FILE_NAME_CONVERT clause in the DUPLICATE statement. This method will have the precedence if the DB_FILE_NAME_CONVERT / DB_LOG_FILE_NAME_CONVERT parameters are used in the Init.ora file.

3) DB_FILE_NAME_CONVERT and DB_LOG_FILE_NAME_CONVERT parameters in Init.ora.

4) In case Oracle managed files are used in the source database, either you can specify ‘SET NEWNAME’ commands for each datafile and/or specify the parameters DB_FILE_CREATE_DEST and DB_CREATE_ONLINE_LOG_DEST_n in the auxiliary instance to get the datafiles, temp files and redo logs created in the respective directories.

Step-by-step

Terms used to describe the method step by step:

Target: Database to be cloned. (Source database – ORCL instance)
Auxiliary: Destination database to which the source database will be cloned (ORCLDUP instance)

This scenario explains that the source and target database instances are on a Windows server. The method is the same for database duplication across 2 different servers (on Unix also) except that some of the OS commands mentioned below may need to be changed.

1) Prepare Init.ora file for the auxiliary instance

Copy the Init.ora file from the source server to the target server, to the dbs/database directory based on the underlying platform. Make necessary modifications in the Init.ora file, mainly the below parameters:

*.db_name='ORCLDUP'
*.control_files='C:\ORACLE\ORADATA\ORCLDUP\control01.ctl,
C:\ORACLE\ORADATA\ORCLDUP\control02.ctl, C:\ORACLE\ORADATA\ORCLDUP\control03.ctl'

2) Create Password file

Copy the password file to the target server, rename it in the below format, this is the easiest method; you can always create the same with orapwd utility.

Unix Servers: orapw<SID><sid>.ora
Windows Servers: PWD<SID><sid>.ora

3) Create Oracle related services (Required for Oracle on Windows only)

Use Oradim utility to create an Oracle service on Windows servers., this step is not applicable for Unix servers.

oradim -new -sid orcldup -srvc oracleserviceorcldup

4) Create directories for database files

Create the required directories on the target server for datafiles, redo logs, control files, temporary files etc, this example assumes that all the database files will be stored under ‘C:ORACLEORADATAORCLDUP’

5) Configure listener to user static service registration

Static service registration for the listener must be done on the target server, this is because the database instance in NOMOUNT state (auxiliary instance before cloning) will not allow database connections, it will be BLOCKED. To configure static service registration with the listener, update the listener.ora file as below. Please note that, in this example the target and auxiliary instances are on the same server.

SID_LIST_LISTENER =
(SID_LIST = (SID_DESC =
(ORACLE_HOME = C:\oracle\product\11.1.0\db_1)
(SID_NAME = ORCL))
(SID_DESC = (ORACLE_HOME = C:oracle\product\11.1.0\db_1)
(SID_NAME = ORCLDUP))
)

6) Reload the listener

lsnrctl reload;

7) Start and verify the auxiliary instance

Start the auxiliary instance; verify the parameters db_name and control_files.


SQL> show parameter db_name;
SQL> show parameter control_files;

8) Perform Oracle 11g Active database duplication

RMAN>
run {
SET NEWNAME FOR DATAFILE 1 TO 'C:\ORACLE\ORADATA\ORCLDUP\SYSTEM01.DBF';
SET NEWNAME FOR DATAFILE 2 TO 'C:\ORACLE\ORADATA\ORCLDUP\SYSAUX01.DBF';
SET NEWNAME FOR DATAFILE 3 TO 'C:\ORACLE\ORADATA\ORCLDUP\UNDOTBS01.DBF';
SET NEWNAME FOR DATAFILE 4 TO 'C:\ORACLE\ORADATA\ORCLDUP\USERS01.DBF';
SET NEWNAME FOR DATAFILE 5 TO 'C:\ORACLE\ORADATA\ORCLDUP\EXAMPLE01.DBF';
SET NEWNAME FOR TEMPFILE 1 TO 'C:\ORACLE\ORADATA\ORCLDUP\TEMP01.DBF';
duplicate target database to orcldup from active database;
}

9) Logs

Oracle 11g Active database duplication Log

10) References

 

Oracle documentation on RMAN database duplication

Share

7 comments to Oracle 11g active database duplication on Unix/Windows

  • Aninueuniog

    Interestingly written!

  • Tweezer

    One requirement left out is the tns entry for the auxiliary database must also exist on the target host. This is commonly not documented.

  • RANA

    Hi Pundits,

    Thanks for the article,

    I have tested it, it worked successfully, let me add we don’t need to set SET NEWNAME anymore starting with 10g we can use nofilenamecheck. and i have used it in same windows TEST server for both the databases.
    i have used following statement in RMAN Session after connecting to both and it just worked fine.

    RMAN> duplicate target database to ‘test’ from active database nofilenamecheck;

  • HornSpiel

    In step 4 you create the required directories on the target server for … control files…

    In step 7 you do
    show parameter control_files;

    When do you create the control files?

  • admin

    Thanks for pointing it out, Just that the control_files parameter was verified in that step. The controlfile will get created during the duplicate process. I’ll remove that line to remove confusion.

    Thank you.

  • Siti

    Hi,
    on this step: 7) Start and verify the auxiliary instance

    I cannot connect as sysdba. It gives ORA-01031: insufficient privileges. The newly setup oracle service (since I am doing this on Windows) is running.

    Any entries I need to do in sqlnet/tnsnames/listener.ora?

  • admin

    Did you try setting this parameter in sqlnet.ora?

    SQLNET.AUTHENTICATION_SERVICES= (NTS)

    Also, make sure REMOTE_LOGIN_PASSWORD_FILE is set and password file is being used. Also, verify ORACLE_SID in the terminal.
    I remember setting LOCAL=DBNAME has fixed the problem. Please try that too.

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>