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