Install Weblogic PSU T5F1 (UPDATE 10.3.6.0.8)

Recently I was asked to apply the latest PSU on top of Oracle WebLogic Server (WLS) 11gR1 (10.3.6) on RHEL 6.

As you know, the patch set updates (PSUs) come with a "README.txt" file that explains how to apply the patches. You should always follow the patch note, but the basic format is typically as follows:

Before proceeding further, make sure to append the following entries into the "/home/oracle/.bash_profile" file if they are already not present.

export MW_HOME=/u01/app/oracle/product/fmw11g

export WLS_HOME=$MW_HOME/wlserver_10.3

export WL_HOME=$WLS_HOME

# Set to the appropriate JAVA_HOME.

#export JAVA_HOME=/usr/java/jdk1.6.0_33

export JAVA_HOME=/u01/app/oracle/jrockit-jdk1.6.0_45-R28.2.7-4.1.0

#export JAVA_HOME=/u01/app/oracle/jdk1.7.0_17

export PATH=$JAVA_HOME/bin:$PATH 

So, let's begin now

• Unzip the patch into the correct location.

$ mkdir -p $MW_HOME/utils/bsu/cache_dir

$ cd $MW_HOME/utils/bsu/cache_dir

$ unzip /<patch_top>/p18040640_1036_Generic.zip

Note: Make sure weblogic servers are down before applying the patch

$ . $WLS_HOME/server/bin/setWLSEnv.sh $ ./stopWebLogic.sh Stopping Weblogic Server... Initializing WebLogic Scripting Tool (WLST) ... Jython scans all the jar files it can find at first startup. Depending on the system, this process may take a few minutes to complete, and WLST may not return a prompt right away. Welcome to WebLogic Server Administration Scripting Shell Type help() for help on available commands Connecting to t3://abc.oracle.com:7001 with userid weblogic ... Successfully connected to Admin Server 'AdminServer' that belongs to domain 'WL_domain'. Warning: An insecure protocol was used to connect to the server. To ensure on-the-wire security, the SSL port or Admin port should be used instead. Shutting down the server AdminServer with force=false while connected to AdminServer ... Disconnected from weblogic server: AdminServer Exiting WebLogic Scripting Tool. Done Stopping Derby Server... To verify if all services are down 1.      netstat -na|grep 7001 2.      http://<hostname>:7001/console/login/LoginForm.jsp

•  Apply the patch

$ cd $MW_HOME/utils/bsu

$ ./bsu.sh -install -patch_download_dir=$MW_HOME/utils/bsu/cache_dir 

-patchlist=T5F1 –prod_dir=$MW_HOME/wlserver_10.3 –verbose

If you get conflicts, you may have to remove previous patches, before attempting to apply the patch again.

$ cd $MW_HOME/utils/bsu

$ ./bsu.sh -remove -patchlist=ABCD -prod_dir=$WLS_HOME

$ ./bsu.sh -install -patch_download_dir=$MW_HOME/utils/bsu/cache_dir -patchlist= T5F1 -prod_dir=$WLS_HOME

•  View Status of the Installed Patch

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

As you can see the patch is successfully applied, now restart all WebLogic servers.

•  Check the version.

$ . $WLS_HOME/server/bin/setWLSEnv.sh

$ java weblogic.version

WebLogic Server 10.3.6.0.8 PSU Patch for BUG18040640 

THU MARCH 27 15:54:42 IST 2014

WebLogic Server 10.3.6.0  Tue Nov 15 08:52:36 PST 2011 1441050

Use 'weblogic.version -verbose' to get subsystem information

Use 'weblogic.utils.Versions' to get version information for all modules.

HTH

Thanks for reading..!!

How to track sysadmin password in EBS R12 or track password changes

Recently, in one of our single node EBS R12 environment we were facing a issue where sysadmin password was getting changed intermittently. Every time when  DBA team tried to login with the old password they had for SYSADMIN user, it seems to be not working.

We knew that there is something fishy, as few days back we gave sysAdmin responsibilities to some of the users knowing the fact that this is high level of privilege for any user who is not involved in any system administration work.
We had to do this as per the client's business requirement.

You can run below select on FND_USERS table for the user to get the information about when records related to that user were updated..

select USER_NAME,USER_ID,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,LAST_LOGON_DATE from fnd_user where USER_NAME='SYSADMIN';

