ORA-03264: cannot drop offline datafile of locally managed tablespace

有个哥们在添加数据文件的时候，指错了表空间，然后把新加的datafile offline drop，接着在操作系统上rm了这个文件，他以为这样就能完整的删掉这个文件了，结果回到数据库里查询，发现这个文件还存在，再想添加同名的文件或者drop 这个datafile 都会报错，
其实现在不去管这个文件，继续添加别的文件，库也能正常工作，但是有这么一个offline的文件感觉很别扭
由于他的库是线上的库，所以我建议他还是别处理这个offline的文件了，继续添加别的数据文件即可
下面是我自己的一个简单测试：
SQL> select file_id,file_name from dba_data_files;

   FILE_ID FILE_NAME
---------- -------------------------------------------------------
         4 /opt/app/oracle/oradata/ora10g/users01.dbf
         3 /opt/app/oracle/oradata/ora10g/sysaux01.dbf
         2 /opt/app/oracle/oradata/ora10g/undotbs01.dbf
         1 /opt/app/oracle/oradata/ora10g/system01.dbf
         5 /opt/app/oracle/oradata/ora10g/example01.dbf
         6 /opt/app/oracle/oradata/ora10g/undotbs02.dbf
         7 /opt/app/oracle/oradata/ora10g/users02.dbf

7 rows selected.

SQL> alter datafile 7 offline drop;
alter datafile 7 offline drop
      *
ERROR at line 1:
ORA-00940: invalid ALTER command

SQL> alter database datafile 7 offline drop;

Database altered.

SQL> ! rm -rf /opt/app/oracle/oradata/ora10g/users02.dbf

SQL> select file_id,file_name from dba_data_files;

   FILE_ID FILE_NAME
---------- -------------------------------------------------------
         4 /opt/app/oracle/oradata/ora10g/users01.dbf
         3 /opt/app/oracle/oradata/ora10g/sysaux01.dbf
         2 /opt/app/oracle/oradata/ora10g/undotbs01.dbf
         1 /opt/app/oracle/oradata/ora10g/system01.dbf
         5 /opt/app/oracle/oradata/ora10g/example01.dbf
         6 /opt/app/oracle/oradata/ora10g/undotbs02.dbf
         7 /opt/app/oracle/oradata/ora10g/users02.dbf

7 rows selected.

SQL> alter tablespace users drop datafile 7;
alter tablespace users drop datafile 7
*
ERROR at line 1:
ORA-03264: cannot drop offline datafile of locally managed tablespace

SQL> alter database datafile 7 online;
alter database datafile 7 online
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/opt/app/oracle/oradata/ora10g/users02.dbf'

SQL> recover datafile 7;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 7: '/opt/app/oracle/oradata/ora10g/users02.dbf'
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '/opt/app/oracle/oradata/ora10g/users02.dbf'

想尝试用句柄恢复一下这个文件，发现在fd目录下找不到这个文件了

[root@goolen ~]# ps aux | grep dbw
root     14156  0.0  0.0 103240   844 pts/2    S+   11:49   0:00 grep dbw
oracle   21623  0.0  3.7 1482504 222752 ?      Ss   Nov25   0:43 ora_dbw0_ora10g
[root@goolen ~]# cd /proc/21623/fd
[root@goolen fd]# ll
total 0
lr-x------ 1 oracle oinstall 64 Dec 16 11:50 0 -> /dev/null
lr-x------ 1 oracle oinstall 64 Dec 16 11:50 1 -> /dev/null
lr-x------ 1 oracle oinstall 64 Dec 16 11:50 10 -> /dev/zero
lr-x------ 1 oracle oinstall 64 Dec 16 11:50 11 -> /dev/zero
lr-x------ 1 oracle oinstall 64 Dec 16 11:50 12 -> /opt/app/oracle/product/10.2.0/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 Dec 16 11:50 13 -> /opt/app/oracle/product/10.2.0/dbs/hc_ora10g.dat
lrwx------ 1 oracle oinstall 64 Dec 16 11:50 14 -> /opt/app/oracle/product/10.2.0/dbs/lkORA10G
lrwx------ 1 oracle oinstall 64 Dec 16 11:50 15 -> /opt/app/oracle/oradata/ora10g/control01.ctl
lrwx------ 1 oracle oinstall 64 Dec 16 11:50 16 -> /opt/app/oracle/oradata/ora10g/control02.ctl
lrwx------ 1 oracle oinstall 64 Dec 16 11:50 17 -> /opt/app/oracle/oradata/ora10g/control03.ctl
lrwx------ 1 oracle oinstall 64 Dec 16 11:50 18 -> /opt/app/oracle/oradata/ora10g/system01.dbf
lrwx------ 1 oracle oinstall 64 Dec 16 11:50 19 -> /opt/app/oracle/oradata/ora10g/undotbs01.dbf
l-wx------ 1 oracle oinstall 64 Dec 16 11:50 2 -> /opt/app/oracle/admin/ora10g/bdump/ora10g_dbw0_21623.trc
lrwx------ 1 oracle oinstall 64 Dec 16 11:50 20 -> /opt/app/oracle/oradata/ora10g/sysaux01.dbf
lrwx------ 1 oracle oinstall 64 Dec 16 11:50 21 -> /opt/app/oracle/oradata/ora10g/users01.dbf
lrwx------ 1 oracle oinstall 64 Dec 16 11:50 22 -> /opt/app/oracle/oradata/ora10g/example01.dbf
lrwx------ 1 oracle oinstall 64 Dec 16 11:50 23 -> /opt/app/oracle/oradata/ora10g/temp01.dbf
lr-x------ 1 oracle oinstall 64 Dec 16 11:50 24 -> /opt/app/oracle/product/10.2.0/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 Dec 16 11:50 25 -> /opt/app/oracle/oradata/ora10g/undotbs02.dbf
lrwx------ 1 oracle oinstall 64 Dec 16 11:50 26 -> socket:[1051378]
lr-x------ 1 oracle oinstall 64 Dec 16 11:50 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 Dec 16 11:50 4 -> /dev/null
l-wx------ 1 oracle oinstall 64 Dec 16 11:50 5 -> /opt/app/oracle/admin/ora10g/udump/ora10g_ora_21615.trc
l-wx------ 1 oracle oinstall 64 Dec 16 11:50 6 -> /opt/app/oracle/admin/ora10g/bdump/alert_ora10g.log
lrwx------ 1 oracle oinstall 64 Dec 16 11:50 7 -> /opt/app/oracle/product/10.2.0/dbs/lkinstora10g (deleted)
l-wx------ 1 oracle oinstall 64 Dec 16 11:50 8 -> /opt/app/oracle/admin/ora10g/bdump/alert_ora10g.log
lrwx------ 1 oracle oinstall 64 Dec 16 11:50 9 -> /opt/app/oracle/product/10.2.0/dbs/hc_ora10g.dat


