[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"

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

ktetbdba=$[ ktetbdba + 1 ]

#echo $ktetbdba

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

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

begin=$[ begin + 1 ]

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

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 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

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硬性編碼取出對於值.不能保證你遇到類似問題,直接拿來使用.^_^

--//何況真實的生產系統可能比我在測試環境遇到的問題更加複雜.

[20190130]刪除tab$記錄的恢復2.txt

相關文章