Oracle 控制文件详解

1、控制文件存储的数据信息
1）数据库名称和数据库唯一标识符（DBID)
2）创建数据库的时间戳
3）有关数据文件、联机重做日志文件、归档重做日志文件的信息
4）表空间信息
5）检查点信息
6）日志序列号信息
 控制文件包含数据文件、联机重做日志文件等信息。
 控制文件也会跟踪数据库的结构变化。
 控制文件包含未打开数据库时必须可访问的元数据。

2、控制文件在什么时候用
mount时、open时、备份恢复过程中都会用到控制文件

3、至少有一个控制文件、建议有多个控制文件
多个控制文件的好处:多路复用,避免单点故障

4、控制文件位置


SYS@orcl(CDB$ROOT)> conn / as sysdba
Connected.

SYS@orcl(CDB$ROOT)> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/control01.ctl
/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl

SYS@orcl(CDB$ROOT)> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------------------------------------------------------------------
control_files                        string      /u01/app/oracle/oradata/ORCL/control01.ctl, /u01/app/oracle/fast_recovery_area/ORCL/control02.ctl

5、控制文件的增加、删除、重定位、重命名

5.1 增加控制文件

SYS@orcl(CDB$ROOT)> alter system set control_files='/u01/app/oracle/oradata/ORCL/control01.ctl','/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl','/u01/app/oracle/oradata/ORCL/control03.ctl' scope=spfile;

System altered.

SYS@orcl(CDB$ROOT)> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------------------------------------------------------------------
control_files                        string      /u01/app/oracle/oradata/ORCL/control01.ctl, /u01/app/oracle/fast_recovery_area/ORCL/control02.ctl

SYS@orcl(CDB$ROOT)> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@orcl(CDB$ROOT)> host cp /u01/app/oracle/oradata/ORCL/control01.ctl /u01/app/oracle/oradata/ORCL/control03.ctl

SYS@orcl(CDB$ROOT)> startup
ORACLE instance started.

Total System Global Area 1526723568 bytes
Fixed Size                  9135088 bytes
Variable Size             939524096 bytes
Database Buffers          570425344 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.

SYS@orcl(CDB$ROOT)> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_files                        string      /u01/app/oracle/oradata/ORCL/control01.ctl, /u01/app/oracle/fast_recovery_area/ORCL/control02.ctl, /u01/app/oracle/oradata/ORCL/control03.ctl

注意shutdown 后要复制生成参数中新增的控制文件，否则启动数据库会失败:

SYS@orcl(CDB$ROOT)> startup
ORACLE instance started.

Total System Global Area 1526723568 bytes
Fixed Size                  9135088 bytes
Variable Size             973078528 bytes
Database Buffers          536870912 bytes
Redo Buffers                7639040 bytes
ORA-00205: error in identifying control file, check alert log for more info

原因:找不到/u01/app/oracle/fast_recovery_area/ORCL/control03.ctl
查看alert日志会看到类似如下信息:

Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_mz00_102709.trc:
ORA-00202: control file: '/u01/app/oracle/oradata/ORCL/control01.ctl,/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl,/u01/app/oracle/fast_recovery_area/ORCL/control03.ctl'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory

5.2 删除控制文件
恢复成最初的两个控制文件

SYS@orcl(CDB$ROOT)> alter system set control_files='/u01/app/oracle/oradata/ORCL/control01.ctl','/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl' scope=spfile;

System altered.

SYS@orcl(CDB$ROOT)> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@orcl(CDB$ROOT)> startup
ORACLE instance started.

Total System Global Area 1526723568 bytes
Fixed Size                  9135088 bytes
Variable Size             939524096 bytes
Database Buffers          570425344 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.

SYS@orcl(CDB$ROOT)> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------------------------------------------------------------------
control_files                        string      /u01/app/oracle/oradata/ORCL/control01.ctl, /u01/app/oracle/fast_recovery_area/ORCL/control02.ctl

5.3 重命名控制文件
将/u01/app/oracle/oradata/ORCL/control01.ctl 改成/u01/app/oracle/oradata/ORCL/control04.ctl

