云计算服务
服务器租用
DDOS 防护
虚拟主机
域名服务
基础设施
关于我们
问题描述：当采用以下步骤删除数据文件后发现v$datafile视图中仍然有该文件记录,且状态recover，意识到该删除步骤有问题，生产环境中存在此种情况，此时作为dba应该怎么办？
a、 使用alterdatabasedatafile5offline;
b、rm命令删掉数据文件
实验目标：模拟该异常，并提供解决方案.
实验环境：oracle 11.2.0.4 + 单实例
1、模拟异常
SQL> alter tablespace users add datafile '/u01/app/oracle/oradata/leodb/users02.dbf' size 10m autoextend on;
Tablespace altered.
SQL> select file#,name from v$datafile
FILE# NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/leodb/system01.dbf
2 /u01/app/oracle/oradata/leodb/sysaux01.dbf
3 /u01/app/oracle/oradata/leodb/undotbs01.dbf
4 /u01/app/oracle/oradata/leodb/users01.dbf
5 /u01/app/oracle/oradata/leodb/users02.dbf
SQL> select d.tablespace_name,v.file#,d.file_name,d.bytes/1024/1024,v.status,v.enabled,d.autoextensible,d.increment_by from dba_data_files d,v$datafile v where d.file_id=v.file#
TABLESPACE_NAME FILE# FILE_NAME D.BYTES/1024/1024 STATUS ENABLED AUT INCREMENT_BY
--------------- ---------- -------------------------------------------------- ----------------- ------- ---------- --- ------------
USERS 4 /u01/app/oracle/oradata/leodb/users01.dbf 5 ONLINE READ WRITE YES 160
UNDOTBS1 3 /u01/app/oracle/oradata/leodb/undotbs01.dbf 70 ONLINE READ WRITE YES 640
SYSAUX 2 /u01/app/oracle/oradata/leodb/sysaux01.dbf 490 ONLINE READ WRITE YES 1280
SYSTEM 1 /u01/app/oracle/oradata/leodb/system01.dbf 740 SYSTEM READ WRITE YES 1280
USERS 5 /u01/app/oracle/oradata/leodb/users02.dbf 10 ONLINE READ WRITE YES 1
现使用错误方法删数据文件users02.dbf
SQL> alter database datafile 5 offline;
Database altered.
现在系统层面删除数据文件users02.dbf
[oracle@leo leodb]$ ll
total 1531236
-rw-r----- 1 oracle asmadmin 9748480 Aug 7 13:54 control01.ctl
-rw-r----- 1 oracle asmadmin 52429312 Aug 7 13:23 redo01.log
-rw-r----- 1 oracle asmadmin 52429312 Aug 7 13:53 redo02.log
-rw-r----- 1 oracle asmadmin 52429312 Aug 7 13:23 redo03.log
-rw-r----- 1 oracle asmadmin 534781952 Aug 7 13:54 sysaux01.dbf
-rw-r----- 1 oracle asmadmin 775954432 Aug 7 13:54 system01.dbf
-rw-r----- 1 oracle asmadmin 30416896 Aug 7 12:33 temp01.dbf
-rw-r----- 1 oracle asmadmin 73408512 Aug 7 13:54 undotbs01.dbf
-rw-r----- 1 oracle asmadmin 5251072 Aug 7 13:23 users01.dbf
-rw-r----- 1 oracle asmadmin 10493952 Aug 7 13:23 users02.dbf
[oracle@leo leodb]$ rm -rf users02.dbf
[oracle@leo leodb]$ pwd
/u01/app/oracle/oradata/leodb
此时通过以下语句查询发现users02.dbf状态为recover，v$datafile中仍然有该数据文件记录,意识到操作错误.
SQL> select d.tablespace_name,v.file#,d.file_name,d.bytes/1024/1024,v.status,v.enabled,d.autoextensible,d.increment_by from dba_data_files d,v$datafile v where d.file_id=v.file#;
TABLESPACE_NAME FILE# FILE_NAME D.BYTES/1024/1024 STATUS ENABLED AUT INCREMENT_BY
--------------- ---------- -------------------------------------------------- ----------------- ------- ---------- --- ------------
USERS 4 /u01/app/oracle/oradata/leodb/users01.dbf 5 ONLINE READ WRITE YES 160
UNDOTBS1 3 /u01/app/oracle/oradata/leodb/undotbs01.dbf 70 ONLINE READ WRITE YES 640
SYSAUX 2 /u01/app/oracle/oradata/leodb/sysaux01.dbf 510 ONLINE READ WRITE YES 1280
SYSTEM 1 /u01/app/oracle/oradata/leodb/system01.dbf 740 SYSTEM READ WRITE YES 1280
USERS 5 /u01/app/oracle/oradata/leodb/users02.dbf RECOVER READ WRITE
SQL> select name from v$datafile;
NAME
--------------------------------------------------
/u01/app/oracle/oradata/leodb/system01.dbf
/u01/app/oracle/oradata/leodb/sysaux01.dbf
/u01/app/oracle/oradata/leodb/undotbs01.dbf
/u01/app/oracle/oradata/leodb/users01.dbf
/u01/app/oracle/oradata/leodb/users02.dbf
说明：成功模拟出异常现象.
2、解决方案
a、添加同路径同名数据文件
b、RECOVER并ONLINE该文件
c、用正确方法进行删除
操作过程：
SQL> alter database create datafile 5 as '/u01/app/oracle/oradata/leodb/users02.dbf' size 10m;
Database altered.
[oracle@leo leodb]$ ll
total 1531244
-rw-r----- 1 oracle asmadmin 9748480 Aug 7 14:24 control01.ctl
-rw-r----- 1 oracle asmadmin 52429312 Aug 7 13:23 redo01.log
-rw-r----- 1 oracle asmadmin 52429312 Aug 7 14:24 redo02.log
-rw-r----- 1 oracle asmadmin 52429312 Aug 7 13:23 redo03.log
-rw-r----- 1 oracle asmadmin 534781952 Aug 7 14:24 sysaux01.dbf
-rw-r----- 1 oracle asmadmin 775954432 Aug 7 14:24 system01.dbf
-rw-r----- 1 oracle asmadmin 30416896 Aug 7 14:18 temp01.dbf
-rw-r----- 1 oracle asmadmin 73408512 Aug 7 14:23 undotbs01.dbf
-rw-r----- 1 oracle asmadmin 5251072 Aug 7 13:23 users01.dbf
-rw-r----- 1 oracle asmadmin 10493952 Aug 7 14:24 users02.dbf
SQL> recover datafile 5;
Media recovery complete.
SQL> alter database datafile '/u01/app/oracle/oradata/leodb/users02.dbf' online;
Database altered.
SQL> alter tablespace users drop datafile '/u01/app/oracle/oradata/leodb/users02.dbf';
Tablespace altered.
SQL> host ls -l /u01/app/oracle/oradata/leodb/users02.dbf
ls: cannot access /u01/app/oracle/oradata/leodb/users02.dbf: No such file or directory
SQL> select d.tablespace_name,v.file#,d.file_name,d.bytes/1024/1024,v.status,v.enabled,d.autoextensible,d.increment_by from dba_data_files d,v$datafile v where d.file_id=v.file#;
TABLESPACE_NAME FILE# FILE_NAME D.BYTES/1024/1024 STATUS ENABLED AUT INCREMENT_BY
--------------- ---------- -------------------------------------------------- ----------------- ------- ---------- --- ------------
USERS 4 /u01/app/oracle/oradata/leodb/users01.dbf 5 ONLINE READ WRITE YES 160
UNDOTBS1 3 /u01/app/oracle/oradata/leodb/undotbs01.dbf 70 ONLINE READ WRITE YES 640
SYSAUX 2 /u01/app/oracle/oradata/leodb/sysaux01.dbf 510 ONLINE READ WRITE YES 1280
SYSTEM 1 /u01/app/oracle/oradata/leodb/system01.dbf 740 SYSTEM READ WRITE YES 1280
[oracle@leo leodb]$ ll
total 1520996
-rw-r----- 1 oracle asmadmin 9748480 Aug 7 14:26 control01.ctl
-rw-r----- 1 oracle asmadmin 52429312 Aug 7 13:23 redo01.log
-rw-r----- 1 oracle asmadmin 52429312 Aug 7 14:26 redo02.log
-rw-r----- 1 oracle asmadmin 52429312 Aug 7 13:23 redo03.log
-rw-r----- 1 oracle asmadmin 534781952 Aug 7 14:24 sysaux01.dbf
-rw-r----- 1 oracle asmadmin 775954432 Aug 7 14:24 system01.dbf
-rw-r----- 1 oracle asmadmin 30416896 Aug 7 14:18 temp01.dbf
-rw-r----- 1 oracle asmadmin 73408512 Aug 7 14:23 undotbs01.dbf
-rw-r----- 1 oracle asmadmin 5251072 Aug 7 13:23 users01.dbf
说明：users02.dbf被成功删除，系统层面及v$datafile中已不存在该文件记录.
.png)
.png)
