why is ORA-01017 after 'connect /@SID' ?

[carlos_zhang3]

Dear Guru ,

I am doing the Database Import Step for our SAP4.7 system .

Everything is okay , but when i use below command to login the database , it showed me the error message :

SQL> connect /@T19 ;

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE

Does someone can help me solve this problem ?

Thanks .

Best Regards,

Carlos Zhang

[Former Member]

Hi Carlos,

For better understanding(on OPS$ Mechanism) read SAP Note 400241 and SAP Note 50088.

Regards,

Nick Loy

[Former Member]

Hi,

Mainly this error occurs in SAP system, when there is a problem in Oracle database OPS$ authentication mechanism.

Check if the correct operating system username entries are there in database.

Things to Check:

1. Check that environment variable ORACLE_SID=<DBSID> is set properly.

2. Check if remote_os_authent=true is set in your oracle initialization/spfile.

3. Check if OPS$<SID>adm user is created in the database.

SQL>select username,account_status from dba_users;
USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
OPS$<SID>ADM                     OPEN
OPS$ORA<SID>                     OPEN
OPS$SAPSERVICE<SID>              OPEN
SYS                            OPEN
SYSTEM                         OPEN
SAPSR3                         OPEN
OUTLN                          LOCKED
TSMSYS                         EXPIRED & LOCKED
DIP                            EXPIRED & LOCKED
ORACLE_OCM                     EXPIRED & LOCKED
DBSNMP                         EXPIRED & LOCKED

11 rows selected.

4. Check that you are using correct username and password.

Below SAP Notes will help you:

[361641 - Creating OPS$ users on UNIX|https://service.sap.com/sap/support/notes/361641]

[50088 - Creating OPS$ users on Windows NT-Oracle|https://service.sap.com/sap/support/notes/50088]

[400241 - Problems with ops$ or sapr3 connect to Oracle|https://service.sap.com/sap/support/notes/400241]

Regards.

Rajesh Narkhede

[carlos_zhang3]

Hi Rajesh ,

Thanks .

I checked the ORACLE_SID , oracle init profile and OPS$ user .

Everything seems fine . Please see our user status :

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
OUTLN                          OPEN
SYS                            OPEN
SYSTEM                         OPEN
FH_03_OCA_NET\SAPSERVICET19    OPEN
SAPT19                         OPEN
FH_03_OCA_NET\T19ADM           OPEN
DBSNMP                         LOCKED
TSMSYS                         EXPIRED & LOCKED
DIP                            EXPIRED & LOCKED
ORACLE_OCM                     EXPIRED & LOCKED

10 rows selected.

SQL>

And when i try to connect with /@<SID> , it failed and reported the same error message .

SQL>connect /@T19
ERROR:
ORA-01017: invalid username/password; logon denied

After i switched to user/pass@<SID> mode , it works .

SQL>CONNECT SAPT19/password@T19
Connected.
SQL>

And i have checked the note "400241" ....

So , is it the problem on OPS$ user ? we are using windows domain ..

Many thanks .

Best Regards,

Carlos Zhang

Edited by: Carlos Zhang on Apr 19, 2010 6:44 AM

[Former Member]

Hi,

If you see carefuly, OPS$ORA<SID> user is missing...

Just create the users as per the given note and try...

ORA<SID> should be a member of ORA_DBA group...

If you login as <SID>adm user or SAPService<SID> user, you would be able to login to oracle as "sqlplus / as sysdba"

Regards.

Rajesh Narkhede

[carlos_zhang3]

Hi Rajesh ,

We are using windows 2003 as our SAP4.7 OS , So , Is it the OPS$ORA<SID> user not so important for us ?

Because i checked other SAP system with same configuration is without this issue ....

And the user i listed on that server are :

SQL> select username , account_status from dba_users;

USERNAME                       ACCOUNT_STATUS
------------------------------ --------------------------------
OUTLN                          OPEN
SYS                            OPEN
SYSTEM                         OPEN
SAPD19                         OPEN
OPS$SDE192\D19ADM              OPEN
OPS$SDE192\SAPSERVICED19       OPEN
DBSNMP                         LOCKED
TSMSYS                         EXPIRED & LOCKED
DIP                            EXPIRED & LOCKED
ORACLE_OCM                     EXPIRED & LOCKED

10 rows selected.

SQL>

Thanks .

Best Regards,

Carlos Zhang

[Former Member]

Hi Carlos,

By which windows user you are trying to logon to Oracle ?

Is that user a member of ORA_DBA group ?

Please go through the below note carefuly..

[Note 480266 - Problems with the SYSDBA/SYSOPER/INTERNAL connect|https://service.sap.com/sap/support/notes/480266]

[Note 562863 - FAQ: Logon mechanisms|https://service.sap.com/sap/support/notes/562863]

Regards.

Rajesh Narkhede

Edited by: Rajesh Narkhede on Apr 19, 2010 11:00 AM

[former_member204746]

try this:

connect /@T19 as sysdba

[kishore_soma]

Dear Carlos,

the OS authentication is not happening. Pls logon to database and check for the table SAP<SID>/sapuser,

Or try to insert correct password of SAP<SID> (schema user) in this table.

- delete the existing entry and insert the user id sap<SID> and password of this user.

Then try connecting it will connect.

Regards,

Kishore

[Former Member]

Hi Carlos,

You can use the below command to change the password of DB User.

It will take care of where that new password need to be updated in the database correctly...

Run this command as ora<sid> user:

# brconnect -f chpass -o SAPD19 -p <new_password>

Regards.

Rajesh Narkhede

[kishore_soma]

Dear Carlos,

This is purely, OPS$ authentication problem. So please try the below;

Logon wit <SID>adm

>sqlplus "/as sysdba"

check for OPS$SDE192\D19ADM.sapuser table

>select * from 'OPS$SDE192\D19ADM'.sapuser;

then if there is any entry, delete the entry

>delete from 'OPS$SDE192\D19ADM'.sapuser;

then reset schema password SAP<SID>

>alter user <username> identified by sap;

here sap is the pasword, insert the sap password in to OPS$SDE192\D19ADM.sapuser table

>insert into OPS$SDE192\D19ADM.sapuser values('SAP<SID>','sap');

Then try connecting using sqlplus /

this should work.

Pls update after trying

kishore

[Former Member]

Dear Carlos,

Yes right OPS$ user is not important only if your password for SCHEMA User is default i.e. 'sap'.

If you want to secure your schema user you have to configure OPS$ Mechanisum, to do so Just download CHDBPASS.SQL script from

service marketplace > Downloads> database patches--> oracle other --> scripts

& run it as instructed in screept itself.

Regards,

Darshan