This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Feb 11, 2013 12:37 AM by Fran RSS

file 2 needs media recovery data file 2 undotbs01.dbf

_hifni Newbie
Currently Being Moderated
Uses: Oracle 9i, Windows XP, 40GB Disk space, 1GB Ram;

The terminal above serves as my test DB. I use this terminal to import dump file from Production Server every day. I tried to open the Database by using GUI Enterprise Console > Configuration but I got this error below:

ORA-01113: file 2 needs media recovery
ORA-0110: data file 2: c:\oracle\oradata\orcl\undotbs01.dbf

I did research prior to this, but scenario is quite different. As I said earlier, I only need to up the database and data loss is irrelevant.

I tried the commands after login as Sys as Sysdba, but I'm getting the following error.

SQL*Plus: Release 9.2.0.6.0 - Production on Wed Feb 6 19:13:19 2013

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: sys as sysdba
Enter password:

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 487662076 bytes
Fixed Size 455164 bytes
Variable Size 385875968 bytes
Database Buffers 100663296 bytes
Redo Buffers 667648 bytes
Database mounted.
SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-00354: corrupt redo log block header
ORA-00353: log corruption near block 108807 change 116801561 time 02/06/2013
07:53:24
ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\ORCL\REDO01.LOG'


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 2 needs media recovery
ORA-01110: data file 2: 'C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF'


SQL> show parameter background

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
background_core_dump string partial
background_dump_dest string C:\oracle\admin\ORCL\bdump
SQL> select * from v$log
2 ;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIM
------------- ---------
1 1 10781 104857600 1 NO ACTIVE
116801019 06-FEB-13

2 1 10782 104857600 1 NO CURRENT
116802439 06-FEB-13

3 1 10780 104857600 1 NO INACTIVE
116800243 06-FEB-13


SQL>

