[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硬性编码取出对于值.不能保证你遇到类似问题,直接拿来使用.^_^
--//何况真实的生产系统可能比我在测试环境遇到的问题更加复杂.
相关文章
- 02-17[20190212]删除tab$记录的恢复3.txt
- 02-17[20190130]删除tab$记录的恢复2.txt
- 02-17[20190226]删除tab$记录的恢复6.txt
- 02-17一个“未雨绸缪”的办法，解决安卓手机微信删除记录不能恢复的问题
- 02-17（win+linux）双系统，删除linux系统的条件下，删除grub引导记录，恢复windows引导
- 02-17Oracle数据文件在open状态被删除的恢复记录
- 02-17Oracle数据文件在open状态被删除的恢复记录
- 02-17[20190130]删除tab$记录的恢复.txt
- 02-17[20180228]11g删除表空间的恢复2.txt