[20190130]刪除tab$記錄的恢復2.txt--//前面鏈接寫好了腳本,開始測試刪除後的恢復.千萬不要在生產系統做這樣的測試!!--//參考鏈接:http://blog.itpub.net/267265/viewspace-2565245/=>[20190130]刪除tab$記錄的恢復.tx ...
[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硬性編碼取出對於值.不能保證你遇到類似問題,直接拿來使用.^_^
--//何況真實的生產系統可能比我在測試環境遇到的問題更加複雜.
