Active (Network-Based) Database Duplication - Oracle 11g

Prior to Oracle Database 11g,database duplication through RMAN always meant backup-basedduplication,since the duplicatedatabase relied on the source data base’s datafile and archived redo log backups.

In Oracle Database 11g, you can directly duplicate a database over the network without having to back up and provide thesource database files.This direct database duplication is called active databaseduplication or network-aware database duplication.

This is definitely one of Oracle Database 11g’s more useful innovations, since you can create duplicate databases andstandby data bases directly from a live production database,with only an overhead in terms of additional CPU and networkutilization for the duplication process.RMAN simply copies the target database files to the destination server (it can be the same server) over a network connection.

Until the Oracle Database 11g release, duplicating a destination database with RMAN meantyou had to have all of the following:

  • A source database
  • A copy of the source database on the source database (or on disk)
  • A copy of the source database on the destination server

When you use the network-enabled active database duplication, the process of duplicating is considerably simpler.You don’t need any previously made backup of the source database,and you are able to duplicate a live production database to a duplicateinstance or create a physical standby database over the network without actually restoring a previously existing backup.

You can perform this network-aware, backup-less databaseduplication either with Database Control or through RMAN.You still use the RMAN duplicate data base command, but with a new clause named from active data base,to let RMAN know you aren’t using backups as the source but are instead using the network for the database duplication.

The database files are directly copied over the networkconnection from the source database to the new destination database.

Unlike in the case of a normal backup-based databaseduplication,you don’t have to use the until clause when performing an active database duplication. RMAN automatically selects completed, so it can recover the datafiles to a consistent point in time.Because RMAN doesn’t copy the online redo log files and apply them to the duplicate database, it must always perform an incomplete recovery. RMAN can recover the duplicate database to the point in time of the latest archived redo log on the source database.


Here are the requirements you must satisfy before starting active database duplication over the network:

  • Both the target and destination databases must be on an identical operating system platform.
  • Oracle Net must be aware of both the target and duplicate instances.
  • Both the target and destination databases must have the same sysdba password,enforced through the use ofpassword files.
  • The source database can be either in the open or in the mount state.
  • If the target database is open, it must be in archivelog mode.
  • If the target database is in the mount state, you must have shut it down cleanly before bringing it up in the mount state.
  • You must provide a network service name when connecting to the auxiliary instance,even if you’re performing the duplication on the same server, since you’re performing a network-based database duplication.
  • By default, RMAN will not copy the password file from the target to the duplicate destination.You must specify the password file clause inside the duplicate databasecommand to copy the password file.
  • In a Data Guard environment, the contents of the password file must be the same.


The source database remains fully accessible to users while you’re performing the database duplication,which is yet another great feature of the active database duplication technique.Of course, be prepared to take a slight hit on CPU usage andnetwork bandwidth consumption during the datafile duplication.


Necessary Spfile Modifications

In Oracle Database 10g, you didn’t copy the spfile as part of a database duplication process.You had to first copy the spfile to the destination server and modify it to suit the duplicate database’s requirements.

In Oracle Database 11g, you can specify the spfile clause in the database duplication command for both the new active database duplication (network-enabled duplication) as well as traditional backup-based database duplication. By specifying the new spfile clause,you direct RMAN to copy the source data base’s spfile to the destination server.

For this to happen,you must have started the source database with a server parameter file.Before RMAN processes the duplicate database command,it first copies over this spfile,modifies it based on the settings in the spfile clause,and starts up the auxiliary instance with this modified server para meter file.If you use the spfile command in your duplicate database command, parameter file (init.ora)

When you duplicate a database, RMAN has to generate names for the duplicate database’s data files,controlfiles,online redo log files, and tempfiles.If you’re using the same directory structure on a different host,you can use identical database file names as the source database.

You must specify the nofilenamecheck clause in the duplicate database command when you do this to avoid errors.If the duplicate host uses a diffe rent directory structure or you want to rename the database files for some reason, you must adopt a strategy to generate the new datafile names during the database duplication processThe simplest way to generate file names is to specify the spfile clause in the duplicate command, as shown here:

The spfile clause lets you set all the file name–relatedparameters (except the db_file_name_convert parameter).If you’re using the backup-based dupli cation method, RMANrestores the server parameter file from a backup of the source database server parameter file.If you are using the activedatabase duplication method,on the other hand,RMAN will copy the server parameter file currently being used by the source database.

RMAN copies or restores this spfile to the default location for the auxiliary instance on the host where it’s running.If you don’t specify the spfile clause, you must copy the server parameter file yourself.You can also specify a normal text-based initialization parameter(pfile) by speci fying the pfile parameter instead of the spfile parameter as part of the duplicate command.

Necessary Spfile Modifications

You can provide values for the parameter_value_convert clause when you use the spfile to specify all path names except the db_file_name_convert and log_file_name_convert parameters.