[root@goolen ~]# cat /etc/fstab 
UUID=7960dff7-9c5d-4414-9d6c-ac72e8cecc42 /                       ext4    defaults        1 1

[root@goolen goolen]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/cciss/c0d0p2      49G   30G   17G  65% /
tmpfs                 2.9G     0  2.9G   0% /dev/shm
/dev/cciss/c0d0p1      97M   34M   59M  36% /boot
/dev/cciss/c0d1p1     135G   46G   82G  36% /tol

使用的是ext4文件系统，打算尝试用恢复工具恢复一下，结果也失败了：
[root@goolen goolen]# extundelete /dev/cciss/c0d0p2 --restore-file '/opt/app/oracle/oradata/ora10g/users02.dbf'     
WARNING: Extended attributes are not restored.
WARNING: EXT3_FEATURE_INCOMPAT_RECOVER is set.
The partition should be unmounted to undelete any files without further data loss.
If the partition is not currently mounted, this message indicates 
it was improperly unmounted, and you should run fsck before continuing.
If you decide to continue, extundelete may overwrite some of the deleted
files and make recovering those files impossible.  You should unmount the
file system and check it with fsck before using extundelete.
Would you like to continue? (y/n) 
y
Loading filesystem metadata ... 391 groups loaded.
Loading journal descriptors ... 24946 descriptors loaded.
Writing output to directory RECOVERED_FILES/
Unable to restore inode 422149 (opt/app/oracle/oradata/ora10g/users02.dbf): No undeleted copies found in the journal.

[root@goolen goolen]# extundelete /dev/cciss/c0d0p2 --restore-all
WARNING: Extended attributes are not restored.
WARNING: EXT3_FEATURE_INCOMPAT_RECOVER is set.
The partition should be unmounted to undelete any files without further data loss.
If the partition is not currently mounted, this message indicates 
it was improperly unmounted, and you should run fsck before continuing.
If you decide to continue, extundelete may overwrite some of the deleted
files and make recovering those files impossible.  You should unmount the
file system and check it with fsck before using extundelete.
Would you like to continue? (y/n) 
y
Loading filesystem metadata ... 391 groups loaded.
Loading journal descriptors ... 24938 descriptors loaded.
Searching for recoverable inodes in directory / ... 
0 recoverable inodes found.
Looking through the directory structure for deleted files ... 
0 recoverable inodes still lost.
No files were undeleted.

