Oracle清除离线数据文件记录 发表于 2016-11-11 | 分类于 Oracle Issue Oracle清除离线数据文件记录 测试前提:数据文件离线,系统上删除了该文件,需要删除在数据字典中,关于这条离线数据文件记录 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /opt/oracle/oradata/test/archivelogOldest online log sequence 210Next log sequence to archive 212Current log sequence 212--数据库是归档模式 SQL> col file_name for a40SQL> 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 a20SQL> 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.chfls: /opt/oracle/oradata/test/odu03.chf: No such file or directory--说明该数据文件已经从硬盘上删除 SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup nomountORACLE instance started. Total System Global Area 209715200 bytesFixed Size 2082784 bytesVariable Size 130025504 bytesDatabase Buffers 71303168 bytesRedo 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 a40SQL> 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 immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> STARTUP NOMOUNTORACLE instance started. Total System Global Area 209715200 bytesFixed Size 2082784 bytesVariable Size 130025504 bytesDatabase Buffers 71303168 bytesRedo 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.