rman備份產生等待事件
最近資料庫升級後rman備份出現了很多等待事件
SQL> select * from v$version;
BANNER
------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL>
SQL> SELECT sid,event FROM v$session WHERE wait_class#<>6;
SID EVENT
---------- ----------------------------------------------------------------
1165 Backup Restore Throttle sleep
1208 Backup Restore Throttle sleep
1292 Backup Restore Throttle sleep
1379 Backup Restore Throttle sleep
1552 Backup Restore Throttle sleep
2239 resmgr:cpu quantum
2542 SQL*Net message to client
查詢alert日誌資訊
Tue Oct 23 22:00:00 2012
Setting Resource Manager plan SCHEDULER[0x256B668]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Tue Oct 23 22:00:00 2012
Starting background process VKRM
Tue Oct 23 22:00:00 2012
VKRM started with pid=120, OS id=16419
Tue Oct 23 22:00:02 2012
Begin automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Tue Oct 23 22:00:34 2012
Thread 1 advanced to log sequence 18849 (LGWR switch)
Current log# 1 seq# 18849 mem# 0: /oradata/finance/datafile/redo01.log
Current log# 1 seq# 18849 mem# 1: /oradata/finance/datafile/redo01_1.log
Tue Oct 23 22:00:38 2012
Archived Log entry 19993 added for thread 1 sequence 18848 ID 0x99d4983c dest 1:
Tue Oct 23 23:00:05 2012
End automatic SQL Tuning Advisor run for special tuning task "SYS_AUTO_SQL_TUNING_TASK"
Tue Oct 23 23:30:10 2012
Thread 1 advanced to log sequence 18850 (LGWR switch)
Current log# 4 seq# 18850 mem# 0: /oradata/finance/datafile/redo04.log
Current log# 4 seq# 18850 mem# 1: /oradata/finance/datafile/redo04_4.log
Tue Oct 23 23:30:14 2012
Archived Log entry 19994 added for thread 1 sequence 18849 ID 0x99d4983c dest 1:
Wed Oct 24 01:17:07 2012
Thread 1 advanced to log sequence 18851 (LGWR switch)
Current log# 5 seq# 18851 mem# 0: /oradata/finance/datafile/redo05.log
Current log# 5 seq# 18851 mem# 1: /oradata/finance/datafile/redo05_5.log
Wed Oct 24 01:17:17 2012
Archived Log entry 19995 added for thread 1 sequence 18850 ID 0x99d4983c dest 1:
Wed Oct 24 02:00:00 2012
Closing scheduler window
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter
Wed Oct 24 07:23:20 2012
從這裡可以看出來,因為SCHEDULER定時啟動和關閉資源管理的DEFAULT_MAINTENANCE_PLAN從而導致在晚上10點到2點Resource Manager plan處於啟用狀態,所有rman執行任務,Backup Restore Throttle sleep等待是因為Resource Manager plan啟用導致(使用SCHEDULER控制其啟用和關閉),很多情況下資料庫跑的應用比較單一,不是十分的需要啟動資源管理.
在11g中關閉方法如下:
1. Set the current resource manager plan to null (or another plan that is not restrictive):
alter system set resource_manager_plan='' scope=both;
2. Change the active windows to use the null resource manager plan (or other nonrestrictive plan) using:
execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');
3. Then, for each window_name (WINDOW_NAME from DBA_SCHEDULER_WINDOWS), run:
execute dbms_scheduler.set_attribute('<window name>','RESOURCE_PLAN','');
SQL> select WINDOW_NAME from DBA_SCHEDULER_WINDOWS;
WINDOW_NAME
------------------------------
WEEKNIGHT_WINDOW
WEEKEND_WINDOW
MONDAY_WINDOW
TUESDAY_WINDOW
WEDNESDAY_WINDOW
THURSDAY_WINDOW
FRIDAY_WINDOW
SATURDAY_WINDOW
SUNDAY_WINDOW
9 rows selected.
execute dbms_scheduler.set_attribute('WEEKNIGHT_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('WEEKEND_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('MONDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('TUESDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('WEDNESDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('THURSDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('FRIDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('SATURDAY_WINDOW','RESOURCE_PLAN','');
execute dbms_scheduler.set_attribute('SUNDAY_WINDOW','RESOURCE_PLAN','');
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/28211342/viewspace-2141191/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle生產環境RMAN備份指令碼Oracle指令碼
- 線上非rman備份產生和普通操作產生的redo大小比較
- 生產系統下,RMAN的2級備份。以及恢復
- 使用RMAN從相關的備份集、RMAN備份產生的日誌等獲取DBID的二種方法
- Backup And Recovery User's Guide-備份RMAN備份-使用RMAN備份備份集GUIIDE
- 模擬產生CBC LATCH與buffer busy wait等待事件AI事件
- RMAN說,我能備份(9)--RMAN增量備份與備份保留策略
- AIX/Linux生產環境下crontab自動排程RMAN物理備份例子AILinux
- 【RMAN】RMAN備份至ASMASM
- RMAN說,我能備份(14)--實戰RMAN備份
- rman 備份策略
- RMAN備份原理
- Backup And Recovery User's Guide-備份RMAN備份-用RMAN備份映象拷貝備份GUIIDE
- 歸檔日誌大小 與使用rman 備份後的歸檔日誌產生的備份集大小的關係
- RMAN說,我能備份(3)--RMAN全庫備份和表空間備份
- 生產環境備份shell指令碼薦指令碼
- 【RMAN】RMAN的備份保留策略
- rman備份和增量備份指令碼指令碼
- RMAN備份恢復典型案例——RMAN備份&系統變慢
- RMAN說,我能備份(5)--RMAN備份歸檔檔案
- Backup And Recovery User's Guide-RMAN備份概念-RMAN備份的多個拷貝-備份的備份GUIIDE
- RMAN的備份原理
- rman 備份指令碼指令碼
- rman備份型別型別
- RMAN 備份詳解
- RMAN備份詳解
- rman備份學習
- oracle之rman備份Oracle
- rman備份筆記筆記
- RMAN的備份加密加密
- rman 0.1.2 備份 script
- Oracle RMAN 增量備份Oracle
- oracle rman備份命令Oracle
- 常用RMAN 備份命令
- rman備份指令碼指令碼
- rman 備份 全庫
- 【轉】Oracle rman備份Oracle
- -- RMAN備份詳解