Database recovery 3
Section-I Saving files prior to refresh process 3
crontab files 3
Oracle files 3
Section-II Recovery of database as vba1 3
1 Identify the control file for the database recovery 3
2 Recover database 3
Section-III Renaming vba1 database to perf 4
1 Create control files for perf database 4
2 Post recovery 4
Section-IV Post refresh 5
1 Reset SITEMINDER account data 5
2 Reset ADOBE account data 5
3 Create application accounts 5
Overview
The refresh process on vbaperf transfers a copy of the production environment to vbaperf. Usually this process runs quarterly or on demand and takes about 2-3 days to complete.
The refresh is of two types, all or database only. In any case, you need database recovery. The files are copied from the backup of the production database, usually called a Gold Copy. The Gold Copy is copied to BCV disks of vbaperf. The SA for vbaperf will make sure that the OS part of the refresh is working and DBA will work on the database side of refresh. This document will describe the recovery steps and post procedure for database recovery.
Remember that, there are two instances of Oracle databases in production environment; vba1 and bdnprod. The steps described in the next sections should be performed for both the databases separately.
Database recovery
Section-I Saving files prior to refresh process
crontab files
· Save crontab file for Oracle and webrept accounts
Oracle files
· $ORACLE_HOME/dbs/Initperf.ora
· $ORACLE_HOME/dbs/Initbdnperf.ora
· $ORACLE_HOME/network/admin/Tnsnames.ora
· $ORACLE_HOME/ network/admin /Listener.ora
· $ORACLE_HOME/ network/admin /Sqlnet.ora
Section-II Recovery of database as vba1
1 Identify the control file for the database recovery
· Login as oracle
· Run command ‘. setsid’ with input ‘vba1’
· Make sure ORACLE_HOME and ORACLE_SID (in this case it will be vba1) values are set appropriately
· Comment out all jobs from crontab entries
· List (ls –l ) all control files for the databases
· Save control files by copying ( cp –p) to a backup directory. For example, /tmp/create
· Identify the oldest time stamp for the control file
· Go to directory /282arch1/oracle/vba1/cfile and look for the backup control file around the time stamp that you found in earlier step. This way, you will need least number of archive logs. For bdnprod database, the directory is /282bdnarch1/oracle/bdnprod/cfile
· Copy identified backup control file to all control files
2 Recover database
· Before you start recovery, remember that this backup was taken by putting all tablespaces in hot mode
· In sqlplus session, run command ‘recover database using backup control file until cancel’ to start database recovery
· One by one, database will ask for the archive files. Use your DBA judgment for how many archive logs to apply
· Recover the database
· Run command ‘alter database backup controlfile to trace’ to create a trace file for creating script for the control files. Say crPerf.sql
· Review the log and shutdown database
Section-III Renaming vba1 database to perf
1 Create control files for perf database
· Go to directory $ORACLE_HOME/dbs. Copy file initvba1.ora to initperf.ora. Edit the file to change vba1 text to perf to indicate perf database.
· Make sure all directory entries in the initperf.ora file exist on vbaperf
· In sqlplus session, login as ‘/ as sysdba’ and run script to create control file that was modified in the previous steps. Say crPerf.sql
· Run command ‘alter database open restlogs’
· Make sure the are no ‘MISSING*’ files in the database. Check the script crPerf.sql for details
· Add temporary data files to the database
2 Post recovery
· Delete the archive logs from directory /282arch1/oracle/vba1/log and /282bdnarch1/oracle/bdnprod/log once both databases are recovered.
· Delete the backup control files from directory /282arch1/oracle/vba1/cfile and /282arch1/oracle/bdnprod/cfile once both databases are recovered.
· Delete entries from /var/opt/oracle/oratab for vba1 and bdnprod databases
· Grant dba to sqlexcoll account and unlock this account
· Change password for perfstat account
· Truncate all perfstat account tables using $ORACLE_HOME/rdbms/admin/sptrunc.sql script
· Change frequency of collecting statspack to every 15 min interval. To achieve this, run following commands:
o Login as oracle and go to directory /usr/vba/dba/adm/COMMON/statspack
o Run command ‘JobStop perf’
o Run command ‘JobStart -m15 perf’
Section-IV Post refresh
A specific database export (backup) is run every night and saved in directory /vbaperf/dbexport/exp/perf/dmp. This includes
· Full export of account SITEMINDER
· Full export of account ADOBE
· Full export of BMCEDS database
1 Reset SITEMINDER account data
· In sqlplus, drop all tables for account SITEMINDER
· Locate the valid backup prior to refresh in /vbaperf/dbexport/exp/perf/dmp
· Import the dump file using following command
o imp sqlexcoll fromuser=siteminder touser=siteminder file=
2 Reset ADOBE account data
· In sqlplus, run command ‘drop user adobe cascade’
· Create user ADOBE with following sql commands
o Create user adobe identified by
o Alter user adobe quota unlimited on adobetbs
o Grant connect to adobe;
o Grant hines_resource to adobe;
· Locate the valid backup prior to refresh in /vbaperf/dbexport/exp/perf/dmp
· Import the dump file using following command
o imp sqlexcoll fromuser=siteminder touser=ADOBE file=
3 Create application accounts ( CSUM )
This script will create application users in the database. Every user is attached to a station number and he or she is authorized to work with certain applications. The procedure add_to_stn will add user to a station number and the procedure add_to_applcn_at_stn will assign user to the application at a specific station number. As station numbers and applications are constantly added in the database, the script needs constant modification. The script needs to be reviewed by the team lead for the LoadRunner team. The users details are listed in the top portion of the script. If the user line has ‘#’ in front, the script will not run for that particular user.
· In sqlplus, run following scripts as ‘corpprod’ user
o delete_ops.sql
o add_to_stn.sql
o add_to_applcn_at_stn.sql
· At UNIX level, run command
o crCsumUsers.ksh
0 comments:
Post a Comment