[20140519]使用dgmgrl配置dataguard.txt

[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。整个操作非常简单。