|
|
|
|
Oracle Application has so many modules that each module that you have licensed needs to be setup at the functional level for it to work and then tested for the functionality. The setup then goes in for a User Acceptance test. Once accepted it moves to production. Since the setups need to be done each time manually and is error prone, there are Object Migration tools that are available to migrate the required setups from one Application database to another. For Oracle Alerts though, oracle has its own transfer utility with which you can setup and test the Alerts in a test database and transfer the same to a QNA database and from there transfer to production.
I have broken this document into two sections. One that shows how to transfer Oracle Alerts and the other that talks about the basics and setting up a separate manager to run these Oracle Alerts to reduce the request clogs.
Transferring Oracle Alerts from One Database to Another:
To transfer Alerts from say QNA to PROD we need the Alert Manager GUI Responsibility for user who is transferring the alerts. We also need appropriate database links setup correctly for user Apps in the test or QNA databases. The underlying alert programs/triggers are transferred from the test/QNA databases through the links to the production database. This needs to be setup and tested first.
drop database link link_name;
create database link link_name connect to APPS identified by apps_password using ‘target_host’;
Check if the link works,
select name from v$database@link_name; or
select sysdate from dual@link_name;
The output should be the name of the target database or the sysdate should be displayed. Once this is tested, we move on to the Oracle Application to do the actual transfer of the Alert programs.
Logon to QNA Apps ( Test database):
Choose the Alert Manager Responsibility:
Navigate to Alert --> Define as show below.
Figure 1
Define would bring a screen as shown below. Query the Name of the Alert using the Name field.
Figure 2
From this screen you will drop down
the window SPECIAL --> Transfer in 01.7NCA and in
Release 11i TOOLS --> Transfer Alerts.
The screen below would pop up.
Enter the Source Alert --> Database Field as apps/password@QNA
Enter the Destination Alert --> Database Field as apps/password@PROD
Press Transfer.
Figure 3
This will not give any feed back. The only way to verify is to transfer again. The error screen will pop up to say that the Alert already exists.
Exit out of the QNA Application.
Logon to PROD Application:
Logon to Apps and choose the Alert Manager Responsibility.
Navigate to Alert --> Define as show above and the same screen will pop up.
Query for the Name filed and the Alert that was transferred would appear.
Make sure to,
Verify the Start time Field.
Verify the End Time Field.
Verify the Check Interval and input the Time provided by the Developer.
Check the ENABLED check box.
| Note: The Enabled Check Box gets dropped in the transfer process and needs to be checked manually in production for the Alert to fire off. |
Firing Alerts with Its Own Manager
In this section we are focusing on creating a new concurrent manager for Alerts. Oracle Applications default installation does not provide a separate manager and related processes for executing the Alerts. It instead uses the Standard Concurrent Manager for doing the both. This leads to the Standard Concurrent Manager queuing up all the jobs. The goal is to reduce the queue up and load on the Standard Concurrent Manager and improve performance and maintenance by having a separate Concurrent Manager to execute these Alerts.
Oracle Applications have two important alerts: Event Alerts and Periodic Alerts.
Alerts in Oracle Applications are basically database triggers that are fired at the application level. All the triggers are owned by the user APPS and not by the respective schema owners. These triggers are not activated when the intended action happens while using another utility, for example, SQLPLUS. When a user tries to manipulate data at the database level using any of the utilities other than the Oracle Application or Concurrent programs at the command level, these triggers are not active and will lead to the corruption of data. These triggers are triggered only when the event happens using Oracle Applications or Application related command level executables.
Apart from the above, alerts are also called exception handlers. Let's say a database event has to be monitored and mail has to be sent, or an event occurs and an OS level script has to be executed, etc.
Event alerts are the ones that are fired when an event like an insert, update or delete happens. Periodic alerts are the ones that happen at a predefined set interval or are fired at scheduled intervals.
When an event like insert, update, or delete happens and the related event or periodic alerts are triggered, these alerts get into the same queue of the Standard Manager and use the processes that have been allocated for the Standard Manager. This leads to a clog and other concurrent programs submitted, which are important but have the same priority, would not be executed till the queue is processed sequentially. Since the alerts are fired much faster and their relevant programs are usually in the queue more often, this loads the Standard Manager and its processes. The events to be fired, which might be important also, get clogged in the queue waiting for their turn. And if some program gets terminated or gets into a loop, all the other processes gets slowed down. Sometimes none of them get executed in time. This is very relevant if you are using the OE Module of Oracle Applications.
It would be a better idea to move all the triggered or fired programs to be executed from a separate manager and allocate it with its own processes to do the job, with its own defined priority and sleep time. To do this we have to first define a New Manager and attach him to the Oracle Application Object Library and program library executable and register the same.
Let's create a new manger and its own related process for handling the alerts.
Login to the Oracle Application screen using the SYSADMIN responsibility. Go to Default SYSADMIN (Figure 4) Screen -> Manager -> Define.
Figure 4
Enter a name for the manager. This can be any name. I have entered the manager field with ALERT MANAGER. Now input a short name, which again can be any name like ALERTMGR. Move to the Application field and point your cursor there. Then pull down the Edit and you will find the "List Of Values”. Click on the same and you will get the LOV screen with values as show in the figure above. Choose the APPLICATION OBJECT LIBRARY and click the OK button. Next move the cursor and fill the description field with what the manager is supposed to do, for example, ‘New Manager to Run Alerts’ and then move the cursor to the TYPE field. Pull down the LOV and choose the type as Concurrent Manager.
Figure 5
Next enter the Program library information. To do this move the cursor to the Program Library name field and pull down the Edit and you will find the "List Of Values”. Click on the same and you will get the LOV screen with values. Choose the FNDLIBR and click the OK button. The required information of Name and the Application fields are filled in in the Program Library section of the form as shown below in Figure 6.
The Cache size has a value of 1, as stated in the Oracle Manual. See the excerpt below:
Enter a value of 1 when defining a manager that runs long, time-consuming jobs, and a value of 3 or 4 for managers that run small, quick jobs
(For more information Refer Oracle Application Manual)
Figure 6
Next, get the specialization rules set for this Alert Manager that was defined. Click on the specialization button. You will get a screen like the one below in Figure 7.
With the specialization rule we can include the relevant programs to be run using this manger. Hence we choose the fields Include/Exclude with ‘Include’, Type field with ‘Program’ and place the cursor on the Application field. Pull down the LOV and choose Oracle Alerts, and click OK.
Figure 7
Next move the cursor to the Name field and pull down the LOV. The LOV screen will look like Figure 8 below. Choose the ‘Check Event Alert Program’ name and click OK. Do the same again to include the Program ‘Check Periodic Alert’ in the next record.
Figure 8
Once the two programs that are to be run by this new Alert Manager defined by us are included in the Specialization Rule, we have to exclude them from the Standard Manager so that the Standard Manager does not fire the same.
Next exclude the two programs from being run by the Standard Manager. Go to Standard Manager Specialization Rules and Exclude the same as shown in Figure 9.
Figure 9
Save the changes and move to the Work Shifts. With the work shifts screen we can define the number of processes the manager can bring up by default and the sleep seconds it takes to refresh. The processes are Operating system processes and each process can run a concurrent request.
| Note: The processes takes up memory. The number of processes we defined here will bring up that many Library executables and hence the amount of memory available should be checked before defining the same. We can check these processes in NT by using the Task Manager and clicking on the Processes button. On Unix we can use the ps –ef | grep FNDLIBR. |
The screen in Figure 10 below shows the work shift screen defined with 2 processes for Alert Manager. The sleep seconds is defined as the number of seconds this manger would wait to check for pending requests to execute next. This can be set to a lower value if the number of events fired is pretty high. In one of the sites running Auto Invoice the change to this parameter to a lesser value allowed for a reasonably better performance.
Figure 10
Save all the work done and exit out of the Application. The Concurrent Manager has to be bounced to bring the new manager up. Login to the Application and go to the Administer Concurrent manager screen. It should show you the new manager defined with the processes allocated. The actual and target say that the number of processes allocated were all up and running. The screen in Figure 11 below shows the same.
Figure 11
|
Note: The number of processes defined for the Standard Manager is 8. The number of processes defined for Alert Manager is 2, so we should have 10 FNDLIBR processes when we check the same in NT using the Task Manager or using the ps –ef | grep FNDLIBR in Unix. Also, remember that some of the other Concurrent Managers would also use the standard manager program executable FNDLIBR. That would add up to the processes that would be displayed using the above command. |
Remember that excluding and including the concurrent programs should be done during off peak hours or during no database or application activity. Since the memory is allocated for these processes and which get resident, we should make sure that we have enough free memory to add the processes accordingly and not end up with page outs and swapping.
Also remember to try this on a test or a development database before implementing on the production system.
Ref:
Oracle Application System Admin Manual.
Oracle Alert – Technical Reference Manual
Note: The above as usual has nothing to do with the company I work for and are entirely my own views. Please test before you use this on your production. However, I have used the same on Oracle Application version 10.7SC, 10.7NCA and 11.0 3 on NT, HP-UX, SUN, AIX and DG UNIX platforms.
Shankar Govindan is an Oracle Certified 7, 8 and 8I Professional and Microsoft Certified Professional working as an Oracle Applications DBA for CNF, Portland, Oregon.
