1.彻底删除数据文件

尝试通过重建控制文件的方式,清理错误创建的数据文件。使用 “OFFLINE FOR DROP” 将该文件从数据库中删除，改文件在数据库字典中标记为 recover 。 用户想彻底的删除该文件，且该文件记录的存在影响了数据库的备份恢复。
参考文档：
http://bytehouse.org.cn/doc/3191/
http://bytehouse.org.cn/doc/3190/

如果谁有更好的方法不灵赐教。

2.数据库字典删除数据文件

select file#,status$ from file$;
复制

SQL> delete file$ where FILE#=124;

1 row deleted.

SQL> commit;

Commit complete.

SQL> 
复制

此时数据字典中的信息已删除，但控制文件仍留有记录，需要重建控制

3.重建控制文件

step 1.首先生成重建控制文件的脚本：

SQL> alter database backup controlfile to trace as '/home/oracle/jcpt.ctl';

Database altered.
复制

数据库的Alert log中也会详细输出这个文件的路径和名字。

step 2.设置cluster_database=false：
在时候需要把RAC环境设置未EXCLUSIVE模式，才可以执行重建控制文件操作

SQL> alter system set cluster_database=false scope=spfile;
System altered.
复制

否则，在重建控制文件的时候会报下面的错误：

CREATE CONTROLFILE REUSE DATABASE "RACDB" RESETLOGS  ARCHIVELOG
*
ERROR at line 1:
ORA-01503: CREATE CONTROLFILE failed
ORA-12720: operation requires database is in EXCLUSIVE mode
复制

step 3.停止所有数据库实例：

[oracle@rac1 trace]$ srvctl stop database -d RACDB
[oracle@rac1 trace]$ srvctl status database -d RACDB
Instance RACDB1 is not running on node rac1
Instance RACDB2 is not running on node rac2
复制

step 4.用resetlogs模式重建控制文件：

[oracle@rac1 trace]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 8 12:45:25 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area  739065856 bytes
Fixed Size                  2232032 bytes
Variable Size             549454112 bytes
Database Buffers          184549376 bytes
Redo Buffers                2830336 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "RACDB" RESETLOGS  ARCHIVELOG
 2      MAXLOGFILES 192
 3      MAXLOGMEMBERS 3
 4      MAXDATAFILES 1024
 5      MAXINSTANCES 32
 6      MAXLOGHISTORY 292
 7  LOGFILE
 8    GROUP 1 (
 9      '+DATA/racdb/onlinelog/group_1.261.783272805',
10      '+RECO/racdb/onlinelog/group_1.257.783272807'
11    ) SIZE 50M BLOCKSIZE 512,
12    GROUP 2 (
13      '+DATA/racdb/onlinelog/group_2.262.783272807',
14      '+RECO/racdb/onlinelog/group_2.258.783272809'
15    ) SIZE 50M BLOCKSIZE 512
16  -- STANDBY LOGFILE
17  DATAFILE
18    '+DATA/racdb/datafile/system.256.783272707',
19    '+DATA/racdb/datafile/sysaux.257.783272707',
20    '+DATA/racdb/datafile/undotbs1.258.783272707',
21    '+DATA/racdb/datafile/users.259.783272707',
22    '+DATA/racdb/datafile/example.264.783272831',
23    '+DATA/racdb/datafile/undotbs2.265.783273081'
24  CHARACTER SET AL32UTF8
25  ;

Control file created.

SQL> RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;  <=========必须使用UNTIL CANCEL，否则数据库无法open
ORA-00279: change 6976933 generated at 01/08/2013 12:45:12 needed for thread 1
ORA-00289: suggestion : +RECO
ORA-00280: change 6976933 for thread 1 is in sequence #2


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
CANCEL 
Media recovery cancelled.
复制

接下来需要加其他thread，因为用resetlogs重建controlfile只是增加了thread为1的redo log：

SQL> ALTER DATABASE ADD LOGFILE THREAD 2
 2    GROUP 3 (
 3      '+DATA/racdb/onlinelog/group_3.269.804115405',
 4      '+RECO/racdb/onlinelog/group_3.261.804115405'
 5    ) SIZE 50M BLOCKSIZE 512 REUSE,
 6    GROUP 4 (
 7      '+DATA/racdb/onlinelog/group_4.270.804115405',
 8      '+RECO/racdb/onlinelog/group_4.263.804115407'
 9    ) SIZE 50M BLOCKSIZE 512 REUSE;
ALTER DATABASE ADD LOGFILE THREAD 2
*
ERROR at line 1:
ORA-01276: Cannot add file +DATA/racdb/onlinelog/group_3.269.804115405.  File
has an Oracle Managed Files file name. 
复制

对于ASM，使用了OMF命名规则时不能指定具体的文件名，只需要指定diskgroup名即可：

SQL> ALTER DATABASE ADD LOGFILE THREAD 2
 2    GROUP 3 (
 3      '+DATA',
 4      '+RECO'
 5    ) SIZE 50M BLOCKSIZE 512 REUSE,
 6    GROUP 4 (
 7      '+DATA',
 8      '+RECO'
 9    ) SIZE 50M BLOCKSIZE 512 REUSE;

Database altered.

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '+DATA/racdb/tempfile/temp.263.783272821'
 2           SIZE 39845888  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

Tablespace altered.
复制

step 5. 设置cluster_database=true :

SQL> alter system set cluster_database=true scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
复制

cluster_database 参数错误，启动数据库会报错如下：

ORA-29707: inconsistent value 0 for initialization parameter cluster_database with other instances
复制

step 6. 启动所有实例：

[oracle@rac1 trace]$ srvctl start database -d RACDB
[oracle@rac1 trace]$ srvctl status database -d RACDB
Instance RACDB1 is running on node rac1
Instance RACDB2 is running on node rac2 
复制