Solved

ORA-02097: parameter cannot be modified because specified value is invalid

Posted on 2011-02-21
13
2,197 Views
Last Modified: 2013-12-12
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?
0
Comment
Question by:bkreynolds48
  • 6
  • 5
  • 2
13 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
ID: 34943488
sorry, I think I misunderstood your previous question.

when you asked if you needed to bounce, I thought you were asking if you needed to bounce in order to add more undo or rollback to your system (based on the other questions)


if you want to swtich from rollback to undo,  YES, that does require a bounce.
because you can't set undo_management parameter without bouncing the system
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34943502
set


undo_management =AUTO

then you'll be able to define the undo_tablespace parameter
0
 
LVL 47

Expert Comment

by:schwertner
ID: 34952297
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 1

Author Comment

by:bkreynolds48
ID: 34960190
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
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34960448
>>>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)
0
 
LVL 47

Expert Comment

by:schwertner
ID: 34960685
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....
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 34960992
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.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 34961055
look in v$parameter,  see if

undo_management =AUTO

if it isn't then it didn't work.

if it is,  you should then be able to do the SET undo_tablespace.

If you can't set the undo_tablespace, that's another test to see if it worked.  If you are still in manual(rollback) then you'll get the error you posted above
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 34961074
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
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34961105
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
0
 
LVL 1

Author Comment

by:bkreynolds48
ID: 34961135
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.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 34961177
it's relatively simple but not really related to this question.  
It was just a suggestion,  certainly not required.
0
 
LVL 1

Author Closing Comment

by:bkreynolds48
ID: 34961214
Thanks so much
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query 9 48
How can I rollback insert statements after commit in oracle? 7 143
How do I call MySQL Stored Procedure from oracle using HS link ? 5 46
use lov values 2 51
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question