清除离线数据文件记录

发表于 2011 年 10 月 21 日 由 惜分飞

联系：手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题：清除离线数据文件记录

作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

测试前提：数据文件离线，系统上删除了该文件，需要删除在数据字典中，关于这条离线数据文件记录

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/oradata/test/archivelog
Oldest online log sequence     210
Next log sequence to archive   212
Current log sequence           212
--数据库是归档模式

SQL> col file_name for a40
SQL>  select file_id,file_name,bytes from dba_data_files order by 1;

   FILE_ID FILE_NAME                                     BYTES
---------- ---------------------------------------- ----------
         1 /opt/oracle/oradata/test/system01.dbf     524288000
         2 /opt/oracle/oradata/test/undotbs01.dbf   1289748480
         3 /opt/oracle/oradata/test/sysaux01.dbf     377487360
         4 /opt/oracle/oradata/test/users01.dbf        5242880
         5 /opt/oracle/oradata/test/user32g.dbf       10485760
         6 /opt/oracle/oradata/test/xifenfei01.dbf    20971520
         7 /opt/oracle/oradata/test/user02.dbf        10485760
         8 /opt/oracle/oradata/test/odu02.dbf       1.1283E+10
         9 /opt/oracle/oradata/test/odu01.dbf        104857600
        10 /opt/oracle/oradata/test/odu03.chf                            

10 rows selected.

SQL> col error for a20
SQL>  select file#,ONLINE_STATUS,ERROR,CHANGE# from V$RECOVER_FILE order by 1;

     FILE# ONLINE_ ERROR                   CHANGE#
---------- ------- -------------------- ----------
        10 OFFLINE FILE NOT FOUND                0

SQL> !ls /opt/oracle/oradata/test/odu03.chf
ls: /opt/oracle/oradata/test/odu03.chf: No such file or directory
--说明该数据文件已经从硬盘上删除

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  2082784 bytes
Variable Size             130025504 bytes
Database Buffers           71303168 bytes
Redo Buffers                6303744 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/opt/oracle/oradata/test/redo01.log'  SIZE 50M,
  9    GROUP 2 '/opt/oracle/oradata/test/redo02.log'  SIZE 50M,
 10    GROUP 3 '/opt/oracle/oradata/test/redo03.log'  SIZE 50M
 11  DATAFILE
 12    '/opt/oracle/oradata/test/system01.dbf',
 13    '/opt/oracle/oradata/test/undotbs01.dbf',
 14    '/opt/oracle/oradata/test/sysaux01.dbf',
 15    '/opt/oracle/oradata/test/users01.dbf',
 16    '/opt/oracle/oradata/test/user32g.dbf',
 17    '/opt/oracle/oradata/test/xifenfei01.dbf',
 18    '/opt/oracle/oradata/test/user02.dbf',
 19    '/opt/oracle/oradata/test/odu02.dbf',
 20    '/opt/oracle/oradata/test/odu01.dbf'
        ,'/opt/oracle/oradata/test/odu03.chf'     --文件不存在，创建控制文件这条记录需要除掉
 21  CHARACTER SET ZHS16GBK
 22  ;

Control file created.


SQL> alter database open;

Database altered.

SQL> select file_id,file_name,bytes from dba_data_files order by 1;

   FILE_ID FILE_NAME                                     BYTES
---------- ---------------------------------------- ----------
         1 /opt/oracle/oradata/test/system01.dbf     524288000
         2 /opt/oracle/oradata/test/undotbs01.dbf   1289748480
         3 /opt/oracle/oradata/test/sysaux01.dbf     377487360
         4 /opt/oracle/oradata/test/users01.dbf        5242880
         5 /opt/oracle/oradata/test/user32g.dbf       10485760
         6 /opt/oracle/oradata/test/xifenfei01.dbf    20971520
         7 /opt/oracle/oradata/test/user02.dbf        10485760
         8 /opt/oracle/oradata/test/odu02.dbf       1.1283E+10
         9 /opt/oracle/oradata/test/odu01.dbf        104857600
        10 /opt/oracle/product/10.2.0/db_1/dbs/MISSING00010     --系统默认创建了自定义的数据文件名称

10 rows selected.

SQL> select file#,ONLINE_STATUS,ERROR,CHANGE# from V$RECOVER_FILE order by 1;

     FILE# ONLINE_ ERROR                   CHANGE#
---------- ------- -------------------- ----------
        10 OFFLINE FILE MISSING                  0  
--提示该文件是离线状态，需要恢复，结果同开始时候状态

SQL> select file#,STATUS$,TS#,RELFILE# from file$ 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          4          5
         6          2          6          6
         7          2          4          7
         8          2          7          9
         9          2          7          6
        10          2          7         10
        11          1

11 rows selected.

SQL> delete from file$ where file#=10;    ---重要的就是这个操作

1 row deleted.

SQL> select file#,STATUS$,TS#,RELFILE# from file$ 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          4          5
         6          2          6          6
         7          2          4          7
         8          2          7          9
         9          2          7          6
        11          1

10 rows selected.

SQL> col name for a40
SQL> select * from v$dbfile order by 1;

     FILE# NAME
---------- ----------------------------------------
         1 /opt/oracle/oradata/test/system01.dbf
         2 /opt/oracle/oradata/test/undotbs01.dbf
         3 /opt/oracle/oradata/test/sysaux01.dbf
         4 /opt/oracle/oradata/test/users01.dbf
         5 /opt/oracle/oradata/test/user32g.dbf
         6 /opt/oracle/oradata/test/xifenfei01.dbf
         7 /opt/oracle/oradata/test/user02.dbf
         8 /opt/oracle/oradata/test/odu02.dbf
         9 /opt/oracle/oradata/test/odu01.dbf
        10 /opt/oracle/product/10.2.0/db_1/dbs/MISSING00010