SYS@orcl(CDB$ROOT)> alter system set control_files='/u01/app/oracle/oradata/ORCL/control04.ctl','/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl' scope=spfile;

SYS@orcl(CDB$ROOT)> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@orcl(CDB$ROOT)> host cp /u01/app/oracle/oradata/ORCL/control01.ctl /u01/app/oracle/oradata/ORCL/control04.ctl

SYS@orcl(CDB$ROOT)> startup
ORACLE instance started.

Total System Global Area 1526723568 bytes
Fixed Size                  9135088 bytes
Variable Size             939524096 bytes
Database Buffers          570425344 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.
SYS@orcl(CDB$ROOT)> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------------------------------------------------------------------
control_files                        string      /u01/app/oracle/oradata/ORCL/control04.ctl, /u01/app/oracle/fast_recovery_area/ORCL/control02.ctl

5.4 重定位控制文件
修改 /u01/app/oracle/oradata/ORCL/control04.ctl 路径，为 /u01/app/oracle/oradata/control04.ctl

SYS@orcl(CDB$ROOT)> alter system set control_files='/u01/app/oracle/oradata/control04.ctl','/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl' scope=spfile;

System altered.

SYS@orcl(CDB$ROOT)> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@orcl(CDB$ROOT)> host cp /u01/app/oracle/oradata/ORCL/control04.ctl /u01/app/oracle/oradata/control04.ctl

SYS@orcl(CDB$ROOT)> startup
ORACLE instance started.

Total System Global Area 1526723568 bytes
Fixed Size                  9135088 bytes
Variable Size             939524096 bytes
Database Buffers          570425344 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.

SYS@orcl(CDB$ROOT)> show parameter control_files

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------------------------------------------------------------------
control_files                        string      /u01/app/oracle/oradata/control04.ctl, /u01/app/oracle/fast_recovery_area/ORCL/control02.ctl

除了上述方法，还有如下方法可以修改control_files的配置。

查看得知当前是使用的spfile文件：

SYS@orcl(CDB$ROOT)> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/19.3.0/db_1/dbs/spfileorcl.ora

生成pfile文件：

SYS@orcl(CDB$ROOT)> create pfile from spfile;

File created.

查看当前使用的控制文件：

SYS@orcl(CDB$ROOT)> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/control04.ctl
/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl

关闭数据库：

SYS@orcl(CDB$ROOT)> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

在$ORACLE_HOME/dbs 下可看到生成的pfile文件initorcl.ora

# su - oracle
Last login: Mon Mar 25 20:48:45 CST 2024 on pts/7
$ cd $ORACLE_HOME
$ cd dbs
$ ll
total 40
-rw-rw----. 1 oracle oinstall 1544 Mar 16 01:22 hc_oradb.dat
-rw-rw----. 1 oracle oinstall 1544 Mar 25 21:32 hc_orcl.dat
-rw-r--r--. 1 oracle oinstall 3079 May 14  2015 init.ora
-rw-r--r--. 1 oracle oinstall 1087 Mar 25 21:30 initorcl.ora
-rw-r-----. 1 oracle oinstall   24 Mar 16 01:07 lkORADB
-rw-r-----. 1 oracle oinstall   24 Mar 14 20:11 lkORCL
-rw-r-----. 1 oracle oinstall 2048 Mar 16 01:10 orapworadb
-rw-r-----. 1 oracle oinstall 2048 Mar 14 20:13 orapworcl
-rw-r-----. 1 oracle oinstall 3584 Mar 16 06:03 spfileoradb.ora
-rw-r-----. 1 oracle oinstall 3584 Mar 25 21:25 spfileorcl.ora

这里的spfile文件可以删除：

$ rm spfileorcl.ora

修改pfile文件initorcl.ora ：

$ vi initorcl.ora

在这里插入图片描述
将红框部分修改为：

*.control_files='/u01/app/oracle/oradata/ORCL/control01.ctl','/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl'

备份控制文件：

$ cp /u01/app/oracle/fast_recovery_area/ORCL/control02.ctl /u01/app/oracle/oradata/ORCL/control01.ctl

生成新的spfile文件：

SYS@orcl(CDB$ROOT)>  create spfile from pfile;

File created.

重启数据库后查看使用的控制文件：

