Restoring DATAFILES only ??!!

Hi to all<

I hope someone can aswer the next question;

Due to a corruption on an Oracle database we had to save the latest backup set ( Datafiles, CTR files, INI.ora and Archive files ) which should be in a valid state.

Unfortinately there was no backup server containg a duplicate environment to recover this backupset but instead a simple workstation must be used. On this workstation there is a complete different filesystem setup and a recovery will not work. (the backupped contolfile points to other file locations as on the workstation available).

The recovery scripts I used sofar (

SQL> connect sys/pw as sysdba;
SQL> shutdown immediate
startup mount
_ set autorecovery on
_ recover database until time '2005-04-11:20:00:00' (using backup controlfile) )

are prompting errors (ora-00283 and ora-01610) time after time so I have to find me some other way to restore the valid backup set.

On the workstation I created a new instance containing a database with the same name as the original database and I want to attach the DATEFILES (only) on this new created database.

Is this a to be accomplished and if so please can you tell me how?

The thing I tried sofar;

1) I Created a database on the workstation (with the name).
2) Changing all datafile-names (or add if not excisting) as found in the syntax "alter database backup controlfile to trace;"
3) Changing all pathname in the script to be valid.
4) Logging in using commandline; sqlplus '/ as sysdba'
5) Gave a "shutdown immediate"
6) Copy overwrite the original files with the files to attach.
7) startup mount
8) executed the script (see below)

and here it goes wrong (Dutch version);
FOUT in regel 1:
.ORA-01503: CREATE CONTROLFILE is mislukt.
ORA-01159: Bestand niet uit dezelfde database als vorige bestanden; onjuiste database-ID.
ORA-01110: Gegevensbestand 5: 'D:\ORACLE\ORADATA\PROD\PRODUSR.ORA' (One testfile which is overwrritten bij one of the files to be attached ( with the same name))

next steps should be;

9)Recover database until cancel using backup controlfile; ( Cancel when required ).
10) Alter database open resetlogs.


Used script from "alter database backup controlfile to trace;" ;

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 16
MAXLOGHISTORY 1815
LOGFILE
GROUP 1 'D:\ORACLE\ORADATA\PROD\REDO03.LOG' SIZE 1M,
GROUP 2 'D:\ORACLE\ORADATA\PROD\REDO02.LOG' SIZE 1M,
GROUP 3 'D:\ORACLE\ORADATA\PROD\REDO01.LOG' SIZE 1M
DATAFILE
'D:\ORACLE\ORADATA\PROD\SYSTEM01.DBF',
'D:\ORACLE\ORADATA\PROD\RBS01.DBF',
'D:\ORACLE\ORADATA\PROD\USERS01.DBF',
'D:\ORACLE\ORADATA\PROD\TEMP01.DBF',
'D:\ORACLE\ORADATA\PROD\TOOLS01.DBF',
'D:\ORACLE\ORADATA\PROD\INDX01.DBF',
'D:\ORACLE\ORADATA\PROD\DR01.DBF',
'D:\ORACLE\ORADATA\PROD\PRODDATA.ORA',
'D:\ORACLE\ORADATA\PROD\PRODRBS.ORA',
'D:\ORACLE\ORADATA\PROD\PRODSYST.ORA',
'D:\ORACLE\ORADATA\PROD\PRODTEMP.ORA',
'D:\ORACLE\ORADATA\PROD\PRODTOOL.ORA',
'D:\ORACLE\ORADATA\PROD\PRODUSR.ORA',
'D:\ORACLE\ORADATA\PROD\PRODILOG.ORA',
'D:\ORACLE\ORADATA\PROD\.ORA',
'D:\ORACLE\ORADATA\PROD\PRODILOG2.ORA'
CHARACTER SET WE8ISO8859P1
;


I you can help me out please do because I'm spending the whole day to restore this backup (and I almost being cooked by now !!))

Gkramer
The Netherlands

You can't 'attach' datafiles. Think about it, your system tablespace in your new database cannot know what is in those datafiles.

But you got the solution right before you.

Assuming your workstation is the same os, try:

start from scratch, restore all your files again. Edit the following so all your locations are correct, note the 'set' and 'resetlogs':

CREATE CONTROLFILE set DATABASE "PROD" RESETLOGS ARCHIVELOG
MAXLOGFILES 32
MAXLOGMEMBERS 2
MAXDATAFILES 32
MAXINSTANCES 16
MAXLOGHISTORY 1815
LOGFILE
GROUP 1 'D:\ORACLE\ORADATA\PROD\REDO03.LOG' SIZE 1M,
GROUP 2 'D:\ORACLE\ORADATA\PROD\REDO02.LOG' SIZE 1M,
GROUP 3 'D:\ORACLE\ORADATA\PROD\REDO01.LOG' SIZE 1M
DATAFILE
'D:\ORACLE\ORADATA\PROD\SYSTEM01.DBF',
'D:\ORACLE\ORADATA\PROD\RBS01.DBF',
'D:\ORACLE\ORADATA\PROD\USERS01.DBF',
'D:\ORACLE\ORADATA\PROD\TEMP01.DBF',
'D:\ORACLE\ORADATA\PROD\TOOLS01.DBF',
'D:\ORACLE\ORADATA\PROD\INDX01.DBF',
'D:\ORACLE\ORADATA\PROD\DR01.DBF',
'D:\ORACLE\ORADATA\PROD\PRODDATA.ORA',
'D:\ORACLE\ORADATA\PROD\PRODRBS.ORA',
'D:\ORACLE\ORADATA\PROD\PRODSYST.ORA',
'D:\ORACLE\ORADATA\PROD\PRODTEMP.ORA',
'D:\ORACLE\ORADATA\PROD\PRODTOOL.ORA',
'D:\ORACLE\ORADATA\PROD\PRODUSR.ORA',
'D:\ORACLE\ORADATA\PROD\PRODILOG.ORA',
'D:\ORACLE\ORADATA\PROD\.ORA',
'D:\ORACLE\ORADATA\PROD\PRODILOG2.ORA'
CHARACTER SET WE8ISO8859P1
;

Now try:
> startup nomount
> run the script.
If it was a hot backup:
> recover database until cancel using backup controlfile
> alter database open resetlogs;