Thinking Out Loud

August 23, 2015

Monitoring RMAN Operations

Filed under: RMAN — mdinh @ 5:41 am

Just a reference to source and my version of the script.

This is for restore since there are OUTPUTS.

Script to monitor RMAN Backup and Restore Operations (Doc ID 1487262.1)

$ sqlplus / as sysdba @mon_rman_restore.sql

SQL*Plus: Release 10.2.0.4.0 - Production on Sun Aug 23 01:14:31 2015

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Session altered.


  SID SERIAL# USERNAME	 LOGON_TIME	 OSUSER     PROCESS	   SPID 	MACHINE        ST PROGRAM
----- ------- ---------- --------------- ---------- -------------- ------------ -------------- -- --------------------------------
 3290	   12 SYS	 22-08-15 20:36  oracle     31267	   31298	prod2      I  rman@prod2 (TNS V1-V3)
 3292	    9 SYS	 22-08-15 20:36  oracle     31267	   31297	prod2      I  rman@prod2 (TNS V1-V3)
 3289	   11 SYS	 22-08-15 20:36  oracle     31267	   31299	prod2      A  rman@prod2 (TNS V1-V3)
 3279	    1 SYS	 22-08-15 20:36  oracle     31267	   31301	prod2      A  rman@prod2 (TNS V1-V3)
 3285	   14 SYS	 22-08-15 20:36  oracle     31267	   31300	prod2      A  rman@prod2 (TNS V1-V3)
 3278	    1 SYS	 22-08-15 20:36  oracle     31267	   31302	prod2      A  rman@prod2 (TNS V1-V3)
 3277	    1 SYS	 22-08-15 20:36  oracle     31267	   31303	prod2      A  rman@prod2 (TNS V1-V3)
 3275	    1 SYS	 22-08-15 20:36  oracle     31267	   31305	prod2      A  rman@prod2 (TNS V1-V3)
 3276	    1 SYS	 22-08-15 20:36  oracle     31267	   31304	prod2      A  rman@prod2 (TNS V1-V3)
 3274	    1 SYS	 22-08-15 20:36  oracle     31267	   31306	prod2      A  rman@prod2 (TNS V1-V3)
 3273	    1 SYS	 22-08-15 20:36  oracle     31267	   31307	prod2      A  rman@prod2 (TNS V1-V3)
 3272	    1 SYS	 22-08-15 20:37  oracle     31267	   31308	prod2      A  rman@prod2 (TNS V1-V3)
 3270	    1 SYS	 22-08-15 20:37  oracle     31267	   31310	prod2      A  rman@prod2 (TNS V1-V3)
 3271	    1 SYS	 22-08-15 20:37  oracle     31267	   31309	prod2      A  rman@prod2 (TNS V1-V3)

14 rows selected.


  SID SERIAL# CHANNEL			 SEQ# EVENT			     STATE		SECS	  SOFAR  TOTALWORK % COMPLETE
----- ------- -------------------- ---------- ------------------------------ ------------ ---------- ---------- ---------- ----------
 3274	    1 rman channel=d08		54992 RMAN backup & recovery I/O     WAITING		   0	 342523    6815742	 5.03
 3275	    1 rman channel=d07		18384 RMAN backup & recovery I/O     WAITING		   0	 501503    7340030	 6.83
 3278	    1 rman channel=d04		48839 RMAN backup & recovery I/O     WAITING		   3	 502704    7340030	 6.85
 3272	    1 rman channel=d10		13502 RMAN backup & recovery I/O     WAITING		   3	 495473    6815742	 7.27
 3270	    1 rman channel=d12		39023 RMAN backup & recovery I/O     WAITING		   0	 535039    7340030	 7.29
 3271	    1 rman channel=d11		51018 RMAN backup & recovery I/O     WAITING		   0	 536703    7340030	 7.31
 3276	    1 rman channel=d06		  121 RMAN backup & recovery I/O     WAITING		   0	 503423    6815742	 7.39
 3277	    1 rman channel=d05		  276 RMAN backup & recovery I/O     WAITING		   3	 553855    7389182	  7.5
 3285	   14 rman channel=d02		56444 RMAN backup & recovery I/O     WAITING		   3	 611128    7340030	 8.33
 3289	   11 rman channel=d01		 2482 RMAN backup & recovery I/O     WAITING		   3	 846732    7340030	11.54
 3279	    1 rman channel=d03		 5065 RMAN backup & recovery I/O     WAITING		   3	 882685    7340030	12.03
 3273	    1 rman channel=d09		49115 RMAN backup & recovery I/O     WAITING		   3	1004287    7340030	13.68

