Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 









Force Logging Tips

Oracle Database Tips by Donald BurlesonApril 18, 2015


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:


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


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.

Before the existence of FORCE LOGGING, Oracle provided logging and nologging options. These two options have higher precedence at the schema object level than the tablespace level; therefore, it was possible to override the logging settings at the tablespace level with nologging setting at schema object level.


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:


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


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


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.


The above text is an excerpt from the book: Oracle Data Guard Handbook

Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.