SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

When execute ’sqlplus’ as a user outside of the dba or Oracle group, you get the following errors.

Error 6 initializing SQL*Plus
Message file sp1<lang>.msb not found
SP2-0750: You may need to set ORACLE_HOME to your Oracle software directory

Syntax:
$ sqlplus username/password

This problem did not occur in previous releases of Oracle.

Similar problems occur with other client tools, like IMP, EXP, etc.

Cause

The files that sqlplus needs to execute do not have read/execute permissions on the O/S level.

Solution

There is a one-off patch for the base bug 4516865, but this fixes the permissions problem only on the server side. Only for Linux, this patch includes fix for both server and client side (Bug 4747264).  Patch 4516865 provides a script called changePerm.sh. Since 10.2.0.2, the changperm.sh script is included in all patchsets and is documented in the patchset README.

At the time this note was written, there was no patch for the client side for platforms other than Linux.   However, there are a couple of workarounds:

I.  Logged in as the Oracle user (or the user that installed the 10gR2 software), manually change the permissions on the client.  For example:

chmod -R 755 <client_home>

In our case:

chmod -R 755 $ORACLE_HOME/sqlplus

II.  If doing a recursive permissions command is not acceptable, then you will need to pinpoint exactly what files the client is reading at the time of execution, and manually change permissions only on those files.  In our case, we need to pinpoint what files are being accessed by SQL*Plus.   To implement this workaround, please execute the following steps:

1. As the non-Oracle user, run the truss utility to find out which files are being accessed.  Sample command:
truss -aefo /tmp/truss_sqlplus.out sqlplus username/password

2. Use this truss_sqlplus.out trace file to see what files have error “EACCES” when attempting to access.   In our case, the truss_sqlplus.out showed a problem accessing the following file:
$ORACLE_HOME/sqlplus/mesg/sp1us.msb

Another possible error to search for in the truss output is ENOENT.  For example:
9775: open(“./sqlplus/mesg/sp1us.msb”, O_RDONLY) Err#2 ENOENT

3. Logged in as the Oracle user, change permissions on folders leading up to, and including sp1us.msb:

chmod 755 $ORACLE_HOME/sqlplus
chmod 755 $ORACLE_HOME/sqlplus/mesg
chmod 755 $ORACLE_HOME/sqlplus/mesg/sp1us.msb

4. After making above permission changes, a different error may appear when executing sqlplus as non-Oracle user, such as:

$ sqlplus username/password
SP2-1503: Unable to initialize Oracle call interface
SP2-0152: ORACLE may not be functioning properly
$

5. At this point, you need to re-run the truss (as non-Oracle) to see what other files are trying to be accessed.  In our case, the following files were trying to get accessed, but showed “EACCES” failure:

$ORACLE_HOME/nls/data/lx1boot.nlb
$ORACLE_HOME/oracore/zoneinfo/timezlrg.dat

6. Logged in as the Oracle user, change permissions on these files and the directories leading up to these files.

chmod 755 $ORACLE_HOME/nls
chmod 755 $ORACLE_HOME/nls/data
chmod 755 $ORACLE_HOME/nls/data/lx1boot.nlb
chmod 755 $ORACLE_HOME/oracore
chmod 755 $ORACLE_HOME/oracore/zoneinfo
chmod 755 $ORACLE_HOME/oracore/zoneinfo/timezlrg.dat

7. Now, invoking sqlplus as a non-Oracle user was successful in our case.

ORA-12638

SQL*Plus Connection Fails With ORA-12638

Checked for relevance on 07-FEB-2008.

Problem Description:
==================== 

You have installed Oracle 8i or newer client software on your Windows machine. The SQL*Plus connection fails with
Connections via the listener fail with the following error:

 ORA-12638: Credential retrieval failed
     Cause: The authentication service failed to retrieve the credentials of a
            user.
    Action: Enable tracing to determine the exact error.

Problem Explanation:
==================== 

The Windows client is trying to use NTS authentication and is failing.  If
you do not intend to use NTS authentication, please use the instructions
in this document to resolve this error.

Search Words:
============= 

sqlnet, sqlplus, net8, NTS,
Sqlnet.authentication_services= (NTS)
ORA-12638

