This discussion is archived
1 2 3 4 Previous Next 46 Replies Latest reply: Mar 29, 2013 9:29 AM by Aman.... RSS

Cannot open database without UNDO tablespace.

karthiksingh_dba Explorer
Currently Being Moderated
Dear Legends,

I'm not able to recover Oracle DB 10gR2 in windows XP 32bit without UNDO Table space. Since my UNDO Tablespace has been corrupted and went to 0bytes, it happens due to OS Filesystem check.

Tries so for,
1. Deleted the 0bytes UNDO TBS and tried to restore UNDO TBS from Production to Test - failed, following are the error
sql> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF'
Process ID:0
Session ID: 159 Serial number: 3

Tried deleting the UNDO TBS by offfline drop and also manual delete. I used various references as follows
1. ORA-01092: ORACLE instance terminated. Disconnection forced
2. CAN I RECOVER DELETED DATAFILE AND  ITS TABLESPACE USING FLASHBACK DATABASE
3. Cannot recover drop/re-initilialise undo tablespace: cannot open database
4. http://practical-tech.blogspot.in/2011/07/undo-tablespace-recovery-in-case-of-no.html
5. http://www.iselfschooling.com/mc4articles2/undotbs-recovery.htm
6. http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5669213349582
7. http://colbran.co.za/wordpress/open-a-database-without-the-undo-tablespace/

