oracle 控制文件没有备份,没有自动备份的情况下控制文件全部丢失的恢复

报错信息：

SQL> startup

ORACLE instance started.

Total System Global Area  849530880 bytes

Fixed Size                  1339824 bytes

Variable Size             515903056 bytes

Database Buffers          327155712 bytes

Redo Buffers                5132288 bytes

ORA-00205: error in identifying control file, check alert log for more info

查看警报日志文件：

ALTER DATABASE   MOUNT

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/flash_recovery_area/orcl/control02.ctl'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-00210: cannot open the specified control file

ORA-00202: control file: '/u01/app/oracle/oradata/orcl/control01.ctl'

ORA-27037: unable to obtain file status

Linux Error: 2: No such file or directory

Additional information: 3

ORA-205 signalled during: ALTER DATABASE   MOUNT...

错误显示丢失了control01.ctl 和control02.ctl

查看参数control_files的值：

SQL> show parameter control_files

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_files                        string      /u01/app/oracle/oradata/orcl/c

ontrol01.ctl, /u01/app/oracle/

flash_recovery_area/orcl/contr

ol02.ctl

由此可知数据库中的所有控制文件全部丢失。

而在rman中是没有开启configure controlfile autobackup on;的。于是我们只能通过重建控制文件来恢复数据库了。

由于数据库缺失控制文件无法mount，所以不能使用alter database backup controlfile to trace获取控制文件脚本。

但是我们可以找到oracle的建库脚本，依据实际情况自己创建一个重建控制文件的脚本。

脚本createcontrol.sql内容如下：

STARTUP NOMOUNT

CREATE CONTROLFILE REUSE SET  DATABASE "ORCL"

MAXINSTANCES 8

MAXLOGHISTORY 1

MAXLOGFILES 16

MAXLOGMEMBERS 3

MAXDATAFILES 100

DATAFILE

'/u01/app/oracle/oradata/orcl/system01.dbf',

'/u01/app/oracle/oradata/orcl/sysaux01.dbf',

'/u01/app/oracle/oradata/orcl/undotbs01.dbf',

'/u01/app/oracle/oradata/orcl/users01.dbf',

'/u01/app/oracle/oradata/orcl/test01.dbf'

LOGFILE GROUP 1 ('/u01/app/oracle/oradata/orcl/redo01.log') SIZE 51200K,

GROUP 2 ('/u01/app/oracle/oradata/orcl/redo02.log') SIZE 51200K,

GROUP 3 ('/u01/app/oracle/oradata/orcl/redo03.log') SIZE 51200K RESETLOGS;

运行脚本：

SQL> @createcontrol.sql

ORACLE instance started.

Total System Global Area  849530880 bytes

Fixed Size                  1339824 bytes

Variable Size             515903056 bytes

Database Buffers          327155712 bytes

Redo Buffers                5132288 bytes

Control file created.

打开数据库：

SQL> alter database open resetlogs;

Database altered.