Re: unrecoverable datafiles ?

From: Kenny Payton <k3nnyp_at_gmail.com>
Date: Wed, 26 Aug 2015 22:25:58 -0400
Message-Id: <AA4CDF44-405A-4551-83E5-77E1380B6A84_at_gmail.com>

I absolutely agree. As a practice we run backup validates weekly since the databases we do this in are typically 20-30T in size. Unfortunately this is one of those environments that gets little attention and when the change in strategy was implemented several things were missed. No excuse really, we screwed up in a number of ways for this database and are paying the price.

Kenny

> On Aug 26, 2015, at 10:20 PM, Mark Brinsmead <mark.brinsmead_at_gmail.com> wrote:
> 
> Something to consider when making backups of a physical standby using tools other than RMAN...
> 
> Run a regular backup validate database check logical against the standby.  This will raise errors (and record them in the RMAN views in the standby database) if there is any corruption found -- either from NOLOGGING operations applied to the standby or from random bit-flipping at the storage layer.
> 
> Unless your database is massively huge, this operation is usually feasible to perform daily.  For really big databases, you might do it weekly or even monthly.
> 
> Doing this helps to (significantly) raise confidence that your standby database is valid, and that your "snaopshot" (or other non-RMAN) backups are valid.
> 
> Of course, it does not replace the need for occasional recovery testing, but it does provide much earlier detection of corruption issues should one arise.
> 
> On Wed, Aug 26, 2015 at 7:57 PM, Kenny Payton <k3nnyp_at_gmail.com <mailto:k3nnyp_at_gmail.com>> wrote:
> 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 <mailto: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 <mailto: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 - 04:25:58 CEST