sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle报错ORA-27014 彻底删除已经不存在的数据文件方法

时间:2016-11-25 23:06   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心案例分析:分享一篇Oracle报错ORA-27014,如何彻底删除已经不存在的数据文件?

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客
本文链接地址: 如何彻底删除已经不存在的数据文件?

如何彻底清掉已经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,而启动失败,我现在想删掉这个数据文件的配置数据,保证以后重启数据库能正常启动,请问该如何操作呢?

是否有什么办法能够彻底清楚已经不存在的数据文件,虽然已经执行了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    http://www.oracleplus.netORDER 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@oracleplus~]$ cd /home/ora10g/oradata/roger/
[ora10g@oracleplusroger]$ 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@oracleplusroger]$ cp control01.ctl control01.ctl.bak
[ora10g@oracleplusroger]$ 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@oracleplusroger]$ 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了。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-27014 彻底删除已经不存在的数据文件方法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1323.html

Oracle研究中心

关键词:

Oracle报错ORA-27014

ORA-27014解决办法笔记

如何彻底删除已经不存在的数据文件?