How to migrate Oracle Alerts?


1] Using FNDLOAD utility:

Here first DOWNLOAD the custom alert from the source instance, copy the ldt file to the new instance and then UPLOAD to the destination instance.

Command to download:

FNDLOAD apps_user_name/apps_password 0 Y DOWNLOAD $ALR_TOP/patch/115/import/alr.lct my_file.ldt ALR_ALERTS APPLICATION_SHORT_NAME=’XXCUST’ ALERT_NAME=<Alert name to download>

Here are some additional parameters that can also be passed are: 

ALR_DISTRIBUTION_LISTS
ALR_LOOKUPS
ALR_MESSAGE_SYSTEMS
ALR_ORACLE_MAIL_ACCOUNTS
ALR_PROFILE_OPTIONS
ALR_PERIODIC_SETS

Command to upload:

FNDLOAD apps_user_name/apps_password 0 Y UPLOAD  $ALR_TOP/patch/115/import/alr.lct my_file.ldt – CUSTOM_MODE=FORCE

2] Using Alert Manager Responsibility:

You can use the Transfer Alert Definitions window to transfer an alert definition to another database, or make a copy of an existing alert. 

1. From the Menu, choose Tools > Transfer Alert.

2. In the Source Alert block of the Transfer Alert Definition window, enter:

* Application name associated with the alert to be transferred

* Alert name to be transferred

* Username and password for the database where the Alert to transfer resides

Include any necessary SQL*Net syntax to indicate where your database resides.
SQL*Net syntax may be used. The different databases need to be defined in the
$TNS_ADMIN/tnsnames.ora file.

Example of: username/ password@ SQL*Net_syntax

sysadmin/friday@T:testing:db2

sysadmin/friday@db2

Note: Database Links are not supported in Applications

3. Similarly, in the Destination Alert block, enter:

* Application name for the new Alert.
* Alert name for the new Alert.
* Username and password for the new database location.

4. Click Transfer button to complete the alert transfer.

NOTE: This process works for NEW Alerts that do not exist. If the Alert exists already in the Destination location, you will receive the error:
APP-ALR-04016: The alert <alert_name> already exists on the destination database account.

Reference:

  • Oracle Alert User’s Guide
  • How to transfer alert definition from One Instance to another instance using cmdline script [ID 400295.1]
Advertisements

How to design Periodic Alert to send emails?


1] Go to ‘Alert Manager’ responsibility and navigate Alert > Define.

  • Enter the name of the application that will own the alert
  • Enter a suitable Name of the alert (up to 50 characters), and give it a meaningful description (up to 240 characters).
  • Select a frequency for your periodic alert. You can choose from nine frequency options:
  1. On Demand
  2. On Day of the Month
  3. On Day of the Week
  4. Every N Calendar Days
  5. Every Day
  6. Every Other Day
  7. Every N Business Days
  8. Every Business Day
  9. Every Other Business Day
  • Choose ‘On Demand’ frequency when you are developing a periodic alert so that you can test your alert at any time you want. When you will sure that the alert is working fine, then you can change the frequency as per business need.
  • Depending on the frequency you choose in the previous step, the Start Time and End Time fields become enabled.  You may also specify the number of times within a 24-hour period that Oracle Alert checks your alert.
  • Specify a value in the Keep _ Days field to indicate the number of days of exceptions, actions, and response actions history you want to keep for this alert.
  • Specify a value in the End Date field if you want to disable your alert by a certain date.
  • Enter a SQL Select statement that retrieves all the data your alert needs to perform the actions you plan to define.

Note:

  • Your periodic alert Select statement must include an INTO clause that contains one output for each column selected by your Select statement.
  • Identify any inputs with a colon before the name, for example, :INPUT_NAME.
  • Identify any outputs with an ampersand (&) before the name, for example, &OUTPUT_NAME.
  • Do not use set operators in your Select statement.
  • You can use PL/SQL functions in your Select statement to fetch complex business logic.

Click on the ‘Verify’ button to check the select statement is correct.

Click on the ‘Run’ button to execute the Select statement.

Once you are satisfied with the SQL statement, save your work.

2] You can view all the input and output column details in ‘Alert Details’ Tab. The Alert Details window includes information such as which Application installations you want the alert to run against, what default values you want your inputs variables to use, and what additional characteristics you want your output variables to have.

3] After you define your alert you need to create the actions you want your alert to perform. For that click on the ‘Actions’ tab.

  • Enter a name (up to 80 characters) and description (up to 240 characters) for your alert action.
  • Select a level for your action: Detail, Summary, or No Exception.
  1. Detail action: performs once for each individual exception found
  2. Summary action: performs once for all exceptions found
  3. No exception action: performs when no exceptions are found.

4] Click on ‘Action Details’ tab to display the Action Details window.

  • Select the Action Type field as ‘Message’ if you want to send emails. Other action types are: Concurrent Program, Operating System Script and SQL Script.
  • Specify the electronic mail IDs of the recipients you want to send your message to in the To field.
  • If you list more than one recipient in any of these recipient fields, separate each recipient by a space, or a comma, or a combination of the two.
  • You can enter as many recipients as you want, up to 240 characters.
  • You can also enter alert outputs or response variables in any of the alert detail fields. Oracle Alert automatically substitutes the associated output value when checking the alert or the associated response variable value when reading the response.
  • Save your changes.

