Automating Database Refreshes using BCV

Steps:
1. Backup’s of the tables/database.
2. BCV snapshot of the SOURCE database
3. BRING DOWN THE LISTENERS
4. TAKE A TRACE OF THE CONTROL FILE
5. REUSE THE TRACE CONTROL FILE to MAKE THE CONTROL FILE OF THE SOURCE DATABASE ON THE TEST SERVER
6. USE THE TRACE CONTROL FILE to MAKE THE CONTROL FILE OF THE TARGET DATABASE ON THE TEST SERVER
7. RUN all THE POST DATABASE SCRIPTS
8. BRING UP THE LISTENERS



BACKUP of the tables/Databases

Please ensure that before you start the refresh process you take a backup of the tables which are needed to be backed up.


BCV snapshot of the database

You can do this step in coordination with unix team where in you can request unix team/System Administrator to take a BCV snap shot of the database server from which you needs your development/test database to be refreshed. For this methodology storage team use to set up some SRDF disks on the server on which the BCV needs to be set up. Here I am taking an assumption that production database name is SOURCE-DB and the database needs to be refreshed is TARGET-DB

Once BCV is completed by the SA’s these BCV disks are placed on the dev/test server. Once this is completed, The BCV disks will be looking as below:


/dev/vx/dsk/bldmx1dg/d1000n
10G 7.6G 2.3G 78% /d1000b
/dev/vx/dsk/bldmx1dg/d1001n
172G 156G 15G 92% /d1001b
/dev/vx/dsk/bldmx1dg/d1002n
182G 119G 59G 68% /d1002b
/dev/vx/dsk/bldmx1dg/d1003n
182G 122G 57G 69% /d1003b
/dev/vx/dsk/bldmx1dg/d1004n
182G 118G 60G 67% /d1004b
/dev/vx/dsk/bldmx1dg/d1005
182G 112G 66G 63% /d1005b
/dev/vx/dsk/bldmx1dg/d1006
182G 182G 92M 100% /d1006b
/dev/vx/dsk/bldmx1dg/d1007
182G 155G 25G 87% /d1007b
/dev/vx/dsk/bldmx1dg/d1008
182G 128G 51G 72% /d1008b
/dev/vx/dsk/bldmx1dg/d1009
182G 137G 42G 77% /d1009b
/dev/vx/dsk/bldmx1dg/d1010
182G 82G 94G 47% /d1010b
/dev/vx/dsk/bldmx1dg/d1011
182G 97G 80G 55% /d1011b
/dev/vx/dsk/bldmx1dg/d1012
182G 61G 114G 35% /d1012b
/dev/vx/dsk/bldmx1dg/d1013
182G 64G 111G 37% /d1013b
/dev/vx/dsk/bldmx1dg/d1014
182G 110G 67G 63% /d1014b
/dev/vx/dsk/bldmx1dg/d1015
182G 98G 79G 56% /d1015b
/dev/vx/dsk/bldmx1dg/d1016
364G 363G 945M 100% /d1016b
/dev/vx/dsk/bldmx1dg/d1017
364G 360G 3.5G 100% /d1017b



Above ‘b’ in /d****b represents the BCV disks.



BRING DOWN THE LISTENERS


echo "Stopping listener : `date`" >> ${BCV_LOG}

lsnrctl stop

TAKE A TRACE OF THE CONTROL FILE


Here we use to bring up the database as SOURCE-DB on the test server. This is done because while the database BCV was done there were already many transactions are running on the database. So you need to shut down the database cleanly.

Below is the script that we use to bring up SOURCE database on the test server and then shut it down cleanly to have its SMON do all the transaction recovery.

Step1: set up the oracle environments. Here in my case I am using script /var/opt/oracle/env/.env to set up the environment

/var/opt/oracle/env/.env

Set the ORACLE_SID correctly.

export ORACLE_SID=

Connect as sysdba to the database and redirecting this to a log file $BCV_LOG
sqlplus "/ as sysdba" <>${BCV_LOG}

