Oracle doesn't start with startsap

[Former Member]

Hi,

Today we updated the oracle database from 10.2.0.2 to 10.2.0.4 and then we applied the 7592346 CPU, we have a Portal 7.0 with HPUX, when we try to start the database with startsap we saw an error and we cant start the database:

4 ETW000 54 0.199270

4 ETW000 [dev trc ,00000] ALTER SESSION SET NLS_SORT = BINARY

4 ETW000 53 0.199323

4 ETW000 [dev trc ,00000] -->oci_prepare_stmt(con_hdl=0, len=0, stmth_p=0x600000000105e6e0)

4 ETW000 530 0.199853

4 ETW000 [dev trc ,00000] SELECT USERID, PASSWD FROM SAPUSER WHERE USERID IN (:A0, :A1)

4 ETW000 49 0.199902

4 ETW000 [dev trc ,00000] OCI-call failed with -1=OCI_ERROR 1335 0.201237

4 ETW000 [dev trc ,00000] SQL error 942: 'ORA-00942: table or view does not exist'

4 ETW000 60 0.201297

4 ETW000 [dbsloci. ,00000] *** ERROR => ORA-942 when accessing table SAPUSER

4 ETW000 145 0.201442

4 ETW000 [dev trc ,00000] set_ocica() -> OCI or SQL return code 942 32 0.201474

4 ETW000 [dev trc ,00000] Disconnecting from connection 0 ... 123 0.201597

4 ETW000 [dev trc ,00000] Rolling back transaction ... 70 0.201667

4 ETW000 [dev trc ,00000] Closing user session (con_hdl=0,svchp=0x6000000001051e08,usrhp=0x60000000010d8390)

4 ETW000 319 0.201986

4 ETW000 [dev trc ,00000] Now I'm disconnected from ORACLE 780 0.202766

4 ETW000 [dev trc ,00000] Try to connect with default password 43 0.202809

4 ETW000 [dev trc ,00000] Connecting as SAPR3/<pwd>@DBP on connection 0 (nls_hdl 0) ... (dbsl 700 030508)

4 ETW000 53 0.202862

4 ETW000 [dev trc ,00000] Nls CharacterSet NationalCharSet C EnvHp ErrHp ErrHpBatch

4 ETW000 193 0.203055

4 ETW000 [dev trc ,00000] 0 UTF8 1 0x6000000001039cb0 0x6000000001041760 0x6000000001051ed8

4 ETW000 51 0.203106

4 ETW000 [dev trc ,00000] Assigning username to user session 0x60000000010d8390

4 ETW000 46 0.203152

4 ETW000 [dev trc ,00000] Assigning password to user session 0x60000000010d8390

4 ETW000 68 0.203220

4 ETW000 [dev trc ,00000] Starting user session (con_hdl=0,svchp=0x6000000001051e08,srvhp=0x6000000001054118,usrhp=0x60000000010d8390)

4 ETW000 314 0.203534

*4 ETW000 [dboci.c ,00000] *** ERROR => OCI-call 'OCISessionBegin' failed with rc=1017*

4 ETW000 3076 0.206610

*4 ETW000 [dbsloci. ,00000] *** ERROR => CONNECT failed with sql error '1017'*

4 ETW000 49 0.206659

4 ETW000 [dev trc ,00000] set_ocica() -> OCI or SQL return code 1017 30 0.206689

