RMAN Duplication

These are steps I wrote for duplicating databases from production to development and test at a company I worked for. I thought I’d post them here for the usefulness of everyone. They are specific to the environment I was in, but still should be useful anywhere.

Database names have not been changed to protect the innocent.

Mount target backups on auxiliary server

1. Have the target backup directory mounted on (or copied to) the auxiliary server. (e.g. dbfarm1-prd4:/oraback mounted to dbfarm1-tst4:/mnt/oraback )
!! Make sure mounted filesystems are mounted hard (rsize>=32768,wsize>=32768,hard) and readonly !!
(to verify – touch a file (e.g. touch testfile), it should fail)
2. Create link for target backups in the auxiliary /oraback directory if needed
(e.g. ln -s /mnt/oraback/P10PTT P10PTT)

 

Make sure you have enough disk space on Auxiliary

Be sure you have enough disk space for the database to be duplicated to the auxiliary.  (RMAN will overwrite existing data files, and not append or create new).

 

Setup auxiliary listener

  1. Setup an entry in the listener.ora for the auxiliary database.
  2. Reload the listener

This is required because the auxiliary will be in nomount for the duplication, therefore it will not dynamically register with the listener.

 

Setup target tnsnames.ora

1. Create a (SERVER=DEDICATED) service name entry in the tnsnames for the auxiliary database.
This should be a separate entry from the normal tnsnames entry to avoid possible adverse affects to the production system.
Standard is to name it <SID>_D (e.g. T10VDS_D)

 

Remove auxiliary from catalog (if it is being backed up via the catalog)

1. Get the auxiliary databases dbid from v$database
2. Using RMAN, connect to the catalog only and issue
rman> set dbid = <dbid>;
rman> unregister database;

If the dbid is not available the following command will work but it has more risk.
Using RMAN, connect to the catalog only and issue
rman> unregister database <SID>;

Rman will have you verify that this is the correct database and then it will delete the specified database from the catalog.

 

Nomount auxiliary database (auxiliary server)

1. If the auxiliary databases datafiles and redo logs will be in a different path than the target’s, then configure db_file_name_convert and log_file_name_convert in the auxiliary’s controlfile.  See if it is set by: show parameter convert;
(e.g. alter system set db_file_name_convert = ‘/oradata/P10PTT’,’/oradata/D10PTT’ scope=spfile;)
2. Shutdown auxiliary
3. Clear out the auxiliary bdump and udump directories
4. Backup the auxiliary (if you might need to rollback – usually just a cold OS copy)
5. startup nomount
6. exit    (if you don’t exit your session duplicate will indefinitely wait on your logout during the duplicate process when it needs to shutdown the auxiliary)

 

Duplicate to the auxiliary

!! Verify the Target and Auxiliary have the same Version, Release, and Patchset. Failure to do so most certainly will result in the inability to duplicate and a worthless auxiliary!

You can start the duplicate from either the production server or the RMAN catalog server.  By using the RMAN server, you don’t need a FW request between the target and auxiliary servers.  Choose one of the 2 options below to start the duplicate.

  • Clear out the alert or trace directories (alert log, trace files, cores, etc)
  • To duplicate from the production server
  • note: if shared server is not used on the auxiliary you don’t need a specific SID_d tnsnames entry

<verify the target and auxiliary databases are at the same patchset level – DO NOT continue if they aren’t!>
rman
connect target sys/pwd
connect catalog <catalogowner>@<catalogSID>    (you really don’t need to do this – JSW)
connect auxiliary sys@<aux-net service name>   (e.g. “sys@d10ptt_d”)
duplicate target database to <aux sid>;        (e.g. “to D10PTT”)

To duplicate from another server

<verify the target and auxiliary databases are at the same patchset level – DO NOT continue if they aren’t!>
rman
connect target sys@<production_db>             (make sure it is a dedicated connection)
connect catalog rcatmgr@p10dba
connect auxiliary sys@<aux-net service name>   (e.g. “sys@d10ptt_d”)
duplicate target database to <aux sid>;        (e.g. “to D10PTT”)

Notes : If target and auxiliary have same path to data files and redo logs then duplicate target database to <aux_sid> nofilenamecheck; Given our naming conventions this should not happen.

To run in parallel for larger databases use multiple channels:

run
{
 allocate channel chan1 type disk;
 allocate channel chan2 type disk;
 allocate auxiliary channel aux1 type disk;
 allocate auxiliary channel aux2 type disk;
 duplicate target database to <aux sid>;
}

Reset auxiliary (auxiliary server)

After duplication is complete make sure to do the following on the auxiliary

  1. Drop and re-add temporary tablespace temp files (pre-11g). (as sysdba run @/oracle/dba/procs/duplicate/add_temp_tbl.sql)
  2. Drop and recreate the data_pump_dir directory with correct path (create or replace directory data_pump_dir as ‘/oraback/&SID/dpdump’;)
  3. Store dbid if the auxiliary will be RMAN backed-up (store in /oraback/dbid.txt)
  4. Reset passwords to match what they were before.
  5. Remove the target backup link (if target and auxiliary share same backup path)
  6. PlaceHolder – if service names are created (DG for example), the procedures that create them need to be updated correctly. (or I need to modify the procedure so it actually gets the db_name to create the service names – better idea)  (some things, alter system reset log_archive_config; alter system reset log_archive_dest_2, more?   then if DG setup follow dg instructs – need more work here)

 