Startup the database with the spfile/pfile of the source database. Here you can use of minimal parameters which are required to bring up the database
startup mount pfile=/var/opt/oracle/product/9.2.0.5.0/dbs/init.ora;



The above command will bring up the database SOURCE-DB on the target server using the control files of SOURCE-DB on the target server that is a copy of the CONTROL file of the SOURCE DATABASE of the source server.

Now take a trace of the control file. We are doing this so as to REUSE and recreate the control file of the SOURCE DATABASE on the test box.


alter database backup controlfile to trace as '/d1000/var/opt/oracle/admin//udump/control__$proc_date.trc'; --------------------------------------1

Now shut down this database
shutdown immediate
exit
eot

REUSE THE TRACE CONTROL FILE to MAKE THE CONTROL FILE OF THE SOURCE DATABASE ON THE TEST SERVER

Below we are going to the location where the command 1 will create the trace file.

echo "Creating control file script for : `date`" >> ${BCV_LOG}
cd /d1000/var/opt/oracle/admin//udump

Now below is what we are doing:
i. Below is the sample trace file which we get on execting step1



ii. On this trace file we are running the below script which will give us a modified script where in below will be the changes
TARGET-DB to SOURCE-DB
RESETLOG to NORESETLOGS
ARCHIVELOG to NOARCHIVELOG

Also because the mount point name (in my case its /d***b)) on the target server is different as compared to that of the SOURCE SERVER(here name is /d****). If this is not the case in your case you can skip the below changes
d1001/d1001b
d1002/d1002b
d1003/d1003b
d1004/d1004b
d1005/d1005b
d1006/d1006b
d1007/d1007b
d1008/d1008b
d1009/d1009b
d1010/d1010b
d1011/d1011b
d1012/d1012b
d1013/d1013b
d1014/d1014b
d1015/d1015b
d1016/d1016b
d1017/d1017b
iii. Here basically there are two control file script in the trace file so we are taking one and passing all these to file control_.sql.
iv. Below is the script that we are using.

sed -e "1,/# be invalidated/d" -e "s///g" -e "s/RESETLOGS/NORESETLOGS/g" -e "s/ARCHIVELOG/NOARCHIVELOG/g" -e "/d200/d" -e "s/d1001/d1001b/g" -e "s/d1002/d1002b/g" -e "s/d1003/d1003b/g" -e "s/d1004/d1004b/g" -e "s/d1005/d1005b/g" -e "s/d1006/d1006b/g" -e "s/d1007/d1007b/g" -e "s/d1008/d1008b/g" -e "s/d1009/d1009b/g" -e "s/d1010/d1010b/g" -e "s/d1011/d1011b/g" -e "s/d1012/d1012b/g" -e "s/d1013/d1013b/g" -e "s/d1014/d1014b/g" -e "s/d1015/d1015b/g" -e "s/d1016/d1016b/g" -e "s/d1017/d1017b/g" -e "/^# Configure snapshot controlfile filename/,/^$/d" -e "s/STARTUP NOMOUNT//g" -e "/^$/d" -e "s/ALTER/---ALTER/g" control__$proc_date.trc > control_.sql


v. Run the generated control file for the database.

. /var/opt/oracle/env/.env
export ORACLE_SID=
sqlplus "/ as sysdba" <>${BCV_LOG}

startup nomount pfile=/var/opt/oracle/product/9.2.0.5.0/dbs/init.ora;
@control_.sql

This will create the control file for the SOURCE-DB. Above we are bringing up the instance as nomount using the pfile of the SOURCE-DB.

vi. Recovery
As ofcourse the database will ask for recovery and we will recover as below and once the media recovery completes we will be open the database.

recover database;
alter database open;


vii. Do a clean shut down of this database
DO a clean shut down of this database as this will disable the TX recovery process as well.

shutdown immediate;
eot









USE THE TRACE CONTROL FILE to MAKE THE CONTROL FILE OF THE TARGET DATABASE ON THE TEST SERVER



