TechSudo

Query the Oracle Alert Log using SQL commands

2009-01-01T11:45:00.003+05:30

Read the alert log with SQL

Published in Database Tips by cdawes Tuesday February 27, 2007

There are often times when you want to know what has been recorded in the database’s alert log, but may not want to or be able to log into the database server and view the file. An SQL interface to the alert log can offer several advantages – you don’t need an account on the database server and you can read the alert log from SQL and PLSQL programs (which can be incorporated into other programs). This posting will show you how to create a usable SQL interface to the alert log. Then using this interface you can write simple SQL to show the alert log entries for the past hour or all the alert log entries that resulted from errors so far this month.

External tables, introduced in database release 9.0 give us the ability to access the alert log using SQL. The IGNORE NULLS option to the LAST analytic function, introduced in database release 10g, allows us to really make use of the dates we find in the alert log.

In this example, we'll create all the schema objects in the SYSTEM schema and run the commands as user SYS (we can’t grant ourselves privileges, so we can’t run all of the statements as SYSTEM)

The first block of code below does three things, first is to create a directory for the bdump_dest – the directory location of the alert log. We call it appropriately BDumpDir. Next, we need to grant the read privilege to SYSTEM, so this directory can be used for the external table. Finally, we create the external table on the file alert_sid.log:



DECLARE

 BDumpDir  VARCHAR2(200);

 SID       VARCHAR2(16);

 ObjectExists EXCEPTION;

 PRAGMA EXCEPTION_INIT(ObjectExists,-955);

BEGIN

 -- get the bdump dir

 SELECT value

 INTO BDumpDir

 FROM v$parameter

 WHERE name='background_dump_dest';

 -- create the directory for the bdump dir

 EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY bdump_dir AS '''||

   BDumpDir||'''';

 -- grant the necessary privileges

 EXECUTE IMMEDIATE 'GRANT READ ON DIRECTORY bdump_dir TO system';

 -- get the SID

 SELECT instance_name INTO SID FROM v$instance;

 -- create the external table

 EXECUTE IMMEDIATE 'CREATE TABLE system.ALERT_LOG_EXTERNAL

   (TEXT VARCHAR2(255)

   ) ORGANIZATION EXTERNAL

   (TYPE ORACLE_LOADER

    DEFAULT DIRECTORY BDUMP_DIR

    ACCESS PARAMETERS

    (records delimited by newline

     nobadfile

     nologfile

    )

    LOCATION (''alert_'||SID||'.log'')

   )

   REJECT LIMIT UNLIMITED'

 ;

-- ignore ORA-955 errors (object already exists)

EXCEPTION WHEN ObjectExists THEN NULL;

END;

/

Now we can query the external table to read the alert log.



SELECT * FROM system.alert_log_external

WHERE ROWNUM < 20;
TEXT

------------------------------------------------------------------------------

Thu Jan 25 14:58:36 2007

Thread 1 advanced to log sequence 227

 Current log# 1 seq# 227 mem# 0: C:\INFO\ORACLE\ORADATA\ORA102\REDO01.LOG

Dump file c:\info\oracle\product\10.2.0\admin\ora102\bdump\alert_ora102.log

Fri Jan 26 09:18:36 2007

ORACLE V10.2.0.1.0 - Production vsnsta=0

vsnsql=14 vsnxtr=3

Windows XP Version V5.1 Service Pack 2

CPU                 : 2 - type 586

Process Affinity    : 0×00000000

Memory (Avail/Total): Ph:671M/2046M

Fri Jan 26 09:18:36 2007

Starting ORACLE instance (normal)

LICENSE_MAX_SESSION = 0

LICENSE_SESSIONS_WARNING = 0

Picked latch-free SCN scheme 2

Using LOG_ARCHIVE_DEST_10 parameter default value as USE_DB_RECOVERY_FILE_DEST

Autotune of undo retention is turned on.

IMODE=BR

19 rows selected.

