1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

ORA-01092: ORACLE instance terminated. Disconnection forced

Discussion in 'General' started by member.forums@gmail.com, Sep 17, 2009.

  1. member.forums@gmail.com

    member.forums@gmail.com Active Member

    Messages:
    16
    Likes Received:
    0
    Trophy Points:
    80
    Hi
    i try to create a new database in oracle using the following script

    CREATE DATABASE CQWB
    Maxinstances 1
    Maxloghistory 1
    Maxlogfiles 10
    Maxlogmembers 3
    Maxdatafiles 100
    sysaux datafile '/db/cqwb/sysaux.dbf'
    size 10M
    autoextend on
    next 10M
    maxsize unlimited
    Datafile '/db/cqwb/datafile_01.dbf'
    size 300M reuse autoextend on next 15M maxsize unlimited
    character set WE8MSWIN1252
    national character set AL16UTF16
    logfile group 1('/db/cqwb/redo_01.log') size 75M,
    group 2('/db/cqwb/redo_02.log') size 75M,
    group 3('/db/cqwb/redo_03.log') size 75M
    default temporary tablespace TEMP
    tempfile '/db/cqwb/temp_01.dbf'
    extent management local uniform size 1M
    undo tablespace UNDO_TS datafile '/db/cqwb/datafile_01.dbf'
    size 125M reuse autoextend on next 15M maxsize unlimited;


    now I get the error

    ORA-01092: ORACLE instance terminated. Disconnection forced

    Any help wold be highly appreciated

    Sapan
     
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    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.

    This error is most likely cause by an erroneous parameter in init.ora or an invalid directory.

    Provide the following two details

    1. alert.log file
    2. pfile

    Also make sure that the undo_tablespace name in parameter file and in your CREATE statement are same.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You're trying to use the same datafile name for the UNDO tablespace that you used for the SYSTEM tablespace, so I'm not surprised the database won't run. For the SYSTEM tablespace you use

    Datafile '/db/cqwb/datafile_01.dbf'
    size 300M reuse autoextend on next 15M maxsize unlimited

    and for the UNDO tablepace you use

    undo tablespace UNDO_TS datafile '/db/cqwb/datafile_01.dbf'
    size 125M reuse autoextend on next 15M maxsize unlimited

    The sizes are different but the fully-qualified name is the same for both files and Oracle cannot create two files of the same name in the same location. Since you have no UNDO tablespace the instance dies; you'll need to change the file name, file location or both before this will build a usable database. Why did you not use

    /db/cqwb/system_01.dbf

    for the SYSTEM tablespace and

    /db/cqwb/undo_01.dbf

    for the UNDO tablespace and avoid this naming mess altogether?