If you're looking for more information, you may have to enable Audit on the table in question. The audit trail functionality in Oracle apps allows Applications administrators to monitor data changes made on Oracle tables. Audit trail is not enabled by default as this means additional load on the server, so be very careful before making this change in production servers.
Let's say we have to enable audit for table FND_USERS, oracle will internally create an audit table FND_USER_A with selected columns for FND_USER_A. Now, all the inserts,updates done on FND_USER will be recorded in audit table.

• Enable audit for the instance

Set the value of profile Option, AuditTrail:Activate, on the Site level to Yes.
Navigation: Profile > System

• Run below select to the Application short name

select f1.TABLE_NAME, f1.USER_TABLE_NAME, f2.APPLICATION_SHORT_NAME, f2.APPLICATION_NAME from fnd_application_vl f2, FND_TABLES f1 where f1.TABLE_NAME='FND_USER' and f1.APPLICATION_ID=f2.APPLICATION_ID;

• Enable audit for the application

Enable audit for the application to which the base tables belong.
Navigation: Security > Audit > Install
Check the box named, Audit Enabled. Save and close the form.

• Add the table for auditing

The table has to be added in Oracle for auditing
Navigation: Security > AuditTrail > Tables
Query for the tables, FND_USERS and select the columns on which auditing will be done.

• Add the columns whatever you want to audit.
• Create AuditTrail Group

Go to Audit Trail -> Groups -> Query for Audit Setup Group

• Enable the 'Audit Setup Group'.
• Run audittrail update table.
• Add the Define an application user table under Audit Setup Group.
• Run the audit train update table again.
• Go to Audit Query Navigator ->Functional Groups-> Now you can see the Define an Application user table added under the audit setup
  group
• If the table is available as per the last step, now run the Audit trail report,you will be able to get the audit information.

Now, let see how can we SYSADMIN password in EBS R12.

There are three very simple steps of doing that.

1.  Connect to database as APPS user.
   
   SQL> conn apps
   Password:****
   Connected.
2. Create a Function to decrypt the encrypted password.
   
   CREATE OR REPLACE PACKAGE XXX_GET_PWD
   AS
      FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
         RETURN VARCHAR2;
   END XXX_GET_PWD;
   /
   
   CREATE OR REPLACE PACKAGE BODY XXX_GET_PWD
   AS
      FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
         RETURN VARCHAR2
      AS
         LANGUAGE JAVA
         NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt
   (java.lang.String,java.lang.String) return java.lang.String' ;
   END XXX_GET_PWD;
   /
3. Query to find out the username for the password.
   
   SELECT Usr.User_Name,
          Usr.Description,
          XXX_GET_PWD.Decrypt (
             (SELECT (SELECT XXX_GET_PWD.Decrypt (
                                Fnd_Web_Sec.Get_Guest_Username_Pwd,
                                Usertable.Encrypted_Foundation_Password)
                        FROM DUAL)
                        AS Apps_Password
                FROM applsys.Fnd_User Usertable
               WHERE Usertable.User_Name =
                        (SELECT SUBSTR (
                                   Fnd_Web_Sec.Get_Guest_Username_Pwd,
                                   1,
                                     INSTR (Fnd_Web_Sec.Get_Guest_Username_Pwd,
                                            '/')
                                   - 1)
                           FROM DUAL)),
             Usr.Encrypted_User_Password)
             Password
     FROM applsys.Fnd_User Usr
    WHERE Usr.User_Name = '&User_Name'; --SYSADMIN

 

HTH

Thanks for reading..!!

Purging Optimizer Stats

Space consumption continuously increases in the following tables:

• wri$_optstat_tab_history
• wri$_optstat_ind_history
• wri$_optstat_histhead_history

As you know, by default the MMON performs the automatic purge that removes all history older than:

     current time - statistics history retention

Default for Statistics History Retention is 31 days, which is too much and causes excessive tablespace growth due to retained statistics.

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

GET_STATS_HISTORY_RETENTION

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

            31

Fun Fact: MMON performs the purge of the optimizer stats history automatically. However it has an internal limit of 5 minutes to perform this job. If the operation takes more than 5 minutes, then it is aborted and stats not purged. No trace or alert message is reported

There are two ways to purge statistics:

1. This is the one recommended by oracle, but it takes too much time to purge

• Size of the table

SQL> select sum(bytes/1024/1024) MB,segment_name from dba_segments where segment_name='WRI$_OPTSTAT_HISTHEAD_HISTORY' group by segment_name;

        MB SEGMENT_NAME

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

     5,512 WRI$_OPTSTAT_HISTHEAD_HISTORY

•      Show the current history level

 SQL> select dbms_stats.get_stats_history_availability from dual;

