[20190130]删除tab$记录的恢复2.txt

--//前面链接写好了脚本,开始测试删除后的恢复.千万不要在生产系统做这样的测试!!

--//参考链接:http://blog.itpub.net/267265/viewspace-2565245/=>[20190130]删除tab$记录的恢复.txt

1.环境:

SCOTT@book> @ ver1

PORT_STRING                    VERSION        BANNER

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

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table ORACHK001 tablespace system  as select * from sys.tab$;

SYS@book> create table ORACHK001 tablespace system  as select * from sys.tab$;

Table created.

SYS@book> select count(*) from sys.tab$;

  COUNT(*)

----------

      2966

SYS@book> select count(*) from orachk001;

  COUNT(*)

----------

      2965

--//这样建立的表不包含orachk001.

SYS@book> select * from sys.tab$ minus select * from orachk001;

...

SYS@book> select  OBJ# ,  DATAOBJ# from sys.tab$ minus select OBJ# ,  DATAOBJ# from orachk001;

      OBJ#   DATAOBJ#

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

     91090      91090

SYS@book> insert into orachk001 select * from sys.tab$ where (OBJ#,DATAOBJ#) in ((91090,91090));

1 row created.

SYS@book> commit ;

Commit complete.

2.做一个冷备份:

--//关闭数据库略.

$ cp -r /mnt/ramdisk/book /home/oracle/backup/

--//重启数据库.

SYS@book> startup

ORACLE instance started.

Total System Global Area  643084288 bytes

Fixed Size                  2255872 bytes

Variable Size             205521920 bytes

Database Buffers          427819008 bytes

Redo Buffers                7487488 bytes

Database mounted.

Database opened.

SYS@book> delete from sys.tab$;

2966 rows deleted.

SYS@book> commit ;

Commit complete.

SYS@book> alter system checkpoint ;

System altered.

SYS@book> alter system checkpoint ;

System altered.

SYS@book> alter system checkpoint ;

System altered.

SYS@book> shutdown immediate ;

ORA-00957: duplicate column name

SYS@book> shutdown abort ;

ORACLE instance shut down.

--//再保留一份坏的备份.

$ mv /home/oracle/backup/book /home/oracle/backup/book_20190122_good

`/home/oracle/backup/book' -> `/home/oracle/backup/book_20190122_good'

$ cp -r /mnt/ramdisk/book /home/oracle/backup/

$ mv /home/oracle/backup/book /home/oracle/backup/book_20190122_bad

`/home/oracle/backup/book' -> `/home/oracle/backup/book_20190122_bad'

3.开始恢复:

--//执行如下脚本,自己建立目录/home/oracle/zzz430,zdate,rlbbed是别名.看前面的链接.

--//脚本scan.sh:

#! /bin/bash

/bin/rm /home/oracle/zzz430/bbed/scan*.txt

cd /home/oracle/zzz430/bbed

echo "process 1 start : `zdate` scan dba 1,144 ,  create scan1.txt about ktetbdba,ktetbnbk"

echo "p /d dba 1,144"  ktetb | rlbbed | egrep 'ktetbdba|ktetbnbk' | cut -c8-16,55- |tr " " "=" | paste -d ";" - - > scan1a.txt

high_water=`echo p /d dba 1,144 ktech.hwmark_ktech.blkno_ktehw | rlbbed | grep blkno_ktehw | cut -c60- | tr -d " " `

sed "\$s/ktetbnbk=.*$/ktetbnbk=$high_water/" scan1a.txt > scan1.txt

read -p "process 1 finish: `zdate`,enter continue..."

echo "process 2 start : `zdate` scan block , get kdbtnrow,kdbtoffs ang grep kdbtnrow=0"

cat scan1.txt | while read i

do

        eval $i

        #echo $ktetbdba  $ktetbnbk

    for ((j=1; j<=$ktetbnbk ; j++))

    do

                echo -n "dba=$ktetbdba;" >> scan2a.txt

                echo "p /d dba $ktetbdba offset 0 kdbt[1]" | rlbbed | egrep 'kdbtoffs|kdbtnrow' |  cut -c8-16,55- |tr " " "=" | paste -d ";" - - >> scan2a.txt

                ktetbdba=$[ ktetbdba + 1 ]

                #echo $ktetbdba

        done

done

grep -v kdbtnrow=0 scan2a.txt > scan2.txt

read -p "process 2 finish: `zdate`,enter continue..."

echo "process 3 start : `zdate` scan block , create bbed'script scan3_bbed.txt for modify delete of flag  and create scan4a.txt about block of ckix "

cat scan2.txt | while read i

do

        eval $i

    begin=$kdbtoffs

        end=$[ kdbtoffs + kdbtnrow -1 ]

        #  echo $dba $kdbtoffs $kdbtnrow  $begin $end

        kdbr_size=`echo map dba $dba| rlbbed | grep "sb2 kdbr" | sed -e "s/^.*\[//" -e "s/].*$//" `

        while [ $begin -le $end ]

        do

                kdbr_off=`echo p dba $dba offset 0 kdbr | rlbbed | grep "\[$begin\]" | cut -c55-`

                if [ $kdbr_off -gt $kdbr_size ]

                then

                        echo "x /rc dba $dba *kdbr[$begin]" | rlbbed | grep '^flag@' | grep KDRHFD | sed -e 's/^flag@/offset=/' -e 's/ (.*)//' -e 's/: /;value=/' | tr -d " " | while read k

                        #echo "x /rc dba $dba *kdbr[$begin]" | rlbbed | grep '^flag@' | sed -e 's/^flag@/offset=/' -e 's/ (.*)//' -e 's/: /;value=/' | tr -d " " | while read k

                        do

                                eval $k

                                #echo $dba $offset $value

                                value=`printf "0x%x" $(( value - 0x10  )) `

                                echo "assign /x dba $dba offset $offset = $value " >> scan3_bbed.txt

                        done

                        #  if not found ckix@ and  found flag=0x7c , then ckix_value=0, and do not process chained row.

                        echo "x /rx dba $dba *kdbr[$begin]" | rlbbed | grep "^ckix@" | sed -e "s/^ckix/dba=$dba;/" -e  's/@.*:/ckix_value=/' | tr -d " " >> scan4a.txt

                        echo "x /rx dba $dba *kdbr[$begin]" | rlbbed | egrep "^ckix@" > /dev/null

                        if [ $? -eq 1 ]

                        then

                                echo "x /rx dba $dba *kdbr[$begin]" | rlbbed | egrep "^^flag@.*: *0x7c" > /dev/null

                                if [ $? -eq 0 ]

                                then

                                        echo "dba=$dba;ckix_value=0" >> scan4a.txt

                                fi

                        fi

                fi

                begin=$[ begin + 1 ]

        done

done

read -p "process 3 finish: `zdate`,enter continue..."

echo "process 4 start : `zdate` create bbed's scan4_bbed.txt for modify cluster of mref of value "

sort scan4a.txt | uniq > scan4b.txt

cat scan4b.txt | while read i

do

        eval $i

        #echo $dba  $ckix_value

        echo -n "dba=$dba;" >> scan4c.txt

        echo "x /rn dba $dba *kdbr[$ckix_value]" | rlbbed | egrep "^kref@|^mref@" | sed -e "s/@/_offset=/" -e "s/:/;value=/" | tr -d " " | paste -d ";" - -  >> scan4c.txt

done

sed -e 's/;$/;mref_offset=0;value=0/' -e 's/value=/valuek=/' scan4c.txt > scan4.txt

cat scan4.txt | while read i

do

    eval $i

    # echo $dba $kref_offset $valuek $mref_offset $value

    if [ $mref_offset -eq 0 ]

    then

        mref_offset=$[ $kref_offset+ 2 ]

    fi

    valuem=$[ value + 1 ]

    if [ $valuem -lt $valuek ]

    then

        echo "assign dba $dba offset $mref_offset = $valuem" >> scan4m_bbed.txt

    else

        echo "assign dba $dba offset $mref_offset = $valuek" >> scan4k_bbed.txt

    fi

done

read -p "process 4 finish: `zdate`,enter continue..."

echo "process 5 start : create bbed's scan5_bbed.txt for sum apply"

sed -e 's/^dba=/sum apply dba /' -e 's/;.*$//' scan2.txt > scan5_bbed.txt

read -p "process 5 finish: `zdate`,enter continue..."

--//执行脚本scan.sh.

$ . scan.sh

process 1 start : 2019/01/30 10:20:32 scan dba 1,144 ,  create scan1.txt about ktetbdba,ktetbnbk

process 1 finish: 2019/01/30 10:20:33,enter continue...

process 2 start : 2019/01/30 10:20:33 scan block , get kdbtnrow,kdbtoffs ang grep kdbtnrow=0

process 2 finish: 2019/01/30 10:21:04,enter continue...

process 3 start : 2019/01/30 10:21:28 scan block , create bbed'script scan3_bbed.txt for modify delete of flag  and create scan4a.txt about block of ckix

process 3 finish: 2019/01/30 10:26:36,enter continue...

process 4 start : 2019/01/30 10:26:42 create bbed's scan4_bbed.txt for modify cluster of mref of value

process 4 finish: 2019/01/30 10:27:47,enter continue...

process 5 start : create bbed's scan5_bbed.txt for sum apply

process 5 finish: 2019/01/30 10:27:52,enter continue...

--//主要时间消耗在第3步,大约需要5分多钟.看看生成修改的脚本:

$ ls -l scan*bbed*

-rw-r--r-- 1 oracle oinstall 124731 2019-01-30 10:26:36 scan3_bbed.txt

-rw-r--r-- 1 oracle oinstall 105556 2019-01-30 10:27:47 scan4k_bbed.txt

-rw-r--r-- 1 oracle oinstall     70 2019-01-30 10:27:47 scan4m_bbed.txt

-rw-r--r-- 1 oracle oinstall  12782 2019-01-30 10:27:52 scan5_bbed.txt

$ head scan3_bbed.txt

assign /x dba 4194449 offset 7884 = 0x6c

assign /x dba 4194449 offset 7756 = 0x6c

assign /x dba 4194449 offset 7632 = 0x6c

assign /x dba 4194449 offset 7512 = 0x6c

assign /x dba 4194449 offset 7388 = 0x6c

assign /x dba 4194449 offset 7266 = 0x6c

assign /x dba 4194449 offset 7138 = 0x6c

assign /x dba 4194450 offset 509 = 0x6c

assign /x dba 4194450 offset 7882 = 0x6c

assign /x dba 4194450 offset 7756 = 0x6c

$ head scan4k_bbed.txt

assign dba 4194449 offset 8171 = 17

assign dba 4194449 offset 8149 = 15

assign dba 4194449 offset 8127 = 27

assign dba 4194449 offset 8083 = 11

assign dba 4194449 offset 8061 = 20

assign dba 4194449 offset 8039 = 8

assign dba 4194449 offset 8017 = 34

assign dba 4194450 offset 8149 = 21

assign dba 4194450 offset 8105 = 34

assign dba 4194450 offset 8083 = 27

$ head scan4m_bbed.txt

assign dba 4288539 offset 8169 = 1

assign dba 4288546 offset 8145 = 1

$ head scan5_bbed.txt

sum apply dba 4194449

sum apply dba 4194450

sum apply dba 4194451

sum apply dba 4194452

sum apply dba 4194453

sum apply dba 4194454

sum apply dba 4194455

sum apply dba 4194456

sum apply dba 4194457

sum apply dba 4194458

--//注意检查修改flag不是0x6c的记录.

$ grep -v "0x6c $" scan3_bbed.txt

assign /x dba 4194451 offset 7349 = 0x20

assign /x dba 4197642 offset 7888 = 0x20

assign /x dba 4207636 offset 7087 = 0x20

assign /x dba 4225801 offset 3621 = 0x4c

assign /x dba 4225801 offset 4436 = 0x4c

assign /x dba 4288537 offset 7717 = 0x4c

$ grep  "0x6c $" scan3_bbed.txt |wc

   2965   23720  124479

--//可以发现dba=4194451发生了行迁移行,删除后一样存在标识KDRHFD.(注:101是好的system01.dbf文件)

BBED> x /rx dba 4194451 *kdbr[14]

rowdata[6848]                               @7349

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

flag@7349: 0x30 (KDRHFD, KDRHFH)

lock@7350: 0x02

cols@7351:    0

BBED> x /rx dba 101,147 *kdbr[14]

rowdata[6848]                               @7349

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

flag@7349: 0x20 (KDRHFH)

lock@7350: 0x02

cols@7351:    0

nrid@7352:0x00407b09.1

--//dba=4197642也是原来发生了行迁移.

BBED> x /rx dba 4197642 *kdbr[8]

rowdata[7431]                               @7888

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

flag@7888: 0x30 (KDRHFD, KDRHFH)

lock@7889: 0x02

cols@7890:    0

BBED> x /rx dba 101,3338 *kdbr[8]

rowdata[7431]                               @7888

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

flag@7888: 0x20 (KDRHFH)

lock@7889: 0x00

cols@7890:    0

nrid@7891:0x00407b09.0

--//dba=4207636也是原来发生了行迁移.

BBED> x /rx dba 4207636 *kdbr[16]

rowdata[6599]                               @7087

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

flag@7087: 0x30 (KDRHFD, KDRHFH)

lock@7088: 0x02

cols@7089:    0

BBED> x /rx dba 101,13332 *kdbr[16]

rowdata[6599]                               @7087

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

flag@7087: 0x20 (KDRHFH)

lock@7088: 0x00

cols@7089:    0

nrid@7090:0x00417019.2

--//分析assign /x dba 4225801 offset 3621 = 0x4c的情况.

BBED> x /rx dba 4225801 *kdbr[10]

rowdata[0]                                  @3621

----------

flag@3621: 0x5c (KDRHFL, KDRHFF, KDRHFD, KDRHFC)

lock@3622: 0x02

cols@3623:    0

ckix@3624:    6

BBED> x /rx dba 101,31497 *kdbr[10]

rowdata[0]                                  @3621

----------

flag@3621: 0x4c (KDRHFL, KDRHFF, KDRHFC)

lock@3622: 0x00

cols@3623:   31

ckix@3624:    6

hrid@3625:0x00400d0a.1

col    0[3] @3631:  0xc2  0x06  0x1b

col    1[1] @3635:  0x80

--//0x400d0a=4197642,4197642= alter system dump datafile 1 block 3338,实际上行迁移的部分.

--//实际上你可以看出我多恢复了几条. 3+2965 = 2968,这样多恢复2条.

--//我写脚本扫描块,取出tailchk值,发现大部分都是0x5f5[cdef]的信息.

$ cat scanx.sh

#! /bin/bash

grep dba scan3_bbed.txt | cut -d" " -f4 | uniq | while read dba

do

        echo -n $dba :

        echo "p dba $dba offset 8188"| rlbbed | grep "ub4 tailchk"

done

$ . scanx.sh | cut -c64-71 | sort|uniq -c

      1 0x2d3e06

     69 0x5f5c06

      5 0x5f5d06

    499 0x5f5f06

      1 0xdde306

$ . scanx.sh | grep -v  '0x5f5[0-9abcdef]06'

4288539 :ub4 tailchk                                 @8188     0xdde30601

4288546 :ub4 tailchk                                 @8188     0x2d3e0601

$ egrep -n '4288539|4288546' scan3_bbed.txt

2962:#assign /x dba 4288539 offset 7920 = 0x6c

2971:#assign /x dba 4288546 offset 7851 = 0x6c

--//可以确定这两个块不需要修改,因为scn号相差太大.而且正好2条记录,估计以前删除表留下痕迹,与前面的信息正好吻合.

--//注解对应行取消它的执行.

$ cat scan4m_bbed.txt

assign dba 4288539 offset 8169 = 1

assign dba 4288546 offset 8145 = 1

--//以上脚本也许不需要执行.但是还有如下tailchk不是0x5f5f是什么回事呢?

     69 0x5f5c06

      5 0x5f5d06

--//实际上这个是延迟块块提交的产物,当dml修改块很多的情况下,仅仅部分块做快速提交(好像是缓存的某个百分比).

--//也就是有74块需要特殊处理.不然在open时报错,或者数据库直接关闭.参考链接:

--//http://blog.itpub.net/267265/viewspace-2564717/ => [20190124]bbed恢复数据遇到延迟块清除的问题2.txt

--//也就是oracle对于system表空间检测更加严格,不知道有什么参数可以临时关闭或者绕过这种检测.

4.执行生成的bbed脚本.

bbed parfile=/home/oracle/bbed/bbed.par cmdfile=/home/oracle/zzz430/bbed/scan3_bbed.txt

bbed parfile=/home/oracle/bbed/bbed.par cmdfile=/home/oracle/zzz430/bbed/scan4k_bbed.txt

bbed parfile=/home/oracle/bbed/bbed.par cmdfile=/home/oracle/zzz430/bbed/scan5_bbed.txt

--//注意输入Y.

--//如果通过管道执行脚本,必须修改脚本在第2行加入Y.

5.禁用sys.tab$的索引I_TAB1.

--//这样恢复，索引与表存在不一致情况，要禁用sys.tab$的索引I_TAB1.

BBED> x /rnnc dba 1,523 *kdbr[9]

rowdata[1269]                               @4910

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

flag@4910: 0x2c (KDRHFL, KDRHFF, KDRHFH)

lock@4911: 0x01

cols@4912:    3

col    0[2] @4913: 33

col    1[2] @4916: 33

col  2[189] @4919: CREATE INDEX I_TAB1 ON TAB$(BOBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483

645 PCTINCREASE 0 OBJNO 33 EXTENTS (FILE 1 BLOCK 312))

--//设置flag=3c,表示删除.

BBED> assign /x  dba 1,523 offset 4910= 0x3c

Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y

ub1 rowdata[0]                              @4910     0x3c

BBED> sum apply dba 1,523

Check value for File 1, Block 523:

current = 0x7e6b, required = 0x7e6b

6.启动数据库看看:

SYS@book> create pfile='/tmp/@.ora' from spfile ;

File created.

--//修改/tmp/book.ora文件，加入修改如下:

*._system_trig_enabled=false

*.job_queue_processes=0

--//启动遇到如下错误:

ORA-00600: internal error code, arguments: [kdBlkCheckError], [1], [94232], [6110], [], [], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [kdBlkCheckError], [1], [9951], [6110], [], [], [], [], [], [], [], []

ORA-00600: internal error code, arguments: [kdBlkCheckError], [1], [31548], [6110], [], [], [], [], [], [], [], []

--//实际上我修复dba=1,94232后出现dba=1,9951.接着修复,接着在出现1,31548.而且这块还标识为坏块.即使启动ok,错误不断,都是

--//涉及tab$的块记录.实际上就是我前面提到延迟提交的问题.

--//分析dba=1,94232

BBED> set dba 1 , 94232

        DBA             0x00417018 (4288536 1,94232)

BBED> p tailchk

ub4 tailchk                                 @8188     0x5f5d0601

--//可以这些都是延迟块提交导致对应块没有更新.

BBED> p dba 1, 94232 ktbbh.ktbbhitl[1]

struct ktbbhitl[1], 24 bytes                @68

   struct ktbitxid, 8 bytes                 @68

      ub2 kxidusn                           @68       0x0005

      ub2 kxidslt                           @70       0x0018

      ub4 kxidsqn                           @72       0x00000751

   struct ktbituba, 8 bytes                 @76

      ub4 kubadba                           @76       0x00c0050f

      ub2 kubaseq                           @80       0x04eb

      ub1 kubarec                           @82       0x0b

   ub2 ktbitflg                             @84       0x0001 (NONE)

   union _ktbitun, 2 bytes                  @86

      sb2 _ktbitfsc                         @86       126

      ub2 _ktbitwrp                         @86       0x007e

   ub4 ktbitbas                             @88       0x00000000

BBED> p   dba 1, 9951 ktbbh.ktbbhitl[1]

struct ktbbhitl[1], 24 bytes                @68

   struct ktbitxid, 8 bytes                 @68

      ub2 kxidusn                           @68       0x0005

      ub2 kxidslt                           @70       0x0018

      ub4 kxidsqn                           @72       0x00000751

   struct ktbituba, 8 bytes                 @76

      ub4 kubadba                           @76       0x00c0013f

      ub2 kubaseq                           @80       0x04de

      ub1 kubarec                           @82       0x1b

   ub2 ktbitflg                             @84       0x0005 (NONE)

   union _ktbitun, 2 bytes                  @86

      sb2 _ktbitfsc                         @86       695

      ub2 _ktbitwrp                         @86       0x02b7

   ub4 ktbitbas                             @88       0x00000000

BBED> p  dba 1, 31548 ktbbh.ktbbhitl[1]

struct ktbbhitl[1], 24 bytes                @68

   struct ktbitxid, 8 bytes                 @68

      ub2 kxidusn                           @68       5

      ub2 kxidslt                           @70       24

      ub4 kxidsqn                           @72       1873

   struct ktbituba, 8 bytes                 @76

      ub4 kubadba                           @76       12583968

      ub2 kubaseq                           @80       1251

      ub1 kubarec                           @82       19

   ub2 ktbitflg                             @84       5 (NONE)

   union _ktbitun, 2 bytes                  @86

      sb2 _ktbitfsc                         @86       570

      ub2 _ktbitwrp                         @86       570

   ub4 ktbitbas                             @88       0

BBED> p  dba 1, 31548 ktbbh.ktbbhitl[1]

struct ktbbhitl[1], 24 bytes                @68

   struct ktbitxid, 8 bytes                 @68

      ub2 kxidusn                           @68       0x0005

      ub2 kxidslt                           @70       0x0018

      ub4 kxidsqn                           @72       0x00000751

   struct ktbituba, 8 bytes                 @76

      ub4 kubadba                           @76       0x00c00420

      ub2 kubaseq                           @80       0x04e3

      ub1 kubarec                           @82       0x13

   ub2 ktbitflg                             @84       0x0005 (NONE)

   union _ktbitun, 2 bytes                  @86

      sb2 _ktbitfsc                         @86       570

      ub2 _ktbitwrp                         @86       0x023a

   ub4 ktbitbas                             @88       0x00000000

   

---//注意.xid=0x0005.0x0018.0x00000751.其对应的ktbitbas是0. 其ktbitflg也可以看出没有提交.

--//我单独写一个脚本:

$ cat scanx.sh

#! /bin/bash

grep dba scan3_bbed.txt | cut -d" " -f4 | uniq | while read dba

do

        echo -n $dba :

        echo "p dba $dba offset 8188"| rlbbed | grep "ub4 tailchk"

done

$ . scanx.sh | grep -v 0x5f5f06 >| clearout.txt

--//先取出taichk不是5f5f的记录.注意删除dba=4288539,4288546 两行.

$ cat scana.sh

#! /bin/bash

cat clearout.txt | while read dba

do

    echo set dba $dba

    echo -n "assign "

    echo -e  "set dba $dba \np ktbbh" |rlbbed | /bin/grep -B 4 -A 9 "ub4 kxidsqn \+@.*0x00000751$"| egrep "ktbbhitl|ktbitflg" | cut -c11-21,55-60 | paste -d. - - | sed -e 's/   /=/'

    echo sum apply dba $dba

done

--//注:仅仅读取存在ub4 kxidsqn @72 0x00000751,不大可能别的ITL槽kxidsqn也正好是这个值.

$ . scana.sh >| clearout_bbed.txt

$ head  clearout_bbed.txt

set dba 4204236

assign ktbbhitl[1].ktbitflg=0x0002

sum apply dba 4204236

set dba 4204237

assign ktbbhitl[1].ktbitflg=0x0003

sum apply dba 4204237

set dba 4204241

assign ktbbhitl[1].ktbitflg=0x0002

sum apply dba 4204241

set dba 4204243

--//使用vim执行:%s/=0x00/=0x20/g.也就是设置提交标识.注意检查替换是否74行.

--//不想在写脚本了.^_^.

$ bbed parfile=/home/oracle/bbed/bbed.par cmdfile=/home/oracle/zzz430/bbed/clearout_bbed.txt

$ bbed parfile=/home/oracle/bbed/bbed.par cmdfile=/home/oracle/zzz430/bbed/scan5_bbed.txt

SYS@book> startup pfile='/tmp/book.ora'

ORACLE instance started.

Total System Global Area  643084288 bytes

Fixed Size                  2255872 bytes

Variable Size             205521920 bytes

Database Buffers          427819008 bytes

Redo Buffers                7487488 bytes

Database mounted.

Database opened.

SYS@book> shutdown  immediate ;

Database closed.

Database dismounted.

ORACLE instance shut down.

--//OK.马上关闭以只读打开看看.

SYS@book> startup open read only pfile='/tmp/book.ora'

ORACLE instance started.

Total System Global Area  643084288 bytes

Fixed Size                  2255872 bytes

Variable Size             205521920 bytes

Database Buffers          427819008 bytes

Redo Buffers                7487488 bytes

Database mounted.

Database opened.

select /*+ full(tab$) */ * from tab$;

--//没有任何错误.

SYS@book> select count(*) from tab$;

  COUNT(*)

----------

      2966

SYS@book> select /*+ full(tab$) */ count(*) from tab$;

  COUNT(*)

----------

      2966

--//完全能与前面的对上.

SYS@book> select * from sys.tab$ minus select * from orachk001;

no rows selected

SYS@book> select * from orachk001 minus select * from sys.tab$;

no rows selected

--//几乎完美恢复.

--//一般情况下这样的数据库做好导出到另外的数据库,执行如下类似导出操作,没有任何问题.仅仅owner=oe出现如下错误:

$ exp system/oracle file=a.dmp owner=oe BUFFER=8388608

Export: Release 11.2.0.4.0 - Production on Wed Jan 30 11:56:02 2019

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified users ...

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user OE

. exporting PUBLIC type synonyms

. exporting private type synonyms

. exporting object type definitions for user OE

About to export OE's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

EXP-00056: ORACLE error 8181 encountered

ORA-08181: specified number is not a valid system change number

ORA-06512: at "SYS.XMLTYPE", line 138

EXP-00000: Export terminated unsuccessfully

--//不过我读写模式下打开数据库没有问题,视乎执行如下:

SELECT VALUE (p$)

  FROM "XDB"."XDB$SCHEMA" AS OF SNAPSHOT ( :2) p$

 WHERE SYS_NC_OID$ = :1

--//我在toad下schema模式下点击data(open read only),调用如下sql语句:

SYS@book> SELECT X.SYS_NC_ROWINFO$.GetClobVal () AS SYS_NC_ROWINFO$ FROM XDB.XDB$SCHEMA X;

ERROR:

ORA-08181: specified number is not a valid system change number

ORA-06512: at "SYS.XMLTYPE", line 138

no rows selected

--//有点奇怪的是在dg下active dataguard mode下(read only),不报错.另外写一篇blog分析这个问题.

4.后记:

--//我脚本实际上元旦之前就写差不多,当然还有许多细节没有考虑好.比如行迁移或者链接问题.

--//还有bbed 如果ckix=0,mref=0不显示的问题.

--//还有就是延迟提交导致的问题,等等许多细节几乎导致我放弃恢复测试.

--//使用bash shell写脚本实际上执行效率很低的操作,通过这个恢复,了解许多oracle cluster table许多相关知识,

--//还有一些细节的处理,许多编码是使用cut硬性编码取出对于值.不能保证你遇到类似问题,直接拿来使用.^_^

--//何况真实的生产系统可能比我在测试环境遇到的问题更加复杂.