dataguard parameters

dataguard的一些重要参数的解释。

系统版本：

SQL> select * from v$version   2  /BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE    11.2.0.4.0  Production

开启日志的强制写

SQL> ALTER DATABASE FORCE LOGGING

这里写到开启force logging时候可能会遇到的问题

When you issue this statement, the primary database must at least be
mounted (and it can also be open). This statement can take a
considerable amount of time to complete, because it waits for all
unlogged direct write I/O to finish.

此举是防止丢失数据的

2 设置redo的传输方式
通常都是依靠密码文件哦,很多人都不知道oracle还有一个基于weblogic类似AD域认证的东西。

3 设置主库的init_sid.ora参数(初始化文件参数)

如这里有主备两个库

Database            DB_UNIQUE_NAME  Oracle Net Service NamePrimary             chicago                 chicagoPhysical standby    boston                   boston

解释一下主库的初始化文件的

DB_NAME=chicago
DB_UNIQUE_NAME=chicago
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(chicago,boston)’

– 这里指定主库和备库，至少让主库知道备库在哪里

– 当然我们可以用命令来修改

alter system set log_archive_config = 'dg_config=(chicago,boston)';

CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'LOG_ARCHIVE_DEST_1= 'LOCATION=/arch1/chicago/   VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=chicago'  ``` --- 该参数指定归档的路径 ，注意valid_for参数的设置LOG_ARCHIVE_DEST_2= 'SERVICE=boston ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)   DB_UNIQUE_NAME=boston'   -- 该参数指定我们on redo日子的传输路径，同样我们要注意vaild_for参数的设置 `LOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLEREMOTE_LOGIN_PASSWORDFILE=EXCLUSIVELOG_ARCHIVE_FORMAT=%t_%s_%r.arc

注意一下这两个参数：

LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE

这个参数是允许日子传输的,但实际上其状态应该enable无他了。

备库的初始化参数文件的设置

FAL_SERVER=boston
DB_FILE_NAME_CONVERT=’boston’,’chicago’ – 官方文档倒是很厉害的样子，
LOG_FILE_NAME_CONVERT=
‘/arch1/boston/’,’/arch1/chicago/’,’/arch2/boston/’,’/arch2/chicago/’
STANDBY_FILE_MANAGEMENT=AUTO

我自己以前搭dg的设置

*.db_file_name_convert='/u01/app/oracle/oradata/orcl_prd','/u01/app/oracle/oradata/orcl_dg'*.log_file_name_convert='/u01/app/oracle/oradata/orcl_prd','/u01/app/oracle/oradata/orcl_dg'

那么fal_server 这个参数也是需要研究的

看看官方文档的解释:

Specify the Oracle Net service name of the FAL server (typically this
is the database running in the primary role). When the Chicago
database is running in the standby role, it uses the Boston database
as the FAL server from which to fetch (request) missing archived redo
log files if Boston is unable to automatically send the missing log
files.)

其实很简单：
在主库上
fal_server=boston (当现在的主库 chicago 切成的备库的时候，那么这个archivelog gap 去哪找呢 就去
boston上找呗，因为当时的boston已经由备库转变成主库啦
)

在备库上:(这个逻辑就很简单咯)
fal_server=chicago

所以我觉得官网这个fal_server配置的应该是错误的。不过这个参数只有在 archivelog gap时候有用

至于STANDBY_FILE_MANAGEMENT

STANDBY_FILE_MANAGEMENTSet to AUTO so when datafiles are added to or
dropped from the primary database, corresponding changes are made
automatically to the standby database.

4 启动到主库的归档模式

alter database archivelog;

附上主库和备库的初始化参数：

DB_NAME=chicagoDB_UNIQUE_NAME=chicagoLOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'CONTROL_FILES='/arch1/chicago/control1.ctl', '/arch2/chicago/control2.ctl'LOG_ARCHIVE_DEST_1= 'LOCATION=/arch1/chicago/   VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB_UNIQUE_NAME=chicago'LOG_ARCHIVE_DEST_2= 'SERVICE=boston ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)   DB_UNIQUE_NAME=boston'LOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLEREMOTE_LOGIN_PASSWORDFILE=EXCLUSIVELOG_ARCHIVE_FORMAT=%t_%s_%r.arc

备库：

DB_NAME=chicagoDB_UNIQUE_NAME=bostonLOG_ARCHIVE_CONFIG='DG_CONFIG=(chicago,boston)'CONTROL_FILES='/arch1/boston/control1.ctl', '/arch2/boston/control2.ctl'DB_FILE_NAME_CONVERT='chicago','boston'LOG_FILE_NAME_CONVERT= '/arch1/chicago/','/arch1/boston/','/arch2/chicago/','/arch2/boston/'LOG_ARCHIVE_FORMAT=log%t_%s_%r.arcLOG_ARCHIVE_DEST_1= 'LOCATION=/arch1/boston/  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)   DB_UNIQUE_NAME=boston'LOG_ARCHIVE_DEST_2= 'SERVICE=chicago ASYNC  VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)   DB_UNIQUE_NAME=chicago'LOG_ARCHIVE_DEST_STATE_1=ENABLELOG_ARCHIVE_DEST_STATE_2=ENABLEREMOTE_LOGIN_PASSWORDFILE=EXCLUSIVESTANDBY_FILE_MANAGEMENT=AUTOFAL_SERVER=chicago

注意一下 STANDBY_FILE_MANAGEMENT 也要设置下，这里涉及到主库一些涉及物理文件的操作

SQL> create tablespace t datafile'/u01/app/t001.dbf' size 10m autoextend on next 5m maxsize unlimited  2  ;Tablespace created.SQL> show parameter db_unique_nameNAME                     TYPE    VALUE------------------------------------ ----------- ------------------------------db_unique_name               string  orcl_prdSQL> 去备库查看的时候：SQL> show parameter db_uniqueNAME                     TYPE    VALUE------------------------------------ ----------- ------------------------------db_unique_name               string  orcl_dgSQL> select name from v$tablespace  2  /NAME------------------------------SYSTEMSYSAUXUNDOTBS1USERSTEMPTESTT7 rows selected.