首页 » ORACLE » Recover from rm -rf deleted datafile in NOARCHIVELOG mode using BBED

Recover from rm -rf deleted datafile in NOARCHIVELOG mode using BBED

A friend from QQ Group  find me, he removed a online datafile from the his production database, but for as long as the database remains up. he re I take a demo to show recovery process.  The procedure below works on linux.

col name for a50
sys@ANBOB>col name for a50
sys@ANBOB>@dfscn
               FILE# cfst    dbhst                  cfscn               bfhscn NAME
-------------------- ------- ------- -------------------- -------------------- --------------------------------------------------
                   1 SYSTEM  ONLINE           34604957572          34604957572 /u01/app/oracle/oradata/anbob/system01.dbf
                   2 ONLINE  ONLINE           34604957572          34604957572 /u01/app/oracle/oradata/anbob/sysaux01.dbf
                   3 ONLINE  ONLINE           34604957572          34604957572 /u01/app/oracle/oradata/anbob/undotbs01.dbf
                   4 ONLINE  ONLINE           34604957572          34604957572 /u01/app/oracle/oradata/anbob/users01.dbf
                   5 ONLINE  ONLINE           34604957572          34604957572 /u01/app/oracle/oradata/anbob/audit_tbs01.dbf
                   6 ONLINE  ONLINE           34604957572          34604957572 /u01/app/oracle/oradata/anbob/tbs_audit01.dbf
                   7 ONLINE  ONLINE           34604957572          34604957572 /u01/app/oracle/oradata/anbob/user02.dbf
                   8 ONLINE  ONLINE           34604957572          34604957572 /oradata/anbob/user03.dbf
                   9 ONLINE  ONLINE           34604957572          34604957572 /oradata/anbob/user04.dbf
                  11 ONLINE  ONLINE           34604957572          34604957572 /oradata/anbob/tt01.dbf
                  12 ONLINE  ONLINE           34604957572          34604957572 /oradata/anbob/tbs_rm01.dbf

11 rows selected.
Elapsed: 00:00:00.01
sys@ANBOB>host

[oracle@db231 ~]$ rm /oradata/anbob/tbs_rm01.dbf
[oracle@db231 ~]$ ll /oradata/anbob/tbs_rm01.dbf
ls: /oradata/anbob/tbs_rm01.dbf: No such file or directory

Try to recovery

[oracle@db231 ~]$ lsof |grep /oradata/anbob/tbs_rm01.dbf
oracle     3761    oracle  268uW     REG                8,2   10493952  9011205 /oradata/anbob/tbs_rm01.dbf (deleted)
oracle     3763    oracle  271u      REG                8,2   10493952  9011205 /oradata/anbob/tbs_rm01.dbf (deleted)
oracle     3765    oracle  268u      REG                8,2   10493952  9011205 /oradata/anbob/tbs_rm01.dbf (deleted)
oracle     3767    oracle  266u      REG                8,2   10493952  9011205 /oradata/anbob/tbs_rm01.dbf (deleted)
oracle    13487    oracle  268u      REG                8,2   10493952  9011205 /oradata/anbob/tbs_rm01.dbf (deleted)
oracle    29541    oracle  268u      REG                8,2   10493952  9011205 /oradata/anbob/tbs_rm01.dbf (deleted)

[oracle@db231 ~]$ ps aux|grep 3761|grep -v grep 
oracle    3761  0.0  8.7 2377432 714936 ?      Ss   Sep09   1:19 ora_dbw0_anbob

sys@ANBOB>alter system checkpoint;
System altered.

alert log
—————————
Errors in file /u01/app/oracle/diag/rdbms/anbob/anbob/trace/anbob_m000_29958.trc:
ORA-01116: error in opening database file 12
ORA-01110: data file 12: ‘/oradata/anbob/tbs_rm01.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory

sys@ANBOB>@dfscn
               FILE# cfst    dbhst                  cfscn               bfhscn NAME
