月份：2013年6月

C:\Users\dell>exp ufgov/ufgov@orcl file=d:\hddatabackup\ufgov_20130228.dmp log=d:\hddatabackup\ufgov_20130228.log buffer=40960000


Export: Release 10.2.0.1.0 - Production on 星期四 2月 28 10:13:48 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


EXP-00056:
EXP-00000: Export terminated unsuccessfully

这是一个很普通的错误，通常是由于EXP程序在执行某条命令错误所造成的，可以通过自己手动执行如下脚本解决，

@D:\Program_Files\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catmeta.sql
@D:\Program_Files\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catexp.sql

C:\Users\dell>exp ufgov/ufgov@orcl file=d:\hddatabackup\ufgov_20130228.dmp log=d:\hddatabackup\ufgov_20130228.log buffer=40960000

EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00000: Export terminated unsuccessfully

如果是测试库，可以尝试：

sqlplus /nolog

SQL> connect / as sysdba
SQL> @D:\Program_Files\oracle\product\10.2.0\db_1\RDBMS\ADMIN\catmetx.sql
SQL> @D:\Program_Files\oracle\product\10.2.0\db_1\RDBMS\ADMIN\utlrp.sql
SQL> exit

如果是生产库，先做备份，可以考虑升级，不行再尝试。

C:\Users\dell>exp ufgov/ufgov@orcl file=d:\hddatabackup\ufgov_20130228.dmp log=d:\hddatabackup\ufgov_20130228.log buffer=40960000

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的用户...
. 正在导出 pre-schema 过程对象和操作
. 正在导出用户 UFGOV 的外部函数库名
. 导出 PUBLIC 类型同义词
. 正在导出专用类型同义词
. 正在导出用户 UFGOV 的对象类型定义
即将导出 UFGOV 的对象...
. 正在导出数据库链接
. 正在导出序号
. 正在导出簇定义
. 即将导出 UFGOV 的表通过常规路径...
. . 正在导出表                ADJUST_FZYE_TEMP导出了           0 行
. . 正在导出表                ADJUST_KMYE_TEMP导出了           0 行
. . 正在导出表                       BBSRCYWLX导出了           0 行
. . 正在导出表                        BBYWLXDM导出了           0 行
. . 正在导出表                           BG_BB导出了           0 行
...............

生成sql trace可以有以下几种方式：

1、参数设置：非常传统的方法。
系统级别：
参数文件中指定: sql_trace=true
或

SQL> alter system set sql_trace=true;

注意：系统级别启用sql_trace，会产生大量trace文件，很容易耗尽磁盘空间，因此一般设置会话级别，并且及时关闭。
会话级别：

SQL> alter session set sql_trace=true;
SQL> 执行sql
SQL> alter session set sql_trace=false;

启用跟踪后，跟踪文件保存在user_dump_dest下
可以使用下面的查询来找到生成的跟踪文件

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /


TRACE_FILE_NAME
--------------------------------------------------------------------------------
/oracle/admin/RLZY/udump/rlzy_ora_721532.trc

也可以给要生成的跟踪文件指定标识符来让你更容易的找到跟文件

SQL> alter session set tracefile_identifier='jingyong';

2、使用10046事件：
10046事件级别：
Lv0 – 禁用sql_trace，等价于sql_trace=false
Lv1 – 启用标准的sql_trace功能,等价于sql_trace=true
Lv4 – Level 1 + 绑定变量值(bind values)
Lv8 – Level 1 + 等待事件跟踪(waits)
Lv12 – Level 1 + Level 4 + Level 8

全局设定：
参数文件中指定: event=”10046 trace name context forever,level 12″
或者

SQL> alter system set events '10046 trace name context forever, level 12';
SQL> alter system set events '10046 trace name context off';

注意：系统级别启用sql_trace，会产生大量trace文件，很容易耗尽磁盘空间，因此一般设置会话级别，并且及时关闭。

当前session设定：

SQL> alter session set events '10046 trace name context forever, level 12';
SQL> 执行sql
SQL> alter session set events '10046 trace name context off';

3、dbms_session包：只能跟踪当前会话，不能指定会话。
跟踪当前会话：

SQL> exec dbms_session.set_sql_trace(true);
SQL> 执行sql
SQL> exec dbms_session.set_sql_trace(false);

dbms_session.set_sql_trace相当于alter session set sql_trace，从生成的trace文件可以明确地看
alter session set sql_trace语句。
使用dbms_session.session_trace_enable过程，不仅可以看到等待事件信息还可以看到绑定变量信息，
相当于alter session set events ‘10046 trace name context forever, level 12’;语句从生成的trace文件可以确认。

SQL> exec dbms_session.session_trace_enable(waits=>true,binds=>true);
SQL> 执行sql
SQL> exec dbms_session.session_trace_enable();

4、dbms_support包：不应该使用这种方法，非官方支持。
系统默认没有安装这个包，可以手动执行$ORACLE_HOME/rdbms/admin/bmssupp.sql脚本来创建该包
跟踪当前会话：

SQL> exec dbms_support.start_trace
SQL> 执行sql
SQL> exec dbms_support.stop_trace

跟踪其他会话：等待事件+绑定变量，相当于level 12的10046事件。

