【大膽的實驗】Oracle的不一致性,未commit資料變成實際資料並被其他回話查詢

PiscesCanon發表於2017-07-19
OracleMIT
作業系統版本：
  1. [oracle@oracle zkm]$ uname -a
  2. Linux oracle.example.com 2.6.32-431.el6.x86_64 #1 SMP Sun Nov 10 22:19:54 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
  3. [oracle@oracle zkm]$ lsb_release -a
  4. LSB Version:    :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
  5. Distributor ID: RedHatEnterpriseServer
  6. Description:    Red Hat Enterprise Linux Server release 6.5 (Santiago)
  7. Release:        6.5
  8. Codename:       Santiago

資料庫版本：
  1. SYS@proc> select * from v$version where rownum=1;

  3. BANNER
  4. --------------------------------------------------------------------------------
  5. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

環境構造：
  1. SYS@proc> drop table tt purge;

  3. Table dropped.

  4.
  5. SYS@proc> create table tt (id int,name varchar2(2000)) tablespace users;

  7. Table created.

  9. SYS@proc> begin
  10.               for i in 1..10000 loop
  11.                   insert into tt values(i,rpad('a',2000,'+'));
  12.               end loop;
  13.               commit;
  14.           end;
  15.           /

  17. PL/SQL procedure successfully completed.