like these I have tried using metalink also. All resources say that I have to open the Database, where in my case it is not possible.
Waiting for a solution, Any kind of help would be appreciated much.
Thanks in advance
Regards,
Karthik
  • 1. Re: Cannot open database without UNDO tablespace.
    moreajays Pro
    Currently Being Moderated
    Karthik,

    You may try below.. as a last option at your own risk
    1) Edit your *init<sid>.ora file to change undo_management and add two parameters.
    
    + Change UNDO_MANAGEMENT=AUTO to
    
    UNDO_MANAGEMENT=MANUAL
    
    + Remove or comment out UNDO_TABLESPACE and UNDO_RETENTION.
    
    + Add
    
    _ALLOW_RESETLOGS_CORRUPTION = TRUE
    _CORRUPTED_ROLLBACK_SEGMENTS =(comma separated list of Automatic Undo segments)
    
    Example: 
    
    _CORRUPTED_ROLLBACK_SEGMENTS = (_SYSSMU1$, _SYSSMU2$, _SYSSMU3$, _SYSSMU4$, _SYSSMU5$, _SYSSMU6$, _SYSSMU7$, _SYSSMU8$, _SYSSMU9$, _SYSSMU10$)
    
    Note, sometimes the alert log will tell you what Automatic Undo segments are in use. Search the alert log for SYSS. If the alert log does not contain 
    that information then use _SYSSMU1$ through _SYSSMU10$ as shown in the example above.
    
    In UNIX you can issue this command to get the undo segment names:
    
    $ strings system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u
    
    From the output of the strings command above, add a $ to end of each _SYSSMU undo segment name.
    
    + If you only have a spfile available, you can from the closed, nomount or the mount stage create an init<sid>.ora file as follows:
    
    SQL> CREATE PFILE FROM SPFILE;
    
    Do NOT edit the SPFILE.
    
    2) Invoke SQL*Plus, startup mount, check that correct init<sid>.ora was used and 
    all datafiles are in the status of online or system.
    
    $ sqlplus "/as sysdba"
    
    SQL> startup mount pfile = (full path / file name to init<sid>.ora)
    
    Confirm that the hidden parameters from step 3 were used:
    
    SQL> show parameters corrupt
    
    You should see both hidden parameters listed. If not, the wrong init<sid>.ora may have been modified. Do not continue until "show parameters corrupt" shows both hidden parameters.
    
    SQL> show parameters undo
    
    You should see undo management set to manual. If not, the wrong init<sid>.ora may have been modified. Do not continue until "show parameters undo" shows undo management as manual.
    
    Check that all files you want to open with are listed as ONLINE or as SYSTEM. 
    
    SQL> select name, file#, status from v$datafile where status not in ('SYSTEM', 'ONLINE');
    
    If any rows are returned from the query above, bring the file(s) online with: 
    
    SQL> ALTER DATABASE DATAFILE file# ONLINE; 
    
    and repeat until there are no files in an OFFLINE status.
    
    
    3) Perform a fake incomplete recovery then open the database with resetlogs.
    
    SQL> recover database until cancel;
    
    or
    
    SQL> recover database using backup controlfile until cancel;
    
    WHEN PROMPTED FOR AN ARCHIVELOG FILE TYPE cancel THEN PRESS ENTER.
    
    SQL> ALTER DATABASE OPEN RESETLOGS;
    Thanks,
    Ajay More
    http://www.moreajays.com
  • 2. Re: Cannot open database without UNDO tablespace.
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    I don't know why, but I feel this is coming from a metalink note... at least it is easy to find over the Net that's not coming from you.
    Better answer in that case would be : "please contact your Oracle support".

    Nicolas.
  • 3. Re: Cannot open database without UNDO tablespace.
    Mark D Powell Guru
    Currently Being Moderated
    Karthik, "Deleted the 0 bytes UNDO TBS and tried to restore UNDO TBS from Production to Test " It is not supported to copy individual Oracle database data files between databases like this. Each database file header contains information as to which database the file belongs to. You can restore an entire database to another server or instance but just moving one file would require using transportable tablespaces, or taking another approach to just move the data like export/import.
    - -
    Undo tablespaces can be re-created however if you lost the single active undo tablespace while the database was in use then both physical and logical corruption of the database is possible after following the instructions for switching to a new undo tablespace since required undo operations will not be possible. You will need to check out the recovered database very carefully to be sure you are not in a situation where you need to extract your data and reload it into a new/re-created database.
    - -
    HTH -- Mark D Powell --
  • 4. Re: Cannot open database without UNDO tablespace.
    karthiksingh_dba Explorer
    Currently Being Moderated
    Hi,

    My initorcl.ora(C:\oracle\product\10.2.0\db_1\database)

    *.audit_file_dest='C:\oracle\product\10.2.0/admin/orcl/adump'
    *.background_dump_dest='C:\oracle\product\10.2.0/admin/orcl/bdump'
    *.compatible='10.2.0.1.0'
    *.control_files='C:\oracle\product\10.2.0\oradata\orcl\control01.ctl','C:\oracle\product\10.2.0\oradata\orcl\control02.ctl','C:\oracle\product\10.2.0\oradata\orcl\control03.ctl'
    *.core_dump_dest='C:\oracle\product\10.2.0/admin/orcl/cdump'
    *.db_block_size=8192
    *.db_domain=''
    *.db_file_multiblock_read_count=16
    *.db_name='orcl'
    *.db_recovery_file_dest='C:\oracle\product\10.2.0/flash_recovery_area'
    *.db_recovery_file_dest_size=2147483648
    *.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
    *.job_queue_processes=10
    *.open_cursors=300
    *.pga_aggregate_target=202375168
    *.processes=150
    *.remote_login_passwordfile='EXCLUSIVE'
    *.sga_target=609222656
    *.undo_management='MANUAL'
    #*.undo_tablespace='UNDOTBS2'
    *.user_dump_dest='C:\oracle\product\10.2.0/admin/orcl/udump'
    *._ALLOW_RESETLOGS_CORRUPTION = TRUE
    *._CORRUPTED_ROLLBACK_SEGMENTS =(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$,_SYSSMU11$,_SYSSMU12$,_SYSSMU13$,_SYSSMU14$,_SYSSMU15$)

    SQLPLUS status
    Connected to an idle instance.

    SQL> create pfile from spfile;

    File created.

    SQL> startup mount pfile=C:\oracle\product\10.2.0\db_1\database\initorcl.ora;
    ORACLE instance started.

    Total System Global Area 612368384 bytes
    Fixed Size 1250428 bytes
    Variable Size 167775108 bytes
    Database Buffers 436207616 bytes
    Redo Buffers 7135232 bytes
    Database mounted.
    SQL> show parameters corrupt;
    SQL>

    nothing is shown

    SQL> show parameters undo;

    NAME TYPE VALUE
    ------------------------------------ ----------- ------
    undo_management string MANUAL
    undo_retention integer 900
    undo_tablespace string

    SQL> select name, file#, status from v$datafile where status not in ('SYSTEM', 'ONLINE');

    NAME
    --------------------------------------------------------------------------------

    FILE# STATUS
    ---------- -------
    C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
    2 RECOVER


    Kindly suggest me what should I do now? To proceed with the next steps as suggested?

    Regards,
    Karthik
  • 5. Re: Cannot open database without UNDO tablespace.
    Mark D Powell Guru
    Currently Being Moderated
    You probably want to drop the old, non-existent undo tablespace which shows as needing recovery.

    You want to check your database for corruption since displaying parameters related to corruption would not identify corruption. Because Oracle itself will not recognize corruption until the rdbms attempts to access the corrupted area you want to access everything. You might want to run a full export and see if all tables and their related metadata can be accessed. You could also run an rman backup with validate. Then there is the database validation utility, dbv, that you could run on the database data files.

    For rman validate see the Backup and Recovery manuals and for dbv see the Utility manual.

    The views that show corruption are
    v$backup_corruption
    v$database_block_corruption

    Which view you use depends on how the corruption has been located.

    HTH -- Mark D Powell --
  • 6. Re: Cannot open database without UNDO tablespace.
    karthiksingh_dba Explorer
    Currently Being Moderated
    Hi Mark,
    Karthik, "Deleted the 0 bytes UNDO TBS and tried to restore UNDO TBS from Production to Test " It is not supported to copy individual Oracle database data files between databases like this. Each database file header contains information as to which database the file belongs to. You can restore an entire database to another server or instance but just moving one file would require using transportable tablespaces, or taking another approach to just move the data like export/import.
    Yes, you are right. I even tried to copy the UNDO TBS from Production to Test and I face the issue that it says DBID is not belongs to this DATABASE.
    Undo tablespaces can be re-created however if you lost the single active undo tablespace while the database was in use then both physical and logical corruption of the database is possible after following the instructions for switching to a new undo tablespace since required undo operations will not be possible. You will need to check out the recovered database very carefully to be sure you are not in a situation where you need to extract your data and reload it into a new/re-created database.
    Here I'm not getting you. You mean to install a fresh database and perform a restore right?

    Thanks,
    Karthik
  • 7. Re: Cannot open database without UNDO tablespace.
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    It's just a stupid recommendation that you are going to follow. Please read Mark's post. Or, since you have access to metalink (My Oracle Support), contact your Oracle support.

    Nicolas.
  • 8. Re: Cannot open database without UNDO tablespace.
    Nicolas.Gasparotto Oracle ACE
    Currently Being Moderated
    moreajays wrote:
    ...
    Happy now to see the OP's actions ?

    Nicolas.
  • 9. Re: Cannot open database without UNDO tablespace.
    EdStevens Guru
    Currently Being Moderated
    karthiksingh_dba wrote:
    Hi,
    <snip>
    Kindly suggest me what should I do now? To proceed with the next steps as suggested?
    Did you note the disclaimer "at your own risk "?

    I hope this database is not critical. This is not the kind of thing you need to be doing based on the comments of some stranger on the web. You really need to be working an SR with Oracle Support.

    Regards,
    Karthik
  • 10. Re: Cannot open database without UNDO tablespace.
    Aman.... Oracle ACE
    Currently Being Moderated
    A quick Google search gave this
    http://blog.csdn.net/liaoyuanzi/article/details/7767393

    It also mentions a note id but I can't seem to find it on MOS.

    Aman....
  • 11. Re: Cannot open database without UNDO tablespace.
    JohnWatson Guru
    Currently Being Moderated
    karthiksingh_dba wrote:
    Hi,

    My initorcl.ora(C:\oracle\product\10.2.0\db_1\database)
    SQLPLUS status
    Connected to an idle instance.

    SQL> create pfile from spfile;

    File created.
    At this point, you overwrote your edited pfile with a new one, from the spfile. That is why you do not see those CORRUPT parameters. Why did you do it?
  • 12. Re: Cannot open database without UNDO tablespace.
    Aman.... Oracle ACE
    Currently Being Moderated
    Ajay, I believe it's mentioned to you before too to cite the source of the information if it's not from you and also to mention the ramifications of the actions like this on a db. These parameters, without being involving the support is just like running towards the wall blindfolded!

    Aman....
  • 13. Re: Cannot open database without UNDO tablespace.
    karthiksingh_dba Explorer
    Currently Being Moderated
    Hi Aman,
    A quick Google search gave this
    http://blog.csdn.net/liaoyuanzi/article/details/7767393
    I think it is the doc in ALL SOURCES which took to a community id 158632. Is this the right one?

    Karthik..
  • 14. Re: Cannot open database without UNDO tablespace.
    Mark D Powell Guru
    Currently Being Moderated
    No, I am telling you that if you followed the posted instructions to re-create the undo tablespace that your database can suffer corruption after following this procedure.

    First off your post talked about trying to copy the file to test but if this was a production database the best course of action upon losing the undo tablespace data file (or files or one of the files) would be providing the database is in archive log mode to run rman restore and recovery to restore and then forward recover the one missing data file up to the point of loss. This would provide a valid consistent database.

    Being that you chose to ignore the contents of the undo segments so that no failed or incomplete transactions could be backed out that means logical and physical corruption can exist within the database. I listed ways for you to determine if corruption exists. It may not, but if the database was in heavy use when the undo tablespace file got wiped then corruption is very possible.

    Why what I say above is true is explained in the Oracle Concepts manual.

    If this was a test system then you may not be in archive log mode and might just use exports instead of rman backups. Your options depend on how you back up your test database.

    HTH -- Mark D Powell --
1 2 3 4 Previous Next

Legend

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