Approach to apply PSU/CPU patches to Oracle E-Business Suite 12.2.x

As you know, Oracle is keep releasing the Critical Patch Update (CPU) / Patch Set Update (PSU) on every quarter.   In this article, we discuss about applying these patches to Oracle E-Business Suite (EBS) along with the Oracle MOS id references. 

In Oracle E-Business Suite (EBS) we have various tech stack components. It is important to keep update the critical patch update (CPU) to all these components.

The components are

1. Forms and Reports 
2. Fusion Middleware - Web Tier
3. Fusion Middleware - oracle_common Home
4. WebLogic. 
5. Database ( Do not get confused here; Database is also a component of EBS application.  This also needs to be considered while applying the patches to EBS environment)

First, it is better to apply patches the latest Quarterly patches to DB tier and then to Apps/Middle Tier.  For DB tier, I strongly advise applying the Patch Set Update (PSU) instead of Critical Patch Update (CPU).  PSU contains a lot of pro-active and Stabilization fixes for issues which you might have encountered already, or you are going to encounter in near future. These fixes will have the learnings from the issues arisen across the customers of Oracle.  PSU also includes Critical Patch Update (CPU) along with the pro-active stabilization fixes.  

Database Tier

Firstly, always try to apply the latest PSU patch available along with OJVM patch. Always latest. 

Secondly, run the EBS Technology Code level Checker (ETCC). The ETCC script can be downloaded from the Patch 17537119.  Always run the latest one while running ETCC. 

ETCC has two scripts. 

·         checkDBpatch.sh: This script needs to be run in DB tier.

·         checkMTpatch.sh: This script needs to be run in Apps tier. 

Run the checkDBpatch.sh script against the database and identify the patches needs to apply. 

Thirdly, in case if you are on Exadata or Superclusters, need to apply the Proactive Bundle Patch(BP). While downloading the patch, you need to select the right release. Consider the newly applied PSU patch release. 