SQL> select sid,serial#,username from v$session where ...;
SQL> exec dbms_support.start_trace_in_session(sid=>sid,serial=>serial#,waits=>true,binds=>true);
SQL> exec dbms_support.stop_trace_in_session(sid=>sid,serial=>serial#);

5、dbms_system包：
跟踪其他会话：
使用dbms_system.set_ev设置10046事件

SQL> select sid,serial#,username from v$session where ...;
SQL> exec dbms_system.set_ev(sid,serial#,10046,12,'');
SQL> exec dbms_system.set_ev(sid,serial#,10046,0,'');

但经过测试在10g中使用级别为8,12的跟踪并没有在跟踪文件中生产等待事件信息

6、dbms_monitor包：10g提供，功能非常强大。可在模块级别、动作级别、客户端级别、数据库级别、会话级别进行跟踪。oracle官方支持。
跟踪当前会话：

SQL> exec dbms_monitor.session_trace_enable;
SQL> 执行sql
SQL> exec dbms_monitor.session_trace_disable;

跟踪其他会话：

SQL> exec dbms_monitor.session_trace_enable(session_id=>sid,serial_num=>serial#,waits=>true,binds=>true);
SQL> exec dbms_monitor.session_trace_disable(session_id=>sid,serial_num=>serial#);

7、oradebug
这是sqlplus的工具，需要提供OSPID或者oracle PID。
跟踪当前会话：

SQL> oradebug setmypid;
Statement processed.
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> 执行sql
SQL> oradebug tracefile_name
SQL> oradebug event 10046 trace name context off;
Statement processed.

跟踪其他会话：

SQL> select spid,pid2  from v$process
  2  where addr in (select paddr from v$session where sid=(select distinct sid from v$mystat));
SPID                PID
------------ ----------
1457                 313
SQL> oradebug setospid 1457;
Statement processed.
或者
SQL> oradebug setorapid 313;
Statement processed.
SQL> oradebug unlimit;
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> oradebug tracefile_name
SQL> oradebug event 10046 trace name context off;
Statement processed.

数据库版本是10.2.0.1.0和10.2.0.4.0

SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.04.0 - Prod
SQL> alter session set events '10046 trace name context forever,level 12';

Session altered

SQL> select count(*) from obj$;

  COUNT(*)
----------
     51486

SQL>
SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_2487.trc

对当前会话使用10046级别为12级的跟踪可以看到等待事件信息

select count(*)
from
 obj$


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.04       0.06         23        133          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.04       0.06         23        133          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=133 pr=23 pw=0 time=61480 us)
  51486   INDEX FAST FULL SCAN I_OBJ1 (cr=133 pr=23 pw=0 time=1252937 us)(object id 36)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.01          0.01
  db file sequential read                         3        0.01          0.01
  db file scattered read                          7        0.00          0.00
********************************************************************************

下面来跟踪其它会话

SQL> exec dbms_system.set_ev(147,57,10046,12,'SYS');

PL/SQL procedure successfully completed

SQL> exec dbms_system.set_sql_trace_in_session(147,57,true);

PL/SQL procedure successfully completed

SQL>  exec dbms_system.set_sql_trace_in_session(147,57,false);

PL/SQL procedure successfully completed

SQL>  exec dbms_system.set_ev(147,57,10046,0,'SYS');

但是在跟踪文件中没有等待信息

select count(*)
from
 obj$


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.03       0.03          0        133          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.03       0.03          0        133          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=133 pr=0 pw=0 time=33599 us)
  51486   INDEX FAST FULL SCAN I_OBJ1 (cr=133 pr=0 pw=0 time=876016 us)(object id 36)

但是在全局启用10046级别为12级的跟踪在生成的跟文件中可以看到等待事件信息

select cols,audit$,textlength,intcols,property,flags,rowid
from
 view$ where obj#=:1


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse      317      0.06       0.05          0         19          0           0
Execute    438      0.09       0.08          0          0          0           0
Fetch      438      0.08       0.33         56       1314          0         438
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1193      0.23       0.48         56       1333          0         438

Misses in library cache during parse: 20
Misses in library cache during execute: 20
Optimizer mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  TABLE ACCESS BY INDEX ROWID VIEW$ (cr=3 pr=3 pw=0 time=20069 us)
      1   INDEX UNIQUE SCAN I_VIEW1 (cr=2 pr=2 pw=0 time=10796 us)(object id 99)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  db file sequential read                        56        0.01          0.30
  SQL*Net message to client                      32        0.00          0.00
  SQL*Net message from client                    32        0.00          0.00
********************************************************************************

而使用dbms_support来进行跟踪在生成的跟踪文件中也能看到等待事件信息

SQL> @D:\oracle\product\10.2.0\db_1\RDBMS\ADMIN\dbmssupp.sql

Package created
SQL> exec sys.dbms_support.start_trace_in_session(147,59,true,true);

PL/SQL procedure successfully completed

SQL> exec sys.dbms_support.stop_trace_in_session(147,59);

PL/SQL procedure successfully completed
 select count(*)
from
 col$


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.14       0.29        130        138          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.14       0.30        130        138          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=138 pr=130 pw=0 time=296924 us)
  56008   INDEX FAST FULL SCAN I_COL3 (cr=138 pr=130 pw=0 time=1431460 us)(object id 47)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.02          0.02
  db file sequential read                         1        0.02          0.02
  db file scattered read                         18        0.02          0.21

使用dbms_monitor来跟其他会话在生成的跟踪文件中也能看到等待事件

SQL> exec dbms_monitor.session_trace_enable(147,61,true,true);

PL/SQL procedure successfully completed

SQL> exec dbms_monitor.session_trace_disable(147,61);

PL/SQL procedure successfully completed

select count(*)
from
 ind$


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.00       0.03          2          9          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.01       0.04          2          9          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE (cr=9 pr=2 pw=0 time=37846 us)
   2345   INDEX FAST FULL SCAN I_IND1 (cr=9 pr=2 pw=0 time=107104 us)(object id 39)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2        0.01          0.01
  db file sequential read                         2        0.03          0.03



********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        5      0.00       0.00          0          0          0           0
Execute      5      0.00       0.00          0          0          0           2
Fetch        3      0.00       0.03          2          9          0           3
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       13      0.01       0.04          2          9          0           5

Misses in library cache during parse: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       8        0.00          0.00
  SQL*Net message from client                     7        4.37          4.40
  db file sequential read                         2        0.03          0.03

控制文件

ckpt的一项任务是更新数据文件头和控制文件,记录检查点信息,这些信息对于数据库的恢复和完整性校验都至关重要.下面来看一下控制文件和数据文件头都记录了哪些信息.通过以下内部命令可以转储oracle的数据文件头信息:
alter session set events ‘immediate trace name file_hdrs level 10’;
首先以immediate方式关闭数据库,在mount状态下执行该命令,研究一下此时转储的文件头信息:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              67111156 bytes
Database Buffers           96468992 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> alter session set events 'immediate trace name file_hdrs level 10';

Session altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_4606.trc

查看跟踪文件信息,选取一个文件的信息(这里选择userso1.dbf文件),这类trace文件的信息包含两个部分,一部分来自控制文件,另一部分来自数据文件:

DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:192 scn: 0x0000.0011601b 01/18/2013 06:31:30
 Stop scn: 0x0000.0011601b 01/18/2013 06:31:30
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 ......
 V10 STYLE. FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG'
Activation ID=0=0x0
Control Seq=4511=0x119f, File size=6400=0x1900
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS  rel_fn:4
Creation   at   scn: 0x0000.00002946 06/30/2005 19:10:40
Backup taken at scn: 0x0000.000d3f79 01/06/2013 09:20:39 thread:1
 reset logs count:0x2fecc5c7 scn: 0x0000.000e487c reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 prev reset logs count:0x2fecb1fc scn: 0x0000.000df5e6 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 recovered at 01/14/2013 13:30:37
 status:0x0 root dba:0x00000000 chkpt cnt: 192 ctl cnt:191
begin-hot-backup file size: 640
Checkpointed at scn:  0x0000.0011601b 01/18/2013 06:31:30
 thread:1 rba:(0x9.1a0.10)
 .....

其中”FILE HEADER”开始的信息就是来自数据文件头,之前的相关内容来自控制文件,在mount状态下将users01.dbf文件移除,重新转储数据文件头:

With the Partitioning, OLAP and Data Mining options
[oracle@jingyong ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jan 18 06:37:49 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> ! mv /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf.bak

SQL> alter session set events 'immediate trace name file_hdrs level 10';

Session altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_4620.trc

检查现在生成的跟踪文件可以看到,由于users01.dbf文件丢失,”FILE HEADER”部分信息将无法获得:

DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:192 scn: 0x0000.0011601b 01/18/2013 06:31:30
 Stop scn: 0x0000.0011601b 01/18/2013 06:31:30
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 thread:0 rba:(0x0.0.0)
 ......
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf'
*** Error 1157 in open/read file # 4 ***

此时报出的错误信息是,文件无法找到,也就是说当执行trace file_hdrs时需要读取数据文件头,获得相关信息,回过头来看一下来自控制文件部分的信息,其中包含:
Checkpoint cnt:192 scn: 0x0000.0011601b 01/18/2013 06:31:30
在”FILE HEADER”部分信息中包含了如下部分:
status:0x0 root dba:0x00000000 chkpt cnt: 192 ctl cnt:191
begin-hot-backup file size: 640
Checkpointed at scn: 0x0000.0011601b 01/18/2013 06:31:30
其中控制文件中记录的scn指最后一次成功完成的检查点scn;数据文件头中记录的checkpointed at scn指数据文件头中记录的最后一次成功完成的检查点scn;这两者在正常情况下是相等的.此外在控制文件和数据文件头都记录一个检查点计数(chkpt cnt或checkpoint cnt)而且数据文件头还记录了一个控制文件检查点计数(ctl cnt),在以上输出中ctl cnt:191比控制文件中的checkpoint cnt192要小1,这是因为当检查点更新控制文件和数据文件头上的chkpt cnt/checkpoint cnt信息时,在更新控制文件之前,可以获得当前的控制文件的clt cnt,这个信息被记入到数据文件头中,也就是ctlcnt:191,为什么要写这个ctl cnt到数据文件头了.是因为不能保证当前更新控制文件上的checkpoint cnt一定会成功(数据库可能突然crash了),记录之前成功的ctl cnt可以确保上一次的checkpoint是成功完成的,从而节了校验步骤.

数据库的启动验证
在数据库启动过程中的检验包含以下两个步骤;
第一步检查数据文件头中的checkpoint cnt是否与对应的控制文件中的checkpoint cnt一致.如果相等,则进行第二步检查.

第二步检查数据文件头的开始scn和对应的控制文件中的结束scn是否一致,如果控制文件中的结束scn等于数据文件头中的开始scn,则不需要对那个文件进行恢复.

对于每个数据文件都要完成检查后才打开数据库,同时将每个数据文件的结束的scn设置为无穷大也就是0xFFFFFFF.FFFFF

当使用alter session set events ‘immediate trace name file_hdrs level 10’来转储数据文件头信息时,oracle会转储两部分信息,一部分来自控制文件,另一部分来自数据文件,在数据库启动过程中,这两部分信息要用来进行启动验证.通过以下过程来进一步深入探讨一下这部分内容.

首先来看一下来自mount状态控制文件部分转储;

DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:192 scn: 0x0000.0011601b 01/18/2013 06:31:30
 Stop scn: 0x0000.0011601b 01/18/2013 06:31:30
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 thread:0 rba:(0x0.0.0)
这部分中包含的重要信息有检查点计数(ckeckpoint cnt:192),检查点scn
(scn: 0x0000.0011601b 01/18/2013 06:31:30)和数据文件Stop scn(
top scn: 0x0000.0011601b 01/18/2013 06:31:30).

接下来再来看来自数据文件头的信息:

V10 STYLE. FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG'
Activation ID=0=0x0
Control Seq=4511=0x119f, File size=6400=0x1900
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS  rel_fn:4
Creation   at   scn: 0x0000.00002946 06/30/2005 19:10:40
Backup taken at scn: 0x0000.000d3f79 01/06/2013 09:20:39 thread:1
 reset logs count:0x2fecc5c7 scn: 0x0000.000e487c reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 prev reset logs count:0x2fecb1fc scn: 0x0000.000df5e6 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 recovered at 01/14/2013 13:30:37
 status:0x0 root dba:0x00000000 chkpt cnt: 192 ctl cnt:191
begin-hot-backup file size: 640
Checkpointed at scn:  0x0000.0011601b 01/18/2013 06:31:30
 thread:1 rba:(0x9.1a0.10)

这部分中包含的重要信息有检查点scn(Checkpointed at scn: 0x0000.0011601b 01/18/2013 06:31:30)
和检查点计数库(chkpt cnt: 192 ctl cnt:191),这两者都和控制文件中所记录的一致.如果这两者一致.数据库启动时就能通过验证,启动数据库.

那么如果不一致,oracle则会请求进行恢复;以下是从崩溃中进行恢复users01.dbf文件.首先第一部分从控制文件中获得的信息是相同的:
先复制users01.dbf

[oracle@jingyong udump]$ cp /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf.bak

使用shutdown abort来模拟崩溃故障

SQL> shutdown abort;
ORACLE instance shut down.

再来删除users01.dbf文件

[oracle@jingyong udump]$ mv /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf.del

再从备份的users01.dbf.bak文件中还原users01.dbf文件

[oracle@jingyong udump]$ mv /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf.bak /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf

再启动到mount状态下转储数据文件头信息

SQL> startup mount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              67111156 bytes
Database Buffers           96468992 bytes
Redo Buffers                2973696 bytes
Database mounted.

再业转储数据文件头信息

Database mounted.
SQL> alter session set events 'immediate trace name file_hdrs level 10';

Session altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_4923.trc


DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:201 scn: 0x0000.00120188 01/18/2013 07:15:10
 Stop scn: 0x0000.00120188 01/18/2013 07:15:10
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 thread:0 rba:(0x0.0.0)

得到的控制文件中记录检查点计数(Checkpoint cnt:201),检查点scn

(scn: 0x0000.00120188 01/18/2013 07:15:10),和数据文件Stop scn
(Stop scn: 0x0000.00120188 01/18/2013 07:15:10)

V10 STYLE. FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG'
Activation ID=0=0x0
Control Seq=4545=0x11c1, File size=6400=0x1900
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS  rel_fn:4
Creation   at   scn: 0x0000.00002946 06/30/2005 19:10:40
Backup taken at scn: 0x0000.000d3f79 01/06/2013 09:20:39 thread:1
 reset logs count:0x2fecc5c7 scn: 0x0000.000e487c reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 prev reset logs count:0x2fecb1fc scn: 0x0000.000df5e6 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 recovered at 01/18/2013 07:12:48
 status:0x4 root dba:0x00000000 chkpt cnt: 199 ctl cnt:198
begin-hot-backup file size: 640
Checkpointed at scn:  0x0000.00120027 01/18/2013 07:12:49
 thread:1 rba:(0xb.2.10)

而从文件头中获得的备份文件信息则是:检查点是Checkpointed at scn: 0x0000.00120027 01/18/2013 07:12:49
检查点计数为:chkpt cnt: 199 ctl cnt:198

数据文件头中的检查计数为chkpt cnt: 199小于控制文件中的记录的Checkpoint cnt:201oracle可以判断文件是从备份中恢复的,或者文件故障,需要进行介质恢复.如果此时试图打开数据库,则oracle提示文件需要介质恢复.而且控制文件中的检查点scn: 0x0000.00120188与数据文件头中的Checkpointed at scn: 0x0000.00120027也不相同

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4:
'/u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf'

执行介质恢复

SQL> recover datafile 4;
Media recovery complete.

再来对数据文件头信息进行转储,来看一下恢复完成之后,控制文件和数据文件头的变化.

SQL> alter session set events 'immediate trace name file_hdrs level 10';

Session altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_4940.trc

首先看控制文件的变化:

DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:202 scn: 0x0000.00120188 01/18/2013 07:15:10
 Stop scn: 0x0000.00120187 01/18/2013 07:15:10
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 thread:0 rba:(0x0.0.0)

检查点计数据Checkpoint cnt:202,执行了恢复之后,检查点计数较前增加了1,此时检查点scn是scn: 0x0000.00120188 01/18/2013 07:15:10,数据文件的Stop scn为
Stop scn: 0x0000.00120187 01/18/2013 07:15:10,说明数据文件stop scn和数据文件进行了同步.

以下是数据文件头信息:

V10 STYLE. FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG'
Activation ID=0=0x0
Control Seq=4554=0x11ca, File size=6400=0x1900
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS  rel_fn:4
Creation   at   scn: 0x0000.00002946 06/30/2005 19:10:40
Backup taken at scn: 0x0000.000d3f79 01/06/2013 09:20:39 thread:1
 reset logs count:0x2fecc5c7 scn: 0x0000.000e487c reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 prev reset logs count:0x2fecb1fc scn: 0x0000.000df5e6 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 recovered at 01/18/2013 07:24:36
 status:0x0 root dba:0x00000000 chkpt cnt: 202 ctl cnt:201
begin-hot-backup file size: 640
Checkpointed at scn:  0x0000.00120187 01/18/2013 07:15:10
 thread:1 rba:(0xb.15a.10)

此时数据文件头信息显示检查点(Checkpointed at scn: 0x0000.00120187 01/18/2013 07:15:10)
和控制文件中记录的Stop scn(Stop scn: 0x0000.00120187 01/18/2013 07:15:10)一致,数据库启动可以顺利进行.检查点计数为(chkpt cnt: 202 ctl cnt:201)

打开数据库,看一看open阶段的变化:

SQL> alter database open;

Database altered.


SQL> alter session set events 'immediate trace name file_hdrs level 10';

Session altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_4968.trc

此时数据库恢复正常运行,控制文件信息如下:

DATA FILE #4:
  (name #5) /u01/app/oracle/product/10.2.0/oradata/jingyong/users01.dbf
creation size=0 block size=8192 status=0xe head=5 tail=5 dup=1
 tablespace 4, index=4 krfil=4 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:203 scn: 0x0000.00120189 01/18/2013 07:28:46
 Stop scn: 0xffff.ffffffff 01/18/2013 07:15:10
 Creation Checkpointed at scn:  0x0000.00002946 06/30/2005 19:10:40
 thread:0 rba:(0x0.0.0)

此时Stop scn被置为无穷大(Stop scn: 0xffff.ffffffff)
数据文件头信息如下,其中检查点信息和控制文件中记录的checkpoint信息一致:

V10 STYLE. FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG'
Activation ID=0=0x0
Control Seq=4557=0x11cd, File size=6400=0x1900
File Number=4, Blksiz=8192, File Type=3 DATA
Tablespace #4 - USERS  rel_fn:4
Creation   at   scn: 0x0000.00002946 06/30/2005 19:10:40
Backup taken at scn: 0x0000.000d3f79 01/06/2013 09:20:39 thread:1
 reset logs count:0x2fecc5c7 scn: 0x0000.000e487c reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 prev reset logs count:0x2fecb1fc scn: 0x0000.000df5e6 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 recovered at 01/18/2013 07:24:36
 status:0x4 root dba:0x00000000 chkpt cnt: 203 ctl cnt:202
begin-hot-backup file size: 640
Checkpointed at scn:  0x0000.00120189 01/18/2013 07:28:46
 thread:1 rba:(0xb.15a.10)

检查点

检查点只是一个数据库事件,它存在的根本意义在于减少崩溃恢复(crash recovery)时间.检查点事件由ckpt后台进程触发,当检查点发生时,ckpt进程会负责通知dbwr进程将脏数据(dirty buffer)写出到数据文件上,ckpt进程的另外一个职责是负责更新数据文件头及控制文件上的检查点信息.

检查点(checkpoint)的工作原理
在oracle数据库中,当进行数据修改时,需要首先将数据读入内存中(buffer cache),修改数据的同时,oracle会记录重做(redo)信息用于恢复.因为有了重做信息的存在,oracle不需要在事务提交时(commit)立即将变化的数据写回磁盘(立即写的效率会很低),重做的存在也正是为了在数据库崩溃之后,数据可以恢复.

常见的情况,数据库可能因为断电而crash,那么内存中修改过的,尚没有写入数据文件的数据将会丢失.在下一次数据库启动之后,oracle可以通过重做(redo)日志进行事务重演(也就是进行前滚),将数据库恢复到崩溃之前的状态,然后数据库可以打开提供使用之后oracle可以将没有提交的事务进行回滚.

检查点的存在就是为了缩短这个恢复时间.当检查点发生时(此时的scn被称为checkpoint scn). oracle会通知dbwr进程,把修改过的数据,也就是此checkpoint scn之前的脏数据(dirty data)从buffer cache写入磁盘,当写入完成之后,ckpt进程则会相应更新控制文件和数据文件头,记录检查点信息,标识变更.

checkpoint scn可以从数据库中查询得到:

SQL> select file#,checkpoint_change#,to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time from v$datafile;

     FILE# CHECKPOINT_CHANGE# CHECKPOINT_TIME
---------- ------------------ -------------------
         1            1131856 2013-01-18 01:33:36
         2            1131856 2013-01-18 01:33:36
         3            1131856 2013-01-18 01:33:36
         4            1131856 2013-01-18 01:33:36
         5            1131856 2013-01-18 01:33:36
         6            1131856 2013-01-18 01:33:36

6 rows selected.

SQL> select dbid,checkpoint_change# from v$database;

      DBID CHECKPOINT_CHANGE#
---------- ------------------
3172629284            1131856

在检查点完成之后,此检查点之前修改过的数据都已经写回磁盘,重做日志文件中的相应重做记录对于崩溃/实例恢复不再有用.

如果检查点的频率高,那么恢复时间需要应用的重做日志就相对得少,恢复时间就可以缩短,如果oracle可以在性能允许的情况下,使得检查点的scn接宾redo的最新变更,那么,使得oracle可以最大化地减少恢复时间.

常规检查点与增量检查点
脏缓冲列表(Dirty List).
当数据在buffer cache中被修改之后,Dirty Buffer会被转移到dirty list,以便将来
执行的检查点可以将这些修改过的buffer写出到数据文件上.

但是注意,由于dirty list上的buffer并没有顺序,有的buffer反复被修改,在链表上的位置就可能发生变化,当检查点发生时,oracle需要将脏缓冲列表上的数据全部写出到数据文件.为了区分,在oracle8之前,oracle实施的这类检查点通常被称为常规检查点(Conventional checkpoint),由于检查点时需要写出全部的脏数据,所以也被称为完全检查点(complete checkpoint),常规检查点按特定的条件触(log_checkpoint_interval,
log_checkpoint_timeout参数设置及log switch等条件触发),触发时会同时更新数据文件头以及控制文件记录检查点信息.

从oracle8开始,oracle引入了增量检查点(Incremental checkpoint)的概念.和以前的
版本相比,在新版本中,主要的变化是引入了检查点队列(checkpoint queue ckptq)机制.在数据库内部,每一个脏数据块都会被记录到检查点队列中,按照LRBA(Low RBA,第一次对此数据块修改所对应的redo byte address)的顺序来排列,如果一个数据块进行过多次修改,该数据块在检查点队列上的顺序并不会发生变化(相对LRBA,
后面修改的RBA被称为HRBA)

当执行增量检查点时,dbwr从检查点队列按照Low RBA的顺序写出,此时先修改的数据就可以被按顺序优先写出,实例检查点因此可以不断增进,同时,ckpt进程也阶段性地使用非常轻量级的控制文件更新协议,将当前的最低的RBA写控制文件.为了减少频繁增量检查点的性能影响,ckpt在进行轻量级更新时,并不会改写控制文件中数据文件
的检查点信息以及数据文件头信息.而只是记录控制文件检查点scn(controlfile checkpoint at scn)并且根据增量检查点的写出增进RBA信息.

通过增量检查点,数据库可以将以前的全量写出变更为增量渐进写出,从而可以极大工减少对于数据库性能的影响;而检查点队列则进一步地将RBA和检查点关联起来,从而可以通过检查点来确定恢复的起点.

检查点队列在数据库内部通过latch保护:

select name,gets,misses from v$latch where name='checkpoint queue latch';

SQL> select name,gets,misses from v$latch where name='checkpoint queue latch';

NAME                                                     GETS     MISSES
-------------------------------------------------- ---------- ----------
checkpoint queue latch                                  86382          0

checkpoint queue latch存在多个子latch,可以通过v$latch_children视图查询:

select name,gets,misses from v$latch_children where name='checkpoint queue latch';

SQL> select name,gets,misses from v$latch_children where name='checkpoint queue latch';

NAME                                                     GETS     MISSES
-------------------------------------------------- ---------- ----------
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                  11374          0
checkpoint queue latch                                  11724          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0
checkpoint queue latch                                   4550          0

16 rows selected.

除了检查点队列(ckptq)之外,数据库中还存在另外一个队列和检查点相关,这就是文件检查点队列(file queue),通常缩写为fileq,文件检查点队列的引入提高了表空间检查点(tablespace checkpoint)的性能.每个dirty buffer同时链接到这两个队列,ckptq包含实例所有需要执行检查点的buffer,fileq包含属于特定文件需要执行检查点的buffer,每个文件都包含一个文件队列,在执行表空间检查点请求时需要使用fileq,通常当对表空间执行offline等操作时会触发表空间检查点.

在buffer cache中,每个buffer的header上都存在ckptq以及fileq队列信息,通过如下命令可以转储buffer cache信息(注意应发仅在测试环境中尝试);

alter session set events 'immediate trace name buffers level 10';

SQL> alter session set events 'immediate trace name buffers level 10';

Session altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_4239.trc

以下bh信息来自oracle10g

    BH (0x24ff61dc) file#: 3 rdba: 0x00c00a6c (3/2668) class: 1 ba: 0x24eee000
      set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 46
      dbwrid: 0 obj: 8780 objn: 8780 tsn: 2 afn: 3
      hash: [29115d3c,29115d3c] lru: [24ff62e0,24ff60d0]
      lru-flags:
      ckptq: [24ff5184,29150a60] fileq: [24ff518c,29150a9c] objq: [27b37888,27b37888]
      st: XCURRENT md: NULL tch: 35
      flags: buffer_dirty gotten_in_current_mode block_written_once
              redo_since_read
      LRBA: [0x5.2e7.0] HSCN: [0x0.11546d] HSUB: [1]
      buffer tsn: 2 rdba: 0x00c00a6c (3/2668)
      scn: 0x0000.0011546d seq: 0x01 flg: 0x02 tail: 0x546d0601
      frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

注意信息中的ckptq和fileq,这就是检查点队列和文件队列,每个队列后面记录了两个地址信息,分别是前一块以及下一块的地址,通过这个信息ckptq和fileq构成了双向链表.注意仅仅只有dirty buffer才会包含ckptq信息,否则为null,信息类似如下

    BH (0x24be637c) file#: 1 rdba: 0x004096b0 (1/38576) class: 1 ba: 0x2480a000
      set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 46
      dbwrid: 0 obj: 18 objn: 18 tsn: 0 afn: 1
      hash: [290c0878,290c0878] lru: [24be6320,24be6480]
      lru-flags: moved_to_tail
      ckptq: [NULL] fileq: [NULL] objq: [24be6374,24be64d4]
      st: XCURRENT md: NULL tch: 3
      flags: only_sequential_access
      LRBA: [0x0.0.0] HSCN: [0xffff.ffffffff] HSUB: [65535]
      buffer tsn: 0 rdba: 0x004096b0 (1/38576)
      scn: 0x0000.00030101 seq: 0x01 flg: 0x04 tail: 0x01010601
      frmt: 0x02 chkval: 0x8c8d type: 0x06=trans data

[oracle@jingyong udump]$ grep ckptq jingyong_ora_4239.trc |grep -v NULL
      ckptq: [26c13b04,233f6364] fileq: [237e9e1c,237e9d6c] objq: [237e9de4,237ea0a4]
      ckptq: [233f6414,237e6be4] fileq: [233f641c,237e6bec] objq: [237e6c64,233f6494]
      ckptq: [237e9aa4,237e9cb4] fileq: [237e9aac,237e9cbc] objq: [237e9d34,27b8afa8]
      ckptq: [237e6c94,233f6154] fileq: [237e6c9c,233f615c] objq: [233f61d4,237e6d14]
      ckptq: [237e9944,237ea0d4] fileq: [237e994c,237ea0dc] objq: [237ea154,237e99c4]
      ckptq: [29150a60,237e9944] fileq: [29150a74,237e994c] objq: [27b1cc58,27b1cc58]
      ckptq: [26c13b04,26c18194] fileq: [26c13b0c,26c1819c] objq: [26c18214,26c13b84]
      ckptq: [26c12924,291529c8] fileq: [26c1292c,29152a40] objq: [27b1d0b8,26c129a4]
      ckptq: [26c18034,26c12874] fileq: [26c1803c,26c1287c] objq: [26c128f4,26c180b4]
      ckptq: [291529c8,26c12be4] fileq: [29152a40,26c12bec] objq: [26c12c64,27b1d0b8]
      ckptq: [233f6204,26c18034] fileq: [233f620c,26c1803c] objq: [26c180b4,233f6284]
      ckptq: [26c12b34,233f6204] fileq: [26c12b3c,233f620c] objq: [233f6284,26c12bb4]
      ckptq: [26c12be4,26c127c4] fileq: [26c12bec,26c127cc] objq: [26c12844,26c12c64]
      ckptq: [26c12874,233f6364] fileq: [26c1287c,233f636c] objq: [233f63e4,26c128f4]
      ckptq: [26c127c4,233f6414] fileq: [26c127cc,233f641c] objq: [233f6494,26c12844]
      ckptq: [29150a60,24ff50d4] fileq: [29150a9c,24ff50dc] objq: [27b37f18,27b37f18]
      ckptq: [24ff5184,29150a60] fileq: [24ff518c,29150a9c] objq: [27b37888,27b37888]
      ckptq: [233f6364,26c12924] fileq: [233f636c,26c1292c] objq: [26c129a4,233f63e4]

在sga中存在一块内存区域用于记录这个检查点队列

select name,bytes from v$sgastat where upper(name) like '%CHECKPOINT%';

SQL> select name,bytes from v$sgastat where upper(name) like '%CHECKPOINT%';

NAME                            BYTES
-------------------------- ----------
Checkpoint queue               128320

从oracle10g开始,数据库中额外增加了对象检查点队列(object queue,objq)用于记录对象检查点信息:

    BH (0x24ff61dc) file#: 3 rdba: 0x00c00a6c (3/2668) class: 1 ba: 0x24eee000
      set: 3 blksize: 8192 bsi: 0 set-flg: 2 pwbcnt: 46
      dbwrid: 0 obj: 8780 objn: 8780 tsn: 2 afn: 3
      hash: [29115d3c,29115d3c] lru: [24ff62e0,24ff60d0]
      lru-flags:
      ckptq: [24ff5184,29150a60] fileq: [24ff518c,29150a9c] objq: [27b37888,27b37888]
      st: XCURRENT md: NULL tch: 35
      flags: buffer_dirty gotten_in_current_mode block_written_once
              redo_since_read
      LRBA: [0x5.2e7.0] HSCN: [0x0.11546d] HSUB: [1]
      buffer tsn: 2 rdba: 0x00c00a6c (3/2668)
      scn: 0x0000.0011546d seq: 0x01 flg: 0x02 tail: 0x546d0601
      frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

共享池中分配了相关内存用于OBJECT QUEUE:

select * from v$sgastat where name like 'object queue%';
SQL> select * from v$sgastat where name like 'object queue%';

POOL         NAME                            BYTES
------------ -------------------------- ----------
shared pool  object queue hash table d        3040
shared pool  object queue hash buckets       69632
shared pool  object queue                    16352

下面来看一下控制文件以及增量检查点的协同工作,以下输出来自oracle10g，两次level 8级控制文件的转储.
第一部分重要信息是控制文件的seq号,控制文件随着数据库的变化而增进版本.

[oracle@jingyong udump]$ diff jingyong_ora_4445.trc jingyong_ora_4463.trc
1c1
< /u01/app/oracle/admin/jingyong/udump/jingyong_ora_4445.trc
---
> /u01/app/oracle/admin/jingyong/udump/jingyong_ora_4463.trc
13c13
< Unix process pid: 4445, image: oracle@jingyong (TNS V1-V3)
---
> Unix process pid: 4463, image: oracle@jingyong (TNS V1-V3)
15,18c15,18
< *** 2013-01-18 05:40:06.832
< *** SERVICE NAME:(SYS$USERS) 2013-01-18 05:40:06.797
< *** SESSION ID:(159.15) 2013-01-18 05:40:06.797
< DUMP OF CONTROL FILES, Seq # 4498 = 0x1192
---
> *** 2013-01-18 05:44:49.284
> *** SERVICE NAME:(SYS$USERS) 2013-01-18 05:44:49.258
> *** SESSION ID:(159.21) 2013-01-18 05:44:49.258
> DUMP OF CONTROL FILES, Seq # 4499 = 0x1193
23c23
< Control Seq=4498=0x1192, File size=450=0x1c2
---
>       Control Seq=4499=0x1193, File size=450=0x1c2
44c44
< Database checkpoint: Thread=1 scn: 0x0000.00114550
---
>  Database checkpoint: Thread=1 scn: 0x0000.00115af8

接下来是控制文件检查点scn,增量检查点不断增进的内容之一:
66c66
< Controlfile Checkpointed at scn:  0x0000.001145c1 01/18/2013 05:38:56
---
>  Controlfile Checkpointed at scn:  0x0000.00115af8 01/18/2013 05:44:37
96,97c96,97

检查点记录之后是RBA信息,检查点和redo相关联在这里实现,通过以下信息可以注意到,通过增量检查点之后,而low cache rba从0x5.307.0增进到0x5.412.0,low cache rba是下一次恢复的起点,而on disk rba则是指已经写入磁盘(redo log file)的rba地址.
这就是前滚恢复能够到达的终点.增量检查点的作用由此体现:

< low cache rba:(0x5.307.0) on disk rba:(0x5.407.0)
< on disk scn: 0x0000.00115a04 01/18/2013 05:33:13
---
> low cache rba:(0x5.412.0) on disk rba:(0x5.442.0)
> on disk scn: 0x0000.00115af9 01/18/2013 05:44:38
99c99

最后一部分是heartbeat心跳信息,每3秒更新一次用于验证实例的存活性：

< heartbeat: 805047415 mount id: 3142683107
---
> heartbeat: 805047509 mount id: 3142683107

通过以上分析可以清晰地看到增量检查点的实施过程,因为增量检查点可以连续进行,所以检查点rba可以比常规点更接近数据库的最后状态,从而在数据库的实例恢复中可以极大地减少恢复时间.而且,通过增量检查点,dbwr可以持续进行写出,从而避免了常规检查点出发的峰值,写入对于I/O的过度征用.

显而易见的是,增量检查点明显优于常规的完全检查点,所以在引入检查点队列之后,数据库正常情况下执行的都是增量检查点,从oracle8i开始,完全检查点仅仅在以下两种情况下出现:
alter system checkpoint;
shutdown(除了abort方式外)
log switch事件同样是触发的增量检查点,但是在log switch触发的检查点会促使数据文件头与控制文件信息的同步

log_checkpoints_to_alert参数
在数据库中,可以设置初始化参数log_checkpoints_to_alert为true,则数据库会将检查点的执行情况记入警告日志文件中,这个参数的初始值为false:

SQL> show parameter checkpoints_to

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_checkpoints_to_alert             boolean     FALSE
SQL> alter system set log_checkpoints_to_alert=true;

System altered.

当数据库执行各类检查点进,日志文件中会记录详细信息,以下是来自oracle 10g的警告日志文件中的信息摘录.注意以下信息中,共发生了两次检查点,触发条件都是log switch,在日志中,注意rba信息和检查点scn同时出现,这就是检查点队列的作用,log switch检查点的特别之处在于,需要同时在控制文件和数据文件头上标记检查点进度

[oracle@jingyong bdump]$ tail -20 alert_jingyong.log
Fri Jan 18 06:12:00 2013
Beginning log switch checkpoint up to RBA [0x8.2.10], SCN: 1138121
Thread 1 advanced to log sequence 8
  Current log# 2 seq# 8 mem# 0: /u01/app/oracle/product/10.2.0/oradata/jingyong/redo02.log
Fri Jan 18 06:12:21 2013
Beginning log switch checkpoint up to RBA [0x9.2.10], SCN: 1138140
Thread 1 advanced to log sequence 9
  Current log# 3 seq# 9 mem# 0: /u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log
Fri Jan 18 06:17:12 2013
Completed checkpoint up to RBA [0x8.2.10], SCN: 1138121
Fri Jan 18 06:17:30 2013
Completed checkpoint up to RBA [0x9.2.10], SCN: 1138140

从以上信息还可以观察到,检查点的触发和检查点完成具有一定的时间间隔,这进一步说明,检查点仅仅是一个数据库事件,发生检查点时ckpt进程负责通知dbwr执行写出,但是检查点不会等待写出完成,它会在下一次触发时写出上一次成功完成的检查点信息.

在警告日志文件中,可能还会看到Incremental checkpoint的信息,这些信息和检查点的另外一个触发条件有关.为了保证检查点不会滞后整个日志文件,oracle限制最长的检查点跨度不超过最小日志大小的90%.所以数据库在运行过程中会根据log tail进行计算,主动触发增量检查点.

scn的定义

scn(system change number)就是通常所说的系统改变号,是数据库中非常重要的一个数据结构,
用以标识数据库在某个确切时刻提交的版本.在事务提交时,它被赋予一个唯一的标示事务的scn.
scn同时被作为oracle数据库的内部时钟机制,可以被看作逻辑时钟,每一个数据库都有一个全局的
scn生产器.

作为数据库内部的逻辑时钟,数据库事务依照scn而排序,oracle也依据scn来实现一致性读(
read consistency)等重要数据库功能,另外对于分布式事务(distributed transactions),
scn也极为重要,scn在数据库中是唯一的,并随时间而增加,但是scn可能并不连续,除非重建数据库,
要不然scn的值永远都不会被重置为0.

scn来维护数据库的一致性,并通过scn实施oracle至关重要的恢复机制.

scn在数据库中是无处不在的,常见的事务表,控制文件,数据文件头,日志文件,数据块头等都记录
有scn值.冠以不同前缀,scn也有了不同的名称,比如检查点scn(checkpoint scn),resetlogs scn等.
scn由两部分组成,高位scn wrap由2 bytes记录,低位scn base由4 bytes记录.

scn的获取方式
可以通过如下几种方式获得数据库的当前或近似scn.
(1)从oracle9i开始
可以通过使用dbms_flashback.get_system_change_number来获得:

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1131096

(2)oracle9i之前
可以通过查询x$ktuxe([K]ernel [T]ransaction [U]ndo Transa[x]tion [E]ntry (table))获得系统
最接近当前值的scn

select max(ktuxescnw*power(2,32)+ktuxescnb) scn_now from x$ktuxe;

SQL> select max(ktuxescnw*power(2,32)+ktuxescnb) scn_now from x$ktuxe;

   SCN_NOW
----------
   1131134

(3)从oracle10g开始
在v$database视图中增加了current_scn字段,通过查询该字段可以获得数据库的当前scn值;

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    1131155

(4)从内存中取得scn信息
通过oradebug工具可以直接读取内存中用于记录scn的内存变量;

SQL> oradebug setmypid
Statement processed.
SQL> oradebug DUMPvar SGA kcsgscn_
kcslf kcsgscn_ [20009104, 20009124) = 00000000 001142B5 000002AE 00000000 00000000
00000000 00000000 20008F10
SQL> select to_number('1142B5','xxxxxxxxx') scn from dual;

       SCN
----------
   1131189

scn的进一步说明
系统当前scn并不是在任何的数据库操作发生时都会改变,scn通常在事务提交或回滚时改变,
在控制文件,数据文件头,数据块,日志文件头,日志文件change vector中都有scn,但其作用
各不相同.

(1)数据文件头中包含了该数据文件的checkpoint scn,表示该数据文件最近一次执行检查点
操作时的scn.
从控制文件的dump文件中,可以得到以下内容:

***************************************************************************
DATA FILE RECORDS
***************************************************************************
 (size = 428, compat size = 428, section max = 100, section in-use = 6,
  last-recid= 45, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 11, numrecs = 100)
DATA FILE #1:
  (name #8) /u01/app/oracle/product/10.2.0/oradata/jingyong/system01.dbf
creation size=0 block size=8192 status=0xe head=8 tail=8 dup=1
 tablespace 0, index=1 krfil=1 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:177 scn: 0x0000.00113c5a 01/18/2013 00:35:03
 Stop scn: 0xffff.ffffffff 01/18/2013 00:34:20
 Creation Checkpointed at scn:  0x0000.00000009 06/30/2005 19:10:11

对于每一个数据文件都包含了一个这样的条目,记录该文件的检查点scn的值以及检查点发生的
时间,这里的checkpoint scn,stop scn以及checkpoint cnt都是非常重要的数据结构.

同样可以通过命令转储数据文件头,观察其具体信息及检查点记录等;

DATA FILE #1:
  (name #8) /u01/app/oracle/product/10.2.0/oradata/jingyong/system01.dbf
creation size=0 block size=8192 status=0xe head=8 tail=8 dup=1
 tablespace 0, index=1 krfil=1 prev_file=0
 unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00
 Checkpoint cnt:177 scn: 0x0000.00113c5a 01/18/2013 00:35:03
 Stop scn: 0xffff.ffffffff 01/18/2013 00:34:20
 Creation Checkpointed at scn:  0x0000.00000009 06/30/2005 19:10:11
 thread:0 rba:(0x0.0.0)
 ...
 Offline scn: 0x0000.000e487b prev_range: 0
 Online Checkpointed at scn:  0x0000.000e487c 01/07/2013 02:11:19
 thread:1 rba:(0x1.2.0)
 .....
 Hot Backup end marker scn: 0x0000.00000000
 aux_file is NOT DEFINED
 V10 STYLE. FILE HEADER:
  Compatibility Vsn = 169869568=0xa200100
  Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG'
  Activation ID=0=0x0
  Control Seq=4491=0x118b, File size=76800=0x12c00
  File Number=1, Blksiz=8192, File Type=3 DATA
Tablespace #0 - SYSTEM  rel_fn:1
Creation   at   scn: 0x0000.00000009 06/30/2005 19:10:11
Backup taken at scn: 0x0000.001128c1 01/17/2013 04:06:35 thread:1
 reset logs count:0x2fecc5c7 scn: 0x0000.000e487c reset logs terminal rcv data:0x0 scn: 0x0000.00000000
 prev reset logs count:0x2fecb1fc scn: 0x0000.000df5e6 prev reset logs terminal rcv data:0x0
scn: 0x0000.00000000
 recovered at 01/14/2013 13:30:37
 status:0x2004 root dba:0x00400179 chkpt cnt: 177 ctl cnt:176
begin-hot-backup file size: 76800
Checkpointed at scn:  0x0000.00113c5a 01/18/2013 00:35:03
 thread:1 rba:(0x4.2feb.10)
...
Backup Checkpointed at scn:  0x0000.00112909 01/17/2013 04:09:44
 thread:1 rba:(0x4.2a47.10)
....
External cache id: 0x0 0x0 0x0 0x0
Absolute fuzzy scn: 0x0000.00000000
Recovery fuzzy scn: 0x0000.00000000 01/01/1988 00:00:00
Terminal Recovery Stamp  01/01/1988 00:00:00
Platform. Information:   Creation Platform. ID: 10
Current Platform. ID: 10  Last Platform. ID: 10

在以上输出中,file header部份这前信息来自控制文件,之后信息来自数据文件头,在数据库的启动过程中,
需要依赖两部分信息进行比对判断,从而确保数据库的一致性和判断是否需要进行恢复.

(2)日志文件头中包含了Low scn和next scn
这两个scn标示该日志文件包含有介于low scn到next scn的重做信息,对于current的日志文件
(当前正在被使用的redo logfile),其最终scn不可知,所以next scn被设置为无穷大,也就是ffffffff

看一下日志文件的情况;

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
         1          1          4   52428800          1 NO       CURRENT                1103335 2013-1-14 1
         2          1          2   52428800          1 YES      INACTIVE                981195 2013-1-7 20
         3          1          3   52428800          1 YES      INACTIVE               1014044 2013-1-7 20

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
                 1131849

SQL> alter system switch logfile;

System altered

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- -----------
         1          1          4   52428800          1 NO       ACTIVE                 1103335 2013-1-14 1
         2          1          5   52428800          1 NO       CURRENT                1131856 2013-1-18 1
         3          1          3   52428800          1 YES      INACTIVE               1014044 2013-1-7 20

可以看到,scn 1131849显然位于log group#为1的日志文件中,该日志文件包含了scn自1103335到1131856的redo信息.
oracle在进行恢复时就需要根据低scn和高scn来确定需要的恢复信息位于哪一个日志或归档日志文件中.

通过控制文件转储,可以在控制文件中找到关于日志文件的信息:

SQL> alter session set events 'immediate trace name controlf level 8';

Session altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_3310.trc

***************************************************************************
LOG FILE RECORDS
***************************************************************************
 (size = 72, compat size = 72, section max = 16, section in-use = 3,
  last-recid= 9, old-recno = 0, last-recno = 0)
 (extent = 1, blkno = 10, numrecs = 16)
LOG FILE #1:
  (name #3) /u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log
 Thread 1 redo log links: forward: 2 backward: 0
 siz: 0x19000 seq: 0x00000004 hws: 0x24 bsz: 512 nab: 0x3313 flg: 0x0 dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000f791c
 Low scn: 0x0000.0010d5e7 01/14/2013 13:30:38
 Next scn: 0x0000.00114550 01/18/2013 01:33:36
LOG FILE #2:
  (name #2) /u01/app/oracle/product/10.2.0/oradata/jingyong/redo02.log
 Thread 1 redo log links: forward: 3 backward: 1
 siz: 0x19000 seq: 0x00000005 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.0010d5e7
 Low scn: 0x0000.00114550 01/18/2013 01:33:36
 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
LOG FILE #3:
  (name #1) /u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log
 Thread 1 redo log links: forward: 0 backward: 2
 siz: 0x19000 seq: 0x00000003 hws: 0x1b bsz: 512 nab: 0x819c flg: 0x1 dup: 1
 Archive links: fwrd: 0 back: 0 Prev scn: 0x0000.000ef8cb
 Low scn: 0x0000.000f791c 01/07/2013 20:08:55
 Next scn: 0x0000.0010d5e7 01/14/2013 13:30:38

从以上信息可以注意到,log file#2是当前的日志文件,该文件拥有的next scn为无穷大.
同样可以直接dump日志文件的方式来进行转储:

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER
---------- ------- ------- ----------------------------------------------------------
         3 STALE   ONLINE  /u01/app/oracle/product/10.2.0/oradata/jingyong/redo03.log
         2         ONLINE  /u01/app/oracle/product/10.2.0/oradata/jingyong/redo02.log
         1         ONLINE  /u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log

SQL> alter system dump logfile '/u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log';

System altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from v$mystat m,
  6  v$session s,v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from v$thread  t,v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_3318.trc

DUMP OF REDO FROM FILE '/u01/app/oracle/product/10.2.0/oradata/jingyong/redo01.log'
 Opcodes *.*
 RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
 SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
 Times: creation thru eternity
 FILE HEADER:
Compatibility Vsn = 169869568=0xa200100
Db ID=3172629284=0xbd1a7b24, Db Name='JINGYONG'
Activation ID=3141682910=0xbb4246de
Control Seq=4496=0x1190, File size=102400=0x19000
File Number=1, Blksiz=512, File Type=2 LOG
 descrip:"Thread 0001, Seq# 0000000004, SCN 0x00000010d5e7-0x000000114550"
 thread: 1 nab: 0x3313 seq: 0x00000004 hws: 0x24 eot: 0 dis: 0
 resetlogs count: 0x2fecc5c7 scn: 0x0000.000e487c (936060)
 resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000
 prev resetlogs count: 0x2fecb1fc scn: 0x0000.000df5e6 (914918)
 prev resetlogs terminal rcv count: 0x0 scn: 0x0000.00000000
 Low  scn: 0x0000.0010d5e7 (1103335) 01/14/2013 13:30:38
 Next scn: 0x0000.00114550 (1131856) 01/18/2013 01:33:36
 Enabled scn: 0x0000.000e487c (936060) 01/07/2013 02:11:19
 Thread closed scn: 0x0000.00113c59 (1129561) 01/18/2013 00:34:20
 Disk cksum: 0x3c58 Calc cksum: 0x3c58
 Terminal recovery stop scn: 0x0000.00000000
 Terminal recovery  01/01/1988 00:00:00
 Most recent redo scn: 0x0000.00000000
 Largest LWN: 1914 blocks
 End-of-redo stream : No
 Unprotected mode
 Miscellaneous flags: 0x0
 Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000

在上面的信息中可以看到
Low scn: 0x0000.0010d5e7 (1103335) 01/14/2013 13:30:38
Next scn: 0x0000.00114550 (1131856) 01/18/2013 01:33:36

与v$log视图中log group#1日志文件记录的scn的信息是一致的

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARCHIVED STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ------------------
         1 1          14         52428800   1          NO       INACTIVE        1103335       2013-1-14 13:30:38
         2 2          15         52428800   1          NO       CURRENT         1131856       2013-1-18 1:33:36
         3 3          13         52428800   1          YES      INACTIVE        1014044       2013-1-7  20:08:55

istener.ora文件的内容如下:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = jingyong)
      (ORACLE_HOME =/u01/app/oracle/product/10.2.0/db)
      (SID_NAME = jingyong)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = jingyong)(PORT = 1521))
    )
  )

上面的配置只配置了一个listener

下面再增加一个listener_2

SID_LIST_LISTENER_2 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = jingyong)
      (ORACLE_HOME =/u01/app/oracle/product/10.2.0/db)
      (SID_NAME = jingyong)
    )
  )

LISTENER_2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = jingyong)(PORT = 1522))
    )
  )

