Skip to Content
avatar image
Former Member

ORA-942 when accessing table SAPUSER Error on WP

Hi gurus, this is my landscape: I have an SAP PRD Server with Windows 2008 and Oracle 11i, 606 (came upgrade from 4.7) and works fine. We have an SAP Stand-by Server with a stand-by database, this is ok. Database replicate changes fine with Oracle Data Guard, when we run switchover on Stnad-by Server, everything is ok, database open fine.

My problem is when I try to start up SAP, SAP does not start up, and problem is with workprocess. I add OPS$ from stand-by in dba_users and fine, when I run r3trans -d, it finish with no error. I upgrade kernel, I check tsnames.ora, database password, etc, and they are fine.

I can connect to database with OPS$SAPServiceSID and OPS$SIDadm fine. When I check log (work) I can see following error in WP:

File: dev_w1

.

.

Wed Jul 02 10:16:47 2014

M  calling db_connect ...

B  Loading DB library 'D:\usr\sap\PSQ\DVEBMGS00\exe\dboraslib.dll' ...

B  Library 'D:\usr\sap\PSQ\DVEBMGS00\exe\dboraslib.dll' loaded

B  Version of 'D:\usr\sap\PSQ\DVEBMGS00\exe\dboraslib.dll' is "720.00", patchlevel (0.522)

C  Prepending D:\usr\sap\PSQ\DVEBMGS00\exe to Path.

C  Register application info.

C Wed Jul 02 10:16:49 2014

C  Oracle client version: 11.2.0.3.0, (dbsl 720 030114, non-unicode)

C  Detailed Oracle client (patch) info not available.

C Wed Jul 02 10:16:52 2014

C  Client NLS setting (by OCINlsGetInfo(con=0)): 'AMERICAN_AMERICA.WE8DEC'

C  Logon as OPS$-user to get SAPSR3's password

C  Connecting as /@SR3 on connection 0 (nls 0) ... (dbsl 720 030114, non-unicode)

C  Attaching to database server SR3 (con=0, svchp=0286E7F0, srvhp=0639BCE0)

C Wed Jul 02 10:16:53 2014

C  Starting user session: OCISessionBegin(con=0, usr='/', svc=0286E7F0, srv=0639BCE0, usr=063AACA0)

C     OCIStmtExecute() failed with -1=OCI_ERROR

C     SQL error 942:

C  Could not read session ID from V$MYSTAT. rc=-1.

C  Now '/@SR3' is connected: con=0, nls=0, session=-1, time='2014-07-02 10:16:53'

C     OCIStmtExecute() failed with -1=OCI_ERROR

C     SQL error 942:

C  *** ERROR => ORA-942 when accessing table SAPUSER

[dbsloci.c    16660]

C  Disconnecting from connection 0 ...

C  Closing user session (con=0, svc=0286E7F0, usr=063AACA0)

C  Disconnected (con=0) from ORACLE.

C  Info: 99=DBSL_ERR_DB, oerr=942, try to connect with default password.

C  Connecting as SAPSR3/<pwd>@SR3 on connection 0 (nls 0) ... (dbsl 720 030114, non-unicode)

C  Starting user session: OCISessionBegin(con=0, usr='SAPSR3', svc=0286E7F0, srv=0639BCE0, usr=063AACA0)

C Wed Jul 02 10:18:27 2014

C     OCISessionBegin(OCI_DEFAULT) failed with SQL error 1017:

C     ORA-01017: invalid username/password; logon denied                                                                           

C  Server handle (con=0,svchp=0286E7F0,srvhp=0639BCE0) detached

C  *** ERROR => CONNECT failed with SQL error '1017'

[dbsloci.c    15709]

B  ***LOG BY2=> sql error 1017   performing CON [dbsh         1246]

B  ***LOG BY0=> ORA-01017: invalid username/password; logon denied [dbsh         1246]

B  ***LOG BY2=> sql error 1017   performing CON [dblink       544]

B  ***LOG BY0=> ORA-01017: invalid username/password; logon denied [dblink       544]

M  ***LOG R19=> ThInit, db_connect ( DB-Connect 000256) [thxxhead.c   1513]

M  in_ThErrHandle: 1

M  *** ERROR => ThInit: db_connect (step 1, th_errno 13, action 3, level 1) [thxxhead.c   11422]

M  ......

I add some files with commands output.

Some suggestion? ...

