Home » RDBMS Server » Server Administration » reset parameter value in spfile
reset parameter value in spfile [message #225299] Mon, 19 March 2007 11:16 Go to next message
fengbi
Messages: 9
Registered: March 2007
Junior Member
Hi, I accidently put remote_listener=' ' in the spfile, and the database can't startup now because of the wrong remote_listener value. how can i reset it and startup the database (10g)? thanks very much.
Re: reset parameter value in spfile [message #225303 is a reply to message #225299] Mon, 19 March 2007 11:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

startup mount;
create pfile from spfile;
!vi pfile --> correct it
create spfile from pfile;
shutdown;
startup;

Regards
Michel
Re: reset parameter value in spfile [message #225315 is a reply to message #225303] Mon, 19 March 2007 12:22 Go to previous messageGo to next message
fengbi
Messages: 9
Registered: March 2007
Junior Member
thanks Michel, but it doesn't work.
the database even can't STARTUP NOMOUNT because of the wrong remote_listener value, otherwise it would be easily reset by ALTER SYSTEM RESET REMOTE_LISTENER=...
Re: reset parameter value in spfile [message #225318 is a reply to message #225299] Mon, 19 March 2007 12:27 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
You'll need to create a new pfile based upon what is in the alert_SID.log file from the previous good startup.
The non-default parameter values are displayed in this file.
Re: reset parameter value in spfile [message #225320 is a reply to message #225315] Mon, 19 March 2007 12:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I tried but don't find any issue setting REMOTE_LISTENER to ''. Are you sure the problem comes from that?
SQL> alter system set remote_listener='' scope=spfile;

System altered.

SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  209715200 bytes
Fixed Size                  1289748 bytes
Variable Size             109052396 bytes
Database Buffers           96468992 bytes
Redo Buffers                2904064 bytes
Database mounted.
Database opened.

Regards
Michel
Re: reset parameter value in spfile [message #225321 is a reply to message #225315] Mon, 19 March 2007 12:34 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Another option would be to create a copy of your spfile and then use 'stings' on the file if on UNIX or open in Wordpad if on Windows. Then you can copy the parameters & values from the file and save to a ascii (text) file and startup with this file.
Re: reset parameter value in spfile [message #225350 is a reply to message #225320] Mon, 19 March 2007 13:58 Go to previous messageGo to next message
fengbi
Messages: 9
Registered: March 2007
Junior Member
Michel, in my situation, there was a space in the quetation marks.
alter system set remote_listener=' ' scope=spfile;
Re: reset parameter value in spfile [message #225356 is a reply to message #225350] Mon, 19 March 2007 14:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I can do it this way (Oracle 10.2.0.3 on WinXP):
SQL> alter system set remote_listener=' ' scope=spfile;

System altered.

SQL> shutdown;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORA-00119: invalid specification for system parameter REMOTE_LISTENER
ORA-00132: syntax error or unresolved network name ' '
SQL> startup nomount
ORA-00119: invalid specification for system parameter REMOTE_LISTENER
ORA-00132: syntax error or unresolved network name ' '
SQL> create pfile from spfile;
ERROR:
OCI-21710: argument is expecting a valid memory address of an object



File created.

SQL> ed D:\Oracle\ora102\database\initMIKA.ora

SQL> create spfile from pfile;
ERROR:
ORA-01034: ORACLE not available



File created.

SQL> startup
ORACLE instance started.
Total System Global Area  209715200 bytes
Fixed Size                  1289748 bytes
Variable Size             113246700 bytes
Database Buffers           92274688 bytes
Redo Buffers                2904064 bytes
Database mounted.
Database opened.

Regards
Michel
Re: reset parameter value in spfile [message #225361 is a reply to message #225350] Mon, 19 March 2007 14:13 Go to previous messageGo to next message
fengbi
Messages: 9
Registered: March 2007
Junior Member
actually, CREATE PFILE FROM SPFILE and CREATE SPFILE FROM PFILE
can work with idle instances (no pfile or spfile needed at this stage). so the problem is easily fixed.
thanks everybody for your help.
Re: reset parameter value in spfile [message #225363 is a reply to message #225356] Mon, 19 March 2007 14:16 Go to previous message
fengbi
Messages: 9
Registered: March 2007
Junior Member
Thank you very much Michel!
Previous Topic: 10g redolog Files have all only one change date
Next Topic: How to Create Database in Oracle 10G Express Edition?
Goto Forum:
  


Current Time: Sat Dec 10 22:42:48 CST 2016

Total time taken to generate the page: 0.14312 seconds