REPOST: Re: Lost oracle database but have the datafiles

From: koert54 <koert54_at_nospam.com>
Date: Sun, 30 Dec 2001 20:06:50 GMT
Message-ID: <9$--$$-$$_%-%__$$$@news.noc.cabal.int>

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
> >>
> >
>

• WAS CANCELLED BY =======: Path: news.sol.net!spool1-nwblwi.newsops.execpc.com!newsfeeds.sol.net!news-out.visi.com!hermes.visi.com!uunet!ash.uu.net!sac.uu.net!lax.uu.net!news.navix.net!u-n-c-a-n-c-e-l-l-e-r From: "koert54" <koert54_at_nospam.com> Newsgroups: alt.config,comp.lang.c,comp.databases.oracle.server Subject: cmsg cancel <utKX7.2434$bS5.751834242_at_hebe.telenet-ops.be> Control: cancel <utKX7.2434$bS5.751834242_at_hebe.telenet-ops.be> Date: Mon, 31 Dec 2001 07:24:59 GMT Organization: Navix Internet Subscribers Lines: 2 Message-ID: <cancel.utKX7.2434$bS5.751834242_at_hebe.telenet-ops.be> NNTP-Posting-Host: 166.102.15.34 X-Trace: iac5.navix.net 1009788666 26548 166.102.15.34 (31 Dec 2001 08:51:06 GMT) X-Complaints-To: abuse_at_navix.net NNTP-Posting-Date: 31 Dec 2001 08:51:06 GMT X-No-Archive: yes Comment: Anarchy! Fuck You! X-Commentary: I love NewsAgent 1.10, Sandblaster Build 74 (19 March 1999) and the Polaris Cancel Engine V. 6.1 X-Unacanc3l: yes

This message was cancelled from within Mozilla...not Received on Sun Dec 30 2001 - 14:06:50 CST