Best regards.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Jul 02, 2014 at 09:22 PM

    Hello

    First check whether the SAPUSER table is present or not.

    Follow this SAP note 400241 - Problems with ops$ or sapr3 connect to Oracle

    Regards

    RB

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Benhjamin, thanks!, SAP note 400241 resolve my problem, just this part:

      General checks

      • Check whether creating the OPS$ mechanism in accordance with to Note 50088 (WINDOWS) or Note 361641 (UNIX) solves the problem.
      • The table SAPUSER must occur in the system only once and it must be assigned to the user OPS$<sid>ADM. Use the following query to check this:

        SELECT OWNER FROM DBA_TABLES WHERE TABLE_NAME = 'SAPUSER';

        If the system returns an owner <owner> other than OPS$<sid>ADM, you must delete the relevant SAPUSER tables:

        DROP TABLE "<owner>".SAPUSER;

        If the system does not return OPS$<sid>ADM, you must create the table SAPUSER as <sid>adm and enter the password:

        CREATE TABLE "OPS$<sid>ADM".SAPUSER
          (USERID VARCHAR2(256), PASSWD VARCHAR2(256));
        INSERT INTO "OPS$<sid>ADM".SAPUSER VALUES ('<sapowner>', '<password>');
      • This section applies particularly to NT. However, if more than one OPS$ user is also used on UNIX, you must perform the checks there at the same time.

        On NT, the user sapservice<sid> must also be able to access the table SAPUSER. In order to avoid data-consistency problems, it does not make sense to create an additional SAPUSER table that has the same contents. Instead, you should specify a synonym. Use the following command to check whether a suitable synonym exists:

        SELECT OWNER, TABLE_OWNER, TABLE_NAME FROM DBA_SYNONYMS
          WHERE SYNONYM_NAME = 'SAPUSER';

        This call should return either OPS$SAPSERVICE<sid> or PUBLIC as the first value, followed by OPS$<sid>ADM and SAPUSER. If this is not the case, recreate the synonym after you have deleted it:

        If PUBLIC is returned as the first value:

        DROP PUBLIC SYNONYM SAPUSER;

        If another name <name> is returned as the first value:

        DROP SYNONYM "<name>".SAPUSER;

        You can now recreate the synonym (if possible, not PUBLIC):

        CREATE SYNONYM "OPS$SAPSERVICE<sid>".SAPUSER FOR
          "OPS$<sid>ADM".SAPUSER;

        To allow access to the synonym (or the associated table), you must execute a grant. Only the OPS$ user to whom the table belongs has authorization to do this (in this case, OPS$<sid>ADM). Therefore, you must logon using the relevant operating system user (<sid>adm) and execute the following lines:

        CONNECT /
        GRANT SELECT, UPDATE ON SAPUSER TO "OPS$SAPSERVICE<sid>";
      • By default, R3trans uses SAPR3 as the database user. However, you can use the environment variable to specify another database user. This makes particular sense with R/3 6.x, if SAPR3 is replaced by SAP<sid>. In R/3 4.x or lower, if dbs_ora_schema is set to a value other than SAPR3, the connection fails since this user does not exist in the database or in the table SAPUSER. As a result, errors such as ORA-01403 or ORA-01017 occur. Up to and including 4.6D, dbs_ora_schema must not be set in any user environment.

      ORA-01004: default username feature not supported; logon denied

      The OPS$ mechanism is not activated in Oracle. To provide the general OPS$ connection option, proceed as follows:

      • Set the following parameter:

        REMOTE_OS_AUTHENT = TRUE

                 in init<sid>.ora.

      • Restart the database.

      On Windows, you must also set the parameter SQLNET.AUTHENTICATION_SERVICE = (NTS) in sqlnet.ora.ORA-01017: invalid username/password; logon denied

      Message ORA-01017 may occur in step 1a), 1b), or 2). Depending on this, the problem can be solved as follows:

      1a) Log entries:  Logon as OPS$ user to get <sapowner>'s password
                        Connecting as /@<sid> on connection 0 ...
                        *** ERROR => OCI-call 'olog' failed: rc = 1017
                        *** ERROR => CONNECT failed with sql error '1017'
      If you intend to use the standard password for <sapowner> anyway, you can ignore the error message at this point, because the OPS$ mechanism is not required at all and the system connects successfully using <sapowner>/sap. However, note that that BR*TOOLS do require a working OPS$ mechanism when using DB13 to execute.
                 Otherwise, you must ensure that an appropriate OPS$ user is set up. To do this, proceed as follows:

      • Check whether the parameter

        os_authent_prefix = ops$

        is set correctly in init<sid>.ora. If you have to make any changes, restart the database afterwards.
      • On Windows, ensure that the following parameter is set in sqlnet.ora:

        SQLNET.AUTHENTICATION_SERVICES = (NTS)
      • Determine which operating system user <os_user> wants to create the connection. If this is a connection that was initiated from the R/3 System (for example, work process connection, DB13 actions), the system uses the user <sid>adm on UNIX and the user sapservice<sid> on NT. If you manually called the program that executes the connection, the user that you used is decisive. ...

      I read and check step by step this note, later SAP is running! ....

      Thank you! ....

  • avatar image
    Former Member
    Jul 02, 2014 at 05:38 PM

    Hello José,

    Run the note bellow and let me know the results:

    134592 - Importing the SAPDBA role (sapdba_role.sql)

    Best regards,

    Thiago

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi, I apply SAP Note:134592 - Importing the SAPDBA role (sapdba_role.sql)

      Problem persist.

      Thanks.

  • avatar image
    Former Member
    Jul 02, 2014 at 05:41 PM

    Hi José,

    You may also need note:

    50088 - Creating OPS$ users on Windows NT/Oracle

    And adjust the password of your SAPSR3 using brconnect as bellow:

    brconnect -u / -f chpass -o SAPSR3 -p <your_password>

    Regards,

    Thiago

    Add comment
    10|10000 characters needed characters exceeded