场景介绍:
当Oracle的启动时提示:ORA-00205: error in identifying control file, check alert log for more info
那就表示在当前的数据库控制路径无法找到控制文件了,不过此时的数据库是已mount方式启动的。
问题描述
例如:
SQL> startup
ORACLE instance started.
Total System Global Area 1996487000 bytes
Fixed Size 9136472 bytes
Variable Size 452984832 bytes
Database Buffers 1526726656 bytes
Redo Buffers 7639040 bytes
ORA-00205: error in identifying control file, check alert log for more info
解决方法:
出现这类问题分别有三种恢复方法
方法一:如果你之前备份有二进制的控制文件,那么你可以使用如下命令,查询到你的控制文件路径,并直接cop过去,即可。
SQL> show parameter control;
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
control_file_record_keep_time integer
7
control_files string
/u01/app/oracle/oradata/ORCL/c
ontrol01.ctl, /u01/app/oracle/
fast_recovery_area/ORCL/contro
l02.ctl
control_management_pack_access string
DIAGNOSTIC+TUNING
方法二:如果你之前有做了
alter database backup controlfile to trace;备份控制文件的创建sql,那么你可以通过trace跟踪日志进行恢复。
##查询trace跟踪文件路径
select * from v$diag_info where name like '%Trace%'
输出结果:
SQL> select * from v$diag_info where name like '%Trace%';
NAME VALUE
---------------------------------------------------------------------
Diag Trace /u01/app/oracle/diag/rdbms/orcl/orcl/trace 0
Default Trace File /u01/app/oracle/diag/rdbms/orcl/orcl/trace/trace/orcl_ora_10138.trc
知道路径后,我习惯先到这个路径下的alert_orcl.log文件,先看找下我最新的备份是在那个trc文件。
不过你也可以直接到这个Default Trace File 当前的正在用的orcl_ora_10138.trc文件里直接去找备份的创建sql。
这里我先演示通过alert_orcl.log,找我最新的备份,例如:
2024-03-16T18:47:46.746381+08:00
alter database backup controlfile to trace(这个表示:更改要跟踪的数据库备份控文件)
2024-03-16T18:47:46.748466+08:00
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_10138.trc
Completed: alter database backup controlfile to trace
###进入orcl_ora_10138.trc,提取备份的sql去执行。
执行结果:
SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/ORCL/redo01.log' SIZE 200M BLOCKSIZE 512,
GRO 2 3 4 5 6 7 8 9 UP 2 '/u01/app/oracle/oradata/ORCL/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/ORCL/redo03.log' SIZE 200M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/ORCL/system01.dbf',
'/u01/app/oracle/oradata/ORCL/sysaux01.dbf',
' 10 11 12 13 14 /u01/app/oracle/oradata/ORCL/undotbs01.dbf',
'/u01/app/oracle/oradata/ORCL/pdbseed/system01.dbf',
'/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf',
'/u01/app/oracle/oradata/ORCL/users01.dbf',
'/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf', 15 16 17 18
'/u01/app/oracle/oradata/ORCL/pdb/system01.dbf',
'/u01/app/oracle/oradata/ORCL/pdb/sysaux01.dbf',
'/u01/app/oracle/oradata/ORCL/pdb/undotbs01.dbf',
'/u01/app/oracle/oradata/ORCL/pdb/users01.dbf'
CHARACTER SET ZHS16GBK
; 19 20 21 22 23 24
Control file created.
方式三:如果你什么备份都没有,也不要慌。你可以自己重做一个控制文件
这里我提供了一个重做模板,你需要根据你数据库的redo文件路径和表空间等信息去修改一个模板,按照上方去执行就好了。
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/ORCL/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/ORCL/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/ORCL/redo03.log' SIZE 200M BLOCKSIZE 512
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/pdbseed/system01.dbf',
'/u01/app/oracle/oradata/ORCL/pdbseed/sysaux01.dbf',
'/u01/app/oracle/oradata/ORCL/users01.dbf',
'/u01/app/oracle/oradata/ORCL/pdbseed/undotbs01.dbf',
'/u01/app/oracle/oradata/ORCL/pdb/system01.dbf',
'/u01/app/oracle/oradata/ORCL/pdb/sysaux01.dbf',
'/u01/app/oracle/oradata/ORCL/pdb/undotbs01.dbf',
'/u01/app/oracle/oradata/ORCL/pdb/users01.dbf'
CHARACTER SET ZHS16GBK
扩展
在做完控制文件恢复后,我发现alter database open还是无法正常启动数据库,给出的错误提示如下:
SQL> alter database open;
alter database open
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
原因
我在做的时候重启了下数据库。数据库异常停止,停止的时候碰巧还没有来得及将redo缓存区中的redo log归档,就被强制SHUTDOWN了。数据库再次启动的时候,会去检查SCN 系统变动码.如果发现不一致,那么就意味着有不一致的LOG需要处理。处理方法如下:
###尝试添加resetlogs参数启动,显然不行
SQL> /
alter database open resetlogs
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery(有一个数据文件需要介质恢复,一致性恢复)
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/system01.dbf'
查询redo日志文件状态:SELECT GROUP#, THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG;
输出结果:CURRENT(正在使用的redo)、ACTIVE(还没有从DB buffer cache写入磁盘)、INACTIVE(已经过期的,也就是完成的无需恢复)、UNUSED(还没被使用的)
SQL> SELECT GROUP#, THREAD#, SEQUENCE#, ARCHIVED, STATUS FROM V$LOG;
GROUP# THREAD# SEQUENCE# ARC STATUS
---------- ---------- ---------- --- ----------------
1 1 0 YES UNUSED
3 1 0 YES CURRENT
2 1 0 YES UNUSED
recover database using backup controlfile;执行命令
SQL> recover database using backup controlfile;
ORA-00279: change 2336782 generated at 03/16/2024 18:56:46 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/fast_recovery_area/ORCL/archivelog/2024_03_16/o1_mf_1_9_%u_.arc
ORA-00280: change 2336782 for thread 1 is in sequence #9
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}(到这里会卡住,需要你指定输入你想要通过那个redolog来恢复,这里就可以指定redo状态为:ACTIVE的用于恢复)
/u01/app/oracle/oradata/ORCL/redo03.log
Log applied.
Media recovery complete.
再次尝试启动数据库:alter database open RESETLOGS;
输出结果:
SQL> alter database open RESETLOGS;
Database altered.
检查数据库状态:select status from v$instance;(ok,完美)
SQL> select status from v$instance;
STATUS
------------
OPEN