RMAN Important Queries And Views

RMAN Important Queries And Views



To Display the backup details of the database (Full, Incremental & Archive log backup), when the DB is not connect with the

recover catalog.  (Work in Mount stage)


select session_key,input_type,status,to_char(start_time,'yyyy-mm-dd hh24:mi') start_time,to_char(end_time,'yyyy-mm-dd hh24:mi')  

end_time,output_bytes_display,time_taken_display from v$rman_backup_job_details order by session_key asc;



Query to see Backed Up Archive Logs in the database (Work in Mount stage):

select recid,set_stamp,sequence#,first_change#,next_change# from  v$backup_redolog;



This script will report on all backups – full, incremental and archivelog backups:


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;


This script will give you RMAN backup status along with start and stop timing.

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




RMAN related views:

select table_name from dict where table_name like 'V$%RMAN%';





V$RMAN_BACKUP_JOB_DETAILS è displays details about backup jobs.

V$RMAN_BACKUP_SUBJOB_DETAILS è  merges similar operations within an RMAN session into a single row. For example, if there are four

BACKUP DATAFILE <n> commands, three RECOVERY COPY OF DATAFILE commands, and one BACKUP RECOVERY AREA command, this view will

contain three rows - one each for BACKUP, ROLLFORWARD, and COPY_DISK_TO_TAPE operation.

V$RMAN_BACKUP_TYPEè displays information about RMAN backup types.
SQL> select * from V$RMAN_BACKUP_TYPE;

    WEIGHT INPUT_TYPE
---------- -------------
         1 BACKUPSET
         2 SPFILE
         3 CONTROLFILE
         4 ARCHIVELOG
         5 DATAFILE INCR
         6 DATAFILE FULL
         7 DB INCR
         8 RECVR AREA
         9 DB FULL


V$RMAN_COMPRESSION_ALGORITHM : provides descriptions of supported compression algorithms. It is used by the RMAN client.


V$RMAN_CONFIGURATION : Information about RMAN persistent configuration settings.


V$RMAN_ENCRYPTION_ALGORITHMS : displays supported encryption algorithms. It is used by the RMAN client to validate user-requested

algorithms. This view will list AES128, AES192, and AES256 encryption algorithms for the current release. The default algorithm is

AES128.

V$RMAN_OUTPUT : displays messages reported by RMAN. This is an in-memory view and is not recorded in the controlfile. The view can

hold 32768 rows.

V$RMAN_STATUS : displays the finished and on-going RMAN jobs. For on-going jobs, this view displays progress and status. The jobs

which are in progress are stored only in memory while the finished jobs are stored in the controlfile


V$ARCHIVED_LOG : show which archives have been created, backed up, and cleared in the database.
 

V$BACKUP_CORRUPTION : shows which blocks have been found to be corrupt during a backup of a backup set            


V$BACKUP_DATAFILE : useful for creating equal-sized backup sets by determining the number of blocks in each datafile. It can also

help you find the number of corrupt blocks in the datafile


V$BACKUP_DEVICE : display information about supported backup devices. DISK is  not returned because it is always available

V$BACKUP_FILES : display information about all RMAN backups (image copies and backup sets) and archived logs. The view simulates the LIST BACKUP and LIST COPY commands

V$BACKUP_PIECE : show backup pieces created for backup sets

V$BACKUP_REDOLOG : show archived logs stored in backup sets

V$BACKUP_SET : show backup sets that have been created

V$BACKUP_SPFILE : display information about server parameter files in backup sets

V$COPY_CORRUPTION : show which blocks have been found to be corrupt during an image copy

V$DATABASE_BLOCK_CORRUPTION : list database blocks marked as corrupt during the most recent RMAN backup

V$RMAN_CONFIGURATION : list information about RMAN persistent configuration settings

V$PROXY_ARCHIVEDLOG, V$PROXY_DATAFILE : proxy settings for RMAN

V$BACKUP_ASYNC_IO, V$BACKUP_SYNC_IO :  backup performance statistics