前言
在 Oracle 数据库中，控制文件（Control File） 是数据库的核心组件之一，记录了数据库的物理结构和状态信息。它是数据库启动、恢复和正常运行的关键文件。如果控制文件发生损坏或者丢失，该如何解决？
控制文件的作用
存储关键元数据：
数据库名称、创建时间、当前日志序列号。
数据文件（Data Files）和日志文件（Redo Log Files）的路径及状态。
表空间信息、检查点（Checkpoint）信息。
归档日志（Archive Log）记录。
数据库启动和恢复：
数据库启动时通过控制文件验证数据文件和日志文件的完整性。
在恢复（Recovery）过程中，控制文件指导如何应用重做日志。
控制文件的内容
-----在mount或者open状态操作SQL> alter database backup controlfile to trace as '/home/oracle/control.txt';[oracle@dbatest:/home/oracle]$ cat control.sql
-- The following are current System-scope REDO Log Archival related
-- parameters and can be included in the database initialization file.
--
-- LOG_ARCHIVE_DEST=''
-- LOG_ARCHIVE_DUPLEX_DEST=''
--
-- LOG_ARCHIVE_FORMAT=%t_%s_%r.dbf
--
-- DB_UNIQUE_NAME="dbatest"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/product/19c/db/dbs/arch'
-- LOG_ARCHIVE_DEST_1='MANDATORY REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='NOREGISTER'
-- LOG_ARCHIVE_DEST_1='NOALTERNATE'
-- LOG_ARCHIVE_DEST_1='NODEPENDENCY'
-- LOG_ARCHIVE_DEST_1='NOMAX_FAILURE NOQUOTA_SIZE NOQUOTA_USED NODB_UNIQUE_NAME'
-- LOG_ARCHIVE_DEST_1='VALID_FOR=(PRIMARY_ROLE,ONLINE_LOGFILES)'
-- LOG_ARCHIVE_DEST_STATE_1=ENABLE
--
-- Below are two sets of SQL statements, each of which creates a new
-- control file and uses it to open the database. The first set opens
-- the database with the NORESETLOGS option and should be used only if
-- the current versions of all online logs are available. The second
-- set opens the database with the RESETLOGS option and should be used
-- if online logs are unavailable.
-- The appropriate set of statements can be copied from the trace into
-- a script file, edited as necessary, and executed when there is a
-- need to re-create the control file.
--
-- Set #1. NORESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- Additional logs may be required for media recovery of offline
-- Use this only if the current versions of all online logs are
-- available.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DBATEST" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/data/datafile/DBATEST/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/data/datafile/DBATEST/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/data/datafile/DBATEST/redo03.log' SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/data/datafile/DBATEST/system01.dbf',
'/data/datafile/DBATEST/sysaux01.dbf',
'/data/datafile/DBATEST/undotbs01.dbf',
'/data/datafile/DBATEST/users01.dbf'
CHARACTER SET ZHS16GBK
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/19c/db/dbs/arch1_1_1175338103.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- No tempfile entries found to add.
--
-- Set #2. RESETLOGS case
--
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- The contents of online logs will be lost and all backups will
-- be invalidated. Use this only if online logs are damaged.
-- After mounting the created controlfile, the following SQL
-- statement will place the database in the appropriate
-- protection mode:
-- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "DBATEST" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/data/datafile/DBATEST/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/data/datafile/DBATEST/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/data/datafile/DBATEST/redo03.log' SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/data/datafile/DBATEST/system01.dbf',
'/data/datafile/DBATEST/sysaux01.dbf',
'/data/datafile/DBATEST/undotbs01.dbf',
'/data/datafile/DBATEST/users01.dbf'
CHARACTER SET ZHS16GBK
;
-- Commands to re-create incarnation table
-- Below log names MUST be changed to existing filenames on
-- disk. Any one log file from each branch can be used to
-- re-create incarnation records.
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/product/19c/db/dbs/arch1_1_1175338103.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE USING BACKUP CONTROLFILE
-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;
-- No tempfile entries found to add.
--
[oracle@dbatest:/home/oracle]$
故障处理
模拟删除控制文件
[oracle@dbatest:/data/datafile/DBATEST]$ rm -f control0*
[oracle@dbatest:/data/datafile/DBATEST]$ ls
DBATEST redo01.log redo02.log redo03.log sysaux01.dbf system01.dbf temp01.dbf undotbs01.dbf users01.dbf[oracle@dbatest:/data/datafile/DBATEST]$ ps -ef|grep smon
oracle 24386 1 0 Mar10 ? 00:00:02 ora_smon_dbatest
oracle 114763 22678 0 10:11 pts/0 00:00:00 grep --color=auto smonSQL> create table t2 as select * from dual;
Table created.创建b表没有问题.....
SQL> insert into t2 values ('q');
1 row created.
SQL> commit;
Commit complete.insert 没有问题。。。
涉及到更新控制文件内容时，比如增加数据文件，就会触发报错：
SQL> alter tablespace users add datafile size 10M;
alter tablespace users add datafile size 10M
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data/datafile/DBATEST/control01.ctl'
ORA-27041: unable to open file
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: '/data/datafile/DBATEST/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Process ID: 114809
Session ID: 1145 Serial number: 49055
我们再来看看告警日志alert：
报错找不到控制文件~~
2025-03-11T10:12:57.411217+08:00
Errors in file /u01/app/oracle/diag/rdbms/dbatest/dbatest/trace/dbatest_mz02_114833.trc:
ORA-00202: control file: '/data/datafile/DBATEST/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
2025-03-11T10:12:57.534802+08:00
Errors in file /u01/app/oracle/diag/rdbms/dbatest/dbatest/trace/dbatest_mz02_114833.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data/datafile/DBATEST/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
2025-03-11T10:18:41.175830+08:00
alter tablespace users add datafile size 10M
2025-03-11T10:18:41.223756+08:00
Errors in file /u01/app/oracle/diag/rdbms/dbatest/dbatest/trace/dbatest_mz00_115176.trc:
ORA-00202: control file: '/data/datafile/DBATEST/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /u01/app/oracle/diag/rdbms/dbatest/dbatest/trace/dbatest_ora_114809.trc (incident=240458):
ORA-00603: ORACLE server session terminated by fatal error
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data/datafile/DBATEST/control01.ctl'
ORA-27041: unable to open file
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: '/data/datafile/DBATEST/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Incident details in: /u01/app/oracle/diag/rdbms/dbatest/dbatest/incident/incdir_240458/dbatest_ora_114809_i240458.trc
2025-03-11T10:18:41.329647+08:00
Errors in file /u01/app/oracle/diag/rdbms/dbatest/dbatest/trace/dbatest_mz00_115176.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data/datafile/DBATEST/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
2025-03-11T10:18:41.988565+08:00
*****************************************************************
An internal routine has requested a dump of selected redo.
This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional
redo dumps are required to help with the diagnosis.
*****************************************************************
2025-03-11T10:18:41.996921+08:00
Errors in file /u01/app/oracle/diag/rdbms/dbatest/dbatest/incident/incdir_240458/dbatest_ora_114809_i240458.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data/datafile/DBATEST/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00603: ORACLE server session terminated by fatal error
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data/datafile/DBATEST/control01.ctl'
ORA-27041: unable to open file
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: '/data/datafile/DBATEST/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
2025-03-11T10:18:41.997754+08:00
Errors in file /u01/app/oracle/diag/rdbms/dbatest/dbatest/incident/incdir_240458/dbatest_ora_114809_i240458.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data/datafile/DBATEST/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00603: ORACLE server session terminated by fatal error
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data/datafile/DBATEST/control01.ctl'
ORA-27041: unable to open file
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: '/data/datafile/DBATEST/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
opiodr aborting process unknown ospid (114809) as a result of ORA-603
2025-03-11T10:18:42.031692+08:00
Errors in file /u01/app/oracle/diag/rdbms/dbatest/dbatest/trace/dbatest_ora_114809.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data/datafile/DBATEST/control01.ctl'
ORA-27041: unable to open file
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: '/data/datafile/DBATEST/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
2025-03-11T10:18:45.098188+08:00
Errors in file /u01/app/oracle/diag/rdbms/dbatest/dbatest/trace/dbatest_cl04_113432.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data/datafile/DBATEST/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
2025-03-11T10:18:45.588090+08:00
ORACLE Instance dbatest (pid = 23) - Error 210 encountered while recovering transaction (1, 14).
2025-03-11T10:18:45.588418+08:00
Errors in file /u01/app/oracle/diag/rdbms/dbatest/dbatest/trace/dbatest_smon_24386.trc:
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data/datafile/DBATEST/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
解决方法
重建控制文件
--强制关闭数据库
SQL> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: '/data/datafile/DBATEST/control01.ctl'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL>
SQL> shutdown abort;
ORACLE instance shut down.---启动数据库报错
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 2516581456 bytes
Fixed Size 8899664 bytes
Variable Size 1090519040 bytes
Database Buffers 1409286144 bytes
Redo Buffers 7876608 bytes
SQL>
SQL> alter database mount;
alter database mount
*
ERROR at line 1:
ORA-00205: error in identifying control file, check alert log for more info---重建控制文件
SQL>CREATE CONTROLFILE REUSE DATABASE "DBATEST" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/data/datafile/DBATEST/redo01.log' SIZE 200M BLOCKSIZE 512,
GROUP 2 '/data/datafile/DBATEST/redo02.log' SIZE 200M BLOCKSIZE 512,
GROUP 3 '/data/datafile/DBATEST/redo03.log' SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/data/datafile/DBATEST/system01.dbf',
'/data/datafile/DBATEST/sysaux01.dbf',
'/data/datafile/DBATEST/undotbs01.dbf',
'/data/datafile/DBATEST/users01.dbf'
CHARACTER SET ZHS16GBK
;--恢复数据库
SQL> recover database;
Media recovery complete.--启动数据库
SQL> alter database open;
Database altered.----添加数据文件成功
SQL> alter tablespace users add datafile size 1G;
Tablespace altered.
