This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Oct 21, 2011 12:41 AM by 890512 RSS

ORA-01092: ORACLE instance terminated. Disconnection forced

623797 Newbie
Currently Being Moderated
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 Explorer
    Currently Being Moderated
    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
    MarioAlcaide Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Do not need upgrade database; first post there last 30 lines of alert.log
  • 6. Re: ORA-01092: ORACLE instance terminated. Disconnection forced
    635694 Newbie
    Currently Being Moderated
    Please send alert.log file for last 100 lines.
  • 7. Re: ORA-01092: ORACLE instance terminated. Disconnection forced
    623797 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

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