-------------------- ------- ------- -------------------- -------------------- --------------------------------------------------
                   1 SYSTEM  ONLINE           34604962792          34604962792 /u01/app/oracle/oradata/anbob/system01.dbf
                   2 ONLINE  ONLINE           34604962792          34604962792 /u01/app/oracle/oradata/anbob/sysaux01.dbf
                   3 ONLINE  ONLINE           34604962792          34604962792 /u01/app/oracle/oradata/anbob/undotbs01.dbf
                   4 ONLINE  ONLINE           34604962792          34604962792 /u01/app/oracle/oradata/anbob/users01.dbf
                   5 ONLINE  ONLINE           34604962792          34604962792 /u01/app/oracle/oradata/anbob/audit_tbs01.dbf
                   6 ONLINE  ONLINE           34604962792          34604962792 /u01/app/oracle/oradata/anbob/tbs_audit01.dbf
                   7 ONLINE  ONLINE           34604962792          34604962792 /u01/app/oracle/oradata/anbob/user02.dbf
                   8 ONLINE  ONLINE           34604962792          34604962792 /oradata/anbob/user03.dbf
                   9 ONLINE  ONLINE           34604962792          34604962792 /oradata/anbob/user04.dbf
                  11 ONLINE  ONLINE           34604962792          34604962792 /oradata/anbob/tt01.dbf
                  12 ONLINE  ONLINE           34604962792          34604962792 /oradata/anbob/tbs_rm01.dbf

[oracle@db231 fd]$ ll /proc/3761/fd/268 
lrwx------ 1 oracle oinstall 64 Sep 23 10:07 /proc/3761/fd/268 -> /oradata/anbob/tbs_rm01.dbf (deleted)

[oracle@db231 fd]$ dd if=/proc/3761/fd/268 of=/tmp/tbs_rm01.dbf
20496+0 records in
20496+0 records out
10493952 bytes (10 MB) copied, 0.147866 seconds, 71.0 MB/s

[oracle@db231 fd]$ cp /tmp/tbs_rm01.dbf /oradata/anbob/tbs_rm01.dbf

note:
no offline datafile,online datafile again ,no recover ,Here left a security risk

[oracle@db231 ~]$ ll /oradata/anbob/tbs_rm01.dbf
-rw-r--r-- 1 oracle oinstall 10493952 Sep 23 10:15 /oradata/anbob/tbs_rm01.dbf

sys@ANBOB>@dfscn
               FILE# cfst    dbhst                  cfscn               bfhscn NAME
-------------------- ------- ------- -------------------- -------------------- --------------------------------------------------
                   1 SYSTEM  ONLINE           34604962792          34604962792 /u01/app/oracle/oradata/anbob/system01.dbf
                   2 ONLINE  ONLINE           34604962792          34604962792 /u01/app/oracle/oradata/anbob/sysaux01.dbf
                   3 ONLINE  ONLINE           34604962792          34604962792 /u01/app/oracle/oradata/anbob/undotbs01.dbf
                   4 ONLINE  ONLINE           34604962792          34604962792 /u01/app/oracle/oradata/anbob/users01.dbf
                   5 ONLINE  ONLINE           34604962792          34604962792 /u01/app/oracle/oradata/anbob/audit_tbs01.dbf
                   6 ONLINE  ONLINE           34604962792          34604962792 /u01/app/oracle/oradata/anbob/tbs_audit01.dbf
                   7 ONLINE  ONLINE           34604962792          34604962792 /u01/app/oracle/oradata/anbob/user02.dbf
                   8 ONLINE  ONLINE           34604962792          34604962792 /oradata/anbob/user03.dbf
                   9 ONLINE  ONLINE           34604962792          34604962792 /oradata/anbob/user04.dbf
                  11 ONLINE  ONLINE           34604962792          34604962792 /oradata/anbob/tt01.dbf
                  12 ONLINE  ONLINE           34604962792          34604962792 /oradata/anbob/tbs_rm01.dbf

11 rows selected.

Elapsed: 00:00:00.01
sys@ANBOB>shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

sys@ANBOB>startup
ORACLE instance started.

Total System Global Area           2137886720 bytes
Fixed Size                            2230072 bytes
Variable Size                      1056966856 bytes
Database Buffers                   1073741824 bytes
Redo Buffers                          4947968 bytes
Database mounted.
ORA-01113: file 12 needs media recovery
ORA-01110: data file 12: '/oradata/anbob/tbs_rm01.dbf'

sys@ANBOB>select open_mode from v$database;
OPEN_MODE
--------------------
MOUNTED

sys@ANBOB>@dfscn
               FILE# cfst    dbhst                  cfscn               bfhscn NAME
