Weblogic Patch - BSU Commands

To apply the patch:

./bsu.sh -install -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -patchlist=AAAA prod_dir=$FMW_HOME/wlserver_10.3

To remove/rollback the patch:

./bsu.sh -remove -patchlist=BBBB -prod_dir=$FMW_HOME/wlserver_10.3

To find the applied patch:

./bdu.sh -prod_dir=$FMW_HOME/wlserver_10.3 -status=applied -verbose -view

To find the downloaded patch:

./bsu.sh prod_dir=$FMW_HOME/wlserver_10.3 -patch_download_dir=$FMW_HOME/utils/bsu/cache_dir -status=downloaded -view -verbose 

Oacore Service Not Starting

Error: While starting oacore service we are getting below error

Caused By: javax.security.auth.login.FailedLoginException: [Security:090303]Authentication Failed: User weblogic weblogic.security.providers.authentication.LDAPAtnDelegateException: [Security:090295]caught unexpected exception

Solution:

Remove the ldap folder from below location

                       

$DOMAIN_HOME/servers/oacore_server1/data

Retry the issue

Password Changing Steps for Operations User

How to change the password for operations user in Oracle EBS.

When we are trying to change the operations user password using FNDCPASS command, we will get the below error notifications.

$FNDCPASS apps/apps 0 Y system/manager USER operations  password
Log filename : L7746964.log

Report filename : O7746964.out

$cat L7746964.log
+---------------------------------------------------------------------------+
Application Object Library: Version : 12.0.0

Copyright (c) 1979, 1999, Oracle Corporation. All rights reserved.

 module:
+---------------------------------------------------------------------------+

Current system time is 09-OCT-2018 15:04:42

+---------------------------------------------------------------------------+

This password is not managed by Oracle Applications so it can not be changed here.

+---------------------------------------------------------------------------+
Concurrent request completed
Current system time is 09-OCT-2018 15:04:42

+---------------------------------------------------------------------------+

Solution:

We have to change the operations user password through EBS Application url. 
System Administrator -> User -> Define

EBS 12.1.3 Login Page not working

Issue:

On : 12.1.3 version:

When attempting to login to ebs application, the following error occurs:

Error:

You have encountered an unexpected error. Please contact the System Administrator for assistance.

The Error Detail page displays:

Exception Details. 
oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: SQL error during statement preparation.  Statement: select /*+ ORDERED PUSH_SUBQ USE_NL (WN WL WIT) index(WN WF_NOTIFICATIONS_N1)*/ WN.NOTIFICATION_ID,

## Detail 0 ##
java.sql.SQLSyntaxErrorException: ORA-00904: "WN"."SENT_DATE": invalid identifier

Solution:

Follow the below steps to fix the issue.

1. Stop the application tier services.

2. Use adodfcmp utility to alert the table:

cd $FND_TOP/patch/115/odf

adodfcmp odffile=afwf.odf mode=tables changedb=Yes \

userid=applsys/apps touser=apps/apps priv_schema=SYSTEM/manager

3. Restart the application tier services.
4. Retest the issue.

Recreate the appsutil/scripts directory

Issue: The $ORACLE_HOME/appsutil directory or subdirectories of appsutil are missing.

Solution:

To implement the solution, please update the RDBMS ORACLE_HOME file system with AutoConfig files from the AppsTier by performing the following steps exactly:

1. On the Application Tier (as the APPLMGR user):

2. Log in to the APPL_TOP environment and source the environment file

3. Create appsutil.zip file: "perl /bin/admkappsutil.pl"
(This will create appsutil.zip in $APPL_TOP/admin/out/appsutil.zip)

4. Copy or FTP the appsutil.zip file to the RDBMS $ORACLE_HOME

5. On the Database Tier (as the ORACLE user):
$ cd $ORACLE_HOME

$ unzip -o appsutil.zip

6. Generate the Database Context File:

Context File Creation on Linux:

$ cd $ORACLE_HOME

$ *.env

$ cd $ORACLE_HOME/appsutil/bin

$ perl adbldxml.pl tier=db appsuser= appspasswd=

$ cd $ORACLE_HOME/appsutil/bin


$ adconfig.sh contextfile= appspass=

Find OPP Log File Using Concurrent Request ID

Select logfile_name from fnd_concurrent_requests where request_id = 11111111;


Select 
fcpp.concurrent_request_id req_id, 
fcp.node_name, 
fcp.logfile_name 
FROM 
fnd_conc_pp_actions fcpp, 
fnd_concurrent_processes fcp 
where 
fcpp.processor_id = fcp.concurrent_process_id 
AND 
fcpp.action_type = 6 
AND 
fcpp.concurrent_request_id = 1111111;

