RMAN Status Query!!!
How to get RMAN information about the backup type, status, start time,end time, Size of the backup etc!!!
How to find the backup size?
How to check the RMAN backup status?
How to check the previous RMAN backup status?
#Calculate the RMAN backup size
SET PAGES 2000 LINES 200
SELECT TO_CHAR(COMPLETION_TIME, 'YYYY-MON-DD') COMPLETION_TIME, TYPE, ROUND(SUM(BYTES)/1048576) MB, ROUND(SUM(ELAPSED_SECONDS)/60) MIN
FROM
(SELECT
CASE
WHEN S.BACKUP_TYPE='L' THEN 'ARCHIVELOG'
WHEN S.CONTROLFILE_INCLUDED='YES' THEN 'CONTROLFILE'
WHEN S.BACKUP_TYPE='D' AND S.INCREMENTAL_LEVEL=0 THEN 'LEVEL0'
WHEN S.BACKUP_TYPE='I' AND S.INCREMENTAL_LEVEL=1 THEN 'LEVEL1'
END TYPE,
TRUNC(S.COMPLETION_TIME) COMPLETION_TIME, P.BYTES, S.ELAPSED_SECONDS
FROM V$BACKUP_PIECE P, V$BACKUP_SET S
WHERE P.STATUS='A' AND P.RECID=S.RECID
UNION ALL
SELECT 'DATAFILECOPY' type, TRUNC(COMPLETION_TIME), OUTPUT_BYTES, 0 ELAPSED_SECONDS FROM V$BACKUP_COPY_DETAILS)
GROUP BY TO_CHAR(COMPLETION_TIME, 'YYYY-MON-DD'), TYPE
ORDER BY 1 ASC,2,3;
RMAN> LIST BACKUP OF DATABASE SUMMARY;
RMAN> LIST BACKUP TAG LV0BKP;
#Calculate RMAN backup status, size, timing and session key
#RMAN information about the backup type, status, start time,end time, Size of the backup etc.
SET PAGES 2000 LINES 200
COL STATUS FORMAT a9
COL hrs FORMAT 999.99
select SESSION_KEY,
INPUT_TYPE,
STATUS,
TO_CHAR(START_TIME,'mm/dd/yy HH24:MI:SS') START_TIME,
TO_CHAR(END_TIME,'mm/dd/yy HH24:MI:SS') END_TIME,
ELAPSED_SECONDS/3600 HRS,
INPUT_BYTES/1024/1024/1024 SUM_BYTES_BACKED_IN_GB,
OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,
OUTPUT_DEVICE_TYPE
FROM V$RMAN_BACKUP_JOB_DETAILS
order by SESSION_KEY;
#The following query shows the backup job speed ordered by session key, which the primary key for the RMAN session. The columns in_sec and out_sec columns display the data input and output per second.
SET PAGES 2000 LINES 200
COL OPTIMIZED for a10
COL INPUT_PER_SEC FORMAT a20
COL OUTPUT_PER_SEC FORMAT a20
COL TIME_TAKEN_DISPLAY FORMAT a10
SELECT SESSION_KEY,
INPUT_TYPE,
OPTIMIZED,
COMPRESSION_RATIO,
INPUT_BYTES_PER_SEC_DISPLAY INPUT_PER_SEC,
OUTPUT_BYTES_PER_SEC_DISPLAY OUTPUT_PER_SEC,
TIME_TAKEN_DISPLAY
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;
#RMAN information about the backup type, status, start time,end time, Size of the backup etc from Recovery Catalog
SET PAGES 2000 LINES 200
COL STATUS FORMAT a9
COL HRS FORMAT 999.99
SELECT DB_NAME,
INPUT_TYPE,
STATUS,
TO_CHAR(START_TIME,'MM/DD/YY HH24:MI:SS') START_TIME,
TO_CHAR(END_TIME,'MM/DD/YY HH24:MI:SS') END_TIME,
ELAPSED_SECONDS/3600 HRS,
INPUT_BYTES/1024/1024/1024 SUM_BYTES_BACKED_IN_GB,
OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,
OUTPUT_DEVICE_TYPE
FROM RC_RMAN_BACKUP_JOB_DETAILS
–WHERE DB_NAME='SBLTPS'
ORDER BY DB_NAME,SESSION_KEY;
Reference
Log:
====
[oracle@node1 ~]$ . oraenv
ORACLE_SID = [oracle] ? ORA19C
The Oracle base has been set to /u01/app/oracle
[oracle@node1 ~]$
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 15 14:54:33 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> SET PAGES 2000 LINES 200
SELECT TO_CHAR(COMPLETION_TIME, 'YYYY-MON-DD') COMPLETION_TIME, TYPE, ROUND(SUM(BYTES)/1048576) MB, ROUND(SUM(ELAPSED_SECONDS)/60) MIN
FROM
(SELECT
CASE
WHEN S.BACKUP_TYPE='L' THEN 'ARCHIVELOG'
WHEN S.CONTROLFILE_INCLUDED='YES' THEN 'CONTROLFILE'
WHEN S.BACKUP_TYPE='D' AND S.INCREMENTAL_LEVEL=0 THEN 'LEVEL0'
WHEN S.BACKUP_TYPE='I' AND S.INCREMENTAL_LEVEL=1 THEN 'LEVEL1'
SQL> 2 3 4 5 6 7 8 9 END TYPE,
TRUNC(S.COMPLETION_TIME) COMPLETION_TIME, P.BYTES, S.ELAPSED_SECONDS
FROM V$BACKUP_PIECE P, V$BACKUP_SET S
10 11 12 WHERE P.STATUS='A' AND P.RECID=S.RECID
UNION ALL
13 14 SELECT 'DATAFILECOPY' type, TRUNC(COMPLETION_TIME), OUTPUT_BYTES, 0 ELAPSED_SECONDS FROM V$BACKUP_COPY_DETAILS)
15 GROUP BY TO_CHAR(COMPLETION_TIME, 'YYYY-MON-DD'), TYPE
16 ORDER BY 1 ASC,2,3;
COMPLETION_TIME TYPE MB MIN
-------------------- ------------ ---------- ----------
2023-AUG-01 DATAFILECOPY 776 0
2023-SEP-12 ARCHIVELOG 33 0
2023-SEP-13 ARCHIVELOG 250 2
2023-SEP-14 ARCHIVELOG 225 2
2023-SEP-14 CONTROLFILE 565 1
2023-SEP-14 DATAFILECOPY 3795 0
2023-SEP-15 ARCHIVELOG 183 1
2023-SEP-15 CONTROLFILE 657 1
8 rows selected.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@node1 ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Fri Sep 15 14:55:15 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORA19C (DBID=1121712241)
RMAN> LIST BACKUP OF DATABASE SUMMARY;
using target database control file instead of recovery catalog
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
42981 B 1 A DISK 14-SEP-23 1 1 NO MALLIK_BACKUP_TAG
RMAN> LIST BACKUP TAG MALLIK_BACKUP_TAG;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
42981 Incr 1 253.69M DISK 00:00:08 14-SEP-23
BP Key: 42991 Status: AVAILABLE Compressed: NO Tag: MALLIK_BACKUP_TAG
Piece Name: /var/rubrik/oracle/f9707ae9-ff5d-4333-8291-2b234a6a4865_backup/c0/fh26bpf3_1_1
List of Datafiles in backup set 42981
File LV Type Ckp SCN Ckp Time Abs Fuz SCN Sparse Name
---- -- ---- ---------- --------- ----------- ------ ----
1 1 Incr 74040947 14-SEP-23 NO /u01/app/oracle/oradata/ORA19C/datafile/o1_mf_system_j64p3dcg_.dbf
3 1 Incr 74040947 14-SEP-23 NO /u01/app/oracle/oradata/ORA19C/datafile/o1_mf_sysaux_j64p4hpv_.dbf
4 1 Incr 74040947 14-SEP-23 NO /u01/app/oracle/oradata/ORA19C/datafile/o1_mf_undotbs1_j64p58v3_.dbf
7 1 Incr 74040947 14-SEP-23 NO /u01/app/oracle/oradata/ORA19C/datafile/o1_mf_users_j64p59w2_.dbf
14 1 Incr 74040947 14-SEP-23 NO /u01/app/oracle/oradata/ORA19C/datafile/o1_mf_user1_j8r9y84m_.dbf
16 1 Incr 74040947 14-SEP-23 NO /u01/app/oracle/oradata/ORA19C/datafile/o1_mf_mallik2_j909fm6p_.dbf
20 1 Incr 74040947 14-SEP-23 NO /u01/app/oracle/oradata/ORA19C/datafile/o1_mf_test1_ktlw1tvs_.dbf
RMAN> exit
Recovery Manager complete.
[oracle@node1 ~]$
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Sep 15 14:55:41 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> SET PAGES 2000 LINES 200
SQL> COL STATUS FORMAT a9
COL hrs FORMAT 999.99
select SESSION_KEY,
INPUT_TYPE,
STATUS,
SQL> SQL> 2 3 4 TO_CHAR(START_TIME,'mm/dd/yy HH24:MI:SS') START_TIME,
5 TO_CHAR(END_TIME,'mm/dd/yy HH24:MI:SS') END_TIME,
ELAPSED_SECONDS/3600 HRS,
INPUT_BYTES/1024/1024/1024 SUM_BYTES_BACKED_IN_GB,
OUTPUT_BYTES/1024/1024/1024 SUM_BACKUP_PIECES_IN_GB,
6 OUTPUT_DEVICE_TYPE
FROM V$RMAN_BACKUP_JOB_DETAILS
order by SESSION_KEY; 7 8 9 10 11
SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME HRS SUM_BYTES_BACKED_IN_GB SUM_BACKUP_PIECES_IN_GB OUTPUT_DEVICE_TYP
----------- ------------- --------- ----------------- ----------------- ------- ---------------------- ----------------------- -----------------
<Truncated>
215289 DB INCR COMPLETED 09/14/23 13:43:46 09/14/23 13:45:05 .02 2.54919434 .333282471 DISK
215305 DB INCR COMPLETED 09/14/23 13:57:55 09/14/23 13:59:45 .03 2.74450684 .528594971 DISK
215326 ARCHIVELOG COMPLETED 09/14/23 14:08:08 09/14/23 14:08:38 .01 .046971798 .04708147 DISK
215336 ARCHIVELOG COMPLETED 09/14/23 14:11:57 09/14/23 14:12:11 .00 .042680264 .042789936 DISK
215346 ARCHIVELOG COMPLETED 09/14/23 15:08:08 09/14/23 15:08:31 .01 .047270775 .047380447 DISK
215356 ARCHIVELOG COMPLETED 09/14/23 16:08:28 09/14/23 16:08:42 .00 .047048092 .047157764 DISK
215366 ARCHIVELOG COMPLETED 09/14/23 17:08:18 09/14/23 17:08:32 .00 .04712677 .047236443 DISK
215376 ARCHIVELOG COMPLETED 09/14/23 18:08:44 09/14/23 18:08:59 .00 .047767639 .047877312 DISK
215386 ARCHIVELOG COMPLETED 09/14/23 19:08:32 09/14/23 19:08:46 .00 .04718399 .047293663 DISK
215396 ARCHIVELOG COMPLETED 09/14/23 20:08:36 09/14/23 20:08:58 .01 .046964645 .047074318 DISK
215406 ARCHIVELOG COMPLETED 09/14/23 21:08:48 09/14/23 21:09:03 .00 .047271729 .047381401 DISK
215416 ARCHIVELOG COMPLETED 09/14/23 22:09:00 09/14/23 22:09:14 .00 .12175703 .121866703 DISK
215426 ARCHIVELOG COMPLETED 09/14/23 23:09:00 09/14/23 23:09:15 .00 .084067345 .084177017 DISK
215436 ARCHIVELOG COMPLETED 09/15/23 00:09:46 09/15/23 00:10:17 .01 .047124386 .047234058 DISK
215446 ARCHIVELOG COMPLETED 09/15/23 01:09:47 09/15/23 01:10:18 .01 .047052383 .047162056 DISK
215456 ARCHIVELOG COMPLETED 09/15/23 02:09:43 09/15/23 02:10:06 .01 .046983242 .047092915 DISK
215466 ARCHIVELOG COMPLETED 09/15/23 03:09:44 09/15/23 03:09:59 .00 .047057629 .047167301 DISK
215476 ARCHIVELOG COMPLETED 09/15/23 04:09:39 09/15/23 04:09:54 .00 .047075272 .047184944 DISK
215486 ARCHIVELOG COMPLETED 09/15/23 05:09:31 09/15/23 05:09:42 .00 .046983242 .047092915 DISK
215496 ARCHIVELOG COMPLETED 09/15/23 06:09:47 09/15/23 06:10:02 .00 .048101902 .048211575 DISK
215506 ARCHIVELOG COMPLETED 09/15/23 07:09:49 09/15/23 07:10:00 .00 .046898365 .047008038 DISK
215516 ARCHIVELOG COMPLETED 09/15/23 08:09:57 09/15/23 08:10:11 .00 .046968937 .047078609 DISK
215526 ARCHIVELOG COMPLETED 09/15/23 09:09:39 09/15/23 09:09:45 .00 .047005177 .047114849 DISK
215536 ARCHIVELOG COMPLETED 09/15/23 10:09:45 09/15/23 10:09:51 .00 .046883583 .046993256 DISK
215546 ARCHIVELOG COMPLETED 09/15/23 11:09:45 09/15/23 11:09:52 .00 .046927452 .047037125 DISK
215556 ARCHIVELOG COMPLETED 09/15/23 12:10:14 09/15/23 12:10:37 .01 .047740459 .047850132 DISK
215566 ARCHIVELOG COMPLETED 09/15/23 13:10:16 09/15/23 13:10:31 .00 .046950817 .04706049 DISK
215576 ARCHIVELOG COMPLETED 09/15/23 14:10:21 09/15/23 14:10:32 .00 .04672718 .046836853 DISK
1209 rows selected.
SQL>
SQL> SET PAGES 2000 LINES 200
COL OPTIMIZED for a10
COL INPUT_PER_SEC FORMAT a20
COL OUTPUT_PER_SEC FORMAT a20
COL TIME_TAKEN_DISPLAY FORMAT a10
SELECT SESSION_KEY,
INPUT_TYPE,
OPTIMIZED,
COMPRESSION_RATIO,
INPUT_BYTES_PER_SEC_DISPLAY INPUT_PER_SEC,
OUTPUT_BYTES_PER_SEC_DISPLAY OUTPUT_PER_SEC,
TIME_TAKEN_DISPLAY
FROM V$RMAN_BACKUP_JOB_DETAILS
ORDER BY SESSION_KEY;SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 9
SESSION_KEY INPUT_TYPE OPTIMIZED COMPRESSION_RATIO INPUT_PER_SEC OUTPUT_PER_SEC TIME_TAKEN
----------- ------------- ---------- ----------------- -------------------- -------------------- ----------
<Truncated>
215289 DB INCR NO 7.64875011 33.04M 4.32M 00:01:19
215305 DB INCR NO 5.19207898 25.55M 4.92M 00:01:50
215326 ARCHIVELOG NO 1 1.60M 1.61M 00:00:30
215336 ARCHIVELOG NO 1 3.12M 3.13M 00:00:14
215346 ARCHIVELOG NO 1 2.10M 2.11M 00:00:23
215356 ARCHIVELOG NO 1 3.44M 3.45M 00:00:14
215366 ARCHIVELOG NO 1 3.45M 3.46M 00:00:14
215376 ARCHIVELOG NO 1 3.26M 3.27M 00:00:15
215386 ARCHIVELOG NO 1 3.45M 3.46M 00:00:14
215396 ARCHIVELOG NO 1 2.19M 2.19M 00:00:22
215406 ARCHIVELOG NO 1 3.23M 3.23M 00:00:15
215416 ARCHIVELOG NO 1 8.91M 8.91M 00:00:14
215426 ARCHIVELOG NO 1 5.74M 5.75M 00:00:15
215436 ARCHIVELOG NO 1 1.56M 1.56M 00:00:31
215446 ARCHIVELOG NO 1 1.55M 1.56M 00:00:31
215456 ARCHIVELOG NO 1 2.09M 2.10M 00:00:23
215466 ARCHIVELOG NO 1 3.21M 3.22M 00:00:15
215476 ARCHIVELOG NO 1 3.21M 3.22M 00:00:15
215486 ARCHIVELOG NO 1 4.37M 4.38M 00:00:11
215496 ARCHIVELOG NO 1 3.28M 3.29M 00:00:15
215506 ARCHIVELOG NO 1 4.37M 4.38M 00:00:11
215516 ARCHIVELOG NO 1 3.44M 3.44M 00:00:14
215526 ARCHIVELOG NO 1 8.02M 8.04M 00:00:06
215536 ARCHIVELOG NO 1 8.00M 8.02M 00:00:06
215546 ARCHIVELOG NO 1 6.86M 6.88M 00:00:07
215556 ARCHIVELOG NO 1 2.13M 2.13M 00:00:23
215566 ARCHIVELOG NO 1 3.21M 3.21M 00:00:15
215576 ARCHIVELOG NO 1 4.35M 4.36M 00:00:11
1209 rows selected.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@node1 ~]$
Regards,
Mallik
