-
Undo Management
I've not set the parameter UNDO_MANAGEMENT in init.ora file,so it takes the default value of MANUAL,
SQL> show parameter undo_management;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
When i queries to know the status of undo segments, it shows all are in OFFLINE,
SQL> select segment_name,tablespace_name,status from dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ ----------------
SYSTEM SYSTEM ONLINE
_SYSSMU1$ UNDOTBS1 OFFLINE
_SYSSMU2$ UNDOTBS1 OFFLINE
_SYSSMU3$ UNDOTBS1 OFFLINE
_SYSSMU4$ UNDOTBS1 OFFLINE
_SYSSMU5$ UNDOTBS1 OFFLINE
_SYSSMU6$ UNDOTBS1 OFFLINE
_SYSSMU7$ UNDOTBS1 OFFLINE
_SYSSMU8$ UNDOTBS1 OFFLINE
_SYSSMU9$ UNDOTBS1 OFFLINE
_SYSSMU10$ UNDOTBS1 OFFLINE
Then i tried,to set UNDO_TABLESPACE,so that all undo segments can be brought ONLINE,but it shows the following the error,
SQL> alter system set undo_tablespace=UNDOTBS1
2 /
alter system set undo_tablespace=UNDOTBS1
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-30014: operation only supported in Automatic Undo Management mode
Can anyone explain me,
1) what could be the reason for all the segments to be in OFFLINE,eventhough the undo tablespace is in ONLINE?
2) What i've to do to make all undo segments ONLINE?
3) Since UNDO_MANAGEMENT parameter is static,how can i change its value dynamically?
-
Hi.
1) They are offline because you've not started automatic undo management!
2) Start automatic undo management and specify the correct undo tablespace.
3) It's a static parameter. It can't be changed dynamically. If it could, it would be called a dynamic parameter!
Set the parameter in the init.ora file or the spfile and restart the database, like:
Code:
CREATE UNDO TABLESPACE UNDOTBS1
DATAFILE '/my/path/to/file/undo0101.dbf'
SIZE 100M REUSE AUTOEXTEND ON;
alter system set undo_management=AUTO scope=SPFILE;
alter system set undo_tablespace=UNDOTBS1 scope=SPFILE;
alter system set undo_retention=900 scope=SPFILE;
shutdown immediate;
startup;
Read this:
http://www.oracle-base.com/articles/...Management.php
Cheers
Tim...
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|