SID_LIST_ 这里的要和LISTENER名称一致。
SID_DESC则注册要提供服务的数据库实例名。这样LISTENER2 才能够提供服务。

再来启动两个listener

[oracle@jingyong udump]$ lsnrctl stop listener

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 13-JAN-2013 18:06:21

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jingyong)(PORT=1521)))
The command completed successfully
[oracle@jingyong udump]$ lsnrctl start listener_2

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 13-JAN-2013 18:06:37

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/10.2.0/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db/network/log/listener_2.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jingyong)(PORT=1522)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jingyong)(PORT=1522)))
STATUS of the LISTENER
------------------------
Alias                     listener_2
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                13-JAN-2013 18:06:37
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db/network/log/listener_2.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jingyong)(PORT=1522)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "jingyong" has 1 instance(s).
  Instance "jingyong", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@jingyong udump]$ lsnrctl start listener

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 13-JAN-2013 18:06:41

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/10.2.0/db/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jingyong)(PORT=1521)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=jingyong)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                13-JAN-2013 18:06:41
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=jingyong)(PORT=1521)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "jingyong" has 1 instance(s).
  Instance "jingyong", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

一个oracle实例对于多个监听，一个监听对于多个端口。
现在我们可以创建一个oracle实例一个监听的两个端口。这一共有两种方法：
在listener文件添加一行，具体如下：

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = jingyong)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = jingyong)(PORT = 1522))
    )
  )

