October 2009 Archives

在"如何找到你要修改的数据字典的物理存储位置"这篇文章里我们介绍了在数据库open的情况下找到你要修改的数据字典记录的物理存储位置的方法。

这篇文章我们介绍在数据库shutdown的情况下如何找到你要修改的数据字典记录的物理存储位置。

还是那句话----具体的解释我就不详细说了，因为有完善的备份的话这些旁门左道都是不需要的！

这里我还是要把表空间TESTSCN的online$由1改为2。

首先我找到C_TS#的段头位置，从bootstrap$可以知道C_TS#的段头位置在FILE 1 BLOCK 57，剩下的就是一连串的BBED动作了：

BBED> set file 1

        FILE#           1

BBED> set block 57

        BLOCK#          57

BBED> map /v

 File: /dras11/oradata/astca/system01.dbf (1)

 Block: 57                                    Dba:0x00400039

------------------------------------------------------------

 Unlimited Data Segment Header

 struct kcbh, 20 bytes                      @0      

    ub1 type_kcbh                           @0      

    ub1 frmt_kcbh                           @1      

    ub1 spare1_kcbh                         @2      

    ub1 spare2_kcbh                         @3      

    ub4 rdba_kcbh                           @4      

    ub4 bas_kcbh                            @8      

    ub2 wrp_kcbh                            @12     

    ub1 seq_kcbh                            @14     

    ub1 flg_kcbh                            @15     

    ub2 chkval_kcbh                         @16     

    ub2 spare3_kcbh                         @18     

 struct ktech, 72 bytes                     @20     

    ub4 spare1_ktech                        @20     

    word tsn_ktech                          @24     

    ub4 lastmap_ktech                       @28     

    ub4 mapcount_ktech                      @32     

    ub4 extents_ktech                       @36     

    ub4 blocks_ktech                        @40     

    ub2 mapend_ktech                        @44     

    struct hwmark_ktech, 32 bytes           @48     

    struct locker_ktech, 8 bytes            @80     

    ub4 flag_ktech                          @88     

 struct ktemh, 16 bytes                     @92     

    ub4 count_ktemh                         @92     

    ub4 next_ktemh                          @96     

    ub4 obj_ktemh                           @100    

    ub4 flag_ktemh                          @104    

 struct ktetb[12], 96 bytes                 @108    

    ub4 ktetbdba                            @108    

    ub4 ktetbnbk                            @112    

 struct ktshc, 8 bytes                      @4148   

    ub2 ktshcnxf                            @4148   

    ub2 ktshcnfl                            @4150   

    ub2 ktshcnfb                            @4152   

    ub1 ktshctyp                            @4154   

 struct ktsfs_seg[1], 20 bytes              @4156   

    ub2 ktsfsflg                            @4156   

    struct ktsfsxid, 8 bytes                @4160   

    ub4 ktsfslhd                            @4168   

    ub4 ktsfsltl                            @4172   

 struct ktsfs_txn[28], 560 bytes            @4176   

    ub2 ktsfsflg                            @4176   

    struct ktsfsxid, 8 bytes                @4180   

    ub4 ktsfslhd                            @4188   

    ub4 ktsfsltl                            @4192   

 ub4 tailchk                                @8188   

BBED> p hwmark_ktech

struct hwmark_ktech, 32 bytes               @48     

   ub4 extno_ktehw                          @48       0x0000000b

   ub4 blkno_ktehw                          @52       0x00000005

   ub4 extsize_ktehw                        @56       0x00000008

   ub4 blkaddr_ktehw                        @60       0x0040f57e

   ub4 mapblk_ktehw                         @64       0x00000000

   ub4 offset_ktehw                         @68       0x0000000b

   ub4 flblks_ktehw                         @72       0x00000003

   ub4 blkcnt_ktehw                         @76       0x0000005c

BBED> p ktetb[11].ktetbdba

ub4 ktetbdba                                @196      0x0040f579

BBED> set dba 0x0040f57d

        DBA             0x0040f57d (4257149 1,62845)

BBED> find /x 5445535453434e

BBED-00212: search string not found

BBED> set dba 0x0040f57c

        DBA             0x0040f57c (4257148 1,62844)

BBED> find /x 5445535453434e

 File: /dras11/oradata/astca/system01.dbf (1)

 Block: 62844            Offsets: 5903 to 6414           Dba:0x0040f57c

