oracle 12.2.0.1 使用 active dataguard broker 之一 setup

xiaoxiao2021-02-28  22

os: centos 7.4 database:12.2.0.1 + dbf

手动配置好 dataguard 后,发生故障时,需要人工介入,不是太好。broker observer + flashback database 可以实现故障自动切换( fast_start failover )。

规划如下

maser slave $ORACLE_SID orcl orcl db_name: orcl orcl db_unique_name: orclp orcls1

oradb-node1 192.168.56.101 master

安装好了12.2.0.1 的软件,并创建了数据库,环境变量$ORACLE_SID=orcl dataguard 所有节点的 db_name 是相同的,通过 db_unique_name 来区分彼此。

主库name相关

$ORACLE_SID orcl db_name: orcl db_unique_name: orclp net service name: tns_orclp_dgmgrl

修改 db_unique_name

SQL> show parameter db_name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_name string orcl SQL> alter system set db_unique_name='orclp' scope=spfile; System altered.

添加静态监听

$ vi listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1) (SID_NAME = orcl) ) (SID_DESC = (GLOBAL_DBNAME = orclp_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1) (SID_NAME = orcl) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oradb-node1)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )

修改 net service name

$ vi tnsnames.ora tns_orclp_dgmgrl = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oradb-node1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclp_DGMGRL) ) ) tns_orcls1_dgmgrl = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oradb-node2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcls1_DGMGRL) ) )

主库启用 force logging

SQL> alter database force logging; Database altered.

主库启用 archivelog

SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination /u01/app/oracle/product/12.2.0/db_1/dbs/arch Oldest online log sequence 1 Current log sequence 2 SQL> shutdown immediate; SQL> startup mount; SQL> alter database archivelog; SQL> alter database open;

执行 open pdb,确保处于 read write

SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLPDB MOUNTED SQL> alter pluggable database ORCLPDB open; Pluggable database altered. SQL> show pdbs; CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 ORCLPDB READ WRITE NO

主库创建 standby redo logfile

SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID ---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- ---------- 1 1 4 209715200 512 1 NO CURRENT 1572343 2018-07-07 14:47:20 1.8447E+19 0 2 1 2 209715200 512 1 YES INACTIVE 1429048 2018-07-07 11:45:32 1472300 2018-07-07 14:44:53 0 3 1 3 209715200 512 1 YES INACTIVE 1472300 2018-07-07 14:44:53 1572343 2018-07-07 14:47:20 0 SQL> col MEMBER format a40; SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ CON_ID ---------- ------- ------- ---------------------------------------- --- ---------- 3 ONLINE /u01/app/oracle/oradata/orcl/redo03.log NO 0 2 ONLINE /u01/app/oracle/oradata/orcl/redo02.log NO 0 1 ONLINE /u01/app/oracle/oradata/orcl/redo01.log NO 0

最少添加n+1个standby redo logfile,完全可以比n+1多,如下面

alter database add standby logfile '/u01/app/oracle/oradata/standby_redo01.log' size 200M; alter database add standby logfile '/u01/app/oracle/oradata/standby_redo02.log' size 200M; alter database add standby logfile '/u01/app/oracle/oradata/standby_redo03.log' size 200M; alter database add standby logfile '/u01/app/oracle/oradata/standby_redo04.log' size 200M; alter database add standby logfile '/u01/app/oracle/oradata/standby_redo05.log' size 200M;

主库修改 dataguard broker 参数

SQL> show parameter dg_broker_config_file NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ dg_broker_config_file1 string /u01/app/oracle/product/12.2.0/db_1/dbs/dr1orclp.dat dg_broker_config_file2 string /u01/app/oracle/product/12.2.0/db_1/dbs/dr2orclp.dat SQL> alter system set dg_broker_config_file1='/u01/app/oracle/product/12.2.0/db_1/dbs/dr1orcl.dat' scope=spfile; alter system set dg_broker_config_file2='/u01/app/oracle/product/12.2.0/db_1/dbs/dr2orcl.dat' scope=spfile; alter system set dg_broker_start=true scope=spfile; SQL> startup force; SQL> create pfile from spfile;

主库的一些关键参数

*.db_name='orcl' *.db_unique_name='orclp' *.standby_file_management='auto' *.db_file_name_convert='/u01/app/oracle/','/u01/app/oracle/' *.log_file_name_convert='/u01/app/oracle/','/u01/app/oracle/' *.remote_login_passwordfile='EXCLUSIVE' *.dg_broker_config_file1='/u01/app/oracle/product/12.2.0/db_1/dbs/dr1orcl.dat' *.dg_broker_config_file2='/u01/app/oracle/product/12.2.0/db_1/dbs/dr2orcl.dat' *.dg_broker_start=true

