Whether goldengate requires force logging: detailed explanation of Oracle force logging and nologging Modes

Force logging Mode

In Oracle, some DDL statements (such as create database) allow you to specify the nologging clause, which causes some database operations
Redo logs are generated in the database redo log. The nologging setting can accelerate operations that can be easily recovered outside the database recovery mechanism,
However, this setting may have adverse effects on media recovery and standby databases.

Oracle Database supports forcible redo records even if the nologging clause is specified in DDL statements. For temporary tablespace and temporary segment objects,
The database will never generate redo records, so the force logging mode will not affect these objects.

The nologging mode can be specified in Oracle as follows:

Create Table and ALTER TABLE
Create index and alter Index
Create materialized view and alter Materialized View
Create tablespace and alter tablespace
Create index... Rebuild
Alter table... Move

Note:
Specifying the logging setting for the lob column may be different from specifying the logging setting at the table level. If you specify the logging setting at the table level and the lob Column
When nologging is set, the redo record is recorded for the DML changes recorded in the base table, but the redo record is not recorded for the DML changes to the lob data.

For details about specifying a logging clause, see Oracle Database SQL Language Reference

There are two ways to place the database in force logging mode:
1. Specify the force logging clause in the create database statement.
2. Specify the force logging clause in the alter database statement.

After the database is created, the alter database statement can be used to place the database in force logging mode. This statement may take a long time to complete.
Because it will wait for all the records not recorded to be written directly.

Cancel force logging mode:
Alter database no force logging;

In addition to specifying the force logging mode for the database, you can also specify force logging or no force logging at the tablespace level.
However, if the force logging mode takes effect at the database level, this setting takes precedence over the tablespace level setting. If this setting does not take effect at the database level,
Then the settings at each tablespace level take effect. It is recommended that the entire database be placed in the Force logging mode or the tablespace
It is in force logging mode, but it is not recommended to place both of them in force logging mode at the same time;

The Force logging mode is a permanent attribute of the database. That is to say, after the database is shut down and restarted, it will still be in the same logging mode. However
After the control file is created, the database will not be restarted in force logging mode unless the force logging clause is specified in the create control file statement.

Control the generation of redo records

For some database operations, you can control whether the database generates redo records. As we all know, media cannot be restored without redo. However, reduce redo
Generation can improve performance, especially for operations that can be easily restored. For example, the create table as select statement causes a fault in the database or instance.
The statement can still be re-executed.

If the nologging clause is specified in the create tablespace statement, operations performed on database objects in the tablespace will suppress redo generation.
If the nologging clause is not specified or the logging clause is specified, the database generates a redo record whenever the objects in the table space are modified.
No matter which logging attribute is specified, no redo record is generated for temporary segments or temporary tablespace.

The logging attribute specified at the tablespace level is the default attribute of the object created in the tablespace. You can create a table at the schema object level (for example, create table)
Specify the logging or nologging settings to overwrite the default logging attribute.

If the standby database is configured, The nologging mode will cause problems to the high availability and accuracy of the standby database. For customer service, you can specify
Force logging mode. If the force logging clause is specified in the create tablesoace statement, all operations and
Changes will generate a redo record. This setting overwrites all object-level settings.

If the tablespace in force logging mode is transferred to another database, the new tablespace will not remain in force logging mode.

Whether to configure force logging mode for goldengate Synchronization

Regarding whether force logging needs to be configured for goldengate synchronization, many people on the internet believe that goldengate only forces the Enable of Supplemental log
Force logging is not required to be enabled, but xiangsir deems it necessary to enable force logging for the following reasons:

First, we need to clear the meaning of the nologging mode corresponding to the Force logging mode:

Oracle Data guard concepts and administration for information about the effect of nologging on a database

In some SQL statements, the user has the option of specifying the nologging clause, which indicates that
Database operation is not logged in the online redo log file. Even though the User specifies the clause,
Redo record is still written to the online redo log file. However, there is no data associated with this record.
This can result in log application or data access errors at the standby site and manual recovery might be
Required to resume applying log files.
 
The nolloging mode is set in the database, which does not mean that related operations will not generate Redo, but will suppress the operation to generate logs and minimize the number of logs generated.

To create a table most efficiently use the nologging clause in the create table... as select statement. The nologging
Clause causes minimal redo information to be generated during the table creation. This has the following benefits:

Space is saved in the redo log files.
 
The time it takes to create the table is decreased.
 
Performance improves for parallel creation of large tables.
 
The nologging clause also specifies that subsequent direct loads using SQL * loader and direct load insert operations
Are not logged. Subsequent DML statements (Update, delete, and conventional path insert) are unaffected by the nologging
Attribute of the table and generate redo.

From Administrator's Guide

When you create or re-create a large index or modify a large table in direct load SQL * loader and insert operations, we usually specify the nolloging statement, which will generate
For example, insert/* + append,
When set to the logging mode:
If the database is in archive mode, a redo will be generated regardless of whether append is used or not. When the nologging mode with table level is set, only append is set and no
Generate redo.
If the database is in non-archive mode, no redo is generated for append insertion in both logging and nologging modes, and no append is generated.
Redo.

Force logging configuration considerations

Force logging mode results in some performance degradation. If the primary reason for specifying force logging is to ensure
Complete media recovery, and there is no standby database active, then consider the following:

How many media failures are likely to happen?
 
How serious is the damage if Unlogged direct writes cannot be recovered?
 
Is the performance degradation caused by forced logging tolerable?
 

If the database is running in noarchivelog mode, then generally there is no benefit to placing the database in force Logging
Mode. Media recovery is not possible in noarchivelog mode, so if you combine it with force logging, the result may be performance
Degradation with little benefit.

For reprint, please indicate the source and original article links:

Http://blog.csdn.net/xiangsir/article/details/8612366