-------------------- ------- ------- -------------------- -------------------- --------------------------------------------------
                   1 SYSTEM  ONLINE           34604963688          34604963688 /u01/app/oracle/oradata/anbob/system01.dbf
                   2 ONLINE  ONLINE           34604963688          34604963688 /u01/app/oracle/oradata/anbob/sysaux01.dbf
                   3 ONLINE  ONLINE           34604963688          34604963688 /u01/app/oracle/oradata/anbob/undotbs01.dbf
                   4 ONLINE  ONLINE           34604963688          34604963688 /u01/app/oracle/oradata/anbob/users01.dbf
                   5 ONLINE  ONLINE           34604963688          34604963688 /u01/app/oracle/oradata/anbob/audit_tbs01.dbf
                   6 ONLINE  ONLINE           34604963688          34604963688 /u01/app/oracle/oradata/anbob/tbs_audit01.dbf
                   7 ONLINE  ONLINE           34604963688          34604963688 /u01/app/oracle/oradata/anbob/user02.dbf
                   8 ONLINE  ONLINE           34604963688          34604963688 /oradata/anbob/user03.dbf
                   9 ONLINE  ONLINE           34604963688          34604963688 /oradata/anbob/user04.dbf
                  11 ONLINE  ONLINE           34604963688          34604963688 /oradata/anbob/tt01.dbf
                  12 ONLINE  ONLINE           34604963688          34604962792 /oradata/anbob/tbs_rm01.dbf

11 rows selected.

If restored to a consistent state of request the logfiles all is available, issue a recover

sys@ANBOB>recover datafile 12;
Media recovery complete.

sys@ANBOB>alter database open;
Database altered.

Elapsed: 00:00:03.62
sys@ANBOB>@dfscn
               FILE# cfst    dbhst                  cfscn               bfhscn NAME
-------------------- ------- ------- -------------------- -------------------- --------------------------------------------------
                   1 SYSTEM  ONLINE           34604963691          34604963691 /u01/app/oracle/oradata/anbob/system01.dbf
                   2 ONLINE  ONLINE           34604963691          34604963691 /u01/app/oracle/oradata/anbob/sysaux01.dbf
                   3 ONLINE  ONLINE           34604963691          34604963691 /u01/app/oracle/oradata/anbob/undotbs01.dbf
                   4 ONLINE  ONLINE           34604963691          34604963691 /u01/app/oracle/oradata/anbob/users01.dbf
                   5 ONLINE  ONLINE           34604963691          34604963691 /u01/app/oracle/oradata/anbob/audit_tbs01.dbf
                   6 ONLINE  ONLINE           34604963691          34604963691 /u01/app/oracle/oradata/anbob/tbs_audit01.dbf
                   7 ONLINE  ONLINE           34604963691          34604963691 /u01/app/oracle/oradata/anbob/user02.dbf
                   8 ONLINE  ONLINE           34604963691          34604963691 /oradata/anbob/user03.dbf
                   9 ONLINE  ONLINE           34604963691          34604963691 /oradata/anbob/user04.dbf
                  11 ONLINE  ONLINE           34604963691          34604963691 /oradata/anbob/tt01.dbf
                  12 ONLINE  ONLINE           34604963691          34604963691 /oradata/anbob/tbs_rm01.dbf

11 rows selected.

Elapsed: 00:00:00.01

Next ,to demo recovery request the logfiles all isn’t available

sys@ANBOB>host rm /oradata/anbob/tbs_rm01.dbf

sys@ANBOB>host
[oracle@db231 ~]$ ll /oradata/anbob/tbs_rm01.dbf
ls: /oradata/anbob/tbs_rm01.dbf: No such file or directory

Note:
dd restore datafile methods same as above, omitted here

sys@ANBOB>startup
ORACLE instance started.

Total System Global Area           2137886720 bytes
Fixed Size                            2230072 bytes
Variable Size                      1056966856 bytes
Database Buffers                   1073741824 bytes
Redo Buffers                          4947968 bytes
Database mounted.
ORA-01113: file 12 needs media recovery
ORA-01110: data file 12: '/oradata/anbob/tbs_rm01.dbf'

sys@ANBOB>@dfscn
               FILE# cfst    dbhst                  cfscn               bfhscn NAME
