如何彻底清掉已经offline drop的数据文件呢？该问题源于itpub的一个帖子：
数据文件物理性删除相关问题疑惑?
数据文件物理性删除相关问题疑惑?
1. 在生产环境上一个用户下新增了一个数据文件；
alter TABLESPACE KDPDM_DATA add dataFILE '/oradata/crmdb1/rlv_kdpdm_data06_40g.dbf' SIZE 40G ；
(-- 提示：kdpdm_data 用户之前就有5个数据文件)
2. 后来因为看文件跟以前格式不对，用 ssh 物理性删掉了这个文件。在做insert into 操作时，报错如下：
ora 01116
ora 01110
ora 27041
3. 执行了alter database datafile '/oradata/crmdb1/rlv_kdpdm_data06_40g.dbf' offline drop;
insert into 操作没报错了。
4. 我觉得是数据文件删掉了，但是配置文件，控制文件还在，select * from dba_data_files 还是能查看到这个数据文件。
？？？ 问题，以后我要是重启数据库，会不会因为控制文件找不到rlv_kdpdm_data06_40g.dbf，而启动失败，
我现在想删掉这个数据文件的配置数据，保证以后重启数据库能正常启动，请问该如何操作呢？
网友xifenfei问道，是否有什么办法能够彻底清楚已经不存在的数据文件，虽然已经
执行了offline drop，但是查询相关的数据字典试图仍然能够查询到，令人非常不爽。
如下是我的实验全过程，针对该问题进行的解答.
SQL> select file_id,tablespace_name,file_name
2 from dba_data_files
3 order by 1;
FILE_ID TABLESPACE_NAME FILE_NAME
---------- ------------------ --------------------------------------------------
1 SYSTEM /home/ora10g/oradata/roger/system01.dbf
2 UNDOTBS1 /home/ora10g/oradata/roger/undotbs01.dbf
3 SYSAUX /home/ora10g/oradata/roger/sysaux01.dbf
4 USERS /home/ora10g/oradata/roger/users01.dbf
5 ROGER /home/ora10g/oradata/roger/roger01.dbf
SQL> alter tablespace roger
2 add datafile '/home/ora10g/oradata/roger/roger02.dbf'
3 size 10m autoextend off;
Tablespace altered.
SQL> select file_id,tablespace_name,file_name
2 from dba_data_files
3 order by 1;
FILE_ID TABLESPACE_NAME FILE_NAME
---------- ------------------ --------------------------------------------------
1 SYSTEM /home/ora10g/oradata/roger/system01.dbf
2 UNDOTBS1 /home/ora10g/oradata/roger/undotbs01.dbf
3 SYSAUX /home/ora10g/oradata/roger/sysaux01.dbf
4 USERS /home/ora10g/oradata/roger/users01.dbf
5 ROGER /home/ora10g/oradata/roger/roger01.dbf
6 ROGER /home/ora10g/oradata/roger/roger02.dbf
6 rows selected.
SQL> alter database datafile 6 offline drop;
Database altered.
SQL> select file_id,file_name,bytes
2 from dba_data_files
3 order by 1;
FILE_ID FILE_NAME BYTES
---------- -------------------------------------------------- ----------
1 /home/ora10g/oradata/roger/system01.dbf 461373440
2 /home/ora10g/oradata/roger/undotbs01.dbf 969932800
3 /home/ora10g/oradata/roger/sysaux01.dbf 262144000
4 /home/ora10g/oradata/roger/users01.dbf 5242880
5 /home/ora10g/oradata/roger/roger01.dbf 209715200
6 /home/ora10g/oradata/roger/roger02.dbf
6 rows selected.
SQL> select file#,TS#,RFILE# ,STATUS,OFFLINE_CHANGE#
2 from v$datafile
3 order by 1;
FILE# TS# RFILE# STATUS OFFLINE_CHANGE#
---------- --- ---------- ------- ---------------
1 0 1 SYSTEM 387602
2 1 2 ONLINE 387602
3 2 3 ONLINE 387602
4 4 4 ONLINE 387602
5 6 5 ONLINE 0
6 6 6 RECOVER 0
6 rows selected.
SQL> select file#,ONLINE_STATUS,ERROR,CHANGE#
2 from V$RECOVER_FILE
3 order by 1;
FILE# ONLINE_ ERROR CHANGE#
---------- ------- ------------- ----------
6 OFFLINE 664918
++++++ 这种情况其实跟 offline 表空间一样 ++++++
SQL> alter database backup controlfile to trace;
Database altered.
######################### trace 信息如下 #########################
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ROGER" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/home/ora10g/oradata/roger/redo01.log' SIZE 50M,
GROUP 2 '/home/ora10g/oradata/roger/redo02.log' SIZE 50M,
GROUP 3 '/home/ora10g/oradata/roger/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/home/ora10g/oradata/roger/system01.dbf',
'/home/ora10g/oradata/roger/undotbs01.dbf',
'/home/ora10g/oradata/roger/sysaux01.dbf',
'/home/ora10g/oradata/roger/users01.dbf',
'/home/ora10g/oradata/roger/roger01.dbf',
'/home/ora10g/oradata/roger/roger02.dbf'
CHARACTER SET ZHS16GBK
;
我们可以看到，虽然drop了datafile，但是controlfile中仍然有其信息，
如果此时在操作系统上rm删除该数据文件，在启动的时候，必然报错找不到
但是并不是说导致数据库无法启动，是可以正常启动的，如下；
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !rm /home/ora10g/oradata/roger/roger02.dbf
SQL> !ls -ltr /home/ora10g/oradata/roger
-rw-r----- 1 ora10g oinstall 47194112 Oct 12 20:38 /home/ora10g/oradata/roger/temp01.dbf
-rw-r----- 1 ora10g oinstall 52429312 Oct 20 06:19 /home/ora10g/oradata/roger/redo03.log
-rw-r----- 1 ora10g oinstall 52429312 Oct 20 06:19 /home/ora10g/oradata/roger/redo02.log
-rw-r----- 1 ora10g oinstall 5251072 Oct 20 07:33 /home/ora10g/oradata/roger/users01.dbf
-rw-r----- 1 ora10g oinstall 969940992 Oct 20 07:33 /home/ora10g/oradata/roger/undotbs01.dbf
-rw-r----- 1 ora10g oinstall 461381632 Oct 20 07:33 /home/ora10g/oradata/roger/system01.dbf
-rw-r----- 1 ora10g oinstall 262152192 Oct 20 07:33 /home/ora10g/oradata/roger/sysaux01.dbf
-rw-r----- 1 ora10g oinstall 209723392 Oct 20 07:33 /home/ora10g/oradata/roger/roger01.dbf
-rw-r----- 1 ora10g oinstall 52429312 Oct 20 07:33 /home/ora10g/oradata/roger/redo01.log
-rw-r----- 1 ora10g oinstall 7061504 Oct 20 07:33 /home/ora10g/oradata/roger/control03.ctl
-rw-r----- 1 ora10g oinstall 7061504 Oct 20 07:33 /home/ora10g/oradata/roger/control02.ctl
-rw-r----- 1 ora10g oinstall 7061504 Oct 20 07:33 /home/ora10g/oradata/roger/control01.ctl
SQL> startup
ORA-00000: normal, successful completion
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1272576 bytes
Variable Size 113247488 bytes
Database Buffers 46137344 bytes
Redo Buffers 7114752 bytes
Database mounted.
Database opened.
SQL> select file_id,file_name,bytes
2 from dba_data_files
3 order by 1;
FILE_ID FILE_NAME BYTES
---------- -------------------------------------------------- ----------
1 /home/ora10g/oradata/roger/system01.dbf 461373440
2 /home/ora10g/oradata/roger/undotbs01.dbf 969932800
3 /home/ora10g/oradata/roger/sysaux01.dbf 262144000
4 /home/ora10g/oradata/roger/users01.dbf 5242880
5 /home/ora10g/oradata/roger/roger01.dbf 209715200
6 /home/ora10g/oradata/roger/roger02.dbf ==> 仍然存在
6 rows selected.
SQL> select file#,ONLINE_STATUS,ERROR,CHANGE#
2 from V$RECOVER_FILE
3 order by 1;
FILE# ONLINE_ ERROR CHANGE#
---------- ------- --------------------- ----------
6 OFFLINE FILE NOT FOUND 0
SQL> alter database backup controlfile to trace;
Database altered.
############### 此时控制文件中仍然存在该文件的信息 ###############
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ROGER" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/home/ora10g/oradata/roger/redo01.log' SIZE 50M,
GROUP 2 '/home/ora10g/oradata/roger/redo02.log' SIZE 50M,
GROUP 3 '/home/ora10g/oradata/roger/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/home/ora10g/oradata/roger/system01.dbf',
'/home/ora10g/oradata/roger/undotbs01.dbf',
'/home/ora10g/oradata/roger/sysaux01.dbf',
'/home/ora10g/oradata/roger/users01.dbf',
'/home/ora10g/oradata/roger/roger01.dbf',
'/home/ora10g/oradata/roger/roger02.dbf'
CHARACTER SET ZHS16GBK
;
###### 下面来重建controlfile ######
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !
[ora10g@killdb ~]$ cd /home/ora10g/oradata/roger/
[ora10g@killdb roger]$ ls -ltr
total 2084280
-rw-r----- 1 ora10g oinstall 47194112 Oct 12 20:38 temp01.dbf
-rw-r----- 1 ora10g oinstall 52429312 Oct 20 07:34 redo03.log
-rw-r----- 1 ora10g oinstall 52429312 Oct 20 07:34 redo02.log
-rw-r----- 1 ora10g oinstall 5251072 Oct 20 07:39 users01.dbf
-rw-r----- 1 ora10g oinstall 969940992 Oct 20 07:39 undotbs01.dbf
-rw-r----- 1 ora10g oinstall 461381632 Oct 20 07:39 system01.dbf
-rw-r----- 1 ora10g oinstall 262152192 Oct 20 07:39 sysaux01.dbf
-rw-r----- 1 ora10g oinstall 209723392 Oct 20 07:39 roger01.dbf
-rw-r----- 1 ora10g oinstall 52429312 Oct 20 07:39 redo01.log
-rw-r----- 1 ora10g oinstall 7061504 Oct 20 07:39 control03.ctl
-rw-r----- 1 ora10g oinstall 7061504 Oct 20 07:39 control02.ctl
-rw-r----- 1 ora10g oinstall 7061504 Oct 20 07:39 control01.ctl
[ora10g@killdb roger]$ cp control01.ctl control01.ctl.bak
[ora10g@killdb roger]$ exit
exit
SQL> startup nomount
ORA-00000: normal, successful completion
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1272576 bytes
Variable Size 113247488 bytes
Database Buffers 46137344 bytes
Redo Buffers 7114752 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ROGER" RESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/home/ora10g/oradata/roger/redo01.log' SIZE 50M,
9 GROUP 2 '/home/ora10g/oradata/roger/redo02.log' SIZE 50M,
10 GROUP 3 '/home/ora10g/oradata/roger/redo03.log' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/home/ora10g/oradata/roger/system01.dbf',
14 '/home/ora10g/oradata/roger/undotbs01.dbf',
15 '/home/ora10g/oradata/roger/sysaux01.dbf',
16 '/home/ora10g/oradata/roger/users01.dbf',
17 '/home/ora10g/oradata/roger/roger01.dbf'
18 CHARACTER SET ZHS16GBK
19 ;
Control file created.
SQL> alter database open resetlogs;
Database altered.
SQL> select file_id,file_name,bytes
2 from dba_data_files
3 order by 1;
FILE_ID FILE_NAME BYTES
---------- -------------------------------------------------- ----------
1 /home/ora10g/oradata/roger/system01.dbf 461373440
2 /home/ora10g/oradata/roger/undotbs01.dbf 969932800
3 /home/ora10g/oradata/roger/sysaux01.dbf 262144000
4 /home/ora10g/oradata/roger/users01.dbf 5242880
5 /home/ora10g/oradata/roger/roger01.dbf 209715200
6 /home/ora10g/product/10.2/dbs/MISSING00006
6 rows selected.
SQL> select file#,ONLINE_STATUS,ERROR,CHANGE#
2 from V$RECOVER_FILE
3 order by 1;
FILE# ONLINE_ ERROR CHANGE#
---------- ------- ------------------- ----------
6 OFFLINE FILE MISSING 0
让人郁闷的事实发生了，还是存在。
现在问题就来了，如何彻底从这些数据字典里面清楚file 6的信息？
SQL> select file#,STATUS$,TS#,RELFILE#
2 from file$
3 order by 1;
FILE# STATUS$ TS# RELFILE#
---------- ---------- ---------- ----------
1 2 0 1
2 2 1 2
3 2 2 3
4 2 4 4
5 2 6 5
6 2 6 6
6 rows selected.
SQL> delete from file$ where file#=6;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from v$dbfile order by 1;
FILE# NAME
---------- ------------------------------------------------------------
1 /home/ora10g/oradata/roger/system01.dbf
2 /home/ora10g/oradata/roger/undotbs01.dbf
3 /home/ora10g/oradata/roger/sysaux01.dbf
4 /home/ora10g/oradata/roger/users01.dbf
5 /home/ora10g/oradata/roger/roger01.dbf
6 /home/ora10g/product/10.2/dbs/MISSING00006
6 rows selected.
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1272576 bytes
Variable Size 113247488 bytes
Database Buffers 46137344 bytes
Redo Buffers 7114752 bytes
Database mounted.
Database opened.
SQL> select ts#,NAME,ONLINE$,FLAGS from ts$ order by 1;
TS# NAME ONLINE$ FLAGS
---------- ------------------- ---------- ----------
0 SYSTEM 1 1
1 UNDOTBS1 1 17
2 SYSAUX 1 33
3 TEMP 1 2
4 USERS 1 33
5 UNDOTBS2 3 17
6 ROGER 1 33
7 rows selected.
SQL> select file_id,file_name,bytes from dba_data_files order by 1;
FILE_ID FILE_NAME BYTES
---------- -------------------------------------------------- ----------
1 /home/ora10g/oradata/roger/system01.dbf 461373440
2 /home/ora10g/oradata/roger/undotbs01.dbf 969932800
3 /home/ora10g/oradata/roger/sysaux01.dbf 262144000
4 /home/ora10g/oradata/roger/users01.dbf 5242880
5 /home/ora10g/oradata/roger/roger01.dbf 209715200
SQL> select * from v$dbfile order by 1;
FILE# NAME
---------- ------------------------------------------------------------
1 /home/ora10g/oradata/roger/system01.dbf
2 /home/ora10g/oradata/roger/undotbs01.dbf
3 /home/ora10g/oradata/roger/sysaux01.dbf
4 /home/ora10g/oradata/roger/users01.dbf
5 /home/ora10g/oradata/roger/roger01.dbf
6 /home/ora10g/product/10.2/dbs/MISSING00006
6 rows selected.
###### 通过10046 trace，发现如下信息：######
EXEC #21:c=0,e=339,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=1288205615431798
WAIT #21: nam='SQL*Net message to client' ela= 14 driver id=1650815232 #bytes=1 p3=0 obj#=41 tim=1288205615432658
WAIT #21: nam='control file sequential read' ela= 94 file#=0 block#=1 blocks=1 obj#=41 tim=1288205615432961
WAIT #21: nam='control file sequential read' ela= 29 file#=0 block#=16 blocks=1 obj#=41 tim=1288205615433060
WAIT #21: nam='control file sequential read' ela= 25 file#=0 block#=18 blocks=1 obj#=41 tim=1288205615433139
WAIT #21: nam='control file sequential read' ela= 9 file#=0 block#=30 blocks=1 obj#=41 tim=1288205615433164
FETCH #21:c=1000,e=737,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,tim=1288205615433491
WAIT #21: nam='SQL*Net message from client' ela= 615 driver id=1650815232 #bytes=1 p3=0 obj#=41 tim=1288205615434250
WAIT #21: nam='SQL*Net message to client' ela= 8 driver id=1650815232 #bytes=1 p3=0 obj#=41 tim=1288205615434439
FETCH #21:c=0,e=221,p=0,cr=0,cu=0,mis=0,r=5,dep=0,og=1,tim=1288205615434625
*** 2011-10-20 07:55:58.654
WAIT #21: nam='SQL*Net message from client' ela= 8250802 driver id=1650815232 #bytes=1 p3=0 obj#=41 tim=1288205623685646
STAT #21 id=1 cnt=6 pid=0 pos=1 obj=0 op='SORT ORDER BY (cr=0 pr=0 pw=0 time=761 us)'
STAT #21 id=2 cnt=6 pid=1 pos=1 obj=0 op='FIXED TABLE FULL X$KCCFN (cr=0 pr=0 pw=0 time=431 us)'
obj# 41，X$KCCFN 是我们需要关注的地方，首先来看看obj# 41是啥玩意儿（小于56都是bootstrap$核心对象）
SQL> select owner,object_name,object_type
2 from dba_objects
3 where object_id=41;
OWNER OBJECT_NAME OBJECT_TYPE
------------------------------ ------------------------- -------------------
SYS I_FILE1 INDEX
SQL> select metadata.get_ddl('INDEX','I_FILE1','SYS') from dual;
select metadata.get_ddl('INDEX','I_FILE1','SYS') from dual
*
ERROR at line 1:
ORA-00904: "METADATA"."GET_DDL": invalid identifier
SQL> select dbms_metadata.get_ddl('INDEX','I_FILE1','SYS') from dual;
ERROR:
ORA-25153: Temporary Tablespace is Empty
ORA-06512: at "SYS.DBMS_LOB", line 443
ORA-06512: at "SYS.DBMS_METADATA", line 3073
ORA-06512: at "SYS.DBMS_METADATA", line 4787
ORA-06512: at line 1
no rows selected
SQL> create temporary tablespace temp tempfile '/home/ora10g/oradata/roger/temp01.dbf' size 100M;
create temporary tablespace temp tempfile '/home/ora10g/oradata/roger/temp01.dbf' size 100M
*
ERROR at line 1:
ORA-01543: tablespace 'TEMP' already exists
SQL> aLTER TABLESPACE TEMP ADD TEMPFILE '/home/ora10g/oradata/roger/temp01.dbf' size 100m;
aLTER TABLESPACE TEMP ADD TEMPFILE '/home/ora10g/oradata/roger/temp01.dbf' size 100m
*
ERROR at line 1:
ORA-01119: error in creating database file '/home/ora10g/oradata/roger/temp01.dbf'
ORA-27038: created file already exists
Additional information: 1
SQL> !cd /home/ora10g/oradata/roger/
SQL> !rm /home/ora10g/oradata/roger/temp01.dbf
SQL> alter TABLESPACE TEMP ADD TEMPFILE '/home/ora10g/oradata/roger/temp01.dbf' size 100m;
Tablespace altered.
SQL> select dbms_metadata.get_ddl('INDEX','I_FILE1','SYS') from dual;
DBMS_METADATA.GET_DDL('INDEX','I_FILE1','SYS')
--------------------------------------------------------------------------------
CREATE UNIQUE INDEX "SYS"."I_FILE1" ON "SYS"."FILE$" ("FILE#")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "SYSTEM"
SQL> SELECT /*+ FULL(t1) */ t1.*,t1.rowid
2 FROM file$ t1
3 MINUS
4 SELECT /*+ index(t I_FILE1) */ t.*,t.rowid
5 FROM file$ t;
no rows selected
SQL> select FNNAM,FNFNO,FNTYP,BYTES
2 from X$KCCFN
3 order by 2;
FNNAM FNFNO FNTYP BYTES
------------------------------------------------------------ ---------- ---------- ----------
/home/ora10g/oradata/roger/redo01.log 1 3 0
/home/ora10g/oradata/roger/system01.dbf 1 4 0
/home/ora10g/oradata/roger/temp01.dbf 1 7 0
/home/ora10g/oradata/roger/redo02.log 2 3 0
/home/ora10g/oradata/roger/undotbs01.dbf 2 4 0
/home/ora10g/oradata/roger/redo03.log 3 3 0
/home/ora10g/oradata/roger/sysaux01.dbf 3 4 0
/home/ora10g/oradata/roger/users01.dbf 4 4 0
/home/ora10g/oradata/roger/roger01.dbf 5 4 0
/home/ora10g/product/10.2/dbs/MISSING00006 6 4 0
10 rows selected.
X$KCCFN 意义为[K]ernel [C]ache [C]ontrolfile management，换句话说也是从controlfile里面读取
的内容，难得说此时controlfile中还存在我们刚刚删除的file 6？
再次执行alter database backup controlfile to trace，如下：
CREATE CONTROLFILE REUSE DATABASE "ROGER" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/home/ora10g/oradata/roger/redo01.log' SIZE 50M,
GROUP 2 '/home/ora10g/oradata/roger/redo02.log' SIZE 50M,
GROUP 3 '/home/ora10g/oradata/roger/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/home/ora10g/oradata/roger/system01.dbf',
'/home/ora10g/oradata/roger/undotbs01.dbf',
'/home/ora10g/oradata/roger/sysaux01.dbf',
'/home/ora10g/oradata/roger/users01.dbf',
'/home/ora10g/oradata/roger/roger01.dbf',
'/home/ora10g/product/10.2/dbs/MISSING00006'
CHARACTER SET ZHS16GBK
;
###### 我们可以发现，这里居然多了一条'/home/ora10g/product/10.2/dbs/MISSING00006' ######
SQL> ! ls -ltr /home/ora10g/product/10.2/dbs/MISS*
ls: /home/ora10g/product/10.2/dbs/MISS*: No such file or directory
SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination /home/ora10g/product/10.2//dbs/arch
Oldest online log sequence 1
Current log sequence 1
怪异？难得是刚刚共享内存还没释放？我shutdown 数据库，过了一分钟，再次查看，
已经无 oracle 共享内存段了如下：
[ora10g@killdb roger]$ ipcs -a
------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
------ Semaphore Arrays --------
key semid owner perms nsems
------ Message Queues --------
key msqid owner perms used-bytes messages
++++++ 既然是读取的controlfile，那么我再次重建controlfile ++++++
SQL> startup nomount
ORA-00000: normal, successful completion
SQL> conn /as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1272576 bytes
Variable Size 113247488 bytes
Database Buffers 46137344 bytes
Redo Buffers 7114752 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "ROGER" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/home/ora10g/oradata/roger/redo01.log' SIZE 50M,
9 GROUP 2 '/home/ora10g/oradata/roger/redo02.log' SIZE 50M,
10 GROUP 3 '/home/ora10g/oradata/roger/redo03.log' SIZE 50M
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/home/ora10g/oradata/roger/system01.dbf',
14 '/home/ora10g/oradata/roger/undotbs01.dbf',
15 '/home/ora10g/oradata/roger/sysaux01.dbf',
16 '/home/ora10g/oradata/roger/users01.dbf',
17 '/home/ora10g/oradata/roger/roger01.dbf'
18 CHARACTER SET ZHS16GBK
19 ;
Control file created.
SQL> select FNNAM,FNFNO,FNTYP,BYTES
2 from X$KCCFN
3 order by 2;
FNNAM FNFNO FNTYP BYTES
-------------------------------------------- ---------- ---------- ----------
/home/ora10g/oradata/roger/redo01.log 1 3 0
/home/ora10g/oradata/roger/system01.dbf 1 4 0
/home/ora10g/oradata/roger/redo02.log 2 3 0
/home/ora10g/oradata/roger/undotbs01.dbf 2 4 0
/home/ora10g/oradata/roger/redo03.log 3 3 0
/home/ora10g/oradata/roger/sysaux01.dbf 3 4 0
/home/ora10g/oradata/roger/users01.dbf 4 4 0
/home/ora10g/oradata/roger/roger01.dbf 5 4 0
8 rows selected.
SQL> select FETSN, FERFN, FESTA
2 from X$KCCFE
3 order by 2;
FETSN FERFN FESTA
---------- ---------- ----------
0 1 2
1 2 2
2 3 2
4 4 2
6 5 2
SQL> select * from v$dbfile order by 1;
FILE# NAME
---------- ------------------------------------------------------------
1 /home/ora10g/oradata/roger/system01.dbf
2 /home/ora10g/oradata/roger/undotbs01.dbf
3 /home/ora10g/oradata/roger/sysaux01.dbf
4 /home/ora10g/oradata/roger/users01.dbf
5 /home/ora10g/oradata/roger/roger01.dbf
SQL> alter database open;
Database altered.
SQL> select * from v$dbfile order by 1;
FILE# NAME
---------- ------------------------------------------------------------
1 /home/ora10g/oradata/roger/system01.dbf
2 /home/ora10g/oradata/roger/undotbs01.dbf
3 /home/ora10g/oradata/roger/sysaux01.dbf
4 /home/ora10g/oradata/roger/users01.dbf
5 /home/ora10g/oradata/roger/roger01.dbf
SQL> select FETSN, FERFN, FESTA
2 from X$KCCFE
3 order by 2;
FETSN FERFN FESTA
---------- ---------- ----------
0 1 14
1 2 14
2 3 14
4 4 14
6 5 14
SQL> select file_id,file_name,bytes
2 from dba_data_files
3 order by 1;
FILE_ID FILE_NAME BYTES
---------- -------------------------------------------------- ----------
1 /home/ora10g/oradata/roger/system01.dbf 461373440
2 /home/ora10g/oradata/roger/undotbs01.dbf 969932800
3 /home/ora10g/oradata/roger/sysaux01.dbf 262144000
4 /home/ora10g/oradata/roger/users01.dbf 5242880
5 /home/ora10g/oradata/roger/roger01.dbf 209715200
SQL> select file#,TS#,RFILE# ,STATUS,OFFLINE_CHANGE#
2 from v$datafile
3 order by 1;
FILE# TS# RFILE# STATUS OFFLINE_CHANGE#
---------- ---------- ---------- ------- ---------------
1 0 1 SYSTEM 0
2 1 2 ONLINE 0
3 2 3 ONLINE 0
4 4 4 ONLINE 0
5 6 5 ONLINE 0
SQL> select file#,ONLINE_STATUS,ERROR,CHANGE#
2 from V$RECOVER_FILE
3 order by 1;
no rows selected
ok 我们彻底清除了file 6的信息，一切ok了。
补充：我这里是非归档环境，至于说归档模式下，情况如何，请听下回分解！
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