5] Click on ‘Action Sets’ tab in the main Alert Window.

  • Once you create your alert actions, you must include them in an enabled action set for Oracle Alert to perform during an alert check. An action set can include an unlimited number of actions and any combination of actions.
  • Enter a Sequence number that lets you order the execution of action sets during an alert check.
  • Give any suitable name and description.
  • Check Suppress Duplicates if you want Oracle Alert to suppress the actions in this action set if the exception found is a duplicate that occurred during the last alert check.

6] Click on ‘Action Set Details’ tab.

  • Go to ‘Members’ tab.
  • Find and attach the action that is created in Step 3.
  • Save the changes. 

7] Since it is an ‘On Demand’ periodic alert, we can run the alert at any time we want. For that go to Request > Check and enter the alert details. Then click on ‘Submit Request’.

  • This will fire one concurrent program which you can view by going through the navigation:  Request > View
  • View the Log and Output files of the concurrent program to find that the alert is fired successfully.

Done…check your mailbox and you should get emails that are sent from Oracle Alerts.

Oracle Alerts- Few FAQs


What are Oracle Alerts?

Oracle Alert facilitates the flow of information within your organization by letting you create entities called alerts to monitor your business information and to notify you of the information you want.

What they are capable of?

  • Keep you informed of database exception conditions, as they occur.
  • Take predefined actions when it finds exceptions in your database, without user intervention.
  • Take the actions you specify, depending upon your response to an alert message.
  • Perform routine database tasks automatically, according to the schedule you define.
  • Keep you informed of exception conditions through Email.

What are the types of Alert?

You can define one of two types of alerts: an event alert or a periodic alert.

  • An event alert immediately notifies you of activity in your database as it occurs.
  • A periodic alert, on the other hand, checks the database for information according to a schedule you define.

What are the alert actions available?

  • Sending the retrieved information to someone in an Email.
  • Run a concurrent program
  • Run an operating script
  • Run a SQL Statement script

What is On-Demand periodic alert?

It is a periodic alert with frequency as ‘On-Demand’. That means there is no specific period assigned to this alert and you can run this alert at any time you want using Request Periodic Alert Check form.

What you specify in the Alert Details window?

  • Default values for inputs variables
  • Additional characteristics for output variables
  • Application installations information you want the alert to run against

What are the Action Levels for your alert actions?

There are three types of level for your action: Detail, Summary and No Exception.

During an alert check, a detail action performs once for each individual exception found, a summary action performs once for all exceptions found, and a no exception action performs when no exceptions are found.

What is Action Set?

An action set can include an unlimited number of actions and any combination of actions and action groups for your alert. You can define as many action sets as you want for each alert. Oracle Alert executes the alert Select statement once for each action set you define. During each action set check, Oracle Alert executes each action set member in the sequence you specify.

What is Distribution List in Oracle Alert?

Distribution lists let you predefine a set of message recipients for use on many actions. If a recipient changes, you need only adjust it in the distribution list, not in the individual message actions.

What is Summary Threshold?

  • Oracle Alert can automatically determine whether to perform a detail or summary action, depending upon the number of exceptions found by the alert Select statement.
  • If you define a summary threshold, Oracle Alert performs a detail action for each exception found by the Select statement, but if the number of exceptions found exceeds the summary threshold, Oracle Alert performs a summary action.
  • You need to first define a detail and a summary action, include them in a threshold group, and then specify a summary threshold.

What is Periodic Set?

You can create a set of periodic alerts that Oracle Alert checks simultaneously. Use the Request Periodic Alert Check window to check the periodic set. Note that each periodic alert you include in a periodic set continues to run according to its individually defined frequency.

What is Response Processing in Alert?

Oracle Alert can process responses to your alert messages. When Oracle Alert receives a response to a specific alert message, it automatically performs the actions you define. Optionally, respondents can supply values that Oracle Alert uses to perform these actions. Response processing lets you automate routine user-entry transactions, streamlining your organization’s operations.

Note: To enable response processing, ensure that you have performed the required setup steps.

What is Action Escalation?

You can define a set of escalating detail actions, called an escalation group, for Oracle Alert to perform when it finds the same exceptions during consecutive alert checks. Oracle Alert performs a different detail action each time it encounters the same exception, so you can define actions that correspond to increasing severity levels.

How event alert works?

Once you define an event alert to monitor a table for inserts and/or updates, any insert or update to the table will trigger the event alert. When an insert or update to an event table occurs, Oracle Alert submits to the concurrent manager, a request to run a concurrent program called Check Event Alert (ALECTC). The concurrent manager runs this request according to its priority in the concurrent queue. When the request is run, Check Event Alert executes the alert Select statement. If the Select statement finds exceptions, Check Event Alert performs the actions defined in the enabled action set(s) for the alert. If the Select statement does not find any exceptions, Check Event Alert performs the No Exception actions in the enabled action set(s) for the alert.