oracledbsetup.sh,OracleDatabase19c使用DataGuardBroker进行DataGuard物理备库设置

今天准备研究学习一下19c的data guard。由于想快速搭建&＃xff0c;所以选择了vagrant去拖镜像&＃xff0c;但是发现拖下来的东西还是没有实现dg(这里的情况不是很了解&＃xff0c;感觉是执行脚本有个地方报错了)&＃xff0c;不过它里面的脚本我觉得没什么大问题。我自己凑合手动执行脚本搭了一个。

首先看一下先决条件。

primary主机&＃xff0c;操作系统是RedHat 7.8&＃xff0c;安装了oracle 19c数据库软件&＃xff0c;创建了一个叫DB193H1的数据库。

standby主机&＃xff0c;操作系统是RedHat 7.8&＃xff0c;安装了oracle 19c数据库软件。

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

${DB_HOME}/bin/sqlplus / as sysdba

ALTER SYSTEM SET db_create_file_dest&＃61;&＃39;/u02/oradata&＃39;;

ALTER SYSTEM SET db_create_online_log_dest_1&＃61;&＃39;/u02/oradata&＃39;;

ALTER SYSTEM SET local_listener&＃61;&＃39;LISTENER&＃39;;

ALTER SYSTEM SET db_recovery_file_dest_size&＃61;20G;

ALTER SYSTEM SET db_recovery_file_dest&＃61;&＃39;/u01/app/oracle&＃39;;

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&＃61;AUTO;

exit;

${DB_HOME}/bin/sqlplus / as sysdba

ALTER SYSTEM SET dg_broker_start&＃61;TRUE;

exit;

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

DB193H1 &＃61;

(DESCRIPTION &＃61;

(ADDRESS &＃61; (PROTOCOL &＃61; TCP)(HOST &＃61; primary)(PORT &＃61; 1521))

(CONNECT_DATA &＃61;

(SERVER &＃61; DEDICATED)

(SERVICE_NAME &＃61; DB193H1)

)

)

DB193H1_STDBY &＃61;

(DESCRIPTION &＃61;

(ADDRESS_LIST &＃61;

(ADDRESS &＃61; (PROTOCOL &＃61; TCP)(HOST &＃61; standby)(PORT &＃61; 1521))

)

(CONNECT_DATA &＃61;

(SERVER &＃61; DEDICATED)

(SID &＃61; DB193H1)

)

)

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

SID_LIST_LISTENER &＃61;

(SID_LIST &＃61;

(SID_DESC &＃61;

(GLOBAL_DBNAME &＃61; DB193H1_DGMGRL)

(DB_HOME &＃61; /u01/app/oracle/product/19.3.0.0/dbhome_1)

(SID_NAME &＃61; DB193H1)

)

)

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

