Re: unrecoverable datafiles ?

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

I forgot to mention, sorry, your standby database is trash!

The unrecoverable transactions that took place on the primary, back in January and February, were never applied to the standby. If you ever need to run the standby as a primary (switchover or failover) or as a read only reporting database, then any transactions that try to read the tables loaded with the unrecoverable data will error out, probably as follows:

ORA-01578: ORACLE data block corrupted (file # 6, block # 139)
ORA-01110: data file 6: '+DATA/tst11204/datafile/normantest.314.860855047'
ORA-26040: Data block was loaded using the NOLOGGING option

To get back to a usable standby, you need to make sure that the data are still present in the primary - which it should be. Unless you restored and attempted a recovery of the primary, the data are still there - but will not be if you did a restore and recover, and accessing the data will result in the same errors as above.

You need to take copies of the affected data files from the primary over to the standby to ensure that the standby has a copy of the data that were loaded in nologging or unrecoverable mode.

I have a blog entry on this very matter at http://qdosmsq.dunbar-it.co.uk/blog/2014/10/beware-of-the-silent-database-killer/ which has details of how to get the standby back up to date and how to work around an interesting bug in RMAN that you will most likely come across when you attempt this!

RMAN is interesting in that it will not inform you when you restore/recover the primary database, that there is unrecoverable data missing - you only get the errors when you access the data in a query.

Good luck.

Cheers,
Norm.

-- 
Norman Dunbar
Dunbar IT Consultants Ltd

Registered address:
27a Lidget Hill
Pudsey
West Yorkshire
United Kingdom
LS28 7LG

Company Number: 05132767
--
http://www.freelists.org/webpage/oracle-l