EBS 12.2 -- enabling virtual host for the single host database tier, s_virtual_hostname, s_dbhost, ORA-12541

Recently needed to run an EBS's database tier on a single node database environment. (EBS version was 12.2.4 and the EBS's database version was 11GR2)

Actually, running on a single node DB, was only a temporary stage for this EBS.

That is, this database tier of this EBS instance, was already running on a single node system and the customer wanted us to migrate it to Exadata as a 2 Node RAC database.

This migration project was planned in 3 phases: Migrate EBS database tier to Exadata, Upgrade it to 12C and convert it to 2 node RAC.

So during the first phase, our database was needed to run only on 1 of the nodes of Exadata.

Even the database had to run on a single node in the 1st phase, we thought that; it was better to enable virtual host in advance.

 As this database was planned to be converted to RAC in the next stages, it was a good idea.

So, we decided to enable virtual host configuration for the database tier after migrating it to Exadata.

Ofcourse, we planned to use the virtual hostname (vip hostname) of the corresponding Exadata node for the virtual hostname of this newly migrated single node EBS database.

The thing that I want to share with you is actually appeared during the autoconfig.

That is ; everything was going well, we migrated the database using dataguard , cleaned the fnd_nodes etc and updated the context file with the relevant entries. (including s_virtual_hostname).

However; the db tier autoconfig-run was encountering TNS no listener errors (ORA-12541)

We weren't actually expecting that, because we did all the things that were said in the documentation.

The problem was on tnsnames.ora.

That is, the tnsnames.ora generated by autoconfig was relying the actual db hostname, however; the listener generated and started by autoconfig was listening on the virutal host name.

Actually, both the listener and tnsnames should have been relying on the virtual host name.

In order to check why; I jumped into the autoconfig templates and the scripts which were executed by autoconfig.

As for the listener, the template was; ad8ilsnr.ora

ad8ilsnr.ora was okay, as it was designed to rely on the virtual hostname..

Example ad8ilsnr.ora:

%s_db_listener% =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = %s_virtual_hostname%.%s_domainname%)(PORT = %s_dbport%))
    )
  )

SID_LIST_%s_db_listener% =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= %s_db_oh%)
      (SID_NAME = %s_dbSid%)
    )
  )

However, the template that was used for generating the TNS was not okay:  ad8itns.ora
As seen below, it was relying on the value of s_dbhost which was set in the db tier context file.

%s_dbSid% = (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=%s_dbhost%)(PORT=%s_dbport%))
                (CONNECT_DATA=(SID=%s_dbSid%))
            )

%s_instLocalListener% =
            (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=%s_dbhost%)(PORT=%s_dbport%))
            )

%s_instRemoteListener% =
            (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=%s_dbhost%)(PORT=%s_dbport%))
            )
#
# Intermedia
#
extproc_connection_data =
          (DESCRIPTION=
              (ADDRESS_LIST =
                  (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC%s_dbSid%))
              )
              (CONNECT_DATA=
                  (SID=PLSExtProc)
                  (PRESENTATION = RO)

This was weird. listening on virtual hostname but having a tns for dbhost?
So, at this point, I concluded that as a documentation lack/bug. (I said ad8itns.ora is not okay firstly, but it was no a problem of ad8itns.ora, its design was so)

I actually found a document that was justifying this decision.

-> Document

Using Oracle 12c Release 1 Real Application Clusters with Oracle E-Business Suite Release 11i (Doc ID 1498170.1) : Appendix E - >Change s_dbport and s_dbhost to SCAN Port and SCAN host.

So, the document was saying , set the dbhost on the host and port on which your database listener is listening. The document was mentioning the scan name and port, but it was obvious that the s_dbhost should be set to the virtual hostname in our case.

As, you can image, we set the s_dbhost to be the same as the s_virtual_host and rerun the autoconfig. The ORA-12514 error disspeared and autoconfig was successfully completed.

At the end of the day, what I m saying is, if you want to enable virtual host config on EBS, you need to set the s_dbhost context variable to the virtual hostname. (setting only s_virtual_hostname to the virtual hostname is not enough and create problems)