Solution Description:
===================== 

Comment out the line: SQLNET.AUTHENTICATION_SERVICES = (NTS) in the sqlnet.ora file.
Alternatively, set the value to NONE.  

i.e.
SQLNET.AUTHENTICATION_SERVICES = (NONE)

This file is usually located at ORACLE_HOME\network\admin on the client.  

Solution Explanation:
===================== 

By removing the authentication request parameter in the sqlnet.ora file for
the client, a direct SQL*Plus connection can be established.

Get ORA-12638 Error when Connecting via SQL*Plus Using NTS authentication

Symptoms

ORA-12638 when connection via SQL*PLUS from using TNSNAMES resolution, and with SQLNET.AUTHENTICATION_SERVICES=(NTS) set in the SQLNET.ORA file.

Cause

The user who starts the Listener Service and Database service are different. One of them is changed from default Localsystem(Logon as) to a domain user.

Fix

Set the Logon as Local system for both services or set it to domain user.

TNS-03505: Failed to resolve name

Windows OS

set TNS_ADMIN=%ORACLE_HOME%\network\admin

Dos command prompt to test

Set TNS_ADMIN environment variable for SYSTEM and USER

Dos command prompt to test

DBLINK Fails With ORA-12154 After Setting TNS_ADMIN Variable

Symptoms

When creating a dblink between two databases, if TNS_ADMIN is not set or set to $ORACLE_HOME/network/admin  selecting across the dblink works fine.  If setting  $TNS_ADMIN to a  path different from  $ORACLE_HOME/network/admin, select from dblink can fail with:

ORA-12154: TNS:could not resolve the connect identifier specified

The workaround would be to create the dblink with a full connect descriptor instead of a TNS alias.

Here’s an example:
create database link DB_TEST connect to SCOTT
identified by “password”
using ‘(description=(address=(protocol =TCP)
(host=myhost)(port=1521))(connect_data=(SERVICE_NAME=sid.domain)))’;

Changes

This is likely a new database link or the environment has changed.  If $TNS_ADMIN has been set recently, this article may apply.

Cause

The problem is that the TNS alias is not being referenced.  If the origin database has not been restarted since the change in the ENV took place, the database would not reference a TNS alias in the $TNS_ADMIN directory.

Solution

The solution is to restart the origin database after setting TNS_ADMIN. TNS_ADMIN  is inherited when an instance starts.

HOW IS TNS_ADMIN SET?
=====================

The TNS_ADMIN value used by the server is inherited from  the listener. The
value set in the listener is derived from one (or a combination) of three
mechanisms:

a. set TNS_ADMIN as an environment variable
   % setenv TNS_ADMIN /oracle/TNS_ADMIN

   When the listener is started, this environment variable is picked up, and
   inherited by the server processes spawned by the listener whenever a connection
   is made.

