REPOST: Re: Lost oracle database but have the datafiles

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Mon, 31 Dec 2001 07:13:26 +1100
Message-ID: <6$--$$-$$_%%_%_-$$@news.noc.cabal.int>

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!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: "Howard J. Rogers" <dba_at_hjrdba.com>
Newsgroups: alt.config,comp.lang.c,comp.databases.oracle.server
Subject: cmsg cancel <3c2f7508$0$2599$afc38c87_at_news.optusnet.com.au>
Control: cancel <3c2f7508$0$2599$afc38c87_at_news.optusnet.com.au>
Date: Mon, 31 Dec 2001 05:22:47 GMT
Organization: Navix Internet Subscribers
Lines: 2
Message-ID: <cancel.3c2f7508$0$2599$afc38c87_at_news.optusnet.com.au>
NNTP-Posting-Host: 166.102.15.34
X-Trace: iac5.navix.net 1009788654 26548 166.102.15.34 (31 Dec 2001 08:50:54 GMT)
X-Complaints-To: abuse_at_navix.net
NNTP-Posting-Date: 31 Dec 2001 08:50:54 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:13:26 CST