| Undo Management [message #161009] |
Wed, 01 March 2006 07:10  |
gajini
Messages: 262
Registered: January 2006
|
Senior Member |
|
|
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?
|
|
|
|
| Re: Undo Management [message #161036 is a reply to message #161009] |
Wed, 01 March 2006 09:08  |
orausern
Messages: 826
Registered: December 2005
|
Senior Member |
|
|
See docs at:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/undo.htm#654
| Quote: |
1) why offline:
Bringing New Rollback Segments Online
New rollback segments are initially offline. You must issue an ALTER ROLLBACK SEGMENT statement to bring them online and make them available for use by transactions of an instance.
|
2) how to get them online:
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96521/undo.htm#828
| Quote: |
Bringing Rollback Segments Online Manually
You can only bring a rollback segment online if its current status (as shown in the DBA_ROLLBACK_SEGS data dictionary view) is OFFLINE or PARTLY AVAILABLE. To bring an offline rollback segment online, use the ALTER ROLLBACK SEGMENT statement with the ONLINE option.
The following statement brings the rollback segment user_rs_2 online:
ALTER ROLLBACK SEGMENT user_rs_2 ONLINE;
|
3) How to change undo_managment static parameter dynamically?
Not possible.they must be changed statically by bringing the instance down and changing the pfile.
|
|
|
|
| Re: Undo Management [message #161181 is a reply to message #161009] |
Thu, 02 March 2006 07:32 |
alliejane
Messages: 59
Registered: July 2005
Location: Glasgow
|
Member |
|
|
That's fine but it appears that you also have an undo tablespace, what that means is that probably during the creation of the database a specialist tablespace was created to hold rollback (or more usually called undo) segment.
Note. When I refer to undo segments I'm talking about automatic undo management brought in in 9i, rollback segments are the pre 9i way doing things known as manual undo..
When this tablespace was created oracle automatically created 10 "undo" segments for you (with one extra which is used internally)
What you see when you use the script
select segment_name,tablespace_name,status from dba_rollback_segs;
Is actually the automatically created "undo" segments, I can guess this because of their segment name _SYSSMU1$, the leading underscore is a hint but from experience I know these are the default names for them.
The reason they are off line is that Oracle won't attempt to use them until UNDO_MANAGEMENT==AUTO, you can't manually bring them online.
Now my question.
Do you really want to use the Automatic undo feature in 9i (generally recommended) or do you want to use Manual undo, which is when you go back to the pre 9i way of doing things and create the rollback segments yourself.
If you want to use the automatic undo feature you need to change one of the database parameters, to do this you first need to find out if your database is being started with a pfile (init.ora) or a spfile (also a new feature in 9i).
Do this first.
sql > SHOW PARAMETER SPFILE;
1. If the value column is empty then you are using a pfile.
- shutdown the database
- edit the init.ora file to have the line
- UNDO_MANAGEMENT=AUTO
- make sure the parameter
- UNDO_TABLESPACE = UNDOTBS1
- Restart the database
2. If the value column has a value in it then you are using a spfile so you can do the following.
- show parameter undo_tablespace (to make sure it ='s UNDOTBS1
- alter system set UNDO_MANAGEMENT=AUTO SCOPE=SPFILE;
- the scope part tells oracle to change the parameter in the spfile but not to try to use it till the database is restarted (used for non dynamic parameters)
- shutdown the database;
- open the database, and that should be you.
If you have however decided to use the manual management of undo and you want to create and bring online the rollback segments yourself, well that's a bigger issue, which I will get into if you tell me that's your interest..
Get back to me and let me know how you get on.
Allie
|
|
|
|
| Re: Undo Management [message #161742 is a reply to message #161181] |
Mon, 06 March 2006 23:27 |
gajini
Messages: 262
Registered: January 2006
|
Senior Member |
|
|
Hi Allie,
I decided to use the manual management of undo and want to bring the rollback segments online myself,Can you please help me how to do this.
Reply as soon as possible
Thanks,
Malru
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
