Strict Standards: Declaration of Walker_Page::start_lvl() should be compatible with Walker::start_lvl(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 576

Strict Standards: Declaration of Walker_Page::end_lvl() should be compatible with Walker::end_lvl(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 576

Strict Standards: Declaration of Walker_Page::start_el() should be compatible with Walker::start_el(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 576

Strict Standards: Declaration of Walker_Page::end_el() should be compatible with Walker::end_el(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 576

Strict Standards: Declaration of Walker_PageDropdown::start_el() should be compatible with Walker::start_el(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 593

Strict Standards: Declaration of Walker_Category::start_lvl() should be compatible with Walker::start_lvl(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 687

Strict Standards: Declaration of Walker_Category::end_lvl() should be compatible with Walker::end_lvl(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 687

Strict Standards: Declaration of Walker_Category::start_el() should be compatible with Walker::start_el(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 687

Strict Standards: Declaration of Walker_Category::end_el() should be compatible with Walker::end_el(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 687

Strict Standards: Declaration of Walker_CategoryDropdown::start_el() should be compatible with Walker::start_el(&$output) in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/classes.php on line 710

Strict Standards: Redefining already defined constructor for class wpdb in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/wp-db.php on line 58

Deprecated: Assigning the return value of new by reference is deprecated in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/cache.php on line 99

Strict Standards: Redefining already defined constructor for class WP_Object_Cache in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/cache.php on line 404

Deprecated: Assigning the return value of new by reference is deprecated in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/query.php on line 21

Deprecated: Assigning the return value of new by reference is deprecated in /homepages/15/d244775686/htdocs/TDBASHome/BlogHome/BFBlog/wp-includes/theme.php on line 576
Discovering NOLOGGING Operations
Hello VMWare, Goodbye Parallels Why Green Computing?
May 31

Like responsibility, it grows!The goldfish always grows to the size of the bowl. If you’re a DBA goldfish, you’ll probably script out repetitive tasks until the bowl gets bigger. And then they feed you more databases from various business areas, and you grow some more. How is that for a strained analogy?

Any Oracle DBA has been there - After your initial herd of databases are stable, happy, and well-fed, people notice. And then you reap the true reward of good work: More work! Unfortunately, this is usually when someone fishes a stove-piped database that has become very important internally. You know, the one put together by someone who left 2 years ago. No Critical Product Updates, one or two control files, and the telling 5Mb redo logs that switch every 10 seconds. But you gladly take it in anyway…

A bit of work and now the database is chugging along like a champ! Tuned, Optimized, Mirrored, multiplexed, in ARCHIVELOG mode, and integrated into your RMAN backup scripting.

Everything seems fine, but is it?

Surely you could easily and successfully recover if you had to this very minute, right?

Maybe.

Is logging of all operations enforced on this database, or at least in the user’s tablespace? Use the following to find out:

select FORCE_LOGGING from V$DATABASE;
select TABLESPACE_NAME, FORCE_LOGGING from DBA_TABLESPACES;

If forced logging is not or can not be applied to the database, there is a risk that NOLOGGING operations may have been performed on the databases objects. Common operations that are run under NOLOGGING are index builds, index rebuilds, direct load inserts, direct loads with SQL Loader, and partition manipulation. Once a NOLOGGING operation has been performed, we cannot roll forward, past that change in that tablespace! If it is a tablespace only containing indexes, we’ll suffer downtime while the indexes rebuild and bring the database back to a reasonable level of performance. If the database contains objects containing data, the risk grows for losing the transactions since the NOLOGGING operation.

A good first line of defense is to include REPORT UNRECOVERABLE into your RMAN backup scripts, and stay on top of the logs - Or test for the expected return and pipe the results to your dashboard or monitoring software like Big Brother by Quest. This will catch all manners of problems before they become critical:

RMAN> report unrecoverable;
Report of files that need backup due to unrecoverable operations
File Type of Backup Required Name
---- ----------------------- -----------------------------------
4    full or incremental     X:\ORADATA\DATA01\TESTDB\TEST01.DBF

Here’s a quick script I wrote to find when the last NOLOGGING operation occurred (Note: Output has been edited for page fit):

set LINESIZE 120
set PAGESIZE 40
DEFINE LINE1= 'LAST NON-LOGGED OPERATIONS'
DEFINE LINE2= 'Check the Change Numbers and times against your backups to determine'
DEFINE LINE3= 'if non-logged operations have occurred'
TTITLE Skip 3 CENTER LINE1 SKIP 2 LINE2 SKIP 1 LINE3 SKIP 2
BTITLE CENTER "BFBlog.TheDatabaseShop.com"
COLUMN DBF_NAME FORMAT A40 WORD_WRAPPED
COLUMN TS_NAME FORMAT A15 WORD_WRAPPED
select  d.NAME as DBF_NAME,
t.NAME as TS_NAME,
d.UNRECOVERABLE_CHANGE# as NOLOG_CHNG#,
to_char(d.UNRECOVERABLE_TIME, 'Dy DD-Mon-YYYY HH24:MI:SS') as NOLOG_TIME
from V$DATAFILE d join V$TABLESPACE t
on d.TS# = t.TS#
order by t.NAME;

Output:

LAST NON-LOGGEDOPERATIONS

Check the Change Numbers and times against your backups to determine
if non-logged operations have occurred

DBF_NAME             TS_NAME   NOLOG_CHNG# NOLOG_TIME
-------------------- --------- ----------- ------------------------
J:\...\SYSTEM01.DBF  SYSTEM    0
J:\...\UNDOTBS01.DBF UNDOTBS1  0
J:\...\SYSAUX01.DBF  SYSAUX    0
J:\...\TEST01.DBF    TEST      6271597     Tue 02-Jun-2008 18:30:46
J:\...\USERS01.DBF   USERS     0

After that, just make sure your last Level 0 backup is newer than the times listed, and be aware that Point In Time Recovery will be limited to before the NOLOGGING operations occurred and when the last Level 0 backup was taken.

Be sure to set up lines of communication and coordination in the future, so the risk of not being able to recover the entire database to the last transaction is reduced.

Brian Fedorko

Leave a Reply