I was recently involved in a project to migrate an Oracle ERP database to an Exadata server. The ERP database was a non-RAC Oracle 12cR1 running on RHEL with Oracle EBusiness suite 12.1.3.

The migration involved only migrating the ERP database from traditional storage/server technologies to Exadata machine. Upgrade from non-RAC database to RAC. The application remains on the same host. Though similar migration projects were successfully done earlier, this time, I faced a tough challenge resolving autoconfig issues. Here are the details of the issue and how I resolved the problem.

Downtime was not an issue, hence, I opted out for an RMAN backup and restore approach. In nutshell, the following was done:

  • Prepared the Oracle home for ERP on Exadata and applied all recommended patches on the home.
  • Performed all mandatory steps required for ERP Oracle Home.
  • After graceful shutdown of application tier, ERP database was stopped and started in MOUNT state to proceed with RMAN full backup (this is the approach I used, though, many different approaches can be achieved).
  • Copied the files to target (Exadata system) and complete the recovery procedure.
  • Through manual approach, converted the non-RAC database to RAC mode and completed the post migration steps.

Its mandatory to run catbundle script after the database migration to avoid issues like blank login page and issues changing the passwords. Some of you might defer with my advice, but, I faced this at multiple clients. So, I decided to make this as a practice right after migration.

  • The autoconfig on database nodes were successful. However, when autoconfig was executed on application tier, it completed with warnings. The autoconfig,log has the following errors:
Updating s_tnsmode to 'generateTNS'
UpdateContext exited with status: 0
AC-50480: Internal error occurred: java.lang.Exception: Error while generating listener.ora.
Error generating tnsnames.ora from the database, temporary tnsnames.ora will be generated using templates
Instantiating Tools tnsnames.ora
Tools tnsnames.ora instantiated
Web tnsnames.ora instantiated

The NetServiceHandler.log reported the following error:

SQLPLUS Executable : /oracle/PROD/apps/tech_st/10.1.2/bin/sqlplus
SQL*Plus: Release - Production on Thu Feb 15 21:27:02 2018
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Enter value for 1: Enter value for 2: ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Unable to generate listener.ora from database
Using default listener.ora file

 After googling for the solution, the common workaround was the following:

  1. Clean up the entries from fnd_nodes using the EXEC FND_CONC_CLONE.SETUP_CLEAN;
  2. Run the autoconfig on Dbtier and run the autoconfig on App tier

 Unfortunately this doesn't helped us in our case.

When I looked at the host names in the FND_NODES for database, the hostname was registered against the management hostname (Exadata has management, public IPs). Though client opened ports for Public hostname, they didn't opened the firewall against the management network. Though we have multiple IPs (management and public), the DB server can only take one hostname. So, if you are on Exadata, you need to ensure what is the hostname registered on the server, despite the different IPs.
After allowing firewall to open the LISTENER ports through management IP, the autoconfig on App tier went successfully and we manage to connect the application.

The bottom line is, when you are on Exadata for ERP databases, you need to watchout for the hostnames and the ports opened against IPs from application server.

About the Author

Syed Jaffar Hussain

An Oracle Database Expert for over 15 years from his 20 years of Information Technology (IT) career. Over the past 15 years of Oracle journey, he involved with several local and large scaled international banks where he implemented and managed highly complex cluster and non-cluster environments with over 100’s of business critical databases. Recognizing his efforts and contribution towards the Oracle community, Oracle awarded him the prestigious ‘Best DBA of the year, 2011’ and Oracle ACE Director status. He also acquired industry best Oracle credentials, Oracle Certified Master (OCM), Oracle RAC Expert, OCP DBA 8i,9i,10g & 11g in addition to ITIL Expertise. Syed is an active Oracle speaker, regularly presents technical sessions and webinars on various Oracle database technologies at many Oracle events. You can visit his technical blog, http://jaffardba.blogspot.com where he discuss and writes the workaround/solution about the issues confronted from his day-to-day activities. Apart from being the part of the core Technical Review committee member for a few Oracle technology oriented books, he also co-authored an Oracle 11g R1/R2 Real Application Cluster Essentials and Oracle Expert RAC books. His blog can be found at http://jaffardba.blogspot.com/

Start the discussion at forums.toadworld.com