if [ "${CDB}" &＃61;&＃61; "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&＃61;$ORACLE_HOME/dbs/orapw${DB_NAME} password&＃61;${SYS_PASSWORD} entries&＃61;10 format&＃61;12

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

cat > /tmp/init_standby.ora

*.db_name&＃61;&＃39;${DB_NAME}&＃39;

*.local_listener&＃61;&＃39;LISTENER&＃39;

echo "-----------------------------------------------------------------"

echo -e "${INFO}&＃96;date &＃43;%F&＃39; &＃39;%T&＃96;: Making auxillary instance"

echo "-----------------------------------------------------------------"

export ORACLE_SID&＃61;${DB_NAME}

${DB_HOME}/bin/sqlplus / as sysdba

--SHUTDOWN IMMEDIATE;

STARTUP NOMOUNT PFILE&＃61;&＃39;/tmp/init_standby.ora&＃39;;

exit;

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

${DB_HOME}/bin/rman TARGET sys/${SYS_PASSWORD}&＃64;${DB_NAME} AUXILIARY sys/${SYS_PASSWORD}&＃64;${DB_NAME}_STDBY

DUPLICATE TARGET DATABASE

FOR STANDBY

FROM ACTIVE DATABASE

DORECOVER

SPFILE

SET db_unique_name&＃61;&＃39;${DB_NAME}_STDBY&＃39; COMMENT &＃39;Standby&＃39;

NOFILENAMECHECK;

exit;

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

ALTER SYSTEM SET dg_broker_start &＃61; true;

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

${DB_HOME}/bin/dgmgrl sys/${SYS_PASSWORD}&＃64;${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;

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

[oracle&＃64;primary ~]$ dgmgrl sys/welcome1&＃64;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

经过详细查看备库的状态&＃xff0c;发现是ORA-16766: Redo Apply is stopped&＃xff0c;原来是一直没有启动备库的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 &＃61; &＃39;db19h1_stdby&＃39;

ObserverConnectIdentifier &＃61; &＃39;&＃39;

FastStartFailoverTarget &＃61; &＃39;&＃39;

PreferredObserverHosts &＃61; &＃39;&＃39;

LogShipping &＃61; &＃39;ON&＃39;

RedoRoutes &＃61; &＃39;&＃39;

LogXptMode &＃61; &＃39;ASYNC&＃39;

DelayMins &＃61; &＃39;0&＃39;

Binding &＃61; &＃39;optional&＃39;

MaxFailure &＃61; &＃39;0&＃39;

ReopenSecs &＃61; &＃39;300&＃39;

NetTimeout &＃61; &＃39;30&＃39;

RedoCompression &＃61; &＃39;DISABLE&＃39;

PreferredApplyInstance &＃61; &＃39;&＃39;

ApplyInstanceTimeout &＃61; &＃39;0&＃39;

ApplyLagThreshold &＃61; &＃39;30&＃39;

TransportLagThreshold &＃61; &＃39;30&＃39;

TransportDisconnectedThreshold &＃61; &＃39;30&＃39;

ApplyParallel &＃61; &＃39;AUTO&＃39;

ApplyInstances &＃61; &＃39;0&＃39;

StandbyFileManagement &＃61; &＃39;&＃39;

ArchiveLagTarget &＃61; &＃39;0&＃39;

LogArchiveMaxProcesses &＃61; &＃39;0&＃39;

LogArchiveMinSucceedDest &＃61; &＃39;0&＃39;

DataGuardSyncLatency &＃61; &＃39;0&＃39;

LogArchiveTrace &＃61; &＃39;0&＃39;

LogArchiveFormat &＃61; &＃39;&＃39;

DbFileNameConvert &＃61; &＃39;&＃39;

LogFileNameConvert &＃61; &＃39;&＃39;

ArchiveLocation &＃61; &＃39;&＃39;

AlternateLocation &＃61; &＃39;&＃39;

StandbyArchiveLocation &＃61; &＃39;&＃39;

StandbyAlternateLocation &＃61; &＃39;&＃39;

InconsistentProperties &＃61; &＃39;(monitor)&＃39;

InconsistentLogXptProps &＃61; &＃39;(monitor)&＃39;

LogXptStatus &＃61; &＃39;(monitor)&＃39;

SendQEntries &＃61; &＃39;(monitor)&＃39;

RecvQEntries &＃61; &＃39;(monitor)&＃39;

HostName &＃61; &＃39;standby&＃39;

StaticConnectIdentifier &＃61; &＃39;(DESCRIPTION&＃61;(ADDRESS&＃61;(PROTOCOL&＃61;TCP)(HOST&＃61;primary)(PORT&＃61;1521))(CONNECT_DATA&＃61;(SERVICE_NAME&＃61;DB193H1_STDBY_DGMGRL)(INSTANCE_NAME&＃61;DB193H1)(SERVER&＃61;DEDICATED)))&＃39;

TopWaitEvents &＃61; &＃39;(monitor)&＃39;

SidName &＃61; &＃39;(monitor)&＃39;

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

切换到备库&＃xff0c;执行应用&＃xff0c;再次查看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

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

分享到&＃xff1a;

更多