这时我们只有重启监听，就可以用两个端口连接数据库实例了。。。。
当然我们也可以一个实例启动两个监听，但是具体只有一个监听起作用。
我们可以在一个数据库下，一个实例对于一个监听，但实例必须在不同目录。或者先建监听，再建实例。一样能达到效果。。。
具体配置如下：

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = jingyong)
      (ORACLE_HOME =/u01/app/oracle/product/10.2.0/db)
      (SID_NAME = jingyong)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = jingyong)(PORT = 1521))
    )
  )

SID_LIST_LISTENER_2 =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = jingyong)
      (ORACLE_HOME =/u01/app/oracle/product/10.2.0/db)
      (SID_NAME = jingyong)
    )
  )

LISTENER_2 =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = jingyong)(PORT = 1522))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = jingyong)(PORT = 1523))
    )
  )

在oracle文档中,file#是被定义为绝对文件号(the absolute file number)

查询dba_objects视图,发现v$tempseg_usage视图是一个同义词

SQL> select object_type from dba_objects where object_name='V$TEMPSEG_USAGE';

OBJECT_TYPE
-------------------
SYNONYM

v$tempseg_usage是v_$sort_usage的同义词,也就是和v$sort_usage同源.

select * from dba_synonyms where synonym_name='V$TEMPSEG_USAGE';
SQL> select * from dba_synonyms where synonym_name='V$TEMPSEG_USAGE';

