Very often we need to make copies of the production database in test environments to be able to perform functional tests which can then be issued on production.
To make a copy of the database there are several methods. Some of these are:
• Export / import mode (which uses the utility expdp / impdp made available to Oracle and that will be the subject of future articles);
• Cold Copy (made with a physical copy of the file; if the source is down you do not need a step of recovery, while if the source is active then you need to make the recovery. Again, this theme will be reissued in future articles);
• RMAN Backup/Restore (rman backup uses the source restoring the environment on the target machine. At the end, rename the instance manually);
• RMAN Duplicate (such as backup / restore but in this case the renaming of the instance is automatic. RMAN will be within the future articles);
From Oracle 11g you can take advantage of the automatic duplicate without RMAN’s backup pieces.
Active database duplication copies the live target database over the network to the auxiliary destination and then creates the duplicate database. Only difference is that you don’t need to have the pre-existing RMAN backups and copies. The duplication work is performed by an auxiliary channel. This channel corresponds to a server session on the auxiliary instance on the auxiliary host.
As part of the duplicating operation, RMAN automates the following steps:
1. Creates a control file for the duplicate database
2. Restarts the auxiliary instance and mounts the duplicate control file
3. Creates the duplicate datafiles and recovers them with incremental backups and archived redo logs.
4. Opens the duplicate database with the RESETLOGS option
Prerequisites: on source database is need that the parameter remote_login_passwordfile is EXCLUSIVE.
You also need to add in the tnsnames.ora on both machines (source and destination) the connect string relative to another environment. For example, you must add the following connect string:
(ADDRESS = (PROTOCOL = TCP)(HOST = production-host)(PORT = 1521))
(SERVER = DEDICATED)
(SERVICE_NAME = PROD_DB)
Be included in the tnsnames.ora of target environment (test machine) and
(ADDRESS = (PROTOCOL = TCP)(HOST = test-host)(PORT = 1521))
(SERVER = DEDICATED)
(SERVICE_NAME = TEST_DB)(UR=A)
Be included in the tnsnames.ora of source environment (production machine).
It is appropriate to test the connections from each machine to the other using tnsping and remote connections:
From source to destination:
• sqlplus “sys@test_db as sysdba”
• tnsping test_db
From destination to source:
• sqlplus “sys@production_db as sysdba”
• tnsping production _db
Check the paths of datafiles/tempfiles/redologs. If the paths are different insert (if missing) the following two parameters in the init file of the target database: db_file_name_convert (to remap datafile) and log_file_name_convert (to remap redolog).
Start destination database with startup nomount.
Start RMAN and connect to the source database as TARGET, the duplicate database instance as AUXILIARY, and, if applicable, the recovery catalog database.
Recovery Manager: Release 18.104.22.168.0 – Production on Fri Jul 5 10:17:53 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
RMAN> connect target sys/<pwd>@production_db; ## Target database ##
connected to target database: PROD_DB (DBID=1450838187)
RMAN> connect auxiliary sys/<pwd>@test_db; ## Auxiliary database ##
connected to auxiliary database: TEST_DB (not mounted)
Now is possible run duplicate process:
2> duplicate target database
3> to ‘BIRD’
4> from active database;
At the end you need to make all the usual steps of post-refresh a test environmentt. For example:
– reset password
– check temporary tablespace
– check dblink
– run customized script
For details of the whole process, you can refer to the MOS note:
RMAN ‘Duplicate From Active Database’ Feature in 11G [ID 452868.1]