重建控制文件

1. 备份控制文件到trace文件中

SQL> alter database backup controlfile to trace;

Database altered.

2.查看生成的trace 文件

SELECT    d.VALUE
|| '/'
|| LOWER (RTRIM (i.INSTANCE, CHR (0)))
|| '_ora_'
|| p.spid
|| '.trc' trace_file_name
FROM (SELECT p.spid
FROM v$mystat m, v$session s, v$process p
WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
(SELECT t.INSTANCE
FROM v$thread t, v$parameter v
WHERE v.NAME = 'thread'
AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
(SELECT VALUE
FROM v$parameter
WHERE NAME = 'user_dump_dest') d
/

/u01/app/admin/orcl/udump/oracl_ora_8997.trc

3.根据trace文件，创建生成控制文件的sql

cat  /u01/app/admin/orcl/udump/orcl_ora_8997.trc

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oradata/orcl/redo01.log' SIZE 50M,
GROUP 2 '/u01/app/oradata/orcl/redo02.log' SIZE 50M,
GROUP 3 '/u01/app/oradata/orcl/redo03.log' SIZE 50M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oradata/orcl/system01.dbf',
'/u01/app/oradata/orcl/undotbs01.dbf',
'/u01/app/oradata/orcl/sysaux01.dbf',
'/u01/app/oradata/orcl/users01.dbf',
'/u01/app/oradata/orcl/example01.dbf',
'/u01/app/prodct/dbs/tab20110523.dbf'
CHARACTER SET WE8ISO8859P1

  Control file created.

ALTER DATABASE OPEN
出现错误了
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oradata/hubeidb1/system01.dbf'
SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

至此数据库打开

被删除的文件，被ORACLE 明名为MISSING000xxxx

SQL> select name from V$datafile;
NAME
--------------------------------------------------------------------------------
/u01/app/oradata/hubeidb1/system01.dbf
/u01/app/oradata/hubeidb1/undotbs01.dbf
/u01/app/oradata/hubeidb1/sysaux01.dbf
/u01/app/oradata/hubeidb1/users01.dbf
/u01/app/oradata/hubeidb1/example01.dbf
/u01/app/prodct/dbs/MISSING00006

如果能找回被删的数据文件

可以通过ALTER DATABASE RENAME ’xxx' to 'xxxx' 语句将丢失的数据文件指定到正确的文件中去

3.误删数据文件

  正常关闭数据是会报错，因为oracle 会在正常关闭时会检查数据文件一致

SQL>SHUTDOWN ABORT
SQL> startup
ORACLE instance started.
Total System Global Area 1241513984 bytes
Fixed Size 1219136 bytes
Variable Size 318768576 bytes
Database Buffers 905969664 bytes
Redo Buffers 15556608 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '/u01/app/oradata/test/test.dbf'
SQL> alter database datafile 8 offline drop;
Database altered.
SQL> alter database open;
Database altered.