14 Replies Latest reply: Dec 27, 2013 4:40 AM by Eduardo Legatti RSS

Unalble to open database.

Mahfuzur Rahman Newbie
Currently Being Moderated

Hello experts,

my database version 10.2.0.1 on OEL 5.8

I've try several times to open my database , search over internet but not get proper solution

Please help.

 

when i try to open database

=======================================================================

SQL> alter database open;

alter database open

*

ERROR at line 1:

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-01152: file 1 was not restored from a sufficiently old backup

ORA-01110: data file 1: '/u01/app/oracle/oradata/TSH1/system01.dbf'

 

and when i restore datafile 5

RMAN> restore datafile 1;

 

Starting restore at 25-DEC-13

using channel ORA_DISK_1

 

skipping datafile 1; already restored to file /u01/app/oracle/oradata/TSH1/system01.dbf

restore not done; all files readonly, offline, or already restored

Finished restore at 25-DEC-13

and recover database

RMAN> recover database;

 

Starting recover at 25-DEC-13

using channel ORA_DISK_1

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 12/25/2013 18:03:40

RMAN-06094: datafile 5 must be restored

and when try to restore datafile 5

RMAN> restore datafile 5;

 

Starting restore at 25-DEC-13

using channel ORA_DISK_1

 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 12/25/2013 18:03:53

RMAN-06085: must use SET NEWNAME command to restore datafile /u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005

but there is no file such a name

  • 1. Re: Unalble to open database.
    Eduardo Legatti Explorer
    Currently Being Moderated

    Hi,

     

    ORA-01152: file 1 was not restored from a sufficiently old backup  
    Cause: An incomplete recovery session was started, but an insufficient number of logs were applied to make the database consistent. This file is still in the future of the last log applied. The most likely cause of this error is forgetting to restore the file from a backup before doing incomplete recovery.
    Action: Either apply more logs until the database is consistent or restore the database file from an older backup and repeat recovery.


    What exactly commands did you use in RMAN to restore/recover the database?


    Cheers


    Legatti

  • 2. Re: Unalble to open database.
    Mahfuzur Rahman Newbie
    Currently Being Moderated

    RMAN>  restore database;
    Starting restore at 25-DEC-13
    released channel: ORA_DISK_1
    allocated channel: ORA_DISK_1
    channel ORA_DISK_1: sid=155 devtype=DISK
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of restore command at 12/25/2013 20:24:11
    RMAN-06085: must use SET NEWNAME command to restore datafile /u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005


    this file is not exists in this location

  • 3. Re: Unalble to open database.
    Aman.... Oracle ACE
    Currently Being Moderated

    Use search facility of this forum.

    RMAN-06085

     

    HTH

    Aman....

  • 4. Re: Unalble to open database.
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    You are hiding a lot of details.  Apparently, a RECOVER DATABASE has been attempted prior to this.  Apparently, at least one datafile is mssing. Possibly a CREATE CONTROLFILE has been executed with a missing datafile.  All of these *before* the messages you have shown us.  We can't help you if you don't reveal everything -- starting with the first error and continuing through everything you have attempted.

     

     

    Hemant K Chitale

  • 5. Re: Unalble to open database.
    Mahfuzur Rahman Newbie
    Currently Being Moderated

    Yes Sir, create control file has been executed.but it shows a datafile is missing .but this file name UNNAMED00005 and such file is not exists in my datafile.

  • 6. Re: Unalble to open database.
    Suntrupth Expert
    Currently Being Moderated

    Hi,

     

    Can you let me know the output of the below commands?

     

     

    SQL> select * from v$recover_file where error like '%FILE%';

     

     

    SQL> select file#,name from v$datafile where file#=<number from above query>;

     

     

    Are you performing a recovery from current or backup controlfile?

     

    Regards,

    Suntrupth

  • 7. Re: Unalble to open database.
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    Is the original datafile (or a backup of it) still available even though it is no longer part of the database ?  You  can use the ALTER DATABASE RENAME DATAFILE command if you can restore the original datafile.

     

     

    Hemant K Chitale

  • 8. Re: Unalble to open database.
    Mahfuzur Rahman Newbie
    Currently Being Moderated

    @Suntrupth

    SQL> select * from v$recover_file where error like '%FILE%';

     

    FILE# ONLINE  ONLINE_ERROR   CHANGE# TIME

       5 ONLINE       ONLINE            FILE MISSING

    SQL> select file#,name from v$datafile where file#=5;

    FILE#                                  NAME

    --------------------------------------------------------------------------------

             5                          /u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005

  • 9. Re: Unalble to open database.
    Mahfuzur Rahman Newbie
    Currently Being Moderated

    @Hemant K Chitale  Sir,

     

    when i take backup of database this file

    /u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005

    was not part of database. is there any way to remove this datafile entry from control file.

     

    regards

    Mahfuz

  • 10. Re: Unalble to open database.
    Hemant K Chitale Oracle ACE
    Currently Being Moderated

    The datafile with fileid 5  was part of the database earlier.  It's name was not "UNANMED00005" -- that is the name assigned to it now because you ran a CREATE CONTROLFILE without specifying the correct filename for datafile 5 which belongs to the database. I do not know if it was included in the backup.

     

    If you really really do not have a backup of the datafile (please check again !!!!!!!!!!!), you could remove it with an ALTER DATABASE DATAFILE 5 DROP;

    BUT CHECK TO SEE IF THE DATAFILE OR A BACKUP OF IT STILL EXISTS -- it's real name is something *other* than   "/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005"

     

    Hemant K Chitale

  • 11. Re: Unalble to open database.
    Suntrupth Expert
    Currently Being Moderated

    Seems like oracle has detected inconsistencies between the data dictionary and the control file being used.

     

    alter database rename file ' /u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005' to 'correct location of the data file';

     

    You can review your "alert.log" to see the name of the file that you need to input in the above statement.

     

    To find the objects which reside on datafile 5 please perform :

     

    SQL> select owner,segment_name,segment_type from dba_exents where file_id = 5 and tablespace name = (select name from v$tablespace where file#=5);

     

    Check the alert log for any relevance to datafile 5.

     

    -OR- as Hemant has suggested, you can drop this datafile/tablespace if its not being used.

     

    Regards,

    Suntrupth

  • 12. Re: Unalble to open database.
    Mahfuzur Rahman Newbie
    Currently Being Moderated

    @Hemant K Chitale   sir,


    I've found the number 5 datafile .in a location .how can i restore  and recovery this file

     

    regards

    manfuz

  • 13. Re: Unalble to open database.
    Suntrupth Expert
    Currently Being Moderated

    Hi Manfuz,


    Mount the DB


    alter database rename file ' /u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005' to 'correct location of the data file';


    Recover database


    Regards,

    Suntrupth

  • 14. Re: Unalble to open database.
    Eduardo Legatti Explorer
    Currently Being Moderated

    Hi,

     

    The question is: Do you re-created the control file? In your case, I would try to restore the CONTROL FILE that was consistent with your data files. By the way, you are using RMAN!!

     

    I've found the number 5 datafile .in a location .how can i restore  and recovery this file

     

    I would try these steps:

     

    SQL> alter database create datafile '/u01/app/oracle/product/10.2.0/db_1/dbs/UNNAMED00005' as 'location/name of your datafile';

    SQL> recover database until cancel using backup controlfile;

    SQL> alter database open resetlogs;

     

    Cheers

     

    Legatti

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points