Oracle Database Commands and Practical Examples: Dataguard 12c: Configure Dataguard on physical standby 12c

Data guard Configuration 12.1.0.2.0 Release

Configure the Broker in a Data Guard configuration

Create physical standby and tnsping both machine.

Create necessary directories for DG_BROKER_CONFIG_FILE

(In our case we mentioned $ORACLE_HOME/dbs localtion )

• Set Broker configuration file parameters

NOTE : 2 DG_BROKER_CONFIG_FILE is for multiplexing

(DG_BROKER_CONFIG_FILE1, DG_BROKER_CONFIG_FILE2)

• Set up the static listener entries on all databases

• Start the Broker processes on all the databases

(DG_BROKER_START=TRUE)

• Start DGMGRL and connect to the primary database

(dgmgrl sys/type_sys_pwd@db_service_name)

• Create the base configuration

DGMGRL> CREATE CONFIGURATION zabbix AS PRIMARY DATABASE IS zabbix CONNECT IDENTIFIER IS zabbix;

• Add the standby database to the Broker configuration

DGMGRL> ADD DATABASE zabbixdr AS CONNECT IDENTIFIER IS zabbixdr;

• Set the database properties

SHOW DATABASE VERBOSE Primary or database name;

• Enable the configuration

DGMGRL> ENABLE CONFIGURATION;
• Verify the configuration

DGMGRL> SHOW CONFIGURATION;

Step 1: Set Broker configuration file parameters (DG_BROKER_CONFIG_FILE1, DG_BROKER_CONFIG_FILE2)

[oracle@srv3 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Feb 17 20:10:08 2021

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> sho pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 PDB1 READ WRITE NO

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='/u01/app/oracle/product/12.1.0.2/db_1/dbs/pr1ORADB.dat';

System altered.

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='/u01/app/oracle/product/12.1.0.2/db_1/dbs/pr2ORADB.dat';

System altered.

SQL> exit

Step 2: Start the Broker processes on all the databases

SQL> SHOW PARAMETER DG_BROKER_START

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

dg_broker_start boolean FALSE

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

System altered.

Step 3:

SQL> alter system set LOG_ARCHIVE_DEST_2='' SCOPE=BOTH sid='*';

System altered.

SQL> exit

Step 3: Connect standby database Configure DG broker config file

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE1='/u01/app/oracle/product/12.1.0.2/db_1/dbs/dr1ORADB.dat';

System altered.

SQL> ALTER SYSTEM SET DG_BROKER_CONFIG_FILE2='/u01/app/oracle/product/12.1.0.2/db_1/dbs/dr2ORADB.dat';

System altered.

SQL> SHOW PARAMETER DG_BROKER_START

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

dg_broker_start boolean FALSE

SQL> ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_2='' SCOPE=BOTH sid='*';

System altered.

Step4 : Start DGMGRL and connect to the primary database

[oracle@srv3 admin]$ dgmgrl sys/system123@zabbix

DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Welcome to DGMGRL, type "help" for information.

Connected as SYSDBA.

DGMGRL> show configuration

ORA-16532: Oracle Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL

#####################################################

STEP 5 : Create the base configuration on primary db host

DGMGRL> CREATE CONFIGURATION zabbix AS PRIMARY DATABASE IS zabbix CONNECT IDENTIFIER IS zabbix;

Configuration "zabbix" created with primary database "zabbix"

DGMGRL> show configuration

Configuration - zabbix

Protection Mode: MaxPerformance

Members:

zabbix - Primary database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

Step 6 : Add Standby database in DGMGRL configuration on primary host.

DGMGRL> ADD DATABASE zabbixdr AS CONNECT IDENTIFIER IS zabbixdr;

Database "zabbixdr" added

DGMGRL> SHOW CONFIGURATION;

Configuration - zabbix

Protection Mode: MaxPerformance

Members:

zabbix - Primary database

zabbixdr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

DISABLED

STEP 7 : Enable configuration on primary database

DGMGRL> ENABLE CONFIGURATION;

Enabled.

DGMGRL> SHOW CONFIGURATION;

Configuration - zabbix

Protection Mode: MaxPerformance

Members:

zabbix - Primary database

zabbixdr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS (status updated 17 seconds ago)

DGMGRL>

STEP 7: Check standby database as well on DR database

[oracle@srv4 zabbixdr]$ dgmgrl sys/system123@zabbixdr

DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

Welcome to DGMGRL, type "help" for information.

Connected as SYSDBA.

DGMGRL> show configuration

Configuration - zabbix

Protection Mode: MaxPerformance

Members:

zabbix - Primary database

zabbixdr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS (status updated 15 seconds ago)

DGMGRL>

##################################################################

DGMGRL> SHOW DATABASE VERBOSE zabbix;

Database - zabbix

Role: PRIMARY

Intended State: TRANSPORT-ON

Instance(s):

zabbix

Properties:

DGConnectIdentifier = 'zabbix'

ObserverConnectIdentifier = ''

LogXptMode = 'ASYNC'

RedoRoutes = ''

DelayMins = '0'

Binding = 'optional'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '30'

RedoCompression = 'DISABLE'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyLagThreshold = '0'

TransportLagThreshold = '0'

TransportDisconnectedThreshold = '30'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'AUTO'

ArchiveLagTarget = '1800'

LogArchiveMaxProcesses = '4'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = '/u01/app/oracle/oradata/zabbixdr/, /u01/app/oracle/oradata/zabbix/'

LogFileNameConvert = '/u01/app/oracle/oradata/zabbixdr/, /u01/app/oracle/oradata/zabbix/'

FastStartFailoverTarget = ''

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv3.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=zabbix_DGMGRL)(INSTANCE_NAME=zabbix)(SERVER=DEDICATED)))'

StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = '%t_%s_%r.dbf'

TopWaitEvents = '(monitor)'

Database Status:

SUCCESS

DGMGRL>

#############################################################################

DGMGRL> SHOW DATABASE VERBOSE zabbixdr;

Database - zabbixdr

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: 1.00 KByte/s

Active Apply Rate: 2.37 MByte/s

Maximum Apply Rate: 2.38 MByte/s

Real Time Query: OFF

Instance(s):

zabbixdr

Properties:

DGConnectIdentifier = 'zabbixdr'

ObserverConnectIdentifier = ''

LogXptMode = 'ASYNC'

RedoRoutes = ''

DelayMins = '0'

Binding = 'optional'

MaxFailure = '0'

MaxConnections = '1'

ReopenSecs = '300'

NetTimeout = '30'

RedoCompression = 'DISABLE'

LogShipping = 'ON'

PreferredApplyInstance = ''

ApplyInstanceTimeout = '0'

ApplyLagThreshold = '0'

TransportLagThreshold = '0'

TransportDisconnectedThreshold = '30'

ApplyParallel = 'AUTO'

StandbyFileManagement = 'AUTO'

ArchiveLagTarget = '1800'

LogArchiveMaxProcesses = '4'

LogArchiveMinSucceedDest = '1'

DbFileNameConvert = '/u01/app/oracle/oradata/zabbix/, /u01/app/oracle/oradata/zabbixdr/'

LogFileNameConvert = '/u01/app/oracle/oradata/zabbix/, /u01/app/oracle/oradata/zabbixdr/'

FastStartFailoverTarget = ''

InconsistentProperties = '(monitor)'

InconsistentLogXptProps = '(monitor)'

SendQEntries = '(monitor)'

LogXptStatus = '(monitor)'

RecvQEntries = '(monitor)'

StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=srv4.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=zabbixdr_DGMGRL)(INSTANCE_NAME=zabbixdr)(SERVER=DEDICATED)))'

StandbyArchiveLocation = 'USE_DB_RECOVERY_FILE_DEST'

AlternateLocation = ''

LogArchiveTrace = '0'

LogArchiveFormat = '%t_%s_%r.dbf'

TopWaitEvents = '(monitor)'

Database Status:

SUCCESS

DGMGRL>

##########################################

STEP 8 : Shutdown DG broker configuration to stop MRP process in standby

DGMGRL> EDIT DATABASE zabbixdr SET STATE=APPLY-OFF;

Succeeded.

DGMGRL> show configuration;

Configuration - zabbix

Protection Mode: MaxPerformance

Members:

zabbix - Primary database

zabbixdr - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:

SUCCESS (status updated 3 seconds ago)

DGMGRL>

Oracle Database Commands and Practical Examples

HOME

Wednesday, 10 March 2021

Dataguard 12c: Configure Dataguard on physical standby 12c

No comments:

Post a Comment