Controlling the Generation of Redo- Managing Control Files, Online Redo Logs,and Archivelogs

For some types of applications, you may know beforehand that you can easily re-create the data. An example might be a data warehouse environment in which you perform direct path inserts or use SQL*Loader to load data. In these scenarios you can turn off the generation of redo for direct path loading. You use the NOLOGGING clause to do this:

If you have an existing tablespace and want to alter its logging mode, use the ALTER TABLESPACE statement:

You can confirm the tablespace logging mode by querying the DBA_ TABLESPACES view:

The generation of redo logging cannot be suppressed for regular INSERT, UPDATE, and DELETE statements. For regular data manipulation language (DML) statements, the NOLOGGING clause is ignored. The NOLOGGING clause does apply, however, to the following types of DML:

• Direct path INSERT statements

• Direct path SQL*Loader

The NOLOGGING clause also applies to the following types of DDL statements:

• CREATE TABLE … AS SELECT (NOLOGGING affects only the initial create, not subsequent regular DML statements against the table)
• ALTER TABLE … MOVE

• ALTER TABLE … ADD/MERGE/SPLIT/MOVE/MODIFY PARTITION

• CREATE INDEX
• ALTER INDEX … REBUILD

• CREATE MATERIALIZED VIEW
• ALTER MATERIALIZED VIEW … MOVE

• CREATE MATERIALIZE VIEW LOG
• ALTER MATERIALIZED VIEW LOG … MOVE

Be aware that if the redo is not logged for a table or index and you have a media failure before the object is backed up, then you cannot recover the data; you receive an ORA-01578 error, indicating that there is logical corruption of the data.

Note You can also override the tablespace level of logging at the object level. For example, even if a tablespace is specified as NOLOGGING, you can create a table with the LOGGING clause.

Implementing Archivelog Mode

Recall from the discussions earlier in this chapter that archive redo logs are created only if your database is in archivelog mode. If you want to preserve your database transaction history to facilitate point-in-time and other types of recovery, you need to enable that mode.

In normal operation, changes to your data generate entries in the database redo log files. As each online redo log group fills up, a log switch is initiated. When a log switch occurs, the log-writer process stops writing to the most recently filled online redo log group and starts writing to a new online redo log group. The online redo log groups are written to in a round-robin fashion, meaning the contents of any given online redo log group will eventually be overwritten. Archivelog mode preserves redo data for the long term by employing an archiver background process to copy the contents of a filled online redo log to what is termed an archive redo log file. The trail of archive redo log files is crucial to your ability to recover the database with all the changes intact, right up to the precise point of failure.