实现控制文件的多路复用

控制文件的重要性：

数据库在启动时经历三个过程：nomount-mount-open

nomount状态:根据特定的规则去指定路径下寻找参数文件，根据参数文件中配置的参数信息分配SGA，启动后台进程，打开数据库实例，注意参数文件中记录了控制文件的路径

mount状态：根据参数文件中定义的控制文件的路径找到并打开控制文件，注意控制文件中记录了数据文件以及日志文件等信息

open状态：根据控制文件中记录的数据文件和日志文件的信息，打开加载这些文件，打开数据库。

其中控制文件是一个很小的二进制文件，不能直接打开，可以转储出来查看

SQL> alter database backup controlfile to trace as '/opt/oracle/backup/control.ctl';

 

数据库已更改。

 

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="test"

--

-- 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/arch'

-- 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 "TEST" NORESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/u01/app/oracle/oradata/test/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/u01/app/oracle/oradata/test/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/u01/app/oracle/oradata/test/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/u01/app/oracle/oradata/test/system01.dbf',

  '/u01/app/oracle/oradata/test/sysaux01.dbf',

  '/u01/app/oracle/oradata/test/undotbs01.dbf',

  '/u01/app/oracle/oradata/test/users01.dbf',

  '/u01/app/oracle/oradata/test/example01.dbf'

CHARACTER SET AL32UTF8

;

 

-- Configure RMAN configuration record 1

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');

-- 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/arch/1_1_824297850.dbf';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/arch/1_1_915120894.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/test/temp01.dbf'

     SIZE 30408704  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

-- 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 "TEST" RESETLOGS  ARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 '/u01/app/oracle/oradata/test/redo01.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 2 '/u01/app/oracle/oradata/test/redo02.log'  SIZE 50M BLOCKSIZE 512,

  GROUP 3 '/u01/app/oracle/oradata/test/redo03.log'  SIZE 50M BLOCKSIZE 512

-- STANDBY LOGFILE

DATAFILE

  '/u01/app/oracle/oradata/test/system01.dbf',

  '/u01/app/oracle/oradata/test/sysaux01.dbf',

  '/u01/app/oracle/oradata/test/undotbs01.dbf',

  '/u01/app/oracle/oradata/test/users01.dbf',

  '/u01/app/oracle/oradata/test/example01.dbf'

CHARACTER SET AL32UTF8

;

 

-- Configure RMAN configuration record 1

VARIABLE RECNO NUMBER;

EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');

-- 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/arch/1_1_824297850.dbf';

-- ALTER DATABASE REGISTER LOGFILE '/u01/app/oracle/arch/1_1_915120894.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/test/temp01.dbf'

     SIZE 30408704  REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;

-- End of tempfile additions.

--

 

可以看到控制文件里面记录了数据文件，重做日志，字符集，备份文件等信息，还包括一些日志序列号，检查点信息等，在实例打开的时候会比对控制文件中记录的SCN号和数据文件头部记录的SCN号，如果不一致则会进行恢复，因此对于一个数据库环境来说，控制文件的重要性是不言而喻的。因此在生产环境中就需要考虑到控制文件的多路复用。以便更好的保护控制文件。

下面就介绍控制文件多路复用的实现方法：

备份参数文件:

SQL> create pfile='/opt/oracle/initorcl.ora' from spfile;

 

文件已创建。

看控制文件的存放路径


方法一:修改增加控制文件的存放路径（由于control_files是静态参数，修改完之后必须重启数据库生效）

相关命令为：

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

可以看到我增加了一个名为control03.ctl的控制文件，路径为

/u01/app/oracle/oradata/test/control03.ctl'，因为是测试，所以不考虑不同磁盘的冗余
关闭数据库：

SQL> shut immediate;

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL>

复制控制文件：

[oracle@oracle ~]$ cd /u01/app/oracle/oradata/test/

[oracle@oracle test]$ ls

control01.ctl  control02.ctl  example01.dbf  redo01.log  redo02.log  redo03.log  sysaux01.dbf  system01.dbf  temp01.dbf  undotbs01.dbf  users01.dbf

[oracle@oracle test]$ cp control01.ctl control03.ctl

[oracle@oracle test]$ ls control*

control01.ctl  control02.ctl  control03.ctl

启动数据库并查看控制文件的路径：

可以看到增加的control03.ctl已经记录到control_files里面了,修改已经生效。

方法二：直接修改pfile文件并关闭数据库

SQL> create pfile='/opt/oracle/initorcl.ora' from spfile;

 

文件已创建。

 

SQL> shut immediate

数据库已经关闭。

已经卸载数据库。

ORACLE 例程已经关闭。

SQL>

修改pfile

[oracle@oracle ~]$ vi initorcl.ora

 

test.__db_cache_size=398458880

test.__java_pool_size=4194304

test.__large_pool_size=8388608

test.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

test.__pga_aggregate_target=381681664

test.__sga_target=570425344

test.__shared_io_pool_size=0

test.__shared_pool_size=146800640

test.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/test/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/u01/app/oracle/oradata/test/control01.ctl','/u01/app/oracle/oradata/test/control02.ctl','/u01/app/oracle/oradata/test/control03.ctl','/u01/app/oracle/oradata/test/control04.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='test'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4385144832

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'

*.log_archive_dest_1='location=/u01/app/oracle/arch'

*.memory_target=951058432

*.nls_language='SIMPLIFIED CHINESE'

*.nls_territory='CHINA'

*.open_cursors=300

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.undo_tablespace='UNDOTBS1'

~

~

~

~

~

~

~

~

~

~

~

~

"initorcl.ora" 28L, 1094C 已写入                                                                                                          

[oracle@oracle ~]$

复制控制文件：

[oracle@oracle test]$ cp control01.ctl control04.ctl

启动数据库：

生成pfile文件

SQL> create spfile from pfile='/opt/oracle/initorcl.ora';

 

文件已创建。

 

SQL>

重启数据库

修改已经生效。