oracle数据文件，控制文件和参数文件全部丢失恢复

确保已经对全库进行备份

1、删除所有文件

[oracle&＃64;sql ~]$ cd /oracle/app/oradata/TEST/
[oracle&＃64;sql TEST]$ ls
control01.ctl  initnew.ora  redo03.log    tbs_recover.dbf  users01.dbf
control02.ctl  redo01.log   sysaux01.dbf  temp01.dbf
control03.ctl  redo02.log   system01.dbf  undotbs01.dbf
[oracle&＃64;sql TEST]$ rm -fr *
[oracle&＃64;sql ~]$ cd /oracle/app/oracle/product/10.2.0/db_1/dbs/
[oracle&＃64;sql dbs]$ ls
hc_TEST.dat  initTEST.ora  orapwTEST      spfileTEST.ora
init.ora     lkTEST        snapcf_TEST.f
[oracle&＃64;sql dbs]$ rm -fr spfileTEST.ora
 

SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              67111156 bytes
Database Buffers           96468992 bytes
Redo Buffers                2973696 bytes
ORA-00205: error in identifying control file, check alert log for more info

2、找到数据库启动时加载的参数文件

[oracle&＃64;sql ~]$ cd /oracle/app/admin/TEST/bdump/
[oracle&＃64;sql bdump]$ tail -500 alert_TEST.log | more
 

processes                &＃61; 150
  __shared_pool_size       &＃61; 54525952
  __large_pool_size        &＃61; 4194304
  __java_pool_size         &＃61; 4194304
  __streams_pool_size      &＃61; 0
  sga_target               &＃61; 167772160
  control_files            &＃61; /oracle/app/oradata/TEST/control01.ctl, /oracle/app/oradata/TEST/control02.ctl, /oracle/app/oradata/TEST/control03.ctl
  db_block_size            &＃61; 8192
  __db_cache_size          &＃61; 100663296
  compatible               &＃61; 10.2.0.1.0
  db_file_multiblock_read_count&＃61; 16
  db_recovery_file_dest    &＃61; /oracle/app/flash_recovery_area
  db_recovery_file_dest_size&＃61; 2147483648
  _allow_resetlogs_corruption&＃61; TRUE
  undo_management          &＃61; AUTO
  undo_tablespace          &＃61; UNDOTBS1
  remote_login_passwordfile&＃61; EXCLUSIVE
  db_domain                &＃61;
  dispatchers              &＃61; (PROTOCOL&＃61;TCP) (SERVICE&＃61;TESTXDB)
  utl_file_dir             &＃61; /home/oracle/logminer
  job_queue_processes      &＃61; 10
  background_dump_dest     &＃61; /oracle/app/admin/TEST/bdump
  user_dump_dest           &＃61; /oracle/app/admin/TEST/udump
  core_dump_dest           &＃61; /oracle/app/admin/TEST/cdump
  audit_file_dest          &＃61; /oracle/app/admin/TEST/adump
  db_name                  &＃61; TEST
  open_cursors             &＃61; 300
  pga_aggregate_target     &＃61; 16777216

3、将参数文件写入一个文件中

[oracle&＃64;sql ~]$ vim initnew.ora

db_domain &＃61;
dispatchers &＃61; (PROTOCOL&＃61;TCP) (SERVICE&＃61;TESTXDB)
 

以上这两行删除掉

4、正常启动

SQL>shutdown abort;

SQL> startup nomount pfile&＃61;/home/oracle/initnew.ora

5、恢复 spfile

restore spfile from&＃39;/home/oracle/db_bak/1hnspkoo&＃39;;

[oracle&＃64;sql ~]$ cd /oracle/app/oracle/product/10.2.0/db_1/dbs/
[oracle&＃64;sql dbs]$ ls
hc_TEST.dat  initTEST.ora  orapwTEST      spfileTEST.ora
init.ora     lkTEST        snapcf_TEST.f
 

6、SQL> startup nomount force;(用恢复的spfile启动)

Starting restore at 13-DEC-12
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid&＃61;155 devtype&＃61;DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output filename&＃61;/oracle/app/oradata/TEST/control01.ctl
output filename&＃61;/oracle/app/oradata/TEST/control02.ctl
output filename&＃61;/oracle/app/oradata/TEST/control03.ctl
Finished restore at 13-DEC-12
 

7、恢复数据文件

RMAN> alter database mount;
RMAN> restore database;

RMAN> recover database;(报错因为没有日志了&＃xff0c;但必须运行)
RMAN> alter database open resetlogs;

至此数据库已经恢复 我们查看数据库损坏之前的数据。

SQL> select count(*) from abc;

  COUNT(*)
----------
     99504

SQL> select count(*) from nba;

  COUNT(*)
----------
     99504