Use bbed to modify the file header, push the SCN, find the offline drop data file

Recently handled a situation in which the database could not be opened due to user action errors.

The user database is Windows 64bit and the database version is 11.2.0.1, non-archive mode. Due to an abnormal power outage, the database does not open properly, and the user's judgment uses the wrong offline drop operation. After the database has been opened, there are too many log file switches to use the Recover command.

Because there is no other operation in the middle, so after receiving case, here will be damaged several data files copy to Linux using bbed to modify, and then copy back to Windows, successfully skipped recover, opened the offline drop data file.

Scenario Restore:

[email protected]> select Name, checkpoint_change# from V$datafile; NAME checkpoint_change#------------------------------------------------------ --------------/u01/apps/oracle/oradata/utf8a/system01.dbf 1040256/u01/apps/oracle/oradata/utf8a/sysaux01 . dbf 1040256/u01/apps/oracle/oradata/utf8a/undotbs01.dbf 1040256/u01/apps/oracle/oradat A/UTF8A/USERS01.DBF 1039896 [email protected]> Select sequence#, GROUP#,FIRST_CHANGE#,STATUSFR  Om V$log; sequence# group# first_change# STATUS-------------------------------------------------19 1 1040 2 1040250 INACTIVE 3 1040253 INACTIVE [email protected]& Gt Recover datafile 4; Ora-00279:change 1039896 generated at03/30/2015 09:31:05 needed for thread 1ora-00289:suggestion:/ U01/apps/oracle/product/11.2.0/dbhome_1/dbs/arch1_12_827004096.dbfora-00280:change 1039896 for thread 1 isin sequence #12 Specify log: {<ret>=suggested |filename | AUTO | Cancel}autoora-00308:cannot Open archived log '/u01/apps/oracle/product/11.2.0/dbhome_1/dbs/arch1_12_827004096.dbf ' Ora-27037:unable to obtain file statuslinux-x86_64 error:2: No such file ordirectoryadditional Information:3 ORA-0030 8:cannot Open archived log '/u01/apps/oracle/product/11.2.0/dbhome_1/dbs/arch1_12_827004096.dbf ' ora-27037:unable to Obtain file statuslinux-x86_64 error:2: No such file ordirectoryadditional Information:3

Sequence has passed and cannot perform recovery, so you can only use bbed to modify the file header. Archiving and backup are really important.

Block is 8192.

Flist:

1/U01/APPS/ORACLE/ORADATA/UTF8A/SYSTEM01.DBF 754974720

2/U01/APPS/ORACLE/ORADATA/UTF8A/SYSAUX01.DBF 566231040

3/U01/APPS/ORACLE/ORADATA/UTF8A/UNDOTBS01.DBF 83886080

4/U01/APPS/ORACLE/ORADATA/UTF8A/USERS01.DBF 9175040

How to initialize the bbed environment, as well as download various bbed versions, see my Blog

http://blog.csdn.net/renfengjun/article/details/7944629

This is not repeated here.

Shut down the database first and boot to Mount state.

[Email protected]> selectfile#,change#,online_status from V$recover_file;     file#    change# online_---------------------------        4    1039896 OFFLINE [email protected]> select name, checkpoint_change# from V$datafile; NAME                                              checkpoint_change#--------------------------------------------------------------------/u01/apps/ ORACLE/ORADATA/UTF8A/SYSTEM01.DBF                   1041564/u01/apps/oracle/oradata/utf8a/sysaux01.dbf                   1041564/u01/apps/ ORACLE/ORADATA/UTF8A/UNDOTBS01.DBF                  1041564/u01/apps/oracle/oradata/utf8a/users01.dbf                    1039896

Remember to back up all database files before modifying them.

Bbed> Set file 4 file# 4 bbed> p kcvfhckpstruct kcvfhckp, bytes @484 struc T Kcvcpscn, 8 bytes @484 ub4 kscnbas @484 0x000fde18 ub2 KSCNWR                            P @488 0x0000 ub4 kcvcptim @492 0x34321859 ub2 kcvcpthr            @496 0x0001 Union u, Bytes @500 struct Kcvcprba, bytes                      @500 ub4 kcrbaseq @500 0x0000000c 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

Note Offset 484, Kscnbas is the SCN for the data file now.

After using the calculator calculates the 0x000fde18, obtains the decimal number to be 1039896, confirms unmistakable, as long as modifies the SCN to be the newest 1041564, can open the corrupt data file.

bbed> d/v dba 4,1 offset 484 count File:/u01/apps/oracle/oradata/utf8a/users01.dbf (4) block:1      offsets:  48 4 to  499 dba:0x01000001-------------------------------------------------------18de0f00 00000000 59183234 01000000 l ... Y.24 ....  

Because is here for Linux x64, for little endian.

1039896=18de0f00

1041564=9ce40f00

can be modified directly.

Bbed> Set mode edit       mode            edit bbed> m/x 9ce40f dba 4,1 offset 484 file:/u01/apps/oracle/oradata/utf8a/users 01.DBF (4) block:1                offsets:  484 to 499           dba:0 x01000001------------------------------------------------------------------------9ce40f00 00000000 59183234 01000000  <32 bytes per line>   bbed> m/x 9ce40f dba 4,1 offset 484 File:/u01/apps/oracle/oradata/utf8a/us ERS01.DBF (4) block:1                offsets:  484 to 499           dba:0 x01000001------------------------------------------------------------------------9ce40f00 00000000 59183234 01000000  <32 bytes per line> bbed> sumcheck value for File 4, Block 1:current = 0x5f67, required = 0x65e3 BB ed> sum Applycheck value for File 4, Block 1:current = 0x65e3, required = 0x65e3

Complete the task

[Email protected]> ALTER DATABASE datafile 4online; Database altered.     [Email protected]> selectfile#,change#,online_status from V$recover_file; file# change# online_---------------------------4 1041564 ONLINE [email protected]> ALTER DATABASE data File 4online; Database altered. [Email protected]> ALTER DATABASE open; ALTER DATABASE Open*error at line 1:ora-01113:file 4 needs media recoveryORA-0 1110:data file 4: '/u01/apps/oracle/oradata/utf8a/users01.dbf ' [email protected]> recover datafile 4; Media recovery complete. [Email protected]> ALTER DATABASE open; Database altered.      [Email protected]> select * from Scott.emp where rownum<2; EMPNO ename JOB MGR hiredate SAL COMM DEPTNO---------------------------------------                    ---------------------------------------7369 SMITH Clerk 7902 17-dec-80 800 20

When you are done, remember to export all user data logically and complete the backup plan.

Use bbed to modify the file header, push the SCN, find the offline drop data file