Home » SQL & PL/SQL » Client Tools » SP2-0667: Message file sp1<lang>.msb not found
SP2-0667: Message file sp1<lang>.msb not found [message #672910] Wed, 31 October 2018 17:20 Go to next message
mcdinesh.200@gmail.com
Messages: 18
Registered: March 2016
Junior Member
Hi, I am getting the below errors while accessing the SQLPLUS.

SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

I tried multiple scenarios changing the Oracle_home and path variables but no luck.
These are my env variables.

C:\Windows\System32>path
PATH=C:\oraclexe\app\oracle\product\11.2.0\server\bin\;C:\Windows\system32;C:\Wi
ndows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Pr
ogram Files (x86)\ATI Technologies\ATI.ACE\Core-Static;C:\instantclient-basic-wi
ndows.x64-11.2.0.4.0\instantclient_11_2\;C:\Users\DINESH\AppData\Local\Programs\
Python\Python37\Scripts\;C:\Users\DINESH\AppData\Local\Programs\Python\Python37\

ORACLE_HOME:
C:\instantclient-basic-windows.x64-11.2.0.4.0\instantclient_11_2\;C:\oraclexe\app\oracle\product\11.2.0\server\;C:\oraclexe\app\oracl e\product\11.2.0\server\bin\;C:\oraclexe\app\oracle\product\11.2.0\server\bin\;C:\oraclexe\app\oracle\product\11.2.0\server\


Thanks,
Dinesh
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672914 is a reply to message #672910] Wed, 31 October 2018 17:44 Go to previous messageGo to next message
EdStevens
Messages: 1148
Registered: September 2013
Senior Member
mcdinesh.200@gmail.com wrote on Wed, 31 October 2018 17:20
Hi, I am getting the below errors while accessing the SQLPLUS.

SP2-0667: Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

I tried multiple scenarios changing the Oracle_home and path variables but no luck.
These are my env variables.

C:\Windows\System32>path
PATH=C:\oraclexe\app\oracle\product\11.2.0\server\bin\;C:\Windows\system32;C:\Wi
ndows;C:\Windows\System32\Wbem;C:\Windows\System32\WindowsPowerShell\v1.0\;C:\Pr
ogram Files (x86)\ATI Technologies\ATI.ACE\Core-Static;C:\instantclient-basic-wi
ndows.x64-11.2.0.4.0\instantclient_11_2\;C:\Users\DINESH\AppData\Local\Programs\
Python\Python37\Scripts\;C:\Users\DINESH\AppData\Local\Programs\Python\Python37\

ORACLE_HOME:
C:\instantclient-basic-windows.x64-11.2.0.4.0\instantclient_11_2\;C:\oraclexe\app\oracle\product\11.2.0\server\;C:\oraclexe\app\oracl e\product\11.2.0\server\bin\;C:\oraclexe\app\oracle\product\11.2.0\server\bin\;C:\oraclexe\app\oracle\product\11.2.0\server\


Thanks,
Dinesh
Your ORACLE_HOME is all messed up. ORACLE_HOME is not like PATH. You can't just concatenate a bunch of directories together. It has to be a single directory.


Re: SP2-0667: Message file sp1<lang>.msb not found [message #672925 is a reply to message #672910] Thu, 01 November 2018 03:48 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
When did this start happening? Oracle on Windows also populates the registry. You've got a munged install by the looks of it. You don't say which version of Oracle but I assume it is Oracle 11 XE from your OP. If so, try going through the deinstall instructions here. Follow the instructions exactly. Oracle 11 on Windows is a lot better at clearing up after itself than earlier versions. Then go through the install instructions here.
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672939 is a reply to message #672925] Thu, 01 November 2018 07:20 Go to previous messageGo to next message
mcdinesh.200@gmail.com
Messages: 18
Registered: March 2016
Junior Member
As you said, I removed the variable ORACLE_HOME and kept only path as C:\oraclexe\app\oracle\product\11.2.0\server

Then I kept Client directory path in Path variable.
C:\instantclient-basic-windows.x64-11.2.0.4.0\instantclient_11_2\

Now, when I tried to access the Sqlplus its throwing error like sqlplus not available.


Then, I added the Oracle server path to Path variable along with Client directory path.

i.e. C:\oraclexe\app\oracle\product\11.2.0\server\bin\;C:\instantclient-basic-windows.x64-11.2.0.4.0\instantclient_11_2\



Now, I can able to access the sqlplus but its throwing below error.

C:\Users\DINESH>sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Thu Nov 1 17:05:41 2018

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Enter user-name: hr

Enter password:

ERROR:

ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist

Process ID: 0

Session ID: 0 Serial number: 0



I tried to restart the services for Oracle but none of them or in enable mode to start or stop.

Re: SP2-0667: Message file sp1<lang>.msb not found [message #672940 is a reply to message #672939] Thu, 01 November 2018 07:23 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
If that is the error you're getting then you can simply:
SQL> connect / as sysdba;
SQL> startup;

Have you tried?
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672946 is a reply to message #672940] Thu, 01 November 2018 07:47 Go to previous messageGo to next message
mcdinesh.200@gmail.com
Messages: 18
Registered: March 2016
Junior Member
Thanks for quick response.

I tried the way that you mentioned but this time getting the below error.

C:\Users\DINESH>sqlplus /NOLOG

SQL*Plus: Release 11.2.0.2.0 Production on Thu Nov 1 18:08:12 2018

Copyright (c) 1982, 2014, Oracle. All rights reserved.

SQL> connect / as sysdba;
ERROR:
ORA-01031: insufficient privileges

Re: SP2-0667: Message file sp1<lang>.msb not found [message #672947 is a reply to message #672946] Thu, 01 November 2018 07:52 Go to previous messageGo to next message
BlackSwan
Messages: 26640
Registered: January 2009
Location: SoCal
Senior Member
mcdinesh.200@gmail.com wrote on Thu, 01 November 2018 05:47
Thanks for quick response.

I tried the way that you mentioned but this time getting the below error.

C:\Users\DINESH>sqlplus /NOLOG

SQL*Plus: Release 11.2.0.2.0 Production on Thu Nov 1 18:08:12 2018

Copyright (c) 1982, 2014, Oracle. All rights reserved.

SQL> connect / as sysdba;
ERROR:
ORA-01031: insufficient privileges

You need to be logged onto the OS as the same OS user who owns the Oracle software (usually "oracle").
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672949 is a reply to message #672946] Thu, 01 November 2018 07:54 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
The user that you are logged in as has to be a member of the "ora_dba" Windows group. Also, copy and paste the contents of your C:\oraclexe\app\oracle\product\11.2.0\server\sqlnet.ora file here. Please use [code] tags to format any output.
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672950 is a reply to message #672947] Thu, 01 November 2018 07:54 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
On Windows?
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672953 is a reply to message #672950] Thu, 01 November 2018 08:04 Go to previous messageGo to next message
mcdinesh.200@gmail.com
Messages: 18
Registered: March 2016
Junior Member
Yes its windows.

The sqlnet.ora file is lying under C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN. below is the content.

[SQLNET.AUTHENTICATION_SERVICES = (NTS)]
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672955 is a reply to message #672953] Thu, 01 November 2018 08:09 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Quote:
The user that you are logged in as has to be a member of the "ora_dba" Windows group.
Is it?
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672956 is a reply to message #672955] Thu, 01 November 2018 08:11 Go to previous messageGo to next message
mcdinesh.200@gmail.com
Messages: 18
Registered: March 2016
Junior Member
how can I check this?
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672957 is a reply to message #672956] Thu, 01 November 2018 08:19 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
You don't mention your version of Windows, but try:
<Windows key> + R
Then type
compmgmt.msc
into the Run Dialog box. Navigate to "Local Users and Groups" -> "Groups". Is there a group called "ora_dba"? If so, open it and see what usernames are part of the group.
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672960 is a reply to message #672957] Thu, 01 November 2018 08:29 Go to previous messageGo to next message
mcdinesh.200@gmail.com
Messages: 18
Registered: March 2016
Junior Member
Yes. The ORA_DBA group exists and it contains only one username as 'sony' which is not my computer name.

My computer name is 'DINESH'
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672961 is a reply to message #672960] Thu, 01 November 2018 08:32 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
If you are logged in as "dinesh" then you must add that to the "ora_dba" group.
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672964 is a reply to message #672961] Thu, 01 November 2018 08:39 Go to previous messageGo to next message
mcdinesh.200@gmail.com
Messages: 18
Registered: March 2016
Junior Member
Yeah, I have created user DINESH in Ora_dba and tried as you mentioned above. Still getting few error. below is the output.

[SQL> connect / as sysdba;
Connected to an idle instance.
SQL> startup;
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file 'C:\oraclexe\app\oracle\product\11.2.0\serv
er\dbs/spfileXE.ora'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.]
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672965 is a reply to message #672964] Thu, 01 November 2018 08:41 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
OK, I'm going to take a punt here. Try:
SQL> create spfile from pfile;
SQL> startup;
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672966 is a reply to message #672965] Thu, 01 November 2018 08:44 Go to previous messageGo to next message
BlackSwan
Messages: 26640
Registered: January 2009
Location: SoCal
Senior Member
post results (COPY & PASTE) from OS command below

DIR C:\oraclexe\app\oracle\product\11.2.0\server\dbs
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672967 is a reply to message #672965] Thu, 01 November 2018 08:45 Go to previous messageGo to next message
mcdinesh.200@gmail.com
Messages: 18
Registered: March 2016
Junior Member
Still getting same error.

[SQL> connect / as sysdba;
Connected to an idle instance.
SQL> create spfile from pfile;

File created.

SQL> startup;
ORA-01078: failure in processing system parameters
ORA-01565: error in identifying file 'C:\oraclexe\app\oracle\product\11.2.0\serv
er\dbs/spfileXE.ora'
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
SQL>]
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672969 is a reply to message #672967] Thu, 01 November 2018 08:49 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Quote:
post results (COPY & PASTE) from OS command below

DIR C:\oraclexe\app\oracle\product\11.2.0\server\dbs
Do as BlackSwan suggests.
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672970 is a reply to message #672967] Thu, 01 November 2018 08:50 Go to previous messageGo to next message
mcdinesh.200@gmail.com
Messages: 18
Registered: March 2016
Junior Member
Actually the SPFILEXE.ORA file is lying under 'C:\oraclexe\app\oracle\product\11.2.0\server\database' path.

But system is searching in 'C:\oraclexe\app\oracle\product\11.2.0\server\dbs' path
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672971 is a reply to message #672970] Thu, 01 November 2018 08:53 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
So copy it to:
C:\oraclexe\app\oracle\product\11.2.0\server\dbs
and try again.

Windows uses "database" and *nix uses "dbs" so I don't know what happened there.
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672972 is a reply to message #672970] Thu, 01 November 2018 08:54 Go to previous messageGo to next message
BlackSwan
Messages: 26640
Registered: January 2009
Location: SoCal
Senior Member
mcdinesh.200@gmail.com wrote on Thu, 01 November 2018 06:50
Actually the SPFILEXE.ORA file is lying under 'C:\oraclexe\app\oracle\product\11.2.0\server\database' path.

But system is searching in 'C:\oraclexe\app\oracle\product\11.2.0\server\dbs' path

FWIW use of ORACLE_HOME/dbs is *NIX naming "standard" & ORACLE_HOME\database is WINDOWS "standard"

I can't think of how or why Oracle behaves as you report.
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672974 is a reply to message #672972] Thu, 01 November 2018 08:58 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Snap! Shocked
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672975 is a reply to message #672972] Thu, 01 November 2018 08:59 Go to previous messageGo to next message
mcdinesh.200@gmail.com
Messages: 18
Registered: March 2016
Junior Member
Again new error.

SQL> startup;
ORA-01506: missing or illegal database name
SQL>
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672977 is a reply to message #672975] Thu, 01 November 2018 09:03 Go to previous messageGo to next message
BlackSwan
Messages: 26640
Registered: January 2009
Location: SoCal
Senior Member
mcdinesh.200@gmail.com wrote on Thu, 01 November 2018 06:59
Again new error.

SQL> startup;
ORA-01506: missing or illegal database name
SQL>

What is value of OS environmental variable ORACLE_SID?

It should be %ORACLE_SID%=XE
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672978 is a reply to message #672977] Thu, 01 November 2018 09:07 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
I thought that but I would expect it to be in the registry... We'll see.
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672979 is a reply to message #672978] Thu, 01 November 2018 09:08 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Quote:

ORA-01506: missing or illegal database name
Cause
You tried to execute an ALTER DATABASE command without a specified database name, and there is no value specified for the DB_NAME initialization parameter in the initialization file.

Resolution
The option(s) to resolve this Oracle error are:

Option #1
Either specify a database name in the ALTER DATABASE command or set a value for the DB_NAME parameter in the initialization file.
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672980 is a reply to message #672977] Thu, 01 November 2018 09:13 Go to previous messageGo to next message
mcdinesh.200@gmail.com
Messages: 18
Registered: March 2016
Junior Member
c:\>echo %ORACLE_HOME%
C:\oraclexe\app\oracle\product\11.2.0\server\

c:\>echo %ORACLE_SID%
XE

c:\>path
PATH=C:\oraclexe\app\oracle\product\11.2.0\server;C:\oraclexe\app\oracle\product
\11.2.0\server\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem;C:\Wi
ndows\System32\WindowsPowerShell\v1.0\;C:\Program Files (x86)\ATI Technologies\A
TI.ACE\Core-Static;C:\instantclient-basic-windows.x64-11.2.0.4.0\instantclient_1
1_2;C:\Users\DINESH\AppData\Local\Programs\Python\Python37\Scripts\;C:\Users\DIN
ESH\AppData\Local\Programs\Python\Python37\

[Updated on: Thu, 01 November 2018 09:16]

Report message to a moderator

Re: SP2-0667: Message file sp1<lang>.msb not found [message #672981 is a reply to message #672980] Thu, 01 November 2018 09:15 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
Copy and paste the contents of
C:\oraclexe\app\oracle\product\11.2.0\server\dbs\initXE.ora
here please. And well done for using [ CODE ] tags. It makes it much easier for us to read and help you Smile
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672982 is a reply to message #672981] Thu, 01 November 2018 09:18 Go to previous messageGo to next message
mcdinesh.200@gmail.com
Messages: 18
Registered: March 2016
Junior Member
The initXE.ORA content is here.

SPFILE='C:\oraclexe\app\oracle\product\11.2.0\server\dbs/spfileXE.ora' 
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672984 is a reply to message #672982] Thu, 01 November 2018 09:52 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
No, the file initXE.ORA is used to create spfileXE.ora. initXE.ORA is a text file so please open it and post the content here.
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672986 is a reply to message #672984] Thu, 01 November 2018 10:17 Go to previous messageGo to next message
mcdinesh.200@gmail.com
Messages: 18
Registered: March 2016
Junior Member
Apart from initXE.ora file, I have oradim.txt file and SPFILEXE.ORA only inside the dbs folder and database folder.
The oradim.txt file content is here.

Thu Nov 01 17:56:33 2018
C:\oraclexe\app\oracle\product\11.2.0\server\bin\oradim.exe -startup -sid xe -usrpwd *  -log oradim.log -nocheck 0 
Thu Nov 01 17:56:34 2018
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672987 is a reply to message #672986] Thu, 01 November 2018 10:21 Go to previous messageGo to next message
BlackSwan
Messages: 26640
Registered: January 2009
Location: SoCal
Senior Member
please post the content of SPFILEXE.ORA file.
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672988 is a reply to message #672987] Thu, 01 November 2018 10:34 Go to previous messageGo to next message
mcdinesh.200@gmail.com
Messages: 18
Registered: March 2016
Junior Member
The content of SPFILEXE.ORA is here.

                                                                             Y   »;                                                                                                                                                                                                                                                                                                                                                                                                                                        *.db_name='XE'
*.SPFILE='C:\oraclexe\app\oracle\product\11.2.0\server\dbs/spfileXE.ora'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672989 is a reply to message #672988] Thu, 01 November 2018 10:37 Go to previous messageGo to next message
mcdinesh.200@gmail.com
Messages: 18
Registered: March 2016
Junior Member
Actually the *.db_name='XE' was missing in the initXE.ORA file. I have added it and created SPFILEXE.ORA again and tried to startup.
But still getting some error.

SQL> startup;
ORACLE instance started.

Total System Global Area  217157632 bytes
Fixed Size                  2251776 bytes
Variable Size             159384576 bytes
Database Buffers           50331648 bytes
Redo Buffers                5189632 bytes
ORA-00205: error in identifying control file, check alert log for more info
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672990 is a reply to message #672989] Thu, 01 November 2018 11:15 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
You control file location will be referenced in your now created initXE.ORA. Post it here or look for the parameter "control_files" parameter and work it out for yourself Smile

You will have to recreate your SPFILE every time you edit the initXE.ORA, obviously. That file is also known as the PFILE (parameter file)
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672991 is a reply to message #672990] Thu, 01 November 2018 11:42 Go to previous messageGo to next message
mcdinesh.200@gmail.com
Messages: 18
Registered: March 2016
Junior Member
The initXE.ora file doesn't have any information regarding control file. Here is the content.

*.SPFILE='C:\oraclexe\app\oracle\product\11.2.0\server\dbs/spfileXE.ora',*.db_name='XE'

Content of SPFILEXE.ORA.

                                                                             Y   »;                                                                                                                                                                                                                                                                                                                                                                                                                                        *.db_name='XE'
*.SPFILE='C:\oraclexe\app\oracle\product\11.2.0\server\dbs/spfileXE.ora'
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672992 is a reply to message #672991] Thu, 01 November 2018 11:49 Go to previous messageGo to next message
gazzag
Messages: 1082
Registered: November 2010
Location: Bristol, UK
Senior Member
I'm afraid that your XE install is so munged that I think you should start again. We don't know what other problems lie in store even if we fix this one. Why don't you uninstall and re-install like I recommended here? It sounds like you have inherited a load of problems created by someone else.

Is there important data you need to keep?
Re: SP2-0667: Message file sp1<lang>.msb not found [message #672993 is a reply to message #672991] Thu, 01 November 2018 11:50 Go to previous messageGo to previous message
BlackSwan
Messages: 26640
Registered: January 2009
Location: SoCal
Senior Member
post results (COPY & PASTE) from OS command below

DIR C:\oraclexe\app\oracle\product\11.2.0\server\database
Previous Topic: Password Expired
Next Topic: Enterprise Data Quality
Goto Forum:
  


Current Time: Sun Dec 08 09:53:19 CST 2019