Restoring DATAFILES only ??!!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Restoring DATAFILES only ??!!

  1. #1
    Join Date
    Apr 2003
    Location
    Rosmalen, Netherlands.
    Posts
    73

    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
    Last edited by GKramer; 04-15-2005 at 09:56 AM.

  2. #2
    Join Date
    Jul 2002
    Posts
    335
    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;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width