Oracle Apps: Oracle Alerts
What are Oracle Alerts?
A: Oracle Alerts monitor your Database information and notify you when the condition that you have specified is found. You can define Alerts in any Oracle application or custom Oracle application. Some applications (Purchasing, for example) supply Alerts that Can simply be activated and used. There are two type of Alerts, Event and Periodic.
A: Oracle Alerts monitor your Database information and notify you when the condition that you have specified is found. You can define Alerts in any Oracle application or custom Oracle application. Some applications (Purchasing, for example) supply Alerts that Can simply be activated and used. There are two type of Alerts, Event and Periodic.
Event Based Alerts : These Alerts are fired/triggered based on some change in data in the database.
Ex: If u want to notify your manager when you create an item in the inventory you can use event based alerts. When you create an item in the inventory it will cretae a new record in mtl_system_items_b, here inserting a record in the table is an event so when ever a new record is inserted it will send the alert.In same alert you can also send the information related to that particular item
Periodic Alerts : These Alerts are triggered hourly,daily, weekly, monthly or yearly based on your input.
Ex: If you want to know list of items created on that day at the end of day you can use periodic alerts repeating periodically by single day.This alert is not based on any chages to database.this alert will notify you everyday regardless of data exists or not that means even if no items are created you wil get a blank notification.
Q: What types of actions can be generated when an Alert is triggered?
A: When an alert is triggered or the event is true, the alert can Email a Message, Submit a concurrent program request, Run a SQL statement Script or Run an operating system script.
A: When an alert is triggered or the event is true, the alert can Email a Message, Submit a concurrent program request, Run a SQL statement Script or Run an operating system script.
Q: Can I build an Alert to run with my custom applications or tables?
A: Event or Periodic Alerts can work with any custom application, as long as the application is properly registered within the Oracle Applications package.
A: Event or Periodic Alerts can work with any custom application, as long as the application is properly registered within the Oracle Applications package.
Q: Which Email packages work with Alerts?
A: Oracle Alert is designed to work with Oracle Office, Oracle Interoffice, UNIX Send mail, and VMS Mail.
A: Oracle Alert is designed to work with Oracle Office, Oracle Interoffice, UNIX Send mail, and VMS Mail.
Q: Can Alerts be triggered by other Tools? (i.e. other than Oracle Forms and concurrent programs)
A: Oracle Alerts can only be triggered from an application that has been registered in Oracle Applications. Alerts cannot be triggered via SQL updates or deletes to an Alert activated trigger.
A: Oracle Alerts can only be triggered from an application that has been registered in Oracle Applications. Alerts cannot be triggered via SQL updates or deletes to an Alert activated trigger.
Q: What is Response Processing?
A: Response processing is a component of Alerts which allows the recipients of an alert to reply with a message and have the applications take some action based on the response. Response Processing only works with Oracle Mail Products.
A: Response processing is a component of Alerts which allows the recipients of an alert to reply with a message and have the applications take some action based on the response. Response Processing only works with Oracle Mail Products.
Q: Do I need Oracle Applications to use Alerts?
Answer——No. The following are the only components required to use Oracle Alerts. The components must be certified versions for your hardware platform and operating system.
Answer——No. The following are the only components required to use Oracle Alerts. The components must be certified versions for your hardware platform and operating system.
-RDBMS
- SQL*PLUS
- FORMS
- ORACLE MAIL product (i.e. Oracle Office or InterOffice
- SQL*PLUS
- FORMS
- ORACLE MAIL product (i.e. Oracle Office or InterOffice
Q: How to create an Alert?
A:
1.Study your Business requirement and decide what type of alert you need either periodic alert or event based alert.
2. If you are going for periodic alert decide the frequency.
3. If you have chosen event based alert then find out on what event(insert,update) you want to fire the alert.
4. Decide what data need to be included in the alert.
5. Based on the data you want in the alert write a SELECT SQL statement to pull the data.
6. Create a distribution list grouping all the people to whom you want to send the alert.
2. If you are going for periodic alert decide the frequency.
3. If you have chosen event based alert then find out on what event(insert,update) you want to fire the alert.
4. Decide what data need to be included in the alert.
5. Based on the data you want in the alert write a SELECT SQL statement to pull the data.
6. Create a distribution list grouping all the people to whom you want to send the alert.
Creating and Testing a Event Alert in Oracle EBS / Apps
In following example I will create an Alert which will send email to specific email ID when ever any body update or create new employee in Employee Master.
Defining an Alert :
1. Responsibility –> Alert Manager.
2. Naviate to the Alerts Window.
Alert –> Define
3. Define a Period Alert:
Application = Human Resources
Name = xxscc_test1
Enable = Selected.
4. Event
Application = Human Resources
Table = PER_ALL_PEOPLE_F
5. Enter the following SQL statement.
select employee_number
into &emp_numfrom
per_all_people_f where rowid=:rowid
6. Verify, to check whether the syntax written is correct.
7. Save
8. Define Alert Actions:
Action Name : send_email_notification
8. Define Action Details:
Click on Action Details Button
TO : Give you email ID here. Note : You can also put here also bind variable with &email if that taken in select statement.
Subject : Give Subject of you Email.
Text : hello this is test email &emp_num
9- Define Action Sets
Click on Action Sets Button.
Action Set Name = Send Email Test
Go to Action Set Details –> Members.
Action = send_email_notification as shown bellow.
10- Define Operating Unit
Click on Alert Details Button from first Alert Window.
Navigate to Installation Tab.
Operating Unit = for me it is FS_OU
11- Save.
Now if you Update or Create New Employee you will received notification in given email ID.Oracle Database Upgrade – 11gR1 (11.1.0.6) to 11gR2 (11.2.0.1)
Oracle Database Upgrade – 11gR1 (11.1.0.6) to 11gR2 (11.2.0.1)
Oracle Database Upgrade - 11gR1 (11.1.0.6) to 11gR2 (11.2.0.1)11.1.0.1.0
11 - Major database release number
1 - Database maintenance release number
0 - Application server release number
1 - Component specific release number
0 - Platform specific release number
http://www.oracle.com/technology/software/products/database/index.html
1. Make sure the source environment is in stable state:
11.1.0.6 Startup Database
Alert log location: $ORACLE_BASE/diag/rdbms/aitmr93w/aitmr93w/trace
2. Download the 11gR2 software into the stage from where it needs to be installed.
3. Oracle Database Pre-installation Requirements
Checking the Hardware Requirements:-
The system must meet the following minimum hardware requirements:
1. Memory Requirements
1. At least 1 GB of RAM
# grep MemTotal /proc/meminfo
2. Determine the size of the configured swap space
# grep SwapTotal /proc/meminfo
The following table describes the relationship between installed RAM and the configured swap space requirement:
|-------------------------------------------------------|
|RAM | Swap Space |
|-------------------------------------------------------|
|Between 1 GB and 2 GB | 1.5 times the size of RAM |
|Between 2 GB and 16 GB | Equal to the size of RAM |
|More than 16 GB | 16 GB |
|-------------------------------------------------------|
3. To determine the available RAM and swap space, enter the following command:
# free
2. System Architecture
# uname -m
3. Disk Space Requirements
1. At least 1 GB of disk space in the /tmp directory
# df -h /tmp
If the free disk space available in the /tmp directory is less than what is required,
then complete one of the following steps:
a) Delete unnecessary files from the /tmp directory to meet the disk space
requirement.
b) Set the TMP and TMPDIR environment variables when setting the oracle
user’s environment.
2. To determine the amount of free disk space on the system, enter the following command:
# df -h
The following tables describe the disk space requirements for software files,
and data files for each installation type on Linux x86:
Installation Type Requirement for Software Files (GB)
Enterprise Edition 3.95
Standard Edition 3.88
Installation Type Disk Space for Data Files (GB)
Enterprise Edition 1.7
Standard Edition 1.5
Checking the Software Requirements:-
1. Operating System & Kernel Requirements
Oracle Enterprise Linux 4 Update 7
Oracle Enterprise Linux 5 Update 2
To determine the distribution and version of Linux installed, enter the following command:
# cat /proc/version
Alternatively, you can also enter the following command on some distributions of Linux:
# lsb_release -id
Oracle Enterprise Linux 4, and Red Hat Enterprise Linux 4:
2.6.9 or later
Oracle Enterprise Linux 5, and Red Hat Enterprise Linux 5:
2.6.18 or later
# uname -r
2. Package Requirements
On Linux x86: Package Requirements
rpm -q binutils-2.15.92.0.2
rpm -q compat-libstdc++-33-3.2.3
rpm -q elfutils-libelf-0.97
rpm -q elfutils-libelf-devel-0.97
rpm -q gcc-3.4.6
rpm -q gcc-c++-3.4.6
rpm -q glibc-2.3.4-2.41
rpm -q glibc-common-2.3.4
rpm -q glibc-devel-2.3.4
rpm -q glibc-headers-2.3.4
rpm -q libaio-devel-0.3.105
rpm -q libaio-0.3.105
rpm -q libgcc-3.4.6
rpm -q libstdc++-3.4.6
rpm -q libstdc++-devel-3.4.6
rpm -q make-3.80
rpm -q numactl-0.6.4.i386
rpm -q pdksh-5.2.14
rpm -q sysstat-5.0.5
rpm -q unixODBC-2.2.11
rpm -q unixODBC-devel-2.2.11
3. Compiler Requirements
Linux-PAM Library
Install the latest Linux-PAM (Pluggable Authentication Modules for Linux) library to
enable the system administrator to choose how applications authenticate users.
Setting the ORACLE_HOSTNAME Environment Variable
In Bourne, Bash, or Korn shell:
$ ORACLE_HOSTNAME=somehost.us.example.com
$ export ORACLE_HOSTNAME
4. Install 11gR2 (11.2.0.1) Software only
4.1 Entry into the "oratab" ...
/etc/oratab -- <ORACLE_SID>:<ORACLE_BASE>/db/11.1:N
Note: Kindly use local inventory than central as we wont be having access to use central
4.2 ./runInstaller -invPtrLoc $ORACLE_HOME/oraInst.loc &
Where the oraInst.loc should have the below entries
[ For installation on ems6678 otherwise modify it accordingly]
inst_group=dba;
inventory_loc=$ORACLE_BASE/db/oraInventory
Note: Click on "Ignore All" if there are any swap space failures
Installation Log: $ORACLE_BASE/db/oraInventory/logs/installActions2010-05-06_11-55-10PM.log
5. Pre-Upgrade steps:
5.1 Executing Pre-Upgrade Tool:
Before you start the upgrade process, to identify these checks.
5.1.1 Copy utlu112i.sql from 11gR2 OH i.e $ORACLE_BASE/db/11.2/rdbms/admin
to $HOME [ Assuming 11gR2 is Installed $ORACLE_BASE/db/11.2 ]
5.2.2 Login as SYSDBA
SQL> SPOOL $HOME/UpgDir/upgrade_info.log
SQL> @utlu112i.sql
SQL> SPOOL OFF
Review the log file and take necesary actions. Then continue with upgrade.
5.2 Correcting the Warnings [if any] found in above step
5.2.1 WARNING : Database is using an old time zone file version. After the upgrade,
patch the database time zone file version using DBMS_DST package to record latest time zone file version.
Sol: From Webiv Note: 815679.1
* For 11.1.0.6 and 11.1.0.7, after the upgrade to 11.2.0.1
use DBSM_DST to update to DSTv11 by following <<Note 977512.1>>
Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST
5.2.2 Database contains stale optimizer statistics.
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
[OR]
EXEC DBMS_STATS.DELETE_SCHEMA_STATS('SYS');
5.2.3 WARNING: --> SYSTEM schema default tablespace has been altered.
.... The SYSTEM schema default tablespace is currently set to TOOLS.
.... Prior to upgrading your database please reset the
.... SYSTEM schema default tablespace to SYSTEM using the command:
.... ALTER USER SYSTEM DEFAULT TABLESPACE SYSTEM;
5.2.4. WARNING:--> recycle bin in use.
.... Your recycle bin turned on.
.... It is REQUIRED
.... that the recycle bin is empty prior to upgrading
.... your database.
.... The command: PURGE DBA_RECYCLEBIN
.... must be executed immediately prior to executing your upgrade.
5.3 Shutdown 11gR1 Database
5.4 Configuring 11gR2 Instance
5.4.1 Source 11gR2 instance
5.4.2 Copy parameter file form pfile location to 11gR2/dbs
Change Parameters
------------------------
*.compatible='11.2.0'
shared_pool_size increase to 298MB [as suggested -- 325058560]
user_dump_dest -- REMOVE
background_dump_dest -- REMOVE
core_dump_dest -- REMOVE
6. Upgrading Database
6.1 Prepare Upgrade
6.1.1 cd $ORACLE_HOME/rdbms/admin
6.1.2 Login as SYSDBA
sqlplus '/ as sysdba'
startup upgrade
SPOOL upgrade.log
@catupgrd.sql
7. Post-Upgrade steps:
7.1 Post-Upgrade Tool
Run utlu112s.sql to display the results of the upgrade:
SQL> SPOOL $HOME/UpgDir/postupgrade_status.log
SQL> @utlu112s.sql
Oracle Database 11.2 Post-Upgrade Status Tool 05-07-2010 01:08:37
Component Status Version HH:MM:SS
Oracle Server VALID 11.2.0.1.0 00:14:38
Oracle Text VALID 11.2.0.1.0 00:00:36
Gathering Statistics 00:02:59
Total Upgrade Time: 00:18:15
PL/SQL procedure successfully completed.
7.2 Post-Upgrade Actions
Run catuppst.sql, located in the ORACLE_HOME/rdbms/admin directory, to
perform upgrade actions that do not require the database to be in UPGRADE mode:
SQL> SPOOL $HOME/UpgDir/postupgrade_status.log
SQL> @catuppst.sql
7.3 Compiling Invalid Objects:
Compiling Ivalid objects:
SQL> SPOOL $HOME/UpgDir/Compile_Invalid.log
SQL> @utlrp.sql
SQL> SPOOL OFF
7.4 Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) -- Webiv Note 977512.1
Oracle 11.2.0.1 has by default all RDBMS DST updates from DSTv1 to DSTv11 included in the software installation.
These files are found in $ORACLE_HOME/oracore/zoneinfo and have a prefix indicating the DST version.
7.4.1 check current RDBMS DST version and "DST UPGRADE STATUS".
conn / as sysdba
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
Check that the output gives
-- PROPERTY_NAME VALUE
-- ------------------------------ ------------------------------
-- DST_PRIMARY_TT_VERSION <the old DST version number>
-- DST_SECONDARY_TT_VERSION 0
-- DST_UPGRADE_STATE NONE
-- DST_PRIMARY_TT_VERSION should match the value found when selecting
7.4.2 SELECT version FROM v$timezone_file;
VERSION
----------
4
7.4.3 Do the actual RDBMS DST version update of the database
7.4.3.1 Change DB into Upgrade Mode
conn / as sysdba
shutdown immediate;
startup upgrade;
set serveroutput on
purge dba_recyclebin;
7.4.3.2 Clean used tables
TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
TRUNCATE TABLE sys.dst$affected_tables;
TRUNCATE TABLE sys.dst$error_table;
7.4.3.3 start upgrade window
EXEC DBMS_DST.BEGIN_UPGRADE(<the new DST version number>);
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(11);
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';
7.4.3.4 Restart the Database
shutdown immediate
startup
set serveroutput on
7.4.3.5 Upgrade the tables who need action
VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/
VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/
7.4.3.6 Verify the Timezone Upgrade
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
SELECT * FROM v$timezone_file;
8. Miscellaneous Database Checkups
8.1 Database, Datafile and Backup
select name,open_mode,log_mode,database_role,force_logging,flashback_on from v$database;
select * from v$recover_file;
select distinct status,count(*) from v$datafile group by status;
select name, RECOVER from v$datafile_header where RECOVER != 'NO';
select name, ERROR from v$datafile_header where ERROR is NOT NULL;
select name, ts# from v$datafile where upper(name) like '%MISSING%';
select NAME, FILE# from v$datafile where status='RECOVER';
select status,count(*) from v$backup group by status;
8.2 DBA Registry
set lines 150 pages 500
column COMP_NAME format a45
column version format a15
column status format a12
select comp_name,version,status from dba_registry;
8.3 DBA Registry History
set lines 150 pages 500
column action format a15
column namespace format a15
column comments format a20 wrap
column ACTION_TIME format a30
col BUNDLE_SERIES format a12
column version format a15
select * from dba_registry_history;
No comments:
Post a Comment