I'm a novice in DBA admin setups. All I need is to up the Database and resume my operation of importing dump. Data loss is not relevant.
  • 1. Re: file 2 needs media recovery data file 2 undotbs01.dbf
    EdStevens Guru
    Currently Being Moderated
    _hifni wrote:
    Uses: Oracle 9i, Windows XP, 40GB Disk space, 1GB Ram;
    9i is very old, and totally unsupported.

    The terminal above serves as my test DB. I use this terminal to import dump file from Production Server every day. I tried to open the Database by using GUI Enterprise Console > Configuration but I got this error below:

    ORA-01113: file 2 needs media recovery
    ORA-0110: data file 2: c:\oracle\oradata\orcl\undotbs01.dbf

    I did research prior to this, but scenario is quite different. As I said earlier, I only need to up the database and data loss is irrelevant.

    I tried the commands after login as Sys as Sysdba, but I'm getting the following error.

    SQL*Plus: Release 9.2.0.6.0 - Production on Wed Feb 6 19:13:19 2013

    Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

    Enter user-name: sys as sysdba
    Enter password:

    Connected to:
    Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
    With the OLAP and Oracle Data Mining options
    JServer Release 9.2.0.6.0 - Production

    SQL> shutdown immediate
    ORA-01109: database not open


    Database dismounted.
    ORACLE instance shut down.
    SQL> startup mount
    ORACLE instance started.

    Total System Global Area 487662076 bytes
    Fixed Size 455164 bytes
    Variable Size 385875968 bytes
    Database Buffers 100663296 bytes
    Redo Buffers 667648 bytes
    Database mounted.
    SQL> recover database;
    ORA-00283: recovery session canceled due to errors
    ORA-00354: corrupt redo log block header
    ORA-00353: log corruption near block 108807 change 116801561 time 02/06/2013
    07:53:24
    ORA-00312: online log 1 thread 1: 'C:\ORACLE\ORADATA\ORCL\REDO01.LOG'


    SQL> alter database open;
    alter database open
    *
    ERROR at line 1:
    ORA-01113: file 2 needs media recovery
    ORA-01110: data file 2: 'C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF'


    SQL> show parameter background

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    background_core_dump string partial
    background_dump_dest string C:\oracle\admin\ORCL\bdump
    SQL> select * from v$log
    2 ;

    GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
    ---------- ---------- ---------- ---------- ---------- --- ----------------
    FIRST_CHANGE# FIRST_TIM
    ------------- ---------
    1 1 10781 104857600 1 NO ACTIVE
    116801019 06-FEB-13

    2 1 10782 104857600 1 NO CURRENT
    116802439 06-FEB-13

    3 1 10780 104857600 1 NO INACTIVE
    116800243 06-FEB-13


    SQL>

    I'm a novice in DBA admin setups. All I need is to up the Database and resume my operation of importing dump. Data loss is not relevant.
    Data loss may be irrelevant for the immediate, but since you are "a novice in DBA admin setups" I'd advise you to take this opportunity to learn about recovery by treating this as if data loss were important.
  • 3. Re: file 2 needs media recovery data file 2 undotbs01.dbf
    _hifni Newbie
    Currently Being Moderated
    Hi Ed,

    Yes, Oracle 9i is very old, but thats the least I got.

    Yes, I would like to learn, pls. guide me.
  • 4. Re: file 2 needs media recovery data file 2 undotbs01.dbf
    Helios-GunesEROL Oracle ACE
    Currently Being Moderated
    Hi;

    Please review:
    Database Startup Fails with ORA-01113, ORA-01110 errors [ID 146039.1]

    Regard
    Helios
  • 5. Re: file 2 needs media recovery data file 2 undotbs01.dbf
    EdStevens Guru
    Currently Being Moderated
    _hifni wrote:
    Hi Ed,

    Yes, Oracle 9i is very old, but thats the least I got.
    Is this a "personal" system, or does it belong to your employer?

    If it is personal, for your own education, why not just download 11.2 ?
    Yes, I would like to learn, pls. guide me.
  • 6. Re: file 2 needs media recovery data file 2 undotbs01.dbf
    Max Seleznev Explorer
    Currently Being Moderated
    Since you do not have all the redo data the only option you have at the moment is try and perform incomplete database recovery until the last available change number.

    From your log
    ORA-00354: corrupt redo log block header
    ORA-00353: log corruption near block 108807 change 116801561 time 02/06/2013
    07:53:24
    You can try

    SQL> recover database until change 116801560;

    ...

    SQL> alter database open resetlogs;

    I would second the above opinion regarding 9i database being unsupported. Please consider using later verision even if for pure educational purpose.

    Hope it helps.
  • 7. Re: file 2 needs media recovery data file 2 undotbs01.dbf
    PavanKumar Guru
    Currently Being Moderated
    Hi,

    As you are finding issue with undo, make the datafile offline (drop)

    => alter system set undo_management = manual scope=spfile;
    now shutdown your database (In-case of pfile edit the parameter fiile undo_management=manual)
    => shut immediate
    =>startup
    => alter database datafile <data_file_number_of_undo> offline for drop;
    or using file name with path
    now open the database
    => alter database open .

    Now,we have manual undo tablespace management, so you drop the exiting undo tablespace and create new undo tbs and set management to auto based on spfile/pfile
    drop tablespace undo_tbs;
    create UNDO tablespace undotbs1 datafile <> ... ;
    rebounce the db .. that would solve your problem.

    - Pavan Kumar N
  • 8. Re: file 2 needs media recovery data file 2 undotbs01.dbf
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    ORA-00354: corrupt redo log block header
    ORA-00353: log corruption near block 108807 change 116801561 time 02/06/2013
    indicate that your files are corrupt.

    How do you "import dump file from Production Server every day" ? Are you running Oracle's export and import utilities (exp.exe and imp.exe on Windows) ? Is the production server a Windows server OR is it some other platform ?
    Are you copying datafiles and redo logfiles ?

    Does the database on your PC get shutdown properly each time ? (i.e. is the right SHUTDOWN IMMEDIATE or SHUTDOWN NORMAL command used)


    Hemant K Chitale
  • 9. Re: file 2 needs media recovery data file 2 undotbs01.dbf
    _hifni Newbie
    Currently Being Moderated
    Hi Pavan,

    I followed your Instruction till the following points.

    => alter system set undo_management = manual scope=spfile;
    now shutdown your database (In-case of pfile edit the parameter fiile undo_management=manual)
    => shut immediate

    When I startup again, I'm getting this error and puzzled over here:

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

    What can I do? Kindly require help?

    -----

    Hi Hemant,

    h6. How do you "import dump file from Production Server every day" ? Are you running Oracle's export and import utilities (exp.exe and imp.exe on Windows) ?
    Yes, I use the Export and Import Utilities on Windows

    h6. Is the production server a Windows server OR is it some other platform ?
    Windows

    h6. Are you copying datafiles and redo logfiles ?
    No

    h6. Does the database on your PC get shutdown properly each time ? (i.e. is the right SHUTDOWN IMMEDIATE or SHUTDOWN NORMAL command used)
    Until inception its only twice or thrice its been restarted by PC itself. I dont throw commands

    Edited by: _hifni on Feb 7, 2013 6:46 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
  • 10. Re: file 2 needs media recovery data file 2 undotbs01.dbf
    Hemant K Chitale Oracle ACE
    Currently Being Moderated
    If you used Export-Import and we assume that database shutdowns were executed by the oracle service ..... why is one of the online redo log files corrupt ?
    That is what you have to investigate. When did the corruption occur ? Could it have spread to other datafiles as well (undetected yet because you haven't accessed them) ?


    Hemant K Chitale
  • 11. Re: file 2 needs media recovery data file 2 undotbs01.dbf
    _hifni Newbie
    Currently Being Moderated
    If you used Export-Import and we assume that database shutdowns were executed by the oracle service
    I presume not by Oracle service, I just hit the restart button of the computer, so assume its shut immediate;

    why is one of the online redo log files corrupt ? That is what you have to investigate.
    I cant figure out why, but we are investigating as much as possible with our little knowledge in oracle

    When did the corruption occur ?
    on 5th Feb. 2013 at 7.31am


    Could it have spread to other datafiles as well (undetected yet because you haven't accessed them) ?
    We were running out space issue, thats only problem I could think of.

    I think its better If I go to square one by creating a new database, by deleting the troubled one.
  • 12. Re: file 2 needs media recovery data file 2 undotbs01.dbf
    EdStevens Guru
    Currently Being Moderated
    _hifni wrote:
    If you used Export-Import and we assume that database shutdowns were executed by the oracle service
    I presume not by Oracle service, I just hit the restart button of the computer, so assume its shut immediate;
    No, if you hit the restart button on the computer, none of the processes -- oracle or otherwise -- had an opportunity to do a clean shutdown. Oracle most definitely did NOT+ do a shut immediate. It didn't even "do" a shut abort. It simply "had the rug pulled out from under it." I'd say that in itself is the most likely cause of corruption. Most of the time oracle can perform a crash recovery upon restart following such an incident, but it's the nature of the beast that there would be no guarantees. Crash recover depends on haveing good online redo log files, and your initial posting indicated a corrupt redo log. Have you multiplexed your redo logs (multiple members in each redo log group0)?
    >
    why is one of the online redo log files corrupt ? That is what you have to investigate.
    I cant figure out why, but we are investigating as much as possible with our little knowledge in oracle

    When did the corruption occur ?
    on 5th Feb. 2013 at 7.31am


    Could it have spread to other datafiles as well (undetected yet because you haven't accessed them) ?
    We were running out space issue, thats only problem I could think of.

    I think its better If I go to square one by creating a new database, by deleting the troubled one.
    Edited by: EdStevens on Feb 8, 2013 7:04 AM
  • 13. Re: file 2 needs media recovery data file 2 undotbs01.dbf
    _hifni Newbie
    Currently Being Moderated
    Thanks guys for all of your valuable help. I used Database Configuration Assistant to recreate the Database and now its up and running.

    I heard that there is a way you can recover the database without RMAN, by copying Control Files and *.DBF files and other files. Can someone enlighten me on that or guide me towards the documentation of it.

    Thanks & Regards,

    Hifni
  • 14. Re: file 2 needs media recovery data file 2 undotbs01.dbf
    sb92075 Guru
    Currently Being Moderated
    _hifni wrote:
    Thanks guys for all of your valuable help. I used Database Configuration Assistant to recreate the Database and now its up and running.

    I heard that there is a way you can recover the database without RMAN, by copying Control Files and *.DBF files and other files. Can someone enlighten me on that or guide me towards the documentation of it.
    cleanly shutdown the DB & use OS file copy command to copy all the requisite files to another directory.
1 2 Previous Next

Legend

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