Oracle Database Logging Mode and Enhancements in 18c

<<Back to Oracle DataGuard Main Page
There are 2 modes of logging in oracle database 
FORCE_LOGGING=NO=> minimal redo log generation for the no logging operations
FORCE_LOGGING=YES=> all changes are recorded in the redo log file even for no logging operations
starting with 18c you have 2 more possibilities 
STANDBY NOLOGGING FOR LOAD PERFORMANCE
STANDBY NOLOGGING FOR DATA AVAILABILITY
Continue reading the post till end for complete details

What is Force Logging Mode in Oracle Database

Enabling the force logging mode in oracle database enables the database to write all the changes (even no logging operations)in redo log file. Therefore force logging mode comes with some performance overhead.
You can enable the force logging at

• Tablespace Level
• Database Level

To enable the force logging at Tablespace Level It is not required to have the force logging enabled at database level.

How to Enable the Force Logging at Tablespace Level

SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
SQL>  select tablespace_name,force_logging from dba_tablespaces where TABLESPACE_NAME='USERS';
TABLESPACE_NAME                FORCE_LOGGING
------------------------------ ---
USERS                                              NO

SQL> alter tablespace USERS force logging;
Tablespace altered.
SQL> select tablespace_name,force_logging from dba_tablespaces where TABLESPACE_NAME='USERS';
TABLESPACE_NAME                FORCE_LOGGING
------------------------------ ---
USERS                                           YES

How to Disable Force Logging at Tablespace Level

SQL> alter tablespace USERS no force logging;
Tablespace altered.
SQL>  select tablespace_name,force_logging from dba_tablespaces where TABLESPACE_NAME='USERS';
TABLESPACE_NAME                FORCE_LOGGING
------------------------------               ---------------
USERS                                           NO

How to Enable Force Level at Database Level

SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES

Enhancement in Logging Mechanism in Oracle 18c

As we learned, the force logging logs all the change (even no logging operations)in redo log file, which of course decreases the performance of the database and specially slows down the data load operations. To keep the physical standby database synchronized while allowing the no logging operation oracle has introduced following no logging modes starting from oracle 18c

• STANDBY NOLOGGING FOR DATA AVAILABILITY
• STANDBY NOLOGGING FOR LOAD PERFORMANCE

What you need to Use these options

From Database Licensing Information User Manual you can use this feature as shown in the table below

Feature / Option / Pack	SE2	EE	EE-ES	DBCS SE	DBCS EE	DBCS EE-HP	DBCS EE-EP	ExaCS
Oracle Data Guard—Automatic Correction of Non-logged Blocks at a Data Guard Standby Database	N	N	Y	N	Y	Y	Y	Y

STANDBY NOLOGGING FOR DATA AVAILABILITY mode causes the load operation to send the loaded data to each standby through its own connection to the standby. The commit is delayed until all the standbys have applied the data as part of running managed recovery in an Active Data Guard environment.

How to Enable NOLOGGING FOR DATA AVAILABILITY Log Mode

SQL> ALTER DATABASE SET STANDBY NOLOGGING FOR DATA AVAILABILITY;
Database altered.

STANDBY NOLOGGING FOR LOAD PERFORMANCE is similar to the previous mode except that the loading process can stop sending the data to the standbys if the network cannot keep up with the speed at which data is being loaded to the primary. In this mode it is possible that the standbys may have missing data, but each standby automatically fetches the data from the primary as a normal part of running managed recovery in an Active Data Guard environment.

How to Enable NOLOGGING FOR LOAD PERFORMANCE Log Mode

SQL> ALTER DATABASE SET STANDBY NOLOGGING FOR LOAD PERFORMANCE;
Database altered.