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#;
当然在本案例恢复的最后遇到了些小问题，比如因为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， 不要在自己的生产库上做此测试