我们在做recover的时候，如果归档文件丢失，就会报ORA-01113错误，这个错误有不同的恢复方法，可以使用隐含参数，我们这里介绍如何使用bbed来恢复这个错误
[oracle@localhost xiaoming]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Thu Oct 24 11:15:43 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> col name for a55
SQL> set lines 120
SQL> select file#,status,name from v$datafile;
FILE# STATUS NAME
---------- ------- -------------------------------------------------------
1 SYSTEM /opt/app/oracle/oradata/goolen/system01.dbf
2 ONLINE /opt/app/oracle/oradata/goolen/sysaux01.dbf
3 ONLINE /opt/app/oracle/oradata/goolen/undotbs01.dbf
4 ONLINE /opt/app/oracle/oradata/goolen/users01.dbf
--这里，我们使用offline datafile的方法来实现ORA-01113错误
SQL> alter database datafile 4 offline;
Database altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
--online datafile的时候报错ORA-01113
SQL> alter database datafile 4 online;
alter database datafile 4 online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/opt/app/oracle/oradata/goolen/users01.dbf'
SQL> alter system checkpoint;
System altered.
--查看控制文件中记录的scn
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1733219
2 1733219
3 1733219
4 1728090
--文件头scn
SQL> select file#,online_status,change# from v$recover_file;
FILE# ONLINE_ CHANGE#
---------- ------- ----------
4 OFFLINE 1728090
--下面我们使用bbed来修改datafile header的信息
下面四项跟数据文件scn相关：
kscnbas (at offset 484) - SCN of last change to the datafile.
kcvcptim (at offset 492) -Time of the last change to the datafile.
kcvfhcpc (at offset 140) - Checkpoint count.
kcvfhccc (at offset 148) - Unknown, but is always 1 less than thecheckpoint point count.
--使用bbed来修改这四个相关信息
[oracle@localhost bbed]$ bbed parfile=bbed.par
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Thu Oct 24 11:22:06 2013
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> info
File# Name Size(blks)
----- ---- ----------
1 /opt/app/oracle/oradata/goolen/system01.dbf 87040
2 /opt/app/oracle/oradata/goolen/sysaux01.dbf 71680
3 /opt/app/oracle/oradata/goolen/undotbs01.dbf 3840
4 /opt/app/oracle/oradata/goolen/users01.dbf 7040
BBED> set dba 4,1
DBA 0x01000001 (16777217 4,1)
BBED> p kcvfhckp.kscnbas
BBED-00451: (kscnbas) is not a member of structure (kcvcp)
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x001a5e5a
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x31735662
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000037
ub4 kcrbabno @504 0x00000002
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x000000a9
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x000000a8
BBED>
BBED>
BBED> set dba 1,1
DBA 0x00400001 (4194305 1,1)
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x001a7263
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x317382b0
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x0000003a
ub4 kcrbabno @504 0x00000015
ub2 kcrbabof @508 0x0010
ub1 kcvcpetb[0] @512 0x02
ub1 kcvcpetb[1] @513 0x00
ub1 kcvcpetb[2] @514 0x00
ub1 kcvcpetb[3] @515 0x00
ub1 kcvcpetb[4] @516 0x00
ub1 kcvcpetb[5] @517 0x00
ub1 kcvcpetb[6] @518 0x00
ub1 kcvcpetb[7] @519 0x00
BBED> p kcvfhcpc
ub4 kcvfhcpc @140 0x000000ac
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x000000ab
--从上面可以看到datafile 4 的SCN 是001a5e5a，转换一下：
SQL> select to_number('001a5e5a','XXXXXXXXXXXX') from dual;
TO_NUMBER('001A5E5A','XXXXXXXXXXXX')
------------------------------------
1728090
--从上面可以看到datafile 1 的SCN 是001A7263，转换一下：
SQL> select to_number('001a7263','XXXXXXXXXXXX') from dual;
TO_NUMBER('001A7263','XXXXXXXXXXXX')
------------------------------------
1733219
--这跟我们select查到的信息一致
BBED> d /v dba 1,1 offset 484 count 32
File: /opt/app/oracle/oradata/goolen/system01.dbf (1)
Block: 1 Offsets: 484 to 515 Dba:0x00400001
-------------------------------------------------------
63721a00 00000000 b0827331 01000000 l cr......°.s1....
3a000000 15000000 1000c5dc 02000000 l :.........?....
<16 bytes per line>
BBED> d /v dba 1,1 offset 492
File: /opt/app/oracle/oradata/goolen/system01.dbf (1)
Block: 1 Offsets: 492 to 514 Dba:0x00400001
-------------------------------------------------------
b0827331 01000000 3a000000 15000000 l °.s1....:.......
1000c5dc 020000 l ..?...
<16 bytes per line>
BBED> d /v dba 1,1 offset 140 count 23
File: /opt/app/oracle/oradata/goolen/system01.dbf (1)
Block: 1 Offsets: 140 to 162 Dba:0x00400001
-------------------------------------------------------
ac000000 39d67031 ab000000 00000000 l ?...9?1?.......
00000000 000000 l .......
<16 bytes per line>
BBED> d /v dba 1,1 offset 148 count 23
File: /opt/app/oracle/oradata/goolen/system01.dbf (1)
Block: 1 Offsets: 148 to 170 Dba:0x00400001
-------------------------------------------------------
ab000000 00000000 00000000 00000000 l ?...............
00000000 000000 l .......
<16 bytes per line>
BBED> modify /x 63721a00 dba 4,1 offset 484
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
Block: 1 Offsets: 484 to 506 Dba:0x01000001
------------------------------------------------------------------------
63721a00 00000000 62567331 01000000 37000000 020000
<32 bytes per line>
BBED> modify /x b0827331 dba 4,1 offset 492
BBED-00209: invalid number (b0827331)
BBED> d /v dba 4,1 offset 492
File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
Block: 1 Offsets: 492 to 514 Dba:0x01000001
-------------------------------------------------------
62567331 01000000 37000000 02000000 l bVs1....7.......
10000000 020000 l .......
<16 bytes per line>
BBED> modify /x b082 dba 4,1 offset 492
File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
Block: 1 Offsets: 492 to 514 Dba:0x01000001
------------------------------------------------------------------------
b0827331 01000000 37000000 02000000 10000000 020000
<32 bytes per line>
BBED> modify /x ac000000 dba 4,1 offset 140
BBED-00209: invalid number (ac000000)
BBED> d /v dba 4,1 offset 140
File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
Block: 1 Offsets: 140 to 162 Dba:0x01000001
-------------------------------------------------------
a9000000 a50e7131 a8000000 00000000 l ?...￥.q1¨.......
00000000 000000 l .......
<16 bytes per line>
BBED> modify /x ac00 dba 4,1
File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
Block: 1 Offsets: 140 to 162 Dba:0x01000001
------------------------------------------------------------------------
ac000000 a50e7131 a8000000 00000000 00000000 000000
<32 bytes per line>
BBED> modify /x ab000000 dba 4,1 offset 148
BBED-00209: invalid number (ab000000)
BBED> d /v dba 4,1 offset 148
File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
Block: 1 Offsets: 148 to 170 Dba:0x01000001
-------------------------------------------------------
a8000000 00000000 00000000 00000000 l ¨...............
00000000 000000 l .......
<16 bytes per line>
BBED> modify /x ab00 dba 4,1 offset 148
File: /opt/app/oracle/oradata/goolen/users01.dbf (4)
Block: 1 Offsets: 148 to 170 Dba:0x01000001
------------------------------------------------------------------------
ab000000 00000000 00000000 00000000 00000000 000000
<32 bytes per line>
BBED> sum apply
Check value for File 4, Block 1:
current = 0x9da8, required = 0x9da8
--修改完毕，我们尝试online datafile 4，还是失败
SQL> alter database datafile 4 online;
alter database datafile 4 online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/opt/app/oracle/oradata/goolen/users01.dbf'
--我们刚刚只是修改了datafile header的信息，而存储在控制文件中的信息是无法用bbed来修改的
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1733219
2 1733219
3 1733219
4 1728090
SQL> select file#,online_status,change# from v$recover_file;
FILE# ONLINE_ CHANGE#
---------- ------- ----------
4 OFFLINE 1733219
--这种情况，先重建控制文件，再进行恢复
SQL> alter database backup controlfile to trace as '/home/oracle/1.ctl.trc';
Database altered.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2087780352 bytes
Fixed Size 2214936 bytes
Variable Size 1207960552 bytes
Database Buffers 872415232 bytes
Redo Buffers 5189632 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "GOOLEN" NORESETLOGS ARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/opt/app/oracle/oradata/goolen/redo01.log' SIZE 50M BLOCKSIZE 512,
9 GROUP 2 '/opt/app/oracle/oradata/goolen/redo02.log' SIZE 50M BLOCKSIZE 512,
10 GROUP 3 '/opt/app/oracle/oradata/goolen/redo03.log' SIZE 50M BLOCKSIZE 512
11 DATAFILE
12 '/opt/app/oracle/oradata/goolen/system01.dbf',
13 '/opt/app/oracle/oradata/goolen/sysaux01.dbf',
14 '/opt/app/oracle/oradata/goolen/undotbs01.dbf',
15 '/opt/app/oracle/oradata/goolen/users01.dbf'
16 CHARACTER SET WE8MSWIN1252
17 ;
Control file created.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: '/opt/app/oracle/oradata/goolen/users01.dbf'
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED
SQL> recover database ;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select file#,checkpoint_change# from v$datafile;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 1733605
2 1733605
3 1733605
4 1733605
SQL> select file#,online_status,change# from v$recover_file;
no rows selected
SQL>
421
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
