清除离线数据文件记录

联系:手机/微信(+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. 惜分飞 说:

    当然可以了

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

  2. 惜分飞 说:

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

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

  3. veronica 说:

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

  4. chengweihua 说:

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

  5. 惜 分飞 说:
    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
    )