Force Logging Tips Oracle Database Tips by Donald BurlesonApril 18, 2015

FORCE LOGGING Option

In Oracle9i release 2, the FORCE LOGGING option was introduced. The FORCE LOGGING option can be set at the database level or the tablespace level. The precedence is from database to tablespace. If a tablespace is created or altered to have FORCE LOGGING enabled, any change in that tablespace will go into the redo log and be usable for recovery.

Similarly, if a database is created or altered to have the FORCE LOGGING enabled, any change across the database, with exception of temporary segments and temporary tablespace, will be available in redo logs for recovery.

The FORCE LOGGING option can be set at database creation time or later using the alter database command.

To set FORCE LOGGING during the database creation, specify the following:

CREATE DATABASE <dbname>…..FORCE LOGGING… 

To enable FORCE LOGGING after the database is created, use the following command:

ALTER DATABASE FORCE LOGGING;

The FORCE LOGGING option is the safest method to ensure that all the changes made in the database will be captured and available for recovery in the redo logs. Force logging is the new feature added to the family of logging attributes.

FORCE LOGGING Option

The database or tablespaces in the database should be put into FORCE LOGGING mode before creating the backup for the standby database. Either a database or all of its tablespaces should be put into this mode but not both.

The following statement will put a tablespace in FORCE LOGGING mode:

ALTER TABLESPACE <tablespace name> FORCE LOGGING;

The FORCE LOGGING mode can be cancelled at the database level using the following statement:

ALTER DATABASE NO FORCE LOGGING;

The FORCE LOGGING mode can be cancelled at the tablespace level using the following statement:

ALTER TABLESPACE <tablespace name> NO FORCE LOGGING;

Temporary tablespaces and temporary segments have no effect during FORCE LOGGING mode because these objects do not generate any redo. Undo tablespaces are in FORCE LOGGING mode by default, so they cannot be put into FORCE LOGGING mode. Oracle will generate an error if an attempt is made to put a temporary tablespace or undo tablespace into FORCE LOGGING mode.

The FORCE_LOGGING column of v$database view can be queried to verify that the database is in FORCE LOGGING mode. Similarly, the FORCE_LOGGING column of dba_tablespaces view provides the same logging information for each tablespace.

select force_logging from v$database;
select force_logging from dba_tablespaces;

Also see these important notes on running DML in nologging mode:

Force logging mode is persistent across database startup, but it is not maintained when the control file is recreated unless the FORCE LOGGING clause is specified in the create controlfile statement. Also, a tablespace in the FORCE LOGGING mode, when transported to another database, does not maintain this mode.

In these situations, the FORCE LOGGING mode would have to be re-enabled. The primary database should remain in FORCE LOGGING mode as long as there is at least one Oracle instance in use.  Putting a database in FORCE LOGGING mode will have some performance impact.