Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: rolling forward after import

Re: rolling forward after import

From: Douglas Hawthorne <>
Date: Mon, 29 Mar 2004 23:49:59 GMT
Message-ID: <HC2ac.130595$>

"Howard J. Rogers" <> wrote in message news:4068b366$0$15065$
> "Douglas Hawthorne" <> wrote in message
> news:Yg2ac.130575$
> > "Peter Laursen" <> wrote in message
> > news:4068aaae$0$18660$
> > > System:, Win2k
> > >
> > > Suppose I have an db in archivelogmode and do a full export.
> > > Some days later I import the full export. Now, how do I roll forward
> > > using the existing archivelogs? The database does not need recovery,
> > > so I expect that no variation of the "recover" cmd will work?
> > >
> > > TIA
> > > Peter Laursen
> > >
> > Peter,
> >
> > I hope you do not mean "a full export" as in doing "exp full=y" because
> > export is a logical copy of the data and does not contain any SCN
> > information. Without the SCN information, the Oracle server cannot roll
> > forward data using the redo logs (online and archived) directly. For a
> more
> > realistic description, see the "Backup and Recovery Guide".
> >
> > However if you are very keen, you can extract the DML applied to the
> tables
> > that you have exported from the archive redo logs via the LogMiner
> > as long as you do not have any 'special' data types. You may wish to
> > consult Appendix A of "Expert One-on-One Oracle" by Thomas Kyte
> > (A-Press:2003) for an overview or search .
> >
> > Using that DML (assuming that you took a consistent export
> > and that you know when the export started), it is very likely that can
> 'roll
> > forward' the imported copy. Whether you are able to ever to catch up
> using
> > this method depends when you decide to stop all activity on the tables
> > concerned.
> It is going to be *very* hard to use Log Miner to "roll forward" in this
> way, because redo works by storing the rowids of the rows affected by DML.
> And the one thing you can guarantee after importing all your data afresh
> that the new data will have completely different rowids from what they
> to have. Therefore, extracting the DML statements via Log Miner is not
> to get you anywhere -not automatically, at any rate.
> Not, at least, until 9i where it becomes possible to embed additional
> information in the redo stream above and beyond the rowid (the primary key
> or the unique key, for example) by adding 'supplemental log groups' to
> particular tables, or to the entire database.
> But that has to be switched on specially (which isn't going to help our
> original poster), and if it's not switched on carefully, it can bring a
> database to its knees: it's no fun swamping LGWR with extra information to
> record on top of the bare information it must record by virtue of some DML
> being performed. Particularly dangerous is the idea of switching on
> supplemental primary key logging at the database level... and then
> discovering some of your tables don't have primary keys. In which case,
> entire row gets written into the redo stream: ouch!
> > I have not tried this scenario myself because I prefer to be lazy and
> > RMAN for backup and recovery.
> >
> > The exp/imp utilities have their place but not realisticly in a database
> > recovery scenario.
> I couldn't agree more.
> Regards


I had forgotten about the rowids being regenerated on import even though the example in Thomas Kyte's book shows the rowids in the LogMiner output.

Thank your correction.

Douglas Hawthorne Received on Mon Mar 29 2004 - 17:49:59 CST

Original text of this message