Forum Stats

  • 3,780,545 Users
  • 2,254,408 Discussions
  • 7,879,377 Comments

Discussions

Categories

NOLOGGING tables and recovery of database (and standby)

rahulras
rahulras Member Posts: 876
edited Dec 15, 2010 8:07AM in General Database Discussions
Hi All,

I am a developer and has not done a recovery of a crashed production database. So, I would like to learn from other people's "practicle" experience.

It is not a big deal now a days about creating a NOLOGGING table and doing direct path load of data in it. What is the implication of this if we have to recover a database from disk failure (i.e. using backup and archived logs) ?

I know, I can read documentation, which will describe the theory (which I know).

But what exactly will happen when a DBA is recovering a crashed database which had few NOLOGGING tables and data loaded in these tables with nologging ?
Will the database be restored without any problems, just giving few messages / warnings about these nologging tables?
or the DBA will have to do some tedious work to restore that database?

Also, if we have a standby database maintained by transporting archived logs, what will be the case if the main (is it called primary) database has (media) crashed and we have to shift on the standby?
Let us say, I have all the files which were loaded (in nologging mode) on the primary database. So, getting the data loaded is not a problem. My question is, because of the NOLOGGING tables? will we have substantial difficulties getting the standby database to work?

Consider Oracle v10.1 and onwards for my question.

Thanks in advance.
· Share on TwitterShare on Facebook

