[20150715]10513事件禁止smon回滾.txt
[20150715]10513事件禁止smon回滾.txt
--如果資料庫特別是特別忙的資料庫出現異常當機的情況,有大量的資料髒塊沒有更新到資料檔案中。還有許多事務沒有提交。
--透過設定10513事件,可以加快資料庫的啟動,但是禁用smon進行tx recovery(所謂tx recovery就是open後資料檔案包含提交和未提交
--資料,資料不一致),不會造成資料庫不一致,雖然禁用了smon自動恢復,但是當查詢的時候還是會進行回滾從undo中讀取回滾資料(等
--同於用到哪個物件回滾哪個物件,這種方式會帶來壓力,且若undo損壞就十分麻煩了,那麼這將是另一個恢復問題select
--segment_name,status,tablespace_name from dba_rollback_segs看那個段損壞使用隱藏引數_offline_rollback_segments 標記,然
--後drop rollback segment 'xxx' ,此時才會造成真正的資料不一致)
--還是透過測試說明情況:
1.建立測試環境:
SCOTT@test> @ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.3.0 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
SCOTT@test> create table t as select rownum id , cast ( 'test' as varchar2(20)) name from xmltable ('1 to 100000');
Table created.
SCOTT@test> alter system flush buffer_cache ;
System altered.
-- 保證髒塊寫盤。
SCOTT@test> select rowid,t.* from t where rownum=1;
ROWID ID NAME
------------------ ---------- --------------------
AABLTpAAEAAABKbAAA 1 test
SCOTT@test> @ lookup_rowid AABLTpAAEAAABKbAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
308457 4 4763 0 4,4763 alter system dump datafile 4 block 4763
2.修改資料不提交看看:
SCOTT@test> update t set name='TTTTTEST' ;
...
不等結束...
開啟另外的會話:
SYS@test> shutdown abort ;
ORACLE instance shut down.
3.透過bbed觀察:
BBED> set dba 4,4763
DBA 0x0100129b (16781979 4,4763)
BBED> x /rnc rowdata
rowdata[0] @2004
----------
flag@2004: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@2005: 0x00
cols@2006: 2
col 0[3] @2007: 524
col 1[4] @2011: test
--可以發現修改的資料並沒有寫盤。使用 dump /v offset 0觀察也可以證明。
SYS@test> startup mount;
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 989859280 bytes
Database Buffers 603979776 bytes
Redo Buffers 7344128 bytes
Database mounted.
SYS@test> alter system set events '10513 trace name context forever,level 2';
System altered.
SYS@test> alter database open ;
Database altered.
--觀察alert*.log檔案:
Completed: ALTER DATABASE MOUNT
Wed Jul 15 11:03:19 2015
OS Pid: 17985 executed alter system set events '10513 trace name context forever,level 2'
Wed Jul 15 11:04:05 2015
alter database open
Beginning crash recovery of 1 threads
parallel recovery started with 3 processes
Started redo scan
Completed redo scan
read 44694 KB redo, 2776 data blocks need recovery
Started redo application at
Thread 1: logseq 3160, block 32678
Recovery of Online Redo Log: Thread 1 Group 2 Seq 3160 Reading mem 0
Mem# 0: /u01/app/oracle11g/oradata/test/redo02.log
Wed Jul 15 11:04:33 2015
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3161 Reading mem 0
Mem# 0: /u01/app/oracle11g/oradata/test/redo03.log
Wed Jul 15 11:04:56 2015
Completed redo application of 38.43MB
Completed crash recovery at
Thread 1: logseq 3161, block 40136, scn 12698741606
2776 data blocks read, 2776 data blocks written, 44694 redo k-bytes read
Wed Jul 15 11:04:57 2015
LGWR: STARTING ARCH PROCESSES
Wed Jul 15 11:04:57 2015
ARC0 started with pid=23, OS id=17995
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
Wed Jul 15 11:04:58 2015
ARC1 started with pid=24, OS id=17997
Wed Jul 15 11:04:59 2015
ARC2 started with pid=25, OS id=17999
Wed Jul 15 11:04:59 2015
ARC3 started with pid=26, OS id=18001
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Thread 1 advanced to log sequence 3162 (thread open)
Thread 1 opened at log sequence 3162
Current log# 1 seq# 3162 mem# 0: /u01/app/oracle11g/oradata/test/redo01.log
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Jul 15 11:04:59 2015
SMON: enabling cache recovery
Archived Log entry 4800 added for thread 1 sequence 3161 ID 0x806ffa4c dest 1:
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
[17985] Successfully onlined Undo Tablespace 2.
Undo initialization finished serial:0 start:1061401088 end:1061401348 diff:260 (2 seconds)
Verifying file header compatibility for 11g tablespace encryption..
Verifying 11g file header compatibility for tablespace encryption completed
SMON: enabling tx recovery
Database Characterset is ZHS16GBK
Wed Jul 15 11:05:00 2015
Incremental checkpoint up to RBA [0xc5a.3.0], current log tail at RBA [0xc5a.3e.0]
No Resource Manager plan active
replication_dependency_tracking turned off (no async multimaster replication found)
Starting background process QMNC
Wed Jul 15 11:05:02 2015
QMNC started with pid=27, OS id=18003
Completed: alter database open
Wed Jul 15 11:05:07 2015
db_recovery_file_dest_size of 20480 MB is 0.09% used. This is a
user-specified limit on the amount of space that will be used by this
database for recovery-related files, and does not reflect the amount of
space available in the underlying filesystem or ASM diskgroup.
Wed Jul 15 11:05:07 2015
Starting background process CJQ0
Wed Jul 15 11:05:07 2015
CJQ0 started with pid=29, OS id=18017
SYS@test> select usn,UNDOBLOCKSDONE,UNDOBLOCKSDONE from v$fast_start_transactions;
no rows selected
--bbed觀察:
BBED> set dba 4,4763
DBA 0x0100129b (16781979 4,4763)
BBED> p *kdbr[0]
rowdata[6982]
-------------
ub1 rowdata[6982] @8173 0x2c
BBED> x /rnc
rowdata[6982] @8173
-------------
flag@8173: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8174: 0x02
cols@8175: 2
col 0[2] @8176: 1
col 1[8] @8179: TTTTTEST
--這個時候說明開機後redo已經應用。也就是前滾。實際上我的資料沒有提交。正常的情況應該看到的像前面的情況。
--這個時候做一個查詢,如果我執行:
SCOTT@test> select rowid,t.* from t where rownum<=1;
ROWID ID NAME
------------------ ---------- --------------------
AABLTpAAEAAABKbAAA 1 test
--說明已經讀取了回滾段資訊。
SCOTT@test> alter system checkpoint;
System altered.
--做一次髒塊寫盤,再使用bbed觀察:
BBED> set dba 4,4763
DBA 0x0100129b (16781979 4,4763)
BBED> p *kdbr[0]
rowdata[6982]
-------------
ub1 rowdata[6982] @8173 0x2c
BBED> x /rnc
rowdata[6982] @8173
-------------
flag@8173: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8174: 0x02
cols@8175: 2
col 0[2] @8176: 1
col 1[8] @8179: TTTTTEST
--可以發現這個時候欄位name還是看到'TTTTEST',而不是'test'。
SCOTT@test> set autot traceonly
SCOTT@test> select rowid,t.* from t where rownum<=1;
Execution Plan
--------------------------
Plan hash value: 508354683
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| T | 94407 | 3411K| 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
713 consistent gets
0 physical reads
108 redo size
675 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--713次consistent gets,也說明讀取了回滾段資訊。
4.正常關閉資料庫,繼續使用bbed觀察:
BBED> set dba 4,4763
DBA 0x0100129b (16781979 4,4763)
BBED> p *kdbr[0]
rowdata[6982]
-------------
ub1 rowdata[6982] @8173 0x2c
BBED> x /rnc
rowdata[6982] @8173
-------------
flag@8173: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8174: 0x02
cols@8175: 2
col 0[2] @8176: 1
col 1[8] @8179: TTTTTEST
--可以發現10513事件禁止smon回滾,但是在讀取時還是透過回滾段讀取正確的資料,但是這些資訊並不會寫盤。
5.正常啟動資料庫,沒有10513事件。
SCOTT@test> select rowid,t.* from t where rownum<=1;
ROWID ID NAME
------------------ ---------- --------------------
AABLTpAAEAAABKbAAA 1 test
SCOTT@test> alter system checkpoint;
System altered.
BBED> set dba 4,4763
DBA 0x0100129b (16781979 4,4763)
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @1329 0x2c
BBED> x /rnc
rowdata[0] @1329
----------
flag@1329: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@1330: 0x00
cols@1331: 2
col 0[2] @1332: 1
col 1[4] @1335: test
--這回正確了。
SCOTT@test> select rowid,t.* from t where rownum<=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 508354683
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 2 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL| T | 94407 | 3411K| 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=1)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
675 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--4consistent gets,也能說明問題。
--總結:
--1.使用10513開啟最好不能給使用者正式使用。僅僅作為監測是否開啟的情況。
--2.最好還是選擇正常的開啟方式,總之dba要情況地瞭解10513事件的作用,解決遇到的資料庫問題。
--3.如果再使用引數_corrupted_rollback_segments,這樣正確的資訊無法顯示,如果再drop undo表空間(不知道是否可以,沒有測試),情
--況就不妙了。正確的資訊再也無法恢復。可以參考我以前寫的連結:
-- http://blog.itpub.net/267265/viewspace-1415396/ => [0126]理解_corrupted_rollback_segments
--4.總之一定要認真瞭解這些引數的含義,選擇正確的方式恢復資料庫。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-1733855/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 直播平臺製作,禁止頁面滾動 / 滾動事件穿透事件穿透
- 回滾操作、回滾段的理解
- [20181222]如何找出回滾操作.txt
- oracle前滾和回滾Oracle
- ORACLE 前滾和回滾Oracle
- 遮罩層禁止頁面滾動遮罩
- 禁止頁面滾動的方法
- [20150715]一條sql語句的優化.txtSQL優化
- oracle回滾溯源Oracle
- ORACLE回滾段Oracle
- oracle event 10513作用Oracle
- 禁止滑鼠點選事件事件
- Oracle 資料回滾Oracle
- Mac回滾iTunes版本Mac
- MONGODB 回滾失敗MongoDB
- ORACLE回滾段(1)Oracle
- ORACLE回滾段(2)Oracle
- ORACLE回滾段(轉)Oracle
- ORACLE回滾段管理Oracle
- 禁止頁面Body在後臺滾動
- SQL code----檢視回滾段名稱及大小 回滾段的管理SQL
- Spring Boot 事物回滾Spring Boot
- java 事務提交/回滾Java
- ORACLE 回滾段詳解Oracle
- 入門Kubernetes - 滾動升級/回滾
- 關於前滾(roll forward)和回滾(roll back)Forward
- 鎖表時KILL SESSION及回滾段監控(回滾時間評估)Session
- JQuery4:滑鼠事件和滾動事件jQuery事件
- vue頁面有彈層,禁止頁面滾動Vue
- 禁止蒙層底部頁面跟隨滾動
- RecyclerView的滾動事件研究View事件
- Oracle例項恢復——說說前滾和回滾Oracle
- 談談 Git 程式碼回滾Git
- Git回滾程式碼暴力法Git
- Oracle提交和回滾處理Oracle
- Oracle的回滾段介紹Oracle
- ORACLE 死事務的回滾Oracle
- 使用 Flash Table 回滾資料