*4 ETW000 [dblink ,00431] **LOG BY2=>sql error 1017 performing CON [dblink#5 @ 431]

4 ETW000 734 0.207423

*4 ETW000 [dblink ,00431] **LOG BY0=>ORA-01017: invalid username/password; logon denied [dblink#5 @ 431]

4 ETW000 48 0.207471

2EETW169 no connect possible: "DBMS = ORACLE --- dbs_ora_tnsname = 'DBP'"

trans.log: END

Do you know what happen?

In fact when we use either startsap or stopsap it shows inmmediatly a message that says that it couldn't find SID_SID_INTANCE_HOSTNAME_HOSTNAME (ABC_ABC_JC01_SAP01_SAP01) in the profile path...

Edited by: Jaime Yanez on Mar 26, 2009 2:46 AM

[Former Member · Accepted solution]

Hi,

Please check the bellow things...

1. Listenert is up: execute the following in command prompt

lanrctl status

2. Startup the database manually: execute the following in command prompt

sqlplus /nolog

SQL> conn sys as sys dba

SQL> startup

SQL> exit;

3. Now check the database connection: execute the following in command prompt

R3trans -d

4. If the return code from R2trans command is "0" then it is ok and from the next time startsap will start the database. If this fail...

5. Check the oracle user id "SYSTEM" is working and not locked: execute following command

sqlplus /nolog

SQL> conn system/manager ---> manager is the default password for system user

Check it is connecting or not. If it is not connecting then take the password backup of system user and set the password manager.

sqlplus /nolog

SQL> conn sys as sysdba

SQL> select USERNAME,PASSWORD from dba_users where username='SYSTEM'; --> for system password backup

SQL> alter user SYSTEM indentified by manager; --> it will reset the system password

SQL> exit;

R3trans -d

Check the return code 0 or not. If the returncode is not 0 then reset the system password back

sqlplus /nolog

SQL> conn sys as sysdba

SQL> alter user SYSTEM indentified by values '<password got from select command before changing>';

SQL> exit;

6. Check the table SAPUSER is exist and have the proper entries

sqlplus /nolog

SQL> conn sys as sysdba

SQL> select owner, table_name from dba_tables where table_name='SAPUSER';

SQL> select * from <owner name found in previous query>.SAPUSER;

If the entries is not proper then insert data in the table using BR*Tool.

Thanks

Manoj

[former_member204746 · Accepted solution]

try to run oradbusrl.sql

try to run sapdba_role.sql

then, change passwords with brconnect

read SAP note 400241.

good luck.

[Former Member · Accepted solution]

Hi,

We don't know what happen, but we didn't do it anything but the system works fine. Manoj we checked user system and everything was fine. In a few days we have to restart all the instance for backup, we hope it works

Thanks!!!

[former_member222931 · Accepted solution]

Hi Jamie,

I am facing the same problem like you also whereby system works fine n etc. The only thing is R3trans -d is giving error (0012).

Did you encounter any issue after the restart? Is your Oracle listener password protected?

Hope to hear from you.

Rgds,

Sharifah

[kishore_kumar59]

Dear Jaime,

AS per the log you have updated, the problem is with the OPS$ user login mechanism.

Try this;

Logon to SQLPLUS as sys users

sqlplus "/as sysdba"

reset the password of the db schema user (for ECC 6.0 it is SAPSR3, for ECC 5.0 and below, it is SAP<SID>, check it in environment variable)

Alter user <schema user> identified by sap;

Update the SAPUSER table with this password;

INSERT INTO "OPS$<sid>ADM".SAPUSER VALUES ('<schema owner>', '<password>');

Then try R3trans -d command from command prompt, it should solve

Else go through the note - 400241

Regards,

Kishore

[Former Member]

Hi,

The issue was solved, we changed all password from brtools to sap as you say and it works fine, additionally tnsnames.ora has an error because it indicated a bad entry with the listener, we erased that and it works fine

[audunlea_hansen]

Hi!

I normally run into this problem when I clone the DB.

Check the env-parameter 'dbs_ora_schema' are set to the schema-name of the table owner. In my systems this parameter are set in dbenv*-files.

Check this parameter are correct and the actual user not are locked in the database.

Test connection with 'R3trans -d -v' (as <sid>adm-user) and look at the logfile it generates in the dir you run the command from.

Regards

Audun

[Former Member]

Hi

Check out the environmental settings for Oracle with the earlier settings.( before the upgrade )

[former_member220071]

Hi ,

>4 ETW000 dev trc ,00000 SQL error 942: 'ORA-00942: table or view does not exist'

Check SAP Note 662644

Abhijeet

[Former Member]

Hello Jaime,

The error logs says:

"OCI-call failed with -1=OCI_ERROR 1335 0.201237

4 ETW000 dev trc ,00000 SQL error 942: 'ORA-00942: table or view does not exist'

4 ETW000 60 0.201297

*4 ETW000 http://dbsloci. ,00000 *** ERROR => ORA-942 when accessing table SAPUSER*

4 ETW000 145 0.201442"

Please check if SAPUSER table exists in the database or not.

Of not you will have to create it.

Refer to Note 400241,This will help you how to solve your problem

Rohit

[Former Member]

Hi,

The note that you reffer to us is not for JAVA applications, it's only for ABAP

Important: This note does NOT provide information about configuring the OPS$ user for Java-based applications.

Edited by: Jaime Yanez on Mar 26, 2009 1:54 PM

[Former Member]

Hi,

It looks like either user (with which you are starting DB) does not have authorization or table does not exists.

Manoj

[markus_doehr2]

Did you run all the upgrade scripts after you updated the database software? Just installing the software is not enough...

can you manually start the database?

Markus

[Former Member]

Hi,

The database can start with sqlplus without problems, we applied all post installations steps