cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Oracle SAPSR3 User Always lock when trying to startsap

Go to solution
Former Member

on ‎08-14-2011 10:29 AM

0 Kudos

hi all experts,

im trying to build a copy of SAP System for profing concept of Oracle Data Guard.I'm using SOLMAN 7.0 EHP1 with oracle 10 GR2 on windows 2003.i have primary system (solman ; SID=SOM)

1. i build a copy of SAP System with homogeneous system copy with export-import(this successful). (lets say this system as solman2 ; SID=SOM)

2. next for creating physical standby database in the new copied system. i am performing offline backup from source system,i am using command: 

brrestore -b begnambq.afd  -d disk -r initSOM.sap

all data file below sapdataN directory aleady removed.

when the restore process 91.79% done, error happen

BR0280I BRRESTORE time stamp: 2011-08-13 14.34.01
BR0418I 18 of 22 files restored - 42730.141 MB of 46550.172 MB done
BR0204I Percentage done: 91.79%, estimated end time: 14:46
BR0001I **********************************************____
BR0352I Decompressing F:\ORACLE\SOM\SAPDATA1\TEMP_1\TEMP.DATA1
BR0355I from F:\export\begnambq\TEMP.DATA1.Z ...
BR0278E Command output of 'F:\usr\sap\SOM\SYS\exe\uc\NTAMD64\uncompress -c F:\export\begnambq\TEMP.DATA1.Z > F:\ORACLE\SOM\SAPDATA1\TEMP_1\TEMP.DATA1':
uncompress: F:/export/begnambq/TEMP.DATA1.Z: unexpected end of file
BR0280I BRRESTORE time stamp: 2011-08-13 14.40.24
BR0279E Return code from 'F:\usr\sap\SOM\SYS\exe\uc\NTAMD64\uncompress -c F:\export\begnambq\TEMP.DATA1.Z > F:\ORACLE\SOM\SAPDATA1\TEMP_1\TEMP.DATA1': 1
BR0359E Restore of F:\ORACLE\SOM\SAPDATA1\TEMP_1\TEMP.DATA1 from F:\export\begnambq\TEMP.DATA1.Z failed due to previous errors
BR0406I End of file restore: regnsglg.rsb 2011-08-13 14.40.24
BR0280I BRRESTORE time stamp: 2011-08-13 14.40.24
BR0404I BRRESTORE terminated with errors

i found that temp.data1,sysaux.data1,system.data1, and undo.data1 failed to restore. then i restore it manually one by one using brtools except temp.data1 (it always fail to restore)

brtools > 5 Restore and Recovery > 5 - Restore of Individual of Backup Files

3. Creating control file in solman2, i create the create controlfile scipt with 

startup nomount;<br>
alter database mount;<br>
alter database backup controlfile to trace resetlogs;

and this is the control.sql script look like :

STARTUP NOMOUNT
CREATE CONTROLFILE set DATABASE "SOM" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 255
    MAXLOGMEMBERS 3
    MAXDATAFILES 254
    MAXINSTANCES 50
    MAXLOGHISTORY 1168
LOGFILE
  GROUP 1 (
    'F:\ORACLE\SOM\ORIGLOGA\LOG_G11M1.DBF',
    'F:\ORACLE\SOM\MIRRLOGA\LOG_G11M2.DBF'
  ) SIZE 50M,
  GROUP 2 (
    'F:\ORACLE\SOM\ORIGLOGB\LOG_G12M1.DBF',
    'F:\ORACLE\SOM\MIRRLOGB\LOG_G12M2.DBF'
  ) SIZE 50M,
  GROUP 3 (
    'F:\ORACLE\SOM\ORIGLOGA\LOG_G13M1.DBF',
    'F:\ORACLE\SOM\MIRRLOGA\LOG_G13M2.DBF'
  ) SIZE 50M,
  GROUP 4 (
    'F:\ORACLE\SOM\ORIGLOGB\LOG_G14M1.DBF',
    'F:\ORACLE\SOM\MIRRLOGB\LOG_G14M2.DBF'
  ) SIZE 50M
