DGMGRL Setup for RAC to Non-RAC

Data Guard Broker helps to automate many of the tasks like switch-over , fail-over etc.

I would like to demonstrate configuration of oracle data guard broker and would like to show how easy to switch-over between primary and standby databases with single line command.

In below example, I have primary database on cluster environment 2 Node RAC and standby database is non-cluster environment. Moreover, Primary database on ASM storage whereas standby database is on file-system.

======
Step  1:  Configure the static registration of SERVICES in the LOCAL LISTENER
======

Note: Here, the primary database is on cluster environment. So the local listener is with GRID user. After making the appropriate changes in the listener.ora file we need to reload the listener.

~~~~~~~~~~~~~~~~~~~
PRIMARY DATABASE
~~~~~~~~~~~~~~~~~~~

Node 1:  racdb1

Before Change:

[grid@racdb1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))           # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

After Change:

Note: Services pocsec_DGB , pocpri_DBG are needed by DGMGRL tool internally.
If you dont create this services then also dgmgrl will automatically register them
It is always better to define all required services.

[grid@racdb1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))           # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=PLSExtProc)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
      (PROGRAM=extproc)
         )
        (SID_DESC=
      (GLOBAL_DBNAME=pocpri_DGMGRL)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME=pocpri1)
         )
        (SID_DESC=
      (GLOBAL_DBNAME=pocsec_DGMGRL)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME=pocsec)
         )
        (SID_DESC=
      (GLOBAL_DBNAME=pocsec_DGB)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME=pocsec)
         )
        (SID_DESC=
      (GLOBAL_DBNAME=pocpri_DGB)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME=pocpri1)
         )
        (SID_DESC=
      (GLOBAL_DBNAME=pocpri)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME=pocpri1)
         )
   )

Reload the listener after changes.

Node 2:  racdb2

Before Change:

[grid@racdb2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))           # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent

After Change:

[grid@racdb2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/11.2.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))           # line added by Agent
LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1))))                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON                # line added by Agent
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (SID_NAME=PLSExtProc)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
      (PROGRAM=extproc)
         )
        (SID_DESC=
      (GLOBAL_DBNAME=pocpri_DGMGRL)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME=pocpri2)
         )
        (SID_DESC=
      (GLOBAL_DBNAME=pocpri_DGB)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME=pocpri2)
         )
        (SID_DESC=
      (GLOBAL_DBNAME=pocsec_DGMGRL)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME=pocsec)
         )
        (SID_DESC=
      (GLOBAL_DBNAME=pocsec_DGB)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME=pocsec)
         )
        (SID_DESC=
      (GLOBAL_DBNAME=pocpri)
      (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME=pocpri2)
         )
   )

Reload the listener after changes.

~~~~~~~~~~~~~~~~~~~
STANDBY DATABASE
~~~~~~~~~~~~~~~~~~~

Note: Here, the standby database is on non-cluster environment so the local listener is with ORACLE user only.

[oracle11g@stndb admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))
    )
)
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = pocpri_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = pocpri1)
     )
    (SID_DESC =
      (GLOBAL_DBNAME = pocpri_DGMGRL)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = pocpri2)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = pocsec_DGMGRL)
      (ORACLE_HOME =/u01/app/oracle/product/11.2.0/db_1)
      (SID_NAME = pocsec)
    )
  )

Reload the listener after changes.

======
Step  2:   Add the entries in the tnsnames.ora file for each node
======

Note: We need to add entries that connects to each node using VIP. We need to add entries for standby database as well using public host name/ip.

~~~~~~~~~~~~~~~~~~~
PRIMARY DATABASE
~~~~~~~~~~~~~~~~~~~

NODE 1:  racdb1

After Change:

[oracle@racdb1 admin]$ cat tnsnames.ora
# tnsnames.ora.racdb1 Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora.racdb2
# Generated by Oracle configuration tools.

POCSEC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pocsec)
    )
  )

POCPRI1_SERV =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 11.11.11.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pocpri)
      (INSTANCE_NAME =pocpri1)
    )
  )

POCPRI2_SERV =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 11.11.11.12)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pocpri)
      (INSTANCE_NAME =pocpri2)
    )
  )

# Below service is instance specific using VIP.
POCPRI =
  (DESCRIPTION =
      (ADDRESS= (PROTOCOL = TCP)(HOST = 11.11.11.11)(PORT = 1521))
   (CONNECT_DATA=
      (SERVICE_NAME = pocpri)
      (INSTANCE_NAME =pocpri1)
   )
  )

Node 2:  racdb2

After Change:

[oracle@racdb2 admin]$ cat tnsnames.ora
# tnsnames.ora.racdb2 Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora.racdb2
# Generated by Oracle configuration tools.
POCSEC =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pocsec)
    )
  )

POCPRI1_SERV =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 11.11.11.11)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pocpri)
      (INSTANCE_NAME =pocpri1)
    )
  )

POCPRI2_SERV =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 11.11.11.12)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pocpri)
      (INSTANCE_NAME =pocpri2)
    )
  )

POCPRI =
  (DESCRIPTION =
      (ADDRESS= (PROTOCOL = TCP)(HOST = 11.11.11.12)(PORT = 1521))
   (CONNECT_DATA=
      (SERVICE_NAME = pocpri)
      (INSTANCE_NAME =pocpri2)
   )
  )

~~~~~~~~~~~~~~~~~~~
STANDBY DATABASE
~~~~~~~~~~~~~~~~~~~

[oracle11g@stndb admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

POCPRI1_SERV =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 11.11.11.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pocpri)
      (INSTANCE_NAME = pocpri1)
    )
  )

POCPRI2_SERV =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 11.11.11.12)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pocpri)
      (INSTANCE_NAME = pocpri2)
    )
  )

POCPRI =
  (DESCRIPTION =
      (ADDRESS= (PROTOCOL = TCP)(HOST = 11.11.11.20)(PORT = 1521))  ---> Here we need to have SCAN NAME/IP
   (CONNECT_DATA=
      (SERVICE_NAME = pocpri)
   )
  )

POCSEC =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.10)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pocsec)
    )
  )

======
Step  3:   Create the password file and copy the same file on each servers.
======

Note:  Password file must be created once on any node from primary database. Then copy the same file on another nodes along with standby database.

~~~~~~~~~~~~~~~~~~~
PRIMARY DATABASE
~~~~~~~~~~~~~~~~~~~
Node 1:

cd $ORACLE_HOME/dbs
orapwd file=orapwpocpri1 password=xxxxxx ignorecase=y force=y

[oracle@racdb1 dbs]$ ls -lrt orapw*
-rw-r----- 1 oracle oinstall 1536 Aug  2 18:10 orapwpocpri1

[oracle@racdb2 dbs]$ ls -lrt orapw*
-rw-r----- 1 oracle oinstall 1536 Aug  2 18:12 orapwpocpri2

[oracle11g@stndb dbs]$ ls -lrt orapw*
-rw-r----- 1 oracle11g oinstall 1536 Aug  2 18:13 orapwpocsec

Copy the same file on another nodes and change the filename with appropriate SID.

Copy the same file to Standby Database also and change the SID accordingly.

======
Step  4:   Change the parameter dg_broker_config_file1 and dg_broker_config_file2
======

~~~~~~~~~~~~~~~~~~~
PRIMARY DATABASE
~~~~~~~~~~~~~~~~~~~

sql> alter system set dg_broker_config_file1='+DATA/pocpri/dr1pocpri.dat' scope=both sid='*';
sql> alter system set dg_broker_config_file2='+DATA/pocpri/dr2pocpri.dat' scope=both sid='*';

~~~~~~~~~~~~~~~~~~~
STANDBY DATABASE
~~~~~~~~~~~~~~~~~~~
Note: On Standby database both the files needs to be located under the $ORACLE_HOME/dbs

sql> alter system set dg_broker_config_file1='/u01/app/oracle/product/11.2.0/db_1/dbs/dr1pocpri.dat' scope=both ;
sql> alter system set dg_broker_config_file2='/u01/app/oracle/product/11.2.0/db_1/dbs/dr2pocpri.dat' scope=both ;

 

======
Step  5:   Enable DG BROKER on both PRIMARY and STANDBY databases.
======

~~~~~~~~~~~~~~~~~~~
PRIMARY DATABASE
~~~~~~~~~~~~~~~~~~~

sql> alter system set dg_broker_start=TRUE scope=both sid='*';

~~~~~~~~~~~~~~~~~~~
STANDBY DATABASE
~~~~~~~~~~~~~~~~~~~

sql> alter system set dg_broker_start=TRUE scope=both ;

 

======
Step  6:   Configure the DGMGRL tool for both PRIMARY and STANDBY databases.
======

~~~~~~~~~~~~~~~~~~~
PRIMARY DATABASE
~~~~~~~~~~~~~~~~~~~

[oracle@racdb2 ~]$ dgmgrl
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

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

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/xxxxxx@pocpri
Connected.
DGMGRL>
DGMGRL> SHOW CONFIGURATION;
ORA-16532: Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL
DGMGRL> 
DGMGRL> create configuration 'POCRACDG' as primary database is 'pocpri' connect identifier is pocpri;
Configuration "POCRACDG" created with primary database "pocpri"
DGMGRL> 
DGMGRL> add database 'pocsec' as connect identifier is pocsec;
Database "pocsec" added
DGMGRL> 
DGMGRL> show configuration;

Configuration - POCRACDG

  Protection Mode: MaxPerformance
  Databases:
    pocpri - Primary database
    pocsec - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL>

======
Step  7:   Check the properties of each instances in the configuraion and change them accordingly.
======

DGMGRL>
DGMGRL> show database verbose pocpri;

Database - pocpri

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

  Properties:
    DGConnectIdentifier             = 'pocpri'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    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               = '/u02/oradata/pocsec/, +DATA/pocpri/'
    LogFileNameConvert              = '/u02/oradata/pocsec/, +DATA/pocpri/'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName(*)
    StaticConnectIdentifier(*)
    StandbyArchiveLocation(*)
    AlternateLocation(*)
    LogArchiveTrace(*)
    LogArchiveFormat(*)
    TopWaitEvents(*)
    (*) - Please check specific instance for the property value

Database Status:
SUCCESS

DGMGRL> edit instance pocpri1 on database pocpri set PROPERTY StaticConnectIdentifier='pocpri';   --> this pocpri name must be there in TNSNAMES.ora
DGMGRL> edit instance pocpri2 on database pocpri set PROPERTY StaticConnectIdentifier='pocpri';

Note: We need to verify "StaticConnectIdentifier" of each instances. 
      We need to change the value according to TNSNAMES.ora file.

DGMGRL> show instance verbose pocpri1;

Instance 'pocpri1' of database 'pocpri'

  Host Name: racdb1
  PFILE:
  Properties:
    SidName                         = 'pocpri1'
    StaticConnectIdentifier         = 'pocpri'
    StandbyArchiveLocation          = '+DATA'
    AlternateLocation               = ''
    LogArchiveTrace                 = '8191'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

DGMGRL>
DGMGRL> show instance verbose pocpri2;

Instance 'pocpri2' of database 'pocpri'

  Host Name: racdb2
  PFILE:
  Properties:
    SidName                         = 'pocpri2'
    StaticConnectIdentifier         = 'pocpri'
    StandbyArchiveLocation          = '+DATA'
    AlternateLocation               = ''
    LogArchiveTrace                 = '8191'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

======
Step  8:   Enable the configuration.
======

DGMGRL> enable configuration;
Enabled.
DGMGRL> show configuration;

Configuration - POCRACDG

  Protection Mode: MaxPerformance
  Databases:
    pocpri - Primary database
    pocsec - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS               

DGMGRL>

 


Switch-over


DGMGRL> switchover to pocsec;
Performing switchover NOW, please wait...
New primary database "pocsec" is opening...
Operation requires shutdown of instance "pocpri1" on database "pocpri"
Shutting down instance "pocpri1"...
ORACLE instance shut down.
Operation requires startup of instance "pocpri1" on database "pocpri"
Starting instance "pocpri1"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "pocsec"
DGMGRL>

DGMGRL> switchover to pocpri;
Performing switchover NOW, please wait...
New primary database "pocpri" is opening...
Operation requires shutdown of instance "pocsec" on database "pocsec"
Shutting down instance "pocsec"...
ORACLE instance shut down.
Operation requires startup of instance "pocsec" on database "pocsec"
Starting instance "pocsec"...
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is "pocpri"
DGMGRL>
Advertisements

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s