[20140415]使用dgmgrl管理dataguard(2)

最新推荐文章于 2023-02-28 22:37:07 发布

cuixun7780

最新推荐文章于 2023-02-28 22:37:07 发布

阅读量141

点赞数

[20140415]使用dgmgrl管理dataguard(2).txt

前面的链接如下:http://blog.itpub.net/267265/viewspace-1142649/

介绍使用dgmgrl的好处以及一些设置工作，这篇将讲解如何配置以及简单使用：

我的测试环境：primary的tnsnames别名test，standby主机的tnsnames别名testdg。数据库版本：

SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

--并且已经配置安装dataguard环境。

1.现在开始配置。

DGMGRL> create configuration study as primary database is "test" connect identifier is "test";
Error:
ORA-16525: the Data Guard broker is not yet available
ORA-06512: at "SYS.DBMS_DRS", line 157
ORA-06512: at line 1

-- 可以发现必须修改一个参数dg_broker_start。
-- broker 表示 n.掮客, 经纪人的意思。

--在修改参数dg_broker_start前做一个记录：
$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 15-APR-2014 16:41:30

$ ps -ef | grep -i dmon
503 10460 9897 0 16:41 pts/1 00:00:00 grep -i dmon
--没有包含dmon名字的进程。

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

$ ll /u01/app/oracle11g/product/11.2.0/db_2/dbs/dr*
ls: /u01/app/oracle11g/product/11.2.0/db_2/dbs/dr*: No such file or directory
--文件不存在。

2.修改dg_broker_start=true，观察变化：

SYS@test> alter system set dg_broker_start=true scope=both;
System altered.

--观察变化：

$ ps -ef | grep -i dmon
503 10572 1 0 16:45 ? 00:00:00 ora_dmon_test
503 10732 9897 0 16:45 pts/1 00:00:00 grep -i dmon
--可以发现启动一个进程ora_dmon_test。继续dgmgrl的配置：

DGMGRL> create configuration study as primary database is "test" connect identifier is "test";
Configuration "study" created with primary database "test"

--OK!成功。

$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 15-APR-2014 16:48:34

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=hisdg)(PORT=1521)))
Services Summary...
Service "test.com" has 2 instance(s).
Instance "test", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:2 refused:0
         LOCAL SERVER
Instance "test", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
Service "testXDB.com" has 1 instance(s).
Instance "test", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER
         (ADDRESS=(PROTOCOL=tcp)(HOST=hisdg)(PORT=60953))
Service "test_DGB.com" has 1 instance(s).
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Instance "test", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:0 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
--注意 ~的内容，可以发现增加一个服务，名字为test_DGB.com.
--我的监听配置里面静态注册test.com服务，静态注册的状态UNKNOWN.从这里看出服务名test_DGB.com是动态注册的(status READY)。

SCOTT@test> show parameter db_domain
NAME TYPE VALUE
---------- ------- ------
db_domain string com

$ ll /u01/app/oracle11g/product/11.2.0/db_2/dbs/dr*
-rw-r----- 1 oracle11g oinstall 8192 2014-04-15 16:48:02 /u01/app/oracle11g/product/11.2.0/db_2/dbs/dr1test.dat
-rw-r----- 1 oracle11g oinstall 12288 2014-04-15 16:48:02 /u01/app/oracle11g/product/11.2.0/db_2/dbs/dr2test.dat

--可以发现建立了这两个文件，注意一点如果是rac环境我估计这两个文件应该放在共享存储上，这样各个实例才能正常访问以及使用。

3.增加dataguard机器。
DGMGRL> add database "testdg" as connect identifier is "testdg" maintained as physical;
Database "testdg" added

4.一些简单命令：

DGMGRL> show database "test" staticConnectIdentifier
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hisdg)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=test_DGMGRL.com)(INSTANCE_NAME=test)(SERVER=DEDICATED)))'
DGMGRL> show database "test" STATICCONNECTIDENTIFIER
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hisdg)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=test_DGMGRL.com)(INSTANCE_NAME=test)(SERVER=DEDICATED)))'
DGMGRL> show database "test" staticconnectidentifier
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hisdg)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=test_DGMGRL.com)(INSTANCE_NAME=test)(SERVER=DEDICATED)))'

