Backup and Recovery Scripts

Jack van Zanen’s RMAN Scripts

def TABLESPACE_NAME=test_ts def DATAFILE_NAME=/tmp/test_ts_file1.dbf — Create a test tablespace and table create tablespace &&TABLESPACE_NAME datafile ‘&&DATAFILE_NAME’ size 10M reuse extent management local; create table test (a date) tablespace &&TABLESPACE_NAME; insert into test values (sysdate); — Test if the tablespace can be transported exec sys.dbms_tts.transport_set_check(‘&&TABLESPACE_NAME’, TRUE); prompt Transport set violations: select * from sys.transport_set_violations; alter tablespace…

List completed RMAN Backups for past 24 hours

tti “RMAN Backups for last 24-hours” col type format a4 col handle format a35 trunc col file# format 9999 col duration format a9 select decode(BACKUP_TYPE, ‘L’, ‘ARCH’, ‘D’, ‘DB’, ‘I’, ‘INC’, ‘Unknown type=’||BACKUP_TYPE) TYPE, to_char(a.start_time, ‘DDMON HH24:MI’) start_time, to_char(a.elapsed_seconds/60, ‘99.9’)||’ Min’ DURATION, substr(handle, -35) handle, nvl(d.file#, l.sequence#) file#, nvl(d.blocks, l.blocks) blocks from SYS.V_$BACKUP_SET a, SYS.V_$BACKUP_PIECE…

Summary of datafiles and archlogs backed up with RMAN over past 24 hours

tti “RMAN Status for past 24-hours” — Datafiles Backed up during past 24 Hours SELECT dbfiles||’ from ‘||numfiles “Datafiles backed up”, cfiles “Control Files backed up”, spfiles “SPFiles backed up” FROM (select count(*) numfiles from sys.v_$datafile), (select count(*) dbfiles from sys.v_$backup_datafile a, sys.v_$datafile b where a.file# = b.file# and a.completion_time > sysdate – 1), (select…

Monitor running RMAN Backups

prompt RMAN Backup Status: SELECT to_char(start_time,’DD-MON-YY HH24:MI’) “BACKUP STARTED”, sofar, totalwork, elapsed_seconds/60 “ELAPSE (Min)”, round(sofar/totalwork*100,2) “Complete%” FROM sys.v_$session_longops WHERE compnam = ‘dbms_backup_restore’ /

List RMAN Backups registered in RMAN catalog database

connect rman/rman col media format a8 col tag format a12 trunc col minutes format 990 select d.name, p.tag, p.media, s.incremental_level “LEVEL”, to_char(s.start_time, ‘DD-MON-YY HH24:MI’) start_time, s.elapsed_seconds/60 “MINUTES” from RC_DATABASE d, RC_BACKUP_PIECE p, RC_BACKUP_SET s where d.name = ‘WH’ and s.start_time > ’04-MAY-02′ and s.completion_time < '06-MAY-02' and p.backup_type = 'D' -- D=Database, L=Log and d.db_key...

Take database data files out of backup mode

connect internal spool end_backup2.log select ‘alter database datafile ”’||f.name||”’ end backup;’ from v$datafile f, v$backup b where b.file# = f.file# and b.status = ‘ACTIVE’ / spool off ! grep ‘^alter’ end_backup2.log >end_backup2.log2 @end_backup2.log2 ! rm end_baclup.log ! rm end_backup.log2 exit

Check for tablespaces in backup mode and take them out of backup mode

column cmd format a80 heading “Text” set feedback off set heading off set pagesize 0 spool end_backup_script.sql select ‘alter tablespace ‘||a.tablespace_name||’ end backup;’ cmd from sys.dba_data_files a, sys.v_$backup b where b.status = ‘ACTIVE’ and b.file# = a.file_id group by a.tablespace_name / spool off set feedback on set heading on set pagesize 24 set termout on…