How to Create a External Table in Oracle

CREATE TABLE "APPS"."TEST_FILESYSTEM"
   ( "FILESYSTEM" VARCHAR2(40 BYTE),
"TOTAL_GB" VARCHAR2(10 BYTE),
"USED_GB" VARCHAR2(10 BYTE),
"AVAILABLE_GB" VARCHAR2(10 BYTE),
"USED_PERCENTAGE" VARCHAR2(10 BYTE),
"MOUNTED_ON" VARCHAR2(25 BYTE)
   )
   ORGANIZATION EXTERNAL
    ( TYPE ORACLE_LOADER
      DEFAULT DIRECTORY "DUMP"
      ACCESS PARAMETERS
      ( records delimited by newline
fields terminated by ' '
    )
      LOCATION
       ( 'Test_filesystem.txt'
       )
    )
   REJECT LIMIT UNLIMITED ;

Note:

* Dump - Directory should be available in dba_directory table.
* Location - txt file should be available in specific location or Path
* use All_external_tables view to check the external table information.

Gather Statistics on Oracle 11g

Schema Statistics:

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('USERNAME',DBMS_STATS.AUTO_SAMPLE_SIZE);

Table Statistics:

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMP'); 

Index Statistics:


EXEC DBMS_STATS.gather_index_stats('SCOTT', 'EMPLOYEES_PK');

Prepare Phase Failed due to Free Space

Error:

$NE_BASE/log/adop/17/prepare_timestamp/TEST_hostname/TXK_SANITY_filesystemcheck/df.out
Execute SYSTEM command : df /ebs1/app/oracle/TEST/fs2
"df" command output:                      ******** *********** ******** 97% /ebs1


MINIMIUM SPACE REQUIRED (in GB): 25
FREE SPACE AVAILABLE    (in GB): 15.1493682861328
Space check FAILED.

[ERROR] Minimum file system space required 25 GB while you have 15.1493682861328 GB

Solution:

Remove old files in filesystem. Minimum 25GB should be available in server for running prepare phase

Prepare Phase Failed ERROR - CLONE-20368 Domain pack failed

Error:

SEVERE : May 15, 2016 12:07:48 - ERROR - CLONE-20368  Domain pack failed.
SEVERE : May 15, 2016 12:07:48 - CAUSE - CLONE-20368  Make sure that values specified are correct.
SEVERE : May 15, 2016 12:07:48 - ACTION - CLONE-20368  Check the T2P logs for more details.
java.lang.Exception: Domain pack failed.

CLONE2016*****.log

<< read domain from "$FMW_Home/user_projects/domains/$DOMAIN_NAME"
>>  succeed: read domain from "$FMW_Home/user_projects/domains//$DOMAIN_NAME"
<< write template to "$INST_TOP/logs/appl/rgf/TXK/CLONINGCLIENT*********************/packed_domain.jar"
CFGFWK-60550:  Script execution aborted. The script may contain an error.
Java heap space
FINE : May 15, 2016 12:07:48 - [CloneLogger:unSetSystemOutErrToCloneLogger] Invoking unsetSystemOutErrToCloneLogger 1
FINE : May 15, 2016 12:07:48 - [CloneLogger:unSetSystemOutErrToCloneLogger] Invoking unsetSystemOutErrToCloneLogger 2
INFO : May 15, 2016 12:07:48 - CLONE-21096   Process complete in 3275466 milli sec.
INFO : May 15, 2016 12:07:48 - CLONE-21031   Time taken to add all the cloners to archive was "3,275" seconds.
INFO : May 15, 2016 12:07:48 - CLONE-21006   Total time taken by T2P process was 3,275 seconds.


Solution:

Let's make a small change in your WLS_HOME/common/bin/pack.sh to raise the used heap to 1Gb.

1) Copy WLS_HOME/common/bin/pack.sh to WLS_HOME/common/bin/packBACKUP.sh

2) Edit WLS_HOME/common/bin/pack.sh

Go to the empty line 95 and insert MEM_ARGS="-Xms1024m -Xmx1024m"

Change this section from:  

cd "${WL_HOME}/common/lib"
JVM_ARGS="-Dprod.props.file='${WL_HOME}/.product.properties' ${MEM_ARGS} ${CONFIG_JVM_ARGS}"

To this:

