Oracle Database Refresh from Production to Development Using RMAN

Refreshing another environment from production is pretty much a routine ritual for any DBA. Still need “live” production data captured in countless other environments. Whether it’s pre-production to test the performance of a support issue or test/development to develop the latest version of software on top of a current copy of production data.

Over the years, the way these refreshes are performed has evolved from restoring the entire RMAN production process to environments fortunate enough to have enough space to accommodate a production-sized copy, to the reduction of data pump import/export of specific schemas.

Starting with version 11gR2, it is possible to clone an Oracle database without using a recovery catalog. Although I clone the database on the same server, the same article can also help you to clone the database on different systems.

For network-enabled database duplication, make sure you follow these rules. Oracle Net must be aware of both the source and destination databases.

• It is possible to mount or access the source database.
• You should have shut down the source database completely before starting it up in mount mode if it has been mounted.
• The source database should be in archivelog mode if it really is accessible.
• You can proceed to use the primary database ordinarily while the database duplication is taking place, but be advised that transmitting the data comes at a cost in terms of CPU and communication bandwidth usage.
• Despite backup-based duplication, in which a password file isn’t required, ongoing database duplication requires the usage of a password file. The password file allows the auxiliary database to be accessed using the same SYSDBA password as the parent database. Both the primary and secondary databases require the very same SYSDBA password. Instead, you may use the duplicate database command to provide the password file parameter.

Following are the steps to Database Refresh from Production to Development Using RMAN:

➤ Step1: Start the auxiliary database (duplicate database) instance on TEST Host. Because you don’t have a control file for this new database yet, you must start the new instance in nomount mode. The SPFILE for the auxiliary instance will have the following contents:

db_name = test

To identify the name of your new duplicate database, you simply need one argument, db name.

SQL> connect / as sysdba

Connected to an idle instance

SQL> startup nomount

➤ Step2: Run RMAN and connect to the target database (prod).

RMAN> connect target sys/oracle@prod

➤ Step3: Use a SQL*Net connection to connect to the duplicate database using the keyword auxiliary:

RMAN> connect auxiliary test

auxiliary database Password:

➤ Step4: To begin the database duplication process, use the duplicate target database command. If you want to do active database duplication on a separate host with the same directory structure, you must add the nofilenamecheck parameter in your database duplication command.

RMAN> duplicate database to test
from active database
nofilenamecheck;

If the source database files are split across many directories, you must rename the files in the duplicate database using the set newname option rather than the db file name convert parameter.

RMAN> run
{
set newname for datafile 1 to ‘/u01/app/oracle/testdata/system01.dbf’;
set newname for datafile 2 to ‘/u01/app/oracle/testdata/sysaux01.dbf’;
set newname for datafile 3 to ‘/u01/app/oracle/testdata/data01.dbf’;
set newname for datafile 4 to ‘/u01/app/oracle/testdata/index01.dbf’;
set newname for datafile 5 to ‘/u01/app/oracle/testdata/undotbs01.dbf’;
duplicate target database to test
logfile
group 1 (‘/u01/app/oracle/testdata/logs/redo01a.log’,
(‘/u01/app/oracle/testdata/logs/redo01b.log’) size 10m reuse,
group 2 (‘/u01/app/oracle/testdata/logs/redo02a.log’,
(‘/u01/app/oracle/testdata/logs/redo02b.log’) size 10m reuse;
}

The run block presented here copies the target database and provides file names for the duplicate database’s data files and online redo log files.

Database cloning now works as follows for different types of source database files:

• Data files are copied from the source database.
• Control files are regenerated but will be copied from the source database if you specify the for standby clause.
• Temporary files are recreated in the location specified by the db_create_file_dest parameter.
• The online log files are regenerated.
• The archived redo log is copied from the source database, but only when necessary for replication.
• The server settings file is copied from the source database if you only use the spfile clause.
• Password files are always copied for backup databases, but for duplicate databases they are copied back only if you specify the password file option in the database command duplicate.

Click here to know more about oracle guides.