RMAN Active Duplicate Script

Source: PRD1
Target: TST1

1. Add Tnsnames entries of PRD1 in TST1
2. Add Tnsnames entries of TST1 in PRD1
3. Make sure both the listeners are up on Source and Target
4. Create orapwd file in TST1

orapwd file=$ORACLE_HOME/dbs/TST1 password=manager entries=10

select * from v$pwfile_users;

5. Keep these paramters in TST1 pfile

*.db_file_name_convert='/data/app/PRD1/db/apps_st/data','/data/app/TST1/db/apps_st/data','/index/app/PRD1/db/apps_st/data','/index/app/TST1/db/apps_st/data'
*.log_file_name_convert='/redo/app/PRD1/db/apps_st/data','/redo/app/TST1/db/apps_st/data'

6. Connect to TST1
   startup nomount

vi TST1_active_dup_db.sh

$ORACLE_HOME/bin/rman nocatalog log=TST1_active_dup_db.log <<EOF
connect target sys/manager@PRD1
connect auxiliary sys/manager@TST1
duplicate target database to TST1 from active database nofilenamecheck;
EOF

nohup sh TST1_active_dup_db.sh &

RMAN Validate Script

rman target /

RMAN> spool log to validate_rman.txt
RMAN> BACKUP VALIDATE CHECK LOGICAL  DATABASE ARCHIVELOG ALL;

SQL> select name from v$database;

NAME
---------
SPROD

SQL> select * from v$database_block_corruption;

no rows selected

RMAN Backup Details

col STATUS format a9
col hrs format 999.99
select
SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi')   end_time,
elapsed_seconds/3600                   hrs
from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;


select object_type,OUTPUT_DEVICE_TYPE,OPERATION,STATUS,START_TIME,END_TIME,OUTPUT_BYTES/1024/1024/1024 as Backup_SIZE_GB
from v$rman_status
 where OBJECT_TYPE is not null and START_TIME > sysdate -2
order by START_TIME;

RMAN Backup Monitor/Progress scripts

REM RMAN Progress
set lines 120
set pagesize 200
alter session set nls_date_format='dd/mm/yy hh24:mi:ss';
select SID, START_TIME,TOTALWORK, sofar, (sofar/totalwork) * 100 done,
sysdate + TIME_REMAINING/3600/24 end_at
from v$session_longops
where totalwork > sofar
AND opname NOT LIKE '%aggregate%'
AND opname like 'RMAN%'
/

REM RMAN Progress

set lines 200

set pagesize 200

alter session set nls_date_format='dd/mm/yy hh24:mi:ss';

select inst_id,sid,start_time,opname, round((sofar/totalwork)*100) "% Completed",sysdate + time_remaining/3600/24 will_end_at from gv$session_longops where totalwork > sofar and opname not like '%aggregate%' and opname like 'RMAN%' order by 1;

REM RMAN waits

set lines 200

column sid format 9999

column spid format 99999

column client_info format a25

column event format a30

column secs format 9999

SELECT SID, SPID, CLIENT_INFO, event, seconds_in_wait secs, p1, p2, p3

  FROM V$PROCESS p, V$SESSION s

  WHERE p.ADDR = s.PADDR

  and CLIENT_INFO like 'rman channel=%'

/

RMAN DB Duplicate Script

$ORACLE_HOME/bin/rman nocatalog log=rman_restore_STG4_$(date +"%Y-%m-%d-%S").log <<EOF
connect auxiliary sys/manager@STG4
run
{
allocate auxiliary channel dup1 type disk;
allocate auxiliary channel dup2 type disk;
allocate auxiliary channel dup3 type disk;
allocate auxiliary channel dup4 type disk;
allocate auxiliary channel dup5 type disk;
allocate auxiliary channel dup6 type disk;
allocate auxiliary channel dup7 type disk;
allocate auxiliary channel dup8 type disk;
allocate auxiliary channel dup9 type disk;
allocate auxiliary channel dup10 type disk;
duplicate target database to STG4 nofilenamecheck backup location '/backups/rman/PRD2/';
}
EOF

RMAN Cold Backup Script

rman target / <<EOF > TPROD_COLDBACKUP_11JAN09.log
run
{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate channel c3 type disk;
allocate channel c4 type disk;
backup as compressed backupset
TAG=COLDBKUP_TPROD_11JAN09
filesperset=10
format '/backups/db_backup/COLDBKUP_TPROD/TPROD_%d%s%t_%U' database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
exit
EOF

Oracle DST upgrade procedure from V14 to V25

DST upgrade procedure from V14 to V25

1. Check the invalid count before upgrade

select count(1) from dba_objects where status='INVALID';

2. Check the Tz version before upgrade.

SQL> SELECT version FROM v$timezone_file;

   VERSION
---------------
14

3. Bringdown apps services and Database and listerner

$ADMIN_SCRIPTS_HOME/adstpall.sh apps/apps

sqlplus '/ as sysdba'
shut immediate
lsnrctl stop TEST2

4. Set the inventory

cat /etc/oraInst.loc
inventory_loc=/etc/oracle/oraInventory
inst_group=dba

5. Verify the inventory

$ORACLE_HOME/OPatch/opatch lsinventory

6.Apply DST25 Patch

cd /stageall/patches/DST/22037014
$ORACLE_HOME/OPatch/opatch apply

7.Verify patch applied or not?

$ORACLE_HOME/OPatch/opatch lsinventory | grep 22037014

8.Post steps of DST  patch

Download scripts from Note: 1585343.1

cd /stagall/patches/DST/DBMS_DST_scriptsV1.9

Conn / as sysdba
spool countstatsTSTZ.log
@countstatsTSTZ.sql
spool off

Conn / as sysdba
spool upg_tzv_check.log
@upg_tzv_check.sql
spool off

Conn / as sysdba
spool upg_tzv_apply.log
@upg_tzv_apply.sql
spool off

9.Verify DST version post upgrade

SQL> SELECT version FROM v$timezone_file;

VERSION
--------------
25

10.Verify invalid objects

select count(1) from dba_objects where status='INVALID';

11. Bring up services, validate the environment and release
------------------------------------------------------------
sqlplus ' /as sysdba'
startup
exit
lsnrctl start TEST2


$ADMIN_SCRIPTS_HOME/adstrtal.sh apps/apps


Ref Note : Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database . (Doc ID 1585343.1)