DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Recovering a DB without a Backup

  1. #1
    Join Date
    Sep 2001
    Location
    Pondicherry-India
    Posts
    33

    Question


    Hi guys


    I am trying to Recover a DB without a Backup:

    I issued the command

    1: ALTER DATABASE CREATE DATAFILE 'e:\oracle\oradata\pnl\users01.dbf' as
    2:'e:\oracle\users01.dbf';

    which is showing the error message :

    ERROR at line 1:

    ORA-01178: file 3 created before last CREATE CONTROLFILE, cannot recreate

    ORA-01110: data file 3: 'e:\oracle\oradata\pnl\users01.dbf'



    help me out


    RAMJI

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    The error is because the datafile was created before the last CREATE CONTROLFILE has been issued.
    Since the CONTROLFILE has been recreated after the datafile creation, you can not create the datafile using this method. You will have to restore from a backup copy of datafile.

    Are you in archivelog mode? Even if you are able to create the file you will need all the archived redo logs since the time the datafile was originally created.

    Sanjay

  3. #3
    Join Date
    Sep 2001
    Location
    Pondicherry-India
    Posts
    33

    Question

    Hi Sanjay

    I have tried your suggestion with the following statement

    create controlfile
    set database pnl
    logfile group 1 ('e:\oracle\oradata\pnl\redo03.log','e:\oracle\ora81\database\redo01') size 1024K,
    group 2 ('e:\oracle\oradata\pnl\redo02.log','e:\oracle\oradata\pnl\redo04.log') size 1024K,
    group 3
    ('e:\oracle\oradata\pnl\log3.ora','e:\oracle\oradata\pnl\log4.ora') size 1024K
    resetlogs
    datafile 'e:\oracle\oradata\pnl\system01.dbf',
    'e:\oracle\oradata\pnl\rbs01.dbf',
    'e:\oracle\oradata\pnl\users01.dbf',
    'e:\oracle\oradata\pnl\temp01.dbf',
    'e:\oracle\oradata\pnl\tools01.dbf',
    'e:\oracle\oradata\pnl\indx01.dbf',
    'e:\oracle\oradata\pnl\dr01.dbf',
    'e:\oracle\oradata\pnl\oem_repository.ora'
    maxlogfiles 50
    maxlogmembers 3
    maxdatafiles 1024
    maxinstances 6
    archivelog
    /




    When i executed I have the following message :


    ERROR at line 1:
    ORA-01503: CREATE CONTROLFILE failed
    ORA-00200: controlfile could not be created
    ORA-00202: controlfile: 'e:\oracle\oradata\pnl\control01.ctl'
    ORA-27038: skgfrcre: file exists
    OSD-04010: option specified, file already exists



    How to proceed now.

    Regards

    Ramji

  4. #4
    Join Date
    Jun 2002
    Location
    VA,USA
    Posts
    83
    hi,
    create ur datafile on the same location where u had ur old datafile.

    >alter database create datafile 'e:\oracle\oradata\pnl\dr01.dbf' as 'e:\oracle\oradata\pnl\dr01.dbf';

    then recover ur datafile.

    >recover datafile ''e:\oracle\oradata\pnl\dr01.dbf';

    saravana kumar

  5. #5
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Time and time again I see posts such as 'recover db without backup'

    Oracle DBA's should have this embedded into them :-

    BACK UP YOUR DATABASE!!

    It is a fundamental part of a DBA's job and always has been.

    If anyone has taught you difffrently then they are teaching you wrong.

    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    funny question heh

  7. #7
    Join Date
    Sep 2001
    Location
    Pondicherry-India
    Posts
    33
    Hi

    I deleted the previous DB and created a new one using the Database Configuration Assistant. After creating the Database, I went to DBA Studio where i saw the Sequence Number for the Redo log was starting from 526 and I changed the DB to Archive Log mode.

    Then I created a Table in the Users tablespace and started doing transactions on this table. Now the Online Redo Log become 528 and i used alter system archive log all to archive 526 and 527. At this stage my datafile got corrupted and now i want to retrieve it back.

    I issued the statement Alter database create datafile 'e:\oracle\oradata\pnl\users01.dbf' as 'e:\oracle\oradata\pnl\users01.dbf'

    Database Altered

    Then i tried RECOVER DATABASE;

    SQL> recover database;
    ORA-00279: change 3758 generated at 07/17/2002 18:28:04 needed for thread 1
    ORA-00289: suggestion : E:\ORACLE\ORA81\RDBMS\ARC00008.001
    ORA-00280: change 3758 for thread 1 is in sequence #8


    Specify log: {=suggested | filename | AUTO | CANCEL}
    e:\oracle\ora81\rdbms\arc00527.001
    ORA-00310: archived log contains sequence 527; sequence 8 required
    ORA-00334: archived log: 'E:\ORACLE\ORA81\RDBMS\ARC00527.001'



    Here ARC00008.001 is the archive log which i got for my previous database and it was there in the same directory when i first started Recover Database statement.

    The system is not allowing me to proceed further. How to go about this problem.

    Regards

    Ramji


  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you need to read

    http://otn.oracle.com/docs/products/...a96519/toc.htm

    http://otn.oracle.com/docs/products/...tore.htm#19041

    If a datafile is damaged and no backup of the file is available, then you can still recover the datafile if:

    All archived log files written after the creation of the original datafile are available
    The control file contains the name of the damaged file (that is, the control file is current, or is a backup taken after the damaged datafile was added to the database)

    do you have all that? doubtful

  9. #9
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    ramji_1123..use key word reuse..in ur create controlfile statement.
    anandkl

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