Re: Associating an orphaned datafile with a new database

"Maximus" <qweqwe_at_qwqwewq.com> wrote in message news:69u%a.770858$Vi5.17435006_at_news1.calgary.shaw.ca...
> "Sam Jones" <zing43000_at_yahoo.com> wrote in message
> news:2663d67c.0308160153.13cc3807_at_posting.google.com...
> > Hi,
> > We had a series of problems (human errors compounded by equipment
> > failures compounded by the big blackout) that caused our only
> > SystemFile to disappear, including our only backup of it. (Argh)
> > We kept all the local datafiles separate. We also have the latest
> > valid control files and all online and archived redo logs.
> > Is there any way to create a new database (using the same creation
> > script, file locations and sizes) as the old one, and convince Oracle
> > that it should incorporate the saved/old datafiles from prior to the
> > disaster?
>
> You need at the very least an old backup of the system datafile to begin
> recovery, without it there is no synchronization point with which to apply
> redo logs. Oracle will reject any datafile that does not belong to the
> original set.

... an additional note, if you are running 9i and were running in ARCHIVE mode, you may want to explore what package DBMS_LOGMNR and DBMS_LOGMNR_D can do. You can extract data from the logfiles manually using these utilities, the problem is that you won't have access to valid dictionary data so you will have to create a bogus dictionary file, then manually identify objects by their obj# and process the data to turn it back into usable SQL. For instance, this example will extract all the CREATE sql from the redo logs for user PROJECT:

set serveroutput on
connect sys/********@db01 as sysdba
--execute dbms_logmnr_d.build('dictionary.ora', '/tmp', options => dbms_logmnr_d.store_in_flat_file);
execute dbms_logmnr.add_logfile( logfilename => '/opt/oracle/oradata/db01/redo01.log', options => dbms_logmnr.new); execute dbms_logmnr.add_logfile( logfilename => '/opt/oracle/oradata/db01/redo02.log', options => dbms_logmnr.addfile); execute dbms_logmnr.add_logfile( logfilename => '/opt/oracle/oradata/db01/redo03.log', options => dbms_logmnr.addfile); execute dbms_logmnr.start_logmnr( dictfilename => '/tmp/dictionary.ora' ); select sql_redo from v$logmnr_contents where username='PROJECT' and sql_redo like '%CREATE%';
execute dbms_logmnr.end_logmnr();

HTH, have fun. Received on Sat Aug 16 2003 - 16:55:00 CDT