EBS 12.2 - ORACLEAGENT BLOG

APPLSYSPUB user Concepts in Oracle Application

APPLSYSPUB

The default password is ‘PUB’ and in case you change it, the password should be always in Upper Case. It is a public schema. which helps in validating Application user credentials when we login to Oracle Application.

How Applsyspub user works?

When we login to applications, initially oracle applications connect to public schema, APPLSYSPUB. This schema has sufficient privileges to perform the authentication of an Applications User (FND user), which includes running PL/SQL packages to verify the username/password combination and the privilege to record the success or failure of a login attempt.

Once authentication is successfully completed, Oracle Application allows to connect to apps schema and allows to choose a responsibility. By using responsibility, we can use HTML or Oracle forms interface to access data that resides in product schema.

How to change password of APPLSYSPUB ?

FNDCPASS utility is used to change the applsyspub password.

$FNDCPASS APPS/[apps_password] 0 Y SYSTEM/[system_passwoord] ORACLE APPLSYSPUB [new_passsword].0 & Y are flags for FNDCPASS0 is request id (request ID 0 is assigned to request ID’s which are not submitted via Submit Concurrent Request Form)’Y’ indicates that this method is directly invoked from the command-line and not from the Submit Request Form.

Once we change the APPLSYSPUB password must propagate the change to application tier configuration files. If the instance is Autoconfig enabled, must edit the CONTEXT file on each tier prior to running Autoconfig.

In the CONTEXT file, locate the autoconfig variable “s_gwyuid_pass” and set it to the new password, then run AutoConfig in each applications nodes.

How to fetch the APPLSYSPUB user password

1. echo $GWYUID

APPLSYSPUB/PUB

2. grep -i applsyspub $FND_SECURE/$TWO_TASK.dbc

GWYUID=APPLSYSPUB/PUB

3. grep -i s_gwyuid_pass $CONTEXT_FILE

password oa_var=”s_gwyuid_pass”– PUB — password

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

R12.2 adop phase=cleanup failed with ORA-20001: Error while calling ad_zd.cleanupORA-01555: snapshot too old:

Below i face an error when i was running adop session.

When i was running adop phase=cleanup i was hit with the below error

[applmgr@r122 ~]$ adop phase=cleanup
Enter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:
Please wait. Validating credentials…

RUN file system context file: /u01/oracle/PROD/fs2/inst/apps/PROD_r122/appl/admin/PROD_r122.xml
PATCH file system context file: /u01/oracle/PROD/fs1/inst/apps/PROD_r122/appl/admin/PROD_r122.xml
************* Start of session *************
version: 12.2.0
started at: Wed May 24 2021 03:34:53
APPL_TOP is set to /u01/oracle/PROD/fs2/EBSapps/appl
[STATEMENT] Using 4 workers (Default: 4, Recommended maximum limit: 62)
Cleanup is not done in earlier session
[START 2021/05/24 03:35:27] adzdoptl.pl run
ADOP Session ID: 4
Phase: cleanup
Log file: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/adop_20210524_033421.log
[START 2021/05/24 03:35:48] cleanup phase
[EVENT] [START 2021/05/24 03:35:52] Performing Cleanup steps
[EVENT] [START 2021/05/24 03:35:57] Running CLEANUP ddls in ddl handler table
Calling: adpatch options=hotpatch,nocompiledb interactive=no console=no workers=4 restart=no abandon=yes defaultsfile=/u01/oracle/PROD/fs2/EBSapps/appl/admin/PROD/adalldefaults.txt patchtop=/u01/oracle/PROD/fs2/EBSapps/appl/ad/12.0.0/patch/115/driver logfile=cleanup.log driver=ucleanup.drv
ADPATCH Log directory: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/cleanup_20210524_033421/PROD_r122/log
[EVENT] [END 2021/05/24 03:39:43] Running CLEANUP ddls in ddl handler table
[EVENT] Cleaning up ABORT DDL from DDL Handler Table
[START 2021/05/24 03:39:54] Generating All DDL Report
[EVENT] Report: /u01/oracle/PROD/fs2/EBSapps/appl/ad/12.0.0/sql/ADZDALLDDLS.sql
[EVENT] Output: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/cleanup_20210524_033421/PROD_r122/adzdallddls_20210524_033957.out
[END 2021/05/24 03:39:58] Generating All DDL Report
[EVENT] Calling cleanup in QUICK mode
[WARNING] Cleanup may take a while. Please wait.
[ERROR] Failed to execute sql statement :
declare
result varchar2(10);
begin
ad_zd.cleanup(‘QUICK’);
exception
when others then
raise_application_error(-20001,’Error while calling ad_zd.cleanup’ || sqlerrm);
end;
[ERROR] SQLPLUS error: buffer=
SQL*Plus: Release 10.1.0.5.0 – Production on Wed May 24 03:40:01 2021
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> SQL> Connected.
SQL> declare
ERROR at line 1:
ORA-20001: Error while calling ad_zd.cleanupORA-01555: snapshot too old:
rollback segment number 2 with name “_SYSSMU2_735814084$” too small
ORA-06512: at line 7
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[UNEXPECTED]Error occurred while calling cleanup plsql API
[UNEXPECTED]Cleanup phase completed with errors/warnings. Please check logfiles
Log file: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/adop_20210524_033421.log
adop exiting with status = 1 (Fail)
[applmgr@r122 ~]$

