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 9999 
col username FOR a10 
col osuser FOR a10 
col status FOR a2 
col program FOR a32 
col logon_time FOR a15 
col module FOR a30 
col action FOR a35 
col process FOR a14 
col machine FOR a14
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,
  SUBSTR(s.status,1,1) 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 a12
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
;
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'
ORDER BY 8, 7 DESC
;
EXIT

August 21, 2015

X-Window Fun

Filed under: Uncategorized — mdinh @ 8:43 pm

When ssh -X to another host, I am able to use X-Windows.

[dinh@ca01ts~]$ ssh -X dinh@192.168.1.137
dinh@192.168.1.137's password:
Last login: Fri Aug 21 11:55:47 2015 from 10.237.102.38
/usr/bin/xauth: creating new authority file /home/dinh/.Xauthority
[dinh@arrow ~]$ xclock
Warning: Missing charsets in String to FontSet conversion
^C

However, sudo to another user and X-Windows breaks.

[dinh@arrow ~]$ sudo su - oracle
[sudo] password for dinh:
[oracle@arrow ~]$ xclock
X11 connection rejected because of wrong authentication.
X connection to localhost:10.0 broken (explicit kill or server shutdown).
[oracle@arrow ~]$

Work Around:

Just list the xauth.

[dinh@arrow ~]$ xauth list
arrow/unix:10 MIT-MAGIC-COOKIE-1 8dfb6c468329ff0d5f5d962b094a82d3

Magic is here.

[dinh@arrow ~]$ xauth list | grep unix`echo $DISPLAY | cut -c10-12` > /tmp/xauth
[dinh@arrow ~]$ sudo su - oracle
[sudo] password for dinh:
[oracle@arrow ~]$ xauth add `cat /tmp/xauth`
xauth: creating new authority file /home/oracle/.Xauthority
[oracle@arrow ~]$ xclock
Warning: Missing charsets in String to FontSet conversion
^C

BINGO!

Create a free website or blog at WordPress.com.