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/*