To overcome this issue.I have extended the undotablespace using below:

SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME like ‘APPS_UNDOTS1’;

TABLESPACE_NAME FILE_NAME

APPS_UNDOTS1 /u01/oracle/PROD/data/undo01.dbf

SQL> ALTER DATABASE DATAFILE ‘/u01/oracle/PROD/data/undo01.dbf’ RESIZE 10240M;
Database altered.
SQL>

Then again i executed the adop phase=cleanup

applmgr@r122 ~]$ adop phase=cleanup
Enter the APPS password:
Enter the SYSTEM password:
Enter the WLSADMIN password:
Please wait. Validating credentials…

RUN file system context file: /u01/oracle/PROD/fs2/inst/apps/PROD_r122/appl/admi
n/PROD_r122.xml
PATCH file system context file: /u01/oracle/PROD/fs1/inst/apps/PROD_r122/appl/ad
min/PROD_r122.xml
************* Start of session *************
version: 12.2.0
started at: Fri Feb 28 2021 04:11:18
APPL_TOP is set to /u01/oracle/PROD/fs2/EBSapps/appl
[STATEMENT] Using 4 workers (Default: 4, Recommended maximum limit: 59)
Cleanup is not done in earlier session
[START 2021/02/28 04:11:45] adzdoptl.pl run
ADOP Session ID: 4
Phase: cleanup
Log file: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/adop_20210228_041049.log
[START 2021/02/28 04:12:03] cleanup phase
[EVENT] [START 2021/02/28 04:12:06] Performing Cleanup steps
[EVENT] [START 2021/02/28 04:12:11] Running CLEANUP ddls in ddl handler table
Calling: adpatch options=hotpatch,nocompiledb interactive=no console=no workers=4 restart=no abandon=yes defaultsfile=/u01/oracle/PROD/fs2/EBSapps/appl/admin/PROD/adalldefaults.txt patchtop=/u01/oracle/PROD/fs2/EBSapps/appl/ad/12.0.0/patch/115/driver logfile=cleanup.log driver=ucleanup.drv
ADPATCH Log directory: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/cleanup_20210228_041049/PROD_r122/log
[EVENT] [END 2021/02/28 04:13:26] Running CLEANUP ddls in ddl handler table
[EVENT] Cleaning up ABORT DDL from DDL Handler Table
[START 2021/02/28 04:13:32] Generating All DDL Report
[EVENT] Report: /u01/oracle/PROD/fs2/EBSapps/appl/ad/12.0.0/sql/ADZDALLDDLS.sql
[EVENT] Output: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/cleanup_20210228_041049/PROD_r122/adzdallddls_20210228_041335.out
[END 2021/02/28 04:13:36] Generating All DDL Report
[EVENT] Calling cleanup in QUICK mode
[WARNING] Cleanup may take a while. Please wait.
[EVENT] [END 2021/02/28 04:57:56] Performing Cleanup steps
[END 2021/02/28 04:58:05] cleanup phase
[START 2021/02/28 04:58:11] Generating AD_ZD_LOGS Report
[EVENT] Report: /u01/oracle/PROD/fs2/EBSapps/appl/ad/12.0.0/sql/ADZDSHOWLOG.sql
[EVENT] Output: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/cleanup_20210228_041049/PROD_r122/adzdshowlog.out
[END 2021/02/28 04:58:24] Generating AD_ZD_LOGS Report
[END 2021/02/28 04:58:25] adzdoptl.pl run
adop phase=cleanup – Completed Successfully
Log file: /u01/oracle/PROD/fs_ne/EBSapps/log/adop/4/adop_20210228_041049.log
adop exiting with status = 0 (Success)

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

