====== RMAN ======
== running Jobs ==
set lines 200 pages 2000
col STATUS format a20
col dev format a10
col START_TIME format a16
col END_TIME format a16
col hrs format 99.99
col INBYTES format a10
col OUTBYTES format a10
select SESSION_KEY,SESSION_RECID, SESSION_STAMP,INPUT_TYPE, STATUS, output_device_type DEV,
to_char(START_TIME,'dd/mm/yyyy hh24:mi') start_time,
to_char(END_TIME,'dd/mm/yyyy hh24:mi') end_time,
elapsed_seconds/3600 hrs,
input_bytes_display inbytes, output_bytes_display outbytes
from V$RMAN_BACKUP_JOB_DETAILS
where status='RUNNING'
order by session_key;
SESSION_KEY SESSION_RECID SESSION_STAMP INPUT_TYPE STATUS DEV START_TIME END_TIME HRS INBYTES OUTBYTES
----------- ------------- ------------- ------------- -------------------- ---------- ---------------- ---------------- ------ ---------- ----------
6060 6060 1186690460 ARCHIVELOG RUNNING SBT_TAPE 02/12/2024 20:14 04/12/2024 10:49 38.58 525.00K 512.00K
6187 6187 1186732699 ARCHIVELOG RUNNING 03/12/2024 07:58 04/12/2024 10:49 26.84 0.00K 0.00K
6193 6193 1186776020 ARCHIVELOG RUNNING 03/12/2024 20:00 04/12/2024 10:49 14.81 0.00K 0.00K
6196 6196 1186776141 ARCHIVELOG RUNNING 03/12/2024 20:02 04/12/2024 10:49 14.78 0.00K 0.00K
6199 6199 1186776200 ARCHIVELOG RUNNING 03/12/2024 20:03 04/12/2024 10:49 14.76 0.00K 0.00K
6202 6202 1186776260 ARCHIVELOG RUNNING 03/12/2024 20:04 04/12/2024 10:49 14.74 0.00K 0.00K
6205 6205 1186776321 ARCHIVELOG RUNNING 03/12/2024 20:05 04/12/2024 10:49 14.72 0.00K 0.00K
6208 6208 1186776380 ARCHIVELOG RUNNING 03/12/2024 20:06 04/12/2024 10:49 14.71 0.00K 0.00K
6211 6211 1186776441 ARCHIVELOG RUNNING 03/12/2024 20:07 04/12/2024 10:49 14.69 0.00K 0.00K
6214 6214 1186776498 ARCHIVELOG RUNNING 03/12/2024 20:08 04/12/2024 10:49 14.68 0.00K 0.00K
6217 6217 1186776560 ARCHIVELOG RUNNING 03/12/2024 20:09 04/12/2024 10:49 14.66 0.00K 0.00K
6220 6220 1186776618 ARCHIVELOG RUNNING 03/12/2024 20:10 04/12/2024 10:49 14.64 0.00K 0.00K
6223 6223 1186776680 ARCHIVELOG RUNNING 03/12/2024 20:11 04/12/2024 10:49 14.63 0.00K 0.00K
6226 6226 1186776740 ARCHIVELOG RUNNING 03/12/2024 20:12 04/12/2024 10:49 14.61 0.00K 0.00K
6229 6229 1186776801 ARCHIVELOG RUNNING 03/12/2024 20:13 04/12/2024 10:49 14.59 0.00K 0.00K
6232 6232 1186776858 ARCHIVELOG RUNNING 03/12/2024 20:14 04/12/2024 10:49 14.58 0.00K 0.00K
6235 6235 1186776923 ARCHIVELOG RUNNING 03/12/2024 20:15 04/12/2024 10:49 14.56 0.00K 0.00K
6238 6238 1186776978 ARCHIVELOG RUNNING 03/12/2024 20:16 04/12/2024 10:49 14.54 0.00K 0.00K
6241 6241 1186777043 ARCHIVELOG RUNNING 03/12/2024 20:17 04/12/2024 10:49 14.52 0.00K 0.00K
\\
== Kill runing jobs ==
### Check what Session will be killed
select s.sid, s.serial#, s.username,
to_char(s.logon_time,'DD-MON HH24:MI:SS') logon_time,
p.pid oraclepid, p.spid "ServerPID", s.process "ClientPID",
s.program clientprogram, s.module, s.machine, s.osuser,
s.status, s.last_call_et
from v$session s, v$process p
where s.paddr=p.addr
and s.program like 'rman%'
order by s.sid
### Generate simple command
select '! kill -9 '||p.spid kill_rman_process
from v$session s, v$process p
where s.paddr=p.addr
and s.program like 'rman%'
order by s.sid
KILL_RMAN_PROCESS
----------------------------------
! kill -9 2516
! kill -9 2517
! kill -9 2497
\\
== Job Summary with SESSION_RECID and SESSION_STAMP==
set lines 220
set pages 1000
col backup_type for a4 heading "TYPE"
col controlfile_included heading "CF?"
col incremental_level heading "INCR LVL"
col pieces for 999 heading "PCS"
col elapsed_seconds heading "ELAPSED|SECONDS"
col device_type for a10 trunc heading "DEVICE|TYPE"
col compressed for a4 heading "ZIP?"
col output_mbytes for 9,999,999 heading "OUTPUT|MBYTES"
col input_file_scan_only for a4 heading "SCAN|ONLY"
select
d.bs_key, d.backup_type, d.controlfile_included, d.incremental_level, d.pieces,
to_char(d.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
to_char(d.completion_time, 'yyyy-mm-dd hh24:mi:ss') completion_time,
d.elapsed_seconds, d.device_type, d.compressed, (d.output_bytes/1024/1024) output_mbytes, s.input_file_scan_only
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
where session_recid = &SESSION_RECID
and session_stamp = &SESSION_STAMP
order by d.start_time;
ELAPSED DEVICE OUTPUT SCAN
BS_KEY TYPE CF? INCR LVL PCS START_TIME COMPLETION_TIME SECONDS TYPE ZIP? MBYTES ONLY
---------- ---- --- ---------- ---- ---------------- ------------------- ---------- ---------- ---- ---------- ----
3336 L NO 1 2024-12-02 20:14 2024-12-02 20:15:25 41 SBT_TAPE YES 1 NO
\\
== Job Detail with SESSION_RECID and SESSION_STAMP ==
set lines 200
set pages 1000
select output
from GV$RMAN_OUTPUT
where session_recid = &SESSION_RECID
and session_stamp = &SESSION_STAMP
order by recid;
OUTPUT
----------------------------------------------------------------------------------------------------------------------------------
connected to target database: C1DIGDEV (DBID=1272122079)
connected to recovery catalog database
PL/SQL package RMAN_C1DIGDEV.DBMS_RCVCAT version 19.21.00.00. in RCVCAT database is not current
PL/SQL package RMAN_C1DIGDEV.DBMS_RCVMAN version 19.21.00.00 in RCVCAT database is not current
resync only records later than timestamp 1186107934
RMAN configuration parameters for database with db_unique_name C1DIGDEV are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE 'SBT_TAPE' TO '%F';
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 4 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK MAXPIECESIZE 2 G;
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' MAXPIECESIZE 2 G;
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE;
CONFIGURE RMAN OUTPUT TO KEEP FOR 90 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO 'SBT_TAPE';
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/odaorahome/oracle/product/19.0.0.0/dbhome_1/dbs/snapcf_C1DIGDEV1.f'; # default
[...]
Finished backup at 02-DEC-24
Starting Control File and SPFILE Autobackup at 02-DEC-24
released channel: ch00
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of Control File and SPFILE Autobackup command on ch00 channel at 12/04/2024 10:57:29
ORA-19506: failed to create sequential file, name="c-1272122079-20241202-ff", parms=""
ORA-27027: sbtremove2 returned error
ORA-19511: non RMAN, but media manager or vendor specific failure, error text:
Failed to remove, c-1272122079-20241202-ff, from image catalog.
\\
== Past 24 hours jobs ==
set lines 220
set pages 1000
col cf for 9,999
col df for 9,999
col elapsed_seconds heading "ELAPSED|SECONDS"
col i0 for 9,999
col i1 for 9,999
col l for 9,999
col output_mbytes for 99,999,999 heading "OUTPUT|MBYTES"
col session_recid for 999999 heading "SESSION|RECID"
col session_stamp for 99999999999 heading "SESSION|STAMP"
col status for a20
col time_taken_display for a10 heading "TIME|TAKEN"
col output_instance for 9999 heading "OUT|INST"
col DOW format a10
col START_TIME format a20
col END_TIME format a20
select
j.session_recid, j.session_stamp,
to_char(j.start_time, 'yyyy-mm-dd hh24:mi:ss') start_time,
to_char(j.end_time, 'yyyy-mm-dd hh24:mi:ss') end_time,
(j.output_bytes/1024/1024) output_mbytes, j.status, j.input_type,
decode(to_char(j.start_time, 'd'), 1, 'SUNDAY', 2, 'MONDAY',
3, 'TUESDAY', 4, 'WEDNESDAY',
5, 'THURSDAY', 6, 'FRIDAY',
7, 'SATURDAY') dow,
j.elapsed_seconds, j.time_taken_display,
x.cf, x.df, x.i0, x.i1, x.l,
ro.inst_id output_instance
from V$RMAN_BACKUP_JOB_DETAILS j
left outer join (select
d.session_recid, d.session_stamp,
sum(case when d.controlfile_included = 'YES' then d.pieces else 0 end) CF,
sum(case when d.controlfile_included = 'NO'
and d.backup_type||d.incremental_level = 'D' then d.pieces else 0 end) DF,
sum(case when d.backup_type||d.incremental_level = 'D0' then d.pieces else 0 end) I0,
sum(case when d.backup_type||d.incremental_level = 'I1' then d.pieces else 0 end) I1,
sum(case when d.backup_type = 'L' then d.pieces else 0 end) L
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
where s.input_file_scan_only = 'NO'
group by d.session_recid, d.session_stamp) x
on x.session_recid = j.session_recid and x.session_stamp = j.session_stamp
left outer join (select o.session_recid, o.session_stamp, min(inst_id) inst_id
from GV$RMAN_OUTPUT o
group by o.session_recid, o.session_stamp)
ro on ro.session_recid = j.session_recid and ro.session_stamp = j.session_stamp
where j.start_time > trunc(sysdate)-1
order by j.start_time;