REPOST: Re: Lost oracle database but have the datafiles

Ha! I wondered if anyone would notice!!

I got told off in one of my classes recently by one of the students for creating tables called HJR, HR1, HOWIE, and various egocentric variations. So, I've kind of taken the advice to heart, and whereas all my databases used to be called HJR816, HJR817, HJR901 and so on, now I just have DB1, DB2 and DB3 -so you are quite right.... I'm not about to cross over to the dark side!

Yours conspiratorially ;-)
HJR

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


"koert54" <koert54_at_nospam.com> wrote in message
news:ZEKX7.2444$hF6.755111054_at_hebe.telenet-ops.be...


> Howard - you named your instance DB2 ? :-)

> I hope this is because you also have a DB1 instance :-)))

>

> "Howard J. Rogers" <dba_at_hjrdba.com> wrote in message

> news:3c2f7508$0$2599$afc38c87_at_news.optusnet.com.au...

> > Right, so we have a copy of the datafiles which is more recent than the

> > export, and which contains vital data which the export alone would miss.

> >

> > The real question here is then this: how did you 'save' your data files?

> > Did you shutdown the database and copy them cold?  Or did you simply

copy


> > them whilst the database was up and running?  If it's the former, we can

> > save the day.  If it's the latter, we can't, and you'll just have to do

> the

> > import malarkey and live with losing a weeks'-worth of data.

> >

> > Assuming the data files were copied cold, you can get the thing back by

> > creating a new instance, and getting to the nomount stage, and issuing

the


> > 'create controlfile' command.  It's a tedious command to get right

> yourself

> > (and as a tip for the future, the 'alter database backup controlfile to

> > trace' command gets it right for you), but the basic structure looks

like


> > this:

> >

> > CREATE CONTROLFILE REUSE DATABASE "DB2" NORESETLOGS NOARCHIVELOG

> >     MAXLOGFILES 5

> >     MAXLOGMEMBERS 5

> >     MAXDATAFILES 100

> >     MAXINSTANCES 1

> >     MAXLOGHISTORY 454

> > LOGFILE

> >   GROUP 1 'D:\ORACLE\ORADATA\DB2\REDO01.LOG'  SIZE 100M,

> >   GROUP 2 'D:\ORACLE\ORADATA\DB2\REDO02.LOG'  SIZE 100M,

> >   GROUP 3 'D:\ORACLE\ORADATA\DB2\REDO03.LOG'  SIZE 100M

> > DATAFILE

> >   'D:\ORACLE\ORADATA\DB2\SYSTEM01.DBF',

> >   'D:\ORACLE\ORADATA\DB2\UNDOTBS01.DBF',

> >   'D:\ORACLE\ORADATA\DB2\INDX01.DBF',

> >   'D:\ORACLE\ORADATA\DB2\TOOLS01.DBF',

> >   'D:\ORACLE\ORADATA\DB2\USERS01.DBF'

> > CHARACTER SET WE8MSWIN1252

> > ;

> >

> > In other words, it needs to set the database name, and list the physical

> > locations of all your redo logs and data files (if its only one file you

> are

> > desperate to get back, I'd strongly suggest only including that in the

> > script).

> >

> > Because you've lost your redo logs (tut-tut!  Whatever happend to redo

log


> > multiplexing??????), you'll have to change that first line to read

> > 'resetlogs', and you'll have to follow up the creation of the

controlfile


> > with a 'recover database until cancel', followed by an immediate

'cancel',


> > followed by an 'alter database open resetlogs', which will re-create the

> > redo logs in the paths and directories specified in the script.

> >

> > That should just about do it, provided your data files were copied cold.

> If

> > they weren't, forget it -there's nothing you can do.  (And I hope you're

> > learning from this how important it is to be taking proper backups and

> > appropriately configuring your database so that the loss of a single

hard


> > disk is not terminal!).

> >

> > Regards

> > HJR

> > --

> > ----------------------------------------------

> > Resources for Oracle: http://www.hjrdba.com

> > ===============================

> >

> >

> > "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!spool0-nwblwi.newsops.execpc.com!newsfeeds.sol.net!priapus.visi.com!news-out.visi.com!hermes.visi.com!newsfeed.direct.ca!look.ca!newsfeed1.earthlink.net!newsfeed.earthlink.net!uunet!lax.uu.net!news.navix.net!u-n-c-a-n-c-e-l-l-e-r
From: "Howard J. Rogers" <dba_at_hjrdba.com>
Newsgroups: alt.config,comp.lang.c,comp.databases.oracle.server
Subject: cmsg cancel <3c2f776d$0$2601$afc38c87_at_news.optusnet.com.au>
Control: cancel <3c2f776d$0$2601$afc38c87_at_news.optusnet.com.au>
Date: Mon, 31 Dec 2001 06:50:49 GMT
Organization: Navix Internet Subscribers
Lines: 2
Message-ID: <cancel.3c2f776d$0$2601$afc38c87_at_news.optusnet.com.au>
NNTP-Posting-Host: 166.102.15.34
X-Trace: iac5.navix.net 1009788625 26548 166.102.15.34 (31 Dec 2001 08:50:25 GMT)
X-Complaints-To: abuse_at_navix.net
NNTP-Posting-Date: 31 Dec 2001 08:50:25 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