實驗過程：
  1. SYS@proc> begin
  2.               for i in 1..10000 loop
  3.                   update tt set name=rpad('b',2000,'-') where id=i;
  4.               end loop;
  5.           end;
  6.           /

  8. PL/SQL procedure successfully completed.

  10. SYS@proc> shutd abort;
  11. ORACLE instance shut down.
  12. SYS@proc> exit
  13. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
  14. With the Partitioning, OLAP, Data Mining and Real Application Testing options
  15. [oracle@oracle ~]$ ll un*
  16. -rw-r-----. 1 oracle oinstall 104865792 Jul 19 11:19 undotbs1.dbf
  17. [oracle@oracle ~]$ rm -fr undotbs1.dbf
  18. [oracle@oracle ~]$ sqlplus / as sysdba

  20. SQL*Plus: Release 11.2.0.4.0 Production on Wed Jul 19 11:20:17 2017

  22. Copyright (c) 1982, 2013, Oracle. All rights reserved.

  24. Connected to an idle instance.

  26. SYS@proc> startup;
  27. ORACLE instance started.

  29. Total System Global Area 521936896 bytes
  30. Fixed Size                 2254824 bytes
  31. Variable Size            306186264 bytes
  32. Database Buffers         209715200 bytes
  33. Redo Buffers               3780608 bytes
  34. Database mounted.
  35. ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
  36. ORA-01110: data file 3: '/home/oracle/undotbs1.dbf'


  39. SYS@proc> alter database datafile 3 offline drop;

  41. Database altered.

  43. SYS@proc> alter database open;

  45. Database altered.

  47. SYS@proc> create undo tablespace undotbs2 datafile '/home/oracle/undotbs2.dbf' size 100m autoextend on;

  49. Tablespace created.

  51. SYS@proc> alter system set undo_tablespace=undotbs2;

  53. System altered.

  55. SYS@proc> show parameter undo_tablespace

  57. NAME                 TYPE     VALUE
  58. ------------------------------------ ----------- ------------------------------
  59. undo_tablespace          string     UNDOTBS2
  60. SYS@proc> select count(*) from tt;
  61. select count(*) from tt
  62.                      *
  63. ERROR at line 1:
  64. ORA-00376: file 3 cannot be read at this time
  65. ORA-01110: data file 3: '/home/oracle/undotbs1.dbf'


  68. SYS@proc> drop tablespace undotbs1 including contents and datafiles;
  69. drop tablespace undotbs1 including contents and datafiles
  70. *
  71. ERROR at line 1:
  72. ORA-01548: active rollback segment '_SYSSMU7_1076253125$' found, terminate dropping tablespace


  75. SYS@proc> --查詢回滾段資訊
  76.     select rownum,
  77.         sys.dba_rollback_segs.segment_name Name,
  78.         v$rollstat.extents Extents,
  79.         v$rollstat.rssize Size_in_Bytes,
  80.         v$rollstat.xacts XActs,
  81.         v$rollstat.gets Gets,
  82.         v$rollstat.waits Waits,
  83.         v$rollstat.writes Writes,
  84.         sys.dba_rollback_segs.status status
  85.    from v$rollstat, sys.dba_rollback_segs, v$rollname
  86.   where v$rollname.name(+) = sys.dba_rollback_segs.segment_name
  87.     and v$rollstat.usn(+) = v$rollname.usn
  88.   order by rownum;

  90.     ROWNUM NAME                              EXTENTS SIZE_IN_BYTES      XACTS       GETS      WAITS     WRITES STATUS
  91. ---------- ------------------------------ ---------- ------------- ---------- ---------- ---------- ---------- ----------------
  92.          1 SYSTEM                                  6        385024          0        293          0      39796 ONLINE
  93.          2 _SYSSMU17_2390902301$                   2        122880          0          2          0          0 ONLINE
  94.          3 _SYSSMU18_1669837494$                   2        122880          0          2          0          0 ONLINE
  95.          4 _SYSSMU39_929646464$                    2        122880          0          4          0        138 ONLINE
  96.          5 _SYSSMU40_3450336478$                   2        122880          0          4          0        430 ONLINE
  97.          6 _SYSSMU41_3787558531$                   2        122880          0          4          0        232 ONLINE
  98.          7 _SYSSMU42_2640506269$                   2        122880          0          4          0        214 ONLINE
  99.          8 _SYSSMU43_391677854$                    2        122880          0          4          0        158 ONLINE
  100.          9 _SYSSMU44_2493446996$                   2        122880          0          7          0        900 ONLINE
  101.         10 _SYSSMU45_2356950286$                   2        122880          0          4          0       3482 ONLINE
  102.         11 _SYSSMU46_275930757$                    2        122880          0          4          0        114 ONLINE

  104.     ROWNUM NAME                           EXTENTS    SIZE_IN_BYTES      XACTS       GETS      WAITS     WRITES STATUS
  105. ---------- ------------------------------ ---------- ------------- ---------- ---------- ---------- ---------- ----------------
  106.         12 _SYSSMU12_1668912051$                                                                               NEEDS RECOVERY
  107.         13 _SYSSMU7_1076253125$                                                                                NEEDS RECOVERY
  108.         14 _SYSSMU16_2690255195$                                                                               NEEDS RECOVERY
  109.         15 _SYSSMU11_3130810267$                                                                               NEEDS RECOVERY
  110.         16 _SYSSMU13_492329380$                                                                                NEEDS RECOVERY
  111.         17 _SYSSMU9_2756025465$                                                                                NEEDS RECOVERY
  112.         18 _SYSSMU15_3752757294$                                                                               NEEDS RECOVERY
  113.         19 _SYSSMU10_146814648$                                                                                NEEDS RECOVERY
  114.         20 _SYSSMU8_4280284895$                                                                                NEEDS RECOVERY
  115.         21 _SYSSMU14_3551377741$                                                                               NEEDS RECOVERY

  117. 21 rows selected.

  119. SYS@proc> shutdown immediate;
  120. Database closed.
  121. Database dismounted.
  122. ORACLE instance shut down.
  123. SYS@proc> create pfile from spfile;

  125. File created.


  128. [oracle@oracle dbs]$ cat initproc.ora | grep offline_rollback_segments
  129. *._offline_rollback_segments='_SYSSMU12_1668912051$','_SYSSMU7_1076253125$','_SYSSMU16_2690255195$','_SYSSMU11_3130810267$','_SYSSMU13_492329380$','_SYSSMU9_2756025465$','_SYSSMU15_3752757294$','_SYSSMU10_146814648$','_SYSSMU8_4280284895$','_SYSSMU14_3551377741$'

  131. SYS@proc> create spfile from pfile;

  133. File created.

  135. SYS@proc> startup;
  136. ORACLE instance started.

  138. Total System Global Area 521936896 bytes
  139. Fixed Size                 2254824 bytes
  140. Variable Size            306186264 bytes
  141. Database Buffers         209715200 bytes
  142. Redo Buffers               3780608 bytes
  143. Database mounted.
  144. Database opened.
  145. SYS@proc> show parameter offline_roll

  147. NAME                                 TYPE        VALUE
  148. ------------------------------------ ----------- ------------------------------
  149. _offline_rollback_segments           string     _SYSSMU12_1668912051$, _SYSSMU
  150.                                                 7_1076253125$, _SYSSMU16_26902
  151.                                                 55195$, _SYSSMU11_3130810267$,
  152.                                                  _SYSSMU13_492329380$, _SYSSMU
  153.                                                 9_2756025465$, _SYSSMU15_37527
  154.                                                 57294$, _SYSSMU10_146814648$,
  155.                                                 _SYSSMU8_4280284895$, _SYSSMU1
  156.                                                 4_3551377741$
  157. SYS@proc> select count(*) from tt;
  158. select count(*) from tt
  159.                      *
  160. ERROR at line 1:
  161. ORA-00376: file 3 cannot be read at this time
  162. ORA-01110: data file 3: '/home/oracle/undotbs1.dbf'

  164. SYS@proc> drop tablespace undotbs1 including contents and datafiles;

  166. Tablespace dropped.

  168. SYS@proc> select count(*) from tt;

  170.   COUNT(*)
  171. ----------
  172.      10000

  174. SYS@proc>
  1. SYS@proc> alter system reset "_offline_rollback_segments";

  3. System altered.

  5. SYS@proc> startup force;
  6. ORACLE instance started.

  8. Total System Global Area 521936896 bytes
  9. Fixed Size                 2254824 bytes
  10. Variable Size            306186264 bytes
  11. Database Buffers         209715200 bytes
  12. Redo Buffers               3780608 bytes
  13. Database mounted.
  14. Database opened.
  15. SYS@proc> show parameter _offline_rollback_segments
  16. SYS@proc>

查詢結果：
select * from tt;(在PLSQL Developer 12 (64 bit)工具中執行)


總結：
需要注意的是，不是每次結果都能看到有a和b結果的混合，很大機率只能看到都是b---..的情況，不過b--..都是未commit的資料，已經達到目的。
至於為什麼會這樣，這涉及到oracle例項恢復原理，讀一致性原理，ITL等等知識，這裡就不詳述了。




來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30174570/viewspace-2142281/，如需轉載，請註明出處，否則將追究法律責任。

相關文章