Re: Checking that I can restore from RMAN backups (if I need to)

> Surely EVERYBODY wants to know if they can recover their databases from
> backup don't they?

First of all, NOBODY sets up 2300 production databases. This is just utter madness.
In Oracle a schema is what a database is in Sqlserver. Time to reorganize everything. There can't be a business need to throw away so much money on Oracle licenses.

Secondly, most people never test their restores, and many people seem to believe an export constitutes a backup.

As RMAN comes with it's own API, and you can run RMAN in debug mode, which will show you the actual API calls, and RMAN has a VALIDATE BACKUP command, you would be better off making use of the API in your script. The script you are setting up now can't cover all aspects of RMAN and will fail sooner or later.
Apart from that: complete backup information comes from the RC_ views and not from the v$ views.
Also: there isn't a thread specific archive log sequence. In short: either your script will be ultimately just plain wrong (because it is drawing it's information from incorrect sources) or obsolete, as Oracle continues to update RMAN.

Oh, BTW: Rman has been around since 8.0, so since 1998. Consequently it is definitely not the 'flavour of the month'. You are lagging behind.
The flavour of the month is 'secure backup'

--
Sybrand Bakker
Senior Oracle DBA






On Oct 8, 10:35 pm, t..._at_aberdour.demon.co.uk wrote:


> My boss asked me if I could be sure that my backups were "adequate" to

> enable me to recover from them.  Well, as I am a DBA it seems like a

> reasonable question doesn't it?

>

> The problem is we have 2,300 databases (that's the supported ones at

> any rate.  God only knows how many test systems there are but they

> aren't my problem).

>

> It seems that RMAN is the flavour of the month for backups and it was

> suggested to me that I consider three main criteria:

>

> 1) There exists a backup of each datafile that is at most 'm' days old.

>

> 2) There is a full backup of each datafile that is at most 'n' days

> old.

>

> 3) The archived logs (on tape or on disk) form an unbroken sequence

> (i.e. you can use them all for rolling forward in a recovery situation)

> starting from the last backup.

>

> To explain the difference betwen 'm' and 'n': If 'm' was 2 days and 'n'

> was 7 days, and a level 1 incremental backup was taken 1 day ago and a

> level 0 (full) backup was taken 6 days ago, we would be in good shape.

> If incrementals are not in use then a full backup must be at most 2

> days old.

>

> Now, I didn't quite fancy logging into 2,300 hosts every morning to

> check the backups so it seemed that a wee script was in order (I am not

> Scottish, but I live there and it seems to be rubbing off on me).

>

> In short order, I worked out that the preferred solution was to use

> various RMAN CROSSCHECK commands and then to start querying the various

> controlfile V$ views.

>

> OK:

>

> CROSSCHECK BACKUP...

> CROSSCHECK COPY....

> CROSSCHECK ARCHIVELOG AFTER.....

>

> seemed a good start.  Now what about those v$ views?

>

> In 10gR2, there is a view V$BACKUP_DATAFILE_DETAILS which seems to make

> it reasonably easy to answer the first two questions (in prior releases

> V$BACKUP_DATAFILE is sufficient) and it seemed to me I was well on my

> way.  Now for requirement three.  After a short time I came up with

> this query:

>

> select 'Archive log gaps between sequence ' || previous_log || ' and '

> || current_log || ' for thread# ' || thread# message from

> (select thread#,sequence# current_log, lag(sequence#) over (partition

> by thread# order by sequence#) previous_log from v$archived_log where

> status ='A')

> where previous_log < current_log-1

>

> A nice, elegant query I thought to myself.  Quite proud, I was, to have

> utilised an analytic function on a controlfile query!

>

> Anyway, I soon realised that I had to make the query a little more

> complex to restrict the archivelogs under consideration to those

> strictly needed for recovery.  I needed to determine the latest backup

> of each datafile and query its checkpoint_change#.  I then had to take

> the minimum of these checkpoint_change#s and that should be the point

> from which I would need to roll forward.  I then had to determine the

> starting sequence# for each thread based on that checkpoint_change#.

>

> Undeterred, I soon I had my draft code in full screen mode admiring my

> handywork, and feeling quite pleased with myself I must say.  Then I

> asked myself "What have I forgotten?".

>

> Well, what about "READ ONLY" tablespaces?  The checkpoint_change# in

> these datafiles may be ancient, but that does not mean we have any

> recoverability issues.  But of course I DO need to check for cases

> where people have put tablespaces into READ ONLY mode and then

> immediately stopped backing them up.  I needed some special treatment

> here.

>

> OK, my simple SQL statement has now grown into quite a big piece of

> PL/SQL, and that is even before I try and deal with the 9i/10g

> differences.  But hold on.  What about RAC?

>

> Sure, I have checked that there is an unborken sequence for each

> thread.  But what about dependencies between threads?  Come to think

> about it:  What if I am missing ALL the archivelogs for a thread?  Is

> that because that one instance has been down for a week, or is it

> because all the archivelogs have been lost?

>

> At this point I started saying to myself that I must be on the wrong

> track.  There must be an easier way.  However, I have been unable to

> locate any discussion on this topic in any books or notes conferences.

> Surely EVERYBODY wants to know if they can recover their databases from

> backup don't they?

>

> So the question is:  "How are you supposed to do that?"

> 

> Any help would be appreciated.