番外：Oracle 中关于 Control File 的备份说明

番外系列说明：该系列所有文章都将作为独立篇章进行知识点讲解，是对其他系列博文进行的补充说明，来自于博客园AskScuti。

主题：关于 Control File 控制文件备份的说明

内容预览：本篇涉及控制文件作用、多路复用和备份。（控制文件的重建恢复不在本系列，将纳入己亥清爽恢复系列）

目录

1. 控制文件的作用

2. 控制文件的多路复用

　　2.1 如何进行多路复用（OCM 11g 考题）

　　2.2 最多可进行几路复用

3. 控制文件的备份

　　3.1 TRACE备份

　　　　3.1.1 TRACE默认备份

　　　　3.1.2 TRACE指定路径备份

　　3.2 BINARY备份

 

1. 控制文件的作用

　　某某某的作用，这种已经烂大街且千篇一律的理论知识，相信你不感兴趣，我也不感兴趣。那是从前，从前的事都过去了，因为你遇见了我，这不叫自恋，而是自信。如果你是初学者，记着前面3句话即可，如果有点基础，记着5句话。

　　第 1 句：它是数据库启动的第二阶段（MOUNT）需要加载读取的文件。

　　第 2 句：它是一个二进制文件，不能直接手工编辑或查看，它很小，但很重要。

　　第 3 句：通过它可以查询当前数据库的物理状态。（比如当前实例是STARTED？还是MOUNTED？还是OPEN？）

　　第 4 句：它要维护数据的一致性（SCN）。

　　第 5 句：它记录着RMAN备份的元数据。

　　不知道什么是元数据？

　　在现实生活中：例如一个仓库的账本，里面记录了多少货架、每个货架分布的位置、每个货架每一层放了什么东西等等，这些信息就是这个仓库的元数据。

　　在数据库中：例如一个数据库的备份，具体什么时间做的备份、什么形式的备份、生成了几个备份集、包含多少数据文件、每个备份集几个备份片、具体路径在哪里等等，这些信息就是RMAN备份的元数据。

 

2. 控制文件的多路复用

2.1 如何进行多路复用（OCM 11g 考题）

直接引用一道考题做说明：Triplex the contrl file on the PROD1

将PROD1库的控制文件进行3路复用。

查看当前控制文件路径：

SQL> show parameter name 

NAME                  TYPE    VALUE
--------------------- ------- ---------
db_file_name_convert  string
db_name               string  PROD1
db_unique_name        string  PROD1
global_names          boolean FALSE
instance_name         string  PROD1
lock_name_space       string
log_file_name_convert string
processor_group_name  string
service_names         string  PROD1

SQL> show parameter control_files
SQL> select name from v$controlfile;

NAME
-------------------------------------------
/u01/app/oracle/oradata/PROD1/control01.ctl
/u01/app/oracle/oradata/PROD1/control02.ctl

更改 control_files 参数：

注意，该参数为动态参数，无法在 memory 中修改，所以scope选项不能是默认both，也不能是memory，只能是spfile。

SQL> alter system set control_files='/u01/app/oracle/oradata/PROD1/control01.ctl','/u01/app/oracle/oradata/PROD1/control02.ctl','/u01/app/oracle/oradata/PROD1/control03.ctl' scope=spfile;

System altered.

干净一致关闭数据库：

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

修改（复制）参数对应的物理文件：

SQL> !cp /u01/app/oracle/oradata/PROD1/control01.ctl /u01/app/oracle/oradata/PROD1/control03.ctl

保证参数里面指定的路径，和物理路径一致。

启动数据库并查询验证：

SQL> startup
ORACLE instance started.

Total System Global Area  830930944 bytes
Fixed Size            　　 2232920 bytes
Variable Size          　　591400360 bytes
Database Buffers      　　 234881024 bytes
Redo Buffers            　　2416640 bytes
Database mounted.
Database opened.
SQL> show parameter control_files 
SQL> select name from v$controlfile;

NAME
-------------------------------------------
/u01/app/oracle/oradata/PROD1/control01.ctl
/u01/app/oracle/oradata/PROD1/control02.ctl
/u01/app/oracle/oradata/PROD1/control03.ctl

2.2 最多可进行几路复用

来个9路复用试一下：所有路径的控制文件内容完全一样，且多路复用建议每一路放在不同路径下。

