Archive logging; NOLOGGING for individual tables

• Article Type: General
• Product: Aleph
• Product Version: 19.01

Description:
I want to verify that all of my files which should have archive logging turned on do, in fact, have it turned on. How can I do this?

Resolution:
As described in these articles (supplied by Christine Moulen, MIT):
http://www.jlcomp.demon.co.uk/faq/nologging.html
http://www.sap-img.com/oracle-database/what-does-nologging-do-for-me.htm

Oracle never lets you really, completely turn off logging. It only lets you disable it for certain kinds of logging -- such as SQL Loader direct loads, which is what the ALEPH batch index regen jobs typically use. Specifying "Archiving Off" in util o/3 will disable the direct-load logging for all tables. But you also have the ability to disable this for an individual table by defining it with "NOLOGGING". (The table definitions can be found in the ./alephm/sql_tab directory.)

Once you are in production, logging should never be disabled for a "corpus" table. Disabling it for a temporary or index table, could possibly be justified. But you should realize that, if you disable logging of direct loads and run an index job which does direct load, and if you then need to recover that table before doing your next complete backup, you would unable to do so: It would need to be regenerated by re-running the batch job. This is a calculated risk which could be justified in some cases. (Especially if you were to do a complete back-up shortly after running the index job.) But generally it is our recommendation that once you are in production you have logging on for all tables and you leave logging on.

{Note: Starting in version 16, all of the ALEPH Oracle indexes (the "...create_index" files in sql_tab) are defined with NOLOGGING. This makes sense since the Oracle indexes can relatively quickly be rebuilt from the table itself.} This SQL command will show you all instances of LOGGING = NO: select table_name, owner from all_tables where logging = 'NO'; }