-- STANDBY LOGFILE
DATAFILE
  'F:\ORACLE\SOM\SAPDATA1\SYSTEM_1\SYSTEM.DATA1',
  'F:\ORACLE\SOM\SAPDATA1\UNDO_1\UNDO.DATA1',
  'F:\ORACLE\SOM\SAPDATA1\SYSAUX_1\SYSAUX.DATA1',
  'F:\ORACLE\SOM\SAPDATA2\SR3_1\SR3.DATA1',
  'F:\ORACLE\SOM\SAPDATA2\SR3_2\SR3.DATA2',
  'F:\ORACLE\SOM\SAPDATA2\SR3_3\SR3.DATA3',
  'F:\ORACLE\SOM\SAPDATA2\SR3_4\SR3.DATA4',
  'F:\ORACLE\SOM\SAPDATA2\SR3_5\SR3.DATA5',
  'F:\ORACLE\SOM\SAPDATA2\SR3_6\SR3.DATA6',
  'F:\ORACLE\SOM\SAPDATA2\SR3_7\SR3.DATA7',
  'F:\ORACLE\SOM\SAPDATA2\SR3_8\SR3.DATA8',
  'F:\ORACLE\SOM\SAPDATA2\SR3_9\SR3.DATA9',
  'F:\ORACLE\SOM\SAPDATA2\SR3_10\SR3.DATA10',
  'F:\ORACLE\SOM\SAPDATA3\SR3700_1\SR3700.DATA1',
  'F:\ORACLE\SOM\SAPDATA3\SR3700_2\SR3700.DATA2',
  'F:\ORACLE\SOM\SAPDATA3\SR3700_3\SR3700.DATA3',
  'F:\ORACLE\SOM\SAPDATA3\SR3700_4\SR3700.DATA4',
  'F:\ORACLE\SOM\SAPDATA4\SR3USR_1\SR3USR.DATA1',
  'F:\ORACLE\SOM\SAPDATA4\SR3DB_1\SR3DB.DATA1',
  'F:\ORACLE\SOM\SAPDATA4\SR3DB_2\SR3DB.DATA2',
  'F:\ORACLE\SOM\SAPDATA4\SR3DB_3\SR3DB.DATA3'
CHARACTER SET UTF8
;

i rename the old control file

SQL>@f:\oracle\SOM\control.sql

the controlfile successfully created.

4. mounting and open the database

i know this is not part for creating standby system,

i just want to makes sure the backup file restored is working

SQL>startup mount;
SQL>alter database open resetlogs;

5. Trying to start sap

i try to start sap from sapmmc, but the dispatcher is yellowed, it say not connected to the message server,

i found error in syslog on sapmmc says:

SAP-Basis System: Initialization DB-Connect Failed, Return Code 000256

i check the the database connection via R3trans -x

Result:

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

then i write more detail to the log files

R3trans -xv

this is the log from trans.log

(below post)

Show replies
Show replies
Answer

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
‎08-14-2011 10:30 AM
0 Kudos

i checked in dba_users table,, SAPSR3 user always locekd and everytime i unlocked it, it always locked when i try to sartsap.

i realized the password for SAPSR3 in solman2 is same with solman, is this causing SAPSR3 user always locked due to incorrect logon?

ho do i resolve this so the sap can started normally?

Please Help

Show replies
Show replies
Former Member
‎08-14-2011 10:32 AM
0 Kudos

this is from trans.log<br><br><br>

