控制文件丢失 ORA-00210 手动重建控制文件

1、故障环境

• 数据库版本 11.2.0.4 单机
• 操作系统 Red Hat Enterprise Linux Server release 6.9 (Santiago)
  

2、报错信息

Thu Feb 02 15:50:32 2023
ALTER DATABASE   MOUNT
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/oracle/app/oradata/ogg01/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: '/oracle/app/oradata/ogg01/control01.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

3、解决办法

1、获取字符集
SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.US7ASCII

2、获取所有数据文件、日志文件路径及文件名（临时文件恢复后重构即可）
/oracle/app/oradata/ogg01/sysaux01.dbf
/oracle/app/oradata/ogg01/undotbs01.dbf
/oracle/app/oradata/ogg01/system01.dbf
/oracle/app/oradata/ogg01/temp01.dbf
/oracle/app/oradata/ogg01/users01.dbf

3、生成创建控制文件脚本


CREATE CONTROLFILE REUSE DATABASE ogg01 NORESETLOGS ARCHIVELOG
    MAXLOGFILES 5
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 1
    MAXLOGHISTORY 226
LOGFILE
  GROUP 1 '/oracle/app/oradata/ogg01/redo01.log' SIZE 50M,
  GROUP 2 '/oracle/app/oradata/ogg01/redo02.log' SIZE 50M,
  GROUP 3 '/oracle/app/oradata/ogg01/redo03.log' SIZE 50M
DATAFILE
  '/oracle/app/oradata/ogg01/sysaux01.dbf',
  '/oracle/app/oradata/ogg01/undotbs01.dbf',
  '/oracle/app/oradata/ogg01/system01.dbf',
  '/oracle/app/oradata/ogg01/users01.dbf'
CHARACTER SET US7ASCII ;

4、打开数据库
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oracle/app/oradata/ogg01/system01.dbf'
报错需要恢复

SQL> recover database ;
Media recovery complete.

SQL> alter database open;

Database altered.


5、检查数据库状态

select open_mode from v$database;

4、总结

1. 获取数据库名；
2. 获取字符集名；
3. 获取数据文件名；
4. 重建控制文件；
5. 执行介质恢复；
6. 打开数据库。