Test database refresh

[rygi]

Greetings,

Would there be an easy way to automate a procedure that would drop a test database and refresh it with a backup from its production counterpart?

Thanks.

[sambavan]

Though you have to write some scripts, RMAN would make it feasible for you.

Sam

[alkesh]

Hi
below give is the script, which will automate cold clonning on the other machine. Now these require to shutdown the production, I suppose u could do the same using hot backup and run recovery on the files.
These would give u atleast the idead how to go about automating the same.
Regards
Alkesh


# Backup Control File to Trace,Make a copy script for datafiles and shutdown the
ORACLE_SID=ftCSdb; export ORACLE_SID
ORACLE_HOME=/u01/app/oracle/product/8.1.6; export ORACLE_HOME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
/u01/app/oracle/product/8.1.6/bin/sqlplus -s /nolog <<EOF
connect sys/test@ftCSdb as sysdba;
alter database backup controlfile to trace;
set feedback off
set heading off
set line 100
spool $HOME/script/copy.sh
select 'rcp orgrugs01:'||file_name||' '||file_name from dba_data_files;
spool off;
shutdown immediate;
quit;
EOF
$HOME/script/copy.sh
# Trim the trace file to suit the clone script
trace=`rsh orgrugs01 ls -ltr /opt/oracle/app/oracle/admin/$ORACLE_SID/udump|tail
rcp orgrugs01:/opt/oracle/app/oracle/admin/$ORACLE_SID/udump/$trace $HOME/script
cp /dev/null $HOME/script/tmp.sql
cp /dev/null $HOME/script/cr8ctrl.sql
tail -40 $HOME/script/trace |while read LINE
do
a=`echo $LINE|awk {'print $1'}`
if [ $a = '#' ]; then
exit;
fi
echo $LINE >> $HOME/script/tmp.sql
done
cat $HOME/script/tmp.sql|sed s/DATABASE/"SET DATABASE"/g|sed s/ARCHIVELOG/NOARCHIVELOG/g|sed s/NORESETLOGS/RESETLOGS/g > $HOME/script/cr8ctrl.sql
echo "alter database open resetlogs;" >>$HOME/script/cr8ctrl.sql
echo "disconnect;" >>$HOME/script/cr8ctrl.sql
echo "exit;" >>$HOME/script/cr8ctrl.sql
# Create new control file from the trace for clone dbs and start the Clone dbs
/u01/app/oracle/product/8.1.6/bin/svrmgrl <<EOF
connect internal;
@/export/home/oracle/script/cr8ctrl.sql
EOF
# Start the Remote Database
/u01/app/oracle/product/8.1.6/bin/sqlplus -s /nolog <<EOF
connect sys/test@ftCSdb as sysdba;
startup pfile='/export/home/oracle/ftCSdb.ora';
EOF