Fourthly, apply the overlay patches. (Overlay patches are answers for conflict patches. Some patches don't co-exist with the latest PSU patches. In such scenarios, you need to consider patches which can co-exist with latest PSU patch). 

After that, Apply the Standard patches. Mostly the standard patches are one-off patches.  

Finally, Run the DB tier ETCC script and check the missing patches. And act on the missing patches. 

Middle Tier

For EBS, Oracle is only releasing Critical Patch Update (CPU) and not PSU (unlike Database). This is important for every Apps DBA to remember if they try to apply quarterly release patches to EBS components. 

Please refer the MOS id for the latest Oracle E-Business Suite Release 12 Critical Patch Update Knowledge Document (April 2018) (Doc ID 2369524.1)

It is important to apply the patches to above all tech stack components in EBS. During this phase, there are certain pre-requisites, we need to apply in case if the pre-requisites do not exist in the system. 

Apart from CPU patches to Oracle E-Business Suite, there are certain pro-active, Stabilization fixes as well for all components.  Apps DBAs should remember to apply all these pro-active fixes if they are considering applying the Critical Patch Update (CPU) to their environment.  The above Oracle Note has mentioned references of this, but not in detail. 

Please find the below MOS id for checking all the pro-active fixes for EBS components.

Oracle E-Business Suite Release 12.2: Consolidated List of Patches and Technology Bug Fixes (Doc ID 1594274.1)

While checking the patches for each component, there could be some conflict patches. Sometimes, both can’t co-exist. In such a case, try to get the combination patch. If you didn't get any clue from the Oracle support site, it is always better to raise an SR with Oracle to get the new patches. Most of the times, Oracle has already encountered this scenario for other customers and the solution might be available immediately for you.  

And one more thing, it is always better to run the EBS Technology Code level Checker (ETCC) before and after the above exercise. 

Post applying the patches, if you find any suggestion from checkMTpatch.sh, please do consider applying those missing patches as well. This is a kind of checker for you to identify if you have missed any patches. This ETCC script covers mostly all the pro-active fixes and not the Critical Patch Update. 

Both CPU and PSU are all are cumulative. You need not worry in case if you are skipping one release and applying the latest one. 

R12.2 : How to Stop/Start Services in all nodes using one single commmand ?

If you are EBS instance is R12.2.x and is configured with multiple nodes, then you can use the below commands to stop and start all services.

To stop the managed servers for all the Oracle E-Business Suite nodes:

adstpall.sh apps/apps_password -mode=allnodes

To Start/Restart the managed servers for all the Oracle E-Business Suite nodes:

adstrtal.sh apps/apps_password -mode=allnodes


(The above commands will ask for WebLogic password)

This command will internally connect all the nodes via ssh as you would have enabled ssh as part of R12.2.x pre-requisites setup. If you have not enabled ssh, you will not be able to apply patches in remoted nodes and it will get failed.

Skip AdminServer while stopping services

For some reasons, if you want stop all services except ADMIN Server for some activities, You can use the below script. This script will skip both Node manager and Adminserver going down.

adstpall.sh apps/apps -skipNM -skipAdmin

Most DBAs usually stop all services and then bring up ADMIN Server alone. This is waste of time as you can skip AdminServer while bringing down all services. 

How to get notification for Latest updates of Oracle MOS Docs?

As we work on critical projects like an upgrade or a migration, we review the Oracle procedures from support.oracle.com documents or note ids (MOS ids).

It is important for us to keep the focus on the latest update in this document.  This saves a lot of time in case we encounter some issue and start working with Oracle through SR. Eventually, Oracle has encountered the same issue before for other customer and has updated the document already with the action plan and chances that you might not notice.

To overcome this, it is better to get a notification in the form of mail to your inbox.  (I am assuming you do have Metalink account with valid CSI)
How can you enable the notification for Note id which you be interested?

It is simple.

1. First, mark the Note id (MOS id) as favorites. (Open the note id, and Click on the Star button at the beginning of the document)
2. Navigate to the Settings page and then traverse to "Hot Topics E-mail" 
3. Now choose the option from the section "Delivery options" and "Content to Include" as shown below. 

Whenever there is an update in the Document/Note ID which you marked as favorites, you will have a notification in your inbox.

Hold Concurrent Programs and Release Hold during Cut-Over or maintenance activities.

Hold programs during Cutover or maintenance activities. 

1. First shutdown the concurrent manager immediately after the downtime starts.
2. Wait for 10 or 15 mins.
3. Get the list of programs which are scheduled but not on hold.
   select * from fnd_concurrent_requests where phase_code='P' and hold_flag='N';
4. create a table to store the results from above query.

create table fnd_conc_req_hold as select * from fnd_concurrent_requests where phase_code='P' and hold_flag='N';

Release the programs once the Cutover or maintenance activities are over. 

Based on the number of requests in  fnd_conc_req_hold which are held manually, it can cause load on the Servers if there are more programs are held. Hence need to release these programs in phase manner.  Maybe 500 programs can be released at a time. Number 500 is a guesswork and you can choose according to your environment. 

Assume, If you have to hold 4000 requests.  We can release the programs in phase manner. 

UPDATE fnd_concurrent_requests

   SET hold_flag = 'N'

 WHERE phase_code = 'P'

   AND hold_flag = 'Y'

   AND request_id IN (SELECT request_id

                        FROM fnd_conc_req_hold

                       WHERE ROWNUM < :row_num

                      );

In the first run pass 500.

Second run, pass 1000.

Third run, pass 1500.

Increase by 500 in value by each run till the number of requests is stored in fnd_conc_req_hold.

Once all the programs are released, run the UPDATE query without WHERE clause. This UPDATE should run 0 rows.  That means, all the programs are released. 

Flashback database in Oracle 11g

You can use the FLASHBACK DATABASE command to rewind the database to a target time, SCN, or log sequence number. This command works by undoing changes made by Oracle Database to the data files that exist when you run the command. 

FLASHBACK DATABASE is usually much faster than a RESTORE operation followed by point-in-time recovery, because the time needed to perform FLASHBACK DATABASE depends on the number of changes made to the database since the desired flashback time. On the other hand, the time needed to do a traditional point-in-time recovery from restored backups depends on the size of the database.

The limitation of FLASHBACK is, it can not fix the physical failures; In a day to day scenario, this is unlikely. But, DBAs should be aware of it.

Is Flashback Enabled?

Use the below query to find out whether the FLASHBACK is enabled or not.

select flashback_on from v$database; 

How to Enable FLASHBACK?

ALTER DATABASE FLASHBACK ON;

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=5760; 

CREATE RESTORE POINT Mil_ref_point GUARANTEE FLASHBACK DATABASE;

DB_FLASHBACK_RETENTION_TARGET --> The Parameter (in minutes) defines the upper limit how far the database can be flashed back.

How do we FLASHBACK?

Please make sure the point of time, you need to restore.

1. shutdown immediate;
2. startup mount
3. FLASHBACK DATABASE TO RESTORE POINT Mil_ref_point;
4. alter database open resetlogs;

How to Disable the FLASHBACK?

ALTER DATABASE FLASHBACK OFF;

How to know the Oldest Snapshot the database can be flashback to? 

SELECT oldest_flashback_time FROM v$flashback_database_log;

Change Username in Oracle EBS

It's a kind of rare requirement to change the username in EBS.  This can be accomplished by using 2 methods.

1. Either by using Front end.
2. Or by using the Oracle Provided Package APPS.FND_USER_PKG.

It is not recommended to update any FND_USER column by using direct UPDATE SQL.  Oracle uses this column to maintain the foreign key relationship with few WF tables(For Ex: WF_LOCAL_USER_ROLES)

1. By using Front end.

           Login to ERP -->  System Administrator  --> Security --> User --> Define.  It launches Java form similar to the below screenshot.

            

Press F11, Enter the old username and search by using Ctrl + F11.  Change the User Name field to New value.  Don't forget to save. 

      2. By using FND_USER_PKG

begin

     fnd_user_pkg.change_user_name(x_old_user_name => 'oldusername', x_new_user_name => 'new_user_name');

     commit;

end;

/

This is really helpful when you are integrating EBS with OID or IDAM.