This discussion is archived
1 2 3 4 Previous Next 46 Replies Latest reply: Mar 29, 2013 9:29 AM by Aman.... Go to original post RSS
  • 15. Re: Cannot open database without UNDO tablespace.
    karthiksingh_dba Explorer
    Currently Being Moderated
    Hi Steve,
    Hi,
    <snip>
    What is this? I'm not getting it? snip? I will correct myself if something wrong.
    Did you note the disclaimer "at your own risk "?
    Yes. But I have to learn things since I'm a beginner.
    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.
    Yes, it is not critical. But i have make the DB available to the users. Please help me

    Karthik
  • 16. Re: Cannot open database without UNDO tablespace.
    Aman.... Oracle ACE
    Currently Being Moderated
    I couldn't find the mentioned doc too Kartik.

    Aman....
  • 17. Re: Cannot open database without UNDO tablespace.
    karthiksingh_dba Explorer
    Currently Being Moderated
    Hi John,
    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?
    To make the change effective I did it. I have to learn a lot from Seniors, Please suggest me what should I do now?
    I'm now in a confused state.

    Karthik...
  • 18. Re: Cannot open database without UNDO tablespace.
    karthiksingh_dba Explorer
    Currently Being Moderated
    Hi Aman,
    I couldn't find the mentioned doc too Kartik.
    This is what i get when i search in ALL SOURCES Link.
         
    oracle recover by modify SCN?[Community Discussion ID 158632]
    with Automatic Undo Management (Doc ID 283945.1) Also check the warning mentioned from the

    Karthik..
  • 19. Re: Cannot open database without UNDO tablespace.
    Girish Sharma Guru
    Currently Being Moderated
    What happens if you execute

    SQL> recover datafile 2;

    SQL> alter database datafile 2 online;

    Why I am saying 2, because your error message says :

    ORA-01110: data file *2*: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF'

    Let us know the result of above commands.

    Regards
    Girish Sharma
  • 20. Re: Cannot open database without UNDO tablespace.
    karthiksingh_dba Explorer
    Currently Being Moderated
    Hi Giri,

    SQL> recover datafile 2;
    ORA-00283: recovery session canceled due to errors
    ORA-01610: recovery using the BACKUP CONTROLFILE option must be done

    Karthik...
  • 21. Re: Cannot open database without UNDO tablespace.
    jgarry Guru
    Currently Being Moderated
    Girish Sharma wrote:
    What happens if you execute

    SQL> recover datafile 2;

    SQL> alter database datafile 2 online;

    Why I am saying 2, because your error message says :

    ORA-01110: data file *2*: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF'

    Let us know the result of above commands.

    Regards
    Girish Sharma
    Won't work. He restored datafile 2 from a different database. He needed to restore it from the same database. Apparently he didn't backup his test database before making the datafile 0 long, in order to do such a restore. I wonder how exactly the datafile became 0 long?
  • 22. Re: Cannot open database without UNDO tablespace.
    JohnWatson Guru
    Currently Being Moderated
    karthiksingh_dba wrote:
    Hi John,
    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?
    To make the change effective I did it. I have to learn a lot from Seniors, Please suggest me what should I do now?
    I'm now in a confused state.

    Karthik...
    I'll try again. When you ran
    create pfile from spfile;
    you over-wrote your carefully edited pfile. That is why it doesn't work. So start again, and do NOT run that command.
  • 23. Re: Cannot open database without UNDO tablespace.
    karthiksingh_dba Explorer
    Currently Being Moderated
    Hi qarry
    Won't work. He restored datafile 2 from a different database. He needed to restore it from the same database.
    Also I have been trying from the same test database which is 0bytes. Previously UNDO TBS in Test is around 40MB.
    Apparently he didn't backup his test database before making the datafile 0 long, in order to do such a restore. I wonder how exactly the datafile became 0 long?
    This has happened due to OS file system check in Windows XP and Unfortunately one of the Developer given Yes when it asks for a delete of files/System Cleaning.

    I have the UNDO TBS now only 0bytes.

    Regards,
    Karthik
  • 24. Re: Cannot open database without UNDO tablespace.
    karthiksingh_dba Explorer
    Currently Being Moderated
    Hi John,
    you over-wrote your carefully edited pfile. That is why it doesn't work. So start again, and do NOT run that command.
    Your are right now it worked to view the corrupted parameters as follows

    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;

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    allowresetlogs_corruption boolean TRUE
    corruptedrollback_segments string SYSSMU1$, SYSSMU2$, _SYSSMU3
    $, SYSSMU4$, SYSSMU5$, _SYSS
    MU6$, SYSSMU7$, SYSSMU8$, _S
    YSSMU9$, SYSSMU10$, SYSSMU11
    $, SYSSMU12$, SYSSMU13$, _SY
    SSMU14$, _SYSSMU15$

    Karthik...
  • 25. Re: Cannot open database without UNDO tablespace.
    jgarry Guru
    Currently Being Moderated
    karthiksingh_dba wrote:
    Hi qarry
    Apparently he didn't backup his test database before making the datafile 0 long, in order to do such a restore. I wonder how exactly the datafile became 0 long?
    This has happened due to OS file system check in Windows XP and Unfortunately one of the Developer given Yes when it asks for a delete of files/System Cleaning.
    Was the db running at that time?
  • 26. Re: Cannot open database without UNDO tablespace.
    karthiksingh_dba Explorer
    Currently Being Moderated
    Hi,

    Can anyone help me after this, since I was trying in a luck fortunately DB now in OPEN state. My tries as follows,

    SQL> alter database datafile 2 online;
    alter database datafile 2 online
    *
    ERROR at line 1:
    ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
    ORA-01110: data file 2: 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF'


    SQL> select open_mode from v$database;

    OPEN_MODE
    ----------
    MOUNTED

    SQL> $rman target /

    Recovery Manager: Release 10.2.0.1.0 - Production on Tue Mar 19 23:42:51 2013

    Copyright (c) 1982, 2005, Oracle. All rights reserved.

    connected to target database: ORCL (DBID=1332830105, not open)

    RMAN> list incarnation;

    using target database control file instead of recovery catalog

    List of Database Incarnations
    DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time
    ------- ------- -------- ---------------- --- ---------- ----------
    1 1 ORCL 1332830105 PARENT 1 30-AUG-05
    2 2 ORCL 1332830105 CURRENT 534907 18-JAN-13

    RMAN> alter database open resetlogs;

    database opened

    SQL> select status from v$instance;

    STATUS
    ------------
    OPEN

    Kindly request to help me to fix this by proceeding after this step. Please suggest that I should drop the tablespace and recreate it? or anything else?

    Regards,
    Karthik
  • 27. Re: Cannot open database without UNDO tablespace.
    EdStevens Guru
    Currently Being Moderated
    karthiksingh_dba wrote:
    Hi Steve,
    Hi,
    <snip>
    What is this? I'm not getting it? snip? I will correct myself if something wrong.
    That's a common way of indicating I've deleted a bunch of stuff that is not important to my comments. It doesn't mean there is anything wrong with it. It just means it had no bearing on what I was saying.

    Did you note the disclaimer "at your own risk "?
    Yes. But I have to learn things since I'm a beginner.
    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.
    Yes, it is not critical. But i have make the DB available to the users.
    If you "have" to make that db available to users, I'll bet those users would disagree with your assessment that it is "not critical"

    Please help me
    Karthik
  • 28. Re: Cannot open database without UNDO tablespace.
    karthiksingh_dba Explorer
    Currently Being Moderated
    It happens during the Startup of Operating SYSTEM.
  • 29. Re: Cannot open database without UNDO tablespace.
    karthiksingh_dba Explorer
    Currently Being Moderated
    As I said in my above post Now the Database is in OPEN status and I can see only 7 tables available in schema test. Please kindly suggest me shall i proceed with re-creating UNDOTBS or How do I get back all the tables that lost.

    Regards,
    Karthik

Legend

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