How to check RMAN Backup jobs

normally  we check the rman backup jobs throw the rman log but we also check the rman jobs  from v$rman_backup_job_details and GV$RMAN_OUTPUT 

TO list all RMAN backup jobs

• use Toad program and login to the database using SYS user or system
• Execute the below  SQL

SELECT

session_recid,

session_stamp,

TO_CHAR(START_time,’dd-mon-rrrr hh24:mi:ss’) AS rman_START_time,

TO_CHAR(end_time,’dd-mon-rrrr hh24:mi:ss’) AS rman_end_time,

time_taken_display Time_taken,

input_bytes/1024/1024/1024 INPUT_size_gig,

output_bytes/1024/1024/1024 OUTPUT_size_gig,

compression_ratio,

INPUT_BYTES_PER_SEC_DISPLAY read_rate_per_second,

OUTPUT_BYTES_PER_SEC_DISPLAY write_rate_per_second,

status,

input_type

FROM v$rman_backup_job_details

ORDER BY start_time desc;

V$RMAN_BACKUP_JOB_DETAILS displays details about backup jobs

Column	Datatype	Description
SESSION_KEY	NUMBER	Session identifier
SESSION_RECID	NUMBER	Together, with SESSION_STAMP, used to uniquely identify job output from V$RMAN_OUTPUT
SESSION_STAMP	NUMBER	Together, with SESSION_RECID, used to uniquely identify job output from V$RMAN_OUTPUT
COMMAND_ID	VARCHAR2(33)	Either a user-specified SET COMMAND ID or a unique command ID generated by RMAN
START_TIME	DATE	Start time of the first BACKUP command in the job
END_TIME	DATE	End time of the last BACKUP command in the job
INPUT_BYTES	NUMBER	Sum of all input file sizes backed up by this job
OUTPUT_BYTES	NUMBER	Output size of all pieces generated by this job
STATUS_WEIGHT	NUMBER	User internally by Enterprise Manager
OPTIMIZED_WEIGHT	NUMBER	Used internally by Enterprise Manager
OBJECT_TYPE_WEIGHT	NUMBER	Used internally by Enterprise Manager
OUTPUT_DEVICE_TYPE	VARCHAR2(17)	Can be DISK, SBT, or *. An * indicates more than one device (in most cases, it will be DISK or SBT).
AUTOBACKUP_COUNT	NUMBER	Number of autobackups performed by this job
AUTOBACKUP_DONE	VARCHAR2(3)	YES or NO, depending upon whether or not a control file autobackup was done as part of this backup job
STATUS	VARCHAR2(23)	One of the following values: RUNNING WITH WARNINGS RUNNING WITH ERRORS COMPLETED COMPLETED WITH WARNINGS COMPLETED WITH ERRORS FAILED
INPUT_TYPE	VARCHAR2(13)	Contains one of the following values. If the user command does not satisfy one of them, then preference is given in order, from top to bottom of the list. DB FULL RECVR AREA DB INCR DATAFILE FULL DATAFILE INCR ARCHIVELOG CONTROLFILE SPFILE
OPTIMIZED	VARCHAR2(3)	YES or NO, depending on whether optimization was applied. Applicable to backup jobs only.
ELAPSED_SECONDS	NUMBER	Number of elapsed seconds
COMPRESSION_RATIO	NUMBER	Compression ratio
INPUT_BYTES_PER_SEC	NUMBER	Input read-rate-per-second
OUTPUT_BYTES_PER_SEC	NUMBER	Output write-rate-per-second
INPUT_BYTES_DISPLAY	VARCHAR2(4000)	Values in user-displayable form. They will be converted to a format of nM, nG, nT, nP, and so on.
OUTPUT_BYTES_DISPLAY	VARCHAR2(4000)	Values in user-displayable form. They will be converted to a format of nM, nG, nT, nP, and so on
INPUT_BYTES_PER_SEC_DISPLAY	VARCHAR2(4000)	Input read-rate-per-second. These values are in user-displayable form. They will be converted to a format of nM, nG, nT, nP, and so on.
OUTPUT_BYTES_PER_SEC_DISPLAY	VARCHAR2(4000)	Output write-rate-per-second. These values are in user-displayable form. They will be converted to a format of nM, nG, nT, nP, and so on.
TIME_TAKEN_DISPLAY	VARCHAR2(4000)	Time taken, shown in user-displayable format <nn>h:<nn>m:<nn>s

