问题描述
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.dbfnote: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#;