ORA-01092: ORACLE instance terminated. Disconnection forced
chris
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...
Maxim Demenko
> 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
>
At the first look, it appears, your rollback segment is actually created
as "rollback01", so the segment "ROLLBACK01" which is defined in the
parameter file can't be found - because it doesn't exist. You are surely
aware, that taking an lowercase identifier into double quotes preserve
the case, so to avoid surprises, it seems to be good practice to use
only uppercase identifier for database objects...
Best regards
Maxim
chris
Thanks for the tips. got it working......