Using duplicates

Duplication is a copy of the database with the simultaneous change of a unique DBID. You can restore the database from a backup file and archives. It is also possible to duplicate a point in time so that you can compare the current state of objects (from the production base) with their state even in the distant past when UNDO data can no longer be used. This duplication to a point in time works just like playing to a point in time, except that on another target database. You can use this as a simple method for copying a database for testing purposes. For example, you can check for upgrades, patches, modifications before implementing changes on the production basis. You can duplicate the database on the same host, or copy the necessary files to another.

Duplication 1-1

The method to copy the database is a few, here I present in my opinion the most convenient ie using the directory. We need three connections. To the source database, to the directory (which acts as a backup data repository), and of course to the target database. In addition, backup files must be accessible from the target database, as well as source database archives. If we duplicate the database to another host, access to backups and archivelogs is reduced to copying them from the source server to the FRA on the target server.

We perform the entire operation from the target host, because the target database before the duplication must be launched into NOMOUNT mode, will not be accessed through the Listener. Duplication itself after having prepared the relevant information and connection is reduced to the command “duplicate target database is target_name”. This command, although it comes down to a few words, calls, in fact, a complicated mechanism. The information that must be provided to RMAN prior to duplication is the new name of the data file, and possibly the moment we reproduce if we make a duplicate to a point in time. You can enter new filenames in two ways – either individually for each data file or by providing a name swapping scheme. In the first place, we will use this first method, even though it is more time consuming, but also easier to understand. Then repeat the operation using the name and path replacement scheme.


First of all, we need a base to which our source database will be unified. So we start DBCA and create a database. Creating a database with this tool is so easy that we will not be doing this in detail. Generally, it is “next, next, next, next, finish” 🙂 Creating a base can take a while.


We log into the new database as a sys. This is needed because duplicate files will need to be given new names. We use the first method first. If we did not make any changes to the filenames at the time of duplication, we would have received a message saying that there were already files with that name and that the duplication would have been aborted. So, let’s define where our data files and temporary files lie.


The source database must work in ARCHIVELOG mode, otherwise, we need at least one full backup which will be the basis for duplication. At this stage you should already know how to enable archivelog mode, but if you forgot to remind me :).

Shutdown immediate first, then boot into mount (startup mount), enable alter database archivelog, and open alter database open.

After creating a new database, you will come across an unexpected obstacle. After creating the database, DBCA sets the ORACLE_SID sid of the new database in the system variable. The consequence of this is that if you log in to the sqlplus database without giving the sid (and thus logging off) you will connect to which SID is set in the ORACLE_SID variable. Same when connected with RMAN. If you want to change the value of this variable, in linux you do this by issuing the command:

export ORACLE_SID = xxx

in Windows:

set ORACLE_SID = xxx

where xxx is sid bases. Case sensitivity is important.



After setting ORACLE_SID to the SID of the source database, we do a full backup of it (if we did not do it earlier). In case you want to do duplication between different hosts and you’ve already done some backups, then it’s worth doing this one full backup. This will result in fewer files to be copied, especially archivelogs.


If you do not have the CATALOG RMAN set yet, do the following:

  • Create a user and give it roles: connect, resource, recovery_catalog_owner. Create a user on any database, if not on that database, which will be the target database. You may want to connect to this database later from the host on which the target database is located.


  • From the RMANa level, connect to the source database and to the schema you just created. Issue commands: “create catalog” and “register database”.
  • Now lead to open the source database and the directory in which it is located (the same database is in me), while the target database was launched in NOMOUNT mode. It must be NOMOUNT because changes will be made to the control file during the duplication.


The next part of the activity is done from the host on which the target database is located. Make sure that the ORACLE_SID system variable is in the target database sid. Now connect to the source database, directory, and target database from rman in the following way:

  • The connect target was the connection line to the source database.
  • After the connect catalog, you will find a link to the schema in which you have created the directory.

    The connect auxiliary was a slash (/) which will connect to the database which sid we have in the system variable ORACLE_SID.

We go to the right duplication. All commands “set newname …” and possibly

“Set until time …” must be in the same run block as the “duplicate target database to …” command. These commands only work in a RUN block, so you can not execute them independently. By using the “set newname” command we give the names of the data files to be created in the target database along with the paths. These numbers are the data file numbers in the source database that we displayed at the beginning.


The command “duplicate target database is new” restores the source database in the target database which sid we give (here it is “new”). We remember that this command was in the same run block as all “set newname”. The last full backup of the source database will be restored to the target database, and all archives will be available.

We have to wait now, the duplication process takes at least as much time as the full reproduction of the source database. After the duplication is complete, the source database will be opened.


If you want to refresh our copy of the database, just repeat the duplicate, but this time we do not create a new database :).

Troubleshoot during duplication

If we have just turned on archivelog mode in the source database, we might get the following error:


It results from the fact that no archivelogs have yet been created. So you just have to switch the log files of the repetitions (in the source database). The message is a bit misleading, it’s a source database – that’s the one we copy, not the target – that’s the one we’re copying. In the RMAN nomenclature, the “target” is the one to which we are connected using the “target” clause. So we’re going to redo log files:


and then repeat the duplication attempt.

Duplicate to point in time

Just as we can recreate the base to a point in time, so we can also duplicate the base to a point in time. Ultimately, duplication to a point in time is playing back to a point in time, but that is another place. All the previous steps are done in the same way, but in the same block in which we call duplication, we add the “set until time” command using the SQL to_date function.

A scheme for renaming data files and paths to them

Now is the time to promise a shorter version of file renaming when duplicating. Instead of specifying new names for all data files separately, we can show RMAN the schema for which to change them for us. The rules and procedures described previously remain essentially unchanged, and the modification only applies to “set newname” commands with the same duplication. Instead of set newname for datafile / tempfile we will write this time set newname for the database. The path to the directory where the data files are to be found and the “% b” tag that represents the old file name. The following sequence will result in the data files in the directory of which the path is given, but they will be named the same as the data files in the source database.