Tweet
Finding Backup details from 11g RMAN respository tables
- List databases registered with rman repository
select NAME
, DBID
from RC_DATABASE;
- List rman backup STATUS details with database name, Start time, End time , Processed MB/GB
SELECT db_name, row_type, start_time, end_time, operation, status, mbytes_processed
FROM rc_rman_status
WHERE db_name like ‘PROD%’
AND start_time > SYSDATE – 1
ORDER BY END_TIME desc;
- List backup details of all registered RMAN databases
SELECT db_name
, start_time
, row_type
, end_time
, operation
, status
, mbytes_processed
FROM rc_rman_status
WHERE start_time > SYSDATE – 1
ORDER BY DB_NAME
, start_time;
- Show backups which are still RUNNING
SELECT db_name
, start_time
, row_type
, operation
, status
FROM rc_rman_status
WHERE start_time > SYSDATE – 1
AND STATUS = ‘RUNNING’
ORDER BY DB_NAME
, start_time;
-
List db name, tablespace & datafiles backed up with size
select DB_NAME
, TABLESPACE_NAME
, FILE#
, BYTES/1000000000 SIZE_GB
, NAME
from RC_DATAFILE
where DB_NAME = ‘PROD’;
-
List RMAN backup configuration Parameters
select
DB_UNIQUE_NAME
,NAME
,VALUE
from RC_RMAN_CONFIGURATION
where DB_UNIQUE_NAME =’PROD’
order by 1,2,3;ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1 ARCHIVELOG DELETION POLICY TO NONE BACKUP OPTIMIZATION OFF CHANNEL DEVICE TYPE DISK FORMAT '/mnt/backup/Rman/PROD1/%d_%s_%p_%U' CONTROLFILE AUTOBACKUP ON CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/mnt/backup/Rman/PROD1/%F' DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1 DEVICE TYPE DISK PARALLELISM 3 BACKUP TYPE TO COMPRESSED BACKUPSET MAXSETSIZE TO UNLIMITED SNAPSHOT CONTROLFILE NAME TO '/opt/app/oracle/product/11.2/db_1/dbs/snapcf_RMANTST.f'
-
List the most recent FULL Database backup with STATUS=’COMPLETED’
select a.db_name
Database
, db.dbid DBID
, a.end_time Latest_Backup
, round ( a.output_bytes / 1000000000 ) GBytes_Processed
, round ( ( end_time – start_time ) * 60 * 24 ) Minutes
from rc_rman_status a
, rc_database db
where object_type in ( ‘DB FULL’
, ‘DB INCR’ )
and status = ‘COMPLETED’
and operation = ‘BACKUP’
and a.db_name = ‘&&DB_NAME’
and end_time in ( select end_time
from rc_rman_status b
where b.db_name = a.db_name
and b.db_key = a.db_key
and object_type in ( ‘DB FULL’
, ‘DB INCR’ )
and status = ‘COMPLETED’
and operation = ‘BACKUP’ )
and db.db_key = a.db_key
order by a.db_name
, end_time desc;
-
List Backups for a Specific Database Instance ‘&&DB_NAME’
select a.db_name
Database
, db.dbid DBID
, a.end_time Latest_Backup
, round ( a.output_bytes / 1000000000 ) GBytes_Processed
, round ( ( end_time – start_time ) * 60 * 24 ) Minutes_Taken
from rman.rc_rman_status a
, rman.rc_database db
where object_type in ( ‘DB FULL’
, ‘DB INCR’ )
and status = ‘COMPLETED’
and operation = ‘BACKUP’
and a.db_name = ‘&&DB_NAME’
and end_time in ( select end_time
from rman.rc_rman_status b
where b.db_name = a.db_name
and b.db_key = a.db_key
and object_type in ( ‘DB FULL’
, ‘DB INCR’ )
and status = ‘COMPLETED’
and operation = ‘BACKUP’ )
and db.db_key = a.db_key
order by a.db_name
, end_time desc;DATABASE DBID LATEST_BA GBYTES_PROCESSED MINUTES_TAKEN -------- ---------- --------- ---------------- ------------- PROD 4020163152 22-SEP-11 0 20 PROD 4020163152 21-SEP-11 0 19 PROD 4020163152 20-SEP-11 0 17 PROD 4020163152 19-SEP-11 0 16 PROD 4020163152 18-SEP-11 4 13 PROD 4020163152 17-SEP-11 0 15
- List Archivelog backups
SELECT a.db_name “Database”
, db.dbid “DBID”
, a.end_time “Latest Backup”
, ROUND ( a.output_bytes / 1000000 ) “MBytes Processed”
, ROUND ( ( end_time – start_time ) * 60 * 24 ) “Minutes Taken”
FROM rman.rc_rman_status a
, rman.rc_database db
WHERE object_type = ‘ARCHIVELOG’
AND status = ‘COMPLETED’
AND operation = ‘BACKUP’
AND a.db_name = ‘&&DB_NAME’
AND end_time IN ( SELECT end_time
FROM rman.rc_rman_status b
WHERE b.db_name = a.db_name
AND b.db_key = a.db_key
AND object_type = ‘ARCHIVELOG’
AND status = ‘COMPLETED’
AND operation = ‘BACKUP’ )
AND db.db_key = a.db_key
ORDER BY a.db_name
, end_time desc;
- List all Database FULL backups done in last 30 days
SELECT SUBSTR ( TO_CHAR ( START_TIME
, ‘DD-MON-YYYY HH24:MI’ )
, 1
, 20 )
, SUBSTR ( OBJECT_TYPE
, 1
, 20 )
FROM rman.rc_rman_status
WHERE start_time > SYSDATE – 30
AND object_type = ‘DB FULL’
ORDER BY 1 DESC;
-
Is RMAN Backup Still Running?
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 opname = ‘dbms_backup_restore’;
