Investigating Redo - Oracle Database Transactions and Locking Revealed - page 127

That CREATE TABLE generated about 4MB of redo information (your results will vary depending on how many
rows are inserted into table T ). We'll drop and re-create the table, in NOLOGGING mode this time:
EODA@ORA12CR1> drop table t;
Table dropped.
EODA@ORA12CR1> variable redo number
EODA@ORA12CR1> exec :redo := get_stat_val( 'redo size' );
PL/SQL procedure successfully completed.
EODA@ORA12CR1> create table t
2 NOLOGGING
3 as
4 select * from all_objects;
Table created.
EODA@ORA12CR1> exec dbms_output.put_line( (get_stat_val('redo size')-:redo) 
|| ' bytes of redo generated...' );
90108 bytes of redo generated...
PL/SQL procedure successfully completed.
This time, we generated only 90KB of redo. As you can see, this makes a tremendous difference—4MB of redo vs.
90KB. The 4MB written in the first example is a copy of the actual table data itself; it was written to the redo log when
the table was created without the NOLOGGING clause.
If you test this on a NOARCHIVELOG -mode database, you will not see any differences between the two. The CREATE
TABLE will not be logged, with the exception of the data dictionary modifications, in a NOARCHIVELOG -mode database.
That fact also points out a valuable tip: test your system in the mode it will be run in production, as the behavior
may be different . Your production system will be running in ARCHIVELOG mode; if you perform lots of operations that
generate redo in this mode, but not in NOARCHIVELOG mode, you'll want to discover this during testing, not during
rollout to the users!
Of course, it is now obvious that you will do everything you can with NOLOGGING , right? In fact, the answer is a
resounding no . You must use this mode very carefully, and only after discussing the issues with the person in charge
of backup and recovery. Let's say you create this table and it is now part of your application (e.g., you used a CREATE
TABLE AS SELECT NOLOGGING as part of an upgrade script). Your users modify this table over the course of the day.
That night, the disk that the table is on fails. “No problem,” the DBA says. “We are running in ARCHIVELOG mode and
we can perform media recovery.” The problem is, however, that the initially created table, since it was not logged, is
not recoverable from the archived redo log. This table is unrecoverable and this brings out the most important point
about NOLOGGING operations: they must be coordinated with your DBA and the system as a whole. If you use them and
others are not aware of that fact, you may compromise the ability of your DBA to recover your database fully after a
media failure. NOLOGGING operations must be used judiciously and carefully.
The important things to note about NOLOGGING operations are as follows:
• Some amount of redo will be generated , as a matter of fact. This redo is to protect the data
dictionary. There is no avoiding this at all. It could be of a significantly lesser amount than
before, but there will be some.
• NOLOGGING does not prevent redo from being generated by all subsequent operations . In the
preceding example, I did not create a table that is never logged. Only the single, individual
operation of creating the table was not logged. All subsequent “normal” operations such as
INSERT s, UPDATE s, DELETE s, and MERGE s will be logged. Other special operations, such as a
direct-path load using SQL*Loader, or a direct-path INSERT using the INSERT /*+ APPEND */
syntax, will not be logged (unless and until you ALTER the table and enable full logging again).
In general, however, the operations your application performs against this table will be logged .