cd "${WL_HOME}/common/lib"
MEM_ARGS="-Xms1024m -Xmx1024m"
JVM_ARGS="-Dprod.props.file='${WL_HOME}/.product.properties' ${MEM_ARGS} ${CONFIG_JVM_ARGS}"

3) Save WLS_HOME/common/bin/pack.sh and exit

4) Retry the prepare phase

R12.2 Post Clone Issue No space left on device

Error: writeTemplate() failed. Do dumpStack() to see details.
Problem invoking WLST - Traceback (innermost last):
  File "/tmp/CLONINGCLIENT************/tmp**********.py", line 239, in ?
  File "/tmp/WLSTOfflineIni************.py", line 74, in writeTemplate
        at com.oracle.cie.domain.script.jython.CommandExceptionHandler.handleException(CommandExceptionHandler.java:51)
        at com.oracle.cie.domain.script.jython.WLScriptContext.handleException(WLScriptContext.java:1538)
        at com.oracle.cie.domain.script.jython.WLScriptContext.writeTemplate(WLScriptContext.java:831)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:606)

com.oracle.cie.domain.script.jython.WLSTException: com.oracle.cie.domain.script.jython.WLSTException: com.oracle.cie.domain.script.ScriptException: java.io.IOException: No space left on device

Solution:
Delete old files and create a free space in filesystem

Apachectl startssl: execing httpd Syntax error

$INST_TOP/ora/10.1.3/Apache/Apache/bin/apachectl startssl: execing httpd
Syntax error on line 17 of $INST_TOP/ora/10.1.3/Apache/Apache/conf/mod_osso.conf:
Unable to open $INST_TOP/ora/10.1.3/Apache/Apache/conf/osso/TEST_hostname_osso.conf.

Solution:

1. Backup the $CONTEXT_FILE
2. Edit the parameter  "s_mod_osso_conf_comment" in the $CONTEXT_FILE using editContext utility,  or manually using a text editor
3. Set the value for the above parameter to "#"
4. Save the changes and run Autoconfig
5. Start the Apache service to re-test the issue

i.e.
<mod_osso_conf_comment oa_var="s_mod_osso_conf_comment">#</mod_osso_conf_comment>

Status failur - Test failed : IO Error : Got minus one from a read call

Error while connecting SQL developer

"Status failur - Test failed : IO Error : Got minus one from a read call"

Resolution:

1. Open sqlnet.ora from $ORACLE_HOME/network/admin location

2. Change the setting to:
tcp.validate_node = no


3. Now try reconnecting to the database from SQL Developer and you should be able to connect it successfully this time.

APPS User Account locked in Oracle EBS

During the post clone in EBS Application, Removed the existing node entry and try to ran autoconfig on database tier. and its completed with error,

In log file, 

WARNING:     Exception occurred: java.sql.SQLException: ORA-28000: the account is locked

SQL> select username,account_status from dba_users where username like 'APPS';

USERNAME                       ACCOUNT_STATUS

------------------------------ --------------------------------

APPS                           LOCKED

SQL> alter user apps account unlock;

User altered.

$ sqlplus apps/apps

SQL*Plus: Release 11.2.0.4.0 on Sat Mar 10 01:11:11 2017

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

ERROR:

ORA-28000: the account is locked

Even after unlock the apps user also, same issue exists.

Solution:

SQL> select * from dba_profiles where RESOURCE_NAME like 'FAILED%';

PROFILE                        RESOURCE_NAME                    RESOURCE

------------------------------ -------------------------------- --------

LIMIT

----------------------------------------

DEFAULT                        FAILED_LOGIN_ATTEMPTS            PASSWORD

3

AD_PATCH_MONITOR_PROFILE       FAILED_LOGIN_ATTEMPTS            PASSWORD

3

EM_OAM_MONITOR_PROFILE         FAILED_LOGIN_ATTEMPTS            PASSWORD

3

SQL> alter profile DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

Profile altered.

After changing the profile value as unlimited, we are able to connect apps user and auto config also completed successfully.

VNC Settings

Stopping the VNC Server:

$ vncserver -kill :1

Killing Xvnc process ID 2695

Changing the VNC Password:

$ vncpasswd

Password:
Verify:

Setting VNC Server Number or Screen Size:

$ vncserver :5

$ vncserver -geometry 800x600

How to update EBS XML Publisher Temporary Directory

SQL> select value from apps.XDO_CONFIG_VALUES WHERE  property_code = 'SYSTEM_TEMP_DIR';

VALUE
--------------------------------------------------------------------------------
/usr/tmp


SQL> update apps.XDO_CONFIG_VALUES set value='/u01/temp' WHERE  property_code = 'SYSTEM_TEMP_DIR';