The real purpose of the parameter_value_convert clause is to set the values of a bunch of initialization parameters when creating the duplicate database.

RMAN will update the initialization parameter values in the spfile it copied (or the backed-up spfile if you are using the backup-method of duplication) based on the values you provide for the parameter_value_convert and set parameters.RMAN will then start the new duplicate (auxiliary) instance with the updated server parameter values.

If you specify the spfile clause with the duplicate command, you must have started the auxiliary instance already with a text-based initialization para meter file (with one requiredparameter,db_name).If you haven’t specified the from active database clause in the duplicate command,RMAN will copy the binary server parameter file and restart the auxiliary instance based on the modified settings gathered from the spfile clause. If you specify the from active database clause in the duplicate command,then the source instance must be using a serverparameter file.

If you don’t want to use the spfile clause technique for naming the duplicate files, you can use alternative file-namingtechniques. You can also choose to use the spfile clause to name the files and supplement that technique with one or more of the alternative techniques.

Use the Oracle-recommended spfile clause in the duplicate command to set all necessary parameters involving file names for the duplicate database.This is by far more straightforward and easier than using the alternative technique ofdb_file_name_convert and other file name–convertingparameters.Thefollowing are the various options you can use to rename files when you’re duplicating a database:

  • spfile ... parameter_value_convert'string_pattern' specifies conversion strings for all initialization parameters specifying path names,except two: db_file_name_convert andlog_file_name_convert.In addition to specifying pathnames,the parameter_value_ convert clause also lets you update any string values.
  • Use spfile...set 'string_pattern' to set thelog_file_name_convert para meter for the online redo log files.The set clause lets you specify initiali zation parameters to the values you want. For example, you can use the set clause to specify initialization parameters such as sga_target or turn off replication options.In essence, the set functionality amounts to temporarily stopping the database duplication process in midstream after restoring the server parameter file and issuing the alter system set statement to change the initialization parametervalues.The parameter_value_convert clause is processed before the set clauses, and values you set for an initialization parameter using the set clause will override any identical initialization parameter settings that you specify through the parameter_value_convert clause.
  • Use the db_file_name_convert 'string_pattern' to specify file-naming rules for creating the duplicate database’s datafiles and tempfiles.

Here’s a simple example that shows how to use the spfile clause within the duplicate command to name datafiles and log files of the duplicate database during the duplication process:

The db_file_name_convert clause substitutes the string/u05/app/oracle in the names of the duplicate database’s datafiles (and tempfiles).

The parameter_value_convert clause in our example specifies the string /u05/app/oracle to be used in all initialization parameters that specify file names for the duplicate database,except the db_file_name_convert and log_file_name_convert parameters.

Note the use of the multiple set clauses to specify various initialization parameters, including the log_file_name_convertparameter,which specifies the substitution of/u05/app/oracle in the file names of the duplicate database’s online redo log files.

Performing Active Duplication

You use the familiar RMAN command, duplicate database, to perform active database duplication,with one importantmodification—you add the new clause from active database toindicate that the source database files must be copied directly from the target database rather than from the target database backups,as is the case in normal database duplication.You can create a duplicate database on a different server using anidentical directory structure as the target database by using the following basic active database duplication command:

The spfile clause means that RMAN will copy the targetdatabase’s spfile over to the destination database.If the destination database is on a different server,the spfile is copied to the new server.Of course,since we aren’t specifying any additional subclauses under the spfile clause to either set or modify any initialization parameters,the source database’s spfile is copied intact to the destination instance.

The nofilenamecheck clause is mandatory because you’respecifying that the duplicate database’s file names be identical to those of the source database (and the two databases are on different hosts).

In the following sections, we’ll explore the specific steps you must take to perform active database duplication.

Setting Initialization Parameter Values

If you don’t have any further clauses after the spfile clause,all the settings in the source database are copied over to the destination database’s spfile.If you want to override the source database settings for any initialization parameters of thedestination database,you can do so by using one or more set clauses.The set clause specifies the value of a specifiedinitialization parameter.Here is an example showing how to set values for various parameters:

  • set db_file_name_convert '/disk1','/disk10'
  • set log_file_name_convert '/disk1','/disk10'
  • set sga_max_size 500m
  • set sga_target 250m

In the example shown in the previous section, since the files on the destination server and the target server are identical, you didn’t have to specify any additional clauses in your database duplication command to dictate the naming convention for the datafiles or log files.

An Active Database Duplication Example

Let’s duplicate a database to learn how powerful the activedatabase duplication feature is.For simplicity, we’ll perform the database duplication on the same server as the target database. You don’t need any backups to start the database duplication process.

