This script will be run in the target( registered ) database, not in the catalog ( Repository ) database.
Login as sysdba and issue the following script:
This script will report on all currently running RMAN backups like full, incremental & archivelog backups:
SQL> col STATUS format a9 SQL> col hrs format 999.99 SQL> 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; SQL> / SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS ----------- ------------- --------- -------------- -------------- ------- 29 DB FULL RUNNING 01/07/14 10:28 01/07/14 10:28 .00 SQL> / SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS ----------- ------------- --------- -------------- -------------- ------- 29 DB FULL RUNNING 01/07/14 10:28 01/07/14 10:28 .01 SQL> / SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS ----------- ------------- --------- -------------- -------------- ------- 29 DB FULL COMPLETED 01/07/14 10:28 01/07/14 10:29 .03
Above script will give you RMAN backup status along with start and stop timing.
OR
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; SID SERIAL# CONTEXT SOFAR TOTALWORK %COMPLETE ---------- ---------- ---------- ---------- ---------- ---------- 18 29 1 9115569 19258880 47.33
Above script will give you SID, Total Work, Sofar & % of completion.
You can also check historical backup status with the help of following script:
set linesize 500 pagesize 2000 col Hours format 9999.99 col STATUS format a10 select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_start_time, to_char(END_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_end_time, elapsed_seconds/3600 Hours from V$RMAN_BACKUP_JOB_DETAILS order by session_key; SESSION_KEY INPUT_TYPE STATUS RMAN_BKUP_START_TIM RMAN_BKUP_END_TIME HOURS ----------- ------------- ---------- ------------------- ------------------- -------- 137764 DB FULL COMPLETED 06-06-2017 02:00:32 06-06-2017 04:12:13 2.19 137770 ARCHIVELOG COMPLETED 06-06-2017 04:00:29 06-06-2017 04:01:05 .01 137778 ARCHIVELOG COMPLETED 06-06-2017 06:00:27 06-06-2017 06:00:35 .00 137782 ARCHIVELOG COMPLETED 06-06-2017 08:00:32 06-06-2017 08:03:36 .05 137786 ARCHIVELOG COMPLETED 06-06-2017 10:00:30 06-06-2017 10:02:03 .03 137790 ARCHIVELOG COMPLETED 06-06-2017 12:00:30 06-06-2017 12:02:34 .03 137794 ARCHIVELOG COMPLETED 06-06-2017 14:00:30 06-06-2017 14:02:58 .04 . .. ...
*****
Note: Please don’t hesitate to revert in case of any query OR feedback.
Thanking you.
Have an easy life ahead.
Hi Jethwa,
How to find the reason for Rman database backup failure in oracle 11g
Hello Balaji,
Thank you for writing…
While executing RMAN backup ( whether manually or by scripting with crontab scheduler ) RMAN will generate backup logs, you can verify its backup logs to verify status of RMAN backups.
Additionally,
You can query to V$RMAN_STATUS dictionary view for completed job information.
Or
For current job information, kindly query to V$RMAN_STATUS dictionary view.
Ex:
I have simulated the same scenario by instantiating RMAN backup and interrupt it by Ctrl + C at the same time, Error log will be written on V$RMAN_OUTPUT dictionary view is as follows:
SQL> select OUTPUT from V$RMAN_OUTPUT;
OUTPUT
————————————————————————————————————————
connected to target database: ORCL (DBID=1138782461)
Starting backup at 03-NOV-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u02/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/u02/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00003 name=/u02/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00005 name=/u02/app/oracle/oradata/orcl/example01.dbf
input datafile file number=00004 name=/u02/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 03-NOV-14
Finished backup at 03-NOV-14
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03099: job cancelled at user request
–x–
Hope this would be clear to you.
In case any query, feel free to ask, stay tune. 🙂
how to rectify rman back up space errors..
Thank you sam for writing!
Please let me know the error.
How to know if RMAN is running a full backup or incremental backup ?
Possible with INPUT_TYPE column from dictionary view V$RMAN_BACKUP_JOB_DETAILS
FYI
https://docs.oracle.com/cd/B28359_01/server.111/b28320/dynviews_2134.htm
Using the first query I got the backup details for only last 38 days or so but my requirement is for last 6 months(180 days). Can somebody guide me how to get it.
Thanks Sanjay fro writing!
Query will show you all details available with respected data dictionary views.
Try last updated query
How do we get the Level 0 backup information from the above query?
How to identify failed rman backup job pieces? Is there any command or script to identify failed backup pieces from backup pieces of successfully completed job.
We have an environment where we are taking rman full,incremental and archivelog backups at regular interval. But some backup jobs get failed after some time and their backup pieces remains there only. Here I want to identify which backup piece belongs to failed backup jobs so that I can clear them, to make room for future backups.
Any idea on how we can check for the backup throughput?
These scripts are very helpfull to check the RMAN Backup details. I have a scenario where i need to check the whether scheduled backup job is running or not. Sometime we will keep backup job disabled and later will forget to enable it back, in that case how we can identify that.
As per my understanding untill RMAN job get started we dont have any details in the V$rman_backup_job_details
Thank you for sharing this. Much appreciated