Alert log alertSID.log

Posted on 2006-03-27
Last Modified: 2008-01-16
I see my alert log on 10g to be one big huge file. HOw do i enable rotation on it? so that it is archived?
Question by:shasta123

    Author Comment

    Also does the alert log tracks the DML as well as DDL on all the database objects by giving the timestamp?
    LVL 9

    Assisted Solution

    i don know any possibility to reuse alertlog not to grow in oracle, but you can do it in operating system. make a script, which will check file size, and when it will be bigger rename it to some "bak". Oracle will create init.ora again ...
    LVL 29

    Expert Comment


    On Unix renaming the file may not work, you would need to copy (ifyou want to preserve old log) and truncate the log:

    cp alertSID.log alert_SID.bak
    LVL 34

    Expert Comment

    The rename works fine in UNIX.  There is no need to truncate the file.  The instance does not keep the file open.
    LVL 34

    Accepted Solution

    You can use a simple file rename command in an O/S script to rename the alert.log file in most O/S's  (Unix, Linux and Windows, at least) since Oracle does not keep the alert.log file locked, and if it doesn't see one when it tries to write the next entry, it will create a new one.  This script can be scheduled to run automatically in the O/S job scheduler at midnight for example, to rename the file each night.

    No, the alert.log file does not contain audits of either DML or most DDL statements.  A few DDL statements are logged in the alert.log file, but usually only those that affect "major" parts of the database, like: datafiles, tablespaces, log files, etc.

    If you want to audit DML and/or DDL activity, you will either have to turn on Oracle's auditing mechanism (but if you do, be preparared for a *LARGE* volume of data that you will have to manage!) or write triggers to capture this activity to logging table(s) or ASCII log files.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
    How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to take different types of Oracle backups using RMAN.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now