-------------------- ------- ------- -------------------- -------------------- --------------------------------------------------
                   1 SYSTEM  ONLINE           34604967937          34604967937 /u01/app/oracle/oradata/anbob/system01.dbf
                   2 ONLINE  ONLINE           34604967937          34604967937 /u01/app/oracle/oradata/anbob/sysaux01.dbf
                   3 ONLINE  ONLINE           34604967937          34604967937 /u01/app/oracle/oradata/anbob/undotbs01.dbf
                   4 ONLINE  ONLINE           34604967937          34604967937 /u01/app/oracle/oradata/anbob/users01.dbf
                   5 ONLINE  ONLINE           34604967937          34604967937 /u01/app/oracle/oradata/anbob/audit_tbs01.dbf
                   6 ONLINE  ONLINE           34604967937          34604967937 /u01/app/oracle/oradata/anbob/tbs_audit01.dbf
                   7 ONLINE  ONLINE           34604967937          34604967937 /u01/app/oracle/oradata/anbob/user02.dbf
                   8 ONLINE  ONLINE           34604967937          34604967937 /oradata/anbob/user03.dbf
                   9 ONLINE  ONLINE           34604967937          34604967937 /oradata/anbob/user04.dbf
                  11 ONLINE  ONLINE           34604967937          34604967937 /oradata/anbob/tt01.dbf
                  12 ONLINE  ONLINE           34604967937          34604963691 /oradata/anbob/tbs_rm01.dbf

Try to modify file#12 scn in datafile header to be same as other datafiles;

				  
BBED> info
 File#  Name                                                        Size(blks)
 -----  ----                                                        ----------
     4  /u01/app/oracle/oradata/anbob/users01.dbf                            0
    12  /oradata/anbob/tbs_rm01.dbf                                          0

BBED> set DBA 4, 1
        DBA             0x01000001 (16777217 4,1)

BBED> show
        FILE#           4
        BLOCK#          1
        OFFSET          0
        DBA             0x01000001 (16777217 4,1)
        FILENAME        /u01/app/oracle/oradata/anbob/users01.dbf
        BIFILE          bifile.bbd
        LISTFILE        /home/oracle/bbed/filelist.txt
        BLOCKSIZE       8192
        MODE            Edit
        EDIT            Unrecoverable
        IBASE           Dec
        OBASE           Dec
        WIDTH           80
        COUNT           512
        LOGFILE         log.bbd
        SPOOL           No

Tip:
The first two attributes are stored in the kcvfhckp sub-structure. The second two are attributes in their own right.
We can use the print command to display them all for the file that requires recovery:

BBED> print kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
   struct kcvcpscn, 8 bytes                 @484     
      ub4 kscnbas                           @484      0x0e9de801
      ub2 kscnwrp                           @488      0x0008
   ub4 kcvcptim                             @492      0x3333c421
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500     
      struct kcvcprba, 12 bytes             @500     
         ub4 kcrbaseq                       @500      0x0000080f
         ub4 kcrbabno                       @504      0x00004c2c
         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      0x0000085f

BBED> p kcvfhccc
ub4 kcvfhccc                                @148      0x0000085e

Tip:
Oracle considers 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

idle>select to_number('80e9de801','xxxxxxxxxxx') from dual;

TO_NUMBER('80E9DE801','XXXXXXXXXXX')
------------------------------------
                         34604967937

BBED> set dba 12, 1
        DBA             0x03000001 (50331649 12,1)

BBED>  print kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
   struct kcvcpscn, 8 bytes                 @484     
      ub4 kscnbas                           @484      0x0e9dd76b
      ub2 kscnwrp                           @488      0x0008
   ub4 kcvcptim                             @492      0x3333b53c
   ub2 kcvcpthr                             @496      0x0001
   union u, 12 bytes                        @500     
      struct kcvcprba, 12 bytes             @500     
         ub4 kcrbaseq                       @500      0x0000080f
         ub4 kcrbabno                       @504      0x00002fdc
         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      0x00000376

BBED> p kcvfhccc
ub4 kcvfhccc                                @148      0x00000375

BBED> d /v dba 4,1 offset 484 count 32
 File: /u01/app/oracle/oradata/anbob/users01.dbf (4)
 Block: 1       Offsets:  484 to  515  Dba:0x01000001
