19c upgrade with EBS 12.1.3 environment

[raiq1]

Dear Sir,

Hope you are fine there, I am planning to upgrade our environment from 11.2.0.4 to 19c upgrade , following the note id.

 Interoperability Notes: Oracle E-Business Suite Release 12.1 with Oracle Database 19c (Doc ID 2580629.1


Now oracle has introduce has health check script to run first, before move forward to apply interoperability patch . I just ran it and the output is showing 1 potential error with warning.

I am confuse that can I ignore this message and move forward to another step.

SendingTEST_ora_7797_HCHECK.trc you hcheck.trc file, kindly check and advice.

Regards,
Raiq.

[ErmanArslansOracleBlog]

Yes you have 1 error and some warning there.
The documents to follow for getting rid of those errors are already written in the trace, so you should follow those documents.

for instance;

FAIL
HCKE-0003: SOURCE$ for OBJ# not in OBJ$ (Doc ID 1360233.1)
SOURCE$ has 283 rows for 22 OBJ# values not in OBJ$

Doc 1360233.1 says the following;

The dictionary SYS.SOURCE$ table has orphaned rows in it.
        The message tells displays how many orphan rows for how many
        distinct object ids do not exist in SYS.OBJ$.

--> The only real impact is wasted space in the SYSTEM tablespace
        for those SOURCE$ rows.

We don't expect to see it in 11.2.0.4.. Probably there are some recrods still in SOURCE$ but they are not in OBJ$.. This seems related with a database bug.

I think that's ignorable, but you are about upgrade your database, so you can't take that risk.

In these types of situation, you should create a SR and upload the trace and tell them "I see 1 error and some warnings in the trace, I think they are ignorable, please approve if that's the case.. but if you don't think I should ignore these error and warnings, please send me an action plan asap"

[raiq1]

Dear sir,

I am the last stage of 19c upgrade, ran post pdb creation script

perl $ORACLE_HOME/appsutil/bin/txkPostPDBCreationTasks.pl -
dboraclehome=/d01/oracle/TEST/19.0.0 -outdir=/d01/oracle/TEST/19.0.0/appsutil/log -cdbsid=TESTCDB -pdbsid=TEST -appsuser=apps -dbport=1542 -servicetype=onpremise

It has created 2 environment files.

-rw-r--r--. 1 oracle oinstall  3217 Feb 14 23:41 TESTCDB_ebs12.env
-rw-r--r--. 1 oracle oinstall  5821 Feb 14 23:41 TEST_ebs12.env

once we tried to connect apps user in PDB environment file, apps are not connected.

[oracle@ebs12 ~]$ . ./TEST_ebs12.env
[oracle@ebs12 ~]$ sqlplus apps/apps@TEST

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 15 01:57:03 2022
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Why it has not connected to pdb database, kindly solve this issue  and we stuck here . because of this issue, we are not move forward with UTL_FILE_CREATION step bcoz there also it has prompt apps password and it is not working.

please advice.

Regards,
Raiq

[ErmanArslansOracleBlog]

Are you sourcing the correct env before using the sqlplus to connect to the database?
I mean Is your PDB name TEST?

When you put @TEST there, you connect through the listener.. Ensure the listener is configured properly.

Probably you have a service name problem.. Check the following blog post accordingly ->

https://www.funoracleapps.com/2021/06/forms-giving-invalid-username-password.html


---Additional Info.
How to connect the Oracle E-Business Suite PDB as APPS user?
Run the following commands to connect PDB as APPS user
$ source <PDB_NAME>_<NODE_NAME>.env
$ sqlplus apps/apps@<PDB_NAME>


Refs:

19c Database with Oracle E-Business Suite R12 Known Issues & Solutions (Doc ID 2662860.1) - ORA-1017 when opening Forms and ORA-1017 Invalid username/password; logon denied FRM-41352 failed to create a new session
Connection Attempt to Pluggable Database PDB Fails with ORA-01017 (Doc ID 2169712.1)

[raiq1]

Hi,

When we ran txkpostdbcreation.pl script, it has created listner.ora & all parameters and also run the autoconfig on database side.

Below i am connected to CDB database that is TESTCDB and TEST are PDB which are READWRITE status


[oracle@ebs12 ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 15 23:10:36 2022
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 TEST                           READ WRITE NO
SQL>
-rw-r--r--.  1 oracle oinstall       5670 Feb 15 17:50 TEST_ebs12.env
drwxr-xr-x.  2 oracle oinstall       4096 Feb 15 18:00 dbs
drwxr-xr-x.  2 oracle oinstall         49 Feb 15 18:01 QOpatch
drwxrwx---.  4 oracle dba              78 Feb 15 18:01 oraInventory


[oracle@ebs12 19.0.0]$ . ./TEST_ebs12.env
[oracle@ebs12 19.0.0]$ sqlplus apps/apps@TEST

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 15 23:17:50 2022
Version 19.12.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:


Kindly advice.

Regards,

[raiq1]

Hi Erman,

I am able to connect PDB , tnsnames.ora file has SERVICE=TESTCDB , here we changed to TEST(PDB),

after that . ./TEST_ebs12.env

sqlplus apps/apps@TEST

it has connected

But this issue are temporary corrected, next step to configure utl_file_dir after ran setutilfiledir to synchronize the utl_file_dir , it has failed again.

setutilfildir are running internal autoconfig , and autoconfig is failed , when I check tnsnames.ora again it has revert the old changes and tnsnames.ora SERVICE are TESTCDB, it should be TEST that has pdb.

kindly advice me permenant solution to fix tnsnames.ora file, it should not change to CDB value in SERVICE.

I have upload autoconfig file for your reference.

Regards,
adconfig.log

[ErmanArslansOracleBlog]

Yes.. tnsnames is regenerated and that's why your temp workaround is overwritten.
In my last update, I sent you 3 notes.
This seems related with the services..
In 19c database service_name parameter should be set to container name..

Please the that blog post and the following MOS Note -> 19c Database with Oracle E-Business Suite R12 Known Issues & Solutions (Doc ID 2662860.1) - Section: ORA-1017 when opening Forms and ORA-1017 Invalid username/password; logon denied FRM-41352 failed to create a new session

[raiq1]

Hi,

As per your advice, I have followed the notes that you have mentioned but there is no luck.

Mean while again I run postpdbcreation and closely monitor the log , I have analyzed few things.

postpdbcreation are run autoconfig internally , at one stage it has changed the tnsnames.ora file with SERVICE_NAME= <CDBNAME> , it should be SERVICE_NAME=<PDBNAME>.

This changes has affected at the end of autoconfig.


[oracle@ebs12 TXK_POST_PDB_Thu_Feb_17_02_49_17_2022]$ grep tnsnames txkPostPDBCreationTasks.log

TARGET : /d01/oracle/TEST/19.0.0/appsutil/log/TXK_POST_PDB_Thu_Feb_17_02_49_17_2022/tns_admin_pdb_temp/tnsnames.ora
SOURCE : /d01/oracle/TEST/19.0.0/network/admin/TEST_ebs12/tnsnames.ora
TARGET : /d01/oracle/TEST/19.0.0/appsutil/log/TXK_POST_PDB_Thu_Feb_17_02_49_17_2022/tns_admin_pdb_bkp/tnsnames.ora
SOURCE : /d01/oracle/TEST/19.0.0/appsutil/log/TXK_POST_PDB_Thu_Feb_17_02_49_17_2022/tns_admin_pdb_temp/tnsnames.ora
TARGET : /d01/oracle/TEST/19.0.0/network/admin/TEST_ebs12/tnsnames.ora

We have grep tnsnames in txkPostPDBCreationTasks.log , the SOURCE has correct entries but when it moved to TARGET it has changed tnsnames.ora

SOURCE  --->  TEST= /d01/oracle/TEST/19.0.0/appsutil/log/TXK_POST_PDB_Thu_Feb_17_02_49_17_2022/tns_admin_pdb_temp/tnsnames.ora

Below are the tnsnames. ora in SOURCE destination .


TEST=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=ebs12.appsdba.info)(PORT=1542))
            (CONNECT_DATA=
                (SERVICE_NAME=TEST)
                (INSTANCE_NAME=TESTCDB)
            )
        )

