Refresh a test database from Prod rman backup step by step
Non production Database TESTDB refresh using TESTPROD DB
backup
===================================
Pre tasks;-
1)Connect to DBSERVER as oracle user ,
set the envirmonent to TESTDB DB and create a rmfiles.ksh
file to remove all the database files before clone.
set linesize 500
set pagesize 2000
spool rmfiles.ksh
select 'rm '|| file_name from dba_data_files;
select 'rm '|| member from v$logfile;
select 'rm '|| file_name from dba_temp_files;
select 'rm '|| NAME from v$controlfile;
spool off;
2) Connect to DBSERVER as oracle user ,
set the envirmonent to TESTDB DB and create BAU users
password reset .sql file.
spool resetpw.sql
set pagesize 500
select 'alter user '||name||' identified by values
'''||password||''';' from user$ where name in ('AIMS_USER','FRAMEWORK_USER','IBM0240','IBM0098','IBM0275','SYSTEM','SYS','TIVOLI');
spool off;
3)Connect to DBSERVER as oracle user ,
set the envirmonent to TESTDB DB and shut down the DB TESTDB.
shutdown immediate;
4)Connect to DBSERVER as oracle user and
run rmfiles.ksh to remove all the db files.
chmod 755 rmfiles.ksh resetpw.sql
./rmfiles.ksh
5)Refresh DB. Connect
to DBSERVER as oracle user ,
set the envirmonent to TESTPROD
startup nomount;
exit;
6)Connect to DBSERVER as oracle user ,
set the envirmonent to TESTPROD ,
Restore the control file from backup and put the database in mount
state.
Mention from where the control file has to be restored
, input to script.
rman target / catalog rmc_TESTPROD/xxxxxx@rmcprd
run{
allocate channel
ch01 type 'sbt_tape' parms
'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo_TESTPROD.opt)';
set dbid =
24996392;
restore
controlfile from tag='CONTROLFILE 20200218T200508';
CONTROLFILE 20200717T165739
ALTER DATABASE
MOUNT;
release channel
ch01;
}
7)Restore and
recover.
Connect to DBSERVER
as oracle user ,
set the
envirmonent to TESTPROD ,
Restore and
recover from backup and put the database in mount state.
Mention
till what time the restore has to happen,input to script.
run{
allocate channel
ch01 type 'sbt_tape' parms
'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo_TESTPROD.opt)';
allocate channel
ch02 type 'sbt_tape' parms 'ENV=(TDPO_OPTFILE=/opt/tivoli/tsm/client/oracle/bin64/tdpo_TESTPROD.opt)';
SET NEWNAME FOR
DATABASE TO '/ora/TESTDB/001/oradata/TESTDB/%b';
ALTER DATABASE
RENAME FILE '/ora/TESTPROD/901/oradata/TESTPROD/redo03a.log'
TO '/ora/TESTDB/901/oradata/TESTDB/redo03a.log';
ALTER DATABASE
RENAME FILE '/ora/TESTPROD/902/oradata/TESTPROD/redo03b.log'
TO '/ora/TESTDB/902/oradata/TESTDB/redo03b.log';
ALTER DATABASE
RENAME FILE '/ora/TESTPROD/902/oradata/TESTPROD/redo02a.log'
TO '/ora/TESTDB/902/oradata/TESTDB/redo02a.log';
ALTER DATABASE
RENAME FILE '/ora/TESTPROD/901/oradata/TESTPROD/redo02b.log'
TO '/ora/TESTDB/901/oradata/TESTDB/redo02b.log';
ALTER DATABASE
RENAME FILE '/ora/TESTPROD/901/oradata/TESTPROD/redo01a.log'
TO '/ora/TESTDB/901/oradata/TESTDB/redo01a.log';
ALTER DATABASE
RENAME FILE '/ora/TESTPROD/902/oradata/TESTPROD/redo01b.log'
TO '/ora/TESTDB/902/oradata/TESTDB/redo01b.log';
set until time
"to_date ('18-Feb-20 22:30:00' , 'dd-mon-yy
hh24:mi:ss')" ;
SET UNTIL TIME
"to_date ('2020-07-17:19:10:10', 'yyyy-mm-dd:hh24:mi:ss')";
restore database;
SWITCH DATAFILE
ALL;
recover database;
release channel
ch01;
release channel
ch02;
}
exit
8)Open the database with resetlogs.
Connect to DBSERVER
as oracle user ,
set the
envirmonent to TESTPROD and connect to Db.
sqlplus '/as sysdba'
ALTER DATABASE OPEN RESETLOGS;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
exit;
9)We run NID and set a
new name [TESTDB] for our database,
Connect
to DBSERVER as oracle user and run
nid TARGET=SYS DBNAME=TESTDB
Database name changed to TESTDB.
Modify parameter file and
generate a new password file before restarting.
Database ID for database TESTDB
changed to 4135780521.
All previous backups and
archived redo logs for this database are unusable.
Database has been shutdown,
open database with RESETLOGS option.
Succesfully changed database
name and ID.
DBNEWID - Completed
succesfully.
10)Create password file.
Connect
to DBSERVER as oracle user and run
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID
password=abcTESTDB entries=10 force=y
11)Open the TESTDB with
resetlogs.
Connect
to DBSERVER as oracle user , set the envirmonent to TESTDB and connect to Db.
sqlplus '/as sysdba'
startup mount;
alter database open resetlogs;
shutdown immediate;
12)Put it in noarchive
log mode.
Connect
to DBSERVER as oracle user ,
set the
envirmonent to TESTDB and connect to Db.
sqlplus '/as sysdba'
startup mount
alter database noarchivelog;
alter database open;
13)Reset passwords for the BAU
users as is before clone in TESTDB by running resetpw.sql as sys user.
Connect
to DBSERVER as oracle user ,set the envirmonent to TESTDB and connect to Db.
sqlplus '/as sysdba'
@/home/oracle/Refresh/resetpw.sql
14)Connect to DBSERVER as oracle user ,set the
envirmonent to TESTDB and connect to Db.
sqlplus
'/as sysdba'
BEGIN
DBMS_NETWORK_ACL_ADMIN.REMOVE_HOST_ACE (
host => 'mailhost.littlewoods.co.uk',
lower_port => 25,
upper_port => 25,
ace => xs$ace_type(privilege_list =>
xs$name_list('smtp'),
principal_name => 'aims_user',
principal_type => xs_acl.ptype_db),
remove_empty_acl
=> TRUE);
END;
/
15)Create ACL ,Connect to DBSERVER
as oracle user ,set the envirmonent to TESTDB and connect to Db.
sqlplus
'/as sysdba'
BEGIN
DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
host => 'mailhost.littlewoods.co.uk',
lower_port => 25,
upper_port => 25,
ace =>
xs$ace_type(privilege_list => xs$name_list('smtp'),
principal_name => 'aims_user',
principal_type => xs_acl.ptype_db));
END;
/
No comments:
Post a Comment