viii. On this trace file we are running the below script which will give us a modified script where in below will be the changes
REUSE to SET
SOURCE-DB to TARGET-DB
NORESETLOG to RESETLOGS
NOARCHIVELOG to ARCHIVELOG

Also because the mount point name (in my case its /d***b)) on the target server is different as compared to that of the SOURCE SERVER(here name is /d****). If this is not the case in your case you can skip the below changes
d1001/d1001b
d1002/d1002b
d1003/d1003b
d1004/d1004b
d1005/d1005b
d1006/d1006b
d1007/d1007b
d1008/d1008b
d1009/d1009b
d1010/d1010b
d1011/d1011b
d1012/d1012b
d1013/d1013b
d1014/d1014b
d1015/d1015b
d1016/d1016b
d1017/d1017b
ix. Here basically there are two control file script in the trace file so we are taking one (here with the SET option) and passing all these to file control_.sql

x. Below is the script that we are using.


echo "Creating control file script for : `date`" >> ${BCV_LOG}
cd /d1000/var/opt/oracle/admin//udump


sed -e "1,/# be invalidated/d" -e "s/REUSE/SET/g" -e "s///g" -e "s/NORESETLOGS/RESETLOGS/g" -e "s/ARCHIVELOG/NOARCHIVELOG/g" -e "/d200/d" -e "s/d1001/d1001b/g" -e "s/d1002/d1002b/g" -e "s/d1003/d1003b/g" -e "s/d1004/d1004b/g" -e "s/d1005/d1005b/g" -e "s/d1006/d1006b/g" -e "s/d1007/d1007b/g" -e "s/d1008/d1008b/g" -e "s/d1009/d1009b/g" -e "s/d1010/d1010b/g" -e "s/d1011/d1011b/g" -e "s/d1012/d1012b/g" -e "s/d1013/d1013b/g" -e "s/d1014/d1014b/g" -e "s/d1015/d1015b/g" -e "s/d1016/d1016b/g" -e "s/d1017/d1017b/g" -e "/^# Configure snapshot controlfile filename/,/^$/d" -e "s/STARTUP NOMOUNT//g" -e "/^$/d" "s/ALTER/---ALTER/g" control__$proc_date.trc > control_.sql

xi. Run the generated control file for the database.
xii. Here before you run the control file script @control_.sql, you need to make sure that you are removing/moving the control files which are older one and are on the same location as per the pfile/spfile that is being used to bring up SOURCE-DB database.


mv /d1001b/oradata//control01.ctl /d1001b/oradata//control01.ctl.bkp
mv /d1002b/oradata//control02.ctl /d1002b/oradata//control02.ctl.bkp

. /var/opt/oracle/env/.env
export ORACLE_SID=
sqlplus "/ as sysdba" <>${BCV_LOG}

startup nomount
@control_.sql

This will create the control file for the SOURCE-DB. Above we are bringing up the instance as nomount using the pfile of the SOURCE-DB.

xiii. Reset all the logs
Here as we don’t need the redo logs of the SOURCE-DB reset all the logs

alter database open resetlogs;

xiv. shut down of this database
DO a clean shut down of this database as this will disable the TX recovery process as well.

shutdown immediate;
eot

xv. Create the password file
Before creating remove the older password file

cd $ORACLE_HOME/dbs
rm orapw
orapwd file=orapw password=spongeb entries=5

xvi. Start the database

export ORACLE_SID=
sqlplus "/ as sysdba" <>${BCV_LOG}



RUN all THE POST DATABASE SCRIPTS

You may need to run the past database refresh scripts like below and if anything else you may include in your script as well.

!sleep 10
set echo on
spool post_refresh__$proc_date.lst
@/var/opt/oracle/scripts/POST_change_passwords.sql
@/var/opt/oracle/scripts/post__sql.sql
@/var/opt/oracle/scripts//post__others.sql


spool off
exit

eof

BRING UP THE LISTENERS

echo "Starting listener : `date`" >> ${BCV_LOG}

lsnrctl start