12 rows selected.


  SID CHANNEL		   STATUS		OPEN_TIME	       SOFAR_MB   TOTAL_MB % COMPLETE TYPE
----- -------------------- -------------------- -------------------- ---------- ---------- ---------- ---------
FILENAME
----------------------------------------------------------------------------------------------------
 3270 rman channel=d12	   IN PROGRESS		23-AUG-2015 01:06:36	4180.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH1_9qqf6d01_49466_1.bus

 3275 rman channel=d07	   IN PROGRESS		23-AUG-2015 01:06:59	3918.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH3_a0qf6hcg_49472_1.bus

 3289 rman channel=d01	   IN PROGRESS		23-AUG-2015 01:02:00	6615.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH8_9pqf6crq_49465_1.bus

 3285 rman channel=d02	   IN PROGRESS		23-AUG-2015 01:05:46	4647.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH7_9rqf6d1e_49467_1.bus

 3279 rman channel=d03	   IN PROGRESS		23-AUG-2015 01:01:26	6895.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH6_9uqf6d3c_49470_1.bus

 3278 rman channel=d04	   IN PROGRESS		23-AUG-2015 01:07:02	3922.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH2_9sqf6d1t_49468_1.bus

 3277 rman channel=d05	   IN PROGRESS		23-AUG-2015 01:06:20	4327.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH5_9oqf6coh_49464_1.bus

 3276 rman channel=d06	   IN PROGRESS		23-AUG-2015 01:07:00	3933.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH8_a2qf6i9i_49474_1.bus

 3274 rman channel=d08	   IN PROGRESS		23-AUG-2015 01:09:24	2674.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH7_a3qf6ic7_49475_1.bus

 3273 rman channel=d09	   IN PROGRESS		23-AUG-2015 00:59:40	7846.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH3_9vqf6d3d_49471_1.bus

 3272 rman channel=d10	   IN PROGRESS		23-AUG-2015 01:07:07	3869.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH5_a4qf6idl_49476_1.bus

 3271 rman channel=d11	   IN PROGRESS		23-AUG-2015 01:06:35	4193.99 		      INPUT
/shares/dd/prod1/rman/PROD/rman_PROD_DB_level0_CH4_9tqf6d1v_49469_1.bus

 3273 rman channel=d09	   IN PROGRESS		23-AUG-2015 00:59:42	   3923      24576	15.96 OUTPUT
+DATA01/prod2/datafile/xxxdata01.305.888454781

 3279 rman channel=d03	   IN PROGRESS		23-AUG-2015 01:01:28	3447.88      24576	14.03 OUTPUT
+DATA01/prod2/datafile/xxxdata01.307.888454887

 3289 rman channel=d01	   IN PROGRESS		23-AUG-2015 01:02:02	   3308      24576	13.46 OUTPUT
+DATA01/prod2/datafile/xxxdata01.309.888454921

 3273 rman channel=d09	   IN PROGRESS		23-AUG-2015 00:59:41	3923.88   32767.98	11.97 OUTPUT
+DATA01/prod2/datafile/xxxidx01.304.888454781

 3279 rman channel=d03	   IN PROGRESS		23-AUG-2015 01:01:27	3448.88   32767.98	10.53 OUTPUT
+DATA01/prod2/datafile/xxxidx01.306.888454887

 3289 rman channel=d01	   IN PROGRESS		23-AUG-2015 01:02:01	   3308   32767.98	 10.1 OUTPUT
+DATA01/prod2/datafile/xxxidx01.308.888454921

 3285 rman channel=d02	   IN PROGRESS		23-AUG-2015 01:05:47	2387.38      24576	 9.71 OUTPUT
+DATA01/prod2/datafile/xxxdata01.311.888455147

 3276 rman channel=d06	   IN PROGRESS		23-AUG-2015 01:07:03	1966.88      20480	  9.6 OUTPUT
+DATA01/prod2/datafile/xxxdata01.449.867145931.tts

 3272 rman channel=d10	   IN PROGRESS		23-AUG-2015 01:07:08	1935.88      20480	 9.45 OUTPUT
+DATA01/prod2/datafile/xxxidx01.325.888455227

 3277 rman channel=d05	   IN PROGRESS		23-AUG-2015 01:06:22	2163.88      24960	 8.67 OUTPUT
+DATA01/prod2/datafile/xxxdata01.313.888455181

 3271 rman channel=d11	   IN PROGRESS		23-AUG-2015 01:06:36	2096.88      24576	 8.53 OUTPUT
+DATA01/prod2/datafile/xxxdata01.315.888455195

 3270 rman channel=d12	   IN PROGRESS		23-AUG-2015 01:06:38	   2090      24576	  8.5 OUTPUT
