ORA-01092: ORACLE instance terminated. Disconnection forced

From: chris <lazyboy_2k_at_yahoo.com>
Date: Tue, 15 Jul 2008 12:00:15 -0700 (PDT)
Message-ID: <8bf2dd97-cdc9-4516-ad52-a065251b3c8e@m45g2000hsb.googlegroups.com>

Hi All,

I'm a newbie in oracle & try to change undo_management=AUTO to MANUAL. I know that Oracle recommends AUTO, but I just want to test the procedure to change. Below are the steps that I follow & it's fails to startup DB. By looking at the log file, I know that missing the rollback01 segment is the rootcause, but I don't understand why. I had it created fine.... Could someone please help me to solve it? Any suggestions/hints are appreciated.

TIA,
-Chris

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL> CREATE TABLESPACE "RBS01"
1 LOGGING
2 DATAFILE '/oraclesw/oradata/North/RBS01.dbf' SIZE 10M 3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100K SEGMENT 4 SPACE MANAGEMENT MANUAL; SQL> alter system set undo_management=manual scope=spfile;

System altered.

SQL> startup
ORACLE instance started.

Total System Global Area 188743680 bytes

Fixed Size                  1977368 bytes
Variable Size             125834216 bytes
Database Buffers           58720256 bytes
Redo Buffers                2211840 bytes

Database mounted.
Database opened.

SQL> CREATE ROLLBACK SEGMENT rollback1 TABLESPACE rbs01 1 STORAGE (initial 10K next 10K)

CREATE ROLLBACK SEGMENT rollback1 TABLESPACE rbs01 *
ERROR at line 1:
ORA-01552: cannot use system rollback segment for non-system tablespace 'RBS01'

SQL> CREATE ROLLBACK SEGMENT dummy;

Rollback segment created.

SQL> ALTER SYSTEM SET rollback_segments=dummy scope=spfile;

SQL> show parameter rollback

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
fast_start_parallel_rollback         string      LOW
rollback_segments                    string
transactions_per_rollback_segment    integer     5

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

SQL> startup
ORACLE instance started.

Total System Global Area 188743680 bytes

Fixed Size                  1977368 bytes
Variable Size             125834216 bytes
Database Buffers           58720256 bytes
Redo Buffers                2211840 bytes

Database mounted.
Database opened.

SQL> show parameter rollback

NAME                                 TYPE        VALUE
------------------------------------ -----------
------------------------------
fast_start_parallel_rollback         string      LOW
rollback_segments                    string      DUMMY
transactions_per_rollback_segment    integer     5

SQL> CREATE ROLLBACK SEGMENT "rollback01" TABLESPACE "RBS01" 1 STORAGE (INITIAL 10K NEXT 10K OPTIMAL NULL 2 MINEXTENTS 10
3* MAXEXTENTS UNLIMITED) Rollback segment created.

SQL> ALTER ROLLBACK SEGMENT "rollback01" ONLINE;

Rollback segment altered.

SQL> alter system set rollback_segments=rollback01 scope=spfile;

System altered.

SQL> shutdown immediate

ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 188743680 bytes

Fixed Size                  1977368 bytes
Variable Size             130028520 bytes
Database Buffers           54525952 bytes
Redo Buffers                2211840 bytes

Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced

Looking at the trace log�

Starting up ORACLE RDBMS Version: 10.2.0.1.0. System parameters with non-default values:

  processes                = 150


  __shared_pool_size       = 113246208

  __large_pool_size        = 12582912

  __java_pool_size         = 4194304

  __streams_pool_size      = 0

  sga_target               = 188743680
  control_files            = /oraclesw/oradata/North/control01.ctl, /

oraclesw/oradata/North/control02.ctl, /oraclesw/oradata/North/ control03.ctl

  db_block_size            = 8192


  __db_cache_size          = 54525952

  compatible               = 10.2.0.1.0
  db_file_multiblock_read_count= 16

db_recovery_file_dest = /oraclesw/product/North db_recovery_file_dest_size= 157286400

  rollback_segments        = ROLLBACK01
  undo_management          = MANUAL
  undo_tablespace          = UNDOTBS1
  remote_login_passwordfile= EXCLUSIVE
  db_domain                =
  dispatchers              = (PROTOCOL=TCP) (SERVICE=NorthXDB)
  job_queue_processes      = 10
  background_dump_dest     = /oraclesw/admin/North/bdump
  user_dump_dest           = /oraclesw/admin/North/udump
  core_dump_dest           = /oraclesw/admin/North/cdump
  audit_file_dest          = /oraclesw/admin/North/adump
  db_name                  = North
  open_cursors             = 300
  pga_aggregate_target     = 62914560
PMON started with pid=2, OS id=7415
PSP0 started with pid=3, OS id=7417
MMAN started with pid=4, OS id=7419
DBW0 started with pid=5, OS id=7421
LGWR started with pid=6, OS id=7423
CKPT started with pid=7, OS id=7425
SMON started with pid=8, OS id=7427
RECO started with pid=9, OS id=7429

CJQ0 started with pid=10, OS id=7431
MMON started with pid=11, OS id=7433
Tue Jul 15 11:49:08 2008
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES) (PROTOCOL=TCP))'...
MMNL started with pid=12, OS id=7435
Tue Jul 15 11:49:09 2008
starting up 1 shared server(s) ...
Tue Jul 15 11:49:09 2008
ALTER DATABASE MOUNT
Tue Jul 15 11:49:14 2008
Setting recovery target incarnation to 2 Tue Jul 15 11:49:14 2008
Successful mount of redo thread 1, with mount id 1648878133 Tue Jul 15 11:49:14 2008
Database mounted in Exclusive Mode
Completed: ALTER DATABASE MOUNT
Tue Jul 15 11:49:15 2008
ALTER DATABASE OPEN
Tue Jul 15 11:49:15 2008
Beginning crash recovery of 1 threads
Tue Jul 15 11:49:15 2008
Started redo scan
Tue Jul 15 11:49:15 2008
Completed redo scan
0 redo blocks read, 0 data blocks need recovery Tue Jul 15 11:49:15 2008
Started redo application at
Thread 1: logseq 150, block 3, scn 5428029 Tue Jul 15 11:49:15 2008
Recovery of Online Redo Log: Thread 1 Group 2 Seq 150 Reading mem 0 Mem# 0 errs 0: /oraclesw/oradata/North/redo02.log Tue Jul 15 11:49:15 2008
Completed redo application
Tue Jul 15 11:49:15 2008
Completed crash recovery at
Thread 1: logseq 150, block 3, scn 5448030 0 data blocks read, 0 data blocks written, 0 redo blocks read Tue Jul 15 11:49:16 2008
Thread 1 advanced to log sequence 151
Thread 1 opened at log sequence 151
Current log# 3 seq# 151 mem# 0: /oraclesw/oradata/North/redo03.log Successful open of redo thread 1
Tue Jul 15 11:49:16 2008
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set Tue Jul 15 11:49:16 2008
SMON: enabling cache recovery
Tue Jul 15 11:49:19 2008
Errors in file /oraclesw/admin/North/udump/north_ora_7456.trc: ORA-01534: rollback segment 'ROLLBACK01' doesn't exist Tue Jul 15 11:49:19 2008
Error 1534 happened during db open, shutting down database USER: terminating instance due to error 1534 Tue Jul 15 11:49:19 2008
Errors in file /oraclesw/admin/North/bdump/north_dbw0_7421.trc: ORA-01534: rollback segment '' doesn't exist Instance terminated by USER, pid = 7456
ORA-1092 signalled during: ALTER DATABASE OPEN... Received on Tue Jul 15 2008 - 14:00:15 CDT

This message: [ Message body ]
Next message: Maxim Demenko: "Re: ORA-01092: ORACLE instance terminated. Disconnection forced"
Previous message: amerar_at_iwc.net: "Re: Union question"
Next in thread: Maxim Demenko: "Re: ORA-01092: ORACLE instance terminated. Disconnection forced"
Reply: Maxim Demenko: "Re: ORA-01092: ORACLE instance terminated. Disconnection forced"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message