OPMN fails to start after latest EBS 12.2 ETCC patches

Sometimes applying Tech Stack patches lead to bugs or issues which we know. This is one such instance where the OPMN was failing to start after applying latest ETCC patches. So let us see in detail:

I have applied latest ETCC patches which includes patches for WebTier, 10.1.2.3, WLS etc. Once they are applied, opmn was failing to start and when I checked the OPMN log file, I have found the below error in it:

[opmn] [ERROR:1] [] [ons-secure] Connection server SSL set credentials failed (43084)
[opmn] [ERROR:1] [222] [ons-secure] SSL initialization failed

Oh! But wait.. I don’t have SSL or TLS configured in my environment but why is it complaining about SSL here? So apparently! after applying latest security patches of Oracle Process Manager and Notification Service (OPMN) and Oracle HTTPS Server (OHS) 11.1.1.9 components for Oracle E-Business Suite, this is expected.

Why does it happen?

The FMW no longer accepts the usage of certificates signed by MD5withRSA and requires using SHA256withRSA or better. So even though you don’t use SSL or TLS in EBS, you still have to create self-signed certificates and import them to wallet.

The detailed steps are present below:

Prerequisite Steps to Configure Oracle Fusion Middleware 11.1.1.9 Components for Oracle E-Business Suite Release 12.2 Before Applying the July 2019 and Later FMW OSS Security Patch (Doc ID 2555355.1)

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

AD/TXK 12 are now available for EBS 12.2

Oracle released update to the AD/TXK on 7th Aug, 2020. The previous version for R12.AD.C.Delta.11 was one of the major updates and there has not been a update to AD/TXK since a long time.

Oracle now released for R12.AD.C.Delta.12 and R12.TXK.C.Delta.12. There are no enhancements for any of the AD utilities and mostly its the bug fixes.

Check the documents below for more details:

Oracle E-Business Suite Applications DBA and Technology Stack Release Notes for R12.AD.C.Delta.12 and R12.TXK.C.Delta.12 (Doc ID 2649885.1)

Applying the Latest AD and TXK Release Update Packs to Oracle E-Business Suite Release 12.2 (Doc ID 1617461.1)

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Compile Invalid Packages in Oracle EBS Database

EBS Functionality breaks if dependent packages become invalid. While troubleshooting any E-Business Suite Functional issue, always check if any packages are changed, modified, or become invalids.

Query to check invalids:

Few queries you must use to check the status of the invalids.

Total Number of Invalids:

select count(*) from dba_objects where status=’INVALID’;

Number of Invalids with schema details

    col owner for a30

    select owner, object_type, count(*) from dba_objects where status='INVALID'

    group by owner, object_type;

Objects which are invalids in the database along with the OWNER name

    col OWNER for a30

    col OBJECT_NAME for a30

    set lines 1000

    select object_name, owner from dba_objects where status='INVALID';