4 ETW000 R3trans version 6.14 (release 700 - 14.02.08 - 14:55:00).
4 ETW000 unicode enabled version
4 ETW000 ===============================================
4 ETW000 
4 ETW000 date&time   : 14.08.2011 - 14:10:40
4 ETW000 control file: <no ctrlfile>
4 ETW000 R3trans was called as follows: R3trans -xv
4 ETW000  trace at level 2 opened for a given file pointer
4 ETW000  [dev trc     ,00000]  Sun Aug 14 14:10:42 2011                               0  0.000000
4 ETW000  [dev trc     ,00000]  db_con_init called                                     0  0.000000
4 ETW000  [dev trc     ,00000]  create_con (con_name=R/3)                              0  0.000000
4 ETW000  [dev trc     ,00000]  Loading DB library 'dboraslib.dll' ...                 0  0.000000
4 ETW000  [dev trc     ,00000]  load shared library (dboraslib.dll), hdl 0         17605  0.017605
4 ETW000  [dev trc     ,00000]      using "F:\usr\sap\SOM\SYS\exe\uc\NTAMD64\dboraslib.dll"
4 ETW000                                                                              58  0.017663
4 ETW000  [dev trc     ,00000]  Library 'dboraslib.dll' loaded                        28  0.017691
4 ETW000  [dev trc     ,00000]  function DbSlExpFuns loaded from library dboraslib.dll
4 ETW000                                                                              79  0.017770
4 ETW000  [dev trc     ,00000]  Version of 'dboraslib.dll' is "700.08", patchlevel (0.144)
4 ETW000                                                                            1734  0.019504
4 ETW000  [dev trc     ,00000]  function dsql_db_init loaded from library dboraslib.dll
4 ETW000                                                                              48  0.019552
4 ETW000  [dev trc     ,00000]  function dbdd_exp_funs loaded from library dboraslib.dll
4 ETW000                                                                             166  0.019718
4 ETW000  [dev trc     ,00000]  New connection 0 created                             105  0.019823
4 ETW000  [dev trc     ,00000]  0: name = R/3, con_id = -000000001 state = DISCONNECTED, perm = YES, reco = NO , timeout = 000, con_max = 255, con_opt = 255, occ = NO 
4 ETW000                                                                              74  0.019897
4 ETW000  [dev trc     ,00000]  db_con_connect (con_name=R/3)                         73  0.019970
4 ETW000  [dev trc     ,00000]  find_con_by_name found the following connection for reuse:
4 ETW000                                                                              47  0.020017
4 ETW000  [dev trc     ,00000]  0: name = R/3, con_id = 000000000 state = DISCONNECTED, perm = YES, reco = NO , timeout = 000, con_max = 255, con_opt = 255, occ = NO 
4 ETW000                                                                              58  0.020075
4 ETW000  [dev trc     ,00000]  CLIENT_ORACLE_HOME is not set as environment variable or 
4 ETW000                        DIR_CLIENT_ORAHOME is not set as profile parameter.
4 ETW000                          assuming using instant client with unspecified location.
4 ETW000                                                                            1857  0.021932
4 ETW000  [dev trc     ,00000]  Oracle Client Version: '10.2.0.2.0'                24707  0.046639
4 ETW000  [dev trc     ,00000]  -->oci_initialize (con_hdl=0)                         40  0.046679
4 ETW000  [dev trc     ,00000]  Client NLS settings: AMERICAN_AMERICA.UTF8          8471  0.055150
4 ETW000  [dev trc     ,00000]  Logon as OPS$-user to get SAPSR3's password           27  0.055177
4 ETW000  [dev trc     ,00000]  Connecting as /@SOM on connection 0 (nls_hdl 0) ... (dbsl 700 250407)
4 ETW000                                                                              65  0.055242
4 ETW000  [dev trc     ,00000]  Nls CharacterSet                 NationalCharSet              C      EnvHp      ErrHp ErrHpBatch
4 ETW000                                                                              61  0.055303
4 ETW000  [dev trc     ,00000]    0 UTF8                                                      1 0000000006164090 00000000002EA510 00000000002EFCD8
4 ETW000                                                                             175  0.055478
4 ETW000  [dev trc     ,00000]  Allocating service context handle for con_hdl=0       72  0.055550
4 ETW000  [dev trc     ,00000]  Allocating server context handle                      20  0.055570
4 ETW000  [dev trc     ,00000]  Attaching to DB Server SOM (con_hdl=0,svchp=00000000002EFB98,srvhp=00000000061830E8)
4 ETW000                                                                             127  0.055697
4 ETW000  [dev trc     ,00000]  Assigning server context 00000000061830E8 to service context 00000000002EFB98
4 ETW000                                                                           33026  0.088723
4 ETW000  [dev trc     ,00000]  Allocating user session handle                       191  0.088914
4 ETW000  [dev trc     ,00000]  Starting user session (con_hdl=0,svchp=00000000002EFB98,srvhp=00000000061830E8,usrhp=00000000002EAD28)
4 ETW000                                                                              40  0.088954
4 ETW000  [dboci.c     ,00000]  *** ERROR => OCI-call 'OCISessionBegin' failed with rc=1017
4 ETW000                                                                           41794  0.130748
4 ETW000  [dbsloci.    ,00000]  *** ERROR => CONNECT failed with sql error '1017'
4 ETW000                                                                              55  0.130803
4 ETW000  [dev trc     ,00000]     set_ocica() -> OCI or SQL return code 1017         48  0.130851
4 ETW000  [dev trc     ,00000]  Try to connect with default password                  88  0.130939
4 ETW000  [dev trc     ,00000]  Connecting as SAPSR3/<pwd>@SOM on connection 0 (nls_hdl 0) ... (dbsl 700 250407)
4 ETW000                                                                              34  0.130973
4 ETW000  [dev trc     ,00000]  Nls CharacterSet                 NationalCharSet              C      EnvHp      ErrHp ErrHpBatch
4 ETW000                                                                              38  0.131011
4 ETW000  [dev trc     ,00000]    0 UTF8                                                      1 0000000006164090 00000000002EA510 00000000002EFCD8
4 ETW000                                                                              31  0.131042
4 ETW000  [dev trc     ,00000]  Assigning username to user session 00000000002EAD28
4 ETW000                                                                              21  0.131063
4 ETW000  [dev trc     ,00000]  Assigning password to user session 00000000002EAD28
4 ETW000                                                                              70  0.131133
4 ETW000  [dev trc     ,00000]  Starting user session (con_hdl=0,svchp=00000000002EFB98,srvhp=00000000061830E8,usrhp=00000000002EAD28)
4 ETW000                                                                              31  0.131164
4 ETW000  [dboci.c     ,00000]  *** ERROR => OCI-call 'OCISessionBegin' failed with rc=28000
4 ETW000                                                                            4104  0.135268
4 ETW000  [dev trc     ,00000]  server_detach(con_hdl=0,stale=1,svrhp=00000000061830E8)
4 ETW000                                                                              23  0.135291
4 ETW000  [dev trc     ,00000]  Detaching from DB Server (con_hdl=0,svchp=00000000002EFB98,srvhp=00000000061830E8)
4 ETW000                                                                              38  0.135329
4 ETW000  [dev trc     ,00000]  Deallocating server context handle 00000000061830E8
4 ETW000                                                                            1099  0.136428
4 ETW000  [dbsloci.    ,00000]  *** ERROR => CONNECT failed with sql error '28000'
4 ETW000                                                                              57  0.136485
4 ETW000  [dev trc     ,00000]     set_ocica() -> OCI or SQL return code 28000        15  0.136500
4 ETW000  [dblink      ,00431]  ***LOG BY2=>sql error 28000  performing CON [dblink#5 @ 431]
4 ETW000                                                                             248  0.136748
4 ETW000  [dblink      ,00431]  ***LOG BY0=>ORA-28000: the account is locked [dblink#5 @ 431]
4 ETW000                                                                              22  0.136770
2EETW169 no connect possible: "DBMS = ORACLE                           --- dbs_ora_tnsname = 'SOM'"

former_member213250
Active Participant
‎08-14-2011 11:52 AM
0 Kudos

Hello there

Regards to the frequent user locks, and based on errors from translog file..

I would say refering to couple of SAP notes will be helpful to solve this issue.

951167 ORA-28000: the account is locked

400241 Problems with ops$ or sapr3 connect to Oracle

Best Regards

Venkat

Former Member
‎08-14-2011 4:05 PM
0 Kudos

Helo Venkat,

Thanks for you reply, i already seen mentioned note, but it seems doesn't clear on me.

can you tell me the general step for resolving this issue please.

Thanks & Regards,

rio

Former Member
‎08-14-2011 6:29 PM
0 Kudos

Hi Rio,

I assume that the SAP server is member of a domain. At the first stage, you should change create ops$ user, by using the statements, below;

CREATE USER "OPS$<DOMAIN>\<SID>ADM" DEFAULT TABLESPACE SYSTEM TEMPORARY TABLESPACE PSAPTEMP IDENTIFIED EXTERNALLY;
CREATE USER "OPS$<DOMAIN>\SAPSERVICE<SID>" DEFAULT TABLESPACE SYSTEM TEMPORARY TABLESPACE PSAPTEMP IDENTIFIED EXTERNALLY;
GRANT CONNECT, RESOURCE, SAPDBA TO "OPS$<DOMAIN>\<SID>ADM";
GRANT CONNECT, RESOURCE, SAPDBA TO "OPS$<DOMAIN>\SAPSERVICE<SID>";
CREATE TABLE "OPS$<DOMAIN>\<SID>ADM".SAPUSER (USERID VARCHAR2(256), PASSWD VARCHAR2(256));
INSERT INTO "OPS$<DOMAIN>\<SID>ADM".SAPUSER VALUES ('SAPSR3', '<Password>');
CREATE SYNONYM "OPS$<DOMAIN>\SAPSERVICE<SID>".SAPUSER FOR "OPS$<DOMAIN>\<SID>ADM".SAPUSER;
CONNECT /
GRANT SELECT, UPDATE ON "OPS$<DOMAIN>\<SID>ADM".SAPUSER TO "OPS$<DOMAIN>\SAPSERVICE<SID>";
CONNECT /as sysdba
GRANT CONNECT, RESOURCE TO "OPS$<DOMAIN>\SAPSERVICE<SID>";
GRANT CONNECT, RESOURCE TO "OPS$<DOMAIN>\<SID>ADM";

Please note that, if the system is not runnnig under a domain, you should replace "<DOMAIN>" with "<HOSTNAME>" in the SQL statements.

At the second step, you should change the password of the Oracle users. To do so, execute the statements on SQLplus. 

select username from dba_users order by username;

Then, execute the command, below on command line for each ORACLE user;

brconnect -c -u / -f chpass -o <Oracle User> -p <Password>

Please note that <Password> must be the same as in the sql statements at the first step.

As a last step, execute the command, in order to avoid againts wrong password, entries;

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED;

At the end of these steps, you will be able to enable OPS$ mechanism and set the correct passwords on the database. I hope that everything is clear.

Best regards,

Orkun Gedik

fidel_vales
Employee
Employee
‎08-14-2011 6:36 PM
0 Kudos

hi,

This is quite difficult to read without formatting.

Nevertheless, it looks like you have not read it (nor the notes explaining how SAP connects to Oracle)

1.- SAP tries to connect as "OPS$" user in order to retrieve the password for SAPSR3. You can see it on the trace file

4 ETW000 [dev trc ,00000] Logon as OPS$-user to get SAPSR3's password

This action failed:

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

That means you DO NOT HAVE the "OPS$" user properly configured

2.- As the previous action did not work then SAP tries to connect with the default password:

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

In this case it fails with the error:

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

But, I'm quite sure you are not using the default password, therefore, as it has failed several times before with the wrong password, Oracle locks the user.

can you tell me the general step for resolving this issue please.

If you are not able to do it reading the notes, then I do not think you should be administering the mentioned system.

Please, check the answer already provided and all the notes

Former Member
‎08-25-2011 3:05 PM
0 Kudos

Hi Videl,

Im Sorry . I do trying to write it formated, but it always wretch away avery time i preview it, thats why i write in separate reply.

thanks for you advise, hope it will encourage me to understand every unclearly notes i've read.

Hi Orkun,

Thank you so much for clearly explanation and step, it solved my problems

anyway, why dont you suggest me to using oradbuser.sql script which attached in notes 50088?

Former Member
‎08-25-2011 7:46 PM
0 Kudos

Hi,

>> why dont you suggest me to using oradbuser.sql script which attached in notes 50088?

Of course, I do. But, I prefer to do this configuration, manually

Best regards,

Orkun Gedik

Former Member
‎08-27-2011 4:51 PM
0 Kudos

Hi Orkun,

formerly i've done a proof of concept of migrating SAP R/3 HP-UX-oracle to Windows-SQL Server. This action done by MS people,

he try to do export with R3Load the SAP System in HP-UX system FROM Windows based Server (remote export), for reason is the new windows server machine has a better hardware technology rather than the HP-UX server. i dont know if this things could possibly done.

He asked me to create window ops$sidadm user in UX machine by executing oradbuser.sql with specified (NT) option, then there so many error ocured in ST22 (the script executed while the system is running), i initate to restart the service, but it cannot start after i stopped it. trans.log file resulte that some user are locked, i check the dba_users table that ops$<sid>adm of UX server is missing, it replaced with domain\ops$<sid>adm of windows server users. so i ran back the oradbuser.sql script for UX, than it comes appear again.

i think there is a statement in oradbuser.sql which drop old ops$<sid>user.sapuser table. thats why the error happens and the SAPR3 user are locked.

am i right?

CMIIW

Regards,

Rio

Former Member
‎08-27-2011 11:33 PM
0 Kudos

Hi Rio,

This is a possible reason. This because, a SAP work process try to connect with OPS$ mechanism, at the first step and read the password from SAPUSER table. In your case, because of the SAPUSER table does not exist with the correct owner, the SAP work processes could not be able to connect to the database and try to logon with the standard password by using SAPSR3 user. At the end of it, after the many failed logon attempts, the SAPSR3 user is getting locked and you faced with this situation.

Check the document, below;

http://help.sap.com/saphelp_nw70ehp2/helpdata/EN/fa/bc883989676778e10000000a11402f/content.htm

In short, your assumption is correct.

Best regards,

Orkun Gedik

Former Member
‎08-29-2011 3:31 PM
0 Kudos

verywell gedik,

Thanks for your helpfull explanation.

This case is solved.

Thanks for all of you who contribute in this thread.

Answers (1)

Answers (1)

Former Member
‎08-14-2011 11:05 AM
0 Kudos

Use brtools to change pwd for sapsr3 and check listner is start or not

Show replies
Show replies
Ask a Question