Database - 63. page

禁用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’ ，此时才会造成真正的数据不一致）

实验证明：
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  943718400 bytes
Fixed Size                  2025400 bytes
Variable Size             247466056 bytes
Database Buffers          692060160 bytes
Redo Buffers                2166784 bytes
Database mounted.
Database opened.
SQL> show parameter event

NAME                                 TYPE        VALUE
———————————— ———– ——————————
event                                string
SQL> conn luda/luda
Connected.
SQL> drop table t1;
create table t1 (id number,id2 number);

declare
begin
for i in 1..100000 loop
insert into t1 values(i,i+1);
end loop;
end;
/
Table dropped.

SQL>
Table created.

SQL> SQL>   2    3    4    5    6    7 

PL/SQL procedure successfully completed.

SQL> conn / as sysdba
Connected.
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  943718400 bytes
Fixed Size                  2025400 bytes
Variable Size             251660360 bytes
Database Buffers          687865856 bytes
Redo Buffers                2166784 bytes
Database mounted.
SQL> alter system set events ‘10513 trace name context forever,level 2′;

System altered.

SQL> alter database open;

Database altered.

SQL> select usn,UNDOBLOCKSDONE,UNDOBLOCKSDONE from v$fast_start_transactions;

no rows selected

SQL> show parameter event

NAME                                 TYPE        VALUE
———————————— ———– ——————————
event                                string
SQL> alter session set events’10046 trace name context forever ,level 12’;

Session altered.

SQL> select * from t1;
select * from t1
              *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from luda.t1;
SQL> alter system set events ‘10046 trace name context off’;

System altered.

在系统的10046的跟踪文件头部记录着：
“znjtepp_ora_26610.trc” 23617 lines, 2172756 characters
/oracle/admin/znjtepp/udump/znjtepp_ora_26610.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /oracle/product/10.2/db_1
System name:    AIX
Node name:      aix53
Release:        3
Version:        5
Machine:        0009AFDA4C00
Instance name: znjtepp
Redo thread mounted by this instance: 1
Oracle process number: 15
Unix process pid: 26610, image: oracle@aix53 (TNS V1-V3)

*** 2011-05-25 11:46:13.172
*** SERVICE NAME:() 2011-05-25 11:46:13.163
*** SESSION ID:(159.3) 2011-05-25 11:46:13.163
Thread 1 checkpoint: logseq 39, block 8118, scn 3255055
  cache-low rba: logseq 39, block 8119
    on-disk rba: logseq 39, block 59764, scn 3255568
  start recovery at logseq 39, block 8119, scn 0
—– Redo read statistics for thread 1 —–
Read rate (ASYNC): 25823Kb in 0.28s => 90.06 Mb/sec
Total physical reads: 26624Kb
Longest record: 20Kb, moves: 0/101746 (0%)
Change moves: 0/2 (0%), moved: 0Mb
Longest LWN: 683Kb, moves: 25/94 (26%), moved: 16Mb
Last redo scn: 0x0000.0031ad12 (3255570)
———————————————-
—– Recovery Hash Table Statistics ———
Hash table buckets = 32768
Longest hash chain = 2
Average hash chain = 1251/1246 = 1.0
Max compares per lookup = 2
Avg compares per lookup = 202468/203698 = 1.0
———————————————-
*** 2011-05-25 11:46:13.452
KCRA: start recovery claims for 1251 data blocks
*** 2011-05-25 11:46:13.830
KCRA: blocks processed = 1251/1251, claimed = 1251, eliminated = 0
*** 2011-05-25 11:46:13.831
Recovery of Online Redo Log: Thread 1 Group 2 Seq 39 Reading mem 0
—– Recovery Hash Table Statistics ———
Hash table buckets = 32768
Longest hash chain = 2
…..略

证明在扫描T1表时候还是会使用undo的段进行回滚。

关于10513的设置以及oracle的解释

查询smon进程号：
luda_dba:/home/oracle$ sqlplus ‘/as sysdba’

SQL*Plus: Release 10.2.0.1.0 – Production on Wed May 25 10:28:34 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> select pid,program from v$process where program like ‘%SMON%’;

       PID PROGRAM
———- ————————————————
         8 oracle@aix53 (SMON)

SQL> oradebug setorapid 8
Unix process pid: 22736, image: oracle@aix53 (SMON)
SQL> oradebug event 10513 trace name context forever,level 2
Statement processed.
SQL>

Oracle官方对于event 10513解释，该文件存放在
$ORACLE_HOJE/rdbms/mesg/oraus.msg文件中。

引用
10513, 00000, “turn off wrap source compression”
// *Cause:
// *Action: Set this event if you do not want source of wrapped PL/SQL
//          objects to be concatenated and stored multiple lines to a row.
// *Comment: This event should be set if you want the source of wrapped
//           PL/SQL objects to be stored one line per source row. The
//           new storage method is multiple lines per row, which
//           improves load and compile performance.