Diasable redo log for the table

Posted on 2006-03-20
Last Modified: 2008-02-01
I have a table which is heavily inserted with data periodically and goes to the order of 5G over the period of 20days and then it is cleaned by trigger. So given point of time it holds only last 20days worth of data.
Question is I want to disable the redo log for this table so that when I recover the database I can skip this table as the data is not critical to recover and we can improve the recovery time by skipping it.

What is the best way to do it, I know there is NOARCHIVELOG option I can set, but according to ORacle documentation that one still generates logs and not reliable.

Any ideas?
Question by:shasta123
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    you cannot disable redo log at all.
    NOARCHIVELOG only avoids dumping out the redo log into .arc files (for recovery), but that's all.

    the only thing you can do is ensure that the redo logs are big enough, that you have a couple of them, and that they are located on the fastest disks on your sever.
    LVL 2

    Assisted Solution

    You can disable redo log for a table by changing it to nologging mode:

    C:\downloads\script>sqlplus "/ as sysdba"

    SQL*Plus: Release - Production on Mon Mar 20 15:47:02 2006

    Copyright (c) 1982, 2005, Oracle.  All rights reserved.

    Connected to:
    Oracle Database 10g Enterprise Edition Release - Production
    With the Partitioning, OLAP and Data Mining options

    SQL> create table amittest as select * from v$session;

    Table created.

    SQL> alter table amittest nologging;

    Table altered.


    LVL 16

    Accepted Solution

    This will help, but for it to be effective, the tablespace it is in also must be a 'nologging' tablespace (otherwise the tablespace logs). And not all DML will operate in loglogging mode, there are a lot of restrictions in getting this to work (we struggled with this on our data warehouse). In fact, updates and deletes to NOT honor nologging.

    For inserts, you need to force the insert to be a direct-path insert by using the
    insert /*+ append */  hint on the insert.

    Per the manual:
    Only the following operations support the NOLOGGING mode for DML:
    Direct-path INSERT (serial or parallel)
    Direct Loader (SQL*Loader)

    I'm afraid for this delete/cleanup, there isn't much you can do. You COULD consider doing a direct-path insert to a new table of all the columns you want, then doing a truncate of the table, and rename the tables. This won't help if you have referential integrity constraints pointing to the table...
    LVL 34

    Assisted Solution

    When you say that after 20 days the data in this table is: "cleaned by trigger" how is that done, with a "delete..." command?  If so, and if you have Oracle's Enterprise Edition, you could partition this table, then simply drop an entire partition when the data is no longer needed.  That will go much faster and generate *MUCH* less redo, at least for the delete.

    You also indicated that NOARCHIVELOG is unreliable.  I disagree, it may not be as thorough as you would like, since NOARCHIVELOG only applies to certain types of inserts and/or data loads, but I have always found it to be reliable in those cases.

    Author Comment

    I have not forgotten your replies, trying to digest them all.

    Author Comment

    Sorry for the late reply, but our table is not loaded once in bulk so that i could use the direct path serial load.

    It is a table which is inserted by a server process periodically? How do i skip the redo for this table so that for recovery i can skip this table ?
    LVL 34

    Expert Comment

    Does your "server process" that does the inserts use this syntax: "insert into [table_name] ... select ... from [some other table(s) or view(s)]"?

    If not, and you can't use a direct path load either, then no, you cannot disable redo logiing for this table.

    What does your "server process" look like?  Is it a stored procedure?
    LVL 16

    Expert Comment

    Also, if you are only inserting one or two records, you wouldn't want to use direct path. It only makes sense if you are really doing a bulk insert.

    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.

    Suggested Solutions

    Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
    Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    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.

    737 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

    21 Experts available now in Live!

    Get 1:1 Help Now