This question is often asked. The simple short answer is no, since redo logging is crucial for the database; it is not overhead and it is not a waste. You do need it, regardless of whether you believe you do or not. It is a fact of life, and it is the way the database works. If you turned off redo, then any temporary failure of disk drives, power, or a software crash would render the entire database unusable and unrecoverable. That said, however, there are some operations that can be done without generating redo log in some cases.

Note Oracle allows you to place your database into FORCE LOGGING mode. In that case, all operations are logged regardless if you specify NOLOGGING. The query SELECT FORCE_LOGGING FROM V$DATABASE will display if logging forced or not. This feature is in support of Data Guard, a disaster recovery feature of Oracle that relies on redo to maintain a standby database copy.

Setting NOLOGGING in SQL

Some SQL statements and operations support the use of a NOLOGGING clause. This does not mean that all operations against the object will be performed without generating redo, just that some very specific operations will generate significantly less redo than normal. Note that I said “significantly less redo,” not “no redo.” All operations will generate some redo—all data dictionary operations will be logged regardless of the logging mode. The amount of redo generated can be significantly less, however. For this example of the NOLOGGING clause, I ran the following in a database running in ARCHIVELOG mode with no force logging enabled:

$ sqlplus eoda/foo@PDB1
SQL> select log_mode, force_logging from v$database;
SQL> drop table t purge;Table dropped.
SQL> variable redo number
SQL> exec :redo := get_stat_val( ‘redo size’ );PL/SQL procedure successfully completed.
SQL> create table t as select * from all_objects; Table created.
SQL> exec dbms_output.put_line( (get_stat_val(‘redo size’)-:redo)
|| ‘ bytes of redo generated…’ ); 4487796 bytes of redo generated…
PL/SQL procedure successfully completed.

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:

SQL> drop table t;Table dropped.
SQL> variable redo number
SQL> exec :redo := get_stat_val( ‘redo size’ );PL/SQL procedure successfully completed.
SQL> create table t NOLOGGING as select * from all_objects; Table created.
SQL> 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. Additionally, if you run this test in a database that has force logging enabled, you will always get redo logged, regardless of the NOLOGGING clause.

These facts point 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 INSERTs, UPDATEs, DELETEs, and MERGEs will be logged. Other special operations, such as a direct path load using SQLLoader 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.

•\ After performing NOLOGGING operations in an ARCHIVELOG mode database, you must take a new baseline backup of the affected datafiles as soon as possible, in order to avoid losing the data created by the NOLOGGING operation due to media failure. Since the data created by the NOLOGGING operation is not in the redo log files, and is not yet in the backups, you have no way of recovering it!