OracleDB‎ > ‎

Duplication

Oracle Database Duplication (duplicate database).
Oracle database migration to identical version/platform.

This procedure will work on both Linux and Windows platforms. In this example we are duplication an instance called "dg" from the "current" database to the "new" database.

On the new server, create an instance called "dg" or just copy over the init file from the current server. In either case make sure the folder structure is exactly the same as the current server, then do a "startup nomount" on the new server.

Make sure the listener is setup and listening. If you created the instance using the init file from the old server, make sure you create a password file so you can login to the instance from a remote server through the listener.

First make sure no expired or orphaned archivelogs exist.

$ rman target / crosscheck archivelog all; delete expired archivelog all;

Backup the db on the current database

$ rman target / run { allocate channel disk1 device type disk format '/orabackups/%U'; backup incremental level 0 database; backup current controlfile; backup archivelog all; }

Note: if you are duplicating the database to a different path, you need to create the new instance on the target server. Then create a pfile from spfile. Add the following to the end of the pfile. After the pfile modification shutdown the new instance and create an spfile from the modified pfile and then start it up in nomount.

Note that the path could be partial.

log_file_name_convert=('<current path>','<target db path>') db_file_name_convert=('<current path>','<target db path>')

Copy the backup folder to the new database (to the exact same location, /orabackups in this case). On the current server, add an entry to tnsnames.ora for the new server's dg instance (here I am calling it dg_new). Run the following from the current database to duplicate the database. Just make sure the new database is in nomount state before running the following.

$ rman target / connect auxiliary sys/<password>@dg_new; run { allocate auxiliary channel aux1 type disk; duplicate target database to dg nofilenamecheck; }

Note that if the new database is a newer version, this is considered an upgrade and the very last step of the duplicate database command will fail to bring the new database online. You need to run dbua do the following:

SQL> startup SQL> recover database; SQL> shutdown immediate

Make sure temp tablespace has a big temporary file then run the following. Look at Oracle TEMP Tablespace for more info.

SQL> STARTUP UPGRADE; SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

Restart the database:

SQL> SHUTDOWN IMMEDIATE; SQL> STARTUP;

Run the utlrp.sql script to recompile all invalid PL/SQL packages now instead of when the packages are accessed for the first time.

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql




DB duplication script:


Note that inittmpdb.ora cat section is created from the pfile of the target (or source) database.

create_tmpdb.sh

#/bin/sh export SOURCE_SID=blackowl export PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin export ORACLE_BASE=/opt/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1 export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib mkdir -p $ORACLE_BASE/oradata/tmpdb mkdir -p $ORACLE_BASE/admin/tmpdb/adump mkdir -p /opt/tmpbak orapwd file=$ORACLE_HOME/dbs/orapwtmpdb password=p4ssw0rd force=y entries=5 cat > $ORACLE_BASE/product/11.1.0/db_1/dbs/inittmpdb.ora << EOF tmpdb.__db_cache_size=37748736 tmpdb.__java_pool_size=16777216 tmpdb.__large_pool_size=4194304 tmpdb.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment tmpdb.__pga_aggregate_target=12582912 tmpdb.__sga_target=201326592 tmpdb.__shared_io_pool_size=0 tmpdb.__shared_pool_size=134217728 tmpdb.__streams_pool_size=0 log_file_name_convert=('$ORACLE_BASE/oradata/$SOURCE_SID','$ORACLE_BASE/oradata/tmpdb') db_file_name_convert=('$ORACLE_BASE/oradata/$SOURCE_SID','$ORACLE_BASE/oradata/tmpdb') *.audit_file_dest='$ORACLE_BASE/admin/tmpdb/adump' *.audit_trail='db' *.compatible='11.1.0.0.0' *.control_files='$ORACLE_BASE/oradata/tmpdb/control01.ctl','$ORACLE_BASE/oradata/tmpdb/control02.ctl','$ORACLE_BASE/oradata/tmpdb/control03.ctl' *.db_block_size=8192 *.db_domain= *.db_name='tmpdb' *.db_recovery_file_dest='$ORACLE_BASE/flash_recovery_area' *.db_recovery_file_dest_size=2147483648 *.diagnostic_dest='$ORACLE_BASE' *.memory_target=210000000 *.open_cursors=300 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.undo_tablespace='UNDOTBS1' EOF export ORACLE_SID=tmpdb sqlplus '/as sysdba' << EOF startup nomount exit sql.sqlcode; EOF export ORACLE_SID=$SOURCE_SID rman target / << EOF crosscheck archivelog all; delete expired archivelog all; run { allocate channel disk1 device type disk format '/opt/tmpbak/%U'; backup incremental level 0 tag tmpdb database; backup current controlfile for standby; backup archivelog all delete input; } connect auxiliary sys/p4ssw0rd@tmpdb; run { allocate auxiliary channel t1 type disk; duplicate target database to tmpdb; } exit; EOF export ORACLE_SID=$SOURCE_SID rman target / << EOF crosscheck backup; delete noprompt backup tag='tmpdb'; exit; EOF

delete_tmpdb.sh

#/bin/sh export ORACLE_SID=tmpdb export PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin export ORACLE_BASE=/opt/oracle export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1 export PATH=$ORACLE_HOME/bin:$PATH export LD_LIBRARY_PATH=$ORACLE_HOME/lib sqlplus '/as sysdba' << EOF shutdown abort; exit sql.sqlcode; EOF rm -f $ORACLE_HOME/dbs/orapwtmpdb rm -f $ORACLE_HOME/dbs/inittmpdb.ora rm -f $ORACLE_HOME/dbs/spfiletmpdb.ora rm -f $ORACLE_BASE/oradata/tmpdb/*



Comments