bkreynolds48
asked on
ORA-02097: parameter cannot be modified because specified value is invalid
created undo tablespace
ALTER SYSTEM SET undo_tablespace = "UNDOTBS_01";
ALTER SYSTEM SET undo_tablespace = "UNDOTBS_01"
*
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
what am I doing wrong?
ALTER SYSTEM SET undo_tablespace = "UNDOTBS_01";
ALTER SYSTEM SET undo_tablespace = "UNDOTBS_01"
*
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
what am I doing wrong?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try
ALTER SYSTEM SET undo_tablespace = 'UNDOTBS_01' scope = both;
But before this you have to release "logically" the old UNDO:
ALTER SYSTEM SET undo_retention = 5 scope = both;
If this is production DB then be very careful. Experiment on a test installation.
ALTER SYSTEM SET undo_tablespace = 'UNDOTBS_01' scope = both;
But before this you have to release "logically" the old UNDO:
ALTER SYSTEM SET undo_retention = 5 scope = both;
If this is production DB then be very careful. Experiment on a test installation.
ASKER
If this is production DB then be very careful. Experiment on a test installation
I was setting this up in dev to test so I bounced the database - took the rbs statement out of the init.ora file. How can I tell if things are fine there - I have to move this to production this week.
Thanks
I was setting this up in dev to test so I bounced the database - took the rbs statement out of the init.ora file. How can I tell if things are fine there - I have to move this to production this week.
Thanks
>>>How can I tell if things are fine there
I'm not sure what you're asking. Test your applications do they work?
If you want to see if the parameters are what you think they are, look in v$parameter.
Also, check to see if you can use the alter statement in your question, if you can, then you must be in AUTO (undo), if you can't, then you're still in MANUAL (rollback)
I'm not sure what you're asking. Test your applications do they work?
If you want to see if the parameters are what you think they are, look in v$parameter.
Also, check to see if you can use the alter statement in your question, if you can, then you must be in AUTO (undo), if you can't, then you're still in MANUAL (rollback)
What are you intending to do?
According to me you want to change the UNDO Tablespace.
If it is os, please confirm we (I)will send you step by step instruction.
If this is not the case please clarify....
According to me you want to change the UNDO Tablespace.
If it is os, please confirm we (I)will send you step by step instruction.
If this is not the case please clarify....
ASKER
Just need to change the database from manual rollback to undo.
I can only work on production on Sunday morning so can build the undo tablespace but not switch it until Sunday. -- testing in dev is not very profitable because the developers don't use that database every day which is why I wasn't sure the changes I made were correct. On Sunday in production I will leave the rollback tablespace in case I need to switch back --- I will shutdown the app, the database, bring the database up switching to undo then bring the app up. This is why I was asking about how to know if the change to undo is working - what to look for.
I can only work on production on Sunday morning so can build the undo tablespace but not switch it until Sunday. -- testing in dev is not very profitable because the developers don't use that database every day which is why I wasn't sure the changes I made were correct. On Sunday in production I will leave the rollback tablespace in case I need to switch back --- I will shutdown the app, the database, bring the database up switching to undo then bring the app up. This is why I was asking about how to know if the change to undo is working - what to look for.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
alter system set undo_tablespace = 'UNDOTBS_01' scope = both;
alter system set undo_tablespace = 'UNDOTBS_01' scope = both
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup
I don't us an spfile - is there a way around this
SYSTEM > select name, value from v$parameter where name like 'undo%'
NAME VALUE
-------------------- -------------------------- ----
undo_management AUTO
undo_tablespace undotbs_01
undo_retention 7200
alter system set undo_tablespace = 'UNDOTBS_01' scope = both
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup
I don't us an spfile - is there a way around this
SYSTEM > select name, value from v$parameter where name like 'undo%'
NAME VALUE
-------------------- --------------------------
undo_management AUTO
undo_tablespace undotbs_01
undo_retention 7200
you're using init.ora which is a pfile, not an spfile so "scope=both" doesn't apply, just leave that part off
and issue the statement like you originally did.
you need to modify the init.ora to make a permanent change
otherwise your changes will only last until the instance is bounced
looks like your changes took though, so you don't need to set to undotbs_01 because it already is
you might want to consider switching to spfiles, they make changes like this easier because "both" meaning (memory and spfile) could be applied in one step
and issue the statement like you originally did.
you need to modify the init.ora to make a permanent change
otherwise your changes will only last until the instance is bounced
looks like your changes took though, so you don't need to set to undotbs_01 because it already is
you might want to consider switching to spfiles, they make changes like this easier because "both" meaning (memory and spfile) could be applied in one step
ASKER
What would switching to an spfile?
Is this simple or complicated?
Do you not use the init.ora if you switch to an spfile?
I have always been working about corruption in an spfile - the init.ora file I backup every week during my cold backup so it can always be replaced.
Is this simple or complicated?
Do you not use the init.ora if you switch to an spfile?
I have always been working about corruption in an spfile - the init.ora file I backup every week during my cold backup so it can always be replaced.
it's relatively simple but not really related to this question.
It was just a suggestion, certainly not required.
It was just a suggestion, certainly not required.
ASKER
Thanks so much
undo_management =AUTO
then you'll be able to define the undo_tablespace parameter