b. explicity set TNS_ADMIN in the listener.ora
   The following excerpt from a listener.ora shows how to configure this:

    SID_LIST_LISTENER =
      (SID_LIST =
       ...
        (SID_DESC =
           (SID_NAME = V817)
           (ORACLE_HOME = /oracle2/OFA_base/app/oracle/product/8.1.7)
           (ENVS = 'TNS_ADMIN=/home/hpsupp/TNS_ADMIN')
         )
       ...

    When the listener is started, the value of TNS_ADMIN in the listener.ora
    overwrites the value picked up from the environment.

c. PMON registers the instance
   At instance startup, PMON picks up the TNS_ADMIN environment variable (in
   the same way that the listener does in Section (a) above). When PMON
   subsequently registers this instance, this value of TNS_ADMIN is passed to
   the listener; causing PMON's TNS_ADMIN value to overwrite the value the
   listener currently has.

   NB. If TNS_ADMIN is not set when PMON starts, then after registration, the
       listener's TNS_ADMIN value is cleared (ie, behaves as if not set).

It may be useful to know that the search order  for tnsnames.ora and
  sqlnet.ora is as follows:

  tnsnames.ora
     search for private tnsnames.ora
          $HOME/.tnsnames.ora  (this is a hidden file)

     in addition to $HOME/.tnsnames.ora, search for system tnsnames.ora
      - this search is halted after the first success
          $TNS_ADMIN/tnsnames.ora
          /etc/tnsnames.ora or /var/opt/oralce/tnsnames.ora  (platform dependant)
          $ORACLE_HOME/network/admin/tnsnames.ora

  sqlnet.ora
     $TNS_ADMIN/sqlnet.ora
     $ORACLE_HOME/network/admin/sqlnet.ora

     NB, for server processes $HOME/.sqlnet.ora is not read. This file is for
     client processes only.

My experienced the DBLINK Fails With ORA-12154 and solution:

When TNS changed, that caused hostname can't be ping and then Service alias
name entry in tnsnames.ora will cause ORA-12154 error.
Changed hostname to ip address and ping it is OK and modifying the revelant alias
entry in tnsnames.ora with its corresponding ip address will solve this DBLINK Fails With ORA-12154 error.

ORA-00439

Starting Primary or Standby Database

Problem Description:
==================== 

When attempting to startup a primary or standby database you receive the
following error message:

  ORA-00439: feature not enabled: Managed Standby
      Cause: The specified feature is not enabled.
     Action: Do not attempt to use this feature.

Querying on V$OPTION shows the Managed Standby parameter has a value of false.

Solution Description:
=====================

The Managed Standby option, which allows a primary or standby database to run
in Managed Recovery Mode, is available only with the Enterprise Edition of
8.1.X. 

If this error occurs during startup nomount while using oracle 8i Standard
Edition then all the standby specific parameters must be removed from the
init.ora file such as: 

STANDBY_ARCHIVE_DEST
LOG_ARCHIVE_DEST_N

Explanation:
============

If you are using 8.1.x Standard Edition, then you are limited to maintaining
the standby database in Manual Recovery Mode.

"ORA-00439: feature not enabled: Managed Standby"

Solution

The version of Oracle shipped with Agile Anywhere and Product Collaboration is the “Standard Edition”, which does not include the “Managed Standby” feature.

1. To eliminate this error, edit the init.ora file to remove the following parameters:

log_archive_dest_1
log_archive_dest_2
log_archive_dest_3
log_archive_dest_4
log_archive_dest_5

2. Add “log_archive_dest” and optionally “log_archive_duplex_dest” in the init.ora file. These two parameters used in combination still allows duplex archived logs. For example:

log_archive_dest=d:\oracle\oradata\aa8i\archive
log_archive_duplix_dest=d:\oracle\oradata\aa8i\archive2

PLS-00204

Oracle Error :: PLS-00204

function or pseudo-column “string” may be used inside a SQL statement only

Cause

A pseudocolumn or proscribed function was used in a procedural statement. The SQL pseudocolumns (CURRVAL, LEVEL, NEXTVAL, ROWID, ROWNUM) can be used only in SQL statements. Likewise, certain functions such as DECODE, DUMP, and VSIZE and the SQL group functions (AVG, MIN, MAX, COUNT, SUM, STDDEV, VARIANCE) can be used only in SQL statements.

Action

Remove the pseudocolumn reference or function call from the procedural statement. Or, replace the procedural statement with a SELECT INTO statement; for example, replace bonus := DECODE(rating, 1, 5000, 2, 2500, …); with the following statement: SELECT DECODE(rating, 1, 5000, 2, 2500, …) INTO bonus FROM dual;

OERR: ORA-12096 error in materialized view log on %s . %s

Error:	  ORA-12096  (ORA-12096)
Text:	  error in materialized view log on %s . %s
---------------------------------------------------------------------------
Cause:	There was an error originating from this materialized view log.
	One possible cause is that schema redefinition has occurred on the
	master table and one or more columns in the log is now a different
	type than corresponding master column(s). Another possible cause
	is that there is a problem accessing the underlying materialized
	view log table.
Action:	Check further error messages in stack for more detail about the
	cause. If there has been schema redefinition, drop the
	materialized view log and recreate it.

ORA-01536 After Revoking DBA Role

Applies to:

Oracle Server – Enterprise Edition – Version: 8.1.7.4 to 10.2.0.3
This problem can occur on any platform.

ORA-01536: space quota exceeded for tablespace ‘<Tablespace_Name>’
After revoking DBA or Resource Role from a user

Example:

SQL> conn /as sysdba
Connected.
SQL> create user testrights identified by testos;
User created.
SQL> grant connect, resource to testrights;
Grant succeeded.
SQL> connect testrights/testos;
Connected.

SQL> CREATE TABLE "TESTRIGHTS"."TESTTAB" ( "TESTFIELD" VARCHAR2(200) NOT NULL
, CONSTRAINT "TESTPK" PRIMARY KEY ("TESTFIELD") VALIDATE ) TABLESPACE "USERS" STORAGE ( INITIAL 64M) ;
Table created.
SQL> conn /as sysdba
Connected.
SQL> grant dba to testrights;
Grant succeeded.
SQL> revoke dba from testrights;
Revoke succeeded.
SQL> show user
USER is "SYS"
SQL> drop table testrights.testtab;
Table dropped.
SQL> conn testrights/testos;
Connected.
SQL> CREATE TABLE "TESTRIGHTS"."TESTTAB" ( "TESTFIELD" VARCHAR2(200) NOT NULL
, CONSTRAINT "TESTPK" PRIMARY KEY ("TESTFIELD") VALIDATE ) TABLESPACE "USERS"  STORAGE ( INITIAL 64M) ;
CREATE TABLE "TESTRIGHTS"."TESTTAB" ( "TESTFIELD" VARCHAR2(200) NOT NULL ,
CONSTRAINT "TESTPK" PRIMARY KEY ("TESTFIELD") VALIDATE ) TABLESPACE "USERS"
 STORAGE ( INITIAL 64M)
*
ERROR at line 1:
ORA-1536: space quota exceeded for tablespace 'USERS'
SQL> conn /as sysdba
Connected.
SQL> grant connect, resource to testrights;
Grant succeeded.
SQL> conn testrights/testos;
Connected.
SQL>
SQL> CREATE TABLE "TESTRIGHTS"."TESTTAB" ( "TESTFIELD" VARCHAR2(200) NOT NULL , CONSTRAINT "TESTPK" PRIMARY KEY ("TESTFIELD") VALIDATE ) TABLESPACE "USERS"
STORAGE ( INITIAL 64M) ;
Table created.

Cause

When roles were first introduced into Oracle in 7.0, the old Oracle V6 privileges of  RESOURCE and DBA were migrated to use the new role functionality. But because  the RESOURCE and DBA roles are not allowed to be granted UNLIMITED  TABLESPACE, in order to preserve the backwards compatibility with V6, the  parser automatically transforms statements such that “grant resource to abc” automatically becomes “grant resource, unlimited tablespace to abc” and  “revoke resource from abc” automatically becomes “revoke resource, unlimited  tablespace from abc”. The same is true when granting and revoking the DBA  role. This behaviour used to be well documented in the SQL reference guide which read:

Note: If you grant or revoke the RESOURCE or DBA role to or from a  user, Oracle implicitly grants or revokes the UNLIMITED TABLESPACE system privilege to or from the user.

To Resolve this issue you need to :

1] Grant DBA or Resource Role back to the user from whom it was revoked.

OERR: ORA-3206 maximum file size of (%s) blocks in AUTOEXTEND clause is out of range

Error:	ORA-3206
Text:	maximum file size of (%s) blocks in AUTOEXTEND clause is out of range
---------------------------------------------------------------------------
Cause:	The maximum file size for an autoextendable file has exceeded the
	maximum number of blocks allowed.
Action:	Reduce the size and retry.

SP2-1503 SP2-0152 SQL*Plus and Microsoft Vista

<< Using Microsoft Vista when the user in Windows Microsoft Vista is a non-administrative user and tries to connect from SQL Plus command line the following errors will occur:

SP2-1503: Unable to initialize Oracle Call Interface 
SP2-0152: ORACLE may not be functioning. >>

To work around this issue download Oracle client patch 10.2.0.3 with patch number 5337014.

This can be downloaded from Metalink under Patches & Updates -> Simple Search -> Insert 5337014 in the Patch number box and choose Microsoft Windows 32-bit for the platform (appx 852 Meg).

Installing the above patch – made SQL*Plus and TOAD work with Microsoft Vista.