In the example,the source and the duplicate databases are on the same host, so we make sure we use different through a transfer of files over the network through the Oracle Net???connection.That’s why the first step for active database duplication is to establish Oracle Net connectivity.

  1. To make sure the source database can connect to the auxiliary instance by means of a net service name, you must make the net service name available on the source database instance.Add the auxiliary instance, which we named test1, to the listener.ora file on the listener after this):
  2. Also add the following information to the tnsnames.ora file,located in the $ORACLE_HOME/ network/admin
    The tnsnames.ora entry shown here maps the net service name test1 to the connect descriptor for a database named test1.
  3. The next step is to create an initialization parameter file for the auxiliary instance.Since we’re using the more straightforward spfile technique for naming the duplicate files, we can get by with a minimum of just a singleparameter (db_name) in the spfile to denotedb_file_name_convert and log_file_name_convert directly in the duplicate database command itself, rather than in the spfile.The simple spfile for our auxiliary instance would then look like this:
  4. db_name=test1 If you didn’t use the spfile technique for naming the duplicate database files, you’d have had to set parameters such as db_file_name_convert and log_file_name_convert in your initialization parameter file.
  5. When you perform active database duplication, you must use a password file for the auxiliary instance.You’ll need the password file so the target instance can connect directly to the auxiliary database instance during the duplication process.Use the same sysdba password in this password file as that of the source database.You can create apassword file with the orapwd utility, as shown here:
  6. $ orapwd file=orapwtest1 password=<sys_pwd> entries=20 ignorecase=n
  7. Using the SQL*Plus command line, start the new auxiliary database in nomount mode:
  8. The startup command will use the spfile you created in step 2 to start the auxiliary instance in the nomount state, since you don’t have a controlfile yet for the new instance. Since you specified the spfile clause in the duplicate database command, RMAN will copy the source database parameter file to the destination host.
  9. Set the ORACLE_SID environment variable to that of the source database, prod1, and start RMAN.You can leave the source database open or in the mount state.If the source database is open,it must be running in archivelog mode.You must connect to the source database,which you are duplicating to the auxiliary instance.Here’s how you start up RMAN and connect to the source (target) database.

An Active Database Duplication Example

  • Next,establish a connection to the auxiliary instance by specifying the keyword auxiliary,
    as shown here:
RMAN> connect auxiliary sys/sammyy1@test1
connected to auxiliary database: TEST1 (not mounted)

We’re not using a recovery catalog in this example. If you’re using the recovery catalog, you must of course also connect to the recovery catalog at this point.

  • Issue the duplicate target database command to create the duplicate database:

RMAN automatically updates the spfile on the server hosting the duplicate database based on the values you provided through the parameter_value_convert and set clauses of the duplicate database command.RMAN then starts the auxiliary instance with the updated spfile and proceeds to copying the source database files over the network.Once this is done,it recovers the duplicate database and opens it with the resetlogs option,thus creating a new set of online redo logs.

In our simple example here,we duplicated an entire database to the same server.You can also use the active duplication technique to duplicate a database to a different server.You can also perform duplication to a past point in time,as well as file system–to–ASM and ASM-to- ASM duplications over the network,just as in the case of traditional backup-based database duplication.

How Network-Enabled Duplication Works

The from active database clause of the RMAN duplicate command starts the database duplication process.RMAN utilizes the network connection to copy the source database files to the auxiliary instance.After copying the datafiles, RMAN performs a database recovery by using a memory script (so named because it exists only in memory) before opening the duplicate database.

The following duplicate database command shows how much simpler the duplication command is when you perform the database duplication on a different host with an identical directory structure as well as datafile names:

You must specify the nofilenamecheck clause in this case, since you’re using the same directory structures for both the source and destination databases.If you want RMAN to copy the entire password file from the source database to the duplicate database,specify the optionalpassword file clause in the duplicate database command.In this example,the password file was created manually instead of specifying the password file clause.

When you perform active database duplication over the network,the various files that are part of the database duplication are copied or re-created, as shown in the following list:

  • Datafiles are copied from the source database.
  • Controlfiles are re-created but will be copied from the source database if you specify the for standby clause.
  • Tempfiles are re-created in the location set by the db_create_file_dest parameter.
  • Online redo log files are re-created.
  • Archived redo logs are copied from the source database, but only if needed for duplication.
  • The server parameter file is copied from the source database, but only if you use the spfile clause.
  • The password file is copied for standby databases always, but for a duplicate database,it’s copied only if you specify the password file option in the duplicate database command.
  • Flash recovery area files aren’t copied during the duplication. Similarly, the duplication doesn’t re-create the flashback log files, the password file, or the block change tracking file.

To create a standby database instead of a duplicate database,just replace the to auxdb part of the duplicate database command with the for standby clause, as shown in the following example:

It’s easy to duplicate a non-ASM file-based database to an ASM file system.To do this,first create an ASM disk group,before running the duplicate database command. Here’s an example:

The duplicate database command shown here creates a database with all of its datafiles, controlfiles,and online redo logs in the ASM disk group +DISK2.

If you want faster duplication of a database,you must increase the number of disk channels on the source database,which leads to a parallel copying of the source database files.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 11g Topics