--可以发现命令不区分大小写。
--另外注意StaticConnectIdentifier的服务名是SERVICE_NAME=test_DGMGRL.com。我估计以后要切换成功，必须修改监听配置，注册这个服务或者修改这个标识。(留到做切换时再测试)

5.继续测试：
DGMGRL> show database verbose "test"

Database - test

Role: PRIMARY
Intended State: OFFLINE
Instance(s):
test

Properties:
    DGConnectIdentifier             = 'test'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'auto'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/u01/app/oracle11g/oradata/test, /u01/app/oracle11g/oradata/test'
    LogFileNameConvert              = '/u01/app/oracle11g/oradata/test, /u01/app/oracle11g/oradata/test'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'test'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=hisdg)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=test_DGMGRL.com)(INSTANCE_NAME=test)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u01/app/oracle11g/archivelog'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
DISABLED

DGMGRL> show configuration

Configuration - study

Protection Mode: MaxPerformance
Databases:
test - Primary database
testdg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

--注意Configuration Status以及Database Status没有enable。

6.enable Configuration以及enable database：
DGMGRL> enable configuration
Enabled.
DGMGRL> show configuration

Configuration - study

Protection Mode: MaxPerformance
Databases:
    test   - Primary database
    testdg - Physical standby database
      Error: ORA-16525: the Data Guard broker is not yet available

Fast-Start Failover: DISABLED

Configuration Status:
ERROR

-- enable configuration 并没有报错，看来要先enable database先。

DGMGRL> Enable database "testdg"
Enabled.

DGMGRL> show database "testdg"

Database - testdg

Role:            PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag:   (unknown)
Apply Lag:       (unknown)
Real Time Query: OFF
Instance(s):
    testdg

Database Status:
DGM-17016: failed to retrieve status for database "testdg"
ORA-16525: the Data Guard broker is not yet available
ORA-16625: cannot reach database "testdg"

--看来dg数据库也要打开dg_broker_start=true。
SYS@testdg> alter system set dg_broker_start=true scope=both ;
System altered.
--补充监听的服务在dg主机上。
testdg$ lsnrctl service

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 15-APR-2014 17:52:58

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=testdb)(PORT=1521)))
Services Summary...
Service "test.com" has 2 instance(s).
Instance "testdg", status UNKNOWN, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:14 refused:0
         LOCAL SERVER
Instance "testdg", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3 refused:0 state:ready
         LOCAL SERVER
Service "testdg.com" has 1 instance(s).
Instance "testdg", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3 refused:0 state:ready
         LOCAL SERVER
Service "testdg_DGB.com" has 1 instance(s).
Instance "testdg", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:3 refused:0 state:ready
         LOCAL SERVER
The command completed successfully
--可以发现也会动态注册服务testdg_DGB.com。

--打开后需要等1小段时间，执行如下才正常。
DGMGRL> show database verbose "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

Properties:
    DGConnectIdentifier             = 'testdg'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'SYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'auto'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = '/u01/app/oracle11g/oradata/test, /u01/app/oracle11g/oradata/test'
    LogFileNameConvert              = '/u01/app/oracle11g/oradata/test, /u01/app/oracle11g/oradata/test'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'testdg'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=testdb)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=testdg_DGMGRL.com)(INSTANCE_NAME=testdg)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u01/app/oracle11g/archivelog'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

6.检查配置：
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

DGMGRL> show con
CONNECT_DATA Connected condition configuration connect convert
DGMGRL> show configuration

Configuration - study

Protection Mode: MaxPerformance
Databases:
test - Primary database
testdg - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> enable database "test"
Enabled.
DGMGRL> enable database "testdg"
Enabled.
DGMGRL> enable configuration
Enabled.

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

--OK 终于正常了。

6.总结：
1.创建配置
create configuration study as primary database is "test" connect identifier is "test";
2.添加备用库
add database "testdg" as connect identifier is "testdg" maintained as physical;
3.查看配置
show configuration
show configuration verbose
4.查看数据库或者实例
show database test
show database verbose test
show database testdg
show database verbose testdg

show instance test
show instance verbose test
show instance testdg
show instance verbose testdg

5.启用配置
enable configuration
enable database test
enable database testdg

6.要启动dgmgrl要打开
alter system set dg_broker_start=true scope=both;
系统会启动一个进程ora_dmon_XXXX.并且会动态注册一个服务，命名为_DGB.

7.要建立两个dg_broker的配置文件。

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 TRUE

我也做了一些简单比较：

$ xxd -c 16 dr1test.dat > /tmp/xx1
$ xxd -c 16 dr2test.dat > /tmp/xx2
$ diff -Nur /tmp/xx1 /tmp/xx2
--- /tmp/xx1 2014-04-16 10:30:12.000000000 +0800
+++ /tmp/xx2 2014-04-16 10:30:16.000000000 +0800
@@ -270,7 +270,7 @@
00010d0: 0000 0000 0000 0000 0000 0000 0000 0000 ................
00010e0: 0100 0000 0000 0000 0000 0000 0100 0000 ................
00010f0: 1e00 0000 1e00 0000 1e00 0000 0000 0000 ................
-0001100: 0b00 0000 944c c824 0000 0000 0000 0000 .....L?........
+0001100: 0a00 0000 944c c824 0000 0000 0000 0000 .....L?........
0001110: 0000 0000 0000 0000 0400 0000 4272 6f75 ............Brou
0001120: 6768 7420 746f 2079 6f75 2062 7920 7468 ght to you by th
0001130: 6520 6669 6e65 2066 6f6c 6b73 2061 7420 e fine folks at
@@ -314,7 +314,7 @@
0001390: 0000 0000 0000 0000 1a00 0000 0000 0300 ................
00013a0: 4d49 5600 0000 0000 0000 0000 0000 0000 MIV.............
00013b0: 0000 0000 0000 0000 0000 0000 0000 0101 ................
-00013c0: 0022 0000 0000 0100 0b00 0000 0100 1a00 ."..............
+00013c0: 0022 0000 0000 0100 0a00 0000 0100 1a00 ."..............
00013d0: 4661 7374 5374 6172 7446 6169 6c6f 7665 FastStartFailove
00013e0: 7254 6872 6573 686f 6c64 0000 0000 0101 rThreshold......
00013f0: 0100 4000 0000 0b00 1e00 0000 0200 1500 ..@.............
@@ -510,7 +510,7 @@
0001fd0: 7200 0000 0000 0000 0000 0000 0000 0102 r...............
0001fe0: 9180 0000 0500 0000 1601 0000 1e00 0e00 ................
0001ff0: 4c73 6279 4153 6b69 7043 6667 5072 0000 LsbyASkipCfgPr..
-0002000: 0b82 5b71 0200 0000 0000 0000 0000 0000 ..[q............
+0002000: 0a82 5a71 0200 0000 0000 0000 0000 0000 ..Zq............
0002010: 0000 0000 0000 0102 9180 0000 0000 0000 ................
0002020: 0000 0000 1f00 0e00 4c73 6279 4453 6b69 ........LsbyDSki
0002030: 7043 6667 5072 0000 0000 0000 0000 0000 pCfgPr..........
@@ -766,7 +766,7 @@
0002fd0: 0000 0000 0000 0102 0503 0000 0000 0000 ................
0002fe0: 0000 0000 0e00 0900 4465 6c61 794d 696e ........DelayMin
0002ff0: 7300 0000 0000 0000 0000 0000 0000 0000 s...............
-0003000: 0b82 f5c8 0300 0000 0000 0000 0000 0101 ..跞............
+0003000: 0a82 f4c8 0300 0000 0000 0000 0000 0101 ..羧............
0003010: 0485 0000 0000 0000 0000 0000 0f00 0700 ................
0003020: 4269 6e64 696e 6700 0000 0000 0000 0000 Binding.........
0003030: 0000 0000 0000 0000 0000 0000 0000 0102 ................