10 rows selected.
--需要重建控制文件，删除不存在的数据文件

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP NOMOUNT
ORACLE instance started.

Total System Global Area  209715200 bytes
Fixed Size                  2082784 bytes
Variable Size             130025504 bytes
Database Buffers           71303168 bytes
Redo Buffers                6303744 bytes

SQL> CREATE CONTROLFILE REUSE DATABASE "TEST" NORESETLOGS  ARCHIVELOG
  2      MAXLOGFILES 16
  3      MAXLOGMEMBERS 3
  4      MAXDATAFILES 100
  5      MAXINSTANCES 8
  6      MAXLOGHISTORY 292
  7  LOGFILE
  8    GROUP 1 '/opt/oracle/oradata/test/redo01.log'  SIZE 50M,
  9    GROUP 2 '/opt/oracle/oradata/test/redo02.log'  SIZE 50M,
 10    GROUP 3 '/opt/oracle/oradata/test/redo03.log'  SIZE 50M
 11  DATAFILE
 12    '/opt/oracle/oradata/test/system01.dbf',
 13    '/opt/oracle/oradata/test/undotbs01.dbf',
 14    '/opt/oracle/oradata/test/sysaux01.dbf',
 15    '/opt/oracle/oradata/test/users01.dbf',
 16    '/opt/oracle/oradata/test/user32g.dbf',
 17    '/opt/oracle/oradata/test/xifenfei01.dbf',
 18    '/opt/oracle/oradata/test/user02.dbf',
 19    '/opt/oracle/oradata/test/odu02.dbf',
 20    '/opt/oracle/oradata/test/odu01.dbf'
 21  CHARACTER SET ZHS16GBK
 22  ;

Control file created.

SQL> alter database open;

Database altered.

SQL> select file_id,file_name,bytes from dba_data_files order by 1;

   FILE_ID FILE_NAME                                     BYTES
---------- ---------------------------------------- ----------
         1 /opt/oracle/oradata/test/system01.dbf     524288000
         2 /opt/oracle/oradata/test/undotbs01.dbf   1289748480
         3 /opt/oracle/oradata/test/sysaux01.dbf     377487360
         4 /opt/oracle/oradata/test/users01.dbf        5242880
         5 /opt/oracle/oradata/test/user32g.dbf       10485760
         6 /opt/oracle/oradata/test/xifenfei01.dbf    20971520
         7 /opt/oracle/oradata/test/user02.dbf        10485760
         8 /opt/oracle/oradata/test/odu02.dbf       1.1283E+10
         9 /opt/oracle/oradata/test/odu01.dbf        104857600

9 rows selected.

SQL> select * from v$dbfile order by 1;

     FILE# NAME
---------- ----------------------------------------
         1 /opt/oracle/oradata/test/system01.dbf
         2 /opt/oracle/oradata/test/undotbs01.dbf
         3 /opt/oracle/oradata/test/sysaux01.dbf
         4 /opt/oracle/oradata/test/users01.dbf
         5 /opt/oracle/oradata/test/user32g.dbf
         6 /opt/oracle/oradata/test/xifenfei01.dbf
         7 /opt/oracle/oradata/test/user02.dbf
         8 /opt/oracle/oradata/test/odu02.dbf
         9 /opt/oracle/oradata/test/odu01.dbf

9 rows selected.

补充说明：非归档模式下，NOARCHIVELOG创建控制文件，其他无太大区别
测试来源：itpub:数据文件物理性删除相关问题疑惑?
参考blog：roger:如何彻底删除已经不存在的数据文件？

此条目发表在 Oracle备份恢复 分类目录。将固定链接加入收藏夹。

《清除离线数据文件记录》有 5 条评论

  1. 惜分飞 说：
    2014 年 8 月 26 日22:28

    当然可以了

    看了博客内容,重建了两次控制文件,是否只建一次控制文件,在开始就进行删除操作

  2. 惜分飞 说：
    2014 年 8 月 26 日22:27

    查询文件的状态，你可以对应到v$datafile里面的file$列，看看各个列的含义

    select file#,STATUS$,TS#,RELFILE# from file$ order by 1;
    这步在里面是什么意思？

  3. veronica 说：
    2014 年 8 月 26 日11:29

    select file#,STATUS$,TS#,RELFILE# from file$ order by 1;
    这步在里面是什么意思？

  4. chengweihua 说：
    2012 年 11 月 16 日09:49

    看了博客内容,重建了两次控制文件,是否只建一次控制文件,在开始就进行删除操作

  5. 惜 分飞 说：
    2011 年 10 月 21 日23:44 
    create table file$                                             /* file table */
( file#         number not null,                   /* file identifier number */
  status$       number not null,                      /* status (see KTS.H): */
                                               /* 1 = INVALID, 2 = AVAILABLE */
  blocks        number not null,                   /* size of file in blocks */
                                           /* zero for bitmapped tablespaces */
  ts#           number,                         /* tablespace that owns file */
  relfile#      number,                              /* relative file number */
  maxextend     number,                                 /* maximum file size */
  inc           number,                                  /* increment amount */
  crscnwrp      number,                                 /* creation SCN wrap */
  crscnbas      number,                                 /* creation SCN base */
  ownerinstance varchar("M_IDEN",                    /* Owner instance name */
  spare1        number,      /* tablespace-relative DBA of space file header */
                                   /* NULL for dictionary-mapped tablespaces */
  spare2        number,
  spare3        varchar2(1000),
  spare4        date
)