SQL> alter system set control_files='/u01/app/oracle/oradata/PROD1/control01.ctl','/u01/app/oracle/oradata/PROD1/control02.ctl','/u01/app/oracle/oradata/PROD1/control03.ctl','/u01/app/oracle/oradata/PROD1/control04.ctl','/u01/app/oracle/oradata/PROD1/control05.ctl','/u01/app/oracle/oradata/PROD1/control06.ctl','/u01/app/oracle/oradata/PROD1/control07.ctl','/u01/app/oracle/oradata/PROD1/control08.ctl','/u01/app/oracle/oradata/PROD1/control09.ctl' scope=spfile;

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> !cp /u01/app/oracle/oradata/PROD1/control01.ctl /u01/app/oracle/oradata/PROD1/control04.ctl

SQL> !cp /u01/app/oracle/oradata/PROD1/control01.ctl /u01/app/oracle/oradata/PROD1/control05.ctl

SQL> !cp /u01/app/oracle/oradata/PROD1/control01.ctl /u01/app/oracle/oradata/PROD1/control06.ctl

SQL> !cp /u01/app/oracle/oradata/PROD1/control01.ctl /u01/app/oracle/oradata/PROD1/control07.ctl

SQL> !cp /u01/app/oracle/oradata/PROD1/control01.ctl /u01/app/oracle/oradata/PROD1/control08.ctl

SQL> !cp /u01/app/oracle/oradata/PROD1/control01.ctl /u01/app/oracle/oradata/PROD1/control09.ctl

SQL> startup
ORACLE instance started.

Total System Global Area  830930944 bytes
Fixed Size            　　 2232920 bytes
Variable Size          　　591400360 bytes
Database Buffers      　　 234881024 bytes
Redo Buffers            　　2416640 bytes
ORA-03113: end-of-file on communication channel
Process ID: 5040
Session ID: 1 Serial number: 3

查看告警日志文件：

[oracle@henry trace]$ pwd
/u01/app/oracle/diag/rdbms/prod1/PROD1/trace
[oracle@henry trace]$ tail -f 200 alert_PROD1.log 
tail: cannot open ‘200’ for reading: No such file or directory
==> alert_PROD1.log <==
ORACLE_BASE from environment = /u01/app/oracle
Tue May 21 19:44:50 2019
ALTER DATABASE   MOUNT
Errors in file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_ckpt_5026.trc:
ORA-00208: number of control file names exceeds limit of 8
System state dump requested by (instance=1, osid=5026 (CKPT)), summary=[abnormal instance termination].
System State dumped to trace file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_diag_5014.trc
Dumping diagnostic data in directory=[cdmp_20190521194450], requested by (instance=1, osid=5026 (CKPT)), summary=[abnormal instance termination].
CKPT (ospid: 5026): terminating the instance due to error 208
Instance terminated by CKPT, pid = 5026

看下第10行：ORA-00208: number of control file names exceeds limit of 8

所以，控制文件多路复用最多为“八路军 - 铁流两万五千里，直向着一个坚定的方向！苦斗十年锻炼成一支不可战胜的力量。”！

再来看下官方文档 11g Release 2 (11.2) Database Reference 对此参数的描述：

CONTROL_FILES

Range of values：1 to 8 filenames

 

3. 控制文件的备份

3.1 TRACE备份

　　需要知道的是，严格意义上来说，这不是控制文件的备份，而是根据当前控制文件生成了一份 trace 跟踪文件，该文件里面记录了数据库结构的基本信息，而且trace出来的文件是可以直接查看的。

3.1.1 TRACE默认备份

SQL> alter database backup controlfile to trace;

Database altered.

默认情况下，放哪里了？可以根据日志文件查看到：

Tue May 21 20:07:53 2019
alter database backup controlfile to trace
Backup controlfile written to trace file /u01/app/oracle/diag/rdbms/prod1/PROD1/trace/PROD1_ora_6647.trc
Completed: alter database backup controlfile to trace

这个路径可以通过参数 dump 查询到：

SQL> show parameter dump

NAME                  TYPE   VALUE
--------------------- ------ ----------------------------
background_core_dump  string partial
background_dump_dest  string /u01/app/oracle/diag/rdbms/prod1/PROD1/trace
core_dump_dest        string /u01/app/oracle/diag/rdbms/prod1/PROD1/cdump
max_dump_file_size    string unlimited
shadow_core_dump      string partial
user_dump_dest        string /u01/app/oracle/diag/rdbms/prod1/PROD1/trace

查看下里面的内容：trace出来的控制文件，可以编辑或查看，原控制文件是不可编辑或查看的。