-------------------------------------------------------
 01e89d0e 08000000 21c43333 01000000 l .......!33....
 0f080000 2c4c0000 100063de 02000000 l ....,L....c....

BBED> d /v dba 12,1 offset 484 count 32
 File: /oradata/anbob/tbs_rm01.dbf (12)
 Block: 1       Offsets:  484 to  515  Dba:0x03000001
-------------------------------------------------------
 6bd79d0e 08000000 3cb53333 01000000 l k......

Tip:
the numbers are stored in little endian format (the low-order byte of the number is stored in memory at the lowest address) as this example database is running on Linux on an Intel platform.
such as:
0x0e9de801===>01e89d0e
0x0e9dd76b===>6bd79d0e

BBED>modify /x 01e89d0e dba 12, 1 offset 484

BBED>modify /x 21c43333 dba 12, 1 offset 492

BBED>modify /x 5f080000 dba 12, 1 offset 140

BBED>modify /x 5e08 dba 12, 1 offset 148

BBED> verify 
DBVERIFY - Verification starting
FILE = /oradata/anbob/tbs_rm01.dbf
BLOCK = 1

Block 1 is corrupt
Corrupt block relative dba: 0x03000001 (file 0, block 1)
Bad check value found during verification
Data in bad block:
 type: 11 format: 2 rdba: 0x03000001
 last change scn: 0x0000.00000000 seq: 0x1 flg: 0x04
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0x00000b01
 check value in block header: 0x4a27
 computed block checksum: 0x3f68

DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 1
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED

#Check the block checksum and apply

BBED> sum dba 12, 1 apply
Check value for File 12, Block 1:
current = 0x754f, required = 0x754f

BBED> verify
DBVERIFY - Verification starting
FILE = /oradata/anbob/tbs_rm01.dbf
BLOCK = 1

DBVERIFY - Verification complete

Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
Message 531 not found;  product=RDBMS; facility=BBED

idle>alter database open  ;
alter database open
*
ERROR at line 1:
ORA-01122: database file 12 failed verification check
ORA-01110: data file 12: '/oradata/anbob/tbs_rm01.dbf'
ORA-01207: file is more recent than control file - old control file

sys@ANBOB>@dfscn
               FILE# cfst    dbhst                  cfscn               bfhscn NAME
-------------------- ------- ------- -------------------- -------------------- --------------------------------------------------
                   1 SYSTEM  ONLINE           34604967937          34604967937 /u01/app/oracle/oradata/anbob/system01.dbf
                   2 ONLINE  ONLINE           34604967937          34604967937 /u01/app/oracle/oradata/anbob/sysaux01.dbf
                   3 ONLINE  ONLINE           34604967937          34604967937 /u01/app/oracle/oradata/anbob/undotbs01.dbf
                   4 ONLINE  ONLINE           34604967937          34604967937 /u01/app/oracle/oradata/anbob/users01.dbf
                   5 ONLINE  ONLINE           34604967937          34604967937 /u01/app/oracle/oradata/anbob/audit_tbs01.dbf
                   6 ONLINE  ONLINE           34604967937          34604967937 /u01/app/oracle/oradata/anbob/tbs_audit01.dbf
                   7 ONLINE  ONLINE           34604967937          34604967937 /u01/app/oracle/oradata/anbob/user02.dbf
                   8 ONLINE  ONLINE           34604967937          34604967937 /oradata/anbob/user03.dbf
                   9 ONLINE  ONLINE           34604967937          34604967937 /oradata/anbob/user04.dbf
                  11 ONLINE  ONLINE           34604967937          34604967937 /oradata/anbob/tt01.dbf
                  12 ONLINE  ONLINE           34604967937          34604967937 /oradata/anbob/tbs_rm01.dbf

idle>shutdown immediate
ORA-01109: database not open

To re-create controlfile with RESETLOGS option

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ANBOB" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 1600
    MAXINSTANCES 8
    MAXLOGHISTORY 584
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/anbob/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/anbob/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/anbob/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/anbob/system01.dbf',
  '/u01/app/oracle/oradata/anbob/sysaux01.dbf',
  '/u01/app/oracle/oradata/anbob/undotbs01.dbf',
  '/u01/app/oracle/oradata/anbob/users01.dbf',
  '/u01/app/oracle/oradata/anbob/audit_tbs01.dbf',
  '/u01/app/oracle/oradata/anbob/tbs_audit01.dbf',
  '/u01/app/oracle/oradata/anbob/user02.dbf',
  '/oradata/anbob/user03.dbf',
  '/oradata/anbob/user04.dbf',
  '/oradata/anbob/tt01.dbf',
  '/oradata/anbob/tbs_rm01.dbf'
CHARACTER SET ZHS16GBK
;

NOTE:
If you try to create a new control file and use “NORESETLOGS” will cause the following error
ORA-01503: CREATE CONTROLFILE failed
ORA-01229: data file 12 is inconsistent with logs
ORA-01110: data file 12: ‘/oradata/anbob/tbs_rm01.dbf’

# Add “_allow_resetlogs_corruption” parameter

idle>alter system set "_allow_resetlogs_corruption"=true scope=spfile;
System altered.
idle>shutdown immediate

idle>startup mount

idle>alter database open resetlogs;
Database altered.

NOTE:
if open database without “_allow_resetlogs_corruption” undocument parameter at this time will encounter the following error

ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: ‘/u01/app/oracle/oradata/anbob/system01.dbf’

# Remove “_allow_resetlogs_corruption” parameter

sys@ANBOB>alter system reset "_allow_resetlogs_corruption" scope=spfile;
sys@ANBOB>shutdown immediate

sys@ANBOB>startup
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size 2230072 bytes
Variable Size 1056966856 bytes
Database Buffers 1073741824 bytes
Redo Buffers 4947968 bytes
Database mounted.
Database opened.

sys@ANBOB>@dfscn
FILE# cfst dbhst cfscn bfhscn NAME
-------------------- ------- ------- -------------------- -------------------- --------------------------------------------------
1 SYSTEM ONLINE 34604968806 34604968806 /u01/app/oracle/oradata/anbob/system01.dbf
2 ONLINE ONLINE 34604968806 34604968806 /u01/app/oracle/oradata/anbob/sysaux01.dbf
3 ONLINE ONLINE 34604968806 34604968806 /u01/app/oracle/oradata/anbob/undotbs01.dbf
4 ONLINE ONLINE 34604968806 34604968806 /u01/app/oracle/oradata/anbob/users01.dbf
5 ONLINE ONLINE 34604968806 34604968806 /u01/app/oracle/oradata/anbob/audit_tbs01.dbf
6 ONLINE ONLINE 34604968806 34604968806 /u01/app/oracle/oradata/anbob/tbs_audit01.dbf
7 ONLINE ONLINE 34604968806 34604968806 /u01/app/oracle/oradata/anbob/user02.dbf
8 ONLINE ONLINE 34604968806 34604968806 /oradata/anbob/user03.dbf
9 ONLINE ONLINE 34604968806 34604968806 /oradata/anbob/user04.dbf
11 ONLINE ONLINE 34604968806 34604968806 /oradata/anbob/tt01.dbf
12 ONLINE ONLINE 34604968806 34604968806 /oradata/anbob/tbs_rm01.dbf

11 rows selected.

sys@ANBOB>ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/anbob/temp01.dbf' REUSE;
Tablespace altered.

Summary:
1、 modify file# 12 SCN in datafile header same as other file
2、 recreate controlfile
3、 add *._allow_resetlogs_corruption=TRUE parameter to spfile ,and startup to mount
4、 open database with resetlogs option
5、 shutdown database with immediate, remove “_allow_resetlogs_corruption” parameter, startup normal

——————–
dfscn.sql

select cf.file#,cf.status "cfst",dfh.status "dbhst",cf.CHECKPOINT_CHANGE# "cfscn",dfh.CHECKPOINT_CHANGE# "bfhscn", cf.name 
from v$datafile cf
left join v$datafile_header dfh on  cf.file#=dfh.file#;
打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. admin | #1
    2014-09-24 at 14:22

    当然在本案例恢复的最后遇到了些小问题,比如因为datafile fuzzy state虽然在scn一致的情况下open 还又提示system need recover,还有ora-600 2662, ora-600 4194 ,最后open数据库后 先做数据导出,重新建库再导入。
    警示:1,除了升级提升速度,没有理由不启用archivelog mode;
    2, 备份重于一切,如果先前有备份,就可以mount 下,offline drop 那个datafile,open 后做数据恢复。
    3, 不要在自己的生产库上做此测试