如何彻底删除已经不存在的数据文件？
本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客
本文链接地址: 如何彻底删除已经不存在的数据文件？
|
1 |
如何彻底清掉已经offline drop的数据文件呢？该问题源于itpub的一个帖子： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
<div id="B" style="height:auto; width:auto;OVERFLOW-y:auto;border:blue 1px solid;margin:10px"> 数据文件物理性删除相关问题疑惑? 1. 在生产环境上一个用户下新增了一个数据文件； alter TABLESPACE KDPDM_DATA add dataFILE '/oradata/crmdb1/rlv_kdpdm_data06_40g.dbf' SIZE 40G ； (-- 提示：kdpdm_data 用户之前就有5个数据文件) 2. 后来因为看文件跟以前格式不对，用 ssh 物理性删掉了这个文件。在做insert into 操作时，报错如下： <a href="http://www.killdb.com/wp-content/uploads/2011/10/my_ora_01116.jpg"><img src="http://www.killdb.com/wp-content/uploads/2011/10/my_ora_01116.jpg" alt="" title="my_ora_01116" width="452" height="152" class="aligncenter size-full wp-image-719" /></a> 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，而启动失败， 我现在想删掉这个数据文件的配置数据，保证以后重启数据库能正常启动，请问该如何操作呢？ </div> |
|
1 2 3 4 |
网友xifenfei问道，是否有什么办法能够彻底清楚已经不存在的数据文件，虽然已经 执行了offline drop，但是查询相关的数据字典试图仍然能够查询到，令人非常不爽。 如下是我的实验全过程，针对该问题进行的解答. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 |
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 ; |
|
1 2 3 |
我们可以看到，虽然drop了datafile，但是controlfile中仍然有其信息， 如果此时在操作系统上rm删除该数据文件，在启动的时候，必然报错找不到 但是并不是说导致数据库无法启动，是可以正常启动的，如下； |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 |
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 |
|
1 2 3 |
让人郁闷的事实发生了，还是存在。 现在问题就来了，如何彻底从这些数据字典里面清楚file 6的信息？ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 |
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. |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
###### 通过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$核心对象） |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
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. |
|
1 2 3 4 |
X$KCCFN 意义为[K]ernel [C]ache [C]ontrolfile management，换句话说也是从controlfile里面读取 的内容，难得说此时controlfile中还存在我们刚刚删除的file 6？ 再次执行alter database backup controlfile to trace，如下： |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
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' ###### |
|
1 2 3 4 5 6 7 8 9 10 11 |
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 |
|
1 2 |
怪异？难得是刚刚共享内存还没释放？我shutdown 数据库，过了一分钟，再次查看， 已经无 oracle 共享内存段了如下： |
|
1 2 3 4 5 6 7 8 9 10 |
[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 |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 |
++++++ 既然是读取的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 |
|
1 2 3 |
ok 我们彻底清除了file 6的信息，一切ok了。 补充：我这里是非归档环境，至于说归档模式下，情况如何，请听下回分解！ |
3 Responses to “如何彻底删除已经不存在的数据文件？”
归档情况可先试试：
SQL> alter database create datafile ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF’ as ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02.DBF’;
数据库已更改。
SQL> recover datafile 7;
完成介质恢复。
SQL> alter database datafile 7 online;
数据库已更改。
你好！我按照你的操作步骤，create control file后，查看相关信息，都没有那个missing的文件，但是一旦alter database open之后，在同样语句查看相关信息，就又出现missing的datafile了。我用的是11gr2版本。请帮忙确认。
我这里也又一个这样的库，一直没敢重建 ControlFile 。。。
Leave a Reply
You must be logged in to post a comment.