Re: unrecoverable datafiles ?

From: Kenny Payton <k3nnyp_at_gmail.com>
Date: Wed, 26 Aug 2015 21:57:42 -0400
Message-Id: <A4442415-7C04-4AD2-B436-CA913951B9CC_at_gmail.com>

Point in case, I was asked a couple days ago to assist in re-loading ( via sqlldr ) 5 months worth of data which I am currently working on. The reason it needs reloading is someone accidentally truncated a table in the wrong environment, roughly 6T. We use snapshots of Dataguard standby databases as prod backups. When the standby was created a step in our doc was skipped and “FORCE LOGGING” was not executed on the primary. This was well known, just a mistake. Restore/recovery tests did not reveal this mistake because they were not thorough enough, this is rare in our environment. The tablespace was PITR, using TT feature, and every partition since the initial build was redo unrecoverable.

I have three points of advice.

1. Be sure everyone involved in the process is very well educated in the risk of NOLOGGING/UNRECOVERABLE operations.
2. Be certain that the cost of LOGGING is really worth the consequence of not. In my experience some careful redolog/archivelog IO tuning/segregation will overcome any costs.
3. If you’re running a standby “FORCE LOGGING” or see #1.

Kenny

> On Aug 26, 2015, at 5:35 PM, Norman Dunbar <oracle_at_dunbar-it.co.uk> wrote:
>
> It sounds like you have had a NOLOGGING or UNRECOVERABLE or a direct load operation at some point. If you have had a full back since then, there's no need to worry. If not, I suggest a full backup ASAP.
>
> You might want to ALTER DATABASE FORCE LOGGING to prevent this happening again. Up to 11.1 this needs the database in mount mode, from 11.2 it can be open.
>
> You should always have force logging turned on on your primary and standby databases.
>
> HTH
>
> Cheers.
> Norm.
>
> On 26 August 2015 20:00:33 BST, Chris King <ckaj111_at_yahoo.ca> wrote:
> Has anyone run into this before?
>
> I ran orachk on the Oracle RAC system and it reported the database has datafiles that are unrecoverable. As recommended, I then ran this command:
> rman > report unrecoverable database;
>
> There are no files listed.
>
> Then I ran this command:
> select file#, unrecoverable_time, unrecoverable_change# from v$datafile where unrecoverable_time is not null;
>
> on both the primary and standby, I get a list of five files, the same list on both primary and standby.
> However, the dates listed are all from January and February this year.
>
> Do I have unrecoverable datafiles? If so, how can I repair this? If not, how can I clean up so the sql query reports properly?
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.

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

Received on Thu Aug 27 2015 - 03:57:42 CEST