11 Replies Latest reply: May 5, 2014 9:53 PM by 1003614 RSS

    system tablespace full

    1003614

      Hi All,

       

      oracle 10g 10.2.0.5

      linux

       

       

      unfortunately my system tablespace got full and db was went down...i have executed startup but am getting below error.

       

      ORA-01092: ORACLE instance terminated. Disconnection forced

       

      kindly let us know how to proceed.

       

      thanks,

      mike.

        • 1. Re: system tablespace full
          Hoek
          ORA-01092: ORACLE instance terminated. Disconnection forced

          Cause: The instance this process was connected to was terminated abnormally, probably via a shutdown abort. This process was forced to disconnect from the instance.

          Action: Examine the alert log for more details. When the instance has been restarted, retry action.


          http://docs.oracle.com/cd/E11882_01/server.112/e17766/e900.htm#ORA-01092

          • 2. Re: system tablespace full
            Anand...

            unfortunately my system tablespace got full and db was went down.

             

            I can't think of anything except of restoring the db from backup and then resizing system tablespace's datafile. You can also open SR with oracle and see if there is any option available.

            • 3. Re: system tablespace full
              Hemant K Chitale

              No, you don't need to restore the database just because the system tablespace is full.

               

              The OP must check his alert log (and trace files if any) for the reasons why the database instance went down and why a startup fails.

               

               

              Hemant K Chitale


              • 4. Re: system tablespace full
                1003614

                trace file details:

                 

                tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x1)

                tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x1)

                Maximum redo generation record size = 156160 bytes

                Maximum redo generation change vector size = 150676 bytes

                tkcrrsarc: (WARN) Failed to find ARCH for message (message:0x10)

                tkcrrpa: (WARN) Failed initial attempt to send ARCH message (message:0x10)

                error 604 detected in background process

                ORA-00604: error occurred at recursive SQL level

                 

                any idea how startup database?

                • 5. Re: system tablespace full
                  HarishDBA

                  Hi,

                   

                  The Error basically says it is disconnected abnormally..

                   

                  This Error may occur for few reasons

                   

                    1 . A shutdown abort issued.

                   

                    2 . Renaming files when Oracle is down or Any OS related issues..

                   

                    3 . Assigning or Bad Permissions to Udump, Bdump,Cdump

                   

                           So need not restore your database... check with this compatibilties...what ever you have given

                  • 6. Re: system tablespace full
                    1003614

                    alert log details:

                     

                    ORA-1653: unable to extend table SYS.UNDO$ by 2 in                 tablespace SYSTEM

                    ORA-1653: unable to extend table SYS.UNDO$ by 2 in                 tablespace SYSTEM

                    Fri May  2 20:23:02 2014

                    Errors in file /opt/oracle/admin/udump/oranv_ora_15962.trc:

                    ORA-00604: error occurred at recursive SQL level 1

                    ORA-01653: unable to extend table SYS.UNDO$ by 2 in tablespace SYSTEM

                    Error 604 happened during db open, shutting down database

                    USER: terminating instance due to error 604

                    Instance terminated by USER, pid = 15962

                    ORA-1092 signalled during: alter database open...

                     

                    please help....

                    • 7. Re: system tablespace full
                      HarishDBA

                      Hi,

                       

                      Error 00604 - basically is for recursive error...

                         check for the logon trigger....on your database...

                       

                      select trigger_name from dba_triggers where triggering_event like '%LOGON%';

                       

                      If so, disable the trigger or modify code so it will work


                      If not wrking checking wit the temp tablespace....

                        try to allocate with local uniform extents size...

                       

                      This error basically come when you create Manual database....

                      • 8. Re: system tablespace full
                        Shivendra Narain Nirala

                        Hi

                         

                        looks like ,server mount point is full where the archive files located and meanwhile the transction were performing leads to system tablespace growth and all the archive log files and system tablespace is on same mount point .That's why this instance got crashed .

                         

                        Solution :-

                         

                        1. First of all take cold backup of archive log and remove from server.

                        2. start your database in mount point and on the another window watch alert log file.

                        3. if everything goes well , then open your database.

                        4. plan for more space and add it to the server OR re-arrange the data files .

                         

                        Regards

                         

                        Shivendra Narain Nirala

                        • 9. Re: system tablespace full
                          KR10822864


                          by looking above errors, hope your system or undo table space got corrupted.

                          please check below.

                          check undo management is auto or manual? check any rollback segments status any corruption...there may chance to corruption...by using below sql...

                          check all possible ways....

                          select segment_name, tablespace_name, status from dba_rollback_segs where tablespace_name=’UNDO′;

                           

                           

                           

                          refer below link for  if any rollback segments corruption...

                          https://community.oracle.com/thread/2593502

                          • 10. Re: system tablespace full
                            Hemant K Chitale

                            >ORA-01653: unable to extend table SYS.UNDO$ by 2 in tablespace SYSTEM

                            In general, Oracle doesn't use an Undo segment in the SYSTEM Tablespace unless

                            a.  There is no available Undo Tablespace

                            b.  A tablespace has been taken offline and Oracle has to maintain undo data for deferred rollbacks.

                             

                            Was there some unusual activity relating to the undo tablespace or any other tablespace before the system tablespace went full ?

                             

                             

                            Hemant K Chitale


                            • 11. Re: system tablespace full
                              1003614

                              thanks all for quick reply....

                              My undo segments got corrupted...unfortunately undo management has changed into manual...that is the reason system table-space got full.

                              my issue got resolved...thanks everyone.