R12 AutoConfig failing with db connection issue

Application Tier Configuration Files To Look For.

    <$INST_TOP>/ora/10.1.2/network/admin/listener.ora

    <$INST_TOP>/ora/10.1.2/network/admin/tnsnames.ora

    <$INST_TOP>/appl/admin/<$CONTEXT_NAME>.xml

    <$FND_SECURE>/<sid>.dbc


Autoconfig is failing with dbConnection issue, to resolve it we update s_dbhost to dbhost1-vip


dbhost1.example.com is not pingable from MT tier.

dbhost1-vip.example.com is pingable from MT tier.


so we updated  s_dbhost to dbhost1-vip and ran autoconfig , autoconfig went smooth.

Not sure , why , because it is Exadata , but dbhost1-vip and dbhost1 will be on different NIC.


Below are message from autoconfig logfile before and after changing s_dbhost1

getConnection() -->
    sdbhost1    : dbhost1
    sDbexample  : example.com
    sDbPort    : 1521
    sDbSid     : VIS
    sDbUser    : APPS
    Trying to connect using SID...
getConnectionUsingSID() -->
    JDBC URL: jdbc:oracle:thin:@dbhost1.example.com:1521:VIS
    Exception occurred: java.sql.SQLException: The Network Adapter could not establish the connection
    Trying to connect using SID as ServiceName
getConnectionUsingServiceName() -->
    JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbhost1.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=VIS)))
    Exception occurred: java.sql.SQLException: The Network Adapter could not establish the connection
    Trying to connect using SID as ServiceName.exampleName
getConnectionUsingServiceName() -->
    JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbhost1.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=VIS.example.com)))
    Exception occurred: java.sql.SQLException: The Network Adapter could not establish the connection
    Connection could not be obtained; returning null


getConnection() -->
    sdbhost1    : dbhost1-vip
    sDbexample  : example.com
    sDbPort    : 1521
    sDbSid     : VIS
    sDbUser    : APPS
    Trying to connect using SID...
getConnectionUsingSID() -->
    JDBC URL: jdbc:oracle:thin:@dbhost1-vip.example.com:1521:VIS
    Exception occurred: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12505, TNS:listener does not currently know of SID given in connect descriptor

    Trying to connect using SID as ServiceName
getConnectionUsingServiceName() -->
    JDBC URL: jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dbhost1-vip.example.com)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=VIS)))
    Connection obtained

Find parent request and/or child request of a request_id

Given a concurrent request , the below query will bring you all the child and parent requests if any

SET verify OFF echo OFF feed OFF trims ON trim ON linesize 1200 pagesize 60
COLUMN "Program Name" format a37
COLUMN "Delay" format 9999.99
COLUMN "Elapsed" format 9999.99
SELECT /*+ ORDERED USE_NL(x fcr fcp fcptl)*/
                                fcr.request_id "Request ID",fcr.parent_request_id as "parent request id",
                                 fcptl.user_concurrent_program_name "Program Name" ,
                                fcr.phase_code,
                                fcr.status_code,
                                --     to_char(fcr.request_date,'DD-MON-YYYY HH24:MI:SS') "Submitted", 
                                --     (fcr.actual_start_date - fcr.request_date)*1440 "Delay",
                                To_char(fcr.actual_start_date,
                                'DD-MON-YYYY HH24:MI:SS')      "Start Time",
                                To_char(fcr.actual_completion_date,
                                'DD-MON-YYYY HH24:MI:SS') "End Time",
                                ( fcr.actual_completion_date -
                                  fcr.actual_start_date ) * 1440 "Elapsed",
                                fcr.oracle_process_id
                                "Trace ID"
FROM   (SELECT /*+ index (fcr1 fnd_concurrent_requests_n3) */ fcr1.request_id
        FROM   apps.fnd_concurrent_requests fcr1
        WHERE  1 = 1
        START WITH fcr1.request_id = ( SELECT request_id FROM fnd_concurrent_requests WHERE NVL (request_type, 'X')= 'M' CONNECT BY PRIOR parent_request_id = request_id START WITH request_id = &RequestID)
        CONNECT BY PRIOR fcr1.request_id = fcr1.parent_request_id) x,
       apps.fnd_concurrent_requests fcr,
       apps.fnd_concurrent_programs fcp,
       apps.fnd_concurrent_programs_tl fcptl
WHERE  fcr.request_id = x.request_id
       AND fcr.concurrent_program_id = fcp.concurrent_program_id
       AND fcr.program_application_id = fcp.application_id
       AND fcp.application_id = fcptl.application_id
       AND fcp.concurrent_program_id = fcptl.concurrent_program_id
       AND fcptl.LANGUAGE = 'US'
ORDER  BY 1;