1.因断电导致数据库因SCN不一致无法启动,但其中的数据重要,需要解决。但该数据库是非归档并且无全备。
以下为模拟当时情况
SQL> select status from v$instance;STATUS
------------
MOUNTED
SQL> select name,CHECKPOINT_CHANGE# from v$datafile_header;
NAME CHECKPOINT_CHANGE#
------------------------------------------------------- ------------------
/u01/app/oracle/oradata/PROD/system01.dbf 910882
/u01/app/oracle/oradata/PROD/sysaux01.dbf 910882
/u01/app/oracle/oradata/PROD/undotbs01.dbf 910882
/u01/app/oracle/oradata/PROD/users01.dbf 910579
/u01/app/oracle/oradata/PROD/example01.dbf 910882
2..安装bbed,在11g中生成bbed,需要先从10g中复制如下文件到相应目录,然后再执行以下命令
$ORACLE_HOME/rdbms/lib/ssbbded.o$ORACLE_HOME/rdbms/lib/sbbdpt.o
$ORACLE_HOME/rdbms/mesg/bbedus.msb
执行如下命令:
cd $ORACLE_HOME/rdbms/lib
make -f $ORACLE_HOME/rdbms/lib/ins_rdbms.mk BBED=$ORACLE_HOME/bin/bbed $ORACLE_HOME/bin/bbed
4.编辑bbed相关配置文件
total 56
-rw-r--r-- 1 oracle oinstall 327 Jul 14 14:45 filelist.txt
-rw-r--r-- 1 oracle oinstall 47 Jul 14 14:45 par.txt
blocksize=8192
listfile=filelist.txt
mode=edit
[oracle@odd ~]$ more filelist.txt **可以通过右边获取: select file#||chr(9)||name||chr(9)||bytes from v$datafile;
1 /u01/app/oracle/oradata/PROD/system01.dbf 723517440
2 /u01/app/oracle/oradata/PROD/sysaux01.dbf 524288000
3 /u01/app/oracle/oradata/PROD/undotbs01.dbf 62914560
4 /u01/app/oracle/oradata/PROD/users01.dbf 5242880
5 /u01/app/oracle/oradata/PROD/example01.dbf 104857600
5.进入bbed(默认密码blockedit)
Password:
BBED: Release 2.0.0.0.0 - Limited Production on Wed Jul 15 11:13:19 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED>
6. 找出datafile 1 的4个SCN号
The file header is stored in the first block of the data file.We can use bbed to examine the block and show the block map. The header blockscontain a single data structure - kcvfh.
datafile 的file header 存储在第一个block里。
Oracleconsiders four attributes of this data structure when determining if a datafile is sync with the other data files of the database:
(1)kscnbas (at offset 484) - SCN of last change to the datafile.
(2)kcvcptim (at offset 492) -Time of the last change to the datafile.
(3)kcvfhcpc (at offset 140) - Checkpoint count.
(4)kcvfhccc (at offset 148) - Unknown, but is always 1 less than thecheckpoint point count.
Oracle有4个属性来判断datafile 是否和其他的datafile 一致,如果都一致,可以正常操作,如果不一致,那么会报ORA-01113错误。
BBED> set dba 4,1
DBA 0x01000001 (16777217 4,1)
--指定datafile 4(需要恢复),第一个block
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes @484
struct kcvcpscn, 8 bytes @484
ub4 kscnbas @484 0x000de4f3
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x34c09d22
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x0000000c
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 0x0000006c
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x0000006b
从上面可以看到datafile 4的SCN 是0x000de4f3,转换一下:
SQL> select to_number('000de4f3','xxxxxxxxxxx') from dual;
TO_NUMBER('000DE4F3','XXXXXXXXXXX')
-----------------------------------
910579 这个和我们之前看到的datafile4的SCN号一致。
现在我们要做的,就是使用BBED 命令,修改datafile 4的4个属性,让其和其他的datafile 一致。
现在看一下systemdatafile 的4个属性值,然后修改到datafile 4上。
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 0x000de622
ub2 kscnwrp @488 0x0000
ub4 kcvcptim @492 0x34c09dc1
ub2 kcvcpthr @496 0x0001
union u, 12 bytes @500
struct kcvcprba, 12 bytes @500
ub4 kcrbaseq @500 0x00000010
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 0x00000073
BBED> p kcvfhccc
ub4 kcvfhccc @148 0x00000070
SQL> select to_number('000de622','xxxxxxxxxxx') from dual;
TO_NUMBER('000DE622','XXXXXXXXXXX')
-----------------------------------
910882 这个和我们之前看到的datafile1的SCN号一致,没问题。
7.将datafile4的SCN进行修改
修改datafile 4的4个对应属性。
BBED> d /v dba 1,1 offset 484
File: /u01/app/oracle/oradata/PROD/system01.dbf (1)
Block: 1 Offsets: 484 to 547 Dba:0x00400001
-------------------------------------------------------
22e60d00 00003c3c c19dc034 0100fdbf l "...<<..
10000000 02000000 10003c3c 02000000 l ..........<<....
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
<16 bytes="" per="" line="">
BBED> d /v dba 1,1 offset 492
File: /u01/app/oracle/oradata/PROD/system01.dbf (1)
Block: 1 Offsets: 492 to 555 Dba:0x00400001
-------------------------------------------------------
c19dc034 0100fdbf 10000000 02000000 l ..........
10003c3c 02000000 00000000 00000000 l ..<<............
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
<16 bytes="" per="" line="">
BBED> d /v dba 1,1 offset 140
File: /u01/app/oracle/oradata/PROD/system01.dbf (1)
Block: 1 Offsets: 140 to 203 Dba:0x00400001
-------------------------------------------------------
73000000 48a1c034 70000000 00000000 l s...H±4p.......
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
<16 bytes="" per="" line="">
BBED> d /v dba 1,1 offset 148
File: /u01/app/oracle/oradata/PROD/system01.dbf (1)
Block: 1 Offsets: 148 to 211 Dba:0x00400001
-------------------------------------------------------
70000000 00000000 00000000 00000000 l p...............
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
00000000 00000000 00000000 00000000 l ................
-----------------------------------------------------------------------------------------
以上四个查询得到以下结论,即准备将datafile 4的4个偏移量修改为以下内容
ub4 kscnbas @484 0x000de622 --> 22e60d00
ub4 kcvcptim @492 0x34c09dc1 -->c19dc034
ub4 kcvfhcpc @140 0x00000073 -->73000000
ub4 kcvfhccc @148 0x00000070 -->70000000
-----------------------------------------------------------------------------------------
开始修改datafile 4
BBED> modify /x 22e60d00 dba 4,1 offset 484
File: /u01/app/oracle/oradata/PROD/users01.dbf (4)
Block: 1 Offsets: 484 to 547 Dba:0x01000001
------------------------------------------------------------------------
22e60d00 00000000 229dc034 01000000 0c000000 02000000 10006c10 02000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
<32 bytes="" per="" line="">
BBED> modify /x c19dc034 dba 4,1 offset 492
BBED-00209: invalid number (c19dc034)
--偏移量492 的位置修改失败.但是这个可以改成比该值小的值。
--注意, 在shutdown 的情况下,4个参数都可以修改。 实际上shutdown了也不行,那么这里就先放一放,之后可以通过重建控制文件解决。
BBED> modify /x 73000000 dba 4,1 offset 140
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) Y
File: /u01/app/oracle/oradata/PROD/users01.dbf (4)
Block: 1 Offsets: 140 to 651 Dba:0x01000001
------------------------------------------------------------------------
73000000 7d9ec034 6b000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
04000000 05005553 45525300 00000000 00000000 00000000 00000000 00000000
00000000 04000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 20316a29 01000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 22e60d00 00000000
229dc034 01000000 0c000000 02000000 10006c10 02000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 0a000a00 0a000100
<32 bytes="" per="" line="">
BBED> modify /x 70000000 dba 4,1 offset 148
File: /u01/app/oracle/oradata/PROD/users01.dbf (4)
Block: 1 Offsets: 148 to 659 Dba:0x01000001
------------------------------------------------------------------------
70000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 04000000 05005553
45525300 00000000 00000000 00000000 00000000 00000000 00000000 04000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 20316a29 01000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 22e60d00 00000000 229dc034 01000000
0c000000 02000000 10006c10 02000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 0a000a00 0a000100 00000000 00000000
<32 bytes="" per="" line="">
BBED> sum dba 4,1 apply (应用)
Check value for File 4, Block 1:
current = 0xe525, required = 0xe525
8.SCN一致后的处理
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01122: database file 4 failed verification check
ORA-01110: data file 4: '/u01/app/oracle/oradata/PROD/users01.dbf'
ORA-01207: file is more recent than control file - old control file
出现问题,文件过新,可能由于偏移量492没有修改的问题导致的。
SQL> select file#,online_status,change# from v$recover_file;
FILE# ONLINE_ CHANGE#
---------- ------- ----------
1 ONLINE 910882
2 ONLINE 910882
3 ONLINE 910882
4 ONLINE 910882
解决方案:通过trace 出controlfile之后利用noresetlogs一块去重建控制文件即可。
Database altered.
[oracle@odd ~]$ cat c.bak |grep -v ^$|grep -v ^->c1.bak
之后将文件c1.bak中resetlogs那一块删掉,保留noresetlogs那一块
停库执行c1.bak
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> @/home/oracle/c1.bak;
ORACLE instance started.
Total System Global Area 485441536 bytes
Fixed Size 1337380 bytes
Variable Size 339740636 bytes
Database Buffers 138412032 bytes
Redo Buffers 5951488 bytes
Control file created.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Media recovery complete.
System altered.
Database altered.
Tablespace altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
参考:http://www.itpub.net/thread-1781066-1-1.html
http://www.cnblogs.com/springside-example/archive/2011/08/13/2529679.html
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30220976/viewspace-1733822/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30220976/viewspace-1733822/