My experience, findings and thoughts in my daily work with Oracle products



Forcing Oracle to use LOGGING mode


E-mail this post



Remember me (?)



All personal information that you provide here will be governed by the Privacy Policy of Blogger.com. More...





I've just read an interesting newsletter about that how we can force the database (or some tablespace) to use the LOGGING mode for all operations. For example, lets imagine that we don't want someone improperly to start some operation in NOLOGGING mode that will lead the database to impossibility of performing of full database recovery after media failure. This could be important issue if a Standby database is used as well.
The FORCE LOGGING clause was introduced in Oracle9i R2. It tells to Oracle redo logs to be written even when the NOLOGGING was specified in a DDL staement, table in NOLOGGING mode was created, or SQL*Loader was used to fill table via direct path loading.
This command can be applied at the database or tablespace level:

ALTER DATABASE FORCE LOGGING;
ALTER TABLESPACE tbs_name FORCE LOGGING;

The following query shows the recovery time of the last used NOLOGGING operation for all datafiles:

SELECT name,unrecoverable_change#,unrecoverable_time FROM v$datafile;


Keep in mind that Oracle never generates redo records for temporary tablespaces and temporary segments, so forced logging has no affect for these.
For more details about this feature:
Oracle9i Documentation - Specifying FORCE LOGGING Mode
Metalink note:174951.1 - Force Logging

Another very interesting Metalink note about the problems coming from using of NOLOGGING operations especially when Standby database exists:
Metalink note:290161.1 - The Gains and Pains of Nologging Operations


0 Responses to “Forcing Oracle to use LOGGING mode”

Leave a Reply

      Convert to boldConvert to italicConvert to link

 



About me

  • » I'm Radoslav Rusinov
  • » From Sofia, Bulgaria
  • » Employer TechnoLogica Ltd.
  • » I am working as a Database Consultant in Sofia, Bulgaria. My main professional interests are in the database area and especially in the Oracle RDBMS, including database design, development, security and administration.
  • » The views expressed on this blog are my own and do not necessarily reflect the views of my employing company and its affiliates
  • » My profile

RSS 2.0 Feed

Search This Blog with Google

Search This Blog with Free Find


powered by FreeFind

Search This Blog with Technorati

Previous Posts

Archives

Articles & Presentations

Discover Bulgaria

Oracle News & Blogs Aggregators

Oracle Resources

Remote DBA

Oracle User Groups

Oracle Blogs

Oracle Forums

Security Resources

Professional CV

Blog Statistics

              

              

               Page Rank Checker