| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: 10G - editing SPFILE
Charlie Edwards wrote:
> "Howard J. Rogers" <hjr_at_dizwell.com> wrote in message
> news:<4176d09a$0$22901$afc38c87_at_news.optusnet.com.au>...
>> >> >> At the command prompt, you'd type: >> >> sqlplus /nolog >> connect / as sysdba >> create pfile from spfile; >> >> (And that last command needs no instance running to work). >>
That error usually means the OracleServiceXXX which provides the memory needed for your instance isn't started before you try and connect to it.
Of course, you will have difficulty fully starting the service if the spfile is stuffed. But you should still be able to functionally start the service.
Also: make sure you're connected to the server itself, not going via a client. And also make sure the account you're using to log onto Windows is a member of the ORA_DBA group. And if none of that works, try using password file authentication instead of O/S authentication (ie, 'connect sys/password as sysdba' rather than 'connect / as sysdba')
By way of a worked example (and using only the command line, because it's difficult to put screenshots in a text-based newsgroup!):
C:\Documents and Settings\howardjr>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Oct 22 06:06:06 2004
ERROR:
ORA-12560: TNS:protocol adapter error
[So I am getting the exact same error message as you]
C:\Documents and Settings\howardjr> net start OracleServiceWin92 The OracleServiceWIN92 service is starting......... The OracleServiceWIN92 service was started successfully.
C:\Documents and Settings\howardjr>sqlplus "/ as sysdba"
[So I start my service, and the error goes away. Note how many dots there are on the 'service is starting' line...]
SQL> create spfile from pfile;
File created.
SQL> startup force
ORACLE instance started.
Total System Global Area 101784276 bytes
Fixed Size 453332 bytes Variable Size 75497472 bytes Database Buffers 25165824 bytes Redo Buffers 667648 bytesDatabase mounted.
[That's to get my database using an spfile in the first place]
SQL> alter system set shared_pool_size=0 scope=spfile; System altered.
[And that's so I will quickly wish I wasn't. An illegal value has just gotten into the spfile, which will prevent future re-starts working properly.]
SQL> shutdown abort
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
C:\Documents and Settings\howardjr> net stop OracleServiceWin92 The OracleServiceWIN92 service is stopping... The OracleServiceWIN92 service was stopped successfully.
[So, imagine I've now shutdown everything for the night. Next morning...]
C:\Documents and Settings\howardjr> net start OracleServiceWin92 The OracleServiceWIN92 service is starting. The OracleServiceWIN92 service was started successfully.
[There's your first clue things aren't right. The service starts, but look how many dots there are on the 'service is starting' line this time. Not many!]
C:\Documents and Settings\howardjr>sqlplus "/ as sysdba"
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Oct 22 06:09:21 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to an idle instance.
[And that's why. The service has started, so the memory is available to an instance, but no instance was actually started (for reasons that I've already mentioned). But I am connected in SQL*Plus, so I can save the situation...]
SQL> create pfile from spfile;
File created.
[And at that point I can go off and edit the pfile in Wordpad, use that as the source of a replacement spfile, and everything will be fine.]
Regards
HJR
Received on Thu Oct 21 2004 - 15:12:58 CDT
 |
 |