2 Replies Latest reply: Jan 4, 2013 8:37 AM by ShishirTekadeR RSS

    Database cannot be opened

    982938
      Hi,

      Our SAP system is down as undo tablespace PSAPUNDO got corrupted and needed recovery.
      I have created a new undo table PSAPUNDO2 with the following commands:
      SQL >create undo tablespace PSAPUNDO2 datafile 'D:\Oracle\SOL\sapdata1
      \undo_2\UNDO.DATA2' size 700
      and altered the system.
      SQL>alter system set undo_tablespace=PSAPUNDO2 scope=spfile;
      I have not dropped the pls tablespace PSAPUNDO.
      Now as I try to restart oracle I cannot restart it normally and getting the error:
      ora-01092: oracle instance terminated. disconnection forced

      So I have given the command:
      shutdown immediate
      > startup mount
      > alter database open
      and got the same error:
      ora-01092: oracle instance terminated. disconnection forced

      Last few lines of the alert log is as follows:

      Thu Jan 03 21:29:51 2013
      Errors in file d:\oracle\sol\saptrace\background\sol_lgwr_8340.trc:
      ORA-30012: undo tablespace '' does not exist or of wrong type

      Thu Jan 03 21:29:51 2013
      Errors in file d:\oracle\sol\saptrace\background\sol_dbw0_10200.trc:
      ORA-30012: undo tablespace '' does not exist or of wrong type

      Thu Jan 03 21:29:51 2013
      Errors in file d:\oracle\sol\saptrace\background\sol_mman_5852.trc:
      ORA-30012: undo tablespace '' does not exist or of wrong type

      Thu Jan 03 21:29:51 2013
      Errors in file d:\oracle\sol\saptrace\background\sol_psp0_8576.trc:
      ORA-30012: undo tablespace '' does not exist or of wrong type

      Thu Jan 03 21:29:52 2013
      Instance terminated by USER, pid = 9792
      ORA-1092 signalled during: ALTER DATABASE OPEN...

      Edited by: 979935 on Jan 4, 2013 6:24 AM
        • 1. Re: Database cannot be opened
          mseberg
          Hello;

          There are several examples available :

          http://mohamedazar.com/2011/01/03/ora-30012-undo-tablespace-undotbs02-does-not-exist-or-of-wrong-type/

          http://oradbastuff.blogspot.com/2010/11/ora-30012.html

          http://rsnetsolution.blogspot.com/2009/11/fix-ora-30012-undo-tablespace-undotbs1.html

          Best Regards

          mseberg
          • 2. Re: Database cannot be opened
            ShishirTekadeR
            Enter user-name: / as sysdba
            Connected to an idle instance.

            SQL> startup mount

            SQL> select * from v$tablespace;

            TS# NAME INC BIG FLA ENC
            ---------- ------------------------------ --- --- --- ---
            0 SYSTEM YES NO YES
            1 SYSAUX YES NO YES
            20 ADDME YES NO YES
            4 USERS YES NO YES
            3 TEMP NO NO YES
            6 EXAMPLE YES NO YES
            9 RCRMAN YES NO YES
            10 USERS_TBS YES NO YES
            5 UNDOTBS2 YES NO YES
            19 TESTTBS YES NO YES
            14 TESTTB YES NO YES

            TS# NAME INC BIG FLA ENC
            ---------- ------------------------------ --- --- --- ---
            15 TESTDB YES NO YES

            12 rows selected.

            SQL> create pfile='d:\backupnew\initazardb.ora' from spfile;

            File created.

            SQL> shutdown immediate

            SQL> startup pfile='d:\backupnew\initazardb.ora';
            Database opened.


            SQL> create spfile from pfile='d:\backupnew\initazardb.ora';
            File created.

            SQL> shutdown immediate

            SQL> startup
            Database opened.

            SQL> show parameter undo;

            NAME TYPE VALUE
            ------------------------------------ ----------- ------------------------------
            undo_management string AUTO
            undo_retention integer 900
            undo_tablespace string UNDOTBS2
            SQL>

            OR


            Do the following steps to open the database:

            1. set undo_management=MANUAL in your init file.

            2. connect "/ as sysdba" and startup the database using this init file (if not it will use the SPFILE by default).
            eg.
            SQL> startup pfile=

            3. drop the tablespace if you wrongly created one without specifying "undo"

            4. create a new undo tablespace:

            create undo tablespace undotbs datafile '/DB1/undotbs01.dbf' size 1M reuse;

            5. shutdown the database

            6. change undo_management=AUTO in your init file and set undo_tablespace=
            UNDOTBS (or whatever name you specified during the undo tablespace creation)

            7. connect "/ as sysdba" and startup the database using this init file (as in step 2).

            Now the database will open using the new undo tablespace and it would have created new undo segments in this tablespace.

            *****************************************
            Best Regards,
            Shishir Tekade.
            My Blog: http://shishirtekade.blogspot.com