Exemplo de consulta para verificar o log da última execução de backup, fazendo um join da V$RMAN_OUTPUT com a V$RMAN_STATUS.
A consulta a seguir vai exibir o log da última execução de backup com mensagens como início, progresso, conclusão e possíveis erros:
SET PAGES 2000
SET LINES 210
SELECT SESSION_RECID,
SESSION_STAMP,
OUTPUT
FROM V$RMAN_OUTPUT
WHERE SESSION_RECID = (SELECT MAX(SESSION_RECID) FROM V$RMAN_STATUS WHERE OPERATION = 'BACKUP')
ORDER BY RECID;
SESSION_RECID SESSION_STAMP OUTPUT
------------- ------------- ----------------------------------------------------------------------------------------------------------------------------------
99305 1184854202 connected to target database: CDBPRD01 (DBID=684432656)
99305 1184854202
99305 1184854202
99305 1184854202 using target database control file instead of recovery catalog
99305 1184854202 RMAN configuration parameters for database with db_unique_name CDBPRD01_GRU14V are:
99305 1184854202 CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 5 DAYS;
99305 1184854202 CONFIGURE BACKUP OPTIMIZATION OFF; # default
99305 1184854202 CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
99305 1184854202 CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
99305 1184854202 CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
99305 1184854202 CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
99305 1184854202 CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
99305 1184854202 CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
99305 1184854202 CONFIGURE MAXSETSIZE TO UNLIMITED; # default
99305 1184854202 CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
99305 1184854202 CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
99305 1184854202 CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
99305 1184854202 CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # default
99305 1184854202 CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
99305 1184854202 CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/19.0.0/dbhome_1/dbs/snapcf_cdbprd01.f'; # default
99305 1184854202
99305 1184854202
99305 1184854202 allocated channel: a1
99305 1184854202 channel a1: SID=492 device type=DISK
99305 1184854202
99305 1184854202 sql statement: alter system archive log current
99305 1184854202
99305 1184854202 Starting backup at 12-NOV-24
99305 1184854202 current log archived
99305 1184854202 channel a1: starting compressed archived log backup set
99305 1184854202 channel a1: specifying archived log(s) in backup set
99305 1184854202 input archived log thread=1 sequence=30234 RECID=30234 STAMP=1184854205
99305 1184854202 input archived log thread=1 sequence=30235 RECID=30235 STAMP=1184854205
99305 1184854202 channel a1: starting piece 1 at 12-NOV-24
99305 1184854202 channel a1: finished piece 1 at 12-NOV-24
99305 1184854202 piece handle=/orabackup/rman/cdbprd01/arch/archive_CDBPRD01_96247_1_1184854205_20241112.arc tag=DISKARCHIVELOGDIARIO comment=NON
99305 1184854202 channel a1: backup set complete, elapsed time: 00:00:15
99305 1184854202 channel a1: deleting archived log(s)
99305 1184854202 archived log file name=/u03/app/oracle/fast_recovery_area/CDBPRD01_GRU14V/archivelog/2024_11_12/o1_mf_1_30234_mm72zcnq_.arc RECI
99305 1184854202 archived log file name=/u03/app/oracle/fast_recovery_area/CDBPRD01_GRU14V/archivelog/2024_11_12/o1_mf_1_30235_mm72zfky_.arc RECI
99305 1184854202 Finished backup at 12-NOV-24
99305 1184854202
99305 1184854202 Starting Control File and SPFILE Autobackup at 12-NOV-24
99305 1184854202 piece handle=/u03/app/oracle/fast_recovery_area/CDBPRD01_GRU14V/autobackup/2024_11_12/o1_mf_s_1184854221_mm72zx9t_.bkp comment=N
99305 1184854202 Finished Control File and SPFILE Autobackup at 12-NOV-24
99305 1184854202
99305 1184854202 released channel: a1
99305 1184854202
99305 1184854202 allocated channel: s1
99305 1184854202 channel s1: SID=492 device type=DISK
99305 1184854202
99305 1184854202 Starting backup at 12-NOV-24
99305 1184854202 channel s1: starting compressed full datafile backup set
99305 1184854202 channel s1: specifying datafile(s) in backup set
99305 1184854202 including current SPFILE in backup set
99305 1184854202 channel s1: starting piece 1 at 12-NOV-24
99305 1184854202 channel s1: finished piece 1 at 12-NOV-24
99305 1184854202 piece handle=/orabackup/rman/cdbprd01/arch/spfile_CDBPRD01_96249_1_1184854224_20241112.spf tag=DISKSPFILEDIARIO comment=NONE
99305 1184854202 channel s1: backup set complete, elapsed time: 00:00:01
99305 1184854202 Finished backup at 12-NOV-24
99305 1184854202
99305 1184854202 Starting Control File and SPFILE Autobackup at 12-NOV-24
99305 1184854202 piece handle=/u03/app/oracle/fast_recovery_area/CDBPRD01_GRU14V/autobackup/2024_11_12/o1_mf_s_1184854225_mm7301ol_.bkp comment=N
99305 1184854202 Finished Control File and SPFILE Autobackup at 12-NOV-24
99305 1184854202
99305 1184854202 released channel: s1
99305 1184854202
99305 1184854202 allocated channel: c1
99305 1184854202 channel c1: SID=492 device type=DISK
99305 1184854202
99305 1184854202 Starting backup at 12-NOV-24
99305 1184854202 channel c1: starting compressed full datafile backup set
99305 1184854202 channel c1: specifying datafile(s) in backup set
99305 1184854202 including current control file in backup set
99305 1184854202 channel c1: starting piece 1 at 12-NOV-24
99305 1184854202 channel c1: finished piece 1 at 12-NOV-24
99305 1184854202 piece handle=/orabackup/rman/cdbprd01/arch/controlfile_CDBPRD01_96251_1_1184854227_20241112.ctl tag=DISKCURRENTCONTROLFILEDIARIO
99305 1184854202 channel c1: backup set complete, elapsed time: 00:00:01
99305 1184854202 Finished backup at 12-NOV-24
99305 1184854202
99305 1184854202 Starting Control File and SPFILE Autobackup at 12-NOV-24
99305 1184854202 piece handle=/u03/app/oracle/fast_recovery_area/CDBPRD01_GRU14V/autobackup/2024_11_12/o1_mf_s_1184854229_mm7305fw_.bkp comment=N
99305 1184854202 Finished Control File and SPFILE Autobackup at 12-NOV-24
99305 1184854202
99305 1184854202 released channel: c1
99305 1184854202
99305 1184854202
99305 1184854202
99305 1184854202
89 rows selected.Já a view V$RMAN_STATUS contém informações resumidas sobre cada execução do RMAN, incluindo o status e a data de início e término. Esta consulta retorna o status (COMPLETED ou FAILED), o tamanho do backup, e o tempo de início e término das últimas operações de backup:
SET LINES 210
SELECT COMMAND_ID,
STATUS,
START_TIME,
END_TIME,
INPUT_BYTES / 1024 / 1024 AS INPUT_MB,
OUTPUT_BYTES / 1024 / 1024 AS OUTPUT_MB,
OUTPUT_DEVICE_TYPE,
OBJECT_TYPE
FROM V$RMAN_STATUS
WHERE OPERATION = 'BACKUP'
ORDER BY START_TIME DESC;
COMMAND_ID STATUS START_TIM END_TIME INPUT_MB OUTPUT_MB OUTPUT_DEVICE_TYP OBJECT_TYPE
--------------------------------- ----------------------- --------- --------- ---------- ---------- ----------------- -------------
2024-11-12T14:10:01 COMPLETED 12-NOV-24 12-NOV-24 42.3125 22.8125 DISK CONTROLFILE
2024-11-12T14:10:01 COMPLETED 12-NOV-24 12-NOV-24 21.15625 21.359375 DISK SPFILE
2024-11-12T14:10:01 COMPLETED 12-NOV-24 12-NOV-24 266.751953 178.879883 DISK ARCHIVELOG
2024-11-12T13:10:02 COMPLETED 12-NOV-24 12-NOV-24 42.3125 22.8125 DISK CONTROLFILE
2024-11-12T13:10:02 COMPLETED 12-NOV-24 12-NOV-24 21.15625 21.359375 DISK SPFILE
2024-11-12T13:10:02 COMPLETED 12-NOV-24 12-NOV-24 324.699219 208.924805 DISK ARCHIVELOG
2024-11-12T12:10:01 COMPLETED 12-NOV-24 12-NOV-24 42.3125 22.8125 DISK CONTROLFILE
2024-11-12T12:10:01 COMPLETED 12-NOV-24 12-NOV-24 21.15625 21.359375 DISK SPFILE
2024-11-12T12:10:01 COMPLETED 12-NOV-24 12-NOV-24 260.390625 172.073242 DISK ARCHIVELOG
2024-11-12T11:10:01 COMPLETED 12-NOV-24 12-NOV-24 42.3125 22.8125 DISK CONTROLFILE
2024-11-12T11:10:01 COMPLETED 12-NOV-24 12-NOV-24 21.15625 21.359375 DISK SPFILE
2024-11-12T11:10:01 COMPLETED 12-NOV-24 12-NOV-24 263.773926 173.476074 DISK ARCHIVELOG
2024-11-12T10:10:01 COMPLETED 12-NOV-24 12-NOV-24 42.3125 22.8125 DISK CONTROLFILE
2024-11-12T10:10:01 COMPLETED 12-NOV-24 12-NOV-24 21.15625 21.359375 DISK SPFILE
2024-11-12T10:10:01 COMPLETED 12-NOV-24 12-NOV-24 355.038574 231.52832 DISK ARCHIVELOG
2024-11-12T09:10:01 COMPLETED 12-NOV-24 12-NOV-24 42.3125 22.8125 DISK CONTROLFILE
2024-11-12T09:10:01 COMPLETED 12-NOV-24 12-NOV-24 21.15625 21.359375 DISK SPFILE
2024-11-12T09:10:01 COMPLETED 12-NOV-24 12-NOV-24 269.641602 179.300293 DISK ARCHIVELOG
2024-11-12T08:10:02 COMPLETED 12-NOV-24 12-NOV-24 42.3125 22.8125 DISK CONTROLFILE
2024-11-12T08:10:02 COMPLETED 12-NOV-24 12-NOV-24 21.15625 21.359375 DISK SPFILE
2024-11-12T08:10:02 COMPLETED 12-NOV-24 12-NOV-24 503.387207 328.035156 DISK ARCHIVELOG
2024-11-12T07:10:02 COMPLETED 12-NOV-24 12-NOV-24 42.3125 22.8125 DISK CONTROLFILE
2024-11-12T07:10:02 COMPLETED 12-NOV-24 12-NOV-24 21.15625 21.359375 DISK SPFILE
2024-11-12T07:10:02 COMPLETED 12-NOV-24 12-NOV-24 219.899902 146.877441 DISK ARCHIVELOG
2024-11-12T06:10:01 COMPLETED 12-NOV-24 12-NOV-24 42.3125 22.8125 DISK CONTROLFILE
2024-11-12T06:10:01 COMPLETED 12-NOV-24 12-NOV-24 21.15625 21.359375 DISK SPFILE
2024-11-12T06:10:01 COMPLETED 12-NOV-24 12-NOV-24 201.074707 134.429688 DISK ARCHIVELOG
2024-11-12T05:10:01 COMPLETED 12-NOV-24 12-NOV-24 42.3125 22.8125 DISK CONTROLFILE
2024-11-12T05:10:01 COMPLETED 12-NOV-24 12-NOV-24 21.15625 21.359375 DISK SPFILE
2024-11-12T05:10:01 COMPLETED 12-NOV-24 12-NOV-24 190.028809 127.066406 DISK ARCHIVELOG
2024-11-12T04:10:02 COMPLETED 12-NOV-24 12-NOV-24 42.3125 22.8125 DISK CONTROLFILE
2024-11-12T04:10:02 COMPLETED 12-NOV-24 12-NOV-24 21.15625 21.359375 DISK SPFILE
2024-11-12T04:10:02 COMPLETED 12-NOV-24 12-NOV-24 1483.48193 839.626953 DISK ARCHIVELOG
2024-11-12T03:10:01 COMPLETED 12-NOV-24 12-NOV-24 42.3125 22.8125 DISK CONTROLFILE
2024-11-12T03:10:01 COMPLETED 12-NOV-24 12-NOV-24 21.15625 21.359375 DISK SPFILE
2024-11-12T03:10:01 COMPLETED 12-NOV-24 12-NOV-24 3330 2702 DISK ARCHIVELOG
36 rows selected.
SQL>
Comentários
Postar um comentário