How to Compile Invalids

Manual approach – Compile each package or package body manually.

alter package <package_name> compile body;

alter package <package_name> compile;

Similarly, we can compile other object_type like VIEW, FUNCTION, SYNONYM, TRIGGER

and MATERIALIZED VIEW

Use the same above query to compile.

alter procedure <procedure_name> compile;

alter synonym <synonym_name> compile;

alter trigger <trigger_name> compile;

DBMS_DDL Package method:

EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');

EBS 12.2 Method

SQL> exec AD_ZD.compile ('XX_DETAIL_PKG');

PL/SQL procedure successfully completed.

SQL> sho error

No errors.

SQL>

utlrp.sql tool

Goto $ORACLE_HOME/rdbms/admin and run utlrp.sql

SQL> @utlrp.sql

EBS 12.2 best methods to compile entire Schema

SQL> EXEC UTL_RECOMP.recomp_serial('APPS');

PL/SQL procedure successfully completed.

SQL>

Compile Invalids with parallel thread

SQL> EXEC UTL_RECOMP.recomp_parallel(10);

Verify the Invalids

    col OWNER for a30

    col OBJECT_NAME for a30

    set lines 1000

    select object_name, owner from dba_objects where status='INVALID';

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Compile Invalid Packages in Oracle EBS Database

EBS Functionality breaks if dependent packages become invalid. While troubleshooting any E-Business Suite Functional issue, always check if any packages are changed, modified, or become invalids.

Query to check invalids:

Few queries you must use to check the status of the invalids.

Total Number of Invalids:

select count(*) from dba_objects where status=’INVALID’;

Number of Invalids with schema details

    col owner for a30

    select owner, object_type, count(*) from dba_objects where status='INVALID'

    group by owner, object_type;

Objects which are invalids in the database along with the OWNER name

    col OWNER for a30

    col OBJECT_NAME for a30

    set lines 1000

    select object_name, owner from dba_objects where status='INVALID';

How to Compile Invalids

Manual approach – Compile each package or package body manually.

alter package <package_name> compile body;

alter package <package_name> compile;

Similarly, we can compile other object_type like VIEW, FUNCTION, SYNONYM, TRIGGER

and MATERIALIZED VIEW

Use the same above query to compile.

alter procedure <procedure_name> compile;

alter synonym <synonym_name> compile;

alter trigger <trigger_name> compile;

DBMS_DDL Package method:

EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');

EBS 12.2 Method

SQL> exec AD_ZD.compile ('XX_DETAIL_PKG');

PL/SQL procedure successfully completed.

SQL> sho error

No errors.

SQL>

utlrp.sql tool

Goto $ORACLE_HOME/rdbms/admin and run utlrp.sql

SQL> @utlrp.sql

EBS 12.2 best methods to compile entire Schema

SQL> EXEC UTL_RECOMP.recomp_serial('APPS');

PL/SQL procedure successfully completed.

SQL>

Compile Invalids with parallel thread

SQL> EXEC UTL_RECOMP.recomp_parallel(10);

Verify the Invalids

    col OWNER for a30

    col OBJECT_NAME for a30

    set lines 1000

    select object_name, owner from dba_objects where status='INVALID';

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

12.2 New Feature AFPASSWD utility to change password

Apps password change routine in Release 12.2 E-Business Suite changed a little bit. We have now extra options to change password, as well as some manual steps after changing the password using FNDCPASS.

There is a new utility introduced called AFPASSWD. This utility unlike FNDCPASS wont require you to enter apps and system user password, and makes it possible to separate duties between database administrator and application administrator. In most cases both these roles are done by same DBA. But in large organizations, there may be different teams that manage Database and Application.

Whether you use FNDCPASS or AFPASSWD to change the APPLSYS/APPS password, you must also perform some additional steps. This is because in R12.2, the old AOL/J connection pooling is replaced with Weblogic Connection Pool ( JDBC Data source ). Currently this procedure is not yet automated. It would be good, if this can be automated using some WLS scripting.

