?Oracle????SELECT????UNDO
- by Liu Maclean(???)
????????Oracle?????(dirty read),?Oracle??????Asktom????????Oracle???????, ???undo??????????(before image)??????Consistent, ???????????????Oracle?????????????
????????? ??,??,Oracle?????????????RDBMS,????????????
?????????2?????:
_offline_rollback_segments or _corrupted_rollback_segments ?2?????????Oracle???????????ORA-600[4XXX]???????????????,???2??????Undo??Corruption????????????,?????2?????????????????
??????????????_offline_rollback_segments ? _corrupted_rollback_segments ?2?????:
???????(FORCE OPEN DATABASE)
????????????(consistent read & delayed block cleanout)
??????rollback segment???
?????:???????Oracle????????,??????????2?????,?????????????!!
_offline_rollback_segments ? _corrupted_rollback_segments ???????????:
??2???????Undo Segments(???/???)????????online
?UNDO$???????????OFFLINE???
???instance???????????????????
??????Undo Segments????????active transaction????????????dead??SMON???(????????SMON??(?):Recover Dead transaction)
_OFFLINE_ROLLBACK_SEGMENTS(offline undo segment list)????(hidden parameter)?????:
???startup???open database???????_OFFLINE_ROLLBACK_SEGMENTS????Undo segments(???/???),?????undo segments????????alert.log???TRACE?????,???????startup??
?????????????,?ITL?????undo segments?:
???undo segments?transaction table??????????????????
???????????commit,?????CR???
????undo segments????(???corrupted??,???missed??)???????????alert.log,???????
?DML?????????????????????????????????CPU,?????????????????????
_CORRUPTED_ROLLBACK_SEGMENTS(corrupted undo segment list)??????????:
?????startup?open database???_CORRUPTED_ROLLBACK_SEGMENTS????undo segments(???/???)????????
???????_CORRUPTED_ROLLBACK_SEGMENTS???undo segments????????????commit,???undo segments???drop???
???????????
??????????????????,??????????????????
??bootstrap???????????,?????????ORA-00704: bootstrap process failure??,???????????(???Oracle????:??ORA-00600:[4000] ORA-00704: bootstrap process failure????)
??????_CORRUPTED_ROLLBACK_SEGMENTS????????????????????,???????????????
Oracle???????TXChecker???????????
???????2?????,??????????????_CORRUPTED_ROLLBACK_SEGMENTS?????SELECT????UNDO???????:
SQL> alter system set event= '10513 trace name context forever, level 2' scope=spfile;
System altered.
SQL> alter system set "_in_memory_undo"=false scope=spfile;
System altered.
10513 level 2 event????SMON ??rollback ??? dead transaction
_in_memory_undo ?? in memory undo ??
SQL> startup force;
ORACLE instance started.
Total System Global Area 3140026368 bytes
Fixed Size 2232472 bytes
Variable Size 1795166056 bytes
Database Buffers 1325400064 bytes
Redo Buffers 17227776 bytes
Database mounted.
Database opened.
session A:
SQL> conn maclean/maclean
Connected.
SQL> create table maclean tablespace users as select 1 t1 from dual connect by level exec dbms_stats.gather_table_stats('','MACLEAN');
PL/SQL procedure successfully completed.
SQL> set autotrace on;
SQL> select sum(t1) from maclean;
SUM(T1)
----------
501
Execution Plan
----------------------------------------------------------
Plan hash value: 1679547536
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| MACLEAN | 501 | 1503 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processe
???????????,????current block,
????????,consistent gets??3?
SQL> update maclean set t1=0;
501 rows updated.
SQL> alter system checkpoint;
System altered.
??session A?commit;
???? session:
SQL> conn maclean/maclean
Connected.
SQL>
SQL> set autotrace on;
SQL> select sum(t1) from maclean;
SUM(T1)
----------
501
Execution Plan
----------------------------------------------------------
Plan hash value: 1679547536
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| MACLEAN | 501 | 1503 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
505 consistent gets
0 physical reads
108 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
?????? ?????????undo??CR?,???consistent gets??? 505
[oracle@vrh8 ~]$ ps -ef|grep LOCAL=YES |grep -v grep
oracle 5841 5839 0 09:17 ? 00:00:00 oracleG10R25 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
[oracle@vrh8 ~]$ kill -9 5841
??session A???Server Process????,???dead transaction ????smon??
select ktuxeusn,
to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') "Time",
ktuxesiz,
ktuxesta
from x$ktuxe
where ktuxecfl = 'DEAD';
KTUXEUSN Time KTUXESIZ KTUXESTA
---------- -------------------- ---------- ----------------
2 06-AUG-2012 09:20:45 7 ACTIVE
???1?active rollback segment
SQL> conn maclean/maclean
Connected.
SQL> set autotrace on;
SQL> select sum(t1) from maclean;
SUM(T1)
----------
501
Execution Plan
----------------------------------------------------------
Plan hash value: 1679547536
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| MACLEAN | 501 | 1503 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
411 consistent gets
0 physical reads
108 redo size
515 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
????? ????kill?? ???smon ??dead transaction ,
???????????? ?????undo???????
????active?rollback segment???
SQL> select segment_name from dba_rollback_segs where segment_id=2;
SEGMENT_NAME
------------------------------
_SYSSMU2$
SQL> alter system set "_corrupted_rollback_segments"='_SYSSMU2$' scope=spfile;
System altered.
? _corrupted_rollback_segments ?? ???2?rollback segment, ????????undo
SQL> startup force;
ORACLE instance started.
Total System Global Area 3140026368 bytes
Fixed Size 2232472 bytes
Variable Size 1795166056 bytes
Database Buffers 1325400064 bytes
Redo Buffers 17227776 bytes
Database mounted.
Database opened.
SQL> conn maclean/maclean
Connected.
SQL> set autotrace on;
SQL> select sum(t1) from maclean;
SUM(T1)
----------
94
Execution Plan
----------------------------------------------------------
Plan hash value: 1679547536
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| MACLEAN | 501 | 1503 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
228 recursive calls
0 db block gets
29 consistent gets
5 physical reads
116 redo size
514 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> /
SUM(T1)
----------
94
Execution Plan
----------------------------------------------------------
Plan hash value: 1679547536
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 3 | | |
| 2 | TABLE ACCESS FULL| MACLEAN | 501 | 1503 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
514 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
?????? consistent gets???3,?????????????????,??ITL???UNDO SEGMENTS?_corrupted_rollback_segments????,???????????COMMIT??,????UNDO?
???????,?????????????????????????(????????????????????),????????????????? ???? , ?????
