I use below script to refresh our Dev database from Production database monthly, by using the Veritas backup of production database on the tape. It worked well.
ORACLE_SID=devebs ## target database name
export ORACLE_SID
export TNS_ADMIN=$ORACLE_HOME/network/admin
ORACLE_USER=oracle
NLS_LANG=AMERICAN_AMERICA.AL32UTF8
NLS_DATE_FORMAT='YYYY-MM-DD hh24:mi:ss'
export NLS_LANG
export NLS_DATE_FORMAT
NB_ORA_CLASS=ebsdb1p ## server name hosting source database PRODEBS
export NB_ORA_CLASS
DATE=`date +"%h%d%y_%H%M"`
rman catalog rman/rman_pwd@rman_db target sys/source_db_pwd@PRODEBS auxiliary / msglog devEBS_refresh.${DATE}.log << EOF
run {
ALLOCATE AUXILIARY CHANNEL ch00 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL ch01 TYPE 'SBT_TAPE';
ALLOCATE AUXILIARY CHANNEL ch02 TYPE 'SBT_TAPE';
SEND 'NB_ORA_CLIENT=ebsdb1p';
# Optional: put Temp in /u04 because of the size
SET NEWNAME FOR TEMPFILE 1 to '/u04/oracle/oradata/devebs/temp01.dbf';
SET NEWNAME FOR TEMPFILE 2 to '/u03/oracle/oradata/devebs/temp02.dbf';
duplicate target database to devebs
UNTIL scn = 5960105731382
;
}
EOF
In the script, statement "UNTIL scn = 5960105731382" is important. Without it, the script will NOT restore necessary archive logs for recovering the database and the refresh will fail (as tested in 10.2.0.4) unless you can manually get the archive logs from production server (which is not easy in ASM environment) and then do a manual recovery on the dev server.
In that case, the script only restores the data files from the latest backup to the target. You need to get all archive logs generated during the period when the latest backup started to the latest backup finished.
If you need to duplicate the target database on the same host with a different database SID, similar script can be used. See Document 73912.1 for more details.
2 comments:
But how do you know what the SCN is? Do you read from the RMAN catalogue before running the script?
One method is to identify the archive log Sequence number frist from the alert.log or the Veritas backup log. It depends on what time point you want to restore your database to. The Sequence number should be in the archive log name, such as
ebsprod_1_11047_700501878.arc
Then, run a query
SQL> select first_change#, next_change#, first_time FROM v$log_history WHERE sequence# = 11047;
FIRST_CHANGE# NEXT_CHANGE# FIRST_TIME
5968915394775 5968915395329 10/11/2010 10:29:01 PM
==> use 5968915394774 as the SCN in the script will recover the database to the time point just before log 11047 was qenerated.
Post a Comment