sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】删除在Oracle数据字典中 不存在的数据文件的记录

时间:2016-07-19 22:12   来源:Oracle研究中心   作者:惜分飞   点击:

天萃荷净 清除离线数据文件记录,运维DBA反映该生产数据库某个数据文件物理删除,在查询数据字典时该文件记录存在,结合案例删除数据字典中不存在的数据文件的记录

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

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /opt/oracle/oradata/oracleplus/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/oracleplus/system01.dbf     524288000
         2 /opt/oracle/oradata/oracleplus/undotbs01.dbf   1289748480
         3 /opt/oracle/oradata/oracleplus/sysaux01.dbf     377487360
         4 /opt/oracle/oradata/oracleplus/users01.dbf        5242880
         5 /opt/oracle/oradata/oracleplus/user32g.dbf       10485760
         6 /opt/oracle/oradata/oracleplus/oracleplus01.dbf    20971520
         7 /opt/oracle/oradata/oracleplus/user02.dbf        10485760
         8 /opt/oracle/oradata/oracleplus/odu02.dbf       1.1283E+10
         9 /opt/oracle/oradata/oracleplus/odu01.dbf        104857600
        10 /opt/oracle/oradata/oracleplus/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/oracleplus/odu03.chf
ls: /opt/oracle/oradata/oracleplus/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 "oracleplus" 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/oracleplus/redo01.log'  SIZE 50M,
  9    GROUP 2 '/opt/oracle/oradata/oracleplus/redo02.log'  SIZE 50M,
 10    GROUP 3 '/opt/oracle/oradata/oracleplus/redo03.log'  SIZE 50M
 11  DATAFILE
 12    '/opt/oracle/oradata/oracleplus/system01.dbf',
 13    '/opt/oracle/oradata/oracleplus/undotbs01.dbf',
 14    '/opt/oracle/oradata/oracleplus/sysaux01.dbf',
 15    '/opt/oracle/oradata/oracleplus/users01.dbf',
 16    '/opt/oracle/oradata/oracleplus/user32g.dbf',
 17    '/opt/oracle/oradata/oracleplus/oracleplus01.dbf',
 18    '/opt/oracle/oradata/oracleplus/user02.dbf',
 19    '/opt/oracle/oradata/oracleplus/odu02.dbf',
 20    '/opt/oracle/oradata/oracleplus/odu01.dbf'
        ,'/opt/oracle/oradata/oracleplus/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/oracleplus/system01.dbf     524288000
         2 /opt/oracle/oradata/oracleplus/undotbs01.dbf   1289748480
         3 /opt/oracle/oradata/oracleplus/sysaux01.dbf     377487360
         4 /opt/oracle/oradata/oracleplus/users01.dbf        5242880
         5 /opt/oracle/oradata/oracleplus/user32g.dbf       10485760
         6 /opt/oracle/oradata/oracleplus/oracleplus01.dbf    20971520
         7 /opt/oracle/oradata/oracleplus/user02.dbf        10485760
         8 /opt/oracle/oradata/oracleplus/odu02.dbf       1.1283E+10
         9 /opt/oracle/oradata/oracleplus/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/oracleplus/system01.dbf
         2 /opt/oracle/oradata/oracleplus/undotbs01.dbf
         3 /opt/oracle/oradata/oracleplus/sysaux01.dbf
         4 /opt/oracle/oradata/oracleplus/users01.dbf
         5 /opt/oracle/oradata/oracleplus/user32g.dbf
         6 /opt/oracle/oradata/oracleplus/oracleplus01.dbf
         7 /opt/oracle/oradata/oracleplus/user02.dbf
         8 /opt/oracle/oradata/oracleplus/odu02.dbf
         9 /opt/oracle/oradata/oracleplus/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 "oracleplus" 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/oracleplus/redo01.log'  SIZE 50M,
  9    GROUP 2 '/opt/oracle/oradata/oracleplus/redo02.log'  SIZE 50M,
 10    GROUP 3 '/opt/oracle/oradata/oracleplus/redo03.log'  SIZE 50M
 11  DATAFILE
 12    '/opt/oracle/oradata/oracleplus/system01.dbf',
 13    '/opt/oracle/oradata/oracleplus/undotbs01.dbf',
 14    '/opt/oracle/oradata/oracleplus/sysaux01.dbf',
 15    '/opt/oracle/oradata/oracleplus/users01.dbf',
 16    '/opt/oracle/oradata/oracleplus/user32g.dbf',
 17    '/opt/oracle/oradata/oracleplus/oracleplus01.dbf',
 18    '/opt/oracle/oradata/oracleplus/user02.dbf',
 19    '/opt/oracle/oradata/oracleplus/odu02.dbf',
 20    '/opt/oracle/oradata/oracleplus/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/oracleplus/system01.dbf     524288000
         2 /opt/oracle/oradata/oracleplus/undotbs01.dbf   1289748480
         3 /opt/oracle/oradata/oracleplus/sysaux01.dbf     377487360
         4 /opt/oracle/oradata/oracleplus/users01.dbf        5242880
         5 /opt/oracle/oradata/oracleplus/user32g.dbf       10485760
         6 /opt/oracle/oradata/oracleplus/oracleplus01.dbf    20971520
         7 /opt/oracle/oradata/oracleplus/user02.dbf        10485760
         8 /opt/oracle/oradata/oracleplus/odu02.dbf       1.1283E+10
         9 /opt/oracle/oradata/oracleplus/odu01.dbf        104857600

9 rows selected.

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

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

9 rows selected.

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


--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】删除在Oracle数据字典中 不存在的数据文件的记录

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/798.html

Oracle研究中心

关键词:

清除离线数据文件记录

删除在Oracle数据字典中异常数据文件的记录

如何彻底删除已经不存在的数据文件

数据文件物理删除后清除数据字典中的记录