Answers

  • 729338
    729338 Member Posts: 1,475
    Hi,

    Even if you have tables with NLLOGGING minimal logging with respect to table structure changes are recorded, so in the case of recovery, you can recover the database with nologging tables from the last full backup but data changes after the full backup can not be recovered on the nologging tables.

    Similar is the case with data guard where the data changes to the table will not be recorded and you have to load the data to the primary database after failover yourself.

    Regards
    · Share on TwitterShare on Facebook
  • jgarry
    jgarry Member Posts: 13,842
    rahulras wrote:
    Hi All,

    I am a developer and has not done a recovery of a crashed production database. So, I would like to learn from other people's "practicle" experience.

    It is not a big deal now a days about creating a NOLOGGING table and doing direct path load of data in it. What is the implication of this if we have to recover a database from disk failure (i.e. using backup and archived logs) ?

    I know, I can read documentation, which will describe the theory (which I know).

    But what exactly will happen when a DBA is recovering a crashed database which had few NOLOGGING tables and data loaded in these tables with nologging ?
    Will the database be restored without any problems, just giving few messages / warnings about these nologging tables?
    or the DBA will have to do some tedious work to restore that database?

    Also, if we have a standby database maintained by transporting archived logs, what will be the case if the main (is it called primary) database has (media) crashed and we have to shift on the standby?
    Let us say, I have all the files which were loaded (in nologging mode) on the primary database. So, getting the data loaded is not a problem. My question is, because of the NOLOGGING tables? will we have substantial difficulties getting the standby database to work?

    Consider Oracle v10.1 and onwards for my question.

    Thanks in advance.
    It can be a difficult question to answer, since many things you tell the db to be nologging actually are not. In fact, the dba can set [url http://www.orafaq.com/wiki/Nologging_and_force_logging]force logging.

    Yes, you have to assume a lot of work will result from nologging operations, no, other operations won't make be any more difficult.

    See http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5280714813869 for some illustration of possibilities and how to figure things out.
    · Share on TwitterShare on Facebook
  • Uwehesse-Oracle
    Uwehesse-Oracle Member Posts: 1,767
    >
    But what exactly will happen when a DBA is recovering a crashed database which had few NOLOGGING tables and data loaded in these tables with nologging ?
    >

    See a demonstration here:
    http://uhesse.wordpress.com/2009/09/15/how-do-nologging-operations-affect-recovery/

    >
    Also, if we have a standby database maintained by transporting archived logs, what will be the case if the main (is it called primary) database has (media) crashed and we have to shift on the standby?
    >

    You should avoid this situation by saying 
    SQL> alter database force logging;
    on the Primary - best before creation of the Standby. Else you will lose the objects created with nologging in case of a failover. If nologging operations have been done on the Primary already, but no damage there yet - better recreate the Standby after force logging on the Primary.

    Kind regards
    Uwe Hesse

    http://uhesse.wordpress.com
    · Share on TwitterShare on Facebook
  • rahulras
    rahulras Member Posts: 876
    Thanks for the replies guys.

    Let me put forward a more precise question.

    Our application has a bunch of tables which are used to process the end-of-day batch only. They are staging tables. Every night we load files (sometime file per table or sometimes multiple files per table) and then process that data to update our application data.
    At the moment, these staging tables which just hold the data from incoming files are in LOGGING mode.
    We have a standby database.
    Now, if we have to move to the standby database, I am not at all worried about the data in these tables. I can fill that up with the files we have (we keep the files for a week).

    What all I need is, all the tables, which are in LOGGING mode should be ready on the standby.
    The tables in NOLOGGING mode should have the up-to-date structure. I can fill up the data and rebuild the indexes.

    Does having few tables in NOLOGGING mode affect the recovery/standby of other tables (which are in LOGGING mode) ??

    If we create a tablespace only for NOLOGGING tables, will that help??

    Thanks
    · Share on TwitterShare on Facebook
  • rahulras
    rahulras Member Posts: 876
    Any thoughts?
    Please...
    · Share on TwitterShare on Facebook
  • Uwehesse-Oracle
    Uwehesse-Oracle Member Posts: 1,767
    After failover, if your intact tables (no NOLOGGING operations against them happend) are not dependent on the now corrupted tables (NOLOGGING operations against them happend) there is no problem - if you don't need to use the now corrupted tables, that is.

    But (big BUT), you must be sure that no other NOLOGGING operations took place on tables that now cannot be reproduced! If you cannot make that sure, your data will be lost inspite of the presence of a standby database. Do you want to bet your job on it?

    Technically, your idea is possible.

    Kind regards
    Uwe Hesse

    http://uhesse.wordpress.com
    · Share on TwitterShare on Facebook
  • rahulras
    rahulras Member Posts: 876
    Hi Uwe Hesse,

    You mentioned, "if your intaact tables are not dependant on now corrupted tables". What do you mean by 'dependant'?

    There are few LOGGING tables and few NOLOGGING tables? why should the NOLOGGING tables affect the recoverability of the LOGGING tables?
    I want to understand that bit.

    On the standby or after recovering the database (with backup and archive/redo logs), I want all my LOGGING tables up-to-date with data and for NOLOGGING tables, I only need the table structure in place. No need of data and indexes on NOLOGGING tables (indexes can be rebuilt, data can be inserted).
    What are the gaps in my idea/understanding ?
    Where are potential areas which can make this idea fall?

    Also, what will be the difference if I have saperate tablespace for NOLOGGING tables? and if I don't have saperate tablespace for NOLOGGING tables?

    Regards
    · Share on TwitterShare on Facebook
  • Uwehesse-Oracle
    Uwehesse-Oracle Member Posts: 1,767
    >
    You mentioned, "if your intaact tables are not dependant on now corrupted tables". What do you mean by 'dependant'?

    There are few LOGGING tables and few NOLOGGING tables? why should the NOLOGGING tables affect the recoverability of the LOGGING tables?
    I want to understand that bit.

    On the standby or after recovering the database (with backup and archive/redo logs), I want all my LOGGING tables up-to-date with data and for NOLOGGING tables, I only need the table structure in place. No need of data and indexes on NOLOGGING tables (indexes can be rebuilt, data can be inserted).
    What are the gaps in my idea/understanding ?
    Where are potential areas which can make this idea fall?

    Also, what will be the difference if I have saperate tablespace for NOLOGGING tables? and if I don't have saperate tablespace for NOLOGGING tables?
    >

    Tables reference other tables sometimes. There are things like joins and foreign keys. I don't know your data model - you do.

    If you are sure that you can reproduce everything done on the primary with NOLOGGING - go for it. But you will not get me recommend something that we officially do not recommend.

    If you run a Data Guard configuration with a Primary without FORCE LOGGING, you do that at your own risk. One of these risks is that you don't necessarily know each and every NOLOGGING operation that somebody somewhen will do.

    It doesn't matter whether you have a tablespace with NOLOGGING or not for the whole question. It doesn't matter why or where objects with NOLOGGING are loaded for the problem. It only matters that you are able to reproduce every change after a failover while your Standby Database is full of corrupted objects.

    If you ask me:
    1. Don't do it.
    2. If you insist: Test that before implementing it and see if it works after failover the way you thought.
    3. Don't do it even if your test was successful :-)

    Kind regards
    Uwe Hesse

    http://uhesse.wordpress.com
    · Share on TwitterShare on Facebook
  • rahulras
    rahulras Member Posts: 876
    Hi Uwe,

    So basically, if we are using Dataguard, NOLOGGING is not a good idea.
    What actually happens there? does the standby is not maintained at all? or only the NOLOGGING objects will be in a corrupt/unusable state?
    Will the standby have the DDL changes on NOLOGGING objects?
    Is it possible to find out such corrupt objects (if we are switching to the standby)?

    Also, even if we are FORCE LOGGING, if we truncate a table or partition, will we brake the standby?

    Thanks
    · Share on TwitterShare on Facebook
  • Uwehesse-Oracle
    Uwehesse-Oracle Member Posts: 1,767
    >
    So basically, if we are using Dataguard, NOLOGGING is not a good idea.
    >
    exactly.

    >
    What actually happens there? does the standby is not maintained at all?
    >
    No. It is maintained.

    >
    or only the NOLOGGING objects will be in a corrupt/unusable state?
    >
    yes.

    >
    Will the standby have the DDL changes on NOLOGGING objects?
    >
    yes.

    >
    Is it possible to find out such corrupt objects (if we are switching to the standby)?
    >
    You use them and get error messages.

    >
    Also, even if we are FORCE LOGGING, if we truncate a table or partition, will we brake the standby?
    >
    no. the truncate is reproduced on the standby.

    Kind regards
    Uwe Hesse

    http://uhesse.wordpress.com
    · Share on TwitterShare on Facebook
This discussion has been closed.