Oracle ERP DB Exadata migration + AC-50480: Internal error occurred + ORA-12154

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