Use bbed to modify the file header, promote scn, and restore offline drop data files.

Use bbed to modify the file header, promote scn, and restore offline drop data files.

 

 

Recently, a database cannot be opened due to user operation errors.

 

The user database is windows 64bit, and the database version is 11.2.0.1. It is not in archive mode. Due to abnormal power failure, the database cannot be opened normally, and the incorrect offline drop operation is used after user's judgment. After the database is opened, too many log files are switched and the recover command cannot be used.

Because there are no other operations in the middle, after the case is received, copy several damaged data files to linux and use bbed for modification. Then copy the damaged data files back to windows. The recover is skipped, open the offline drop data file.

 

Scenario Restoration:

 

 

sys@UTF8A> 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/oradata/utf8a/users01.dbf                    1039896  sys@UTF8A> select sequence#, group#,first_change#,statusfrom v$log ;  SEQUENCE#    GROUP# FIRST_CHANGE# STATUS---------- ---------- -----------------------------       19          1       1040256 CURRENT       17          2       1040250 INACTIVE       18          3       1040253 INACTIVE  sys@UTF8A> 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-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

 

 

Sequence has passed and cannot be restored. Therefore, you can only use bbed to modify the file header. Archiving and backup are really important.

 

The 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/utf8a/users01.dbf 9175040

 

 

For more information about how to initialize the bbed environment and download various bbed versions, see my blog.

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

 

I will not repeat it here.

 

 

Shut down the database and start it to the mount status.

 

 

 

sys@UTF8A> selectfile#,change#,online_status from v$recover_file ;     FILE#    CHANGE# ONLINE_---------- ---------- -------        4    1039896 OFFLINE sys@UTF8A> 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 modification.

 

 

BBED> set file 4       FILE#           4 BBED> p kcvfhckpstruct kcvfhckp, 36 bytes                   @484      struct kcvcpscn, 8 bytes                @484          ub4 kscnbas                           @484      0x000fde18     ub2 kscnwrp                          @488      0x0000  ub4 kcvcptim                            @492      0x34321859  ub2 kcvcpthr                            @496      0x0001  union u, 12 bytes                       @500         struct kcvcprba, 12 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 that offset 484 and kscnbas are the current scn of the data file.

Use the calculator to calculate 0x000fde18 and the resulting decimal number is 1039896. Make sure that the data file is damaged by modifying the scn to the latest 1041564.

 

 

BBED> d /v dba 4,1 offset 484 count 16 File: /u01/apps/oracle/oradata/utf8a/users01.dbf(4) Block: 1      Offsets:  484 to  499 Dba:0x01000001------------------------------------------------------- 18de0f00 00000000 59183234 01000000 l ......Y.24....  <16 bytes per line> 

 

Because linux x64 is used here, it is little endian.

1039896 = 18de0f00

1041564 = 9ce40f00

 

 

Modify it directly.

 

 

BBED> set mode edit       MODE            Edit BBED> m /x 9ce40f dba 4,1 offset 484 File:/u01/apps/oracle/oradata/utf8a/users01.dbf (4) Block: 1                Offsets:  484 to 499           Dba:0x01000001------------------------------------------------------------------------ 9ce40f00 00000000 59183234 01000000  <32 bytes per line>   BBED> m /x 9ce40f dba 4,1 offset 484 File: /u01/apps/oracle/oradata/utf8a/users01.dbf(4) Block: 1                Offsets:  484 to 499           Dba:0x01000001------------------------------------------------------------------------ 9ce40f00 00000000 59183234 01000000  <32 bytes per line> BBED> sumCheck value for File 4, Block 1:current = 0x5f67, required = 0x65e3 BBED> sum applyCheck value for File 4, Block 1:current = 0x65e3, required = 0x65e3

 

 

Complete task

 

 sys@UTF8A> alter database datafile 4online ; Database altered. sys@UTF8A> selectfile#,change#,online_status from v$recover_file ;     FILE#    CHANGE# ONLINE_---------- ---------- -------        4    1041564 ONLINE sys@UTF8A> alter database datafile 4online ; Database altered. sys@UTF8A> alter database open ;alter database open*ERROR at line 1:ORA-01113: file 4 needs media recoveryORA-01110: data file 4:'/u01/apps/oracle/oradata/utf8a/users01.dbf'  sys@UTF8A> recover datafile 4 ;Media recovery complete.sys@UTF8A> alter database open ; Database altered. sys@UTF8A> select * from scott.emp where rownum<2 ;      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM    DEPTNO---------- ---------- --------- ---------- --------- -------------------- ----------      7369 SMITH      CLERK           7902 17-DEC-80        800                    20

 

 

After the operation is completed, all user data is exported logically and the backup plan is improved.