ORA-942 when accessing table SAPUSER Error on WP

[former_member309601]

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 

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 

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 

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 

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 

M  ......

I add some files with commands output.

Some suggestion? ...

Best regards.

[Reagan · Accepted solution]

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

[former_member309601 · Accepted solution]

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

[thiago_cavalheiro]

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

[former_member309601]

Hi, first thing I do was change database password using brtools. It is same actions that command.

Thanks.

[thiago_cavalheiro]

Hi José,

Thanks for that. Did you run note 50088? If not, please do so and then update the password with BR*Tools again. This will adjust the SAPUSER table and make sure the password is correct in all levels.

Regards,

Thiago

[former_member309601]

Hi, I apply note 50088, but problem persist, later I update brtools password again.

Same problem ....

Thanks!

[thiago_cavalheiro]

Hello José,

Run the note bellow and let me know the results:

134592 - Importing the SAPDBA role (sapdba_role.sql)

Best regards,

Thiago

[former_member309601]

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

Problem persist.

Thanks.