GET_STATS_HISTORY_AVAILABILITY

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

19-MAY-14 02.31.58.726187000 AM -05:00

•   Retention period for keeping Stats

 SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

GET_STATS_HISTORY_RETENTION

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

                         31

•   Changed the retention period from 31 to 15

SQL> exec dbms_stats.alter_stats_history_retention(15);

PL/SQL procedure successfully completed.

SQL> select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

GET_STATS_HISTORY_RETENTION

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

                         15

SQL> select count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY;

  COUNT(1)

----------

  29092385

• Running purge stats older than 15 days

 SQL> exec DBMS_STATS.PURGE_STATS(SYSDATE-15);

•  Size of the table

 SQL> select sum(bytes/1024/1024) MB,segment_name from dba_segments where segment_name='WRI$_OPTSTAT_HISTHEAD_HISTORY' group by segment_name;

        MB SEGMENT_NAME

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

    16.125 WRI$_OPTSTAT_HISTHEAD_HISTORY

SQL> select count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY;

  COUNT(1)

----------

      1944

2. This the traditional way, and its much faster than the previous one.

• Let's backup these tables to store stats older than 14 days

create table SYS.WRI$_OPTSTAT_BAK as (select * from sys.wri$_optstat_histhead_history
where savtime > SYSDATE - 14);

create table SYS.WRI$_OPTSTAT_TAB_BAK as (select * from sys.wri$_optstat_tab_history
where savtime > SYSDATE - 14);

create table SYS.WRI$_OPTSTAT_IND_BAK as (select * from sys.wri$_optstat_ind_history
where savtime > SYSDATE - 14

•  Truncate the table with drop storage option

truncate table SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY drop storage;
truncate table SYS.WRI$_OPTSTAT_TAB_HISTORY drop storage;
truncate table SYS.WRI$_OPTSTAT_IND_HISTORY drop storage

• Insert the records back into the table which you dropped

insert into SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY (SELECT * FROM SYS.WRI$_OPTSTAT_BAK);

Issue: SQL> insert into SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY (SELECT * FROM SYS.WRI$_OPTSTAT_BAK); insert into SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY (SELECT * FROM SYS.WRI$_OPTSTAT_BAK)                 * ERROR at line 1: ORA-54013: INSERT operation disallowed on virtual columns Cause: Re-issue the statment without providing values for a virtual column Action: Attempted to insert values into a virtual column Try to run the first query like this: INSERT     INTO         SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY         (             OBJ#,             INTCOL#,             SAVTIME,             FLAGS,             NULL_CNT,             MINIMUM,             MAXIMUM,             DISTCNT,             DENSITY,             LOWVAL,             HIVAL,             AVGCLN,             SAMPLE_DISTCNT,             SAMPLE_SIZE,             TIMESTAMP#,             EXPRESSION,             COLNAME,             SPARE1,             SPARE2,             SPARE3,             SPARE4,             SPARE5,             SPARE6         )     (SELECT          OBJ#,          INTCOL#,          SAVTIME,          FLAGS,          NULL_CNT,          MINIMUM,          MAXIMUM,          DISTCNT,          DENSITY,          LOWVAL,          HIVAL,          AVGCLN,          SAMPLE_DISTCNT,          SAMPLE_SIZE,          TIMESTAMP#,          EXPRESSION,          COLNAME,          SPARE1,          SPARE2,          SPARE3,          SPARE4,          SPARE5,          SPARE6 FROM SYS.WRI$_OPTSTAT_BAK)

insert into SYS.WRI$_OPTSTAT_TAB_HISTORY (SELECT * FROM SYS.WRI$_OPTSTAT_TAB_BAK);
insert into SYS.WRI$_OPTSTAT_IND_HISTORY (SELECT * FROM SYS.WRI$_OPTSTAT_IND_BAK);

•   Drop the temporary backup tables.

drop table SYS.WRI$_OPTSTAT_BAK;
drop table SYS.WRI$_OPTSTAT_TAB_BAK;
drop table SYS.WRI$_OPTSTAT_IND_BAK;

SQL> select sum(bytes/1024/1024) MB,segment_name from dba_segments where segment_name='WRI$_OPTSTAT_HISTHEAD_HISTORY' group by segment_name;

        MB SEGMENT_NAME

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

    16.125 WRI$_OPTSTAT_HISTHEAD_HISTORY

SQL> select count(1) from WRI$_OPTSTAT_HISTHEAD_HISTORY;

  COUNT(1)

----------

      1944

HTH

Thanks for reading..!!