RMAN Querys | Monitoring backups status: COMPLETED, START_TIME, END_TIME & TIME TAKE

G RMAN

G RMAN (Photo credit: Joe Mud)

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>

About Juan Andres
Consultant | Oracle DBA & IT Specialist | LinuxUnix Administrator | Father | Musician | Farmer | Environmentalist | Writer | Builder | Buenos Aires · burzaco.wordpress.com

Comments are closed.

%d bloggers like this: