oracledba.help

<- DataGuard

Network Changes

  1. Configure tnsnames.ora
  2. Configure listener.ora: Standard DB | RAC\Oracle-Restart
  3. Bounce Listener

tnsnames.ora

As the oracle user, create a network alias on both systems so they can connect to each other.
gedit $ORACLE_HOME/network/admin/tnsnames.ora

LISTENER_ORADB =
  (ADDRESS = (PROTOCOL = TCP)(HOST = lnx01)(PORT = 1521))

oradb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lnx01)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradb)
    )
  )

oradb_sb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lnxsb)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradb_sb)
    )
  )
  • On the SB set LISTENER entry to match. Ex: LISTENER_ORADB_SB and lnxsb.
  • SERVER = DEDICATED is required for many DGMGRL communications.
  • (UR=A) in the tnsnames.ora can be used to connect to an Auxillary DB. This is useful for RMAN operations but must be taken out afterward.
  • RAC example here.

Listener Changes

  • Both systems need to have a static entry for DGMGRL so the corresponding database can be restarted.
  • To find the Listener Parameter File to edit run: lsnrctl status
  • For Standalone DB create it as the oracle user. For RAC and Oracle Restart create it as the grid user. RAC example here.

Primary

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnx01)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
	  (SID_DESC =
	    (SID_NAME = oradb)
	    (GLOBAL_DBNAME = oradb)
	    (ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1)
	  )
	  (SID_DESC=
	    (SID_NAME=oradb)
	    (GLOBAL_DBNAME=oradb_DGMGRL)
	    (ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1)
	  )
  )
  • For 18c use 18.3.0.0.0 instead of 12.1.0.2.
  • ADR_BASE_LISTENER entry may not be required. Leave the entry if it is already in the Primary's cfg file.

Standby (for Stand-Alone or Oracle-Restart)

gedit listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = lnxsb)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
	  (SID_DESC =
	    (SID_NAME = oradb_sb)
	    (GLOBAL_DBNAME = oradb_sb)
	    (ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1)
	  )
	  (SID_DESC=
	    (SID_NAME=oradb_sb)
	    (GLOBAL_DBNAME=oradb_sb_DGMGRL)
	    (ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1)
	  )
  )

ADR_BASE_LISTENER = /u01/app/oracle
  • For 18c use 18.3.0.0.0 instead of 12.1.0.2.
  • After above file created on SB start listener: lsnrctl start
  • If needed: alter system set local_listener='' scope=both;

tnsnames.ora RAC Example

oradb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)
    (HOST = rac-scan)
    (PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradb)
    )
  )

oradb1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lnx01-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradb1)
    )
  )

oradb2 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lnx02-vip)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradb2)
    )
  )

oradb_sb =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = lnxsb)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = oradb_sb)
    )
  )

Things to Note:

  • The VIP address is used for each nodes instance.
  • A LISTENER_ORADB type entry is not required on the RAC node tnsnamaes.ora files.

listener.ora RAC Example

Add these static entries to grid user listener.ora on each node:

# Added by ME for Data Guard
SID_LIST_LISTENER =
  (SID_LIST =
	  (SID_DESC=
	    (SID_NAME=oradb)
	    (GLOBAL_DBNAME=oradb)
	    (ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1)
	  )
	  (SID_DESC=
	    (SID_NAME=oradb)
	    (GLOBAL_DBNAME=oradb_DGMGRL)
	    (ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1)
	  )
	  (SID_DESC=
	    (SID_NAME=oradb1)
	    (GLOBAL_DBNAME=oradb_DGMGRL)
	    (ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1)
	  )
	  (SID_DESC=
	    (SID_NAME=oradb2)
	    (GLOBAL_DBNAME=oradb_DGMGRL)
	    (ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1)
	  )
  )
  • Under some conditions connections may be made using the instances SID_NAME (oradb1, oradb2 etc.) rather than the RAC SCAN.
  • To Be Verfied: Per Oracle University Training: _DGMGRL entries no longer needed when using Oracle Restart.

Bounce Listener

After making changes bounce Listener.

Standalone and Oracle Restart

 lsnrctl stop
 lsnrctl start

RAC

Minmally

srvctl stop listener -n lnx01
srvctl start listener -n lnx01
srvctl stop listener -n lnx02
srvctl start listener -n lnx02
srvctl stop scan_listener
srvctl start scan_listener

Optimally I find for RAC it is cleaner to just restart the grid and db services.

 su - oracle
 oracle> srvctl stop database -d MyDBName
 oracle> su -
 # export GRID_HOME=/u01/app/12.1.0.2/grid
 # export PATH=$GRID_HOME/bin:$PATH
 # cd $GRID_HOME/bin
 # crsctl stop cluster -all
 # crsctl start cluster -all
 # oracle> srvctl start database -d MyDBName
To check Cluster Services: grid> crsctl status res -t -init

<- DataGuard