Oracle Database 19c使用Data Guard Broker进行Data Guard物理备库设置

版权声明:本文为Buddy Yuan原创文章,转载请注明出处。原文地址:Oracle Database 19c使用Data Guard Broker进行Data Guard物理备库设置
今天准备研究学习一下19c的data guard。由于想快速搭建,所以选择了vagrant去拖镜像,但是发现拖下来的东西还是没有实现dg(这里的情况不是很了解,感觉是执行脚本有个地方报错了),不过它里面的脚本我觉得没什么大问题。我自己凑合手动执行脚本搭了一个。

首先看一下先决条件。
primary主机,操作系统是RedHat 7.8,安装了oracle 19c数据库软件,创建了一个叫DB193H1的数据库。
standby主机,操作系统是RedHat 7.8,安装了oracle 19c数据库软件。

1.我们先来看一下vargant中主库需要执行的脚本primary_DB_setup.sh。创建完主库之后,需要修改一些相关参数。同时打开归档,添加STANDBY LOGFILE,并且打开FLASHBACK。由于两边数据文件保持路径一致。因此设置STANDBY_FILE_MANAGEMENT为AUTO即可。这里还把dg_broker_start打开了。

${DB_HOME}/bin/sqlplus / as sysdba 
ALTER SYSTEM SET db_create_file_dest='/u02/oradata';
ALTER SYSTEM SET db_create_online_log_dest_1='/u02/oradata';
ALTER SYSTEM SET local_listener='LISTENER';
ALTER SYSTEM SET db_recovery_file_dest_size=20G;
ALTER SYSTEM SET db_recovery_file_dest='/u01/app/oracle';
exit;


${DB_HOME}/bin/sqlplus / as sysdba 
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;

ALTER DATABASE FORCE LOGGING;
ALTER SYSTEM SWITCH LOGFILE;

ALTER DATABASE ADD STANDBY LOGFILE SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE SIZE 50M;

ALTER DATABASE FLASHBACK ON;

ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
exit;



${DB_HOME}/bin/sqlplus / as sysdba 
ALTER SYSTEM SET dg_broker_start=TRUE;
exit;

2.主库设置完毕之后,就可以配置tns链接串了,这个配置需要在主库和备库均设置。设置如下。

DB193H1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = DB193H1)
    )
  )

DB193H1_STDBY =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = standby)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = DB193H1)
    )
  )

当然还需要我们配置一下静态注册。在监听的listener.ora文件中增加下列配置,这个配置需要在主库和备库均设置

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DB193H1_DGMGRL)
      (DB_HOME = /u01/app/oracle/product/19.3.0.0/dbhome_1)
      (SID_NAME = DB193H1)
    )
  )

3.接下来是备库的设置。我们可以按照standby_DB_setup.sh的脚本来设置。先创建相关文件夹(这里需要注意的是,如果你使用了CDB和PDB,就需要创建上面那pdbseed和pdb相关文件夹,没有则不需要),然后生成密码文件。这里的DB_NAME取名为DB193H1_STDBY

if [ "${CDB}" == "true" ]
then
  mkdir -p /u02/oradata/${DB_NAME}/pdbseed
  mkdir -p /u02/oradata/${DB_NAME}/pdb1
fi

mkdir -p ${DB_BASE}/fast_recovery_area/${DB_NAME}
mkdir -p ${DB_BASE}/admin/${DB_NAME}/adump

orapwd file=$ORACLE_HOME/dbs/orapw${DB_NAME} password=${SYS_PASSWORD} entries=10 format=12

4.上述配置完成后,创建参数文件,启动备库到nomount状态。

cat > /tmp/init_standby.ora 
*.db_name='${DB_NAME}'
*.local_listener='LISTENER'


echo "-----------------------------------------------------------------"
echo -e "${INFO}`date +%F' '%T`: Making auxillary instance"
echo "-----------------------------------------------------------------"
export ORACLE_SID=${DB_NAME}
${DB_HOME}/bin/sqlplus / as sysdba 
--SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT PFILE='/tmp/init_standby.ora';
exit;

5.在主库上执行rman连接到备库实例,执行DUPLICATE操作,这里很简单我们的参数只需要修改一个db_unique_name即可,如果文件路径不一致,就需要在这里设置db_file_name_convert和log_file_name_convert

${DB_HOME}/bin/rman TARGET sys/${SYS_PASSWORD}@${DB_NAME} AUXILIARY sys/${SYS_PASSWORD}@${DB_NAME}_STDBY 
DUPLICATE TARGET DATABASE
  FOR STANDBY
  FROM ACTIVE DATABASE
  DORECOVER
  SPFILE
    SET db_unique_name='${DB_NAME}_STDBY' COMMENT 'Standby'
  NOFILENAMECHECK;
exit;

6.上述操作完成之后就可以进入到Data Guard Broker的配置了。先进入到备库设置参数。

ALTER SYSTEM SET dg_broker_start = true;

7.接下来在主库配置Data Guard Broker

