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

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

   

 

 

Force Logging Tips

Oracle Tips by Burleson Consulting

April 18, 2012

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.

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.

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.

 


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


��  
 
 
 
 

 
 
 

 
 
Oracle performance tuning software 
 
oracle dba poster
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 -  2014

All rights reserved by Burleson

Oracle © is the registered trademark of Oracle Corporation.