August 2005

The Specialist
Database News You Can Use
A monthly newsletter for Oracle users

August 2005
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500

Ask DB Specialists: The NOLOGGING Keyword

You’re invited to submit your Oracle-related questions to us at askdbspecialists08@dbspecialists.com. Include your name and telephone number in your email. If your question is answered in The Specialist next month, you will receive a free copy of our Oracle Wait Events reference poster. (US shipping addresses only).

This month’s question comes to us from Adam in Chicago: Certain SQL statements allow me to specify the NOLOGGING keyword. This seems to make operations run faster and generate less redo. Is there a downside to this? Is there a way to tell if NOLOGGING has been used on my database? Is there a way to prevent people from using the NOLOGGING keyword?

Roger Schrag of the Database Specialists team responds: The NOLOGGING keyword can be specified in statements such as CREATE INDEX and CREATE TABLE AS SELECT in order to suppress much of the redo log activity and therefore make the operations run faster. In Oracle 7 the keyword was called UNRECOVERABLE. It did basically the same thing as NOLOGGING, but has been deprecated. Before using either of these keywords it is important to understand the implications of suppressed redo log entries.

The NOLOGGING keyword tells Oracle that the operation being performed does not need to be recoverable in the event of a media failure. In this case Oracle will generate a minimal number of redo log entries in order to protect the data dictionary, and thus the operation will probably run faster. Oracle is relying on the user to recover the data manually in the event of a media failure.

You might use the NOLOGGING keyword, for example, on a development database running in NOARCHIVELOG mode—since media recovery is not available anyhow. For databases running in ARCHIVELOG mode here are two important points to consider regarding NOLOGGING operations:

  • If a media failure occurs in a data file containing a segment that was written with NOLOGGING and the data file was not backed up after the NOLOGGING operation completed, then Oracle will not be able to recover the object. This may not be a concern if you can easily recreate the object, such as recreating an index. However, if you had executed a CREATE TABLE AS SELECT statement with NOLOGGING, the data could potentially be harder to recreate.
  • If the database has a physical standby database, then NOLOGGING operations will render data blocks in the standby “logically corrupt” because of the missing redo log entries. If the standby database ever switches to the primary role, errors will occur when trying to access objects that were previously written with the NOLOGGING keyword.

The V$DATAFILE view shows the last time a NOLOGGING operation was performed in each data file of the database. You can compare this with the last time each data file was backed up in order to know whether or not the data file could be recovered from a backup without having to manually recreate objects written with NOLOGGING.

The following query shows the name of each data file, along with the SCN and timestamp of the last NOLOGGING operation:

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

Starting in Oracle 9i, you can tell Oracle to ignore NOLOGGING keywords—either for a specific tablespace or across the entire database. The statements to do so are as follows:

ALTER TABLESPACE tablespace_name FORCE LOGGING;
ALTER DATABASE FORCE LOGGING;

If logging is forced in this way, Oracle will simply disregard the NOLOGGING keyword and will generate regular redo log activity. This feature can be used to prevent a NOLOGGING operation from undermining a database backup or the integrity of a standby database.

The NOLOGGING keyword can be very helpful for speeding up certain types of operations and minimizing the size of the archived redo log. However, these benefits come at the cost of impacting database recoverability and standby database validity. Fortunately, the V$DATAFILE view and the FORCE LOGGING feature give the DBA the necessary tools to manage these potentially dangerous side effects of the NOLOGGING keyword.

Oracle Database 10g High Availability

These days, many DBAs are tasked with researching and recommending high availability options. If you are interested in this subject, you might be interested in looking at a book from Oracle Press called Oracle Database 10g High Availability with RAC, Flashback, and Data Guard.

We’ve found a place where you can download one chapter of the book for free. Chapter 8, the sample chapter, is entitled Backup and Recovery for High-Availability Environments, and it is available for download at http://www.devx.com/dbzone/Article/21021. You can download this sample chapter to get an understanding of the book’s style and determine if the information will be valuable to you.

The book includes coverage of basic concepts, a section on high availability “workshops,” step-by-step instructions to get you through certain implementations, plus real-world inspired case studies. Along with an RMAN primer, you’ll find some of this handy information in the sample chapter.

Performance Optimization at Oracle OpenWorld

Database Specialists’ founder and president Roger Schrag will speak on the topic of performance optimization and speeding up SQL statements at the upcoming Oracle OpenWorld conference in San Francisco from September 17-22, 2005. Presentations are selected carefully, and speakers participate by invitation only. Roger Schrag—with over 14 years of Oracle experience—has been a favorite speaker of attendees for many years.

The conference includes focus areas of technology, applications, industries, and solutions. There will also be an Oracle Users Group program and the return of the popular hands-on labs. Roger’s talk will be session S058 in the Oracle Database 10g technology track. For additional details on Oracle OpenWorld, see http://www.oracle.com/openworld/sanfrancisco/conference/index.html.

Oracle Blogs

When you’ve got a little extra time on your hands, you might want to check into some of the blogs related to Oracle. Tom Kyte of asktom.oracle.com has a blog at http://tkyte.blogspot.com. You’ll get technical tips (such as information on the new Oracle 10g Release 2 feature, DML Error Logging), rants such as “Why why WHY do people feel obliged to update the data dictionary?” and random musings such as an entry on “the most ridiculous news item of the day.”

Another new blog is written by Pete Finnigan who works in the area of Oracle security specializing in auditing Oracle databases for security issues. One of his recent subjects is Demystifying MS SQL Server & Oracle Database Server Security. You’ll find it at http://www.petefinnigan.com/weblog/entries/. If that’s not enough to keep you busy, check out the OraBlogs website for a listing of blogs in the Oracle development community. It’s at http://www.orablogs.com/orablogs.

Leave a Reply

Your email address will not be published. Required fields are marked *