TO list all RMAN backup running :

use Toad program and login to the database using SYS user or system
Execute the bellow SQL

SELECT session_recid, session_stamp, TO_CHAR(START_time,’dd-mon-rrrr hh24:mi:ss’) AS rman_START_time, TO_CHAR(end_time,’dd-mon-rrrr hh24:mi:ss’) AS rman_end_time, time_taken_display Time_taken, round(input_bytes/1024/1024/1024,2) INPUT_size_gig, round(output_bytes/1024/1024/1024,2) OUTPUT_size_gig, compression_ratio, INPUT_BYTES_PER_SEC_DISPLAY read_rate_per_second, OUTPUT_BYTES_PER_SEC_DISPLAY write_rate_per_second, status, input_type FROM v$rman_backup_job_details where status =‘RUNNING’ ORDER BY end_time;

 

TO list all RMAN backup failed jobs :

use Toad program and login to the database using SYS user or system user Execute the bellow SQL
SELECT
session_recid,
session_stamp,
TO_CHAR(START_time,’dd-mon-rrrr hh24:mi:ss’) AS rman_START_time,
TO_CHAR(end_time,’dd-mon-rrrr hh24:mi:ss’) AS rman_end_time,
time_taken_display Time_taken,
round(input_bytes/1024/1024/1024,2) INPUT_size_gig,
round(output_bytes/1024/1024/1024,2) OUTPUT_size_gig,
compression_ratio,
INPUT_BYTES_PER_SEC_DISPLAY read_rate_per_second,
OUTPUT_BYTES_PER_SEC_DISPLAY write_rate_per_second,
status,
input_type
FROM v$rman_backup_job_details
where status =‘FAILED’
ORDER BY end_time;

 

 

TO list all RMAN backup Completed jobs :

SELECT
session_recid,
session_stamp,
TO_CHAR(START_time,’dd-mon-rrrr hh24:mi:ss’) AS rman_START_time,
TO_CHAR(end_time,’dd-mon-rrrr hh24:mi:ss’) AS rman_end_time,
time_taken_display Time_taken,
round(input_bytes/1024/1024/1024,2) INPUT_size_gig,
round(output_bytes/1024/1024/1024,2) OUTPUT_size_gig,
compression_ratio,
INPUT_BYTES_PER_SEC_DISPLAY read_rate_per_second,
OUTPUT_BYTES_PER_SEC_DISPLAY write_rate_per_second,
status,
input_type
FROM v$rman_backup_job_details
where status =’COMPLETED’
ORDER BY end_time;

 

Backup job output:

And finally, sometimes it may be helpful to retrieve the job’s output from the metadata kept by the instance.
It might be that the original log on disk, if any, may have been overwritten by a more recent backup, or just that selecting it from a V$ view may be easier than connecting to a server to find out where the log file is.

The tricky thing here, though, is that the view that contains the output, V$RMAN_OUTPUT, exists in memory only; the job’s output is not stored in the controlfile or anywhere else in the database. Thus, if the instance gets 

restarted, the contents of that view are reset.

To retrieve the job output for a specific backup job, identified by the (SESSION_RECID, SESSION_STAMP) pair, you can use the following query:

select output

from GV$RMAN_OUTPUT

where session_recid = &SESSION_RECID

and session_stamp = &SESSION_STAMP

order by recid;

OUTPUT
———————————————————————————————————————————-
connected to target database: ORCL (DBID=4132479105)
using target database control file instead of recovery catalog

echo set on

backup archivelog all not backed up delete all input;

Starting backup at 26-AUG-11
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=233 instance=ORCL devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=261 instance=ORCL devtype=DISK
channel ORA_DISK_1: starting compressed archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
…
…
archive log filename=+ALOGS/ORCL/archivelog/2011_08_26/thread_2_seq_23561.462.933333703 recid=213400 stamp=933333707
archive log filename=+ALOGS/ORCL/archivelog/2011_08_26/thread_2_seq_23562.412.933335505 recid=213402 stamp=933335506
archive log filename=+ALOGS/ORCL/archivelog/2011_08_26/thread_2_seq_23563.632.933337017 recid=213404 stamp=933337020
Finished backup at 26-AUG-11

Starting Control File and SPFILE Autobackup at 26-AUG-11
piece handle=/fra/ORCL/autobackup/2011_08_26/o1_mf_s_933337284_34h856x2_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 26-AUG-11