${DB_HOME}/bin/dgmgrl sys/${SYS_PASSWORD}@${DB_NAME} 
CREATE CONFIGURATION db_broker_config AS PRIMARY DATABASE IS ${DB_NAME} CONNECT IDENTIFIER IS ${DB_NAME};
ADD DATABASE ${DB_NAME}_STDBY AS CONNECT IDENTIFIER IS ${DB_NAME}_STDBY MAINTAINED AS PHYSICAL;
ENABLE CONFIGURATION;

我按照这些步骤操作完成之后,执行发现报ORA-16810错误。

[oracle@primary ~]$ dgmgrl sys/welcome1@DB193H1
DGMGRL for Linux: Release 19.0.0.0.0 - Production on Mon Jun 8 12:49:56 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected to "DB193H1"
Connected as SYSDBA.
DGMGRL> 
DGMGRL>   CREATE CONFIGURATION db_broker_config AS PRIMARY DATABASE IS  DB193H1 CONNECT IDENTIFIER IS  DB193H1;
Configuration "db_broker_config" created with primary database "db193h1"
DGMGRL> 
DGMGRL> ADD DATABASE DB193H1_STDBY AS CONNECT IDENTIFIER IS DB193H1_STDBY MAINTAINED AS PHYSICAL;
Database "db193h1_stdby" added
DGMGRL> ENABLE CONFIGURATION;

Enabled.
DGMGRL> DGMGRL> 
DGMGRL> 
DGMGRL> 
DGMGRL> SHOW CONFIGURATION;

Configuration - db_broker_config

  Protection Mode: MaxPerformance
  Members:
  db193h1       - Primary database
    db193h1_stdby - Physical standby database 
      Error: ORA-16810: multiple errors or warnings detected for the member

经过详细查看备库的状态,发现是ORA-16766: Redo Apply is stopped,原来是一直没有启动备库的redo apply。

DGMGRL> show database verbose db193h1_stdby

Database - db193h1_stdby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          (unknown)
  Average Apply Rate: (unknown)
  Active Apply Rate:  (unknown)
  Maximum Apply Rate: (unknown)
  Real Time Query:    OFF
  Instance(s):
    DB193H1

  Database Error(s):
    ORA-16766: Redo Apply is stopped

  Database Warning(s):
    ORA-16854: apply lag could not be determined

  Properties:
    DGConnectIdentifier             = 'db19h1_stdby'
    ObserverConnectIdentifier       = ''
    FastStartFailoverTarget         = ''
    PreferredObserverHosts          = ''
    LogShipping                     = 'ON'
    RedoRoutes                      = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyLagThreshold               = '30'
    TransportLagThreshold           = '30'
    TransportDisconnectedThreshold  = '30'
    ApplyParallel                   = 'AUTO'
    ApplyInstances                  = '0'
    StandbyFileManagement           = ''
    ArchiveLagTarget                = '0'
    LogArchiveMaxProcesses          = '0'
    LogArchiveMinSucceedDest        = '0'
    DataGuardSyncLatency            = '0'
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = ''
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    ArchiveLocation                 = ''
    AlternateLocation               = ''
    StandbyArchiveLocation          = ''
    StandbyAlternateLocation        = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    LogXptStatus                    = '(monitor)'
    SendQEntries                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    HostName                        = 'standby'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=primary)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DB193H1_STDBY_DGMGRL)(INSTANCE_NAME=DB193H1)(SERVER=DEDICATED)))'
    TopWaitEvents                   = '(monitor)'
    SidName                         = '(monitor)'

  Log file locations:
    Alert log               : /u01/app/oracle/diag/rdbms/db193h1_stdby/DB193H1/trace/alert_DB193H1.log
    Data Guard Broker log   : /u01/app/oracle/diag/rdbms/db193h1_stdby/DB193H1/trace/drcDB193H1.log

Database Status:
ERROR

切换到备库,执行应用,再次查看dg broker配置就正常了

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

DGMGRL> SHOW CONFIGURATION;

Configuration - db_broker_config

  Protection Mode: MaxPerformance
  Members:
  db193h1       - Primary database
    db193h1_stdby - Physical standby database 

Fast-Start Failover:  Disabled

Configuration Status:
SUCCESS   (status updated 46 seconds ago)

DGMGRL> SHOW DATABASE db193h1

Database - db193h1

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

Database Status:
SUCCESS

DGMGRL> SHOW DATABASE db193h1_stdby

Database - db193h1_stdby

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 1 second ago)
  Apply Lag:          0 seconds (computed 1 second ago)
  Average Apply Rate: 3.00 KByte/s
  Real Time Query:    ON
  Instance(s):
    DB193H1

Database Status:
SUCCESS

安装顺利成功,接下来我们就可以来研究19c dataguard的新特性了。感兴趣的小伙伴可以自行研究下vargant中的脚本。

分享到: 更多

Post a Comment

Your email is never published nor shared. Required fields are marked *