*** 2019-05-21 20:07:53.396
-- 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="PROD1"
--
-- LOG_ARCHIVE_CONFIG='SEND, RECEIVE, NODG_CONFIG'
-- LOG_ARCHIVE_MAX_PROCESSES=4
-- STANDBY_FILE_MANAGEMENT=MANUAL
-- STANDBY_ARCHIVE_DEST=?/dbs/arch
-- FAL_CLIENT=''
-- FAL_SERVER=''
--
-- LOG_ARCHIVE_DEST_1='LOCATION=/u01/app/oracle/archive1'
-- LOG_ARCHIVE_DEST_1='OPTIONAL REOPEN=300 NODELAY'
-- LOG_ARCHIVE_DEST_1='ARCH NOAFFIRM NOEXPEDITE NOVERIFY SYNC'
-- LOG_ARCHIVE_DEST_1='REGISTER NOALTERNATE 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 "PROD1" NORESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/PROD1/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/PROD1/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/PROD1/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/PROD1/system01.dbf',
  '/u01/app/oracle/oradata/PROD1/sysaux01.dbf',
  '/u01/app/oracle/oradata/PROD1/undotbs01.dbf',
  '/u01/app/oracle/oradata/PROD1/users01.dbf',
  '/u01/app/oracle/oradata/PROD1/example01.dbf',
  '/u01/app/oracle/oradata/PROD1/abc01.dbf',
  '/u01/app/oracle/oradata/PROD1/abcd01.dbf',
  '/u01/app/oracle/oradata/PROD1/tbs_c01.dbf',
  '/u01/app/oracle/oradata/PROD1/aaa01.dbf',
  '/u01/app/oracle/oradata/PROD1/aaa02.dbf'
CHARACTER SET AL32UTF8
;
-- 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/archive1/1_1_762083164.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/archive1/1_1_1001001677.dbf';
-- Recovery is required if any of the datafiles are restored backups,
-- or if the last shutdown was not normal or immediate.
RECOVER DATABASE
-- All logs need archiving and a log switch is needed.
ALTER SYSTEM ARCHIVE LOG ALL;
-- Database can now be opened normally.
ALTER DATABASE OPEN;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/PROD1/temp01.dbf'
     SIZE 30408704  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER TABLESPACE TMP_ABC ADD TEMPFILE '/u01/app/oracle/oradata/PROD1/tmpabc01.dbf'
     SIZE 2097152  REUSE AUTOEXTEND OFF;
-- End of tempfile additions.
--
--     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 "PROD1" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/PROD1/redo01.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 '/u01/app/oracle/oradata/PROD1/redo02.log'  SIZE 50M BLOCKSIZE 512,
  GROUP 3 '/u01/app/oracle/oradata/PROD1/redo03.log'  SIZE 50M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
  '/u01/app/oracle/oradata/PROD1/system01.dbf',
  '/u01/app/oracle/oradata/PROD1/sysaux01.dbf',
  '/u01/app/oracle/oradata/PROD1/undotbs01.dbf',
  '/u01/app/oracle/oradata/PROD1/users01.dbf',
  '/u01/app/oracle/oradata/PROD1/example01.dbf',
  '/u01/app/oracle/oradata/PROD1/abc01.dbf',
  '/u01/app/oracle/oradata/PROD1/abcd01.dbf',
  '/u01/app/oracle/oradata/PROD1/tbs_c01.dbf',
  '/u01/app/oracle/oradata/PROD1/aaa01.dbf',
  '/u01/app/oracle/oradata/PROD1/aaa02.dbf'
CHARACTER SET AL32UTF8
;
-- 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/archive1/1_1_762083164.dbf';
-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/archive1/1_1_1001001677.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;
-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/PROD1/temp01.dbf'
     SIZE 30408704  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;
ALTER TABLESPACE TMP_ABC ADD TEMPFILE '/u01/app/oracle/oradata/PROD1/tmpabc01.dbf'
     SIZE 2097152  REUSE AUTOEXTEND OFF;
-- End of tempfile additions.
--

里面涵盖两段重要内容：Set #1. NORESETLOGS case 和 Set #2. RESETLOGS case

后期可根据实际情况，通过对应代码段手工重建控制文件。本篇不涉及，请移至己亥清爽恢复系列查看。

3.1.2 TRACE指定路径备份

也可以直接trace的时候手工指定存储路径： TO TRACLE AS 路径

SQL> alter database backup controlfile to trace as '/u01/app/oracle/backup/con.ctl';

Database altered.

SQL> !ls /u01/app/oracle/backup/
con.ctl  online.ctl  trac.ctl

3.2 BINARY备份

　　BINARY二进制备份，可看作是控制文件的热备，二进制文件和trace文件不同，前者和原控制文件一样，无法通过命令查看，而后者可以手工查看。前者（binary）多用于手工还原恢复控制文件，而后者（trace）多用于手工重建控制文件。

SQL> alter database backup controlfile to '/u01/app/oracle/backup/binarycon.ctl';

Database altered.

SQL> !ls /u01/app/oracle/backup/
binarycon.ctl  con.ctl    online.ctl  trac.ctl