Oracle控制文件丢失恢复方法（无备份）

将数据库控制文件全部删除，模拟数据库控制文件全部丢失且没有备份，然后启动数据

SQL> startup;
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size                  2252824 bytes
Variable Size             704647144 bytes
Database Buffers          536870912 bytes
Redo Buffers                8892416 bytes
ORA-00205: error in identifying control file, check alert log for more info


SQL> shutdown immediate;

alert日志提示

ALTER DATABASE   MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oradata/eheng/control02.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oradata/eheng/control01.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-205 signalled during: ALTER DATABASE   MOUNT...

从提示中可以看出数据库在mount阶段找不到控制文件而报错

通过手动创建控制文件来拉起数据库，查看数据文件、redo日志文件

[root@eheng eheng]# ls -lsa
total 5115400
      4 drwxr-x---. 2 oracle oinstall       4096 Aug 24 09:32 .
      4 drwxr-xr-x. 7 oracle oinstall       4096 Aug 24 09:31 ..
 524296 -rw-r-----. 1 oracle oinstall  536871424 Aug 24 09:33 redo01.log
 524296 -rw-r-----. 1 oracle oinstall  536871424 Aug 24 09:33 redo02.log
 524296 -rw-r-----. 1 oracle oinstall  536871424 Aug 24 09:33 redo03.log
 524296 -rw-r-----. 1 oracle oinstall  536871424 Aug 24 09:33 redo04.log
 524296 -rw-r-----. 1 oracle oinstall  536871424 Aug 24 09:43 redo05.log
1048588 -rw-r-----. 1 oracle oinstall 1073750016 Aug 24 09:33 roobbin01.dbf
 522252 -rw-r-----. 1 oracle oinstall  534781952 Aug 24 09:38 sysaux01.dbf
 757772 -rw-r-----. 1 oracle oinstall  775954432 Aug 24 09:43 system01.dbf
   1024 -rw-r-----. 1 oracle oinstall   30416896 Aug 24 09:39 temp01.dbf
  76812 -rw-r-----. 1 oracle oinstall   78651392 Aug 24 09:38 undotbs01.dbf
   5128 -rw-r-----. 1 oracle oinstall    5251072 Aug 24 09:33 users01.dbf

将数据库启动到nomount状态，重建控制文件

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1252663296 bytes
Fixed Size                  2252824 bytes
Variable Size             704647144 bytes
Database Buffers          536870912 bytes
Redo Buffers                8892416 bytes
--重建控制文件不需要临时数据文件，控制文件重建成功重用临时数据文件即可
CREATE CONTROLFILE REUSE DATABASE "EHENG" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 8192
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/oradata/eheng/redo01.log'  SIZE 512M BLOCKSIZE 512,
  GROUP 2 '/oradata/eheng/redo02.log'  SIZE 512M BLOCKSIZE 512,
  GROUP 3 '/oradata/eheng/redo03.log'  SIZE 512M BLOCKSIZE 512,
  GROUP 4 '/oradata/eheng/redo04.log'  SIZE 512M BLOCKSIZE 512,
  GROUP 5 '/oradata/eheng/redo05.log'  SIZE 512M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/oradata/eheng/system01.dbf',
  '/oradata/eheng/sysaux01.dbf',
  '/oradata/eheng/undotbs01.dbf',
  '/oradata/eheng/users01.dbf',
  '/oradata/eheng/roobbin01.dbf'
CHARACTER SET ZHS16GBK
;

Control file created.
--打开数据库
SQL> alter database open;
Database altered.
--重用临时文件
SQL> alter tablespace temp add tempfile '/oradata/eheng/temp01.dbf' reuse;
Tablespace altered.

至此，控制文件手动创建成功， 数据库正常启动

SQL> select instance_name,status from v$instance;

INSTANCE_NAME    STATUS
---------------- ---------------------------------------------
eheng            OPEN

SQL>