oracle RECOVERY_PARALLELISM與instance recovery和medium recovery的關係小記
/*********oracle並行恢復的概念*************/
1,透過io並行化減少恢復時間
2,對於資料檔案儲存在多個磁碟作用顯著
3,用於例項和介質恢復
/**********oracle並行恢復相關的引數************/
RECOVERY_PARALLELISM
1,指定啟用幾個並行恢復的程式
2,受限於parallel_max_servers
4,僅適用於多cpu環境
parallel_max_servers
1,指定一個例項最大可用的並行程式和並行恢復程式的個數
2,其預設值為：parallel_threads_per_cpu * cpu_count * curcurrent_parallel_users* 5
3,取值範圍為:0-3600
4,在rac環境下，各例項可取不同值
5,上述的引數curcurrent_parallel_users取值和例項的記憶體管理方式有關
1,禁用了automatic memory management,其值為1
2,啟了pga_aggrregate_target,其值為2
3,啟用了sga自動管理及pga自動管理,其值為4
6,此引數取值太低,某些查詢在並行處理操作期間會出現缺少可用的並行執行程式
此引數取值太高,在業務高峰,導致資源欠缺,反而降低整體效能
parallel_threads_per_cpu
1,與作業系統有關,一般預設為2
2,指定例項的預設並行度
3,確定並行的動態負載均衡演算法
4,確認並行執行操作期間每個cpu可以處理的並行執行程式數或者每個cpu可以處理的執行緒數threads
/*****介質恢復*****/
SQL> show parameter recovery_parallel
NAME TYPE
------------------------------------ --------
VALUE
------------------------------
recovery_parallelism integer
0
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 6 needs media recovery
ORA-01110: data file 6: 'D:\NEW_TBS.DBF'
/****並行恢復*****/
SQL> recover datafile 6 parallel 2;
Media recovery complete.
/*****對於recovery_parallelism未深入理解********/
/***檢視相關官方手冊*****/
---源於sqlplus user manual,關於sqlplus recover的用法及簡介
PARALLEL [integer]
1,此引數根據例項可用cpu個數及parallel_threads_per_cpu選用合理的並行度
2,此引數優先於recovery_parallelism，會覆蓋後者
3,此引數指定並行度,即並行操作期間啟用的並行執行緒個數
4,上述每個並行執行緒可能會使用1或2個並行執行程式,即執行緒與程式是1:m關係
/*******如果我配置parallel_threads_per_cpu為1****是否recovery就不能啟用並行了?*********/
/****修改上述引數1*******/
SQL> show parameter parallel_threads
NAME TYPE
------------------------------------ ----------
VALUE
------------------------------
parallel_threads_per_cpu integer
2
SQL> alter system set parallel_threads_per_cpu=
System altered.
SQL> show parameter parallel_threads
NAME TYPE
------------------------------------ ----------
VALUE
------------------------------
parallel_threads_per_cpu integer
1
/*******修改cpu為1*********/
SQL> show parameter cpu_count
NAME TYPE
------------------------------------ ---------
VALUE
------------------------------
cpu_count integer
4
SQL> alter system set cpu_count=1;
System altered.
SQL> show parameter cpu_count
NAME TYPE
------------------------------------ ---------
VALUE
------------------------------
cpu_count integer
1
/***上述2引數為1可以啟用並行恢復****/
SQL> recover datafile 6 parallel 2;
Media recovery complete.
SQL> alter database open;
小結:
1,上述的thead可對應1或2個並行執行程式,所以上述2引數配置為1也可以啟用並行恢復
/****啟用>2的並行恢復可以嗎?***********/
-->2也可以並行恢復****
SQL> recover datafile 6 parallel 3;
Media recovery complete.
SQL> recover datafile 6 parallel 4;
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
/******recovery_parallelism測試*******/
---此引數靜態須重啟
SQL> show parameter recovery_para
NAME TYPE
------------------------------------ ---------------------
VALUE
------------------------------
recovery_parallelism integer
0
SQL> alter system set recovery_parallelism=2;
alter system set recovery_parallelism=2
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be mo
SQL> alter system set recovery_parallelism=2 scope=spfile;
System altered.
---在sqlplus執行recover datafile 7,如下日誌表明為序列式恢復，非並行恢復
ALTER DATABASE RECOVER datafile 7
Media Recovery Start
Serial Media Recovery started
--啟用parallel還是序列化恢復
SQL> recover datafile 7 parallel 2;
Media recovery complete.
Media Recovery Start
Serial Media Recovery started
/*********************如下recovery_parallelism=2*************/
/*******會話一恢復一個檔案7******/
SQL> recover datafile 7;
Media recovery complete.
SQL> alter database open;
Database altered.
/*******另啟會話一恢復一個檔案8******/
SQL> recover datafile 8;
Media recovery complete.
SQL>
/*******測試開啟3個會話也可以恢復3個檔案***********/
小結：1,recovery_parallelism不是指的是開啟恢復會話的個數，而是指定並行恢復的程式
/*******如下為例項恢復後的並行程式資訊,記錄剛好和recovery_parallelism引數相等********/
SQL> select * from v$px_process;
SERVER_N STATUS PID
-------- ------------------ ----------
SPID SID SERIAL#
------------------------------------------------ ---------- ----------
P000 AVAILABLE 24
10012
P001 AVAILABLE 25
5996
/******重啟庫後上述檢視消失為空******/
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-32004: obsolete or deprecated parameter(s) speci
ORACLE instance started.
Total System Global Area 1670221824 bytes
Fixed Size 2176328 bytes
Variable Size 1056967352 bytes
Database Buffers 603979776 bytes
Redo Buffers 7098368 bytes
Database mounted.
Database opened.
SQL> select * from v$px_process;
no rows selected
/****驗證下recovery_parallelism與v$px_process的關係********/
SQL> alter system set recovery_parallelism=3 scope=spfile;
System altered.
SQL> startup force
ORA-32004: obsolete or deprecated parameter(s) specified f
ORACLE instance started.
Total System Global Area 1670221824 bytes
Fixed Size 2176328 bytes
Variable Size 1056967352 bytes
Database Buffers 603979776 bytes
Redo Buffers 7098368 bytes
Database mounted.
Database opened.
SQL>
/****shutdown abort例項恢復日誌如下*******/
alter database open
Beginning crash recovery of 1 threads
parallel recovery started with 3 processes --明確指示使用3個並行恢復程式,與recovery_parallelism相等
Started redo scan
Completed redo scan
read 102160 KB redo, 4834 data blocks need recovery
Started redo application at
/*******v$px_process也驗證上述日誌**************/
SQL> select * from v$px_process;
SERVER_N STATUS PID
-------- ------------------ ----------
SPID SID SERIAL#
------------------------------------------------ ---------- ----------
P000 AVAILABLE 20
3244
P001 AVAILABLE 21
6404
P002 AVAILABLE 22
9336
小結:
1,recovery_parallelism指定例項或介質恢復啟用的並行恢復程式
2,並行恢復程式用v$px_process進行查詢
3,recovery_parallelism與max_parallel_servers有關聯
4,max_parallel_servers與這幾個引數parallel_threads_per_cpu ,cpu_count,curcurrent_parallel_users有關
5,parallel_threads_per_cpu指定每個cpu並行的執行緒數,而每個執行緒可對應1或多個並行執行程式
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-758200/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle instance recovery例項恢復小記Oracle
- 【Oracle】-Difference between Instance recovery and Crash RecoveryOracle
- RAC筆記之instance recovery筆記
- OMF和flash_recovery_area的關係！
- 關於INSTANCE RECOVERY過程的理解
- Overview of Instance and Crash RecoveryView
- oracle media recovery介質恢復小記Oracle
- Crash recovery和Media recovery的區別
- Oracle Backup and Recovery FAQOracle
- Oracle 11gR2 fast recovery area = flash recovery areaOracleAST
- oracle redo record scn與transaction相關block scn關係小記Oracle RedoBloC
- oracle 之recovery directory databaseOracleDatabase
- oracle backup & recovery測試Oracle
- oracle scn與備份恢復backup recovery(一)Oracle
- SQL Server小知識：Recovery IntervalSQLServer
- Oracle Data Recovery AdvisorOracle
- zt_例項恢復instance recovery相關原理精簡總結（原創）
- oracle12c Performing Backup and Recovery文件筆記OracleORM筆記
- ORA-00265: instance recovery required, cannot set ARCHIVELOGUIHive
- Oracle 閃回區(Oracle Flash recovery area)Oracle
- Oracle歸檔目錄 和 DB_RECOVERY_FILE_DESTOracle
- Oracle例項恢復的時候所採用的 two-phase recovery/two pass recoveryOracle
- ORA-00265: instance recovery required, cannot set ARCHIVELOG modeUIHive
- Create recovery catalog Configure Recovery Manager
- [oracle]log_archive_dest_n與DB_RECOVERY_FILE_DESTOracleHive
- oracle10g_rman_backup recovery area_相關備紀Oracle
- MySQL InnoDB Update和Crash Recovery流程MySql
- Performing Tablespace Point-in-Time Recovery with Recovery Manager（轉）ORM
- ORACLE 10G flash_recovery_areaOracle 10g
- 第二章（backup and recovery 筆記）筆記
- 第五章（backup and recovery 筆記）筆記
- postgres crash recovery
- Oracle歸檔日誌使用情況： v$recovery_file_dest and v$recovery_area_usage.Oracle
- oracle datafile 與 object的關係OracleObject
- Android 關機、重啟、recovery流程分析Android
- oracle session和process的關係OracleSession
- 聊聊Oracle Data Recovery Advisor（DRA）（上）Oracle
- 聊聊Oracle Data Recovery Advisor（DRA）（下）Oracle