SYS@orcl(CDB$ROOT)> startup
ORACLE instance started.

Total System Global Area 1526723568 bytes
Fixed Size                  9135088 bytes
Variable Size             939524096 bytes
Database Buffers          570425344 bytes
Redo Buffers                7639040 bytes
Database mounted.
Database opened.
SYS@orcl(CDB$ROOT)> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/control01.ctl
/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl

6、控制文件的备份
6.1 备份二进制文件

SYS@orcl(CDB$ROOT)> alter database backup controlfile to '/home/oracle/control_bak.ctl';

Database altered.

6.2 备份文本文件

SYS@orcl(CDB$ROOT)> alter database backup controlfile to trace as '/home/oracle/control_text.ctl' ;

Database altered.

备份控制文件备份控制文件是非常重要的，在对数据库的物理结构进行改变后，也需要重新对控制文件进行备份，这些操作包括：
 增加、删除、重命名数据文件；
 增加、删除表空间
 增加、删除日志组或日志文件

7、创建控制文件
建议在归档模式创建控制文档，以避免日志文件被覆盖。（也可在非归档模式）

7.1 打开数据库归档

1）查看数据库归档情况
SYS@orcl(CDB$ROOT)> archive log list
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     7
Current log sequence           9

2）关闭数据库
SYS@orcl(CDB$ROOT)> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

3）启动数据库到 mount
SYS@orcl(CDB$ROOT)> startup mount
ORACLE instance started.

Total System Global Area 1526723568 bytes
Fixed Size                  9135088 bytes
Variable Size             939524096 bytes
Database Buffers          570425344 bytes
Redo Buffers                7639040 bytes
Database mounted.

4）打开归档
SYS@orcl(CDB$ROOT)> alter database archivelog;

Database altered.

5）打开数据库
SYS@orcl(CDB$ROOT)> alter database open;

Database altered.

6）查看数据库归档情况
SYS@orcl(CDB$ROOT)> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     7
Next log sequence to archive   9
Current log sequence           9

7.2 删除所有的控制文件，模拟控制文件丢失

1）查看数据库控制文件
SYS@orcl(CDB$ROOT)> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/control01.ctl
/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl

SYS@orcl(CDB$ROOT)> host rm /u01/app/oracle/oradata/ORCL/control01.ctl

SYS@orcl(CDB$ROOT)> host rm /u01/app/oracle/fast_recovery_area/ORCL/control02.ctl

SYS@orcl(CDB$ROOT)> shutdown abort
ORACLE instance shut down.

7.3 将数据库启动到 NOMOUNT

SYS@orcl(CDB$ROOT)> startup nomount
ORACLE instance started.

Total System Global Area 1526723568 bytes
Fixed Size                  9135088 bytes
Variable Size             939524096 bytes
Database Buffers          570425344 bytes
Redo Buffers                7639040 bytes

7.4 执行创建控制文件的语句
创建控制文件的语句从6.2中做的控制文件备份control_text.ctl中copy，选择NORESETLOGS语句。

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG
    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
-- STANDBY LOGFILE
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 AL32UTF8
;

直接粘贴在SQL>后，如下：

SYS@orcl(CDB$ROOT)> STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS  NOARCHIVELOG
ORA-01081: cannot start already-running ORACLE - shut it down first
    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
-- STANDBY LOGFILE
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 AL32UTF8
 25  ;

Control file created.

7.5 启动数据库

1）查看数据库当前状态
SYS@orcl(CDB$ROOT)> select status from v$instance;

STATUS
------------
MOUNTED

2）启动数据库
SYS@orcl(CDB$ROOT)> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/system01.dbf'

3）恢复数据库
SYS@orcl(CDB$ROOT)> recover database;
Media recovery complete.

4）启动数据库
SYS@orcl(CDB$ROOT)> alter database open;

Database altered.

7.6 查看控制文件

SYS@orcl(CDB$ROOT)> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/control01.ctl
/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl

$ ls /u01/app/oracle/oradata/ORCL/control01.ctl
/u01/app/oracle/oradata/ORCL/control01.ctl
$ ls /u01/app/oracle/fast_recovery_area/ORCL/control02.ctl
/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl