{"id":3630,"date":"2021-04-09T08:42:35","date_gmt":"2021-04-09T08:42:35","guid":{"rendered":"https:\/\/swiv.com.br\/obtaining-information-about-rman-backups-using-vbackup_set_details-and-vdatafile_copy-views\/"},"modified":"2026-05-27T20:02:33","modified_gmt":"2026-05-27T19:02:33","slug":"obtaining-information-about-rman-backups-using-vbackup_set_details-and-vdatafile_copy-views","status":"publish","type":"post","link":"https:\/\/swiv.com.br\/index.php\/2021\/04\/09\/obtaining-information-about-rman-backups-using-vbackup_set_details-and-vdatafile_copy-views\/","title":{"rendered":"OBTAINING INFORMATION ABOUT RMAN BACKUPS using V$BACKUP_SET_DETAILS and V$DATAFILE_COPY views"},"content":{"rendered":"\n

Al\u00e9m do comando LIST do RMAN, outra op\u00e7\u00e3o de resgatarmos informa\u00e7\u00f5es sobre os backups de banco de dados Oracle, seria consumirmos as views “V$BACKUP_SET_DETAILS”, obviamente para os backups do tipo backupset, e a view “V$DATAFILE_COPY” para os image copies. Lembrando que essas informa\u00e7\u00f5es respeitam a reten\u00e7\u00e3o definida no control file, especificamente no par\u00e2metro control_file_record_keep_time (que tem por padr\u00e3o, 7 dias). No meu caso, eu havia aumentado este valor, conforme abaixo:<\/p>\n\n\n

\n[oracle@oel8 BACKUP]$ sqlplus \/ as sysdba\n \nSQL*Plus: Release 18.0.0.0.0 - Production on Fri Apr 9 05:32:05 2021\nVersion 18.13.0.0.0\n \nCopyright (c) 1982, 2018, Oracle.  All rights reserved.\n \n \nConnected to:\nOracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production\nVersion 18.13.0.0.0\n \nSQL> sho parameter control\n \nNAME                                 TYPE        VALUE\n------------------------------------ ----------- ------------------------------\ncontrol_file_record_keep_time        integer     90\n<\/pre><\/div>\n\n\n
Realizando consulta sobre os backupsets:<\/p>\n\n\n
\nset linesize 180 \ncol backup_type for a4 heading "TYPE" \ncol incremental_level heading "INCR LEVEL" \ncol pieces for 99 heading "PCS" \ncol elapsed_seconds heading "SECONDS" \ncol output_mbytes for 999,999 heading "MBYTES" \n \nSELECT D.BS_KEY, D.BACKUP_TYPE, D.INCREMENTAL_LEVEL, D.PIECES, TO_CHAR(D.START_TIME, 'DD-MM-RR HH24:MI:SS') START_TIME, \nTO_CHAR(D.COMPLETION_TIME, 'DD-MM-RR HH24:MI:SS') COMPLETION_TIME, ROUND(D.ELAPSED_SECONDS,3) ELAPSED_SECONDS, (D.OUTPUT_BYTES\/1024\/1024) OUTPUT_MBYTES \nFROM V$BACKUP_SET_DETAILS D JOIN V$BACKUP_SET S ON S.SET_STAMP = D.SET_STAMP AND S.SET_COUNT = D.SET_COUNT ORDER BY D.BS_KEY;\n<\/pre><\/div>\n\n
\nSQL> set linesize 180\ncol backup_type for a4 heading "TYPE"\ncol incremental_level heading "INCR LEVEL"\ncol pieces for 99 heading "PCS"\ncol elapsed_seconds heading "SECONDS"\ncol output_mbytes for 999,999 heading "MBYTES"\n \nSELECT D.BS_KEY, D.BACKUP_TYPE, D.INCREMENTAL_LEVEL, D.PIECES, TO_CHAR(D.START_TIME, 'DD-MM-RR HH24:MI:SS') START_TIME,\nTO_CHAR(D.COMPLETION_TIME, 'DD-MM-RR HH24:MI:SS') COMPLETION_TIME, ROUND(D.ELAPSED_SECONDS,3) ELAPSED_SECONDS, (D.OUTPUT_BYTES\/1024\/1024) OUTPUT_MBYTES\nFROM V$BACKUP_SET_DETAILS D JOIN V$BACKUP_SET S ON S.SET_STAMP = D.SET_STAMP AND S.SET_COUNT = D.SET_COUNT ORDER BY D.BS_KEY;SQL> SQL> SQL> SQL> SQL> SQL> SQL>   2    3\n \n    BS_KEY TYPE INCR LEVEL PCS START_TIME        COMPLETION_TIME      SECONDS   MBYTES\n---------- ---- ---------- --- ----------------- ----------------- ---------- --------\n       154 D                 1 09-04-21 04:31:05 09-04-21 04:32:03         58    1,257\n       155 D                 1 09-04-21 04:32:21 09-04-21 04:32:22          1       11\n       156 D                 1 09-04-21 04:34:09 09-04-21 04:34:09          0        2\n       157 D                 1 09-04-21 04:34:10 09-04-21 04:34:10          0       11\n       158 D                 1 09-04-21 04:34:16 09-04-21 04:34:16          0        2\n       159 D                 1 09-04-21 04:34:17 09-04-21 04:34:18          1       11\n       160 D                 1 09-04-21 04:39:59 09-04-21 04:39:59          0       11\n       161 L                 1 09-04-21 04:43:02 09-04-21 04:43:02          0       72\n       162 D                 1 09-04-21 04:43:05 09-04-21 04:43:05          0       11\n       163 D                 1 09-04-21 04:49:52 09-04-21 04:49:52          0       11\n       164 L                 1 09-04-21 05:17:21 09-04-21 05:17:21          0       23\n \n    BS_KEY TYPE INCR LEVEL PCS START_TIME        COMPLETION_TIME      SECONDS   MBYTES\n---------- ---- ---------- --- ----------------- ----------------- ---------- --------\n       165 D                 1 09-04-21 05:17:22 09-04-21 05:17:23          1       11\n       166 L                 1 09-04-21 05:20:32 09-04-21 05:20:32          0        0\n       167 D                 1 09-04-21 05:20:33 09-04-21 05:20:34          1       11\n       168 L                 1 09-04-21 05:20:53 09-04-21 05:20:53          0        0\n       169 D                 1 09-04-21 05:20:54 09-04-21 05:20:55          1       11\n       170 D                 1 09-04-21 05:23:47 09-04-21 05:23:47          0       11\n \n17 rows selected.\n<\/pre><\/div>\n\n\n
Consulta para os image copies:<\/p>\n\n\n
\ncol TAG format a18 \nSELECT FILE#, STATUS, CREATION_TIME, COMPLETION_TIME, TAG, BLOCKS*BLOCK_SIZE\/1024\/1024 SIZE_MB, INCREMENTAL_LEVEL \nFROM V$DATAFILE_COPY WHERE DELETED ='NO' ORDER BY FILE#, RECID;\n<\/pre><\/div>\n\n
\nSQL> col TAG format a18\nSELECT FILE#, STATUS, CREATION_TIME, COMPLETION_TIME, TAG, BLOCKS*BLOCK_SIZE\/1024\/1024 SIZE_MB, INCREMENTAL_LEVEL\nFROM V$DATAFILE_COPY WHERE DELETED ='NO' ORDER BY FILE#, RECID;SQL>   2\n \n     FILE# S CREATION_TIME       COMPLETION_TIME     TAG                   SIZE_MB INCR LEVEL\n---------- - ------------------- ------------------- ------------------ ---------- ----------\n         1 A 2018-02-07:19:20:19 2021-04-09:04:38:37 DB_FULL_CP                860\n         3 A 2018-02-07:19:20:39 2021-04-09:04:39:15 DB_FULL_CP                630\n         4 A 2018-02-07:21:43:00 2021-04-09:04:39:40 DB_FULL_CP                305\n         5 A 2020-05-02:22:06:13 2021-04-09:04:39:53 DB_FULL_CP                200\n         7 A 2018-02-07:19:22:01 2021-04-09:04:39:58 DB_FULL_CP                  5\n \nSQL>\n<\/pre><\/div>\n\n\n
Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laborat\u00f3rio pessoal para fins de aprendizado.<\/p>\n","protected":false},"excerpt":{"rendered":"
Al\u00e9m do comando LIST do RMAN, outra op\u00e7\u00e3o de resgatarmos informa\u00e7\u00f5es sobre os backups de banco de dados Oracle, seria consumirmos as views “V$BACKUP_SET_DETAILS”, obviamente para os backups do tipo backupset, e a view “V$DATAFILE_COPY” para os image copies. Lembrando que essas informa\u00e7\u00f5es respeitam a reten\u00e7\u00e3o definida no control file, especificamente no par\u00e2metro control_file_record_keep_time (que […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-3630","post","type-post","status-publish","format-standard","hentry","category-backup-recovery"],"_links":{"self":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3630","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/comments?post=3630"}],"version-history":[{"count":1,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3630\/revisions"}],"predecessor-version":[{"id":9183,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/posts\/3630\/revisions\/9183"}],"wp:attachment":[{"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/media?parent=3630"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/categories?post=3630"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/swiv.com.br\/index.php\/wp-json\/wp\/v2\/tags?post=3630"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}