实现控制文件的多路复用

最新推荐文章于 2022-05-22 13:35:29 发布

citeng1329

最新推荐文章于 2022-05-22 13:35:29 发布

阅读量697

点赞数

文章标签：数据库网络

控制文件的重要性：

数据库在启动时经历三个过程：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