Generate RMAN Script for the Production to Test environment refresh with Duplicate Command

RMAN Duplicate command used to generate script for prod to test environment

Backup the User Password of Test Environment

-- Run at Test Environment.
spool reset_passwords_after_new_db.sql
select 'alter user ' || username || ' identified by values ''' || password || ''';' from dba_users order by 1;
spool off

Note: Match the Production and Test user if Test has extra user then take EXPDP backup of that user.

Generate RMAN Script for the Production to Test environment refresh with Duplicate Command

--- First Generate the RMAN shell command

spool dup_TEST.sh
prompt #!/bin/bash
prompt date
prompt . /u01/app/oracle/.bash_profile 2> /dev/null
prompt DATO=`date +%d%m%EY`
prompt ORAENV_ASK=NO
prompt ORACLE_SID=TEST; . oraenv
prompt export DATO ORACLE_SID
prompt rman catalog username/password@rmancatalog target username/password@PROD auxiliary username/password@TEST cmdfile dup_test.rcv log dup_test.log
spool off
---Second Generate the RMAN SCRIPT DUP_TEST.RCV

spool dup_test.rcv
prompt run {
prompt --restore time = Last_successful_DB_backup_completed_time + 30 Mins...
SELECT 'set until time "to_date(''' ||END_TIME|| ''',''yyyy/mm/dd HH24:MI:SS'')";'
FROM (select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,'yyyy/mm/dd HH24:MI:SS') start_time, to_char(END_TIME + 30/1440,'yyyy/mm/dd HH24:MI:SS') end_time, round((end_time-start_time)*1440,2) "Minutes"
from V$RMAN_BACKUP_JOB_DETAILS where INPUT_TYPE='DB INCR' AND STATUS='COMPLETED' order by end_time desc) WHERE ROWNUM = 1;
prompt allocate auxiliary channel C1 device type DISK;
prompt allocate auxiliary channel C2 device type DISK;
---IF using tape then use following parameter
---prompt allocate auxiliary channel C1 device type 'sbt_tape';
---prompt send 'NB_ORA_SERV=tivolibackup.orcl.in,NB_ORA_CLIENT=node1bk';
select 'set newname for datafile ' ||file#|| ' to ''' ||replace(name,'/u02/oradata','/u03')|| ''';' from v$datafile order by file#;
prompt duplicate target database to TEST;
prompt logfile
select '''' || replace(MEMBER,'/u02/oradata','/u03')|| ''' size 500m,' from v$logfile;
}
spool off
This entry was posted in Oracle on by .

About SandeepSingh DBA

Hi, I am working in IT industry with having more than 10 year of experience, worked as an Oracle DBA with a Company and handling different databases like Oracle, SQL Server , DB2 etc Worked as a Development and Database Administrator.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.