OWNER    SYNONYM_NAME     TABLE_OWNER  TABLE_NAME     DB_LINK
-------- ---------------- ------------ -------------- ----------
PUBLIC   V$TEMPSEG_USAGE  SYS          V_$SORT_USAGE

查看这个视图的构造语句;

SQL> select view_definition from v$fixed_view_definition where view_name='GV$SORT_USAGE';

VIEW_DEFINITION
--------------------------------------------------------------------------------
select x$ktsso.inst_id,
       username,
       username,
       ktssoses,
       ktssosno,
       prev_sql_addr,
       prev_hash_value,
       prev_sql_id,
       ktssotsn,
       decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY'),
       decode(ktssosegt,
              1,
              'SORT',
              2,
              'HASH',
              3,
              'DATA',
              4,
              'INDEX',
              5,
              'LOB_DATA',
              6,
              'LOB_INDEX',
              'UNDEFINED'),
       ktssofno,
       ktssobno,
       ktssoexts,
       ktssoblks,
       ktssorfno
  from x$ktsso, v$session
 where ktssoses = v$session.saddr
   and ktssosno = v$session.serial#

注意到在oracle文档中segfile#的定义为:
segfile# number file number of initial extent
在视图中,这个字段来自x$ktsso.ktssofno,也就是说这个字段实际上代表的是绝对文件号,
那么这个绝对文件号能否与v$tempfile中的file#字段关联了

