How to Monitor alert.log not from a file, but from a table

Monitor alert.log not from a file, but from a table

Summary
Instead of login in the unix and start doing vi and tail to see the contents of alert log you can store it in a table. Basically, you take advantage of the external tables feature in Oracle 9i and afterwards.

How to
Find the path for background_dump_dest:

SELECT NAME, VALUE FROM v$parameter WHERE NAME = 'background_dump_dest';

Create a directory for this path:

CREATE OR REPLACE DIRECTORY alert_log_dir1 AS '/path…';

Create the external table:

CREATE TABLE alert_log1 ( text VARCHAR2(4000) )
ORGANIZATION EXTERNAL (
TYPE oracle_loader
DEFAULT DIRECTORY alert_log_dir1
ACCESS PARAMETERS (
records delimited BY newline
nobadfile
nodiscardfile
nologfile
)
LOCATION(alert_log_dir1:'alert_SID.log')
)
REJECT LIMIT UNLIMITED;

Now query the last 200 lines of alert.log:

SELECT TEXT FROM
(SELECT ROWNUM ID, LPAD('---->',DECODE(YEAR,'2008',0,6)) || text text FROM
(SELECT ROWNUM, SUBSTR(text, 21,6) YEAR, text FROM (SELECT ROWNUM, text
FROM sys.ALERT_LOG1 --where text like '%INF%'
ORDER BY ROWNUM DESC)
WHERE ROWNUM < 200)) ORDER BY ID DESC;

View alert log from sql

In 11g, there is an easy way to read you database's alert log, using table X$DBGALERTEXT:

SELECT ORIGINATING_TIMESTAMP,
         MODULE_ID,
         PROCESS_ID,
         MESSAGE_TEXT
    FROM X$DBGALERTEXT
ORDER BY ORIGINATING_TIMESTAMP DESC;

How to take backup a view ?

View backup
==========
Select dbms_metadata.get_ddl(‘’,’’,’OWNER’) from dual;

set long 100000
spool MTL_SYS_ITEMS_VL.lst
select text from dba_views where VIEW_NAME='MTL_SYS_ITEMS_VL';
spool off

How to know "When was the table last modified" ?

I have another questions when is my table last modified and what are the ways i can view it.
Here you go,

1) Enabling audit on segment 
 

 1)Enable auditing on table
 2) Use dba_audit_trail to know insert and update statements

2) DBA_TAB_MODIFICATIONS to view the same, statistics_level set to All or Typical 
 
But here are the caevets

  1) If you are in 9i, the data will be pushed from memory to this view 
     every 15 mins
  2) Until 10gr1, the data will be pushed from memory to this view 
     every 3 hours
  3) From 10gr2 onwards this data will be pushed only when you 
     gather statistics on segment 
  4) or manually flush the monitoring info i.e using  
     DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO

Background:- Table/segment modifications used to be monitored using monitoring
on /off on segments on 9i, where this was removed and monitoring is
default in 10g and monitors all object modifications in mon_mod$_all
Another important aspect is, for example, on the below lines

 SQL> Select * from dba_tab_modifications where table_name='TEST';

 TABLE_NAME      INSERTS    UPDATES    DELETES trnc TIMESTAMP                    
 ------------ ---------- ---------- ---------- ---- -----------------             
 TEST              320          0          0 NO     26-Mar-2012 10:21     

The date is when the record is entered in the table mon_mod$_all not the exact date
when it modified in the original table.

Installing Oracle SOA Suite 11gR1 on Linux

Follow below steps to install Oracle SOA Suite

step 1) Installing oracle database .
Step 2) Install RCU (Repositary creation utility)
Step 3)Install JDK/JROCKIT
Step 4)Install Weblogic Server (10.3.6) i .e 11g
Step 5)Finally install SOA