This is a first step, but the log file isn’t really very usable - the entries are all timestamped, but the timestamp appears inline with the entries. We need to associate the timestamp with each line in the file, so we can query the alert log based on timestamp. We know the timestamp appears in a consistent format, so we can look for these timestamps and convert them to a date datatype. We create a function to do this for us, so we can trap and ignore any errors related to invalid dates. The alert_log_date function looks for strings in the expected timestamp format and converts them to a date. If the line in the alert log does not appear in a timestamp format (ORA-1846 is raised), we ignore it.



CREATE OR REPLACE FUNCTION system.alert_log_date( text IN VARCHAR2 )

 RETURN DATE

IS

 InvalidDate  EXCEPTION;

 PRAGMA EXCEPTION_INIT(InvalidDate, -1846);

BEGIN

 RETURN TO_DATE(text,'Dy Mon DD HH24:MI:SS YYYY'

   ,'NLS_DATE_LANGUAGE=AMERICAN');

EXCEPTION

 WHEN InvalidDate THEN RETURN NULL;

END;

/

So now, we can get the alert log entry and the timestamp, if present:



SELECT ROWNUM row_num ,system.alert_log_date(text) alert_date, text

FROM system.alert_log_external

WHERE ROWNUM < 20

/

This is progress, but we really want that datestamp carried down so each line of text is marked with the datestamp. This is where the IGNORE NULLS option to the LAST function comes into play. We use our working query, but then wrap another query around it.



SELECT row_num

     ,LAST_VALUE(alert_date IGNORE NULLS) OVER(ORDER BY row_num

          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) alert_date

     ,alert_text

FROM (SELECT ROWNUM row_num

           ,system.alert_log_date(text) alert_date

           ,text alert_text

     FROM system.alert_log_external

    )

WHERE ROWNUM < 20

/

So, now we get the line number, date stamp and text for each alert log entry. Hm.. It’s starting to look more like a good old table. Let’s add the starting line number for each alert log entry, so we can extract these entries easier. Again, we’ll build on the query we have by wrapping it with another. We get the starting line number based on the presence of a timestamp in the base table.



SELECT row_num

     ,LAST_VALUE(low_row_num IGNORE NULLS)

        OVER(ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING

        AND CURRENT ROW) start_row

     ,LAST_VALUE(alert_date  IGNORE NULLS)

        OVER(ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING

        AND CURRENT ROW) alert_date

     ,alert_text

FROM (SELECT ROWNUM row_num

           ,NVL2(system.alert_log_date(text),ROWNUM,NULL) low_row_num

           ,system.alert_log_date(text) alert_date

           ,text alert_text

     FROM system.alert_log_external

    )

WHERE ROWNUM < 20

/

Finally, we put this nested query into a view to hide its complexity and create a public synonym on it. You can decide who you allow to read the alert log by granting them SELECT on the alert_log view.



CREATE OR REPLACE FORCE VIEW system.alert_log as

SELECT row_num

     ,LAST_VALUE(low_row_num IGNORE NULLS)

        OVER(ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING

        AND CURRENT ROW) start_row

     ,LAST_VALUE(alert_date  IGNORE NULLS)

        OVER(ORDER BY row_num ROWS BETWEEN UNBOUNDED PRECEDING

        AND CURRENT ROW) alert_date

     ,alert_text

FROM (SELECT ROWNUM row_num

           ,NVL2(system.alert_log_date(text),ROWNUM,NULL) low_row_num

           ,system.alert_log_date(text) alert_date

           ,text alert_text

     FROM system.alert_log_external

    )

;
DECLARE

 ObjectExists EXCEPTION;

 PRAGMA EXCEPTION_INIT(ObjectExists,-955);

BEGIN

 EXECUTE IMMEDIATE 'CREATE PUBLIC SYNONYM alert_log FOR system.alert_log';

-- If the synonym exists, drop and recreate it

EXCEPTION WHEN ObjectExists THEN

 EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM alert_log';

 EXECUTE IMMEDIATE 'CREATE PUBLIC SYNONYM alert_log FOR system.alert_log';

END;

/

Now with our usable view built, we can look for alert log entries with SQL. For example; let’s see the alert log entries for the past hour:



SELECT row_num, alert_text

FROM alert_log

WHERE alert_date > SYSDATE - 1/24

/

Let’s see all the alert log entries for the past month that resulted from errors (contain the text ORA-).



SELECT row_num, alert_text

FROM alert_log

WHERE start_row IN (SELECT start_row

                   FROM alert_log

                   WHERE REGEXP_LIKE(alert_text,'ORA-')

                  )

AND alert_date > TRUNC(SYSDATE,'MON')

/

The next step, that I’ll leave to you, is to write the SQL interface to read these trace files…
Happy coding!

Curtsey - Database Specialist Vol.9 No.12 - Script of the Month - Article by Chip Dawes

Renaming and Relocating Datafiles for a Single Tablespace

2008-12-13T20:13:00.004+05:30

The post offers some procedures for renaming and relocating datafiles in a single tablespace. You must have the ALTER TABLESPACE system privilege to rename datafiles of a single tablespace.

Renaming Datafiles in a Single Tablespace

To rename datafiles from a single tablespace, complete the following steps:

Take the non-SYSTEM tablespace that contains the datafiles offline.
For example:
```
ALTER TABLESPACE users OFFLINE NORMAL;
```
Rename the datafiles using the operating system.
Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to change the filenames within the database.
For example, the following statement renames the datafiles /u02/oracle/rbdb1/user1.dbf and /u02/oracle/rbdb1/user2.dbf to/u02/oracle/rbdb1/users01.dbf and /u02/oracle/rbdb1/users02.dbf, respectively:
```
ALTER TABLESPACE users
RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf',
          '/u02/oracle/rbdb1/user2.dbf'
       TO '/u02/oracle/rbdb1/users01.dbf',
          '/u02/oracle/rbdb1/users02.dbf';
```
The new files must already exist; this statement does not create the files. Also, always provide complete filenames (including their paths) to properly identify the old and new datafiles. In particular, specify the old datafile name exactly as it appears in the DBA_DATA_FILES view of the data dictionary.
Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

Relocating and Renaming Datafiles in a Single Tablespace

Here is an example that illustrates the steps involved for relocating a datafile.

Assume the following conditions:

An open database has a tablespace named users that is made up of datafiles all located on the same disk.
The datafiles of the users tablespace are to be relocated to different and separate disk drives.
You are currently connected with administrator privileges to the open database.
You have a current backup of the database.

Complete the following steps:

Identify the datafile names of interest.

The following query of the data dictionary view DBA_DATA_FILES lists the datafile names and respective sizes (in bytes) of the users tablespace:

SELECT FILE_NAME, BYTES FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'USERS';

FILE_NAME                                  BYTES
------------------------------------------ ----------------
/U02/ORACLE/RBDB1/USERS01.DBF              102400000
/U02/ORACLE/RBDB1/USERS02.DBF              102400000

Take the tablespace containing the datafiles offline, or shut down the database and restart and mount it, leaving it closed. Either option closes the datafiles of the tablespace.
Copy the datafiles to their new locations and rename them using the operating system.
Note:
You can execute an operating system command to copy a file by using the SQL*Plus HOST command.
Rename the datafiles within Oracle.
The datafile pointers for the files that make up the users tablespace, recorded in the control file of the associated database, must now be changed from the old names to the new names.
If the tablespace is offline but the database is open, use the ALTER TABLESPACE ... RENAME DATAFILE statement. If the database is mounted but closed, use the ALTER DATABASE ... RENAME FILE statement.
```
ALTER TABLESPACE users
RENAME DATAFILE '/u02/oracle/rbdb1/users01.dbf',
          '/u02/oracle/rbdb1/users02.dbf'
       TO '/u03/oracle/rbdb1/users01.dbf',
          '/u04/oracle/rbdb1/users02.dbf';
```
Bring the tablespace online, or open the database.
If the users tablespace is offline and the database is open, bring the tablespace back online. If the database is mounted but closed, open the database.
Back up the database. After making any structural changes to a database, always perform an immediate and complete backup.

Copying a File on a Local File System