[root@goolen ~]# ext3grep /dev/cciss/c0d0p2 --ls --inode 2          
Running ext3grep version 0.10.2
WARNING: I don't know what EXT3_FEATURE_COMPAT_EXT_ATTR is.
WARNING: EXT3_FEATURE_INCOMPAT_RECOVER is set. This either means that your partition is still mounted, and/or the file system is in an unclean state.
ext3grep: ext3grep.cc:119: void run_program(): Assertion `be2le(journal_super_block.s_header.h_magic) == 0xc03b3998U' failed.
Aborted (core dumped)

[root@goolen goolen]# echo "lsdel" | debugfs /dev/cciss/c0d0p2
debugfs 1.41.14 (22-Dec-2010)
debugfs:  lsdel
 Inode  Owner  Mode    Size      Blocks   Time deleted
0 deleted inodes found.

最后直接从file$基表里把这个文件删除，再重建控制文件：
SQL> select file_id,file_name,status,online_status from dba_data_files;

   FILE_ID FILE_NAME                                               STATUS                      ONLINE_STATUS
---------- ------------------------------------------------------- --------------------------- ---------------------
         4 /opt/app/oracle/oradata/ora10g/users01.dbf              AVAILABLE                   ONLINE
         3 /opt/app/oracle/oradata/ora10g/sysaux01.dbf             AVAILABLE                   ONLINE
         2 /opt/app/oracle/oradata/ora10g/undotbs01.dbf            AVAILABLE                   ONLINE
         1 /opt/app/oracle/oradata/ora10g/system01.dbf             AVAILABLE                   SYSTEM
         5 /opt/app/oracle/oradata/ora10g/example01.dbf            AVAILABLE                   ONLINE
         6 /opt/app/oracle/oradata/ora10g/undotbs02.dbf            AVAILABLE                   ONLINE
         7 /opt/app/oracle/oradata/ora10g/users02.dbf              AVAILABLE                   RECOVER

7 rows selected.

SQL> select file#,status$, from file$;
select file#,status$, from file$
                      *
ERROR at line 1:
ORA-00936: missing expression

SQL> select file#,status$ from file$;
     FILE#    STATUS$
---------- ----------
         1          2
         2          2
         3          2
         4          2
         5          2
         6          2
         7          2

7 rows selected.

SQL> delete file$ where FILE#=7;

1 row deleted.

SQL> commit;

Commit complete.

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

Database altered.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 1358954496 bytes
Fixed Size                  2083816 bytes
Variable Size             436208664 bytes
Database Buffers          905969664 bytes
Redo Buffers               14692352 bytes
Database mounted.
Database opened.
SQL> select file_id,file_name,bytes from dba_data_files;

   FILE_ID FILE_NAME                                                    BYTES
---------- ------------------------------------------------------- ----------
         4 /opt/app/oracle/oradata/ora10g/users01.dbf                23592960
         3 /opt/app/oracle/oradata/ora10g/sysaux01.dbf              367001600
         2 /opt/app/oracle/oradata/ora10g/undotbs01.dbf             104857600
         1 /opt/app/oracle/oradata/ora10g/system01.dbf              534773760
         5 /opt/app/oracle/oradata/ora10g/example01.dbf             104857600
         6 /opt/app/oracle/oradata/ora10g/undotbs02.dbf               1048576

6 rows selected.

SQL> alter tablespace users add datafile '/opt/app/oracle/oradata/ora10g/users02.dbf' size 1m ;
alter tablespace users add datafile '/opt/app/oracle/oradata/ora10g/users02.dbf' size 1m
*
ERROR at line 1:
ORA-01537: cannot add file '/opt/app/oracle/oradata/ora10g/users02.dbf' - file already part of database


SQL> CREATE CONTROLFILE REUSE DATABASE "ORA10G" NORESETLOGS  NOARCHIVELOG 
  2      MAXLOGFILES 16 
  3      MAXLOGMEMBERS 3 
  4      MAXDATAFILES 100 
  5      MAXINSTANCES 8 
  6      MAXLOGHISTORY 292 
  7  LOGFILE 
  8    GROUP 1 '/opt/app/oracle/oradata/ora10g/redo01.log'  SIZE 50M, 
  9    GROUP 2 '/opt/app/oracle/oradata/ora10g/redo02.log'  SIZE 50M, 
 10    GROUP 3 '/opt/app/oracle/oradata/ora10g/redo03.log'  SIZE 50M 
 11  -- STANDBY LOGFILE 
 12  DATAFILE 
 13    '/opt/app/oracle/oradata/ora10g/system01.dbf', 
 14    '/opt/app/oracle/oradata/ora10g/undotbs01.dbf', 
 15    '/opt/app/oracle/oradata/ora10g/sysaux01.dbf', 
 16    '/opt/app/oracle/oradata/ora10g/users01.dbf', 
 17    '/opt/app/oracle/oradata/ora10g/example01.dbf', 
 18    '/opt/app/oracle/oradata/ora10g/undotbs02.dbf'
 19  CHARACTER SET WE8ISO8859P1 
 20  ; 

Control file created.

SQL> alter database open;

Database altered.

SQL> select file_id,file_name,bytes from dba_data_files;

   FILE_ID FILE_NAME                                                    BYTES
---------- ------------------------------------------------------- ----------
         6 /opt/app/oracle/oradata/ora10g/undotbs02.dbf               1048576
         5 /opt/app/oracle/oradata/ora10g/example01.dbf             104857600
         4 /opt/app/oracle/oradata/ora10g/users01.dbf                23592960
         3 /opt/app/oracle/oradata/ora10g/sysaux01.dbf              367001600
         2 /opt/app/oracle/oradata/ora10g/undotbs01.dbf             104857600
         1 /opt/app/oracle/oradata/ora10g/system01.dbf              534773760

6 rows selected.

SQL> alter tablespace users add datafile '/opt/app/oracle/oradata/ora10g/users02.dbf' size 1m ;

Tablespace altered.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23249684/viewspace-1369573/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23249684/viewspace-1369573/