Oct 6, 2005

RMAN Maintenance

RMAN> show all;

RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 14;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/dbBackup/hotbackup/controlfile%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/dbhome/product/10.2.0/dbs/snapcf_tedsdb.f'; # default

--
--List
--
list backup;
list backup summary;
list backup by file;
list expired backup;
list copy of database archivelog all;

--
--Report
--
report obsolete;
report need backup;

--
--Delete
--
delete obsolete;
delete force obsolete;

--
--Crosscheck
--
crosscheck backup;
delete expired backup;

--
--Change
--

CHANGE BACKUPSET TAG year_end_2005 KEEP FOREVER NOLOGS;

CHANGE BACKUPSET TAG year_end_2005 NOKEEP;

--
--Monitor RMAN via v$ views
--
COLUMN CLIENT_INFO FORMAT a30
COLUMN SID FORMAT 999
COLUMN SPID FORMAT 9999
SELECT s.SID, p.SPID, s.CLIENT_INFO
FROM V$PROCESS p, V$SESSION s
WHERE p.ADDR = s.PADDR
AND CLIENT_INFO LIKE 'rman%'
/
--Monitor Job Progress
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK,
ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS
WHERE OPNAME LIKE 'RMAN%'
AND OPNAME NOT LIKE '%aggregate%'
AND TOTALWORK != 0
AND SOFAR <> TOTALWORK
/
--Monitor sbt event
SELECT p.SPID, EVENT, SECONDS_IN_WAIT AS SEC_WAIT,
sw.STATE, CLIENT_INFO
FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p
WHERE sw.EVENT LIKE '%sbt%'
AND s.SID=sw.SID
AND s.PADDR=p.ADDR
/

--Monitor backup and restore performance
V$BACKUP_SYNC_IO and V$BACKUP_ASYNC_IO

--to validate all datafiles
run {
allocate channel d1 type disk;
backup check logical validate database;
release channel d1;
}

--To test the most recent RMAN database backup:
RMAN> restore validate database;

--To test the most recent RMAN spfile backup:
RMAN> restore validate spfile to 'c:\temp\spfile.ora';

--To test the most recent RMAN controlfile backup:
RMAN> restore validate controlfile to 'c:\temp\control01.ctl';

--To test the most recent RMAN archivelog file(s) backup.
--First determine which archivelog sequences are in the last backup:
RMAN> list backup of archivelog all;
or
RMAN> list backup of archivelog all completed after 'sysdate -1';
--then:
--the beginning number is inclusive and the ending sequence number is not.
RMAN> restore validate archivelog from sequence ii until sequence XX?;

--To test for earlier backups via TAG
RMAN> restore validate database from tag=;

--RMAN configuration
select * from v$rman_configuration;

--
--Make Cold/Consistent Backup
--
run {
shutdown immedate;
startup mount pfile=/yourpath/initxxx.ora;
backup incremental level 0
database format '/yourpath/%d_closed_%U'
tag=PROD_COLD_L0;
shutdown;
startup pfile=/yourpath/initxxx.ora;
}


--RMAN related V$ views
select object_name from dba_objects
where object_name like 'V$BACKUP%'
and object_type = 'SYNONYM';

select object_name from dba_objects
where object_name like 'V$RMAN%'
and object_type = 'SYNONYM';

select TYPE, RECORDS_TOTAL, RECORDS_USED
from v$controlfile_record_section
where type like '%BACKUP%';

* V$CONTROLFILE_RECORD_SECTION
* V$COPY_CORRUPTION
* V$DATABASE
* V$DATABASE_BLOCK_CORRUPTION
* V$DATABASE_INCARNATION
* V$DATAFILE
* V$DATAFILE_COPY
* V$PROXY_ARCHIVEDLOG
* V$PROXY_ARCHIVELOG_DETAILS
* V$PROXY_ARCHIVELOG_SUMMARY
* V$PROXY_COPY_DETAILS
* V$PROXY_COPY_SUMMARY
* V$PROXY_DATAFILE
* V$OBSOLETE_BACKUP_FILES
* V$OFFLINE_RANGE
* V$SESSION_LONGOPS

* V$BACKUP
* V$BACKUP_ARCHIVELOG_DETAILS
* V$BACKUP_ARCHIVELOG_SUMMARY
* V$BACKUP_ASYNC_IO
* V$BACKUP_CONTROLFILE_DETAILS
* V$BACKUP_CONTROLFILE_SUMMARY
* V$BACKUP_COPY_DETAILS
* V$BACKUP_COPY_SUMMARY
* V$BACKUP_CORRUPTION
* V$BACKUP_DATAFILE
* V$BACKUP_DATAFILE_DETAILS
* V$BACKUP_DATAFILE_SUMMARY
* V$BACKUP_DEVICE
* V$BACKUP_FILES
* V$BACKUP_PIECE
* V$BACKUP_PIECE_DETAILS
* V$BACKUP_REDOLOG
* V$BACKUP_SET
* V$BACKUP_SET_DETAILS
* V$BACKUP_SET_SUMMARY
* V$BACKUP_SPFILE
* V$BACKUP_SPFILE_DETAILS
* V$BACKUP_SPFILE_SUMMARY
* V$BACKUP_SYNC_IO

* V$RMAN_BACKUP_JOB_DETAILS
* V$RMAN_BACKUP_SUBJOB_DETAILS
* V$RMAN_BACKUP_TYPE
* V$RMAN_CONFIGURATION
* V$RMAN_ENCRYPTION_ALGORITHMS
* V$RMAN_OUTPUT
* V$RMAN_STATUS