Aychin's Oracle RDBMS Blog

Only for Advanced level Professionals

Monthly Archives: June 2015

About listener static registration and standby db from active database creation in RAC environment.

It will be a small post about the static listener registration and which connection description to use for standby database creation if from active database method used. I write about it, because I found that this topic is not clear for everybody and more of that, there is some popular publications on the web which describe this topic absolutely incorrect.

Actually the topic is very simple. The question is to which listener to connect, local listener or SCAN listener.

To create standby database from active database RMAN requires to connect from primary site to auxiliary instance on remote standby site. Auxiliary instance started in nomount mode, actually it is just memory structures and background processes which are not attached to any database yet, that is why default service is dynamically registered with local and/or SCAN listener (depending on the settings of local_listener and remote_listener parameters) in BLOCKED status. Also duplication process includes database start/stop operations, i.e. when database will be stopped there will be no possibility to connect to standby site. Instance in nomount mode is in BLOCKED status because PMON process (in 12c LREG process) registers this status with the listener, it knows that database can not be used for client connections and informs about it listeners. To avoid this PMON (or LREG) process behavior, we must take this registration process to our self and we will for sure will not ask to block connections :). For this reason we require statically, i.e. manually registered service to the listener and provide him required information like ORACLE_SID, ORACLE_HOME and GLOBAL_DBNAME, actually we do what PMON process does usually, but he provides also additional information like current load which is used by load balancing.

Process PMON or LREG gets the listener address to register instance information from LOCAL_LISTENER and REMOTE_LISTENER initialization parameters. Parameter LOCAL_LISTENER by default points to the local host and port 1521, REMOTE_LISTENER is set by the DBUA after creation to SCAN name and port, if you create database manually then you must set yourself. It means that this two parameters are related only to so cold dynamic registration. Static registration is configured in listener.ora file.

To create standby from active database you will need to configure statically registered service. Theoretically you have two options:

  1. To configure it for local listener
  2. To configure it for SCAN listener

It is possible to manually register service to SCAN listener, but there is few points about that:

  • You will need to do it three times, for each SCAN listener.
  • You will not know to which node you will be connected, because it can be any of this three. If you will try to connect to already started instance, it will be very funny 🙂
  • It will be temporary config which you will need to remove afterwords.

Sure it is not recommended to use SCAN for this purpose. You should use local listener. Assuming that local listener by default called LISTENER, example will look like this:

SID_LIST_LISTENER =
      (SID_LIST =
        (SID_DESC =
          (GLOBAL_DBNAME = mydb_DGMGRL.oracle.com)
          (SID_NAME = mydb)
          (ORACLE_HOME = /u01/app/oracle/product/11.2.0.3/home1)
       )

We use GLOBAL_DBNAME which will be registered as a service name to the LISTENER named listener. To connect to this service we will specify in connection description VIP address of the selected for duplication node and mydb_DGMGRL.oracle.com service name. We use this service name because it will be used by DG Broker also. It means that we will configure it ones, you can do it even on all the nodes of the pool you plan to use for standby database.

You do not need to use SCAN listener. Yes, for regular database connection we should use SCAN listener and by DG Broker it is also used, it will register its service mydb_DGB.oracle.com with SCAN and local listeners automatically and will use it to communicate with primary and vice-verse. But for activities which require database startup/shutdown like switchover or fail-over  it will use connection description constructed from service name in form <DB_UNIQUE_NAME>_DGMGRL.<DB_DOMAIN> and the VIP address of the node where standby runs at the moment. That is why we used mydb_DGMGRL.oracle.com name, to match what DG Broker will search for.

Each time when you will relocate standby or primary database to other nodes DG Broker will automatically update its StaticConnectIdentifier property with the VIP address of the new node! You can check it by next dgmgrl command:

DGMGRL> show database mydb StaticConnectIdentifier
StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.500.119.131)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=mydb_DGMGRL.oracle.com)(INSTANCE_NAME=mydb)(SERVER=DEDICATED)))'

As the summary, configure static listener configuration for local listeners on all required nodes as per Data Guard requirement, then use this service on one of the nodes for initial standby creation.

For 12c software the new feature is ability of Data Guard to coordinate activities with CRS. Actually if you have Grid Infrastructure installed in cluster or sinlge instance mode then Data Guard can delegate database restart to server control software. It means that statically registered listener not required, but both primary site and standby site database must be registered with clusterware. In this case it is advicable to register some temporary service with the local listener on one of the nodes specially for standby creation and remove it after. If you do it on production system and you dont want to affect production listener then it is adviced to start one temporary listener for this perpose, actually it is the way how Enterprise Manager performes standby creation, it starts new temporary listener with static service and after operation removes it.

 


(c) Aychin Gasimov, 06/2015, Munich, Germany