重建控制文件

Elinc

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

1. SQL> alter database backup controlfile to trace;
   
2. 
   
3. Database altered.

2.查看生成的trace 文件

1. SELECT d.VALUE
2. || '/'
3. || LOWER (RTRIM (i.INSTANCE, CHR (0)))
4. || '_ora_'
5. || p.spid
6. || '.trc' trace_file_name
7. FROM (SELECT p.spid
8. FROM v$mystat m, v$session s, v$process p
9. WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
10. (SELECT t.INSTANCE
11. FROM v$thread t, v$parameter v
12. WHERE v.NAME = 'thread'
13. AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
14. (SELECT VALUE
15. FROM v$parameter
16. WHERE NAME = 'user_dump_dest') d
17. /

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

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

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

1. 1. CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS NOARCHIVELOG
      
   2. MAXLOGFILES 16
      
   3. MAXLOGMEMBERS 3
      
   4. MAXDATAFILES 100
      
   5. MAXINSTANCES 8
      
   6. MAXLOGHISTORY 292
      
   7. LOGFILE
      
   8. GROUP 1 '/u01/app/oradata/orcl/redo01.log' SIZE 50M,
      
   9. GROUP 2 '/u01/app/oradata/orcl/redo02.log' SIZE 50M,
      
   10. GROUP 3 '/u01/app/oradata/orcl/redo03.log' SIZE 50M
       
   11. -- STANDBY LOGFILE
       
   12. DATAFILE
       
   13. '/u01/app/oradata/orcl/system01.dbf',
       
   14. '/u01/app/oradata/orcl/undotbs01.dbf',
       
   15. '/u01/app/oradata/orcl/sysaux01.dbf',
       
   16. '/u01/app/oradata/orcl/users01.dbf',
       
   17. '/u01/app/oradata/orcl/example01.dbf',
       
   18. '/u01/app/prodct/dbs/tab20110523.dbf'
       
   19. CHARACTER SET WE8ISO8859P1
   20. 
       

  Control file created.

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

至此数据库打开

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

1. SQL> select name from V$datafile;
   
2. NAME
   
3. --------------------------------------------------------------------------------
   
4. /u01/app/oradata/hubeidb1/system01.dbf
   
5. /u01/app/oradata/hubeidb1/undotbs01.dbf
   
6. /u01/app/oradata/hubeidb1/sysaux01.dbf
   
7. /u01/app/oradata/hubeidb1/users01.dbf
   
8. /u01/app/oradata/hubeidb1/example01.dbf
   
9. /u01/app/prodct/dbs/MISSING00006

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

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

3.误删数据文件

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

1. SQL>SHUTDOWN ABORT
   
2. SQL> startup
   
3. ORACLE instance started.
   
4. Total System Global Area 1241513984 bytes
   
5. Fixed Size 1219136 bytes
   
6. Variable Size 318768576 bytes
   
7. Database Buffers 905969664 bytes
   
8. Redo Buffers 15556608 bytes
   
9. Database mounted.
   
10. ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
    
11. ORA-01110: data file 8: '/u01/app/oradata/test/test.dbf'
    
12. SQL> alter database datafile 8 offline drop;
    
13. Database altered.
    
14. SQL> alter database open;
    
15. Database altered.