来查看一下v$tempfile的来源,

select view_definition from v$fixed_view_definition where view_name='GV$TEMPFILE';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select tf.inst_id,
       tf.tfnum,
       to_number(tf.tfcrc_scn),
       to_date(tf.tfcrc_tim,
               'MM/DD/RR HH24:MI:SS',
               'NLS_CALENDAR=Gregorian'),
       tf.tftsn,
       tf.tfrfn,
       decode(bitand(tf.tfsta, 2), 0, 'OFFLINE', 2, 'ONLINE', 'UNKNOWN'),
       decode(bitand(tf.tfsta, 12),
              0,
              'DISABLED',
              4,
              'READ ONLY',
              12,
              'READ WRITE',
              'UNKNOWN'),
       fh.fhtmpfsz * tf.tfbsz,
       fh.fhtmpfsz,
       tf.tfcsz * tf.tfbsz,
       tf.tfbsz,
       fn.fnnam
  from x$kcctf tf, x$kccfn fn, x$kcvfhtmp fh
 where fn.fnfno = tf.tfnum
   and fn.fnfno = fh.htmpxfil
   and tf.tffnh = fn.fnnum
   and tf.tfdup != 0
   and bitand(tf.tfsta, 32) <> 32
   and fn.fntyp = 7
   and fn.fnnam is not null

再来查看x$kcctf底层表,注意到TFAFN(Temp File Absolute File Number)是存在的

SQL> desc x$kcctf
Name      Type         Nullable Default Comments
--------- ------------ -------- ------- --------
ADDR      RAW(8)       Y
INDX      NUMBER       Y
INST_ID   NUMBER       Y
TFNUM     NUMBER       Y
TFAFN     NUMBER       Y
TFCSZ     NUMBER       Y
TFBSZ     NUMBER       Y
TFSTA     NUMBER       Y
TFCRC_SCN VARCHAR2(16) Y
TFCRC_TIM VARCHAR2(20) Y
TFFNH     NUMBER       Y
TFFNT     NUMBER       Y
TFDUP     NUMBER       Y
TFTSN     NUMBER       Y
TFTSI     NUMBER       Y
TFRFN     NUMBER       Y
TFPFT     NUMBER       Y
TFMSZ     NUMBER       Y
TFNSZ     NUMBER       Y

而v$kcctf.tfafn这个字段在构造v$tempfile时并没有使用,所以不能通过v$sort_usage
和vg$tempfile直接关联绝对文件号.查询一下排序段使用

SQL> select username,segtype,segfile#,segblk#,extents,segrfno# from v$sort_usage;

USERNAME                       SEGTYPE     SEGFILE#    SEGBLK#    EXTENTS   SEGRFNO#
------------------------------ --------- ---------- ---------- ---------- ----------
ZW2003                         DATA             201    2097801          1          1
ZW2001                         DATA             201    2104073          1          1
ZW2001                         DATA             201    2096265          1          1

看到这里的segfile#=201,而在v$tempfile是找不到这个信息的;

select file#,rfile#,ts#,status,blocks from v$tempfile;
SQL> select file#,rfile#,ts#,status,blocks from v$tempfile;

     FILE#     RFILE#        TS# STATUS      BLOCKS
---------- ---------- ---------- ------- ----------
         1          1          3 ONLINE     3840000

可以从x$kcctf中获得这些信息,并可以看到v$tempfile.file#实际上来自x$kcctf.tfnum,
这个字段是临时文件的文件号,而绝对文件号是v$kcctf.tfafn,只有这个字段才可以与
v$sort_usage.segfile#关联;

SQL> select indx,tfnum,tfafn,tfcsz from x$kcctf;

      INDX      TFNUM      TFAFN      TFCSZ
---------- ---------- ---------- ----------
         0          1        201    1048576

为了分离临时文件号和数据文件号,oracle对临时文件的编号是以db_files为起点,所以临时文件
的绝对文件号是等于db_files+file#

db_files参数的值如下:

SQL> show parameter db_files;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_files                             integer     200

所以v$tempfile.file#定义为绝对文件号是不确切的.

数据文件的的文件号

SQL> select a.object_name,a.object_type from dba_objects a where a.object_name='V$DATAFILE';

OBJECT_NAME     OBJECT_TYPE
--------------- -------------------
V$DATAFILE      SYNONYM

从这个查询知道v$datafile是同义词来源于v_$datafile

SQL> select * from dba_synonyms a where a.synonym_name='V$DATAFILE';

OWNER     SYNONYM_NAME  TABLE_OWNER  TABLE_NAME   DB_LINK
--------- ------------- ------------ ------------ -------
PUBLIC    V$DATAFILE    SYS          V_$DATAFILE

v_$datafile的构造语句如下:

SQL> select view_definition from v$fixed_view_definition where view_name='GV$DATAFILE';