AFPASSWD Usage:
Changing SYSADMIN Password from application tier

AFPASSWD -f SYSADMIN
Enter the ORACLE password of Application Object Library ‘APPSUSER’:
Connected successfully to APPS.
Working…
Enter new password for user [SYSADMIN]:
Verify new password for user [SYSADMIN]:
Password is changed successfully for user SYSADMIN.
Password is changed successfully for user SYSADMIN.
AFPASSWD completed successfully.

FOR CHANGING APPLSYS PASSWORD WE NEED TO SHUTDOWN APPLICATION TIER AND ONCE WE FINISH CHANGING THE PASSWORD WE NEED TO RUN AUTOCONFIG ON ALL TIERS

Steps will be as below

1.SHUTDOWN APPLICATION TIER SERVICES
2.CHANGE PASSWORD
3.RUN AUTOCONFIG ON ALL TIERS
4.START APPLICATION TIER SERVICES.

How to change APPLSYS Password from Application Tier
AFPASSWD -c apps@EBSINSTANCE -s APPLSYS
Enter the ORACLE password of Application Object Library ‘APPSUSER’:
Connected successfully to APPS.
Enter the password for your ‘SYSTEM’ ORACLE schema:
Connected successfully to SYSTEM.
Working…
Enter new password for user:
Verify new password for user:
Working…
AFPASSWD completed successfully.

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

How To Change The Password For The SYSADMIN User

we have 2 options:

We can use E-Business suite screens
We can use FNDPASS utility

Please follow below steps for can change sysadmin user password:

1. If we want to change sysadmin user password with E-Business suite

System Administrator -> User -> Query the desired USERNAME and in password tab enter the new password.

To test, relogin into the applications as the SYSADMIN user and the new password.
Verify that a concurrent program such as Active Users runs.

2. If we want to change sysadmin user password with FNDPASS utility

Keep all services running.
Login OS level by way of the applmgr user and source(run)n your environment scripts
cd $APPL_TOP
run APPSenvironment.env

cd $FND_TOP/bin
Run FNDCPASS to change the SYSADMIN password

FNDCPASS apps/apps 0 Y system/manager USER SYSADMIN <New Password>

Example:

FNDCPASS apps/apps 0 Y system/manager USER sysadmin/sysadmin123

Restart the Apache, Forms, Reports, 8.0.6 listener and concurrent manager services. << This step not mandatory, but recommended
To test, login to EBS as the SYSADMIN user with new password.
Verify that a concurrent program such as Active Users runs.

Reference:

How To Change The Password For The SYSADMIN User [ID 423274.1]

How to Change Applications R12 Passwords using Applications Schema Password Change Utility (FNDCPASS)? [ID 437260.1]

NEW FEATURE FOR CHANGING PASSWORD IN R12

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

How to find pending concurrent requests in 11i/R12

Below document describes how to find total pending concurrent requests in the system.

Some times our concurrent requests queue fill up. We find lot of requests in pending status. In that scenarios we need to find the total number of pending requests.

Please use below query to find pending concurrent requests details.

SELECT DECODE(phase_Code,’P’,’Pending’,’R’,’Running’)
phase
, meaning status
, count(*) numreqs
FROM apps.fnd_concurrent_requests, apps.fnd_lookups
WHERE LOOKUP_TYPE = ‘CP_STATUS_CODE’ AND lookup_code = status_code AND phase_code in ( ‘P’,’R’) and status_code!=’D’
and requested_Start_Date < sysdate
group by DECODE(phase_Code,’P’,’Pending’,’R’,’Running’) , meaning

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Category Archives: EBS 12.2

APPLSYSPUB

TABLESPACE_NAME FILE_NAME

Query to check invalids:

How to Compile Invalids

Query to check invalids:

How to Compile Invalids

How to find pending concurrent requests in 11i/R12