1 2 Previous Next 18 Replies Latest reply: Oct 21, 2011 2:41 AM by 890512 RSS

    ORA-01092: ORACLE instance terminated. Disconnection forced

    623797
      Please can any one help me solve this problem? I have already checked the alert log file but there seem no luck for repairs
      This problem came as a result of power cut.

      Below is the result for connecting:

      SQL> conn /as sysdba
      Connected to an idle instance.
      SQL> startup
      ORACLE instance started.

      Total System Global Area 188743680 bytes
      Fixed Size 778036 bytes
      Variable Size 162537676 bytes
      Database Buffers 25165824 bytes
      Redo Buffers 262144 bytes
      Database mounted.
      ORA-01092: ORACLE instance terminated. Disconnection forced
        • 1. Re: ORA-01092: ORACLE instance terminated. Disconnection forced
          zaffer khan, cisa
          user620794 wrote:
          Please can any one help me solve this problem? I have already checked the alert log file but there seem no luck for repairs
          This problem came as a result of power cut.

          Below is the result for connecting:

          SQL> conn /as sysdba
          Connected to an idle instance.
          SQL> startup
          ORACLE instance started.

          Total System Global Area 188743680 bytes
          Fixed Size 778036 bytes
          Variable Size 162537676 bytes
          Database Buffers 25165824 bytes
          Redo Buffers 262144 bytes
          Database mounted.
          ORA-01092: ORACLE instance terminated. Disconnection forced
          Please could you post the error details from the Alert log and other trace files.
          • 2. Re: ORA-01092: ORACLE instance terminated. Disconnection forced
            Mario Alcaide
            Hi,

            Did you recently upgrade your database? If it's so, then you must follow those steps:

            cd $ORACLE_HOME/rdbms/admin
            sqlplus "/as sysdba"
            SQL>startup upgrade;
            SQL>@catupgrd.sql
            SQL>shutdown immediate;
            SQL>startup;
            SQL>@utlrp.sql

            That should solve your problem.

            Regards,

            Mario Alcaide
            • 3. Re: ORA-01092: ORACLE instance terminated. Disconnection forced
              Chinar
              Do not need upgrade database; first post there last 30 lines of alert.log
              then try
              sqlplus "/as sysdba"
              startup mount;
              alter database recover;
              alter database open;
              • 4. Re: ORA-01092: ORACLE instance terminated. Disconnection forced
                623797
                thanks for the quick responds.

                I had the same issue.

                but please how can you repair the corrupt data block with the rdbms repair utility?

                SQL> startup upgrade;
                ORACLE instance started.

                Total System Global Area 188743680 bytes
                Fixed Size 778036 bytes
                Variable Size 162537676 bytes
                Database Buffers 25165824 bytes
                Redo Buffers 262144 bytes
                Database mounted.
                ORA-01092: ORACLE instance terminated. Disconnection forced

                bets regards
                • 5. Re: ORA-01092: ORACLE instance terminated. Disconnection forced
                  Chinar
                  Do not need upgrade database; first post there last 30 lines of alert.log
                  • 6. Re: ORA-01092: ORACLE instance terminated. Disconnection forced
                    635694
                    Please send alert.log file for last 100 lines.
                    • 7. Re: ORA-01092: ORACLE instance terminated. Disconnection forced
                      623797
                      Completed crash recovery at
                      Thread 1: logseq 1742, block 61, scn 0.13607339
                      9 data blocks read, 9 data blocks written, 58 redo blocks read
                      Mon Apr 19 06:46:37 2010
                      Thread 1 advanced to log sequence 1743
                      Maximum redo generation record size = 120832 bytes
                      Maximum redo generation change vector size = 116476 bytes
                      Private_strands 7 at log switch
                      Thread 1 opened at log sequence 1743
                      Current log# 3 seq# 1743 mem# 0: /u02/app/oradata/PROD/redo03.log
                      Successful open of redo thread 1
                      Mon Apr 19 06:46:38 2010
                      MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
                      Mon Apr 19 06:46:38 2010
                      SMON: enabling cache recovery
                      Mon Apr 19 06:46:43 2010
                      Successfully onlined Undo Tablespace 1.
                      Mon Apr 19 06:46:43 2010
                      SMON: enabling tx recovery
                      Mon Apr 19 06:46:43 2010
                      Database Characterset is WE8ISO8859P1
                      Mon Apr 19 06:46:43 2010
                      Published database character set on system events channel
                      Mon Apr 19 06:46:43 2010
                      All processes have switched to database character set
                      Mon Apr 19 06:46:46 2010
                      Hex dump of (file 2, block 1251) in trace file /u01/app/admin/PROD/udump/prod_ora_3851.trc
                      Corrupt block relative dba: 0x008004e3 (file 2, block 1251)
                      Fractured block found during buffer read
                      Data in bad block:
                      type: 2 format: 2 rdba: 0x008004e3
                      last change scn: 0x0000.00bf7aac seq: 0x4 flg: 0x04
                      spare1: 0x0 spare2: 0x0 spare3: 0x0
                      consistency value in tail: 0x89210201
                      check value in block header: 0xa741
                      computed block checksum: 0xbb4f
                      Reread of rdba: 0x008004e3 (file 2, block 1251) found same corrupted data
                      Mon Apr 19 06:46:46 2010
                      Errors in file /u01/app/admin/PROD/udump/prod_ora_3851.trc:
                      ORA-01578: ORACLE data block corrupted (file # 2, block # 1251)
                      ORA-01110: data file 2: '/u02/app/oradata/PROD/undotbs01.dbf'
                      Error 1578 happened during db open, shutting down database
                      USER: terminating instance due to error 1578
                      Instance terminated by USER, pid = 3851
                      ORA-1092 signalled during: ALTER DATABASE OPEN...
                      • 8. Re: ORA-01092: ORACLE instance terminated. Disconnection forced
                        623797
                        The following is the details of the alert log:

                        Completed crash recovery at
                        Thread 1: logseq 1742, block 61, scn 0.13607339
                        9 data blocks read, 9 data blocks written, 58 redo blocks read
                        Mon Apr 19 06:46:37 2010
                        Thread 1 advanced to log sequence 1743
                        Maximum redo generation record size = 120832 bytes
                        Maximum redo generation change vector size = 116476 bytes
                        Private_strands 7 at log switch
                        Thread 1 opened at log sequence 1743
                        Current log# 3 seq# 1743 mem# 0: /u02/app/oradata/PROD/redo03.log
                        Successful open of redo thread 1
                        Mon Apr 19 06:46:38 2010
                        MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
                        Mon Apr 19 06:46:38 2010
                        SMON: enabling cache recovery
                        Mon Apr 19 06:46:43 2010
                        Successfully onlined Undo Tablespace 1.
                        Mon Apr 19 06:46:43 2010
                        SMON: enabling tx recovery
                        Mon Apr 19 06:46:43 2010
                        Database Characterset is WE8ISO8859P1
                        Mon Apr 19 06:46:43 2010
                        Published database character set on system events channel
                        Mon Apr 19 06:46:43 2010
                        All processes have switched to database character set
                        Mon Apr 19 06:46:46 2010
                        Hex dump of (file 2, block 1251) in trace file /u01/app/admin/PROD/udump/prod_ora_3851.trc
                        Corrupt block relative dba: 0x008004e3 (file 2, block 1251)
                        Fractured block found during buffer read
                        Data in bad block:
                        type: 2 format: 2 rdba: 0x008004e3
                        last change scn: 0x0000.00bf7aac seq: 0x4 flg: 0x04
                        spare1: 0x0 spare2: 0x0 spare3: 0x0
                        consistency value in tail: 0x89210201
                        check value in block header: 0xa741
                        computed block checksum: 0xbb4f
                        Reread of rdba: 0x008004e3 (file 2, block 1251) found same corrupted data
                        Mon Apr 19 06:46:46 2010
                        Errors in file /u01/app/admin/PROD/udump/prod_ora_3851.trc:
                        ORA-01578: ORACLE data block corrupted (file # 2, block # 1251)
                        ORA-01110: data file 2: '/u02/app/oradata/PROD/undotbs01.dbf'
                        Error 1578 happened during db open, shutting down database
                        USER: terminating instance due to error 1578
                        Instance terminated by USER, pid = 3851
                        ORA-1092 signalled during: ALTER DATABASE OPEN...


                        Thanks to you all
                        • 9. Re: ORA-01092: ORACLE instance terminated. Disconnection forced
                          Chinar
                          Well.Your database has corrupted block.
                          ORA-01578: ORACLE data block corrupted (file # 2, block # 1251)
                          ORA-01110: data file 2: '/u02/app/oradata/PROD/undotbs01.dbf';
                          Do you have backup as rman ?
                          • 10. Re: ORA-01092: ORACLE instance terminated. Disconnection forced
                            623797
                            These are the results from the db verify:



                            [oracle@oracleserver oracle]$ dbv file=/u02/app/oradata/PROD/undotbs01.dbf \ blocksize=8192

                            DBVERIFY: Release 10.1.0.2.0 - Production on Mon Apr 19 07:34:24 2010

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

                            DBVERIFY - Verification starting : FILE = /u02/app/oradata/PROD/undotbs01.dbf
                            Page 1251 is influx - most likely media corrupt
                            Corrupt block relative dba: 0x008004e3 (file 2, block 1251)
                            Fractured block found during dbv:
                            Data in bad block:
                            type: 2 format: 2 rdba: 0x008004e3
                            last change scn: 0x0000.00bf7aac seq: 0x4 flg: 0x04
                            spare1: 0x0 spare2: 0x0 spare3: 0x0
                            consistency value in tail: 0x89210201
                            check value in block header: 0xa741
                            computed block checksum: 0xbb4f



                            DBVERIFY - Verification complete

                            Total Pages Examined : 12800
                            Total Pages Processed (Data) : 0
                            Total Pages Failing (Data) : 0
                            Total Pages Processed (Index): 0
                            Total Pages Failing (Index): 0
                            Total Pages Processed (Other): 6358
                            Total Pages Processed (Seg) : 0
                            Total Pages Failing (Seg) : 0
                            Total Pages Empty : 6441
                            Total Pages Marked Corrupt : 1
                            Total Pages Influx : 1
                            • 11. Re: ORA-01092: ORACLE instance terminated. Disconnection forced
                              623797
                              The RMAN cannot be invoked. The last available backup is dated 9months ago so recovery with backup is not an option so I will be grateful if you can suggest a method to repair the datablock without RMAN.
                              Thank you.
                              • 12. Re: ORA-01092: ORACLE instance terminated. Disconnection forced
                                657203
                                Run below rman script:

                                run {
                                allocate channel d1 type disk;
                                backup check logical validate database;
                                release channel d1;
                                }


                                Post the result.

                                Question: Are you ready to go with UNDOCUMENTED parameters which is RISKy one?
                                • 13. Re: ORA-01092: ORACLE instance terminated. Disconnection forced
                                  zaffer khan, cisa
                                  Can you try and give the output of the following:
                                  startup mount;
                                  alter database recover;
                                  alter database open;
                                  You don't have any backups, I hope this is not production...

                                  Regards
                                  Z.K.
                                  • 14. Re: ORA-01092: ORACLE instance terminated. Disconnection forced
                                    Chinar
                                    There are some way starting up database and recreate undo tablespace ,Now i explan one way for you step by step
                                    1.First backup full db(cold backup shutdown db and do it);
                                    2. add pfile ($ORACLE_HOME/admin/<sid>/pfile) following lines and change this as follow


                                    ###########################################
                                    # System Managed Undo and Rollback Segments
                                    ###########################################
                                    corruptedrollback_segments = true
                                    offlinerollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$.,SYSSMU8$,_SYSSMU9$,_SYSSMU10$,_SYSSMU11$,_SYSSMU12$,_SYSSMU13$,_SYSSMU14$,_SYSSMU15$,SYSSMU16$,_SYSSMU17$,_SYSSMU18$,_SYSSMU19$,_SYSSMU20$,_SYSSMU21$,_SYSSMU22$,_SYSSMU23$,_SYSSMU24$,_SYSSMU25$,_SYSSMU26$)


                                    undo_management=manual
                                    undo_tablespace=UNDO_TS

                                    # old values
                                    #undo_management=AUTO
                                    #undo_tablespace=UNDOTBS1

                                    3. delete from OS undo tablespace's datafile

                                    4. Then startup database as :

                                    Microsoft Windows XP [Version 5.1.2600]
                                    (C) Copyright 1985-2001 Microsoft Corp.

                                    C:\Documents and Settings\chinar>set ORACLE_SID=SB

                                    C:\Documents and Settings\chinar>sqlplus "sys/sm as sysdba"

                                    SQL*Plus: Release 10.2.0.2.0 - Production on Mon Apr 19 15:44:44 2010

                                    Copyright (c) 1982, 2005, Oracle. All Rights Reserved.

                                    Connected to an idle instance.

                                    SQL> startup mount pfile=D:\oracle\product\10.2.0\admin\SB\pfile\init.ora.512009
                                    10857
                                    ORACLE instance started.

                                    Total System Global Area 159383552 bytes
                                    Fixed Size 1288292 bytes
                                    Variable Size 62916508 bytes
                                    Database Buffers 88080384 bytes
                                    Redo Buffers 7098368 bytes
                                    Database mounted.
                                    SQL> alter database open;
                                    alter database open
                                    *
                                    ERROR at line 1:
                                    ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
                                    ORA-01110: data file 2: 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SB\UNDOTBS01.DBF'


                                    SQL> alter database datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SB\UNDOTBS01.DBF
                                    ' offline drop;

                                    Database altered.

                                    SQL> shutdown immediate;
                                    ORA-01109: database not open


                                    Database dismounted.
                                    ORACLE instance shut down.
                                    SQL> startup mount pfile=D:\oracle\product\10.2.0\admin\SB\pfile\init.ora.512009
                                    10857
                                    ORACLE instance started.

                                    Total System Global Area 159383552 bytes
                                    Fixed Size 1288292 bytes
                                    Variable Size 62916508 bytes
                                    Database Buffers 88080384 bytes
                                    Redo Buffers 7098368 bytes
                                    Database mounted.
                                    SQL> alter database open;
                                    alter database open
                                    *
                                    ERROR at line 1:
                                    ORA-16038: log 1 sequence# 215 cannot be archived
                                    ORA-19809: limit exceeded for recovery files
                                    ORA-00312: online log 1 thread 1:
                                    'D:\ORACLE\PRODUCT\10.2.0\ORADATA\SB\REDO1.LOG'

                                    SQL> alter system set db_recovery_file_dest_size=500G;

                                    System altered.

                                    SQL> alter database open;

                                    Database altered.

                                    SQL> show parameter undo

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

                                    SQL> create undo tablespace undo_ts datafile 'D:\oracle\product\10.2.0\oradata\
                                    sb\udo_ts.01' size 300M autoextend on next 10M;

                                    Tablespace created.

                                    SQL> shutdown immediate;
                                    Database closed.
                                    Database dismounted.
                                    ORACLE instance shut down.

                                    5. again change pfile such as

                                    ###########################################
                                    # System Managed Undo and Rollback Segments
                                    ###########################################
                                    #_corrupted_rollback_segments = true
                                    #_offline_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$.,SYSSMU8$,_SYSSMU9$,_SYSSMU10$,_SYSSMU11$,_SYSSMU12$,_SYSSMU13$,_SYSSMU14$,_SYSSMU15$,SYSSMU16$,_SYSSMU17$,_SYSSMU18$,_SYSSMU19$,_SYSSMU20$,_SYSSMU21$,_SYSSMU22$,_SYSSMU23$,_SYSSMU24$,_SYSSMU25$,_SYSSMU26$)

                                    undo_management=AUTO
                                    undo_tablespace=UNDO_TS

                                    SQL> startup mount pfile=D:\oracle\product\10.2.0\admin\SB\pfile\init.ora.512009
                                    10857
                                    ORACLE instance started.

                                    Total System Global Area 159383552 bytes
                                    Fixed Size 1288292 bytes
                                    Variable Size 62916508 bytes
                                    Database Buffers 88080384 bytes
                                    Redo Buffers 7098368 bytes
                                    Database mounted.
                                    SQL> alter database open;

                                    Database altered.

                                    6. accordingthis method you can drop old undo tablespace(undotbs) useing undocumented parameter
                                    corruptedrollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$.,SYSSMU8$,_SYSSMU9$,_SYSSMU10$,_SYSSMU11$,_SYSSMU12$,_SYSSMU13$,_SYSSMU14$,_SYSSMU15$,SYSSMU16$,_SYSSMU17$,_SYSSMU18$,_SYSSMU19$,_SYSSMU20$,_SYSSMU21$,_SYSSMU22$,_SYSSMU23$,_SYSSMU24$,_SYSSMU25$,_SYSSMU26$)

                                    7.
                                    SQL> show parameter undo

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

                                    Edited by: dba84 on Apr 19, 2010 4:17 AM
                                    1 2 Previous Next