oracle数据文件离线,清除离线数据文件记录 – 提供7*24专业数据库(Oracle,SQL Server,MySQL等)恢复和Oracle技术服务@Tel:+86 13429648788 - 惜...

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

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:如何彻底删除已经不存在的数据文件？