------------------------------------------------------------------------

 54455354 53434e01 8002c102 01800180 018003c2 525d02c1 05018001 8002c102

 06c51630 31252e03 c2021d03 c2021d03 c2021d01 8002c102 018003c2 021d0180

 02c10202 c12302c1 0906c520 2b4c2e34 ffffff01 8001806c 021d0007 54455354

 53434e01 8002c102 018003c2 021a03c2 0f1a03c2 525d02c1 05018001 8002c102

 06c51630 31252e03 c2021d03 c2021d03 c2021d01 8002c102 018003c2 021d0180

 02c10202 c12302c1 0906c520 2b4c2e34 ffffff01 8001806c 001d0007 54455354

 53434e01 8002c103 018003c2 021a03c2 0f1a03c2 525d02c1 0504c307 382406c5

 2b5f6149 6002c102 06c51630 31252e03 c2021d03 c2021d03 c2021d01 8002c102

 018003c2 021d0180 02c10202 c12302c1 0906c520 2b4c2e34 ffffff01 8001806c

 001d0007 54455354 53434e01 8002c103 018003c2 021a03c2 0f1a03c2 525d02c1

 05018001 8002c102 06c51630 31252e03 c2021d03 c2021d03 c2021d01 8002c102

 018003c2 021d0180 02c10202 c12302c1 0906c520 2b4c2e34 ffffff01 8001806c

 021d0007 54455354 53434e01 8002c103 018003c2 021a03c2 0f1a03c2 525d02c1

 0504c307 382406c5 2b5f6149 6002c102 06c51630 31252e03 c2021d03 c2021d03

 c2021d01 8002c102 018003c2 021d0180 02c10202 c12302c1 0906c520 2b4c2e34

 ffffff01 8001806c 001d0007 54455354 53434e01 8002c103 018003c2 021a03c2

 <32 bytes per line>

有时候难免涉及到要修改数据字典，这时候如何找到你要修改的数据字典记录的物理存储位置就很关键了，我这里给大家做一个例子。

具体的解释我就不详细说了，因为有完善的备份的话这些旁门左道都是不需要的！

SQL> select ts#,name,owner#,online$ from ts$ where name='TESTSCN';

       TS# NAME                               OWNER#    ONLINE$

---------- ------------------------------ ---------- ----------

        90 TESTSCN                                 0          1

现在我要把表空间TESTSCN的online$由1改为2，朋友们知道我这么改的目的是什么吗？不明白我为什么要这么改的朋友可以去看sql.bsq。

首先我找到C_TS#的段头位置，我这个库里C_TS#的段头位置在FILE 1 BLOCK 57。

Dump上述段头，内容如下：

Start dump data blocks tsn: 0 file#: 1 minblk 57 maxblk 57

buffer tsn: 0 rdba: 0x00400039 (1/57)

scn: 0x0008.aa4ab54b seq: 0x01 flg: 0x04 tail: 0xb54b1001

frmt: 0x02 chkval: 0xf4ef type: 0x10=DATA SEGMENT HEADER - UNLIMITED

  Extent Control Header

  -----------------------------------------------------------------

  Extent Header:: spare1: 0      spare2: 0      #extents: 12     #blocks: 95   

                  last map  0x00000000  #maps: 0      offset: 4128 

      Highwater::  0x0040f57e  ext#: 11     blk#: 5      ext size: 8    

  #blocks in seg. hdr's freelists: 3    

  #blocks below: 92   

  mapblk  0x00000000  offset: 11   

                   Unlocked

     Map Header:: next  0x00000000  #extents: 12   obj#: 6      flag: 0x40000000

  Extent Map

  -----------------------------------------------------------------

   0x0040003a  length: 7    

   0x004010e1  length: 8    

   0x0040b7c1  length: 8    

   0x0040b7c9  length: 8    

   0x0040b7d1  length: 8    

   0x0040b7d9  length: 8    

   0x0040b7e1  length: 8    

   0x0040b7e9  length: 8    

   0x0040dd41  length: 8    

   0x0040dd51  length: 8    

   0x0040dd59  length: 8    

   0x0040f579  length: 8    

  nfl = 1, nfb = 1 typ = 1 nxf = 28 ccnt = 67

  SEG LST:: flg: USED   lhd: 0x0040f57c ltl: 0x0040f57d

