The Listener's role in failover

[jnrpeardba]

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

[Tim...]

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...

[jnrpeardba]

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

[Tim...]

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...

[jnrpeardba]

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

[Tim...]

Hi.

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

Cheers

Tim...