Hoy voy a compartirles un query que nos es de mucha utilidad cuando trabajamos con backups de RMAN y nos encontramos realizando reportes.
Podemos utilizarlo para conocer el tiempo que tardo cada backup segun una determinada fecha y segun el tipo de backup que tomamos.
Los mismos podrián ser DB FULL , ARCHIVELOG , INCREMENTAL , etc.
También conocer el tipo de STATUS con el que finalizo.
Ya sea COMPLETED o FAILED.
El query en cuenstion es :
set serveroutput on set linesize 150 set pagesize 300 col time_taken_display for a9 select session_key, input_type, status, to_char(start_time,'yyyy-mm-dd hh24:mi') start_time, to_char(end_time,'yyyy-mm-dd hh24:mi') end_time, time_taken_display from v$rman_backup_job_details order by session_key desc;
El query esta formado a partir de la vista v$rman_backup_job_details, siendo la misma muy útil si le agregamos campos como SESSION_KEY, COMMAND_ID, OPTIMIZED o INPUT_BYTES, OUTPUT_BYTES.
SQL> desc v$rman_backup_job_details Name Null? Type ----------------------------------------- -------- ---------------------------- SESSION_KEY NUMBER SESSION_RECID NUMBER SESSION_STAMP NUMBER COMMAND_ID VARCHAR2(33) START_TIME DATE END_TIME DATE INPUT_BYTES NUMBER OUTPUT_BYTES NUMBER STATUS_WEIGHT NUMBER OPTIMIZED_WEIGHT NUMBER OBJECT_TYPE_WEIGHT NUMBER OUTPUT_DEVICE_TYPE VARCHAR2(17) AUTOBACKUP_COUNT NUMBER AUTOBACKUP_DONE VARCHAR2(3) STATUS VARCHAR2(23) INPUT_TYPE VARCHAR2(13) OPTIMIZED VARCHAR2(3) ELAPSED_SECONDS NUMBER COMPRESSION_RATIO NUMBER INPUT_BYTES_PER_SEC NUMBER OUTPUT_BYTES_PER_SEC NUMBER INPUT_BYTES_DISPLAY VARCHAR2(4000) OUTPUT_BYTES_DISPLAY VARCHAR2(4000) INPUT_BYTES_PER_SEC_DISPLAY VARCHAR2(4000) OUTPUT_BYTES_PER_SEC_DISPLAY VARCHAR2(4000) TIME_TAKEN_DISPLAY VARCHAR2(4000) SQL>
Expongo un ejemplo de la primer query.
SQL> set serveroutput on set linesize 150 set pagesize 300 col time_taken_display for a9 select session_key, input_type, status, to_char(start_time,'yyyy-mm-dd hh24:mi') start_time, to_char(end_time,'yyyy-mm-dd hh24:mi') end_time, time_taken_display from v$rman_backup_job_details order by session_key desc; SQL> SQL> SQL> SQL> SQL> 2 3 4 5 6 7 8 SESSION_KEY INPUT_TYPE STATUS START_TIME END_TIME TIME_TAKE ----------- ------------- ----------------------- ---------------- ---------------- --------- 42393 DB FULL COMPLETED 2012-06-22 20:10 2012-06-22 21:26 01:15:46 42388 ARCHIVELOG COMPLETED 2012-06-22 19:19 2012-06-22 19:36 00:17:37 42383 ARCHIVELOG COMPLETED 2012-06-22 18:05 2012-06-22 18:12 00:07:12 .... .... 42134 ARCHIVELOG COMPLETED 2012-06-20 06:05 2012-06-20 06:09 00:03:20 42129 ARCHIVELOG COMPLETED 2012-06-20 05:08 2012-06-20 05:12 00:03:46 42124 ARCHIVELOG COMPLETED 2012-06-20 04:06 2012-06-20 04:10 00:04:20 42120 ARCHIVELOG FAILED 2012-06-20 03:02 2012-06-20 03:05 00:02:53 42117 ARCHIVELOG COMPLETED 2012-06-20 02:34 2012-06-20 02:53 00:19:16 42115 ARCHIVELOG COMPLETED 2012-06-20 00:55 2012-06-20 01:12 00:16:25 42111 DB FULL COMPLETED 2012-06-19 20:10 2012-06-19 21:21 01:11:54 42106 ARCHIVELOG COMPLETED 2012-06-19 19:06 2012-06-19 19:10 00:03:28 42101 ARCHIVELOG COMPLETED 2012-06-19 18:09 2012-06-19 18:13 00:03:50 42096 ARCHIVELOG COMPLETED 2012-06-19 17:08 2012-06-19 17:12 00:04:15 42091 ARCHIVELOG COMPLETED 2012-06-19 16:10 2012-06-19 16:14 00:04:34 42086 ARCHIVELOG COMPLETED 2012-06-19 15:08 2012-06-19 15:12 00:04:41 42081 ARCHIVELOG COMPLETED 2012-06-19 14:08 2012-06-19 14:15 00:06:44 42076 ARCHIVELOG COMPLETED 2012-06-19 13:15 2012-06-19 13:22 00:07:19 42071 ARCHIVELOG COMPLETED 2012-06-19 12:12 2012-06-19 12:19 00:07:02 42066 ARCHIVELOG COMPLETED 2012-06-19 11:09 2012-06-19 11:14 00:05:03 42061 ARCHIVELOG COMPLETED 2012-06-19 10:25 2012-06-19 10:33 00:08:02 42056 DB FULL COMPLETED 2012-06-19 07:00 2012-06-19 08:04 01:03:38 42049 ARCHIVELOG COMPLETED 2012-06-19 06:12 2012-06-19 06:34 00:21:29 42046 ARCHIVELOG COMPLETED 2012-06-19 05:25 2012-06-19 05:57 00:32:05 42041 ARCHIVELOG COMPLETED 2012-06-19 04:06 2012-06-19 04:10 00:04:36 42036 ARCHIVELOG COMPLETED 2012-06-19 03:13 2012-06-19 03:19 00:06:35 42031 ARCHIVELOG COMPLETED 2012-06-19 02:17 2012-06-19 02:34 00:17:33 77 rows selected.SQL>
