Thinking Out Loud

January 16, 2016

Validate DG Broker Config for Switchover

Filed under: 11g,Dataguard,oracle — mdinh @ 6:08 pm

Primary and Standby databases are running on the same server using OMF with listening on port 1530/1531

Note I have – TraceLevel = ‘SUPPORT’

+++ Check listener for DGMGRL service from PRIMARY and STANDBY.

oracle@arrow:hawksan:/media/sf_working/dataguard
$ lsnrctl status listener_las|grep DG -A 1
Service "hawklas_DGB" has 1 instance(s).
  Instance "hawklas", status READY, has 1 handler(s) for this service...
Service "hawklas_DGMGRL" has 1 instance(s).
  Instance "hawklas", status UNKNOWN, has 1 handler(s) for this service...

oracle@arrow:hawksan:/media/sf_working/dataguard
$ lsnrctl status listener_san|grep DG -A 1
Service "hawksan_DGB" has 1 instance(s).
  Instance "hawksan", status READY, has 1 handler(s) for this service...
Service "hawksan_DGMGRL" has 1 instance(s).
  Instance "hawksan", status UNKNOWN, has 1 handler(s) for this service...

Get into habit of using instance versus database where applicable for RAC compatibility.

DGMGRL> show database hawklas

Database - hawklas

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

Database Status:
SUCCESS

DGMGRL> show database hawklas DGConnectIdentifier
  DGConnectIdentifier = 'hawklas'
DGMGRL> show instance hawklas DGConnectIdentifier
  DGConnectIdentifier = 'hawklas'
DGMGRL>

+++ Check DG Configuration

oracle@arrow:hawklas:/media/sf_working/dataguard
$ ./check_dg.sh
***** Checking Data Guard Broker Configuration ....
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
Connected.
DGMGRL> show configuration verbose

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawklas - Primary database
    hawksan - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show configuration TraceLevel
  TraceLevel = 'SUPPORT'
DGMGRL> show database hawklas

Database - hawklas

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

Database Status:
SUCCESS

DGMGRL> show database hawksan

Database - hawksan

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      45.00 KByte/s
  Real Time Query: ON
  Instance(s):
    hawksan

Database Status:
SUCCESS

DGMGRL> show instance hawklas DGConnectIdentifier
  DGConnectIdentifier = 'hawklas'
DGMGRL> show instance hawksan DGConnectIdentifier
  DGConnectIdentifier = 'hawksan'
DGMGRL> show instance hawklas StaticConnectIdentifier
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=arrow)(PORT=1530))(CONNECT_DATA=(SERVICE_NAME=hawklas_DGMGRL)(INSTANCE_NAME=hawklas)(SERVER=DEDICATED)))'
DGMGRL> show instance hawksan StaticConnectIdentifier
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=arrow)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=hawksan_DGMGRL)(INSTANCE_NAME=hawksan)(SERVER=DEDICATED)))'
DGMGRL> show instance hawklas InconsistentProperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

DGMGRL> show instance hawksan InconsistentProperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

DGMGRL> show instance hawklas LogArchiveMaxProcesses
  LogArchiveMaxProcesses = '4'
DGMGRL> show instance hawksan LogArchiveMaxProcesses
  LogArchiveMaxProcesses = '4'
DGMGRL> show instance hawklas DelayMins
  DelayMins = '0'
DGMGRL> show instance hawksan DelayMins
  DelayMins = '0'
DGMGRL> show instance hawklas LogArchiveTrace
  LogArchiveTrace = '0'
DGMGRL> show instance hawksan LogArchiveTrace
  LogArchiveTrace = '0'
DGMGRL> show instance hawklas statusreport
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL> show instance hawksan statusreport
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL> exit
oracle@arrow:hawklas:/media/sf_working/dataguard
$

+++ Test connectivity to database using StaticConnectIdentifier from DG Broker

oracle@arrow:hawksan:/media/sf_working/dataguard
$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 16 08:10:31 2016

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

@> connect sys/oracle@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=arrow)(PORT=1530))(CONNECT_DATA=(SERVICE_NAME=hawklas_DGMGRL)(INSTANCE_NAME=hawklas)(SERVER=DEDICATED)))' as sysdba
Connected.
ARROW:(SYS@hawklas):PRIMARY> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      hawk
db_unique_name                       string      hawklas
global_names                         boolean     FALSE
instance_name                        string      hawklas
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                        string      hawk,hawklas
ARROW:(SYS@hawklas):PRIMARY> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           PRIMARY

ARROW:(SYS@hawklas):PRIMARY> connect sys/oracle@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=arrow)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=hawksan_DGMGRL)(INSTANCE_NAME=hawksan)(SERVER=DEDICATED)))' as sysdba
Connected.
ARROW:(SYS@hawksan):PHYSICAL STANDBY> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      hawk
db_unique_name                       string      hawksan
global_names                         boolean     FALSE
instance_name                        string      hawksan
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                        string      hawk,hawksan
ARROW:(SYS@hawksan):PHYSICAL STANDBY> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY

ARROW:(SYS@hawksan):PHYSICAL STANDBY>

+++ switchover to hawksan (STANDBY)

Must connect as sys@tns (typically same as DGConnectIdentifier) for switchover.

oracle@arrow:hawklas:/media/sf_working/dataguard
$ ./clearlog.sh
oracle@arrow:hawklas:/media/sf_working/dataguard
$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@hawklas
Connected.
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawklas - Primary database
    hawksan - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawklas

Database - hawklas

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

Database Status:
SUCCESS

DGMGRL> show database hawksan

Database - hawksan

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

Database Status:
SUCCESS

DGMGRL> switchover to hawksan
Performing switchover NOW, please wait...
Operation requires a connection to instance "hawksan" on database "hawksan"
Connecting to instance "hawksan"...
Connected.
New primary database "hawksan" is opening...
Operation requires startup of instance "hawklas" on database "hawklas"
Starting instance "hawklas"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "hawksan"
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawksan - Primary database
    hawklas - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawksan

Database - hawksan

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

Database Status:
SUCCESS

DGMGRL> show database hawklas

Database - hawklas

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

Database Status:
SUCCESS

DGMGRL> exit

++++ Save logs for reference

oracle@arrow:hawklas:/media/sf_working/dataguard
$ ./savelog.sh
`/u01/app/oracle/product/11.2.0/dbhome_1/network/log/listener_las.log' -> `/tmp/listener_las.log'
`/u01/app/oracle/product/11.2.0/dbhome_1/network/log/listener_san.log' -> `/tmp/listener_san.log'
`/u01/app/oracle/diag/rdbms/hawklas/hawklas/trace/alert_hawklas.log' -> `/tmp/alert_hawklas.log'
`/u01/app/oracle/diag/rdbms/hawklas/hawklas/trace/drchawklas.log' -> `/tmp/drchawklas.log'
`/u01/app/oracle/diag/rdbms/hawksan/hawksan/trace/alert_hawksan.log' -> `/tmp/alert_hawksan.log'
`/u01/app/oracle/diag/rdbms/hawksan/hawksan/trace/drchawksan.log' -> `/tmp/drchawksan.log'

+++ switchover to hawklas (STANDBY)

Must connect as sys@tns (typically same as DGConnectIdentifier) for switchover.

oracle@arrow:hawklas:/media/sf_working/dataguard
$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@hawklas
Connected.
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawksan - Primary database
    hawklas - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> switchover to hawklas
Performing switchover NOW, please wait...
New primary database "hawklas" is opening...
Operation requires startup of instance "hawksan" on database "hawksan"
Starting instance "hawksan"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "hawklas"
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawklas - Primary database
    hawksan - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawklas

Database - hawklas

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

Database Status:
SUCCESS

DGMGRL> show database hawksan

Database - hawksan

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

Database Status:
SUCCESS

DGMGRL> exit
oracle@arrow:hawklas:/media/sf_working/dataguard
$
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: