Thinking Out Loud

November 30, 2016

Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)

Filed under: 11g,Dataguard — mdinh @ 1:37 pm

This is a quick and dirty note to self about Doc ID 1387859.1 which may be incorrect for Single Instance Database with Oracle Restart.

Please share if you have configuration for Single Instance Database with Oracle Restart using DataGuard Broker and listener registered from Grid – TIA.

Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)	

+++ DO NOT BELIEVE THIS TO BE CORRECT - FAILED FOR TEST CASE.

Single Instance Database with Oracle Restart

Here there is no cluster, but clusterware has been installed to enable the Oracle Restart feature. 

The local listener LISTENER has its LISTENER.ORA located in the /network/admin directory of the Oracle Grid Infrastructure home. 

In this case the static service registration is:
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
     (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
     (ORACLE_HOME=oracle_home)
     (SID_NAME=sid_name)
    )
  )

As with Single Instance databases, the SID_NAME value sid_name will default to the db_unique_name.

+++ SUCCESS
Data Guard & Oracle Restart in 11gR2 https://uhesse.com/2010/09/06/data-guard-oracle-restart-in-11gr2/

DEMO: Listener configured from DB Home and NOT GI Home which failed.

oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ ll
total 0
-rw-r-----. 1 oracle oinstall 0 Nov 30 05:13 alert_HAWKB.log
-rw-r-----. 1 oracle oinstall 0 Nov 30 05:13 drcHAWKB.log

oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ srvctl config listener -l listener11g
Name: LISTENER11G
Home: /u01/app/oracle/product/11.2.0.4/db_1
End points: TCP:1551

oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ lsnrctl status listener11g

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-NOV-2016 05:14:12

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1551)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER11G
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                30-NOV-2016 05:03:54
Uptime                    0 days 0 hr. 10 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1551)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow2.localdomain)(PORT=1551)))
Services Summary...
Service "HAWKB.localdomain" has 2 instance(s).
  Instance "HAWKB", status UNKNOWN, has 1 handler(s) for this service...
  Instance "HAWKB", status READY, has 1 handler(s) for this service...
Service "HAWKB_DGB.localdomain" has 1 instance(s).
  Instance "HAWKB", status READY, has 1 handler(s) for this service...
Service "HAWKB_DGMGRL.localdomain" has 1 instance(s).
  Instance "HAWKB", status UNKNOWN, has 1 handler(s) for this service...
Service "HAWKB_SVC.localdomain" has 1 instance(s).
  Instance "HAWKB", status READY, has 1 handler(s) for this service...
Service "HAWK_SVC.localdomain" has 1 instance(s).
  Instance "HAWKB", status READY, has 1 handler(s) for this service...
The command completed successfully

oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ crs_stat
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER11G.lsnr
               ONLINE  ONLINE       arrow2                   STABLE
ora.ons
               OFFLINE OFFLINE      arrow2                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE                               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       arrow2                   STABLE
ora.hawkb.db
      1        ONLINE  ONLINE       arrow2                   Open,STABLE
ora.hawkb.hawk_svc.svc
      1        ONLINE  ONLINE       arrow2                   STABLE
--------------------------------------------------------------------------------

oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ dgmgrl sys/oracle@hawka
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.
Connected.
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawkb - Primary database
    hawka - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawkb

Database - hawkb

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

Database Status:
SUCCESS

DGMGRL> show database hawka

Database - hawka

  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):
    HAWKA

Database Status:
SUCCESS

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

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawka - Primary database
    hawkb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawka

Database - hawka

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

Database Status:
SUCCESS

DGMGRL> show database hawkb

Database - hawkb

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

Database Status:
SUCCESS

DGMGRL> exit

oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ crs_stat
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER11G.lsnr
               ONLINE  ONLINE       arrow2                   STABLE
ora.ons
               OFFLINE OFFLINE      arrow2                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE                               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       arrow2                   STABLE
ora.hawkb.db
      1        ONLINE  ONLINE       arrow2                   Open,Readonly,STABLE
ora.hawkb.hawk_svc.svc
      1        ONLINE  ONLINE       arrow2                   STABLE
--------------------------------------------------------------------------------
oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$

++++++++++

oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$ ll
total 0
-rw-r--r--. 1 oracle oinstall 0 Nov 30 05:12 alert_HAWKA.log
-rw-r-----. 1 oracle oinstall 0 Nov 30 05:12 drcHAWKA.log

oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$ srvctl config listener -l listener11g
Name: LISTENER11G
Home: /u01/app/oracle/product/11.2.0.4/db_1
End points: TCP:1551

oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$ lsnrctl status listener11g

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-NOV-2016 05:14:39

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1551)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER11G
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                30-NOV-2016 05:04:43
Uptime                    0 days 0 hr. 9 min. 55 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1551)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow1.localdomain)(PORT=1551)))
Services Summary...
Service "HAWKA.localdomain" has 2 instance(s).
  Instance "HAWKA", status UNKNOWN, has 1 handler(s) for this service...
  Instance "HAWKA", status READY, has 1 handler(s) for this service...
Service "HAWKA_DGB.localdomain" has 1 instance(s).
  Instance "HAWKA", status READY, has 1 handler(s) for this service...
Service "HAWKA_DGMGRL.localdomain" has 1 instance(s).
  Instance "HAWKA", status UNKNOWN, has 1 handler(s) for this service...
Service "HAWKA_SVC.localdomain" has 1 instance(s).
  Instance "HAWKA", status READY, has 1 handler(s) for this service...
Service "HAWK_SVC.localdomain" has 1 instance(s).
  Instance "HAWKA", status READY, has 1 handler(s) for this service...
The command completed successfully

oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$ crs_stat
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER11G.lsnr
               ONLINE  ONLINE       arrow1                   STABLE
ora.ons
               OFFLINE OFFLINE      arrow1                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE                               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       arrow1                   STABLE
ora.hawka.db
      1        ONLINE  ONLINE       arrow1                   Open,Readonly,STABLE
ora.hawka.hawk_svc.svc
      1        ONLINE  ONLINE       arrow1                   STABLE
--------------------------------------------------------------------------------

oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$ crs_stat
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER11G.lsnr
               ONLINE  ONLINE       arrow1                   STABLE
ora.ons
               OFFLINE OFFLINE      arrow1                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE                               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       arrow1                   STABLE
ora.hawka.db
      1        ONLINE  ONLINE       arrow1                   Open,STABLE
ora.hawka.hawk_svc.svc
      1        ONLINE  ONLINE       arrow1                   STABLE
--------------------------------------------------------------------------------
oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$

Advertisements

2 Comments »

  1. Hi,
    I have the exact same configuration at one of my customers. They use ASM so GI is installed (but it’s not RAC) and we use DG broker. We disabled dynamic registration for security reasons.
    It was a bit tricky to understand what entries we need to have, but this is the result:
    We use the listener from the GI home.
    We have only one database in the environment
    We added 3 static entries to the listener.ora, in all of them the SID_NAME is the DB unique name, but the global_dbname is different: one with DB_UNIQUE_NAME, one with DB_UNIQUE_NAME_DGMGRL and one with DB_UNIQUE_NAME_DGB. Apparently the dgmgrl utility and the broker use different service names, so you have to add both.
    I hope this helps.

    Comment by amitzil — December 1, 2016 @ 11:35 pm | Reply

  2. […] Data Guard Broker and Static Service Registration (Doc ID […]

    Pingback by Log Buffer #496: A Carnival of the Vanities for DBAs | Official Pythian® Blog – Cloud Data Architect — December 8, 2016 @ 10:37 am | Reply


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: