配置data guard broker时遇到的几个问题

最新推荐文章于 2022-06-27 16:31:04 发布

叽哩古哩

最新推荐文章于 2022-06-27 16:31:04 发布

阅读量1.1k

点赞数 1

分类专栏： dataguard

dataguard 专栏收录该内容

28 篇文章 1 订阅

订阅专栏

配置data guard broker时遇到的几个问题

环境：

Oracle 11203 on rhel6.3

primary: RAC

standby: instance

问题1：

DGMGRL> create configuration 'DRSoution' as
> primary database is 'tantest'
> connect identifier is tantest;
Error:
ORA-16525: the Data Guard broker is not yet available
ORA-06512: at "SYS.X$DBMS_DRS", line 228
ORA-06512: at line 1

DGMGRL> create configuration 'DRSoution' as
> primary database is 'tantest'
> connect identifier is tantest;
Error: ORA-16584: operation cannot be performed on a standby database

上面问题是必须连接到主库dgmgrl

问题2：

DGMGRL> create configuration 'DRSolution' as
> primary database is 'tantest'
> connect identifier by tantest;
connect identifier by tantest;
^
Syntax error before or at "by"
DGMGRL>
DGMGRL>
DGMGRL> create configuration 'DRSolution' as
> primary database is 'tantest'
> connect identifier is tantest;
Error: ORA-16571: Data Guard configuration file creation failure

Failed.

这个问题是因为dg_broker_config_file1 指定的目录有问题：
我的源端是RAC环境，该目录在ASM中，目录路径没有写全，导致出现该问题。文件broker配置中会自动创建，但目录不会自动创建，必须写全。

问题3：

DGMGRL> add database 'DGBRO' as
> connect identifier is dgbro;
Error: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

SQL下连接正常，在dgmgrl下不正常。
原因是connect identifier is dgbro;读的dgbro是在主库的tnsname.ora中的，必须把主库的写正确。

问题4：

DGMGRL> show configuration

Configuration - DRSolution

Protection Mode: MaxPerformance
Databases:
tantest - Primary database
Warning: ORA-16809: multiple warnings detected for the database

dgbro - Physical standby database
Warning: ORA-16809: multiple warnings detected for the database

Fast-Start Failover: DISABLED

Configuration Status:
WARNING

DGMGRL>

DGMGRL> show database 'tantest';

Database - tantest

Role:            PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
    tantest1
    tantest2

Database Warning(s):
ORA-16789: standby redo logs not configured
ORA-16789: standby redo logs not configured

Database Status:
WARNING

参照了下面的方法解决

http://www.oracledbasupport.co.uk/dgmgrl-ora-16789-standby-redo-logs-not-configuredora-16826-apply-service-state-is-inconsistent-with-the-delaymins-property/
在主库执行：

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

no rows selected

SQL> select bytes from v$log;

BYTES
----------
52428800
52428800
52428800
52428800

SQL> select group#, member from v$logfile;

GROUP#
----------
MEMBER
2
+DATA1/tantest/onlinelog/group_2.262.816103583

1
+DATA1/tantest/onlinelog/group_1.261.816103581

3
+DATA1/tantest/onlinelog/group_3.265.816103973

4
+DATA1/tantest/onlinelog/group_4.266.816103975

都是主库执行
alter database add standby logfile group 5 size 50m;
alter database add standby logfile group 6 size 50m;
alter database add standby logfile group 7 size 50m;
alter database add standby logfile group 8 size 50m;

SQL> select * from v$standby_log;

    GROUP# DBID                                                                                THREAD# SEQUENCE#      BYTES BLOCKSIZE       USED ARCHIV STATUS           FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# NEXT_TIME    LAST_CHANGE# LAST_TIME
---------- -------------------------------------------------------------------------------- ---------- ---------- ---------- ---------- ---------- ------ -------------------- ------------- ------------ ------------ ------------ ------------ ------------
         5 UNASSIGNED                                                                                0          0   52428800        512      0 YES    UNASSIGNED
         6 UNASSIGNED                                                                                0          0   52428800        512      0 YES    UNASSIGNED
         7 UNASSIGNED                                                                                0          0   52428800        512      0 YES    UNASSIGNED
         8 UNASSIGNED                                                                                0          0   52428800        512      0 YES    UNASSIGNED
DGMGRL> show database tantest

Database - tantest

Role:            PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
    tantest1
    tantest2

Database Status:
SUCCESS

DGMGRL>

问题5：

DGMGRL> show database verbose 'dgbro';

Database - dgbro

Role:            PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag:   0 seconds
Apply Lag:       20 minutes 29 seconds
Real Time Query: OFF
Instance(s):
    dgbro
      Warning: ORA-16714: the value of property ArchiveLagTarget is inconsistent with the database setting
      Warning: ORA-16714: the value of property LogArchiveMinSucceedDest is inconsistent with the database setting
      Warning: ORA-16714: the value of property LogArchiveTrace is inconsistent with the database setting
      Warning: ORA-16714: the value of property LogArchiveFormat is inconsistent with the database setting

Database Warning(s):
ORA-16826: apply service state is inconsistent with the DelayMins property

Properties:
    DGConnectIdentifier             = 'dgbro'
    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          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = 'tantest, dgbro'
    LogFileNameConvert              = '+FRI/TANTEST/ARCH1, /u01/arch2, +FRI/TANTEST/ARCH2, /u01/arch2'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'dgbro'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oeltodd)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dgbro_DGMGRL)(INSTANCE_NAME=dgbro)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u01/arch1/'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
WARNING

有几个参数和数据库中不一致,修改:
DGMGRL> edit database 'dgbro' set property 'LogArchiveFormat'='%t_%s_%r.dbf';
DGMGRL> edit database 'dgbro' set property 'ArchiveLagTarget'='0';
DGMGRL> edit database 'dgbro' set property 'LogArchiveMinSucceedDest'='1';
DGMGRL> edit database 'dgbro' set property 'LogArchiveTrace'='0';

下面这个参数得大于0
DGMGRL> edit database 'dgbro' set property 'DelayMins'='1';
DGMGRL> edit database 'tantest' set property 'DelayMins'='1';

再次检查正确：
DGMGRL> show database verbose dgbro;

Database - dgbro

Role:            PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag:   0 seconds
Apply Lag:       1 minute 49 seconds
Real Time Query: OFF
Instance(s):
    dgbro

Properties:
    DGConnectIdentifier             = 'dgbro'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '1'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '30'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = 'tantest, dgbro'
    LogFileNameConvert              = '+FRI/TANTEST/ARCH1, /u01/arch2, +FRI/TANTEST/ARCH2, /u01/arch2'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName                         = 'dgbro'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oeltodd)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=dgbro_DGMGRL)(INSTANCE_NAME=dgbro)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = '/u01/arch1/'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'