Re: Database with NOLOGGING everywhere

From: Alex Gorbachev <ag_at_oracloid.com>
Date: Sun, 6 Apr 2008 21:55:47 -0400
Message-Id: <FDE32442-E36E-4E0E-9156-4D679FC71473@oracloid.com>


Good point. The answer to you concern on current transactions with nologging is in documentation (11g).

"FORCE LOGGING
...
If you specify FORCE LOGGING, then Oracle Database waits for all ongoing unlogged operations to finish."

On 6-Apr-08, at 9:26 PM, Mark Brinsmead wrote:

> The archive logs may not be quite as useless as you fear; depending
> on what is actually being done in your database.
>
> As you are most likely already aware, the archive logs contain only
> minimal information about DML operations that were performed in
> NOLOGGING node -- just enough, in fact to mark those blocks as
> "corrupt". So, if you happen to be doing a lot of NOLOGGING
> operations on your data, point in time recovery might look kind of
> pointless.
>
> On the plus side, however, relatively few operations can actually be
> done with NOLOGGING; that is, even is your segment is in NOLOGGING
> mode, most inserts updates and deletes will be logged anyway.
> (Refer to the documentation for your release for a full list of DML
> that can be done with NOLOGGING.)
>
> There is one notable exception to that rule -- LOB segments. If a
> LOB segment is in NOLOGGING mode, and one or two other (very common)
> conditions are also met, pretty much all DML affecting the LOB
> column will be done in NOLOGGING mode, and therefore unrecoverable
> with point-in-time recovery.
>
> So, are the archivelogs useless? Not at all. They are still
> needed, for example, to allow you to perform online backups.
>
> "But my database is constantly subject to NOLOGGING DML, so online
> backups are useless", you say? Not true!
>
> Even if there are constant NOLOGGING operations being done, you
> should be able to guarantee safe online backups by placing your
> database into FORCE LOGGING mode just before beginning the backup.
> Warning: I have neither tried nor tested this idea -- I have no
> idea, for example, how this will work with transactions that were
> active at the time you set FORCE LOGGING. You'll probably want to
> review some documentation, and do some testing, but I think there is
> enough potential here to make it worthwhile.
>
> Even if you are doing backups with shadow copies, there would
> probably be considerable benefit to keeping the database open while
> you make them.
>
> Assuming, of course, that the FORCE LOGGING idea is actually
> feasible. It should be, but I have been surprised before...
>
>
> On Sun, Apr 6, 2008 at 12:09 PM, Kline.Michael <Michael.Kline_at_suntrust.com
> > wrote:
> I've got a 1.08TB warehouse that is pretty much all "batch mode".
>
>
> Because of that "batching" they have almost 80-95% of the production
> tables set to NOLOGGING, including many at the tablespace level.
>
>
> We normally rely on shadow copies, but the database is archivelog.
>
>
> Given all the NOLOGGING, what could I possibly hope to "recover" if
> I ever had to use those archivelogs????
>
>
> The shadow copy is going to naturally be a COLD backup, I would
> think that would make the archivelogs practically useless.
>
>
> Mind you, I may be able to copy a datafile from the shadow copy, and
> then apply archive logs, but again, with all the NOLOGGING defined,
> what can I hope for in the recovery? I guess I can pick up the table
> creates and all, but the data would have to be all repopulated
> through the batch runs?
>
>
> I inherited this one.
>
>
> This is at 9.2.0.6, but when we do the next conversion, I think I'm
> going to push for a "more normal" archivelog + LOGGING
> configuration, RMAN backups and some shadow copy for other things
> that database has to do.
>
>
> Thoughts? Comments?
>
>
> Looking for someone who may have been caught in this situation and
> how you managed. That good old "Been there done that, ended up doing
> " type thing. Been very fortunate thus far.
>
>
> Michael Kline
>
>
>
>
>
>
> LEGAL DISCLAIMER
> The information transmitted is intended solely for the individual or
> entity to which it is addressed and may contain confidential and/or
> privileged material. Any review, retransmission, dissemination or
> other use of or taking action in reliance upon this information by
> persons or entities other than the intended recipient is prohibited.
> If you have received this email in error please contact the sender
> and delete the material from any computer.
>
> SunTrust and Seeing beyond money are federally registered service
> marks of SunTrust Banks, Inc.
> [ST:XCL]
>
>
>
> --
> Cheers,
> -- Mark Brinsmead
> Senior DBA,
> The Pythian Group
> http://www.pythian.com/blogs

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Apr 06 2008 - 20:55:47 CDT

Original text of this message