oradb-node2 192.168.56.102 physical standby

安装好了12.2.0.1 的软件,不创建数据库,用duplicate命令从master拉过来

备库name相关

$ORACLE_SID orcl db_name: orcl db_unique_name: orcls1 net service name: tns_orcls1_dgmgrl

添加静态监听

$ vi listener.ora SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = orcl) (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1) (SID_NAME = orcl) ) (SID_DESC = (GLOBAL_DBNAME = orcls1_DGMGRL) (ORACLE_HOME = /u01/app/oracle/product/12.2.0/db_1) (SID_NAME = orcl) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oradb-node2)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) )

修改 net service name

$ vi tnsnames.ora tns_orclp_dgmgrl = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oradb-node1)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orclp_DGMGRL) ) ) tns_orcls1_dgmgrl = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = oradb-node2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcls1_DGMGRL) ) )

上面的 listener.ora、tnsnames.ora 和 master端有细微差别。

备库目录相关

参考主库创建必要的目录

$ mkdir -p $ORACLE_BASE/admin/orcl/adump; mkdir -p $ORACLE_BASE/archivelog; mkdir -p $ORACLE_BASE/audit; mkdir -p $ORACLE_BASE/oradata/orcl; mkdir -p $ORACLE_BASE/oradata/orcl/pdbseed; mkdir -p $ORACLE_BASE/oradata/orcl/orclpdb;

备库拷贝master 的 password file、pfile

$ scp oracle@192.168.56.101:/u01/app/oracle/product/12.2.0/db_1/dbs/orapworcl ./ $ scp oracle@192.168.56.101:/u01/app/oracle/product/12.2.0/db_1/dbs/initorcl.ora ./

备库的一些主要参数

*.db_name='orcl' *.db_unique_name='orcls1' *.standby_file_management='auto' *.db_file_name_convert='/u01/app/oracle/','/u01/app/oracle/' *.log_file_name_convert='/u01/app/oracle/','/u01/app/oracle/' *.remote_login_passwordfile='EXCLUSIVE' *.dg_broker_config_file1='/u01/app/oracle/product/12.2.0/db_1/dbs/dr1orcl.dat' *.dg_broker_config_file2='/u01/app/oracle/product/12.2.0/db_1/dbs/dr2orcl.dat' *.dg_broker_start=true

确保备库监听打开,数据库处于 nomount 状态.

$ lsnrctl start SQL> startup nomount;

开始 active duplicate database (force logging,且不应用real-time apply)。

$ rman target sys/oracleoracle@tns_orclp_dgmgrl auxiliary sys/oracleoracle@tns_orcls1_dgmgrl RMAN> duplicate target database for standby from active database nofilenamecheck dorecover; SQL> select open_mode from v$database; OPEN_MODE ------------------------------------------------------------ MOUNTED SQL> alter database open; Database altered.

dataguard broker 设置

在主库、备库或者另外一台独立的机器都可以设置。 本次操作是在备库上。

创建

$ which dgmgrl /u01/app/oracle/product/12.2.0/db_1/bin/dgmgrl $ dgmgrl DGMGRL for Linux: Release 12.2.0.1.0 - Production on Sat Jul 7 18:44:38 2018 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> DGMGRL> connect sys/oracleoracle@tns_orclp_dgmgrl Connected to "orclp" Connected as SYSDBA. DGMGRL> help create; Creates a broker configuration Syntax: CREATE CONFIGURATION <configuration name> [AS] PRIMARY DATABASE IS <database name> CONNECT IDENTIFIER IS <connect identifier>; DGMGRL> create configuration dgconf as primary database is orclp connect identifier is tns_orclp_dgmgrl; Configuration "dgconf" created with primary database "orclp" DGMGRL>

增加备库

DGMGRL> help add Adds a member to the broker configuration Syntax: ADD { RECOVERY_APPLIANCE | DATABASE | FAR_SYNC } <object name> [AS CONNECT IDENTIFIER IS <connect identifier>]; DGMGRL> DGMGRL> add database orcls1 as connect identifier is tns_orcls1_dgmgrl; Database "orcls1" added

启用 configuration

DGMGRL> enable configuration Enabled.

master 节点的 archive 参数查看

log_archive_dest_1 string location=dgarc1 mandatory vali d_for=(online_logfile,all_role s) log_archive_dest_2 string service="tns_orcls1_dgmgrl", A SYNC NOAFFIRM delay=0 optional compression=disable max_failu re=0 max_connections=1 reopen= 300 db_unique_name="orcls1" ne t_timeout=30, valid_for=(onlin e_logfile,all_roles)

