RE: nologging and recovery

Thanks Jared, I'm familiar with those pages. What I'm not 100% clear = on, is
it possible to open a database after recovering it , if objects were = created
with nologging and have the db leave them in a state that indicates they need to be recrreated manually.. I don't care about them being = consistant
or current or usable...I can drop and recreate those few objects. My question is minus those objects(yes, they are they for sake of argument, = but
unusuable) can a db be recovered and opened.

Based on the excerpts you have pasted and my understanding I'm thinking = the
db cannot be restored at all.

Thhis piece makes me wonder though:
What is the recoverability of objects created as NOLOGGING?=20 Since nologging disables writing redo log entries, there is no way for Oracle to recover data related to nologging operations. In the case of = a
media failure subsequent to a nologging operation, Oracle will apply the redo log transactions, but, when it reaches the transactions related to = the
nologging operation, it will only be able to apply the extent = invalidation
records, since that is all that was recorded. Any subsequent attempt to access data in those extents will result in an ORA-26040 "Data block was loaded using the NOLOGGING option". The only resolution to the error is = to
drop and recreate the object. Note that this risk only exists until the next successful backup. Backups taken after the completion of the = nologging
operation will provide complete recovery.=20

Based on the above and other info I belive it may be possible to = restore,
recover, open and recreate objects that fit the category of being able = to
easily be rebuilt.

My question was can I restore all of the database in terms of the = objects
that are not operated in with nologging and then recreate those = objects?
The "debate" is over wether the db can be restored to a "consistant" = state
at all. If the db will recover and objects return ora-26040 that were impacted via nologging command after last db backup, then that is what = I'm
trying to get a verification or denial on.

That implies to me that the db can be opened and objects need to be recreated that were operated on in a nolooging mode after the last db backup.

As an aside I presume I could place all objects that do not have to be recovered on their own tablespace and when recovering do not recover or restore that tablespace(recreate ctl file).

This db in generating 1GB archive logs every 5 minutes.

Clearer? ;)

---
Happy New Year Mladen...I see your back to your usual whimsical and =
witty
self.  As an aside, I performed 11 Production RAC system recoveries on =
both
coasts 2 weeks before Xmas concurrently due to a mistake made by one =
person.
;

Cheers
David

-----Original Message-----
From: Jared Still [mailto:jkstill_at_gmail.com]=20
Sent: Tuesday, January 04, 2005 5:13 PM
To: thump_at_cosmiccooler.org
Cc: oracle-l_at_freelists.org
Subject: Re: nologging and recovery


Here's a bit from the fine manual for 8i:

=09
In NOLOGGING mode, data is modified with minimal logging (to mark new
extents invalid and to record dictionary changes). When applied during =
media
recovery, the extent invalidation records mark a range of blocks as
logically corrupt, because the redo data is not logged. Therefore, if =
you
cannot afford to lose this table, it is important to take a backup after =
the
NOLOGGING operation.


And here it is from 10g:

In this mode, Oracle Database inserts data without redo or undo logging.
(Some minimal logging is done to mark new extents invalid, and data
dictionary changes are always logged.) This mode improves performance.
However, if you subsequently must perform media recovery, the extent
invalidation records mark a range of blocks as logically corrupt, =
because no
redo data was logged for them. Therefore, it is important that you back =
up
the data after such an insert operation.

You may recall that this used to be called UNRECOVERABLE ...

Jared


On Tue, 4 Jan 2005 15:29:32 -0800 (PST), David <thump_at_cosmiccooler.org>
wrote:


> Having a bit of a debate here...

> Different docs have alluded to different issues and pros/cons.

>=20


> Question....under 9i...what are the ramifications of performing the=20

> operations that support nologging and then having to recover the=20

> database?

>=20


> These nologging operations are ongoing in such a fashion that it would =




> not be possible to perform a db backup after each operation.

>=20


> Can we recover and the associated objects error out with an error when =




> accessed and need to be recreated or can we not recover the associated =




> tbs/datafile at all?

>=20


> For the record....the objects assocated with the operations were we=20

> would not need to perform logging do not need to be recovered.  All=20

> objects currently reside in the same tbs.

> --

> ..

> David

>=20


> --

> http://www.freelists.org/webpage/oracle-l

>=20


--=20
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist




--
http://www.freelists.org/webpage/oracle-l