11g “Data Guard” configuration with DGMGRL

11g “Data Guard” configuration with DGMGRL

 

This post describes the commands used to create a Data Guard configuration using the command line dgmgrl interface. This can also be done via the Enterprise Manager Grid Control. We assume that we already have created a physical standby database and the database roles are as follows.

Primary Database: 11gpri

Standby Database: 11gsb

On both Primary as well as Standby database start the Data Guard Broker process

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

System altered.

Edit the listener.ora on both nodes to add a static entry for DGMGRL

This is to prevent the ORA-12154 error which can be observed on startup of the standby database after performing a switchover.

Ensure that the GLOBAL_DBNAME is set to db_unique_name_DGMGRL.db_domain

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC =

(GLOBAL_DBNAME = 11gpri_dgmgrl)

(ORACLE_HOME = /u01/oracle/product/11.1.0/db_1)

(SID_NAME = 11gR2)

)

)

Create the configuration

dgmgrl

DGMGRL> connect sys/xxxx

Connected.

DGMGRL> CREATE CONFIGURATION ’11GDR’

> AS

> PRIMARY DATABASE IS ’11gpri’

> CONNECT IDENTIFIER IS ’11gpri’

> ;

Configuration “11GDR” created with primary database “11gpri”

Add the Standby database to the configuration

DGMGRL> ADD DATABASE

> ’11gsb’

> AS

> CONNECT IDENTIFIER IS ’11gsb’

> ;

Database “11gsb” added

DGMGRL> SHOW CONFIGURATION

Configuration

Name: 11GDR

Enabled: NO

Protection Mode: MaxAvailability

Databases:

11gpri – Primary database

11gsb – Physical standby database

Fast-Start Failover: DISABLED

Current status for “11GDR”:

DISABLED

Enable the configuration

DGMGRL> ENABLE CONFIGURATION

Enabled.

DGMGRL> SHOW CONFIGURATION

Configuration

Name: 11GDR

Enabled: YES

Protection Mode: MaxAvailability

Databases:

11gpri – Primary database

11gsb – Physical standby database

Fast-Start Failover: DISABLED

Current status for “11GDR”:

SUCCESS

View the Standby and Primary database properties

DGMGRL> show database 11gpri

Database

Name: 11gpri

Role: PRIMARY

Enabled: YES

Intended State: TRANSPORT-ON

Instance(s):

11gR2

Current status for “11gpri”:

SUCCESS

DGMGRL> show database 11gsb

Database

Name: 11gsb

Role: PHYSICAL STANDBY

Enabled: YES

Intended State: APPLY-ON

Instance(s):

11gR2

Current status for “11gsb”:

SUCCESS

DGMGRL> show database verbose 11gsb

Database

Name: 11gsb

Role: PHYSICAL STANDBY

Enabled: YES

Intended State: APPLY-ON

Instance(s):

11gR2

Properties:

DGConnectIdentifier = ’11gsb’

ObserverConnectIdentifier = ”

LogXptMode = ‘SYNC’

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 = ‘4’

LogArchiveMinSucceedDest = ‘1’

DbFileNameConvert = ”

LogFileNameConvert = ”

FastStartFailoverTarget = ”

StatusReport = ‘(monitor)’

InconsistentProperties = ‘(monitor)’

InconsistentLogXptProps = ‘(monitor)’

SendQEntries = ‘(monitor)’

LogXptStatus = ‘(monitor)’

RecvQEntries = ‘(monitor)’

HostName = ‘raclinux1’

SidName = ’11gR2′

StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=raclinux1)

(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=11gsb_DGMGRL)(INSTANCE_NAME=11gR2)(SERVER=DEDICATED)))’

StandbyArchiveLocation = ‘USE_DB_RECOVERY_FILE_DEST’

AlternateLocation = ”

LogArchiveTrace = ‘0’

LogArchiveFormat = ‘%t_%s_%r.dbf’

LatestLog = ‘(monitor)’

TopWaitEvents = ‘(monitor)’

Current status for “11gsb”:

SUCCESS

Change the properties of a configured databaase

DGMGRL> EDIT DATABASE ’11gpri’ SET PROPERTY ‘LogXptMode’=’SYNC’;

Property “LogXptMode” updated

Performing a switchover

Note: In this case, currently the Primary Database is 11gsb and the Standby database is 11gpri.

DGMGRL> switchover to ’11gpri’

Performing switchover NOW, please wait…

New primary database “11gpri” is opening…

Operation requires shutdown of instance “11gR2” on database “11gsb”

Shutting down instance “11gR2″…

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

Operation requires startup of instance “11gR2” on database “11gsb”

Starting instance “11gR2″…

ORACLE instance started.

Database mounted.

Switchover succeeded, new primary is “11gpri”

DGMGRL> SHOW CONFIGURATION;

Configuration

Name: 11GDR

Enabled: YES

Protection Mode: MaxAvailability

Databases:

11gpri – Primary database

11gsb – Physical standby database

Fast-Start Failover: DISABLED

Current status for “11GDR”:

SUCCESS

Monitoring the Data Guard Broker Configuration

If we receive any error or warnings we could obtain more information running the commands as shown below. In this case there is no output seen because currently we are not experiencing any errors or warning.

DGMGRL> show database 11gpri statusreport

STATUS REPORT

INSTANCE_NAME SEVERITY ERROR_TEXT

DGMGRL> show database 11gpri logxptstatus

LOG TRANSPORT STATUS

PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME STATUS

11gR2 11gsb

DGMGRL> show database 11gpri InconsistentProperties

INCONSISTENT PROPERTIES

INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE

  

SoormaCredit Gavin