TARGET ---> /d01/oracle/TEST/19.0.0/network/admin/TEST_ebs12/tnsnames.ora

TEST=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=ebs12.appsdba.info)(PORT=1542))
            (CONNECT_DATA=
                (SERVICE_NAME=TESTCDB)
                (INSTANCE_NAME=TESTCDB)
            )
        )


I dont know , from where autoconfig has getting the information and change the target tnsnames.ora to SERVCE_NAME=<TESTCDB>

Hope you understand all the scenario, for your informaton sending the logfile to you.

Regards,
RaiqtxkPostPDBCreationTasks.log

[ErmanArslansOracleBlog]

I see.. Nothing useful in the log..

We need to check the perl code of that perl (to find out how it retrieves the service name value..).. + we need to check the context file stored in the filesystem + FND_OAM_CONTEXT_FILES + we need to check the database configuration (init.ora, srvctl config and etc..)

Somehow, it thinks the service name should be the CDB name, but it is wrong as you say.. So we need to check the things that I mentioned above..

[raiq1]

Hi Erman sir,

We have resolved the issue for tnsnames.ora & now PDB database sid are added , as you have mentioned earlied that service name should be available in CDB , after added it , bounce the listener and its working fine.

Now second challenge i have faced is autoconfig in db side are failed with exit code 1.

We have created all the directories mentioned in util parameter but autoconfig not successfully finished.

[CVM Error Report]
The following report lists errors encountered during CVM Phase
      <filename>  <return code where appropriate>
  /d01/oracle/TEST/19.0.0/appsutil/bin/txkCreateEBSPDBService.sh  1

No of scripts failed in CVM phase: 1


AutoConfig is exiting with status 1

Sending the autoconfig log file to you , kindly check and advice.adconfignew.log

[ErmanArslansOracleBlog]

The service that should be created by that failing script is already there in the database. That's why that script fails.

Please check the services and see whether you added a service that shouldn't be added manually before this stage, before running this autoconfig.

select SERVICE_ID,NAME from dba_services;


Error stack:

ERROR DESCRIPTION:
****"(ORA-44303: service name exists"********
ORA-06512: at "SYS.DBMS_SERVICE_ERR", line 21
ORA-06512: at "SYS.DBMS_SERVICE", line 316
ORA-06512: at line 1 (DBD ERROR: OCIStmtExecute) at /d01/oracle/TEST/19.0.0/appsutil/bin/txkCreateEBSPDBService.pl line 407.
)
ERRORCODE = 1 ERRORCODE_END
.end std out.

.end err out.

[raiq1]

sir,

as per your query, result below.

SQL> select SERVICE_ID,NAME from dba_services;

SERVICE_ID NAME
---------- ----------------------------------------------------------------
         1 SYS$BACKGROUND
         2 SYS$USERS
         5 TESTCDBXDB
         6 TESTCDB


one more thing i have realised, after autoconfig run, when i check tnsnames.ora we have found

ebs_TEST service but this service are not available any where, even if i grep context file

[oracle@ebs12 appsutil]$ grep s_dbService TEST_ebs12.xml
      <service_names oa_var="s_dbService">ebs_TEST</service_names>

From where it has fetch ebs_TEST in service name.

Even tnsnames.ora also same .

TEST=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=ebs12.appsdba.info)(PORT=1542))
            (CONNECT_DATA=
                (SERVICE_NAME=ebs_TEST)
                (INSTANCE_NAME=TESTCDB)
            )
        )

kindly explain me from where SERVICE_Name has ebs_TEST record to enter here.

Hope you understand my point.

Regards,

[ErmanArslansOracleBlog]

You wrote:

as per your query, result below.

SQL> select SERVICE_ID,NAME from dba_services;

SERVICE_ID NAME
---------- ----------------------------------------------------------------
         1 SYS$BACKGROUND
         2 SYS$USERS
         5 TESTCDBXDB
         6 TESTCDB


one more thing i have realised, after autoconfig run, when i check tnsnames.ora we have found

ebs_TEST service but this service are not available any where, even if i grep context file

[oracle@ebs12 appsutil]$ grep s_dbService TEST_ebs12.xml
      <service_names oa_var="s_dbService">ebs_TEST</service_names>

From where it has fetch ebs_TEST in service name.

Even tnsnames.ora also same .

TEST=
        (DESCRIPTION=
                (ADDRESS=(PROTOCOL=tcp)(HOST=ebs12.appsdba.info)(PORT=1542))
            (CONNECT_DATA=
                (SERVICE_NAME=ebs_TEST)
                (INSTANCE_NAME=TESTCDB)
            )
        )

kindly explain me from where SERVICE_Name has ebs_TEST record to enter here.

Hope you understand my point.

Regards,

[ErmanArslansOracleBlog]

So according to what you wrote, I guess you have a misconfiguration in your service name configuration, or in your shell enviromment...

txkCreateEBSPDBService.pl tries to create the service but it fails with "ORA-44303: service name exists"
I didn't check the script but I guess it does something like the following;

dbms_service.create_service('ebs_<pdb>','ebs_<pdb>');

According to your dba_services output, you have only CDB related services there.. So probably your PDB name is set to your CDB name in your environment. I guess the script is trying to use the CDB name to create the PDB service, and that's normally failing..

This is my comment on this.. Please check review the Interoperability Notes: Oracle E-Business Suite Release 12.1 with Oracle Database 19c (Document:2580629.1) and ensure you got all the things in place properly, before running that autoconfig at that specific stage.