Duplicating without an existing database

If you need to create a database from Production (or elsewhere) but the new database does not yet exist you do not need to create it using DBCA (or other means) first. You only need some base components to support an instance and RMAN will create the database. The following steps will allow you to duplicate with the base structure.

  1. Add the new database to the /etc/oratab file
  2. Create the /oracle/product/admin/<SID> directory and contents (usually copy and modify/clean from another)
  3. Create the Database password file (in $ORACLE_HOME/dbs, orapwd file=orapw<SID> password=<sys pwd>)
  4. Create an init file for the database (copy dev, test, or production and modify)
  5. Make sure the init file has the log_file_name_convert and db_file_name_convert parameters (e.g. db_file_name_convert=’/oradata/P10AIMS’,’/oradata/D10AIMS’).
  6. Create the /oradata directories (/oradata/<SID>, /oradata/ctl01/<SID>, /oradata/ctl02/<SID>)
  7. Create the /oraback directories (/oraback/<SID> …arch/dpdump/backup)
  8. Change your ORACLE_SID to new database
  9. Startup nomount the new database
  10. Create spfile from pfile;  restart the database so that the spfile is read
  11. **NOTE (If duplicating from a DEV or TEST database that is not being backed up by RMAN – you’ll need to configure that DB to be backed up, and run the backups before following the normal duplication steps.)
  12. <follow normal duplication instructions here>
  13. add to crontab anything required

Duplicating from a different version

If you need to create a database from Production (or elsewhere) but the existing database is a different Oracle version than Production, follow these steps on the server where you are copying the database to:

  1. create pfile from spfile, copy it to the new version’s home, and then edit it conform to new Oracle version, or copy/edit another pfile (initSID.ora).  Make sure to add/change the *_file_name_convert lines, and if audit_file_dest is set, create that directoty BEFORE starting the database.
  2. Shutdown the auxiliary database
  3. . oraenv (to load the correct environment variables)
  4. Remove the following auxiliary files (but not the directories): /oradata/<SID>, /oradata/ctl01/<SID>, /oradata/ctl02/<SID>, /oraback/<SID>, $ORACLE_HOME/dbs/*<SID>*
  5. copy the listener.ora and tnsnames.ora to the new version’s home, and edit them for the new version
  6. start the new listener (if needed)
  7. Create the Database password file (in $ORACLE_HOME/dbs, orapwd file=orapw<SID> password=<sys pwd>)
  8. startup nomount the new database
  9. create spfile from pfile and restart using s$ORACLE_HOME/dbs
  10. Create a new password file using orapwd
  11. <follow normal duplication instructions here>
  12. Create spfile from pfile;  restart the database so that the spfile is read
  13. edit crontab if needed

 

Duplicate to the auxiliary at a point in time

If you need to see how data looked at a particular point in time you can do an rman duplication that recovers the data until a specific time or SCN. This is useful to get data that was accidentally dropped. The only change from the standard duplication is the addition of the until time clause.

duplicate target database to <aux sid> until time “to_date(‘Jan 5 2009 10:30:00′,’Mon DD YYYY HH24:MI:SS’)”;

 

Issues you may encounter

Converting from 64 bit to 32 bit

ERROR

RMAN-12005: error during channel cleanup
ORA-06544: PL/SQL: internal error, arguments: [56319], [], [], [], [], [], [], []
ORA-06553: PLS-801: internal error [56319]

SOLUTION

shutdown the target database
startup upgrade
@?/rdbms/admin/utlirp.sql    — this invalidates all code
shutdown
startup
@?/rdbms/admin/utlrp.sql     — this recompiles all code

Block Change Tracking

If Block Change Tracking is enabled on the Target database you can encounter issues. The block change tracking file must be in a location that can be seen by the auxiliary database. For this reason I find it best to place your block change tracking file in the Recovery Area – besides it isn’t a data file and without it the database can run just fine. As long as it is in that location the duplication will complete though you will get an error:

ORA-19750: change tracking file: '/oraback/P10AIMS/block_change.trk'
ORA-27038: created file already exists

You can ignore this error and your database will be fine. If you store it somewhere that the Auxiliary Database can’t see it then the duplication will fail unfortunately.

To move the block change tracking file (e.g.):

alter database disable block change tracking;
alter database enable block change tracking using file '/oraback/P10AIMS/block_change.trk';

You will then need to run a new backup on the target database so the file is located in the usable location in the backupset. An alternative is to disable the block change tracking, run a new target backup, run the duplication, and then re-enable the block change tracking. Realize that is incremental backups are being used on the target that until an incremental level 0 is run the datafile will have to be scanned in their entirety.

.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s