[20140519]使用dgmgrl配置dataguard.txt
--最近一段时间看了dgmgrl的使用文档,主要是了解一些细节,我也在生产系统使用dgmgrl来管理dataguard,我发现在10g下,
--不是很好用,也许这个是国内许多人并没有使用它来管理。实际上11G已经很好使用,
--11G
DGMGRL> show configuration verbose
Configuration - study
Protection Mode: MaxPerformance
Databases:
test - Primary database
testdg - Physical standby database
Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
--10g
DGMGRL> show configuration verbose
Configuration
Name: xxxlaji
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
xxx430 - Primary database
xxx430dg - Physical standby database
xxx430d2 - Physical standby database
Current status for "xxxlaji":
SUCCESS
--很明显10g显示的信息相对很少。而且10g下显示Intended State: ONLINE,而11G下Intended State: TRANSPORT-ON(主库),
--Intended State: APPLY-ON(备库),这样更加直观。
--在配置dg时,至少要修改编辑一些参数(在主库),备库基本相似:
alter system set fal_client= 'test';
alter system set fal_server= 'testdg' ;
alter system set log_archive_config='DG_CONFIG=(test,testdg)' ;
alter system set log_archive_dest_1='LOCATION=/u01/app/oracle11g/archivelog MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test';
alter system set log_archive_dest_2='SERVICE=testdg LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=testdg' ;
alter system set log_archive_dest_state_2='ENABLE' ;
alter system set standby_file_management='auto' ;
alter system set log_file_name_convert='/u01/app/oracle11g/oradata/test','/u01/app/oracle11g/oradata/test';
alter system set db_file_name_convert='/u01/app/oracle11g/oradata/test','/u01/app/oracle11g/oradata/test';
--我想做一下测试,如果配置参数尽量少改动来完成dg的配置。
1.停止数据库,先做一个参数文件以及dg配置的备份,安全考虑:
--我的测试环境:primary的tnsnames别名test,standby主机的tnsnames别名testdg。数据库版本:
SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
--另外我主备库都打开了flashback。
$ cd /u01/app/oracle11g/product/11.2.0/db_2/dbs
$ mkdir spfile_backup
$ cp spfiletest.ora spfile_backup/spfiletest.ora_20140519
$ mv dr*.dat spfile_backup/
--备用库也一样。
$ cd /u01/app/oracle11g/product/11.2.0/db_2/dbs
$ mkdir spfile_backup
$ cp spfiletestdg.ora spfile_backup/spfiletestdg.ora_20140519
$ mv dr*.dat spfile_backup/
2.建立与修改文本参数文件看看:
$ rlsql
SQL*Plus: Release 11.2.0.3.0 Production on Mon May 19 09:17:06 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to an idle instance.
SYS@test> create pfile from spfile ;
File created.
--注解以下参数:
# *.dg_broker_start=TRUE
# *.fal_client='test'
# *.fal_server='testdg'
# *.log_archive_config='DG_CONFIG=(test,testdg)'
# *.log_archive_dest_1='LOCATION=/u01/app/oracle11g/archivelog MANDATORY VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=test'
# *.log_archive_dest_2='service="testdg"','LGWR SYNC AFFIRM delay=0 optional compression=enable max_failure=0 max_connections=1 reopen=300 db_unique_name="testdg" net_timeout=30','valid_for=(all_logfiles,primary_role)'
# *.log_archive_dest_state_2='ENABLE'
--修改保留以下参数,正常实际上就是增加了DB_UNIQUE_NAME,log_file_name_convert,db_file_name_convert参数,standby_file_management修改为auto,缺省是manual。
*.log_archive_dest_1='LOCATION=/u01/app/oracle11g/archivelog'
*.standby_file_management='auto'
*.DB_UNIQUE_NAME=test
*.log_file_name_convert='/u01/app/oracle11g/oradata/test','/u01/app/oracle11g/oradata/test'
*.db_file_name_convert='/u01/app/oracle11g/oradata/test','/u01/app/oracle11g/oradata/test'
--备用库也一样操作,步骤忽略。
--在主备库上建立spfile。
SYS@test> create spfile from pfile ;
File created.
SYS@testdg> create spfile from pfile ;
File created.
3.启动主库到open,备库到mount状态:
--主库test:
SYS@test> startup
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 1006636496 bytes
Database Buffers 587202560 bytes
Redo Buffers 7344128 bytes
Database mounted.
Database opened.
--备用库testdg:
SYS@testdg> startup nomount
ORACLE instance started.
Total System Global Area 1603411968 bytes
Fixed Size 2228784 bytes
Variable Size 939527632 bytes
Database Buffers 654311424 bytes
Redo Buffers 7344128 bytes
SYS@testdg> alter database mount standby database;
Database altered.
4.使用dgmgrl来管理看看:
$ rldgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration
Error:
ORA-16525: the Data Guard broker is not yet available
Configuration details cannot be determined by DGMGRL
SYS@test> show parameter dg
NAME TYPE VALUE
----------------------- -------- -------------------------------------------------------
dg_broker_config_file1 string /u01/app/oracle11g/product/11.2.0/db_2/dbs/dr1test.dat
dg_broker_config_file2 string /u01/app/oracle11g/product/11.2.0/db_2/dbs/dr2test.dat
dg_broker_start boolean FALSE
--没有配置dg_broker_start=true。
SYS@test> alter system set dg_broker_start=true ;
System altered.
SYS@testdg> alter system set dg_broker_start=true ;
System altered.
DGMGRL> create configuration study as primary database is "test" connect identifier is "test";
Configuration "study" created with primary database "test"
DGMGRL> add database "testdg" as connect identifier is "testdg";
Database "testdg" added
DGMGRL> show configuration
Configuration - study
Protection Mode: MaxPerformance
Databases:
test - Primary database
testdg - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
DISABLED
--没有disabled。
DGMGRL> enable configuration
Enabled.
DGMGRL> show database test
Database - test
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
test
Database Status:
SUCCESS
DGMGRL> show database testdg
Database - testdg
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
testdg
Database Status:
SUCCESS
DGMGRL> show configuration verbose
Configuration - study
Protection Mode: MaxPerformance
Databases:
test - Primary database
testdg - Physical standby database
Properties:
FastStartFailoverThreshold = '30'
OperationTimeout = '30'
FastStartFailoverLagLimit = '30'
CommunicationTimeout = '180'
FastStartFailoverAutoReinstate = 'TRUE'
FastStartFailoverPmyShutdown = 'TRUE'
BystandersFollowRoleChange = 'ALL'
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS
--可以从上面的参数看,已经配置成功!
5.看看那些参数被修改了:
SYS@test> show parameter fal
NAME TYPE VALUE
------------ ------------- ------------
fal_client string
fal_server string
SYS@testdg> show parameter fal
NAME TYPE VALUE
----------- -------------- --------------
fal_client string
fal_server string test
SYS@test> show parameter log_archive_config
NAME TYPE VALUE
------------------- ------- -------------------------
log_archive_config string dg_config=(test,testdg)
SYS@testdg> show parameter log_archive_config
NAME TYPE VALUE
------------------- ------- ------------------------
log_archive_config string dg_config=(testdg,test)
SYS@test> show parameter log_archive_dest_2
NAME TYPE VALUE
-------------------- -------- ----------------------------------------------------------------------------------------------------
log_archive_dest_2 string service="testdg", LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connect
ions=1 reopen=300 db_unique_name="testdg" net_timeout=30, valid_for=(all_logfiles,primary_role)
--其他参数并没有修改。
6.做一个switchover看看:
DGMGRL> switchover to testdg;
Performing switchover NOW, please wait...
New primary database "testdg" is opening...
Operation requires shutdown of instance "test" on database "test"
Shutting down instance "test"...
ORACLE instance shut down.
Operation requires startup of instance "test" on database "test"
Starting instance "test"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "testdg"
SYS@test> select database_role,open_mode,protection_mode,force_logging,FLASHBACK_ON from v$database;
DATABASE_ROLE OPEN_MODE PROTECTION_MODE FOR FLASHBACK_ON
---------------- -------------------- -------------------- --- ------------------
PHYSICAL STANDBY MOUNTED MAXIMUM PERFORMANCE YES YES
--再切换回来!
DGMGRL> switchover to test;
Performing switchover NOW, please wait...
New primary database "test" is opening...
Operation requires shutdown of instance "testdg" on database "testdg"
Shutting down instance "testdg"...
ORACLE instance shut down.
Operation requires startup of instance "testdg" on database "testdg"
Starting instance "testdg"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "test"
DGMGRL> show database testdg
Database - testdg
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds
Apply Lag: 0 seconds
Real Time Query: OFF
Instance(s):
testdg
Database Status:
SUCCESS
DGMGRL> show database test
Database - test
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
test
Database Status:
SUCCESS
SYS@test> select database_role,open_mode,protection_mode,force_logging,FLASHBACK_ON from v$database;
DATABASE_ROLE OPEN_MODE PROTECTION_MODE FOR FLASHBACK_ON
---------------- -------------------- -------------------- --- ------------------
PRIMARY READ WRITE MAXIMUM PERFORMANCE YES YES
SYS@testdg> select database_role,open_mode,protection_mode,force_logging,FLASHBACK_ON from v$database;
DATABASE_ROLE OPEN_MODE PROTECTION_MODE FOR FLASHBACK_ON
---------------- -------------------- -------------------- --- ------------------
PHYSICAL STANDBY MOUNTED MAXIMUM PERFORMANCE YES YES
7.看看参数变换:
SYS@test> show parameter fal
NAME TYPE VALUE
----------- ------- ----------
fal_client string
fal_server string testdg
--自动配置了fal_server参数。
SYS@testdg> show parameter fal
NAME TYPE VALUE
----------- -------- --------
fal_client string
fal_server string test
SYS@test> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------- ------- ----------------------------------------------------------------------------------------------------
log_archive_dest_2 string service="testdg", LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connect
ions=1 reopen=300 db_unique_name="testdg" net_timeout=30, valid_for=(all_logfiles,primary_role)
SYS@testdg> show parameter log_archive_dest_2
NAME TYPE VALUE
------------------- ------- ----------------------------------------------------------------------------------------------------
log_archive_dest_2 string service="test", LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connectio
ns=1 reopen=300 db_unique_name="test" net_timeout=30, valid_for=(all_logfiles,primary_role)
--自动配置了log_archive_dest_2,根本无需操作。
总结:
11G下使用dgmgrl,而且配置变得非常简单,我仅仅增加参数:DB_UNIQUE_NAME,log_file_name_convert,db_file_name_convert参数,
standby_file_management修改为auto,缺省是manual。整个操作非常简单。