show configuration

DGMGRL> show configuration; Configuration - dgconf Protection Mode: MaxPerformance Members: orclp - Primary database orcls1 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 29 seconds ago) DGMGRL> show configuration verbose; Configuration - dgconf Protection Mode: MaxPerformance Members: orclp - Primary database orcls1 - Physical standby database Properties: FastStartFailoverThreshold = '30' OperationTimeout = '30' TraceLevel = 'USER' FastStartFailoverLagLimit = '30' CommunicationTimeout = '180' ObserverReconnect = '0' FastStartFailoverAutoReinstate = 'TRUE' FastStartFailoverPmyShutdown = 'TRUE' BystandersFollowRoleChange = 'ALL' ObserverOverride = 'FALSE' ExternalDestination1 = '' ExternalDestination2 = '' PrimaryLostWriteAction = 'CONTINUE' ConfigurationWideServiceName = 'orcl_CFG' Fast-Start Failover: DISABLED Configuration Status: SUCCESS

show database verbose

DGMGRL> show database verbose orclp; Database - orclp Role: PRIMARY Intended State: TRANSPORT-ON Instance(s): orcl Properties: DGConnectIdentifier = 'tns_orclp_dgmgrl' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' RedoRoutes = '' DelayMins = '0' Binding = 'optional' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyLagThreshold = '30' TransportLagThreshold = '30' TransportDisconnectedThreshold = '30' ApplyParallel = 'AUTO' ApplyInstances = '0' StandbyFileManagement = 'auto' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '4' LogArchiveMinSucceedDest = '1' DataGuardSyncLatency = '0' DbFileNameConvert = '/u01/app/oracle/, /u01/app/oracle/' LogFileNameConvert = '/u01/app/oracle/, /u01/app/oracle/' FastStartFailoverTarget = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' PreferredObserverHosts = '' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oradb-node1)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orclp_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))' StandbyArchiveLocation = '/u01/app/oracle/archivelog' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = '%t_%s_%r.dbf' TopWaitEvents = '(monitor)' Log file locations: Alert log : /u01/app/oracle/diag/rdbms/orclp/orcl/trace/alert_orcl.log Data Guard Broker log : /u01/app/oracle/diag/rdbms/orclp/orcl/trace/drcorcl.log Database Status: SUCCESS DGMGRL> show database verbose orcls1; Database - orcls1 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: 0 Byte/s Active Apply Rate: 0 Byte/s Maximum Apply Rate: 0 Byte/s Real Time Query: OFF Instance(s): orcl Properties: DGConnectIdentifier = 'tns_orcls1_dgmgrl' ObserverConnectIdentifier = '' LogXptMode = 'ASYNC' RedoRoutes = '' DelayMins = '0' Binding = 'optional' MaxFailure = '0' MaxConnections = '1' ReopenSecs = '300' NetTimeout = '30' RedoCompression = 'DISABLE' LogShipping = 'ON' PreferredApplyInstance = '' ApplyInstanceTimeout = '0' ApplyLagThreshold = '30' TransportLagThreshold = '30' TransportDisconnectedThreshold = '30' ApplyParallel = 'AUTO' ApplyInstances = '0' StandbyFileManagement = 'AUTO' ArchiveLagTarget = '0' LogArchiveMaxProcesses = '4' LogArchiveMinSucceedDest = '1' DataGuardSyncLatency = '0' DbFileNameConvert = '/u01/app/oracle/, /u01/app/oracle/' LogFileNameConvert = '/u01/app/oracle/, /u01/app/oracle/' FastStartFailoverTarget = '' InconsistentProperties = '(monitor)' InconsistentLogXptProps = '(monitor)' SendQEntries = '(monitor)' LogXptStatus = '(monitor)' RecvQEntries = '(monitor)' PreferredObserverHosts = '' StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=oradb-node2)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=orcls1_DGMGRL)(INSTANCE_NAME=orcl)(SERVER=DEDICATED)))' StandbyArchiveLocation = '/u01/app/oracle/archivelog' AlternateLocation = '' LogArchiveTrace = '0' LogArchiveFormat = '%t_%s_%r.dbf' TopWaitEvents = '(monitor)' Log file locations: Alert log : /u01/app/oracle/diag/rdbms/orcls1/orcl/trace/alert_orcl.log Data Guard Broker log : /u01/app/oracle/diag/rdbms/orcls1/orcl/trace/drcorcl.log Database Status: SUCCESS

参考: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/high-availability.html

转载请注明原文地址: https://www.6miu.com/read-2650163.html

最新回复(0)