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:

Adding a Host Target in EM 13C

Before adding any Software Targets such as Databases, EBS, Middleware it is mandatory to add the Host Targets first in EM. In this post, we will see the simple process of adding a Host Target in EM. I am going to add a host “apps.example.com” which actually has an Oracle EBS Vision Instance running on it. Before you add the Host you need ensure below are done already:

No firewall between OMS (EM) Host and Target Host. If you cannot open all ports, at least open the needed ports for e.g. 3872 on Target Host and 4889,4903 ports on EM. Always check ports needed to be opened using “emctl status oms -details”
Ensure both hosts are reachable from one another
Create directory for emagent in Target Host for e.g. /u01/install/emagent

Adding Host Target

Adding a Host Target is very easy and self explanatory. Please find the screenshots below.

Using emctl command

Using the emctl status agent -details, you can find details of the Agent and its status as shown in the screenshot above.

Known Issues and Suggestions

You might be facing issues related to firewall, OS permissions on agent, unavailable of OS packages such as ZIP, UNZIP. These you should be able to fix easily ideally.

How to resolve issue :

Clearing an Enterprise manager agent that fails to upload

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:

Oracle Statspack Installation / Re-Creation

Why we need statspack? However for some non critical database where we don’t have license of tuning pack. Hence we need to survive on statspack.

1) Take export backup of exiting perftstat user

2) Drop statspack

 @?/rdbms/admin/spdrop.sql;

3) Create stastpack

 @?/rdbms/admin/spcreate.sql;

4) Change stastpack snap level to level 6

BEGIN
statspack.modify_statspack_parameter(i_snap_level=>6, i_modify_parameter=>'true');
END;
/

select SNAP_ID, SNAP_LEVEL from STATS$SNAPSHOT;

5) Take manual snap

 exec PERFSTAT.statspack.snap;

6) Schedule statspack auto jobs for statspack snap ( Please note job id)

 @?/rdbms/admin/spauto.sql

7) Change snap interval to 30 min

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

set lines 180
col SCHEMA_USER for a20
col INTERVAL for a30
col WHAT for a30
select JOB, SCHEMA_USER, INTERVAL, BROKEN, WHAT from dba_jobs where JOB=826;

exec dbms_job.interval(826,'sysdate+(1/48)');

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

set lines 180
col SCHEMA_USER for a20
col INTERVAL for a30
col WHAT for a30
select JOB, SCHEMA_USER, INTERVAL, BROKEN, WHAT , 
to_char(next_date ,'DD-MON-YYYY:HH24:MI:SS') "next date" , 
failures from dba_jobs where JOB=826;

select name,snap_id,to_char(snap_time,'DD-MON-YYYY:HH24:MI:SS') "Date/Time" 
from stats$snapshot,v$database;

8) After 30 minutes verify snap interval working fine with 30 min and level 6

@?/rdbms/admin/spreport.sql

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:

Clearing an Enterprise manager agent that fails to upload

This is the standard set of actions that I go through when I have problems with an EM agent that a stop/start/upload does not resolve

The two types of errors I generally see are

EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet..

Starting agent …………………………… started but not ready.

Follow the steps below, which can be cut and pasted

export AGENT_HOME=/app/oracle/agent13c/agent_inst

Stop the agent

$AGENT_HOME/bin/emctl stop agent

Remove the old log files from AGENT_HOME/sysman/log

Delete any pending upload files from the agent home

rm -r $AGENT_HOME/sysman/emd/state/*

rm -r $AGENT_HOME/sysman/emd/upload/*

rm $AGENT_HOME/sysman/emd/lastupld.xml

rm $AGENT_HOME/sysman/emd/agntstmp.txt

rm $AGENT_HOME/sysman/emd/protocol.ini

Start the agent

$AGENT_HOME/bin/emctl start agent

Issue an agent clearstate from the agent home

$AGENT_HOME/bin/emctl clearstate agent

Force an upload to the OMS

$AGENT_HOME/bin/emctl upload agent

Finally I sometimes need to re-secure the agent

$AGENT_HOME/bin/emctl secure agent

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:

Clearing an Enterprise manager agent that fails to upload

This is the standard set of actions that I go through when I have problems with an EM agent that a stop/start/upload does not resolve

The two types of errors I generally see are

EMD upload error: uploadXMLFiles skipped :: OMS version not checked yet..

Starting agent …………………………… started but not ready.

Follow the steps below, which can be cut and pasted

export AGENT_HOME=/app/oracle/agent13c/agent_inst

Stop the agent

$AGENT_HOME/bin/emctl stop agent

Remove the old log files from AGENT_HOME/sysman/log

Delete any pending upload files from the agent home

rm -r $AGENT_HOME/sysman/emd/state/*

rm -r $AGENT_HOME/sysman/emd/upload/*

rm $AGENT_HOME/sysman/emd/lastupld.xml

rm $AGENT_HOME/sysman/emd/agntstmp.txt

rm $AGENT_HOME/sysman/emd/protocol.ini

Start the agent

$AGENT_HOME/bin/emctl start agent

Issue an agent clearstate from the agent home

$AGENT_HOME/bin/emctl clearstate agent

Force an upload to the OMS

$AGENT_HOME/bin/emctl upload agent

Finally I sometimes need to re-secure the agent

$AGENT_HOME/bin/emctl secure agent

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:

Tag Archives: oracle agent

APPLSYSPUB

TABLESPACE_NAME FILE_NAME

Adding Host Target

Using emctl command

Known Issues and Suggestions