Além do comando LIST do RMAN, outra opção de resgatarmos informações 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ções respeitam a retenção definida no control file, especificamente no parâmetro control_file_record_keep_time (que tem por padrão, 7 dias). No meu caso, eu havia aumentado este valor, conforme abaixo:
[oracle@oel8 BACKUP]$ sqlplus / as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Fri Apr 9 05:32:05 2021
Version 18.13.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.13.0.0.0
SQL> sho parameter control
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time integer 90
Realizando consulta sobre os backupsets:
set linesize 180
col backup_type for a4 heading "TYPE"
col incremental_level heading "INCR LEVEL"
col pieces for 99 heading "PCS"
col elapsed_seconds heading "SECONDS"
col output_mbytes for 999,999 heading "MBYTES"
SELECT D.BS_KEY, D.BACKUP_TYPE, D.INCREMENTAL_LEVEL, D.PIECES, TO_CHAR(D.START_TIME, 'DD-MM-RR HH24:MI:SS') START_TIME,
TO_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
FROM 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> set linesize 180
col backup_type for a4 heading "TYPE"
col incremental_level heading "INCR LEVEL"
col pieces for 99 heading "PCS"
col elapsed_seconds heading "SECONDS"
col output_mbytes for 999,999 heading "MBYTES"
SELECT D.BS_KEY, D.BACKUP_TYPE, D.INCREMENTAL_LEVEL, D.PIECES, TO_CHAR(D.START_TIME, 'DD-MM-RR HH24:MI:SS') START_TIME,
TO_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
FROM 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
BS_KEY TYPE INCR LEVEL PCS START_TIME COMPLETION_TIME SECONDS MBYTES
---------- ---- ---------- --- ----------------- ----------------- ---------- --------
154 D 1 09-04-21 04:31:05 09-04-21 04:32:03 58 1,257
155 D 1 09-04-21 04:32:21 09-04-21 04:32:22 1 11
156 D 1 09-04-21 04:34:09 09-04-21 04:34:09 0 2
157 D 1 09-04-21 04:34:10 09-04-21 04:34:10 0 11
158 D 1 09-04-21 04:34:16 09-04-21 04:34:16 0 2
159 D 1 09-04-21 04:34:17 09-04-21 04:34:18 1 11
160 D 1 09-04-21 04:39:59 09-04-21 04:39:59 0 11
161 L 1 09-04-21 04:43:02 09-04-21 04:43:02 0 72
162 D 1 09-04-21 04:43:05 09-04-21 04:43:05 0 11
163 D 1 09-04-21 04:49:52 09-04-21 04:49:52 0 11
164 L 1 09-04-21 05:17:21 09-04-21 05:17:21 0 23
BS_KEY TYPE INCR LEVEL PCS START_TIME COMPLETION_TIME SECONDS MBYTES
---------- ---- ---------- --- ----------------- ----------------- ---------- --------
165 D 1 09-04-21 05:17:22 09-04-21 05:17:23 1 11
166 L 1 09-04-21 05:20:32 09-04-21 05:20:32 0 0
167 D 1 09-04-21 05:20:33 09-04-21 05:20:34 1 11
168 L 1 09-04-21 05:20:53 09-04-21 05:20:53 0 0
169 D 1 09-04-21 05:20:54 09-04-21 05:20:55 1 11
170 D 1 09-04-21 05:23:47 09-04-21 05:23:47 0 11
17 rows selected.
Consulta para os image copies:
col TAG format a18
SELECT FILE#, STATUS, CREATION_TIME, COMPLETION_TIME, TAG, BLOCKS*BLOCK_SIZE/1024/1024 SIZE_MB, INCREMENTAL_LEVEL
FROM V$DATAFILE_COPY WHERE DELETED ='NO' ORDER BY FILE#, RECID;
SQL> col TAG format a18
SELECT FILE#, STATUS, CREATION_TIME, COMPLETION_TIME, TAG, BLOCKS*BLOCK_SIZE/1024/1024 SIZE_MB, INCREMENTAL_LEVEL
FROM V$DATAFILE_COPY WHERE DELETED ='NO' ORDER BY FILE#, RECID;SQL> 2
FILE# S CREATION_TIME COMPLETION_TIME TAG SIZE_MB INCR LEVEL
---------- - ------------------- ------------------- ------------------ ---------- ----------
1 A 2018-02-07:19:20:19 2021-04-09:04:38:37 DB_FULL_CP 860
3 A 2018-02-07:19:20:39 2021-04-09:04:39:15 DB_FULL_CP 630
4 A 2018-02-07:21:43:00 2021-04-09:04:39:40 DB_FULL_CP 305
5 A 2020-05-02:22:06:13 2021-04-09:04:39:53 DB_FULL_CP 200
7 A 2018-02-07:19:22:01 2021-04-09:04:39:58 DB_FULL_CP 5
SQL>
Obs: Este procedimento foi criado pelo senhor Ahmed Baraka (www.ahmedbaraka.com) e foi apenas reproduzido por mim em um laboratório pessoal para fins de aprendizado.
Portuguese
English
Spanish