Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: nologging and recovery

RE: nologging and recovery

From: Riyaj Shamsudeen <rshamsud_at_jcpenney.com>
Date: Tue, 04 Jan 2005 22:53:32 -0600
Message-id: <000101c4f2e2$83bd2bc0$6501a8c0@corp.jcp.com>


David
>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.
Correct. DB will recover fine, just the objects which were "truly" loaded via nologging option will error out when accessed.

>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.

That's correct and these objects need to be recreated..

>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).
You are better off recovering the tablespace and then recreating the objects.
If you would like to ignore the datafiles during recovery, then you must cleanup by dropping the tablespace after opening the database since file$ will have entry. Recreating controlfile only removes from the controlfile and the file$ will have an entry for those datafiles that did not participate (with a RECOVER status ?).

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Sent: Tuesday, January 04, 2005 8:38 PM
To: 'Jared Still'
Cc: oracle-l_at_freelists.org
Subject: 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 -- Attached file included as plaintext by Ecartis -- -- Desc: Signature The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer. -- http://www.freelists.org/webpage/oracle-l
Received on Tue Jan 04 2005 - 23:39:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US