Re: Standby database question

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:VsHz9.74282$g9.208887_at_newsfeeds.bigpond.com...
>
> "Richard Foote" <richard.foote_at_bigpond.com> wrote in message
> news:86Bz9.74089$g9.208116_at_newsfeeds.bigpond.com...
> > "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
> > news:4kyz9.74020$g9.208056_at_newsfeeds.bigpond.com...
> > > Well, a simple question, and it seems to have stirred up all sorts of
> > > confusion in the replies!!
> > >
> > > Somewhere in all of the replies is the truth.
> > >
> > > The paths to datafiles can be different, because there's an init.ora
> > > parameter that will do a transparent conversion from
'/bing/bong/x.dbf'
> on
> > > primary to '/blah/bong/x.dbf' on standby.
> > >
> > > The O/S must be the same on primary and standby. You can't ship redo
> from
> > a
> > > Unix box to an NT standby, for example. Neither can you ship Solaris
> redo
> > to
> > > an HP standby. Can an HP10.10 box ship redo to an HP10.20 standby?
> > > Perhaps... but it would be unsupported, and would be the first thing
> > Oracle
> > > Support would pick up on if asked. As such, it's certainly not
> > recommended.
> > > The requirement for the O/S to be the same is true even of the new
> > 'logical
> > > standby' facility in 9iR2.
> > >
> > > The *number* of datafiles (and their size etc) must be identical on
both
> > > primary and standby. You can *NOT* have a subset of your datafiles as
a
> > > standby. If you've 100 datafiles on primary, each of 500MB, you must
> have
> > > 100 datafiles on standby, each of 500MB. The reason? You'll be
shipping
> > > redo from primary to standby, and redo contains instructions such as
"on
> > > file 3, block 650, row 4, change column 4 to 600". So if you don't
have
> a
> > > file 3, or if file 3 is smaller on standby, such that it doesn't have
a
> > > block 650, the whole thing is going to go down the drain.
> >
> > Hi Howard
> >
> > This question has raised some confusion.
> >
> > You *CAN* have a subset of your datafiles on the standby DB.
> >
> > You can make datafiles on the standby database "offline" which means
that
> > these files do not have to be copied across, are not recovered (the redo
> is
> > simply ignored, it doesn't cause any failures) and effectively results
in
> a
> > subset of the datafiles being maintained on the standby.
> >
> > When you open the standby database, you simply drop the now stuffed
> > tablespace and you're away.
> >
>
> What I was going to say, before I had to run off was: this isn't much of a
> standby database, then, is it? As you put it, you've just 'stuffed' some
> tablespace (ie, lost some data and transactions). I can't quite see the
> rationale for going to all the bother of setting up a standby and then
> cheerfully signing up to data loss.
>
> I'm also intrigued by what happens when the piece of redo that gets
skipped
> because the file in question is offline happens to be the insert into the
> DEPT table of a new department... and then a little further in the redo
> stream is a bunch of inserts into EMP (whose datafile is very much online)
> for new employees who happen to work in that new department. It doesn't
> bear thinking about, really, does it? Even the data that does make it
> across to the standby is now logically stuffed.
>
> So whether or not you can practically do what you propose, for a standby
to
> be a meaningful standby, and to be a useful database in extremis, you
> shouldn't.
>
> But in any case, I was rather more addressing the issue of, for example,
two
> 500MB files making up one tablespace on primary, and one 1GB making up the
> same tablespace on standby. That is, say you have 30 files on primary, and
> 15 twice-the-size files on standby. In that sense, you cannot have a
> 'subset' of your datafiles, meaning fewer files on one than on the other.

Hi Howard,

Whether or not we question the merit of such a feature, the fact remains you can have a subset of datafiles on a standby db (or with Data Guard). This is a fully supported and documented feature.

Extract from the Data Guard Concepts manual (9.2) says:

"You can take standby database datafiles offline as a means to support a subset of your primary database's datafiles. For example, to skip recovery of datafiles that were not copied to the standby database, take the missing datafiles offline using the following statement on the standby database: SQL> ALTER DATABASE DATAFILE 'MISSING00004' OFFLINE DROP; If you execute this statement, then you must drop the tablespace containing the offline files after opening the standby database."

I agree it's somewhat out of the norm but I can think of a couple of scenarios where this might be useful (eg. massive DB that just can't be fully supported in the standby environment)

Cheers

Richard Received on Mon Nov 11 2002 - 06:06:09 CST