I'll be using as a example the following config:
-Source database: PRODDB on host PROD
-Destination database: TESTDB on host TEST
-Oracle database 10g/11g with ASM
-Backup and archived redo logs of PRODDB on a nfs location accessible from the two hosts (not needed if using FROM ACTIVE DATABASE option)
-PRODDB database uses a catalog for rman backups.
- ASM has two diskgroups: FLASH (for FLASH RECOVERY AREA) and DATA (for regular database files) on both hosts. ASM owner is the same as db owner.
1. Put a static entry in listener.ora of TEST host
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = +ASM)
(SERVICE_NAME = +ASM)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/grid)
)
(SID_DESC =
(GLOBAL_DBNAME = TSTDB)
(SID_NAME = TSTDB)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/db_1)
)
)
2. Put an entry in tnsnames.ora file of both hosts for the two databases
On host PROD:
TEST_TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb)
(INSTANCE_NAME = testdb)
)
)
On host TEST:
TEST_TESTDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = testdb)
(INSTANCE_NAME = testdb)
)
)
PROD_PRODDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = prod)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = proddb)
(INSTANCE_NAME = proddb)
)
)
3. Copy orapwd file from PROD to TEST (needed if using FROM ACTIVE DATABASE option) and rename it accordingly
4. Create an spfile for your cloned database.
You can make a copy of spfile of PRODDB, edit it to adapt and save as spfile for TESTDB database. At least you will need to change the following:
DB_NAME = 'TESTDB'
5. Make sure you have the necessary backup and archived redo logs for PRODDB, accessible from TEST host (not needed if using FROM ACTIVE DATABASE option)
6. After this preparation steps, you can now run the script:
The script is meant to be run on TESTDB can be put in a crontab, so that is executes on a regular basis.
There are two variants depending if you use FROM ACTIVE DATABASE option (11gR1 onwards) or not. The other option (duplicate from backup) is commented.
First we delete the TESTDB database and then proceed with the new clone.
THIS IS JUST AN EXAMPLE, PLEASE REVIEW IT AND ADAPT BEFORE RUNNING!!
#!/bin/bash
export DB_NAME=TESTDB
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1
export ORACLE_SID=TESTDB
DAY_OF_WEEK=`date +%u`
BCK_LOG=bck_duplicate-${DAY_OF_WEEK}.log
echo "************************************" >>${BCK_LOG}
echo "** Start database in NOMOUNT state *" >>${BCK_LOG}
echo "** `date` - `hostname` *" >>${BCK_LOG}
echo "************************************" >>${BCK_LOG}
sqlplus / as sysdba <<EOF >>${BCK_LOG}
shutdown immediate;
startup nomount;
exit
EOF
# Before dropping the existing database make sure we have rman connectivity
rman target sys/passwd@PROD_PRODDB auxiliary sys/passwd@TEST_TESTDB <<EOF >/tmp/check_rman_connect.log
exit
EOF
if grep -q "^RMAN-" /tmp/check_rman_connect.log; then
echo "RMAN connectivity not properly established; aborting duplicate"
sqlplus / as sysdba <<EOF >>${BCK_LOG}
alter database mount;
alter database open;
exit
EOF
exit 1
fi
sleep 10
echo "************************************" >>${BCK_LOG}
echo "** ASM DROP *" >>${BCK_LOG}
echo "** `date` - `hostname` *" >>${BCK_LOG}
echo "************************************" >>${BCK_LOG}
# Establish ASM env variables (ORACLE_HOME, ORACLE_SID and PATH)
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/grid
export ORACLE_SID="+ASM"
export PATH=$ORACLE_HOME/bin:$PATH
asmcmd << EOF >>${BCK_LOG} 2>&1
cd data
cd $DB_NAME
rm -f datafile/*
rm -f onlinelog/*
rm -f tempfile/*
cd ..
cd ..
cd flash
cd $DB_NAME
rm -f onlinelog/*
rm -rf archivelog/*
rm -f flashback/*
exit
EOF
START_TIME=`date +%s`
echo "************************************">>${BCK_LOG}
echo "** db duplicate *">>${BCK_LOG}
echo "** `date` - `hostname` *">>${BCK_LOG}
echo "************************************">>${BCK_LOG}
# Establish DB env variables (ORACLE_HOME, ORACLE_SID and PATH)
export ORACLE_HOME=/u01/app/oracle/product/11.2.0.3/db_1
export ORACLE_SID="TESTDB"
export PATH=$ORACLE_HOME/bin:$PATH
rman target sys/passwd@PROD_PRODDB auxiliary sys/passwd@TEST_TESTDB <<EOF >>${BCK_LOG}
duplicate target database to 'TESTDB' from active database
NOFILENAMECHECK
LOGFILE
GROUP 1 ('+DATA', '+FLASH') SIZE 100M,
GROUP 2 ('+DATA', '+FLASH') SIZE 100M,
GROUP 3 ('+DATA', '+FLASH') SIZE 100M;
EOF
# Duplicate from backup
# Comment out the following lines if you want to clone from a backup of PRODDB
# make sure you have the backup and the latest archived redo logs.
#rman target sys/passwd@PROD_PRODDB catalog catusr/catpwd@CAT auxiliary sys/passwd@TEST_TESTDB <<EOF >>${BCK_LOG}
#run {
#ALLOCATE CHANNEL ch1 DEVICE TYPE disk;
#ALLOCATE AUXILIARY CHANNEL ch2 DEVICE TYPE disk;
#ALLOCATE AUXILIARY CHANNEL ch2 DEVICE TYPE disk;
#DUPLICATE TARGET DATABASE TO 'TESTDB'
#LOGFILE
# GROUP 1 ('+DATA', '+FLASH') SIZE 100M,
# GROUP 2 ('+DATA', '+FLASH') SIZE 100M,
# GROUP 3 ('+DATA', '+FLAHS') SIZE 100M;
#}
#EOF
if ! grep -q "^database opened" ${BCK_LOG}
then
echo "************************************" >>${BCK_LOG}
echo "** ERROR $0 *" >>${BCK_LOG}
echo "** Error in duplicate ${BCK_LOG} *" >>${BCK_LOG}
echo "** `date` - `hostname` *" >>${BCK_LOG}
echo "************************************" >>${BCK_LOG}
exit 1
fi
END_TIME=`date +%s`
let TIME_SPENT=($END_TIME-$START_TIME)/60
echo "****************************************" >>${BCK_LOG}
echo "**Duplicate OK in $TIME_SPENT minutes *" >>${BCK_LOG}
echo "** `date` - `hostname` *" >>${BCK_LOG}
echo "****************************************" >>${BCK_LOG}
No comments:
Post a Comment