r12.2 - Apps DBA Diaries

“APP-FND-02704: Unable to alter user APPS_NE to change password” while changing APPS password using FNDCPASS

Issue:

Changing apps password using FNDCPASS fails with below error

APP-FND-02704: Unable to alter user APPS_NE to change password.
Oracle error 28003: has been detected in alterpassword2.

Command to change APPS Password.[Ensure to shut down Application services before changing apps password]
FNDCPASS apps/<apps_pwd> 0 Y system/<password> SYSTEM APPLSYS <new_apps_password>
Error Message from Logfile:
+—————————————————————————+
Application Object Library: Version : 12.2
Copyright (c) 1998, 2013, Oracle and/or its affiliates. All rights reserved.
FNDCPASS:
+—————————————————————————+
Current system time is 10-APR-2020 18:43:36
+—————————————————————————+
Arguments
FNDCPASS system/***** SYSTEM APPLSYS *****
+—————————————————————————-+
Working…
APP-FND-02704: Unable to alter user APPS_NE to change password.
Oracle error 28003: has been detected in alterpassword2.
+—————————————————————————+
Concurrent request completed
Current system time is 10-APR-2020 18:43:39
+—————————————————————————+

Cause:
Issue is due to Password_verify_function enables in dba_profiles for APPS,APPLSYS,APPS_NE users

Solution:

Disable the password verify function for APPS,APPLSYS,APPS_NE users in database profiles.
1. Identify the database profile for APPS,APPLSYS,APPS_NE users using below query.

SELECT USERNAME,PROFILE FROM DBA_USERS where USERNAME in (‘APPS’,’APPLSYS’,’APPS_NE’);
SELECT PROFILE,VALUE FROM DBA_PROFILES WHERE PROFILE=’&profile for user’;

2. Disable password verify function

alter profile <profile_name> limit PASSWORD_VERIFY_FUNCTION null;

3. Run the FNDCPASS command to change password.

FNDCPASS apps/<apps_pwd> 0 Y system/<password> SYSTEM APPLSYS <new_apps_password>

4. After changing apps password, run autoconfig on Middle tier and update EBS datasource for R12.2.x

You can follow the below steps to update apps password in EBS Datasource:

http://www.appsdbadiaries.com/2022/02/how-to-update-apps-password-in-ebs.html

5. Startup Application services

4. Revert back the changes done in step 2

How to Update APPS password in EBS Datasource (EBS 12.2.x)?

From EBS 12.2.x versions, whenever we change apps password we need to update the new apps password in EBS Datasource.
Otherwise managed servers wont come up.
Steps to update apps password in Weblogic datasource for EBS 12.2x, post AD-TXK 7:
1. Start Only Admin server,Do not start any other services
$. ./EBSapps.env RUN
$ cd $ADMIN_SCRIPTS_HOME
$adadminsrvctl.sh start

2. Update apps password in Datasource from backend:
$perl $FND_TOP/patch/115/bin/txkManageDBConnectionPool.pl
When prompted select the “updateDSPassword” option.
Sample screen output:

perl $FND_TOP/patch/115/bin/txkManageDBConnectionPool.pl
Please select from list of valid options
updateDSPassword – Update WebLogic Datasource Password
updateDSJdbcUrl – Update WebLogic Datasource Connection String
Enter Your Choice : updateDSPassword
Enter the full path of Applications Context File [DEFAULT -]:
Enter weblogic admin server password:
Enter the APPS user password:

Note: We can also update the apps password in EBS Datasource from Weblogic console.

EBSO 12.2.x : Query to check status of ADOP session

Check Status of adop session using below sql query:

set pagesize 200;
set linesize 160;
col PREPARE_STATUS format a15
col APPLY_STATUS format a15
col CUTOVER_STATUS format a15
col ABORT_STATUS format a15
col STATUS format a15
select NODE_NAME,ADOP_SESSION_ID, PREPARE_STATUS , APPLY_STATUS ,CUTOVER_STATUS , CLEANUP_STATUS , ABORT_STATUS , STATUS
from AD_ADOP_SESSIONS
order by ADOP_SESSION_ID;

Decoding Cutover status:

cutover_status=’Y’ ‘COMPLETED’
cutover_status not in (‘N’,’Y’,’X’) and status=’F’ ‘FAILED’
cutover_status=’0′ ‘CUTOVER STARTED’
cutover_status=’1′ ‘SERVICES SHUTDOWN COMPLETED’
cutover_status=’3′ ‘DB CUTOVER COMPLETED’
cutover_status=’D’ ‘FLIP SNAPSHOTS COMPLETED’
cutover_status=’4′ ‘FS CUTOVER COMPLETED’
cutover_status=’5′ ‘ADMIN STARTUP COMPLETED’
cutover_status=’6′ ‘SERVICES STARTUP COMPLETED’
cutover_status=’N’ ‘NOT STARTED’
cutover_status=’X’ ‘NOT APPLICABLE’

EBS 12.2.X Patching: ADOP Cutover failed while running txkADOPCutOverPhaseCtrlScript.pl

Issue:

During an adop online patching cycle, cutover phase failed while running the script txkADOPCutOverPhaseCtrlScript.pl

[PROCEDURE] Starting Middle Tier Services
[UNEXPECTED]Error occurred running “perl /test/app/fs1/EBSapps/appl/ad/12.0.0/patch/115/bin/txkADOPCutOverPhaseCtrlScript.pl -contextfile=/test/app/fs1/inst/apps/EBSTEST_testebsmt111/appl/admin/EBSTEST_testebsmt111.xml -patchcontextfile=/test/app/fs1/2/inst/apps/EBSTEST_testebsmt111/appl/admin/EBSTEST_testebsmt111.xml -promptmsg=hide -console=off -sessionid=14 -timestamp=20201108_194155 -outdir=/test/app/fs_ne/EBSapps/log/adop/14/20201108_194155/cutover/testebsmt111 -action=forcestartup”
[UNEXPECTED]Cutover phase has failed.

Cause:

Review of all adop and cutover log files shows that Cutover phase failed after filesystem switch and ADMIN server startup,oacore services could not be started after cutover due to port conflict.

Resolution:

1. Check Status of adop session using below sql query:
set pagesize 200;
set linesize 160;
col PREPARE_STATUS format a15
col APPLY_STATUS format a15
col CUTOVER_STATUS format a15
col ABORT_STATUS format a15
col STATUS format a15
select NODE_NAME,ADOP_SESSION_ID, PREPARE_STATUS , APPLY_STATUS ,CUTOVER_STATUS , CLEANUP_STATUS , ABORT_STATUS , STATUS
from AD_ADOP_SESSIONS
order by ADOP_SESSION_ID;
For my issue, cutover status was 5, which means ADMIN startup is completed.

2. Restart the cutover phase with mtrestart=no option

adop phase=cutover mtrestart=no

3. Fix the issues related to services startup on RUN filesystem (which is switched after cutover).

4. Startup the services

5. Perform cleanup phase and complete the adop cycle
adop phase=cleanup

adpreclone.pl Error on Applications Tier

Issue:

adpreclone.pl appsTier on EBS 12.2.x environment fails with error

ERROR: The context variable s_apps_jdbc_connect_descriptor is null

Cause:

Value of parameter “s_apps_jdbc_connect_descriptor” is null in Applications Context file.

Solution:

1. Take Backup of context file

2. Update the value of parameter “s_apps_jdbc_connect_descriptor” with JDBC connect string to connect to the database.

3. Run Autoconfig on Applications node.

4. Restart adpreclone.pl appsTier

Autoconfig Error : ORA-01422: exact fetch returns more than requested number of rows

Issue: Autoconfig on EBS 12.2 environment fails while running script txkGenADOPWrapper.pl

Below is the error message from logfile

=======================

Script Name : txkGenADOPWrapper.pl

Script Version : 120.0.12020000.3

ERROR DESCRIPTION:

(*******FATAL ERROR*******

PROGRAM : (/test/app/fs1/inst/apps/test_mtnode1/admin/install/txkGenADOPWrapper.pl)

TIME : Wed Nov 25 04:22:34 2020

FUNCTION: TXK::SQLPLUS::_doExecute [ Level 3 ]

MESSAGES:

SQLPLUS error: buffer=

SQL*Plus: Release 10.1.0.5.0 – Production on Wed Nov 25 04:22:33 2020

SQL> SQL> Connected.

SQL> SELECT ad_zd_adop.get_node_type(‘mtnode1’) FROM DUAL

ERROR at line 1:

ORA-01422: exact fetch returns more than requested number of rows

ORA-06512: at “APPS.AD_ZD_ADOP”, line 3010

=======================

Cause:

FND_NODES table has incorrect node information.

Found an extra row in FND_NODES table where node_name is null after clone.

Solution:

1. Clean up FND_NODES table

sqlplus apps/<password>

sql>exec FND_CONC_CLONE.SETUP_CLEAN;

2.Run Autoconfig on Database node(s).

cd $ORACLE_HOME/appsutil/bin

adconfig.sh

[Provide Database Context file and apps Password]

3. Run Autoconfig on apps tier

Source the RUN filesystem environment file.

cd $AD_TOP/bin

adconfig.sh

[Provide Applictions Context file and apps Password]

Oracle EBS 12.2.X Login fails with “Unable to create anonymous session. ICX_SESSION_CREATION_FAILED”

Issue:

In a 12.2.6 EBS environment, Login fails with below error

Unable to create anonymous session. ICX_SESSION_CREATION_FAILED (userid=6) exception oracle.apps.fnd.common.PoolException: Exception creating new Poolable object. Encountered a java exception with the message Exception creating new Poolable object. Cause:java.lang.RuntimeException: java.lang.RuntimeException: java.sql.SQLException: java.lang.reflect.InvocationTargetException.

Analysis:

The error indicates that there are not enough resources to create a new JDBC session. oacore_server.log has the below error messages

####<Aug 08, 2017 3:43:05 PM EST> <Info> <Common> <test.domain.com> <oacore_server1> <[ACTIVE] ExecuteThread: ‘5’ for queue: ‘weblogic.kernel.Default (self-tuning)’> <<anonymous>> <> <005dy^V9G5e9xW6RJMU4TB0000xv001lSB> <1591994585553> <BEA-000627> <Reached maximum capacity of pool “EBSDataSource”, making “0” new resource instances instead of “1”.>
####<Aug 08, 2017 3:43:07 PM EST> <Info> <Common> <test.domain.com> <oacore_server1> <pool-2-thread-1> <<anonymous>> <> <*******************> <887561> <BEA-000627> <Reached maximum capacity of pool “EBSDataSource”, making “0” new resource instances instead of “1”.>
####<Aug 08, 2017 3:43:15 PM EST> <Info> <Common> <test.domain.com> <oacore_server1> <[ACTIVE] ExecuteThread: ‘5’ for queue: ‘weblogic.kernel.Default (self-tuning)’> <<anonymous>> <> <005dy^V9G5e9xW6RJMU4TB0000xv001lSB> <1591994595562> <BEA-000627> <Reached maximum capacity of pool “EBSDataSource”, making “0” new resource instances instead of “1”.>
####<Aug 08, 2017 3:43:15 PM EST> <Info> <Common> <test.domain.com> <oacore_server1> <pool-2-thread-1> <<anonymous>> <> <*******************> <955123> <BEA-000627> <Reached maximum capacity of pool “EBSDataSource”, making “0” new resource instances instead of “1”.>

Number of processes capacity for the Datasource “EBSDatasource” is exhausted.

Solution:

1. Increase the number of sessions in EBSDatasource

Click on Lock and Edit, Go to Connection Pool Tab, Increase the value in Number of sessions field.

Click on Activate Changes.

This change does not require any restart of services.

(or)

Alternate Solution:

2. Bounce oacore_server to release any inactive sessions and free up the resources

cd $ADMIN_SCRIPTS_HOME

admanagedsrvctl.sh stop oacore_server

admanagedsrvctl.sh start oacore_server

Troubleshooting concurrent requests struck at Post processing Phase

Sometimes , Eventhough the Database sessions related to the concurrent Programs are in INACTIVE State , we could not terminate the Concurrent Request with error “Could not Lock Request” , Issue could be Requests are struck at Post Processing Phase and OutPut Post Processor is locking the Concurrent Requests.

Need to follow the below steps to perform clean shutdown of Oracle Concurrent Manager.

a) Put Pending Concurrent Requests on hold using the below sql queries.

+Create table apps.conc_req_on_hold as select * from fnd_Concurrent_requests where PHASE_CODE=’P’ and hold_flag=’N’;
+select count(*) from apps.conc_req_on_hold
+ update fnd_Concurrent_requests set hold_flag=’Y’ where PHASE_CODE=’P’ and hold_flag=’N’ and request_id in (select request_id from apps.conc_req_on_hold);

NOTE: You have to commit if select & update are same number of records. Otherwise rollback and try again till the numbers are same

+ Commit;

You can find more details about putting Pending Concurrent Jobs on Hold here.
http://www.appsdbadiaries.com/2016/01/concurrent-requests-on-hold.html

b)Bring Down Concurrent Manager using adcmctl.sh stop

c) Update the status of Struck Concurrent Requests to “Terminated”

SQL> update fnd_concurrent_requests set status_code=’X’,phase_code=’C’ where status_code=’R’ and phase_code=’R’;

Commit;

Use Concurrent Manager Recovery Wizard from OAM to clear the database sessions associated with cancelled Requests.

d) Start Concurrent Manager using adcmctl.sh

e) Remove Hold on Concurrent Requests

SQL>update fnd_Concurrent_requests set hold_flag=’N’ where request_id in (select request_id from apps.conc_req_on_hold);

Commit the changes

SQL>commit;