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;

cloning using cold backup

In the below scenario , i'm creating clone database using cold backup , and forgot to mention undo datafile in the create controlfile script.
SQL> @createctl.sql

Control file created.

SQL> alter database open resetlogs;

Database altered.


SQL> select distinct status from v$datafile;

STATUS
-------
ONLINE
SYSTEM
RECOVER

found that one datafile needs recovery

SQL> select name from v$datafile where status='RECOVER';

NAME
--------------------------------------------------------------------------------
/d01/ora_vis/11.2.0.4/dbs/MISSING00379 ( created  the dummy file in default location ; which $ORACLE_HOME/dbs)


SQL> select * from dba_data_files where file_name like '%MISS%';

FILE_NAME
--------------------------------------------------------------------------------
   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
/d01/ora_vis/11.2.0.4/dbs/MISSING00379
       379 APPS_UNDOTS1                                         AVAILABLE
         379
RECOVER

(confirmed that the datafile belongs to undotablespace)
SQL>  drop tablespace APPS_UNDOTS1 including contents and datafiles;

Tablespace dropped.
(since i have modified undo_management parameter to manual in pfile before starting database; this is being accepted)
(Also , undo data might be required in some case for rollback /roll forward transactions in cold backup and recovery scenario too)

SQL> select distinct status from v$datafile;

STATUS
-------
ONLINE
SYSTEM

SQL> CREATE UNDO TABLESPACE APPS_UNDOTS1 DATAFILE '/d01/ora_vis/data/undo01.dbf' SIZE 50M AUTOEXTEND ON NEXT 5M MAXSIZE 2G;

Tablespace created.

shu immediate
change params
undo_management to auto
undo_tablespace to apps_undots1
startup

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      APPS_UNDOTS1

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
VIS       READ WRITE

PS : So don't forget to include your undo datafile in create controlfile script while cloning using cold or hot backup.
This use-case applies to recover of database if you loss undo datafiles after clean shutdown of database.

E-Business Suite R12 CPU Version

Hi All,

Here is script to find out , latest cpu that has been applied to R 12.1.

select b.bug_number BUG, b.LAST_UPDATE_DATE LDATE, decode(bug_number,
13322561, 'cpu_jan12',
13621942, 'cpu_apr12',
13979377, 'cpu_jul12',
14321237, 'cpu_oct12',
14782696, 'cpu_jan13',
16196190, 'cpu_apr13',
16772121, 'cpu_jul13',
17203944, 'cpu_oct13',
17631895, 'cpu_jan14',
17631895, 'cpu_apr14',
18122013, 'cpu_jul14',
19258579, 'cpu_oct14',
19873049, 'cpu_jan15',
20406628, 'cpu_apr15',
20953340, 'cpu_jul15',
21507207, 'cpu_oct15',
22133441, 'cpu_jan16',
22614470, 'cpu_apr16'
) PATCH
from APPS.AD_BUGS b
where b.BUG_NUMBER in ('13322561','13621942','13979377','14321237','14782696','16196190','16772121','17203944','17631895','17631895','18122013','19258579','19873049','20406628','20953340','21507207','22133441',
'22614470') order by 2;

Hope this helps you.

Thanks,
Uday