SQL> select max(ts#) from ts$ where name='TESTSCN';

  MAX(TS#)

----------

        90

SQL> select dump(1,16) from dual;

DUMP(1,16)

-----------------

Typ=2 Len=2: c1,2

SQL> select dump(90,16) from dual;

DUMP(90,16)

------------------

Typ=2 Len=2: c1,5b

SQL> select dump('TESTSCN',16) from dual;

DUMP('TESTSCN',16)

----------------------------------

Typ=96 Len=7: 54,45,53,54,53,43,4e

从上述查询结果里我们可以知道表空间TESTSCN对应于ts$中的记录必然在起始block为0x0040f579的这个extent中。

Dump上述extent（语法为alter system dump datafile 1 block min 62841 block max 62848），发现dump的内容中有如下内容：

buffer tsn: 0 rdba: 0x0040f57c (1/62844)

scn: 0x0008.bb52f812 seq: 0x01 flg: 0x06 tail: 0xf8120601

frmt: 0x02 chkval: 0x2147 type: 0x06=trans data

Block header dump:  0x0040f57c

 Object id on Block? Y

 seg/obj: 0x6  csc: 0x08.bb52f80d  itc: 2  flg: O  typ: 1 - DATA

     fsl: 0  fnx: 0x40f57d ver: 0x01

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0006.00f.000162b4  0x13000053.3fb8.22  C---    0  scn 0x0008.aa4ab54d

0x02   0x0001.021.000107ec  0x12c00012.38c1.20  --U-    1  fsc 0x000c.bb52f812

data_block_dump,data header at 0x11027405c

===============

tsiz: 0x1fa0

hsiz: 0x1e

pbl: 0x11027405c

bdba: 0x0040f57c

     76543210

flag=--------

ntab=3

nrow=2

frre=-1

fsbo=0x1e

fseo=0x16ae

avsp=0x1f04

tosp=0x1f10

0xe:pti[0] nrow=1    offs=0

0x12:pti[1]      nrow=0    offs=1

0x16:pti[2]      nrow=1    offs=1

0x1a:pri[0]      offs=0x1f8a

0x1c:pri[1]      offs=0x16ae

block_row_dump:

tab 0, row 0, @0x1f8a

tl: 22 fb: K-H-FL-- lb: 0x0  cc: 1

curc: 1 comc: 1 pk: 0x0040f57c.0 nk: 0x0040f57c.0

col  0: [ 2]  c1 5b

tab 2, row 0, @0x16ae

tl: 92 fb: -CH-FL-- lb: 0x2  cc: 29 cki: 0

col  0: [ 7]  54 45 53 54 53 43 4e

col  1: [ 1]  80

col  2: [ 2]  c1 02

col  3: [ 1]  80

col  4: [ 1]  80

col  5: [ 1]  80

col  6: [ 3]  c2 52 5d

col  7: [ 2]  c1 05

col  8: [ 1]  80

col  9: [ 1]  80

col 10: [ 2]  c1 02

col 11: [ 6]  c5 16 30 31 25 2e

col 12: [ 3]  c2 02 1d

col 13: [ 3]  c2 02 1d

col 14: [ 3]  c2 02 1d

col 15: [ 1]  80

col 16: [ 2]  c1 02

col 17: [ 1]  80

col 18: [ 3]  c2 02 1d

col 19: [ 1]  80

col 20: [ 2]  c1 02

col 21: [ 2]  c1 23

col 22: [ 2]  c1 09

col 23: [ 6]  c5 20 2b 4c 2e 34

col 24: *NULL*

col 25: *NULL*

col 26: *NULL*

col 27: [ 1]  80

col 28: [ 1]  80

end_of_block_dump

很明显从上述dump结果中我们可以知道表空间TESTSCN在ts$中的记录的行头的offset是0x16ae。

我们来验证一下：

BBED> set file 1

        FILE#           1

BBED> set block 62844

        BLOCK#          62844

BBED> set offset 0x16ae

        OFFSET          5806

BBED> dump

 File: /dras11/oradata/astca/system01.dbf (1)

 Block: 62844            Offsets: 5806 to 6317           Dba:0x0040f57c

------------------------------------------------------------------------

 c415070a 0e08c715 070a0e0a 341dffff ff05c415 070a0eff ffffffff 03434e59

 ffff0144 01480144 ff024341 04313335 3504c315 070a014e 0144ffff ffffff01

 4e018004 c315070a 0159ffff 014eff04 c315070b ffffffff ffffffff 6c021d00

 07544553 5453434e 018002c1 02018001 80018003 c2525d02 c1050180 018002c1

 0206c516 3031252e 03c2021d 03c2021d 03c2021d 018002c1 02018003 c2021d01

 8002c102 02c12302 c10906c5 202b4c2e 34ffffff 01800180 6c021d00 07544553

 5453434e 018002c1 02018003 c2021a03 c20f1a03 c2525d02 c1050180 018002c1

 0206c516 3031252e 03c2021d 03c2021d 03c2021d 018002c1 02018003 c2021d01

 8002c102 02c12302 c10906c5 202b4c2e 34ffffff 01800180 6c001d00 07544553

 5453434e 018002c1 03018003 c2021a03 c20f1a03 c2525d02 c10504c3 07382406

 c52b5f61 496002c1 0206c516 3031252e 03c2021d 03c2021d 03c2021d 018002c1

 02018003 c2021d01 8002c102 02c12302 c10906c5 202b4c2e 34ffffff 01800180

 6c001d00 07544553 5453434e 018002c1 03018003 c2021a03 c20f1a03 c2525d02

 c1050180 018002c1 0206c516 3031252e 03c2021d 03c2021d 03c2021d 018002c1

 02018003 c2021d01 8002c102 02c12302 c10906c5 202b4c2e 34ffffff 01800180

 6c021d00 07544553 5453434e 018002c1 03018003 c2021a03 c20f1a03 c2525d02

 <32 bytes per line>

上述dump内容中我用红颜色标注的部分就是表空间TESTSCN在ts$中对应的记录的online$的实际物理位置。

在这篇文章里，我们通过BBED强制让丢失了一个datafile的offline状态（且这个offline的datafile已经被rm掉了）的tablespace恢复成了online，并且成功抢救出来了数据。

SQL_astca>archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /dras20/astca/arch

Oldest online log sequence     0

Next log sequence to archive   1

Current log sequence           1

SQL_astca>create tablespace testscn datafile '/dras20/astca/testscn_01.dbf' size 100M extent management local uniform size 1M segment space management auto;

Tablespace created.

SQL_astca>create table tbtestscn tablespace testscn as select * from dba_users;

Table created.

SQL_astca>select count(*) from tbtestscn;

  COUNT(*)

----------

        32

SQL_astca>select username from tbtestscn where rownum<5;

USERNAME

------------------------------

SYS

SCOTT

SYSTEM

DBSNMP

SQL_astca>alter tablespace testscn add datafile '/dras20/astca/testscn_02.dbf' size 100M;

Tablespace altered.

SQL_astca>alter database datafile '/dras20/astca/testscn_02.dbf' offline drop;

Database altered.

SQL_astca>alter system switch logfile;

System altered.

SQL_astca>alter system switch logfile;

System altered.

SQL_astca>alter system switch logfile;

System altered.

SQL_astca>alter system switch logfile;

System altered.

$ cd /dras20/astca/arch

$ ls -l

total 864

-rw-r-----   1 oracle   dba          423424 Oct 28 16:51 1_1.dbf

-rw-r-----   1 oracle   dba            1024 Oct 28 16:51 1_2.dbf

-rw-r-----   1 oracle   dba            3072 Oct 28 16:51 1_3.dbf

-rw-r-----   1 oracle   dba            1024 Oct 28 16:51 1_4.dbf

-rw-r--r--   1 oracle   dba              17 Oct 28 14:31 login.sql

$ rm *.dbf

$ ls -l

total 8

-rw-r--r--   1 oracle   dba              17 Oct 28 14:31 login.sql

SQL_astca>select file_id from dba_data_files where file_name='/dras20/astca/testscn_01.dbf';

   FILE_ID

----------

       139

SQL_astca>select file_id from dba_data_files where file_name='/dras20/astca/testscn_02.dbf';

   FILE_ID

----------

       140

SQL_astca>select status from v$datafile where file#=139;

STATUS

-------

ONLINE

SQL_astca>select status from v$datafile where file#=140;

STATUS

-------

RECOVER

SQL_astca>alter tablespace testscn offline immediate;

Tablespace altered.

$ cd /dras20/astca

$ ls -l

total 9242752

drwxr-xr-x   2 oracle   dba             256 Oct 28 16:52 arch

-rw-r--r--   1 oracle   dba            7366 Oct 28 15:42 createcontrolfile.txt

-rw-r-----   1 oracle   dba       104865792 Oct 28 16:52 drassda0701_03.dbf

-rw-r-----   1 oracle   dba       314580992 Oct 28 16:52 drassdc0610_03.dbf

-rw-r-----   1 oracle   dba       104865792 Oct 28 16:52 drassdc0701_02.dbf

-rw-r-----   1 oracle   dba       314580992 Oct 28 16:52 drasupd0610_03.dbf

-rw-r-----   1 oracle   dba       314580992 Oct 28 16:52 drasupd0705_01.dbf

-rw-r-----   1 oracle   dba       104865792 Oct 28 16:52 drasupd0706_02.dbf

-rw-r--r--   1 oracle   dba              17 Oct 28 16:46 login.sql

-rw-r--r--   1 oracle   dba         1056768 Oct 28 16:52 test01.dbf

-rw-r--r--   1 oracle   dba            8192 Jun 12 14:39 test01.dd

-rw-r--r--   1 oracle   dba            8192 Feb 18 2009  test01_01.txt

-rw-r--r--   1 oracle   dba          958464 Feb 18 2009  test01_02.txt

-rw-r-----   1 oracle   dba         1056768 Mar 03 2009  test01old.dbf

-rw-r-----   1 oracle   dba        10493952 Oct 28 16:52 testlmt01.dbf

-rw-r-----   1 oracle   dba       104865792 Oct 28 16:52 testscn_01.dbf

-rw-r-----   1 oracle   dba       104865792 Oct 28 16:50 testscn_02.dbf

-rw-r-----   1 oracle   dba      1048584192 Oct 28 16:52 testtbs1_01.dbf

-rw-r-----   1 oracle   dba      1048584192 Oct 28 16:52 testtbs1_02.dbf

-rw-r-----   1 oracle   dba      1048584192 Oct 28 16:52 testtbs2_01.dbf

-rw-r-----   1 oracle   dba       104865792 Oct 28 16:52 testtbs3_01.dbf

$ mv testscn_02.dbf testscn_02.bck

SQL_astca>select status from v$datafile where file#=139;

STATUS

-------

RECOVER

SQL_astca>select status from v$datafile where file#=140;

STATUS

-------

RECOVER

SQL_astca>select status from dba_tablespaces where tablespace_name='TESTSCN';

STATUS

---------

OFFLINE

SQL_astca>alter tablespace testscn online;

alter tablespace testscn online

*

ERROR at line 1:

ORA-01113: file 139 needs media recovery

ORA-01110: data file 139: '/dras20/astca/testscn_01.dbf'

SQL_astca>alter database datafile '/dras20/astca/testscn_01.dbf' online;

alter database datafile '/dras20/astca/testscn_01.dbf' online

*

ERROR at line 1:

ORA-01113: file 139 needs media recovery

ORA-01110: data file 139: '/dras20/astca/testscn_01.dbf'

SQL_astca>recover datafile 139;

Media recovery complete.

SQL_astca>alter database datafile '/dras20/astca/testscn_01.dbf' online;

Database altered.

SQL_astca>alter tablespace testscn online;

alter tablespace testscn online

*

ERROR at line 1:

ORA-01157: cannot identify/lock data file 140 - see DBWR trace file

ORA-01110: data file 140: '/dras20/astca/testscn_02.dbf'

SQL_astca>select count(*) from tbtestscn;

select count(*) from tbtestscn

*

ERROR at line 1:

ORA-00376: file 139 cannot be read at this time

ORA-01110: data file 139: '/dras20/astca/testscn_01.dbf'

这里我们可以看到：

表空间testscn无法online，因为其下的datafile 140先被offline drop了，然后datafile 140 recover所需要的archive log也被rm掉了，且datafile 140也被rm掉了。

朋友们知道在这种情况下如何恢复吗？

事实上，在上述这种情况下，用BBED还是可以恢复的，这里我们来看一下恢复后的效果：

SQL_astca>alter database open resetlogs;

Database altered.

SQL_astca>select status from v$datafile where file#=139;

STATUS

-------

ONLINE

SQL_astca>select status from v$datafile where file#=140;

STATUS

-------

ONLINE

SQL_astca>select status from dba_tablespaces where tablespace_name='TESTSCN';

STATUS

---------

OFFLINE

SQL_astca>alter tablespace testscn online;

Tablespace altered.

SQL_astca>select count(*) from tbtestscn;

  COUNT(*)

----------

        32

SQL_astca>select username from tbtestscn where rownum<5;

USERNAME

------------------------------

SYS

SCOTT

SYSTEM

DBSNMP

最近我看DSI都快走火入魔了，要控制一下，不能再这样下去了。

这里记录下我关于control file的一点体会：

1、同样都是db_block_size为8192，10g里control file的文件头为16K，9i里是8k。换句话说， 9i里control file的database entry section是从0x00002000开始，而10g里则是从0x00004000开始。

2、对于incremental checkpoint中的checkpoint heartbeat，在db_block_size为8192的9i里记录上述heartbeat所在的offset是0x00006050，而10g里则是0x0000c050

3、shadow block 真的存在！303里这样提到---- Changes to the control file are managed through shadow block structure, Copies of each block in the control file are stored in the same file with a flag to indicate which block copy is the current copy. 换句话说，每一次对control file中的block的修改，oracle都会将这个block的前镜像copy一份，然后也存在control file里，用于rollback。

我们来证明一下shadow block的存在性：

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

LOG FILE RECORDS

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

 (blkno = 0x5, size = 72, max = 5, in-use = 3, last-recid= 3)

LOG FILE #1:

  (name #5) /dras10/oradata/astca/redo01a.log

  (name #6) /dras11/oradata/astca/redo01b.log

 Thread 1 redo log links: forward: 2 backward: 0

 siz: 0x32000 seq: 0x00000005 hws: 0x1 bsz: 512 nab: 0xffffffff flg: 0x8 dup: 2

 Archive links: fwrd: 0 back: 0 Prev scn: 0x0008.bb4b0e24

 Low scn: 0x0008.bb4b0e28 10/15/2009 08:35:56

 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00

SQL> select dump('/dras10/oradata/astca/redo01a.log',16) dumpformat from dual;

DUMPFORMAT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Typ=96 Len=33: 2f,64,72,61,73,31,30,2f,6f,72,61,64,61,74,61,2f,61,73,74,63,61,2f,72,65,64,6f,30,31,61,2e

,6c,6f,67

我们从随后的control文件里可以看到2f6472617331302f6f7261646174612f61737463612f7265646f3031612e6c6f67出现了两次：

0003a440h: 00 00 00 00 00 03 00 01 00 06 00 00 00 00 2F 64 ; ............../d

0003a450h: 72 61 73 31 30 2F 6F 72 61 64 61 74 61 2F 61 73 ; ras10/oradata/as

0003a460h: 74 63 61 2F 72 65 64 6F 30 31 61 2E 6C 6F 67 00 ; tca/redo01a.log.

0003c440h: 00 00 00 00 00 03 00 01 00 06 00 00 00 02 2F 64 ; ............../d

0003c450h: 72 61 73 31 30 2F 6F 72 61 64 61 74 61 2F 61 73 ; ras10/oradata/as

0003c460h: 74 63 61 2F 72 65 64 6F 30 31 61 2E 6C 6F 67 00 ; tca/redo01a.log.

可以看到一个的offset是0x0003a440，另外一个的offset是0x0003c440，两者相差0x2000，也就是8192，刚好一个block，这就从一个侧面证明了shadow block的存在性，且说明了current block和shadow block是相邻的存在一起。

正是由于shadow block的存在，所以同志们在用ultraEdit修改control file的时候要注意了。

在这篇文章里，我们通过BBED强制让丢失了online需要的archive log的offline状态的datafile恢复成了online，并且成功抢救出来了数据。

希望如下的恢复过程能对朋友们有所帮助。

$ sqlplus '/ as sysdba';

SQL*Plus: Release 9.2.0.6.0 - Production on Wed Oct 14 13:21:25 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

SQL_astca>archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /dras20/astca/arch

Oldest online log sequence     9377

Next log sequence to archive   9379

Current log sequence           9379

SQL_astca > create tablespace testscn datafile '/dras20/astca/testscn_01.dbf' size 100M extent management local uniform size 1M segment space management auto;

Tablespace created

SQL_astca > create table tbtestscn tablespace testscn as select * from dba_users;

Table created

SQL_astca > select count(*) from tbtestscn;

  COUNT(*)

----------

        32

SQL_astca > select username from tbtestscn where rownum<5;

USERNAME

------------------------------

SYS

SCOTT

SYSTEM

DBSNMP

SQL_astca>select file_id from dba_data_files where file_name='/dras20/astca/testscn_01.dbf';

   FILE_ID

----------

       139

SQL_astca>alter database datafile '/dras20/astca/testscn_01.dbf' offline;

Database altered.

SQL_astca>select status from v$datafile where file#=139;

STATUS

-------

RECOVER

SQL_astca>alter system switch logfile;

System altered.

SQL_astca>alter system switch logfile;

System altered.

SQL_astca>alter system switch logfile;

System altered.

SQL_astca>alter system switch logfile;

System altered.

$ cd /dras20/astca/arch

$ ls -l

total 1888

-rw-r-----   1 oracle   dba          901120 Oct 14 16:45 1_9379.dbf

-rw-r-----   1 oracle   dba           48640 Oct 14 17:18 1_9380.dbf

-rw-r-----   1 oracle   dba            1024 Oct 14 17:18 1_9381.dbf

-rw-r-----   1 oracle   dba            1536 Oct 14 17:18 1_9382.dbf

-rw-r-----   1 oracle   dba            1024 Oct 14 17:18 1_9383.dbf

-rw-r--r--   1 oracle   dba              17 Oct 14 15:19 login.sql

$ rm *.dbf

$ ls -l

total 8

-rw-r--r--   1 oracle   dba              17 Oct 14 15:19 login.sql

SQL_astca>alter database datafile '/dras20/astca/testscn_01.dbf' online;

alter database datafile '/dras20/astca/testscn_01.dbf' online

*

ERROR at line 1:

ORA-01113: file 139 needs media recovery

ORA-01110: data file 139: '/dras20/astca/testscn_01.dbf'

SQL_astca>select count(*) from tbtestscn;

select count(*) from tbtestscn

                     *

ERROR at line 1:

ORA-00376: file 139 cannot be read at this time

ORA-01110: data file 139: '/dras20/astca/testscn_01.dbf'

SQL_astca>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

这里直接改datafile 139的datafile header中的checkpoint scn和RBA是不行的！

因为oracle在对某个datafile做offline的时候实际上是相当于offline immediate，此时不会改datafile header中的内容，而只是修改control文件，等到再想online的时候一定要做recovery，对这一点，403e也有描述：

Offline normal (tablespace)：

1、Checkpoints data blocks of tablespace

2、Updates file headers and control file

Offline immediate (tablespace or data file)：

1、Only update control file

2、Data files require recovery

BBED无法改control文件，所以上述这条只改datafile header中的checkpoint scn和RBA的路是走不通的。

这里我采取的方法是先改datafile header，再重建控制文件，即可强制恢复offline状态的datafile，在修改上述offline的datafile header的过程中我是通过比对system01.dbf的datafile header来修改offline datafile的datafile header，完整的恢复过程可见附带的文件session.log

其中重要的步骤如下：

1、  先通过比对system01.dbf的datafile header的内容来修改datafile 139的datafile header。

2、  重建控制文件

3、  用带*._allow_resetlogs_corruption=TRUE的pfile启库到mount状态

4、  用open resetlogs强制打开上述数据库

5、  最后shutdown immediate再startup

我们来看一下最后的结果：

SQL_astca>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL_astca>startup

ORACLE instance started.

Total System Global Area  824150304 bytes

Fixed Size                   743712 bytes

Variable Size             285212672 bytes

Database Buffers          536870912 bytes

Redo Buffers                1323008 bytes

Database mounted.

Database opened.

SQL_astca>select count(*) from tbtestscn;

  COUNT(*)

----------

        32

SQL_astca> select username from tbtestscn where rownum<5;

USERNAME

------------------------------

SYS

SCOTT

SYSTEM

DBSNMP

SQL_astca>select status from v$datafile where file#=139;

STATUS

-------

ONLINE

session.log

403e里这样描述SCN：

The SCN marks consistent states of the database associated with transaction commits.

The SCN also serves as a means of ordinance and acts as an internal clock. Each database has a global SCN generator. 

Almost every time that a new SCN is calculated, there is a verification that the new value is "reasonable," that is, inferior to a theoretical maximum value based on the hypothesis that there cannot be more than 16,384 commits per second.

SCNs are used for concurrency control (read consistency), redo log records ordering, and recovery.

The SCN base (4 bytes) is incremented for each SCN allocation. The SCN wrap (2 bytes) is incremented when the base turns over.

SCN numbers are monotonically increasing, but they may jump in a distributed or parallel server environment.

看到这里，我脑海里会浮现这样几个问题：

1、  按照上述理论----cannot be more than 16,384 commits per second，oracle里的SCN能用多少年？

2、  大家都知道，利用公式scn_wrap*4294967296+scn_bas就可以将oracle存储在物理文件里的SCN转换成我们熟悉的以数字形式表示的SCN，这个公式是如何得到的？

对于问题一：

每秒16384次commit即代表每秒会产生16384个SCN，SCN是6个byte，我们按照一个月31天来计算，上述6个byte的SCN在每秒16384次commit强度的情况下一共可以持续的年份的计算方法就一目了然了：

SQL> select power(16,12)/(16384*60*60*24*31*12) years from dual;

 YEARS

----------

534.519028

也就是说，在每秒16384次commit的情况下可以维持534年。

那上述SCN的终点具体是哪一年呢？这还要从oracle里用数字来代表时间的算法说起，对这一点403e里也有叙述：

Timestamps exist in the file headers and in many control file sections. These timestamps are not the same format as DATE or TIMESTAMP but contain a simple integer with the number of seconds since 1/1/1988.

从这里我们就可以知道上述SCN的终点是2522年：

SQL> select 1988+534 last_year from dual;

 LAST_YEAR

----------

      2522

对于问题二：

我们先来看一个实际的转换的例子：

第484－489个byte，如下图所示：

File: /iprat02/ipratest/system01.dbf (1)

 Block: 1                Offsets:    0 to  511           Dba:0x00400001

------------------------------------------------------------------------

 0ba20000 00400001 00000000 00000104 0bb20000 00000000 0a200100 5091aeb3

 49505241 54455354 0000329d 0000f000 00002000 00010003 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00400179 00000008 00000000 21a23e84 28144e35 00086983 0000000a 00000000

 00000000 00000000 00002004 00000156 28144e27 00000155 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00065359 5354454d 00000000 00000000

 00000000 00000000 00000000 00000000 00000001 00000000 00000000 00000000

 00000000 00005480 00000000 00000000 00000000 00000000 00000000 000a000a

 21a23e71 00000001 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

 00000000 d0d59fee 000bd5c4 29797a28 00019268 000000c4 00000002 00100001

 <32 bytes per line>

d0d59fee 000b表示为X$KCVFH中的FHSCN，表示CHECKPOINT_CHANGE#, Datafile checkpoint change#, Updated on every checkpoint, but not when in Hot backup state (not online backups). This must remain untouched when you are in hot backup mode, because you might get checkpoints between the BEGIN BACKUP and when you actually start the copying process.，d0d59fee为scn base，000b为scn wrap.

这里的CHECKPOINT_CHANGE#是d0d59fee 000b，利用公式scn_wrap*4294967296+scn_bas就可以将其转换成我们熟悉的SCN，过程如下：

SQL> select to_number('b','XXXX') from dual;

TO_NUMBER('B','XXXX')

---------------------

                   11

SQL> select to_number('d0d59fee','XXXXXXXX') from dual;

TO_NUMBER('D0D59FEE','XXXXXXXX

------------------------------

                    3503661038

SQL> select 11*4294967296+3503661038 from dual;

11*4294967296+3503661038

------------------------

             50748301294

SQL> select scn_to_timestamp(50748301294) from dual;

SCN_TO_TIMESTAMP(50748301294)

--------------------------------------------------------------------------------

25-8月 -09 01.16.22.000000000 下午

朋友们明白为什么这里要乘4294967296吗？

其实403e里已经用如下这段话解释清楚了----The SCN base (4 bytes) is incremented for each SCN allocation. The SCN wrap (2 bytes) is incremented when the base turns over.

也就是说当4个byte的SCN base满了后就将SCN base清0，同时在SCN wrap上加1，周而复始，这就是上面那段话里"turns over"的含义。

SCN base是4个byte，也就是16的8次方：

SQL> select power(16,8) from dual;

POWER(16,8)

-----------

 4294967296

这就是4294967296的来历。