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. 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. Direct loading into the database tables also avoids generating redo.
Ex:
Insert /*+ append */ into <destination_table> Let
the source table be external table and destination stage table. Your job
is done with the lowest possible redo generation. Many
stored procedures and functions are seen inserting tons of data with no
concern 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.
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. |