This section includes an example that uses the COPY_FILE procedure in the DBMS_FILE_TRANSFER package to copy a file on a local file system. The following example copies a binary file named db1.dat from the /usr/admin/source directory to the /usr/admin/destination directory as db1_copy.dat on a local file system:

In SQL*Plus, connect as an administrative user who can grant privileges and create directory objects using SQL.

Use the SQL command CREATE DIRECTORY to create a directory object for the directory from which you want to copy the file. A directory object is similar to an alias for the directory. For example, to create a directory object called SOURCE_DIR for the /usr/admin/source directory on your computer system, execute the following statement:

CREATE DIRECTORY SOURCE_DIR AS '/usr/admin/source';

Use the SQL command CREATE DIRECTORY to create a directory object for the directory into which you want to copy the binary file. For example, to create a directory object called DEST_DIR for the /usr/admin/destination directory on your computer system, execute the following statement:

CREATE DIRECTORY DEST_DIR AS '/usr/admin/destination';

Grant the required privileges to the user who will run the COPY_FILE procedure. In this example, the strmadmin user runs the procedure.

GRANT EXECUTE ON DBMS_FILE_TRANSFER TO strmadmin;

GRANT READ ON DIRECTORY source_dir TO strmadmin;

GRANT WRITE ON DIRECTORY dest_dir TO strmadmin;

Connect as strmadmin user:

CONNECT strmadmin/strmadminpw

Run the COPY_FILE procedure to copy the file:

     BEGIN
      DBMS_FILE_TRANSFER.COPY_FILE(
            source_directory_object       =>  'SOURCE_DIR',
            source_file_name              =>  'db1.dat',
            destination_directory_object  =>  'DEST_DIR',
            destination_file_name         =>  'db1_copy.dat');
   END;
   /

Oracle 10g New Feature

2008-12-13T20:08:00.002+05:30

Oracle 10g New Feature

Publish at Scribd or explore others: Computer Science language data

Digital Version of Oracle Magezine

2008-11-04T22:43:00.002+05:30

Oracle Magazine for Nov-Dec 2008

Find a Lot of Real Oracle DBAs Stuff

2008-09-18T19:32:00.000+05:30

Recently I found this one. Its a good blog giving lots of really useful tips to a DBA.

Frits Hoogland Weblog » Oracle EE

Installing Oracle 10g2 64bit on CentOS 4.3 x86_64 (AMD) | Eric's Agile Answers

2008-09-18T19:03:00.000+05:30

Installing Oracle 10g2 64bit on CentOS 4.3 x86_64 (AMD) | Eric's Agile Answers: "Installing Oracle 10g2 64bit on CentOS 4.3 x86_64 (AMD)

I suffered through quite a bit of trial and error during this installation because most of the posts on the issues encountered by those who blazed this trail before me failed to specify exactly which platform and versions they were tackling.

So, let me be clear. I started with the following:

* Oracle Database 10g Release 2 (10.2.0.1.0) for Linux x86-64
* CentOS 4.3 x86_64 (up2date sans kernel packages as of time of this post)
* jdk-1_5_0_07-linux-i586.rpm

An out of the box install on the platform above using these instructions from Oracle will fail during the linking stage of installation.
Here is the list of dependencies that got me over the hump:

compat-db-4.1.25.9 i386
compat-db-4.1.25.9 x86_64
compat-gcc-32-3.2.3.47.3 x86_64
compat-gcc-32-c++-3.2.3.47.3 x86_64
glibc-devel-2.3.4.2.19 i386
glibc-devel-2.3.4.2.19 x86_64
libaio-0.3.105.2 i386
libaio-0.3.105.2 x86_64
libaio-devel-0.3.105.2 x86_64
sysstat-5.0.5.7.rhel4 x86_64
xorg-x11-deprecated-libs-6.8.2.1.EL.13.25.1 i386
xorg-x11-deprecated-libs-6.8.2.1.EL.13.25.1 x86_64

(Note: Some of these are already installed on CentOS 4.3 but I wanted to show the x86_64 beside the i386 to drive home the point that you need both)
I installed all of the above using"