Re: Lost oracle database but have the datafiles

I thought I was seeing double for a moment there!! Sorry -I didn't realise you were answering the same sort of question.

It should work. I've suggested a recover until cancel, followed by an immediate cancel, and then the open database resetlogs, but apart from that, we're offering the same advice. I think the cancel is required because of the loss of the current redo log (there'll be no 'end of redo' marker, so Oracle won't know that the thing was checkpointed)... but frankly, I'm not going to start deleteing 98% of my database to find out for sure!!

Otherwise, your advice about the init.ora, and so on, is spot on and more comprehensive than I managed.

In the meantime, I'll stop duplicating your efforts!!!

Cheers,
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"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

> > >>

> > >

> >

>

>