Duplication

OracleDB‎ > ‎

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

	Search this site