The Listener's role in failover

All posts relating to Oracle database administration.

Moderator: Tim...

The Listener's role in failover

Postby jnrpeardba » Wed Oct 10, 2012 9:32 am

Hi Tim,

This week we attempted a failover from one datacentre to the other. In my eyes its a basic failover, which moves the file system which has all the oracle files and application info via a virtual host. This is a single instance 11.2 database

All works fine until it comes to the database, where because of the listener info, the database does not start.

As normal the listener.ora file contains the hostname (as seen below)

Code: Select all
listener.ora
# Generated by Oracle configuration tools.

LISTENER.ORA NETWORK CONFIGURATION FILE: /MWARE/ORACLE/11.2/PRODUCT/11.2.0/DBHOM
E_1/NETWORK/ADMIN/LISTENER.ORA = listener.ora Network Configuration File: /mware
/oracle/11.2/product/11.2.0/dbhome_1/network/admin/listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = bvecrdev.HSCNI.NET)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

$ hostname
bvecrdev
$


We have two hostnames bvecrdev and rvecrdev and a virtual hostname of ecrdev. At any one time the database will run on either one of the hosts, but if we attempt the failover and hope to use the virtual name, the database is there, but accepts no connections. This all makes sense to me, as without the hostname in the listener file, the database will not accept connections. But when the filesystem is moved along with the listener, it will contain the incorrect node, which therefore means the listener and subsequently the database will not start - hence the reason for using a virtual name (ecrdev) but using this it fails. Is there anything else that I can check here? The client has this method working in a number of similar environments, but their way of doing it is to disable the listener file, so surprise surprise when I do this, the database is not opened. Somehow I do not believe their method ;)

So I was wondering have you got anything that I can check or implement where I can look at the virtual machine, to determine, why I cannot establish connectivity. I was wondering what would happen if you had both hostnames in the listener.ora file and regardless of which ever node it's on, should work. Therefore by-passing the virtual name option and opting for 2 hostnames in the listener.ora file - which would end up looking like this: I'm not even sure if this is feasible. Ideally I'd like to include the virtual name or IP address in the listener.ora, however when I use that there is no connection to the database or service.

Code: Select all
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = bvecrdev.HSCNI.NET)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rvecrdev.HSCNI.NET)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )



Many thanks in advance,

Jnrpeardba
jnrpeardba
Advisor
 
Posts: 392
Joined: Wed May 04, 2011 3:14 pm

Re: The Listener's role in failover

Postby Tim... » Wed Oct 10, 2012 5:10 pm

Hi.

The database doesn't start because of the listener? Really? A database can run without the listener being present...

I think you should set:

- Your ORACLE_HOSTNAME environment variable to the virtual name. (ORACLE_HOSTNAME=ecrdev)
- Use the virtual name in the listener.ora and tnanames.ora files.
- Make sure the DNS (or local hosts file) resolves the virtual name to the currently running IP address.

With this in place, I would expect the listener to start normally.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: The Listener's role in failover

Postby jnrpeardba » Thu Oct 11, 2012 10:07 am

Hello Tim,

I tried this on a test machine, prior to implementing this on a production server - see details below

hostname

Code: Select all
$ echo $ORACLE_HOSTNAME
ecrrepprep
$


changed the tnsnametora and listener.ora to include the virtual host

Code: Select all
$ more tnsnames.ora
# tnsnames.ora Network Configuration File: /mware/oracle/11.2/product/11.2.0/dbh
ome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ECRREPPREP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ecrrepprep.hscni.net)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ecrrepprep)
    )
  )

$ more listener.ora
# listener.ora Network Configuration File: /mware/oracle/11.2/product/11.2.0/dbh
ome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

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

ADR_BASE_LISTENER = /mware/oracle/11.2

$


The listener status now looks like this

Code: Select all
$ lsnrctl status

LSNRCTL for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production on 11-OCT-2012 11:00:01

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ecrrepprep)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.2.0 - Production
Start Date                11-OCT-2012 10:50:04
Uptime                    0 days 0 hr. 9 min. 57 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /mware/oracle/11.2/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /mware/oracle/11.2/diag/tnslsnr/bvecrrepprep/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.210.2.97)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
The listener supports no services
The command completed successfully
$


The database name is

Code: Select all
SQL> select instance_name, status from v$instance;

INSTANCE_NAME    STATUS
---------------- ------------
ecrrepprep       OPEN

SQL>


So finally tested connectivity to the database

Code: Select all
$ sqlplus jnrpeardba/jnrpeardba@ecrrepprep

SQL*Plus: Release 11.2.0.2.0 Production on Thu Oct 11 10:56:15 2012

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name:


Is there anything I have missed or not done correctly?

Many thanks

Jnrpeardba
jnrpeardba
Advisor
 
Posts: 392
Joined: Wed May 04, 2011 3:14 pm

Re: The Listener's role in failover

Postby Tim... » Thu Oct 11, 2012 2:28 pm

Hi.

If you restart the listener it can take up to 60 seconds for the auto-registration to kick in. You can force it using,

Code: Select all
ALTER SYSTEM REGISTER;


Does the service appear once you've done that?

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: The Listener's role in failover

Postby jnrpeardba » Thu Oct 11, 2012 3:42 pm

Hi Tim,

I normally wait a minute or so before connecting, but at not time at all do the services re-appear
But if I use the physical host name etc... with tnsnames and the listener.ora, connection is fine.

Jnrpeardba
jnrpeardba
Advisor
 
Posts: 392
Joined: Wed May 04, 2011 3:14 pm

Re: The Listener's role in failover

Postby Tim... » Thu Oct 11, 2012 5:45 pm

Hi.

I don't understand this. If the name resolves correctly, the virtual name should work fine.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 7 guests

cron