十七、非归档 undo丢失 恢复测试
前面曾提到非归档,undo丢失,存在未提交事务,且是abort方式时,在备份的情况下无法恢复,
情况其实是这样的,单也可以说不是,毕竟我们还是可以使用特殊手段,比如BBED 当然oracle文档中,并未提及,所以说无法恢复。
下面是我的测试
SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) blk# from undo$;
FILE# BLK#
———- ———-
1 106
1 106
1 106
1 106
1 106
1 106
1 106
1 106
1 106
1 106
1 106
FILE# BLK#
———- ———-
1 106
1 106
1 106
1 106
1 106
1 106
1 106
1 106
1 106
1 106
21 rows selected.
SQL>
SQL> desc undo$
Name Null? Type
————————– ——– ——————————————–
US# NOT NULL NUMBER
NAME NOT NULL VARCHAR2(30)
USER# NOT NULL NUMBER
FILE# NOT NULL NUMBER
BLOCK# NOT NULL NUMBER
SCNBAS NUMBER
SCNWRP NUMBER
XACTSQN NUMBER
UNDOSQN NUMBER
INST# NUMBER
STATUS$ NOT NULL NUMBER —-回滚段状态
TS# NUMBER
UGRP# NUMBER
KEEP NUMBER
OPTIMAL NUMBER
FLAGS NUMBER
SPARE1 NUMBER
SPARE2 NUMBER
SPARE3 NUMBER
SPARE4 VARCHAR2(1000)
SPARE5 VARCHAR2(1000)
SPARE6 DATE

SQL> conn /as sysdba
Connected.
SQL> select owner,segment_name,TABLESPACE_NAME,status from dba_rollback_segs;
OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
—— —————————— —————————— —————-
SYS SYSTEM SYSTEM ONLINE
PUBLIC _SYSSMU1$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU2$ UNDOTBS1 ONLINE
PUBLIC _SYSSMU3$ UNDOTBS1 ONLINE
……省略部分
PUBLIC _SYSSMU10$ UNDOTBS1 ONLINE
11 rows selected.
SQL> select NAME,STATUS$ from undo$;
NAME STATUS$
—————————— ———-
SYSTEM 3
_SYSSMU1$ 3
_SYSSMU2$ 3
_SYSSMU3$ 3
_SYSSMU4$ 3
_SYSSMU5$ 3
_SYSSMU6$ 3
_SYSSMU7$ 3
_SYSSMU8$ 3
_SYSSMU9$ 3 —从这里 我们可以猜测出 3表是online
_SYSSMU10$ 3
NAME STATUS$
—————————— ———-
_SYSSMU11$ 1
_SYSSMU12$ 1
_SYSSMU13$ 1
_SYSSMU14$ 1
_SYSSMU15$ 1
_SYSSMU16$ 1
_SYSSMU17$ 1
_SYSSMU18$ 1
_SYSSMU19$ 1
_SYSSMU20$ 1
21 rows selected.
下面我们将undotbs offline,看看status$会发生什么变化
SQL> select owner,segment_name,TABLESPACE_NAME,status from dba_rollback_segs;
OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
—— —————————— —————————— —————-
SYS SYSTEM SYSTEM ONLINE
PUBLIC _SYSSMU1$ UNDOTBS1 OFFLINE
PUBLIC _SYSSMU2$ UNDOTBS1 OFFLINE
….省略部分
PUBLIC _SYSSMU10$ UNDOTBS1 OFFLINE
11 rows selected.
SQL> select NAME,STATUS$ from undo$;
NAME STATUS$
—————————— ———-
SYSTEM 3
_SYSSMU1$ 2
_SYSSMU2$ 2
_SYSSMU3$ 2
_SYSSMU4$ 2
_SYSSMU5$ 2
_SYSSMU6$ 2
_SYSSMU7$ 2
_SYSSMU8$ 2 —从这里 我们可以知道 2表示offline
_SYSSMU9$ 2
_SYSSMU10$ 2
NAME STATUS$
—————————— ———-
_SYSSMU11$ 1
_SYSSMU12$ 1
_SYSSMU13$ 1
_SYSSMU14$ 1
_SYSSMU15$ 1
_SYSSMU16$ 1
_SYSSMU17$ 1 —结合下面,我们可以看出 1表示已经删除或不存在
_SYSSMU18$ 1
_SYSSMU19$ 1
_SYSSMU20$ 1
21 rows selected.

SQL> alter system dump undo header ‘_SYSSMU11$’;
alter system dump undo header ‘_SYSSMU11$’
*
ERROR at line 1:
ORA-01534: rollback segment ‘_SYSSMU11$’ doesn’t exist
准备工作已经做完了, 下面我们回到以前的问题上来,在恢复的时候报错:
Thread 1: Sequence reset to 1.
ORA-00704: bootstrap process failure
ORA-00604: error occurred at recursive SQL level 2
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: ‘/oracle/product/oradata/roger/undotbs.dbf’
[oracle@roger bdump]$
不管怎么样 都无法open数据库,做10046 trace后,发现其实是在bootstrap$初始化就失败了,失败的sql如下:
elect /*+ rule */ name,file#,block#,status$,user#,undosqn,xactsqn,scnbas,scnwrp,DECODE(inst#,0,NULL,inst#),ts#,spare1
from undo$ where us#=:1

BBED> set file 1 block 106
FILE# 1
BLOCK# 106
BBED> p *kdbr[1]
rowdata[848]
————
ub1 rowdata[848] @5345 0x2c
BBED> x /1rnnnnnnnn
rowdata[848] @5345
————
flag@5345: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5346: 0×00
cols@5347: 17
col 0[2] @5348: 1
col 1[9] @5351: -0
col 2[2] @5361: 1
col 3[2] @5364: 2
col 4[2] @5367: 9
col 5[5] @5370: 1263443
col 6[1] @5376: 0
col 7[3] @5378: 335
col 8[3] @5382: 474
col 9[1] @5386: 0
col 10[2] @5388: 3 —对照前面undo$的结构 我们可以发现这里应该是对应的status$
col 11[2] @5391: 1
col 12[0] @5394: *NULL*
col 13[0] @5395: *NULL*
col 14[0] @5396: *NULL*
col 15[0] @5397: *NULL*
col 16[2] @5398: 1

下面再次尝试做该实验
SQL> startup
ORACLE instance started.
Total System Global Area 234881024 bytes
Fixed Size 1266776 bytes
Variable Size 100666280 bytes
Database Buffers 130023424 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.
SQL> conn roger1/roger1
Connected.
SQL> select count(*) from t1;
COUNT(*)
———-
907
SQL> delete from t1 where rownum <100;
99 rows deleted.
SQL> conn /as sysdba
Connected.
SQL> select file_name,status from dba_data_files;
FILE_NAME STATUS
———————————————————— ———
/oracle/product/oradata/roger/users01.dbf AVAILABLE
/oracle/product/oradata/roger/sysaux01.dbf AVAILABLE
/oracle/product/oradata/roger/undotbs01.dbf AVAILABLE
/oracle/product/oradata/roger/system01.dbf AVAILABLE
/oracle/product/oradata/roger/roger01.dbf AVAILABLE
SQL> shutdown abort;
ORACLE instance shut down.
SQL> ! rm /oracle/product/oradata/roger/undotbs01.dbf
SQL> !ls -ltr /oracle/product/oradata/roger
total 1483044
-rw-r—– 1 oracle dba 20979712 Mar 27 14:55 temp01.dbf
-rw-r—– 1 oracle dba 7872512 Mar 27 15:32 users01.dbf
-rw-r—– 1 oracle dba 524296192 Mar 27 15:32 system01.dbf
-rw-r—– 1 oracle dba 272637952 Mar 27 15:32 sysaux01.dbf
-rw-r—– 1 oracle dba 524296192 Mar 27 15:32 roger01.dbf
-rw-r—– 1 oracle dba 52429312 Mar 27 15:32 redo03.log
-rw-r—– 1 oracle dba 52429312 Mar 27 15:32 redo02.log
-rw-r—– 1 oracle dba 52429312 Mar 27 15:33 redo01.log
-rw-r—– 1 oracle dba 7061504 Mar 27 15:33 control03.ctl
-rw-r—– 1 oracle dba 7061504 Mar 27 15:33 control02.ctl
-rw-r—– 1 oracle dba 7061504 Mar 27 15:33 control01.ctl
SQL> —-存在未提交事务,直接rm undodatafile,且是abort方式。