+DATA01/prod2/datafile/xxxidx01.317.888455197

 3278 rman channel=d04	   IN PROGRESS		23-AUG-2015 01:07:03	   1964      24576	 7.99 OUTPUT
+DATA01/prod2/datafile/xxxdata01.323.888455223

 3275 rman channel=d07	   IN PROGRESS		23-AUG-2015 01:07:01	1958.88      24576	 7.97 OUTPUT
+DATA01/prod2/datafile/xxxidx01.319.888455221

 3285 rman channel=d02	   IN PROGRESS		23-AUG-2015 01:05:47	   2388   32767.98	 7.29 OUTPUT
+DATA01/prod2/datafile/xxxdata01.310.888455147

 3277 rman channel=d05	   IN PROGRESS		23-AUG-2015 01:06:21	   2164   32767.98	  6.6 OUTPUT
+DATA01/prod2/datafile/xxxidx01.312.888455181

 3274 rman channel=d08	   IN PROGRESS		23-AUG-2015 01:09:25	1337.88      20480	 6.53 OUTPUT
+DATA01/prod2/datafile/xxxidx01.327.888455365

 3271 rman channel=d11	   IN PROGRESS		23-AUG-2015 01:06:35	   2097   32767.98	  6.4 OUTPUT
+DATA01/prod2/datafile/xxxdata01.314.888455195

 3270 rman channel=d12	   IN PROGRESS		23-AUG-2015 01:06:37	2090.88   32767.98	 6.38 OUTPUT
+DATA01/prod2/datafile/xxxidx01.316.888455197

 3276 rman channel=d06	   IN PROGRESS		23-AUG-2015 01:07:02	   1967   32767.98	    6 OUTPUT
+DATA01/prod2/datafile/xxxdata01.320.888455221

 3278 rman channel=d04	   IN PROGRESS		23-AUG-2015 01:07:03	1964.38   32767.98	 5.99 OUTPUT
+DATA01/prod2/datafile/xxxidx01.321.888455223

 3275 rman channel=d07	   IN PROGRESS		23-AUG-2015 01:07:00	   1960   32767.98	 5.98 OUTPUT
+DATA01/prod2/datafile/xxxidx01.318.888455219

 3272 rman channel=d10	   IN PROGRESS		23-AUG-2015 01:07:07	   1936   32767.98	 5.91 OUTPUT
+DATA01/prod2/datafile/xxxidx01.324.888455227

 3274 rman channel=d08	   IN PROGRESS		23-AUG-2015 01:09:25	1338.88   32767.98	 4.09 OUTPUT
+DATA01/prod2/datafile/xxxdata01.326.888455365


36 rows selected.

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ 


SET linesize 160 trimspool ON pages 1000
ALTER session SET nls_date_format = 'DD-MON-YYYY HH24:MI:SS';
col sid FOR 9999
col serial# FOR 99999
col spid FOR a10
col username FOR a10
col osuser FOR a10
col status FOR a10
col program FOR a36 truncate
col logon_time FOR a15
col module FOR a30
col action FOR a35
col process FOR a10
col machine FOR a18 truncate
SELECT s.sid,
s.serial#,
s.username,
TO_CHAR(s.logon_time,'DD-MM-RR hh24:mi') logon_time,
s.osuser,
s.process,
p.spid,
s.machine,
s.status,
s.program
FROM v$session s, v$process p
WHERE s.program LIKE '%rman%'
AND s.paddr = p.addr (+)
ORDER BY s.logon_time, s.sid
;
col event FOR a30
col channel FOR a20
col state FOR a18
SELECT o.sid,
o.serial#,
client_info channel,
seq#,
event,
state,
seconds_in_wait secs,
sofar,
totalwork,
ROUND(sofar/totalwork*100,2) "%COMPLETE"
FROM v$session_longops o, v$session s
WHERE program LIKE '%rman%'
AND opname NOT LIKE '%aggregate%'
AND o.sid =s.sid
AND totalwork != 0
AND sofar != totalwork
AND wait_time > 0
AND NOT action IS NULL
ORDER BY 10 desc
;
col filename FOR a110
col status FOR a20
SELECT a.sid,
client_info channel,
a.status,
open_time,
ROUND(BYTES /1024/1024,2) SOFAR_MB,
ROUND(total_bytes/1024/1024,2) TOTAL_MB,
ROUND(BYTES/TOTAL_BYTES*100,2) "%COMPLETE",
a.type,
filename
FROM v$backup_async_io a, v$session s
WHERE NOT a.STATUS IN ('UNKNOWN')
AND a.sid =s.sid
AND a.status !='FINISHED'
AND total_bytes>0
ORDER BY 8, 7 DESC
;

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: