OraFAQ Forum: Server Administration » reset parameter value in spfile

Home » RDBMS Server » Server Administration » reset parameter value in spfile

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

reset parameter value in spfile [message #225299]

Mon, 19 March 2007 11:16

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.

Report message to a moderator

Re: reset parameter value in spfile [message #225303 is a reply to message #225299]

Mon, 19 March 2007 11:22

Michel Cadot
Messages: 68686
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

Report message to a moderator

Re: reset parameter value in spfile [message #225315 is a reply to message #225303]

Mon, 19 March 2007 12:22

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=...

Report message to a moderator

Re: reset parameter value in spfile [message #225318 is a reply to message #225299]

Mon, 19 March 2007 12:27

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: reset parameter value in spfile [message #225320 is a reply to message #225315]

Mon, 19 March 2007 12:34

Michel Cadot
Messages: 68686
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

Report message to a moderator

Re: reset parameter value in spfile [message #225321 is a reply to message #225315]

Mon, 19 March 2007 12:34

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.

Report message to a moderator

Re: reset parameter value in spfile [message #225350 is a reply to message #225320]

Mon, 19 March 2007 13:58

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;

Report message to a moderator

Re: reset parameter value in spfile [message #225356 is a reply to message #225350]

Mon, 19 March 2007 14:05

Michel Cadot
Messages: 68686
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

Report message to a moderator

Re: reset parameter value in spfile [message #225361 is a reply to message #225350]

Mon, 19 March 2007 14:13

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.

Report message to a moderator

Re: reset parameter value in spfile [message #225363 is a reply to message #225356]

Mon, 19 March 2007 14:16

fengbi
Messages: 9
Registered: March 2007

Junior Member

Thank you very much Michel!

Report message to a moderator

Previous Topic:	10g redolog Files have all only one change date
Next Topic:	How to Create Database in Oracle 10G Express Edition?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 02:04:22 CDT 2024