下面我们用bbed来尝试
BBED> p kdbr
sb2 kdbr[0] @86 8079
sb2 kdbr[1] @88 5277
sb2 kdbr[2] @90 5221
sb2 kdbr[3] @92 5165
sb2 kdbr[4] @94 4319
sb2 kdbr[5] @96 4374
sb2 kdbr[6] @98 4997
sb2 kdbr[7] @100 4940
sb2 kdbr[8] @102 4598
sb2 kdbr[9] @104 4826
sb2 kdbr[10] @106 4540
sb2 kdbr[11] @108 7471
sb2 kdbr[12] @110 7417
sb2 kdbr[13] @112 7363
sb2 kdbr[14] @114 7309
sb2 kdbr[15] @116 7255
sb2 kdbr[16] @118 7201
sb2 kdbr[17] @120 7146
sb2 kdbr[18] @122 7091
sb2 kdbr[19] @124 7036
sb2 kdbr[20] @126 6981
我们知道当前可用的回滚段是11个(虽然这里显示为21个,其中有10已经被删除了),kdbr[0]是对应的system 回滚段
那也就是说 从kdbr[1] 到 kdbr[10]就是我们的undotbs1
如下所示:
下面用bbed进行修改
BBED> p *kdbr[1]
rowdata[958]
————
ub1 rowdata[958] @5345 0x2c
BBED> x /1rncnnnnnnnnnnn
rowdata[958] @5345
————
flag@5345: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@5346: 0×01
cols@5347: 17
col 0[2] @5348: 1
col 1[9] @5351: _SYSSMU1$
col 2[2] @5361: 1
col 3[2] @5364: 2
col 4[2] @5367: 9
col 5[5] @5370: 1286346
col 6[1] @5376: 0
col 7[3] @5378: 337
col 8[3] @5382: 474
col 9[1] @5386: 0
col 10[2] @5388: 2
col 11[2] @5391: 1
col 12[0] @5394: *NULL*
col 13[0] @5395: *NULL*
col 14[0] @5396: *NULL*
col 15[0] @5397: *NULL*
col 16[2] @5398: 1

BBED> modify /x 02 offset 5390
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /oracle/product/oradata/roger/system01.dbf (1)
Block: 106 Offsets: 5390 to 5901 Dba:0x0040006a
————————————————————————
0202c102 ffffffff 02c1022c 001102c1 0a095f53 5953534d 55392402 c10202c1
…省略部分内容
<32 bytes per line>
…中间的kdbr[2] ~kdbr[10] 省略。。。
BBED> sum apply
Check value for File 1, Block 106:
current = 0xbb06, required = 0xbb06
BBED> exit
下面来看下成果
SQL> startup mount
ORACLE instance started.
Total System Global Area 234881024 bytes
Fixed Size 1266776 bytes
Variable Size 100666280 bytes
Database Buffers 130023424 bytes
Redo Buffers 2924544 bytes
Database mounted.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 2 – see DBWR trace file
ORA-01110: data file 2: ‘/oracle/product/oradata/roger/undotbs01.dbf’

SQL> alter database datafile ‘/oracle/product/oradata/roger/undotbs01.dbf’ offline drop;
Database altered.
SQL> startup mount pfile=’/oracle/a.ora’;
ORACLE instance started.
Total System Global Area 234881024 bytes
Fixed Size 1266776 bytes
Variable Size 100666280 bytes
Database Buffers 130023424 bytes
Redo Buffers 2924544 bytes
Database mounted.
前面由于当时bbed的时候,改错了 开始,,,但是我又不知道原值,所以我recover了一把 这不影响测试
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: ‘/oracle/product/oradata/roger/system01.dbf’

SQL> startup mount pfile=’/oracle/a.ora’; —其中我使用了隐含参数
ORACLE instance started.
Total System Global Area 234881024 bytes
Fixed Size 1266776 bytes
Variable Size 100666280 bytes
Database Buffers 130023424 bytes
Redo Buffers 2924544 bytes
Database mounted.
SQL> alter database open resetlogs;
Database altered.
SQL> select owner,segment_name,TABLESPACE_NAME,status from dba_rollback_segs;
OWNER SEGMENT_NAME TABLESPACE_NAME STATUS
—— —————————— —————————— —————-
SYS SYSTEM SYSTEM ONLINE
SQL> select NAME,STATUS$ from undo$;
NAME STATUS$
————— ———-
SYSTEM 3
_SYSSMU1$ 1
_SYSSMU2$ 1
…..省略部分
_SYSSMU19$ 1
_SYSSMU20$ 1
21 rows selected.
SQL> conn roger1/roger1
Connected.
SQL> select count(*) from t1;
COUNT(*)
———-
808
SQL> —丢失了部分未提交事务
既然open成功了,那剩下来的时期就是重建undo了。

Trackback

no comment untill now

Add your comment now

切换到手机版