RMAN性能监控

2015-06-30 22:37 abce 阅读(897) 评论(0) 编辑收藏举报

RMAN性能调优相关视图

视图名	说明
v$rman_backup_job_details	备份job信息
v$backup_async_io	当前正在运行的、最近完成的备份和restore操作的rman异步I/O性能信息
v$backup_sync_io	当前正在运行的、最近完成的备份和restore操作的rman同步I/O性能信息
v$process	当前活跃进程
v$session	当前活跃会话信息
v$session_longops	可以显示rman备份、还原和恢复进度
v$recovery_progress	rman操作进度
v$session_wait	显示会话正在等待的事件、资源信息

1.找出执行rman的数据库会话

SQL> SELECT s.sid, s.serial#, p.spid, s.client_info
  2    FROM v$process p, v$session s
  3   WHERE p.addr = s.paddr
  4     AND s.client_info LIKE '%rman%';

       SID    SERIAL# SPID                     CLIENT_INFO
---------- ---------- ------------------------ ----------------------------------------------------------------
        69        153 13356                    rman channel=ORA_DISK_1

SQL>

在执行rman操作时候，可以使用"set command id"来标识rman会话进程

RMAN> run{
2> allocate channel d1 type disk;
3> set command id to 'my_session';
4> backup database;
5> }

SQL> SELECT b.sid, b.serial#, a.spid, b.client_info
  2    FROM v$process a, v$session b
  3   WHERE a.addr = b.paddr
  4     AND b.client_info LIKE '%rman%';

       SID    SERIAL# SPID                     CLIENT_INFO
---------- ---------- ------------------------ ----------------------------------------------------------------
        69        157 13434                    id=my_session,rman channel=d1

SQL>

2.查看rman job详细信息：

SQL> select session_recid,
  2         input_bytes_per_sec_display,
  3         output_bytes_per_sec_display,
  4         time_taken_display,
  5         end_time
  6    from v$rman_backup_job_details
  7   order by end_time;

SESSION_RECID INPUT_BYTES_PER_SEC_ OUTPUT_BYTES_PER_SEC TIME_TAKEN_DISPLAY             END_TIME
------------- -------------------- -------------------- ------------------------------ ------------------------------
            1     3.09M                3.12M            00:00:03                       17-JUN-15
            3   178.12K              122.60K            05:38:23                       17-JUN-15
           27   107.93M               75.97M            00:00:17                       23-JUN-15
           42    64.91M               50.01M            00:00:37                       25-JUN-15
           51   109.27M               76.85M            00:00:17                       25-JUN-15
           57   109.27M               76.85M            00:00:17                       25-JUN-15
           90    43.96M               31.23M            00:02:10                       29-JUN-15
           98    19.74M               14.03M            00:03:13                       29-JUN-15

8 rows selected.

SQL>

3.查看rman操作的进度

select s.client_info,
       sl.opname,
       sl.message,
       sl.sid,
       sl.serial#,
       p.spid,
       sl.sofar,
       sl.totalwork,
       round(sl.sofar / sl.totalwork * 100, 2) "% Complete"
  from v$session_longops sl, v$session s, v$process p
 where p.addr = s.paddr
   and sl.sid = s.sid
   and sl.serial# = s.serial#
   and opname LIKE 'RMAN%'
   and opname NOT LIKE '%aggregate%'
   and totalwork != 0
   and sofar <> totalwork;