Re: Lost oracle database but have the datafiles

Forgot something - just read Howard's post - after recreating controlfile and before opening the DB using resetlogs you'll need to fake recovery by issueing :
- recover database until cancel using backup controlfile ; immediately followed by
- cancel
and then
- alter database open resetlogs ;

sorry about that - thanks Howard

"koert54" <koert54_at_nospam.com> wrote in message news:utKX7.2434$bS5.751834242_at_hebe.telenet-ops.be...
> cool - so you just have the datafiles and no controlfiles, no redologs and
> no init.ora
>
> if you did a shutdown (other than abort) the database checkpointed and is
> consistent - so no redologs are
> needed for instance recovery.
>
> what I should do in this case is :
> 1. on the new machine create a dummy database (this should make it easier
to
> create init.ora and a controlfile)
> 2. issue - 'alter database backup controlfile to trace ;'
> This will generate a dump of the controlfile of the dummy database (in
> $ORACLE_HOME/admin/<SID>/udump)- it looks like this :
> *** 2001-12-30 20:53:53.150
> # The following commands will create a new control file and use it
> # to open the database.
> # Data used by the recovery manager will be lost. Additional logs may
> # be required for media recovery of offline data files. Use this
> # only if the current version of all online logs are available.
> STARTUP NOMOUNT
> CREATE CONTROLFILE REUSE DATABASE "PO" NORESETLOGS NOARCHIVELOG
> MAXLOGFILES 32
> MAXLOGMEMBERS 2
> MAXDATAFILES 254
> MAXINSTANCES 1
> MAXLOGHISTORY 899
> LOGFILE
> GROUP 1 'J:\ORA8I\ORADATA\PO\REDO01.LOG' SIZE 1M,
> GROUP 2 'J:\ORA8I\ORADATA\PO\REDO02.LOG' SIZE 1M,
> GROUP 3 'J:\ORA8I\ORADATA\PO\REDO03.LOG' SIZE 1M
> DATAFILE
> 'J:\ORA8I\ORADATA\PO\SYSTEM01.DBF',
> 'J:\ORA8I\ORADATA\PO\RBS01.DBF',
> 'J:\ORA8I\ORADATA\PO\DUL01.DBF'
> CHARACTER SET WE8ISO8859P1
> ;
> # Recovery is required if any of the datafiles are restored backups,
> # or if the last shutdown was not normal or immediate.
> RECOVER DATABASE
> # Database can now be opened normally.
> ALTER DATABASE OPEN;
> # Commands to add tempfiles to temporary tablespaces.
> # Online tempfiles have complete space information.
> # Other tempfiles may require adjustment.
> ALTER TABLESPACE TEMP ADD TEMPFILE 'J:\ORA8I\ORADATA\PO\TEMP01.DBF' REUSE;
> # End of tempfile additions.
> #
> 3. shutdown the dummy database and remove all of it's files except
init.ora
>
> 4. copy the backup of your datafiles to disk
>
> 5. edit the dump of the controlfile (remove all crap)
> a. NORESETLOGS should become RESETLOGS
> b. set the DATABASE <SID> correctly
> c. edit the DATAFILE clause to confirm with the copy of your datafiles
> d. edit the LOGFILE clause to whatever you like (it's ok if you don't have
> the redologs)
> e. rename the dump to control.sql
>
> STARTUP NOMOUNT
> CREATE CONTROLFILE REUSE DATABASE "PO" NORESETLOGS NOARCHIVELOG
> MAXLOGFILES 32
> MAXLOGMEMBERS 2
> MAXDATAFILES 254
> MAXINSTANCES 1
> MAXLOGHISTORY 899
> LOGFILE
> GROUP 1 'J:\ORA8I\ORADATA\PO\REDO01.LOG' SIZE 1M,
> GROUP 2 'J:\ORA8I\ORADATA\PO\REDO02.LOG' SIZE 1M,
> GROUP 3 'J:\ORA8I\ORADATA\PO\REDO03.LOG' SIZE 1M
> DATAFILE
> 'J:\ORA8I\ORADATA\PO\SYSTEM01.DBF',
> 'J:\ORA8I\ORADATA\PO\RBS01.DBF',
> 'J:\ORA8I\ORADATA\PO\DUL01.DBF'
> CHARACTER SET WE8ISO8859P1
> ;
>
> 5. edit init.ora of the dummy database - you should change DB_NAME,
> INSTANCE_NAME, CONTROL_FILES, etc to comply with the backup
> of your database
>
> 6. svrmgrl
> connect internal
> ->>>> run control.sql
> ->>>> controlfiles are now create according to CONTROL_FILES parameter in
> init.ora
>
> 7. alter database open resetlogs ;
> -->>>> this will create the redologs (see LOGFILE clause control.sql)
>
> Howard - you as backup&recovery guru ... can you double check this ? - I'm
> pretty sure it'll work as no recovery is needed ....
>
>
> "Kev" <java2e_at_yahoo.com> wrote in message
> news:hmru2ug9vr69lcdp1h9j24itri42uk8cun_at_4ax.com...
> > I copied the datafiles after I did a shutdown of the server. Then I
> > rebooted the machine. Then today the harddrive crashed.
> >
> > So I have a clean copy of the datafile. I installed Oracle on another
> > machine and I wanted to move those file to the new installation but I
> > have no idea on how to do it.
> >
> > The other machine oracle was on is dead until I can replace the drive.
> >
> > but the new machine is good to go.
> >
> > Thanks.
> >
> >
> > On Sun, 30 Dec 2001 19:46:59 GMT, "koert54" <koert54_at_nospam.com>
> > wrote:
> >
> > >
> > >how did you backup those datafiles ?????
> > >even if you didn't backup controlfiles and redolog you'll be able to
open
> > >the DB
> > >if you first did a shutdown other than abort and then copied the
> datafiles
> > >....
> > >
> > >1. copy the datafiles back to your drive
> > >2. recreate the controlfile (check the create controlfile command)
> > >3. open the database using resetlogs
> > >
> > >"Kev" <java2e_at_yahoo.com> wrote in message
> > >news:l1ru2uk66fa4ci1bb2gjl44rve2ajadqbl_at_4ax.com...
> > >> Hello Howard,
> > >>
> > >> Here is the problem. I completely lost my harddrive but before I did
> > >> about a day ago I saved the datafiles, no idea of why I did it. And
> > >> about 1 week ago I did a export of the schema I care about.
> > >>
> > >> What I need to do is jsut get one table out of the datafile and the
> > >> rest I dont care about.
> > >>
> > >> I might be abot to get the control files but not the arcive or redos.
> > >>
> > >> I can run the import to create the structure but I need to pull the
> > >> data from the datafiles if this is at all possible.
> > >>
> > >> Thanks,
> > >>
> > >> Kev
> > >>
> > >> On Mon, 31 Dec 2001 06:29:12 +1100, "Howard J. Rogers"
> > >> <dba_at_hjrdba.com> wrote:
> > >>
> > >> >Kev,
> > >> >
> > >> >Your post is not entirely clear... Do you have the datafiles as your
> post
> > >> >title suggests, or do you merely have an export file as your post
> > >contents
> > >> >suggests?
> > >> >
> > >> >If you have the datafiles themselves, do you have a control file as
> well?
> > >> >And do you have archives? Because if that's all true, then you can
> > >perform
> > >> >a standard incomplete recovery and get all your data back, except
for
> the
> > >> >last little bit that was in the current online redo log.
> > >> >
> > >> >If all you've got is the export file, then you'll have to create a
> brand
> > >new
> > >> >database from scratch, and run import to get the data back as it was
> at
> > >the
> > >> >time you took the export. The easiest way to proceed in that
> eventuality
> > >> >would be to manually create all the right tablespaces (and users),
and
> > >then
> > >> >leave import to create all the tables, indexes and so on, and
populate
> > >them.
> > >> >
> > >> >Regards
> > >> >HJR
> > >>
> > >
> >
>
>
Received on Sun Dec 30 2001 - 14:21:57 CST