bbed修改SCN号强制打开DB

最新推荐文章于 2021-04-12 19:26:52 发布

congcai5135

最新推荐文章于 2021-04-12 19:26:52 发布

阅读量145

点赞数

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相关配置文件

[oracle@odd ~]$ ll
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

[oracle@odd ~]$ more 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）

[oracle@odd ~]$ bbed
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 ................

<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 ................

<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 ................

<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 ................

-----------------------------------------------------------------------------------------

以上四个查询得到以下结论，即准备将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

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 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 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 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

应用完之后理论上可以打开DB

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一块去重建控制文件即可。

SQL> alter database backup controlfile to trace as '/home/oracle/c.bak';

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/