It was 2014 when I moved from India to US for an Exadata Migration project for an Insurance Customer in New Jersey. I always wanted to write/publish for my company’s Knowledge portal “Knowmax” and this happens to be one of the first topics I wrote that year.
We had to build an active dataguard for the PROD database we migrated to Exadata. After setting up the dataguard and the broker configuration, I witnessed the below issues:
• “Error 12514 received logging on to the standby” – on the primary while connecting to the standby
• “FAL[client, USER]: Error 16191 connecting to primary” – on the standby for fetching gap sequence
The log shipping from primary to standby could not proceed after the dataguard was set up. Also the FAL client service failed to resolve the gap sequence and the primary and standby went out of sync since the MRP could not take place in absence of missing archive logs.
When an instance starts, the pmon does “Dynamic Registration” of the service with the listener. For this, the instance uses the LOCAL_LISTENER initialization parameter in order to register itself with the listener on startup. However, the local_listener value at standby was apparently wrong – the host was pointed to an incorrect IP. Hence the issue. After the local_listener was set up correctly and the database instance was restarted, the issue got resolved – logshipping from primary to standby started and the MRP process also started working.
So let’s discuss ways to perform dynamic and static registration.
1.Dynamic registration: For dynamic registration of the database service with the listener, ensure to update the “local_listener” init parameter to the correct value :
SQL> alter system set local_listener=’ (ADDRESS=(PROTOCOL=TCP)(HOST=<VIP of the local host>) (PORT=<PORT NO>))’ scope=both;
Alternatively, you can also add an entry in the tnsnames.ora file and point the local_listener to that tns entry.
LISTENER_<SID>=(ADDRESS = (PROTOCOL = TCP)(HOST = <VIP>)(PORT = <PORT NO>))
And then:
SQL> alter system set local_listener=’LISTENER_<SID> ‘ scope=both;
Next, restart the listener, in order to flush the current registrations and start clean.
Finally, force a registration of the instance by doing – SQL> alter system register;
Listener status will show as “READY” which means listener knows this because PMON of the instance told it so.
Static registration:
Add a static entry to the listener on standby :
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = <db_unique_name.db_domain>)
(ORACLE_HOME = <RDBMS HOME>)
(SID_NAME = <instance_name>)))
Restart the listener and check if the service has been properly registered. In our case, since the listener was running under grid, we could not bounce the listener- we would rather reload the listener.
Check that the sql*net connectivity is working fine by doing tnsping. Also verify if the ‘sys/<password> AS SYSDBA@<tns_alias>’ is working on both primary and standby.
One advantage of static registration is that it enables you to log on remotely as sys although the instance is not started and therefore no PMON talks to the listener yet – this is especially useful when you are creating the dataguard using RMAN duplicate from active database. The listener status will show as “UNKNOWN” which is expected – as the listener doesn’t verify if there is any such instance broadcasting the service name. However, if there is any client connection request for this service, the listener will try to provide the connection.
Tips:
1.Ensure that the LOCAL_LISTENER initialization parameter is properly set up before starting the database instance.
2.If the local_listener is NULL, the default value is (ADDRESS = (PROTOCOL=TCP)(HOST=hostname)(PORT=1521)) where hostname is the network name of the local host.
3.After you make a static entry in the listener.ora file, make sure to bounce/reload the listener.
4.Ensure that password file is properly renamed on each of the primary and standby instances.
Tech Talks with Indraneil 