1 row updated.

SQL> commit;

Commit complete.

SQL> select value from apps.XDO_CONFIG_VALUES WHERE  property_code = 'SYSTEM_TEMP_DIR';

VALUE
--------------------------------------------------------------------------------
/u01/temp

Add Responsibility to FND User

-- ----------------------------------------------------------
-- Add Responsibility to Oracle FND User
-- -----------------------------------------------------------
DECLARE
    lc_user_name                        VARCHAR2(100)    := 'KUMARV';
    lc_resp_appl_short_name   VARCHAR2(100)    := 'FND';
    lc_responsibility_key          VARCHAR2(100)    := 'APPLICATION_DEVELOPER';
    lc_security_group_key        VARCHAR2(100)    := 'STANDARD';
    ld_resp_start_date                DATE                        := TO_DATE('27-FEB-2018');
    ld_resp_end_date                 DATE                        := NULL;

BEGIN
     fnd_user_pkg.addresp
     (   username           => lc_user_name,
        resp_app             => lc_resp_appl_short_name,
        resp_key             => lc_responsibility_key,
        security_group  => lc_security_group_key,
        description         => NULL,
        start_date           => ld_resp_start_date,
        end_date            => ld_resp_end_date
    );

 COMMIT;

EXCEPTION
            WHEN OTHERS THEN
                        ROLLBACK;
                        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

SHOW ERR;

Find Application Short Name for All Module

SELECT fa.application_id           "Application ID",
       fat.application_name        "Application Name",
       fa.application_short_name   "Application Short Name",
       fa.basepath                 "Basepath"
  FROM fnd_application     fa,
       fnd_application_tl  fat
 WHERE fa.application_id = fat.application_id
   AND fat.language      = USERENV('LANG')
   -- AND fat.application_name = 'Payables'  -- <change it>
 ORDER BY fat.application_name;

Decrypt the Weblogic Password

1. Create a script decrypt_password.py in $DOMAIN_HOME/security directory and paste the following code into it:

from weblogic.security.internal import *
from weblogic.security.internal.encryption import *
encryptionService = SerializedSystemIni.getEncryptionService(".")
clearOrEncryptService = ClearOrEncryptedService(encryptionService)

# Take encrypt password from user
pwd = raw_input("Paste encrypted password ({AES}fk9EK...): ")

# Delete unnecessary escape characters
preppwd = pwd.replace("\\", "")

# Display password
print "Decrypted string is: " + clearOrEncryptService.decrypt(preppwd)

2. Set domain environment variables

source $DOMAIN_HOME/bin/setDomainEnv.sh

3. Get encrypted password, in this example from boot.properties file of AdminServer

#Username:
grep username $DOMAIN_HOME/servers/AdminServer/security/boot.properties | sed -e "s/^username=\(.*\)/\1/"

#Password:
grep password $DOMAIN_HOME/servers/AdminServer/security/boot.properties | sed -e "s/^password=\(.*\)/\1/"


4. Navigate to $DOMAIN_HOME/security directory and run the following command to start decryption:

cd $DOMAIN_HOME/security

java weblogic.WLST decrypt_password.py


Initializing WebLogic Scripting Tool (WLST) ...

Welcome to WebLogic Server Administration Scripting Shell

Type help() for help on available commands

Please enter encrypted password (Eg. {AES}fk9EK...): {AES}jkIkkdh693dsyLt+DrKUfNcXryuHKLJD76*SXnPqnl5oo\=
Decrypted string is: welcome01

Query to find All responsibilities of a user

SELECT fu.user_name                "User Name",
       frt.responsibility_name     "Responsibility Name",
       furg.start_date             "Start Date",
       furg.end_date               "End Date",      
       fr.responsibility_key       "Responsibility Key",
       fa.application_short_name   "Application Short Name"
  FROM fnd_user_resp_groups_direct        furg,
       applsys.fnd_user                   fu,
       applsys.fnd_responsibility_tl      frt,
       applsys.fnd_responsibility         fr,
       applsys.fnd_application_tl         fat,
       applsys.fnd_application            fa
 WHERE furg.user_id             =  fu.user_id
   AND furg.responsibility_id   =  frt.responsibility_id
   AND fr.responsibility_id     =  frt.responsibility_id
   AND fa.application_id        =  fat.application_id
   AND fr.application_id        =  fat.application_id
   AND frt.language             =  USERENV('LANG')
   AND UPPER(fu.user_name)      =  UPPER('KUMAR')  -- <change it>
   ORDER BY frt.responsibility_name;