VIEW_DEFINITION
--------------------------------------------------------------------------------
select /*+ rule */
 fe.inst_id,
 fe.fenum,
 to_number(fe.fecrc_scn),
 to_date(fe.fecrc_tim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
 fe.fetsn,
 fe.ferfn,
 decode(fe.fetsn,
        0,
        decode(bitand(fe.festa, 2), 0, 'SYSOFF', 'SYSTEM'),
        decode(bitand(fe.festa, 18), 0, 'OFFLINE', 2, 'ONLINE', 'RECOVER')),
 decode(fe.fedor,
        2,
        'READ ONLY',
        decode(bitand(fe.festa, 12),
               0,
               'DISABLED',
               4,
               'READ ONLY',
               12,
               'READ WRITE',
               'UNKNOWN')),
 to_number(fe.fecps),
 to_date(fe.fecpt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
 to_number(fe.feurs),
 to_date(fe.feurt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
 to_number(fe.fests),
 decode(fe.fests,
        NULL,
        to_date(NULL),
        to_date(fe.festt, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')),
 to_number(fe.feofs),
 to_number(fe.feonc_scn),
 to_date(fe.feonc_tim, 'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian'),
 fh.fhfsz * fe.febsz,
 fh.fhfsz,
 fe.fecsz * fe.febsz,
 fe.febsz,
 fn.fnnam,
 fe.fefdb,
 fn.fnbof,
 decode(fe.fepax, 0, 'UNKNOWN', 65535, 'NONE', fnaux.fnnam),
 to_number(fh.fhfirstunrecscn),
 to_date(fh.fhfirstunrectime,
         'MM/DD/RR HH24:MI:SS',
         'NLS_CALENDAR=Gregorian')
  from x$kccfe fe, x$kccfn fn, x$kccfn fnaux, x$kcvfh fh
 where ((fe.fepax != 65535 and fe.fepax != 0 and fe.fepax = fnaux.fnnum) or
       ((fe.fepax = 65535 or fe.fepax = 0) and fe.fenum = fnaux.fnfno and
       fnaux.fntyp = 4 and fnaux.fnnam is not null and
       bitand(fnaux.fnflg, 4) != 4 and fe.fefnh = fnaux.fnnum))
   and fn.fnfno = fe.fenum
   and fn.fnfno = fh.hxfil
   and fe.fefnh = fn.fnnum
   and fe.fedup != 0
   and fn.fntyp = 4
   and fn.fnnam is not null
   and bitand(fn.fnflg, 4) != 4

从上面的构造语句可知v$datafile.file#来源于x$kccfe.fenum字段

col operation format a50

col cost      format 999999
col kbytes    format 999999
col object    format a20
select hash_value,child_number,
       lpad('',2*depth)
       ||operation
       ||''
       ||options
       ||decode(id,0,substr(optimizer,1,6)||'Cost='||to_char(cost))operation,
       object_name,object_type,cost,round(bytes/1024) kbytes
from v$sql_plan where hash_value in(
                     select a.sql_hash_value
                     from v$session a,v$session_wait b
                     where a.sid=b.sid
                     and b.event='db file sequential read')
order by hash_value,child_number,ID;

SQL> col operation format a55
SQL> col cost      format 99999
SQL> col kbytes    format 999999
SQL> col object    format a25
SQL> select hash_value,child_number,
  2         lpad('',2*depth)
  3         ||operation
  4         ||''
  5         ||options
  6         ||decode(id,0,substr(optimizer,1,6)||'Cost='||to_char(cost))operation,
  7         object_name,object_type,cost,round(bytes/1024) kbytes
  8  from v$sql_plan where hash_value in(
  9                       select a.sql_hash_value
 10                       from v$session a,v$session_wait b
 11                       where a.sid=b.sid
 12                       and b.event='db file sequential read')
 13  order by hash_value,child_number,ID;

HASH_VALUE CHILD_NUMBER OPERATION                                               OBJECT_NAME                    OBJECT_TYPE                               COST KBYTES
---------- ------------ ------------------------------------------------------- ------------------------------ ---------------------------------------- ----- ------
1722201563            0 SELECT STATEMENTFIRST_Cost=203255                                                                                               20325
                                                                                                                                                            5

1722201563            0 SORTORDER BY                                                                                                                    20325   5910
                                                                                                                                                            5

1722201563            0 HASHGROUP BY                                                                                                                    20325   5910
                                                                                                                                                            5

1722201563            0 HASH JOIN                                                                                                                       20193   5910
                                                                                                                                                            1

1722201563            0 TABLE ACCESSFULL                                        BS_CORP                        TABLE                                       85     41
1722201563            0 NESTED LOOPSSEMI                                                                                                                20184   4561
                                                                                                                                                            5

1722201563            0 HASH JOIN                                                                                                                       20184   4540
                                                                                                                                                            3

1722201563            0 HASH JOIN                                                                                                                       19970   2515
                                                                                                                                                            4

1722201563            0 HASH JOIN                                                                                                                       19529   1813
                                                                                                                                                            2

1722201563            0 HASH JOINRIGHT SEMI                                                                                                             19442    489
                                                                                                                                                            1

1722201563            0 VIEW                                                    VW_SQ_1                        VIEW                                     19402     36
                                                                                                                                                            8

1722201563            0 FILTER
1722201563            0 NESTED LOOPS                                                                                                                    19402    241
                                                                                                                                                            8

1722201563            0 NESTED LOOPS                                                                                                                    19402    157
                                                                                                                                                            6

1722201563            0 MERGE JOINCARTESIAN                                                                                                             28436 264415
1722201563            0 INLIST ITERATOR
1722201563            0 INDEXRANGE SCAN                                         PK_BS_CORP                     INDEX (UNIQUE)                               4      0
1722201563            0 BUFFERSORT                                                                                                                      28432   5509
1722201563            0 TABLE ACCESSFULL                                        LV_BUSI_RECORD                 TABLE                                      862   5509
1722201563            0 TABLE ACCESSBY INDEX ROWID                              BS_FAMILY                      TABLE                                    19402      0
                                                                                                                                                            6


HASH_VALUE CHILD_NUMBER OPERATION                                               OBJECT_NAME                    OBJECT_TYPE                               COST KBYTES
---------- ------------ ------------------------------------------------------- ------------------------------ ---------------------------------------- ----- ------
1722201563            0 BITMAP CONVERSIONTO ROWIDS
1722201563            0 BITMAP AND
1722201563            0 BITMAP CONVERSIONFROM ROWIDS
1722201563            0 INDEXRANGE SCAN                                         PK_BS_FAMILY                   INDEX (UNIQUE)                               1
1722201563            0 BITMAP CONVERSIONFROM ROWIDS
1722201563            0 INDEXRANGE SCAN                                         IDX_BS_FAMILY_CORP_ID          INDEX                                        1
1722201563            0 INDEXRANGE SCAN                                         INDEX_FAMILY_ID                INDEX                                        1      0
1722201563            0 TABLE ACCESSFULL                                        LV_BUSI_BILL                   TABLE                                      393    847
1722201563            0 TABLE ACCESSFULL                                        LV_BUSI_RECORD                 TABLE                                      867  15524
1722201563            0 TABLE ACCESSFULL                                        BS_FAMILY                      TABLE                                     4407  23768
1722201563            0 TABLE ACCESSFULL                                        LV_BUSI_ASSIGN                 TABLE                                      913  12421
1722201563            0 INDEXRANGE SCAN                                         INDEX_FAMILY_ID                INDEX                                        1   3236
1722201563            1 SELECT STATEMENTFIRST_Cost=197255                                                                                               19725
                                                                                                                                                            5

1722201563            1 SORTORDER BY                                                                                                                    19725    907
                                                                                                                                                            5

1722201563            1 HASHGROUP BY                                                                                                                    19725    907
                                                                                                                                                            5

1722201563            1 HASH JOIN                                                                                                                       19704    907
                                                                                                                                                            8

1722201563            1 HASH JOIN                                                                                                                       19613    516
                                                                                                                                                            0

1722201563            1 TABLE ACCESSFULL                                        BS_CORP                        TABLE                                       85     41
1722201563            1 NESTED LOOPSSEMI                                                                                                                19604    389
                                                                                                                                                            4

1722201563            1 NESTED LOOPS                                                                                                                    19604    386
                                                                                                                                                            3

1722201563            1 NESTED LOOPS                                                                                                                    19488    278
                                                                                                                                                            0


HASH_VALUE CHILD_NUMBER OPERATION                                               OBJECT_NAME                    OBJECT_TYPE                               COST KBYTES
---------- ------------ ------------------------------------------------------- ------------------------------ ---------------------------------------- ----- ------
1722201563            1 HASH JOINSEMI                                                                                                                   19442     75
                                                                                                                                                            1

1722201563            1 TABLE ACCESSFULL                                        LV_BUSI_BILL                   TABLE                                      392     70
1722201563            1 VIEW                                                    VW_SQ_1                        VIEW                                     19402     36
                                                                                                                                                            8

1722201563            1 FILTER
1722201563            1 NESTED LOOPS                                                                                                                    19402    241
                                                                                                                                                            8

1722201563            1 NESTED LOOPS                                                                                                                    19402    157
                                                                                                                                                            6

1722201563            1 MERGE JOINCARTESIAN                                                                                                             28436 264415
1722201563            1 INLIST ITERATOR
1722201563            1 INDEXRANGE SCAN                                         PK_BS_CORP                     INDEX (UNIQUE)                               4      0
1722201563            1 BUFFERSORT                                                                                                                      28432   5509
1722201563            1 TABLE ACCESSFULL                                        LV_BUSI_RECORD                 TABLE                                      862   5509
1722201563            1 TABLE ACCESSBY INDEX ROWID                              BS_FAMILY                      TABLE                                    19402      0
                                                                                                                                                            6

1722201563            1 BITMAP CONVERSIONTO ROWIDS
1722201563            1 BITMAP AND
1722201563            1 BITMAP CONVERSIONFROM ROWIDS
1722201563            1 INDEXRANGE SCAN                                         PK_BS_FAMILY                   INDEX (UNIQUE)                               1
1722201563            1 BITMAP CONVERSIONFROM ROWIDS
1722201563            1 INDEXRANGE SCAN                                         IDX_BS_FAMILY_CORP_ID          INDEX                                        1
1722201563            1 INDEXRANGE SCAN                                         INDEX_FAMILY_ID                INDEX                                        1      0
1722201563            1 TABLE ACCESSBY INDEX ROWID                              LV_BUSI_RECORD                 TABLE                                        1      0
1722201563            1 INDEXRANGE SCAN                                         IDX_BUSI_BILL_BILLSN           INDEX                                        1

HASH_VALUE CHILD_NUMBER OPERATION                                               OBJECT_NAME                    OBJECT_TYPE                               COST KBYTES
---------- ------------ ------------------------------------------------------- ------------------------------ ---------------------------------------- ----- ------
1722201563            1 TABLE ACCESSBY INDEX ROWID                              BS_FAMILY                      TABLE                                        1      0
1722201563            1 INDEXUNIQUE SCAN                                        PK_BS_FAMILY                   INDEX (UNIQUE)                               1
1722201563            1 INDEXRANGE SCAN                                         INDEX_FAMILY_ID                INDEX                                        1   3236
1722201563            1 TABLE ACCESSFULL                                        LV_BUSI_ASSIGN                 TABLE                                      913  12421

66 rows selected

块清除(Block Cleanouts)

当用户发出提交(commit)之后,oracle怎样来处理的.oracle是需要写出redo来保证故障时数据可以被
恢复,我们知道oracle并不需要在提交时就写出变更的数据块.那么在提交时,oracle会对数据块进行什么操作?

在事务需要修改数据时,必须分配ITL事务槽,必须锁定行,必须分配回滚段事务槽和回滚表空间来记录要修改
的数据的前镜像.当事务提交时,oracle需要将回滚段上的事务表信息标记为非活动,以便空间可以被重用
那么还有ITL事务信息和锁定信息需要清除,以记录提交.

由于oracle在数据块上存储了ITL和锁定等事务信息,所以oracle必须在事务提交之后清除这些事务数据,
这就是块清除.块清除主要清除的数据有行级锁,ITL信息(包括提交标志,scn等).

如果提交时修改过的数据块仍然在buffer cache中,那么oracle可以清除ITL信息,这种清除叫做快速块清除
(fast block cleanout),快速块清除还有一个限制,当修改的块数量超过buffer cache的10%,则对超出的部
分不再进行快速块清除.

如果提交事务时,修改过的数据块已经被写回到数据文件上(或大量修改超出10%的部分),再次读出该数据块
进行修改,显然成本过于高昂,对于这种情况,oracle选择延迟块清除(delayed block cleanout),等到下一次
访问该block时再来清除ITL锁定信息,这就是延迟块清除.oracle通过延迟块清除来提高数据库的性能,加快
提交操作.快速提交是最普遍的情况.来看一下延迟块清除的处理.
进行测试:

SQL> update emp set sal=4000 where empno=7788;
1 row updated.

SQL> update emp set sal=4000 where empno=7782;

1 row updated.

SQL> update emp set sal=4000 where empno=7698;

1 row updated.

更新完成之后,强制刷新buffer cache,将buffer cache中的数据都写出到数据文件:

SQL> alter session set events='immediate trace name flush_cache';

Session altered

此时再提交事务;

SQL> commit;

Commit complete.

由于此时更新过的数据已经写出到数据文件,oracle将执行延迟块清除,将此时的数据块和回滚段转储出来:

[oracle@jingyong ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 7 10:18:56 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter system dump datafile 4 block 32;

System altered.

SQL> alter system dump undo header '_SYSSMU9$';

System altered.

SQL> alter system dump datafile 2 block 1350;

System altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from sys.v$mystat m,
  6  sys.v$session s,sys.v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from sys.v$thread  t,sys.v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from sys.v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_5023.trc

查看跟踪文件信息,看数据块上的信息,ITL事务信息仍然存在:
其中scn表示提交commit scn,fsc表示快速提交scn

*** 2013-01-07 10:19:33.032
*** SERVICE NAME:(SYS$USERS) 2013-01-07 10:19:33.031
*** SESSION ID:(140.421) 2013-01-07 10:19:33.031
Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32
buffer tsn: 4 rdba: 0x01000020 (4/32)
scn: 0x0000.000e6ebb seq: 0x01 flg: 0x04 tail: 0x6ebb0601
frmt: 0x02 chkval: 0xf364 type: 0x06=trans data

Block header dump:  0x01000020
 Object id on Block? Y
 seg/obj: 0xc7cc  csc: 0x00.e6ebb  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000019 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01d.00000181  0x00800546.0129.18  ----    3  fsc 0x0002.00000000
0x02   0x0002.010.00000158  0x00800598.013f.26  C---    0  scn 0x0000.000d4495
xid=0x0009.01d.00000181的事务lck=3

数据块的锁定信息仍然存在:

tab 0, row 5, @0x1d11
tl: 40 fb: --H-FL-- lb: 0x1  cc: 8
col  0: [ 3]  c2 4d 63
col  1: [ 5]  42 4c 41 4b 45
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 05 01 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 1f
tab 0, row 6, @0x1d39
tl: 40 fb: --H-FL-- lb: 0x1  cc: 8
col  0: [ 3]  c2 4e 53
col  1: [ 5]  43 4c 41 52 4b
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 06 09 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 0b
tab 0, row 7, @0x1e4c
tl: 40 fb: --H-FL-- lb: 0x1  cc: 8
col  0: [ 3]  c2 4e 59
col  1: [ 5]  53 43 4f 54 54
col  2: [ 7]  41 4e 41 4c 59 53 54
col  3: [ 3]  c2 4c 43
col  4: [ 7]  77 bb 04 13 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 15

再来看回滚段的信息:

*** 2013-01-07 10:20:01.417

********************************************************************************
Undo Segment:  _SYSSMU9$ (9)
********************************************************************************

Version: 0x01
  FREE BLOCK POOL::
    uba: 0x00800546.0129.18 ext: 0xa  spc: 0x12ea
    uba: 0x00000000.0129.05 ext: 0xa  spc: 0x1e08
    uba: 0x00000000.0129.42 ext: 0xa  spc: 0x73e
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0
    uba: 0x00000000.0000.00 ext: 0x0  spc: 0x0
  TRN TBL::

index  state cflags  wrap#    uel         scn            dba            parent-xid    nub     stmt_num    cmt
------------------------------------------------------------------------------------------------
0x1d    9    0x00  0x0181  0xffff  0x0000.000e743c  0x00800546  0x0000.000.00000000  0x00000001   0x00000000  1357525082

事务提交,事务表已经释放。如果此时查询scott.emp表,数据库将产生延迟块清除:

SQL> set autotrace on
SQL> select * from scott.emp;

EMPNO      ENAME      JOB              MGR HIREDATE            SAL       COMM      DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800         20

      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300        30

      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500        30
      
      7566 JONES      MANAGER         7839 02-APR-81          2975         20

      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400        30

      7698 BLAKE      MANAGER         7839 01-MAY-81          4000         30

      7782 CLARK      MANAGER         7839 09-JUN-81          4000         10

      7788 SCOTT      ANALYST         7566 19-APR-87          4000         20

      7839 KING       PRESIDENT            17-NOV-81          5000         10
      
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0        30

      7876 ADAMS      CLERK           7788 23-MAY-87          1100         20

      7900 JAMES      CLERK           7698 03-DEC-81           950         30
      
      7902 FORD       ANALYST         7566 03-DEC-81          3000         20

      7934 MILLER     CLERK           7782 23-JAN-82          1300         10


14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
      345 redo size
       1413  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>

注意,在此查询是产生了物理读取和redo,这个redo就是因为延迟块清除导致的,再次查询则不会
产生redo了:

SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM  DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ----------  ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800         20

      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300        30

      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500        30
      
      7566 JONES      MANAGER         7839 02-APR-81          2975         20

      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400        30

      7698 BLAKE      MANAGER         7839 01-MAY-81          4000         30

      7782 CLARK      MANAGER         7839 09-JUN-81          4000         10

      7788 SCOTT      ANALYST         7566 19-APR-87          4000         20

      7839 KING       PRESIDENT            17-NOV-81          5000         10


      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0        30

      7876 ADAMS      CLERK           7788 23-MAY-87          1100         20

      7900 JAMES      CLERK           7698 03-DEC-81           950         30

      7902 FORD       ANALYST         7566 03-DEC-81          3000         20

      7934 MILLER     CLERK           7782 23-JAN-82          1300         10


14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   518 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EMP  |    14 |   518 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1413  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         14  rows processed

SQL>

再次转储一下该数据块和回滚段:

SQL> alter system dump datafile 4 block 32;

System altered.

SQL> alter system dump undo header '_SYSSMU9$';

System altered.

SQL> alter system dump datafile 2 block 1350;

System altered.

SQL> select
  2  d.value||'/'||lower(rtrim(i.instance,
  3  chr(0)))||'_ora_'||p.spid||'.trc' trace_file_name
  4  from ( select p.spid
  5  from sys.v$mystat m,
  6  sys.v$session s,sys.v$process p
  7  where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
  8  ( select t.instance from sys.v$thread  t,sys.v$parameter v
  9  where v.name = 'thread' and
 10  (v.value = 0 or t.thread# = to_number(v.value))) i,
 11  ( select value from sys.v$parameter
 12  where name = 'user_dump_dest') d
 13  /

TRACE_FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/admin/jingyong/udump/jingyong_ora_5043.trc

查看跟踪文件,看到此时ITL事务信息已经清除,但是注意,这里的xid和uba信息仍然存在:

Start dump data blocks tsn: 4 file#: 4 minblk 32 maxblk 32
buffer tsn: 4 rdba: 0x01000020 (4/32)
scn: 0x0000.000e7560 seq: 0x01 flg: 0x00 tail: 0x75600601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

Block header dump:  0x01000020
 Object id on Block? Y
 seg/obj: 0xc7cc  csc: 0x00.e7560  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000019 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.01d.00000181  0x00800546.0129.18  C---    0  scn 0x0000.000e743c
0x02   0x0002.010.00000158  0x00800598.013f.26  C---    0  scn 0x0000.000d4495

数据块块的锁定位也已经清除了:
tab 0, row 5, @0x1d11
tl: 40 fb: --H-FL-- lb: 0x0  cc: 8
col  0: [ 3]  c2 4d 63
col  1: [ 5]  42 4c 41 4b 45
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 05 01 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 1f
tab 0, row 6, @0x1d39
tl: 40 fb: --H-FL-- lb: 0x0  cc: 8
col  0: [ 3]  c2 4e 53
col  1: [ 5]  43 4c 41 52 4b
col  2: [ 7]  4d 41 4e 41 47 45 52
col  3: [ 3]  c2 4f 28
col  4: [ 7]  77 b5 06 09 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 0b
tab 0, row 7, @0x1e4c
tl: 40 fb: --H-FL-- lb: 0x0  cc: 8
col  0: [ 3]  c2 4e 59
col  1: [ 5]  53 43 4f 54 54
col  2: [ 7]  41 4e 41 4c 59 53 54
col  3: [ 3]  c2 4c 43
col  4: [ 7]  77 bb 04 13 01 01 01
col  5: [ 2]  c2 29
col  6: *NULL*
col  7: [ 2]  c1 15

提交之后的undo信息
当提交事务之后,回滚段事务表标记该事务为非活动,继续再来看一下回滚段数据块的信息,
看到这里的irb指向了0x28，此前的事务已经不可回滚

********************************************************************************
UNDO BLK:
xid: 0x0009.02f.00000181  seq: 0x129 cnt: 0x28  irb: 0x28  icl: 0x0   flg: 0x0000

看一下偏移量列表也已经新增了一条信息0x28 0x0a4c

 Rec Offset      Rec Offset      Rec Offset      Rec Offset      Rec Offset
---------------------------------------------------------------------------
0x01 0x1f3c     0x02 0x1ed0     0x03 0x1e54     0x04 0x1de8     0x05 0x1d3c
0x06 0x1c90     0x07 0x1c24     0x08 0x1b78     0x09 0x1acc     0x0a 0x1a20
0x0b 0x1974     0x0c 0x1908     0x0d 0x189c     0x0e 0x17f0     0x0f 0x1784
0x10 0x1718     0x11 0x166c     0x12 0x1600     0x13 0x1594     0x14 0x14e8
0x15 0x147c     0x16 0x13e4     0x17 0x1388     0x18 0x132c     0x19 0x1280
0x1a 0x11d4     0x1b 0x1128     0x1c 0x10ac     0x1d 0x1000     0x1e 0x0f54
0x1f 0x0ee8     0x20 0x0e7c     0x21 0x0e10     0x22 0x0da4     0x23 0x0cf8
0x24 0x0c4c     0x25 0x0ba0     0x26 0x0b24     0x27 0x0ab8     0x28 0x0a4c

再看前镜像0x18 0x132c的信息,仍然存在:

*-----------------------------
* Rec #0x18  slt: 0x1d  objn: 51148(0x0000c7cc)  objd: 51148  tblspc: 4(0x00000004)
*       Layer:  11 (Row)   opc: 1   rci 0x17
Undo type:  Regular undo   Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
rdba: 0x00000000
*-----------------------------
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01
op: C  uba: 0x00800546.0129.17
KDO Op code: URP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01000020  hdba: 0x0100001b
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 5(0x5) flag: 0x2c lock: 0 ckix: 191
ncol: 8 nnew: 1 size: 1
col  5: [ 3]  c2 1d 33

虽然这个事务已经提交了,不可以回滚了,但是在覆盖之前,这个前镜像信息仍然存在,通过某些手段,还是可以获得这个信息的.比如回闪查询