REDO RECORDS

 How to Control the Writing of Redo Records?

For some database operations, it is possible to control whether redo records are generated. Suppressing redo generation can improve performance, and may be appropriate for easily recoverable operations. This might include a CREATE TABLE...AS SELECT statement, where the operation can be repeated if there is a database or instance failure. Without redo, no media recovery is possible.2

Specify the NOLOGGING clause in the CREATE TABLESPACE statement if you wish to suppress redo when these operations are performed for objects within the tablespace. If you do not include this clause, or specify LOGGING instead, then redo is generated when changes are made to objects in the tablespace. Redo is never generated for temporary segments or in temporary tablespaces, regardless of the logging attribute.

The logging attribute specified at the tablespace level is the default attribute for objects created within the tablespace. You can override this default logging attribute by specifying LOGGING or NOLOGGING at the schema object level; for example, in a CREATE TABLE statement.

In the case where you have a standby database, specifying NOLOGGING causes problems with the availability and accuracy of the standby database. To overcome this problem, you can specify FORCE LOGGING mode. When you include the FORCE LOGGING clause in the CREATE TABLESPACE statement, you force the generation of redo records for all operations that make changes to objects in a tablespace. This overrides any specification made at the object level.
If you transport a tablespace that is in FORCE LOGGING mode to another database, the new tablespace will not maintain the FORCE LOGGING mode.

Direct loading into the database tables also avoids generating redo.

(1) APPEND hint to bypass the freelists and raise the high-water mark for the table and reduce redo
(2) Nologging clause to avoid redo generation

Ex: Insert /*+ append */ into
Nologging
Select * from source_table;

Let the source table be external table and destination stage table. Your job is done with the lowest possible redo generation.
This is suggested as, in general, no developer creates the table with NOLOGGING clause and lets the defaults to take over the object behavior. Then in many places the scripts are never verified before implementing in production.

Many stored procedures and functions are seen inserting tons of data with no concern
for the redo generated.

The data warehouses are created with noarchivelog mode and the exports are preferred as the backup solution. This is bad and the days of using exports as backup solution have gone.

The data block corruptions and data file corruptions and the vulnerabilities in net worked storage management systems have to be the best reason for going at the least once in a week clod backup supported by online backup by to a tape which requires to be duplicated and stored off the site. No database should managed with noarchivelog mode.

(3) Using SQL Loader direct method.
(4) Using PLSQL to bulk load data with low redo.
(5) The Streams capture process cannot capture NOLOGGING or UNRECOVERABLE operations because there are no redo records generated.
(6) Frequent commits to the database as far as possible or wherever it is possible to reduce the undo usage and redo also
(7) PARTITION. If you use partitions in the target table, you may create a new table AS SELECT NOLOGGING and then ALTER TABLE EXCHANGE PARTITION. This will generate minimal redo.
(8) External tables help reduce redo as no data is loaded into the table but viewed from the database

It is thought that by creating the tablespace with nologging clause the redo is controlled and never generated. It is not correct. If the objects are created within that tablespace with logging clause they generate redo. It means then the nologging or logging clause for the object created in a tablespace has an over-riding effect on the redo generation.

The default for any object created is LOGGING. Hence if an Object is created with in tablespace with no clause to set NOLOGGING then the redo is generated as this setting of the Object over rides the NOLOGGING nature of the tablespace.