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

[Former Member]

ORA-01017: invalid username/password; logon denied, during the backup it is taking <sysadm> user having sap_all profile. its neither taking SYSTEM nor $OPS user. Gurus please help to resolve this issue ASAP.

[Reagan · Accepted solution]

Hello

Can you provide the complete backup log please ?

Also provide the output of this SQL query.

select username,ACCOUNT_STATUS from dba_users;

Regards

RB

[Former Member · Accepted solution]

Dear RB.

Backup log as follows:

BR0051I BRBACKUP 7.00 (52)

BR0055I Start of database backup: belhfbjk.tib 2013-05-27 14.41.12

BR0484I BRBACKUP log file: /oracle/Q01/sapbackup/belhfbjk.tib

BR0101I Parameters

Name                           Value

oracle_sid                     Q01

oracle_home                    /oracle/Q01/102_64

oracle_profile                 /oracle/Q01/102_64/dbs/initQ01.ora

sapdata_home                   /oracle/Q01

sap_profile                    /oracle/Q01/102_64/dbs/initQ01.sap

backup_dev_type                tape

tape_copy_cmd                  dd

cpio_flags                     -ovcB

cpio_in_flags                  -iuvcB

dd_flags                       obs=64k bs=64k

dd_in_flags                    ibs=64k bs=64k

rewind                         mt -f $ rewind

rewind_offline                 mt -f $ offline

tape_address                   /dev/nst0

tape_address_rew               /dev/st0

volume_backup                  SCRATCH

expir_period                   30

tape_use_count                 100

exec_parallel                  0

system_info                    q01adm/oraq01 development Linux 2.6.18-274.el5xen #1 SMP Fri Jul 8 17:45:44 EDT 2011 x86_64

make_info                      linuxx86_64 OCI_102 Mar  4 2011

command_line                   brbackup -u / -jid TAPIN20130527144112 -i force -c force -n 1 -v SCRATCH

BR0280I BRBACKUP time stamp: 2013-05-27 14.41.12

BR0301W SQL error -1017 at location BrDbConnect-2, SQL statement:

'CONNECT /'

ORA-01017: invalid username/password; logon denied

BR0310W Connect to database instance Q01 failed

BR0205I Backup volume initialization

BR0058I BRBACKUP action ID: belhfbjk

BR0059I BRBACKUP function ID: tib

BR0102I Following backup device will be used: /dev/nst0

BR0131I All volumes get the name SCRATCH

BR0129I Force option set - no label checking during initialization

BR0134I Unattended mode with 'force' active - no operator confirmation allowed

BR0207I Volume in device: /dev/nst0 will be initialized with name: SCRATCH

BR0280I BRBACKUP time stamp: 2013-05-27 14.41.12

BR0226I Rewinding tape volume in device /dev/st0 ...

BR0202I Copying /oracle/Q01/sapbackup/.tape.hdr0

BR0203I to /dev/nst0 ...

BR0209I Volume in device /dev/nst0 has name SCRATCH

BR0280I BRBACKUP time stamp: 2013-05-27 14.41.15

BR0226I Rewinding tape volume in device /dev/st0 ...

BR0280I BRBACKUP time stamp: 2013-05-27 14.41.21

BR0301E SQL error -1017 at location BrDbConnect-2, SQL statement:

'CONNECT /'

ORA-01017: invalid username/password; logon denied

BR0310E Connect to database instance Q01 failed

BR0206I End of backup volume initialization

BR0280I BRBACKUP time stamp: 2013-05-27 14.41.21

BR0054I BRBACKUP terminated with errors

Sql script:

SQL> select username ACCOUNT_STATUS from dba_users;

ACCOUNT_STATUS

------------------------------

OPS$SAPSERVICEP01

OPS$ORAP01

SYS

SYSTEM

OPS$Q01ADM

SAPP01

OUTLN

TSMSYS

DIP

ORACLE_OCM

DBSNMP

11 rows selected.

Please Help.

Thanks & Regards

Khan

[Reagan · Accepted solution]

Hello

Drop the Production OPS$ users from the SQL

drop user OPS$SAPSERVICEP01 cascade;

drop user OPS$ORAP01 cascade;

Use the script attached in the below SAP Note to recreate the OPS$ users.

Note 50088 - Creating OPS$ users on Windows NT/Oracle

Upload it to the server at /oracle/Q01 and change the ownership of the file.

Switch to oraq01 and execute the script as follows.

su - oraq01

sqlplus /NOLOG @ORADBUSR.SQL SAPP01 UNIX Q01 x

Check whether the owner of the table SAPUSER is OPS$Q01ADM  from the SQL.

SELECT OWNER FROM DBA_TABLES WHERE TABLE_NAME = 'SAPUSER';

Expected result : OPS$Q01ADM

Also make sure that the password is present (encrypted) in the SAPUSER table.

select * from OPS$Q01ADM.SAPUSER;

If there is no entry then update the password for SAPP01 user as follows.

Switch to oraq01

su - oraq01

brconnect -u / -f chpass -o SAPP01 -p P@ssw0rd

NB: Replace P@ssw0rd with the password of your choice.

Once done switch to q01adm and check whether the R3trans -d is OK.

If the return code is NOT 0000 then check the trans.log file.

Use the following note to fix the issue:

Note 400241 - Problems with ops$ or sapr3 connect to Oracle

Good Luck

RB

[Former Member · Accepted solution]

Dear RB.

Your response is helpful but still unuable to get as i am new to SAP, if you could help me by giving the exact statement instaed of refering the Note or link would be really appreciable.

Thanks in Advance.

Regards

Khan.

[Reagan · Accepted solution]

Hello

Did you follow the steps I have mentioned above ?

Regards

RB

[Former Member · Accepted solution]

Dear RB.

I am confused at the below step that you have mentioned.

Upload it to the server at /oracle/Q01 and change the ownership of the file.

Switch to oraq01 and execute the script as follows.

su - oraq01

sqlplus /NOLOG @ORADBUSR.SQL SAPP01 UNIX Q01 x

Please guide me more clearly so that i can exicute the exact comand.

Thanks for your valuable support.

Regards

Khan

[Reagan · Accepted solution]

Hello

You need to download the script from this SAP Note

Note 50088 - Creating OPS$ users on Windows NT/Oracle

The script should be present on the server for you to execute it.

You run this script as orasid user.

Regards

RB

[Former Member · Accepted solution]

Hi RB.

I am running the command brconnect at O.S level with ora<sid>, it is not getting exicuted and throwing error.

Please Suggest.

Regards

Khan

[former_member206552 · Accepted solution]

Hi Khan,

copy the script ORADBUSR.TXT to the server from note 50088, once its on the server change the ownership of the script so that ORASID can execute it

su - orasid

sqlplus /NOLOG @oradbusr.sql SAPP01

Best Regards

Marius

[Reagan · Accepted solution]

Hello

I can only help you if you come back with the error messages.

Regards

RB

[Former Member · Accepted solution]

Hi. RB.

The issue resolved with the by following the steps you have mentioned.

Thanks for your support.

Regards

Hakeem Khan

[abhinavdubey]

Dear RB,

Did you perform system refresh in oracle??? because this issue is common in that case

just run the query oradbusr.sql give ypur user name password for DB it will work....

Just let me know the result...

Regards

Abhinav

[former_member206552]

Hi Khan

you can also have a look at note

Note 400241 - Problems with ops$ or sapr3 connect to Oracle

this one is also very helpful

Best Regards

Marius

[former_member206552]

Hi Khan

I would then first start and make sure the OPS$ users are correct.

second did you also rerun sapconn_role.sql  sapdba_role.sql  sapuprof_profile.sql ?

best regards

marius

[Former Member]

Dear Marius

Please tell me, do i directly go SQL> prompt by user ora<sid> and run the above said scripts.

Please reply the procedure if need to run in any other option, and alternatively do i need to do any other thing after running these scripts.

Thanks for your valuable support.

Regards

Khan

[former_member206552]

test to make sure it works first by running R3trans -d as SIDadm and as oraSID

if this does not work then you need to have a look at sqlplus level and see if the OPS$ users are created

select username from dba_users;

OPS$ORASID

OPS$SIDADM

if the do not excist then

source : Note 50088 - Creating OPS$ users on Windows NT/Oracle

Execute the following commands in the database one after the other. Replace <SAPSID> with the corresponding SAP system ID for your system: 

          
'create user OPS$<SAPSID>ADM default tablespace psapuser1d
temporary tablespace psaptemp identified externally;'

'grant connect, resource to OPS$<SAPSID>ADM;'

'connect /'

'create table SAPUSER
( USERID VARCHAR2(256), PASSWD VARCHAR2 (256));'

'insert into SAPUSER values ('SAPR3', '<password>');'

'connect internal'

'alter user sapr3 identified by <password>;'

do this for both user OPS$SIDADM and OPS$ORASID

Best Regards

Marius

[Former Member]

Dear Marius.

I have checked the users OPS$ORASID, OPS$SIDADM, SYSTEM are already existed in Database with status OPEN.

Please Suggest.

Thanks

Khan

[Former Member]

Dear Marius.

I have checked the users OPS$ORASID, OPS$SIDADM, SYSTEM are already existed in Database with status OPEN.

Please Suggest.

Thanks

Khan

[Former Member]

Hi Khan,

Go to command line editor if unix/linux sqlplus "/as sysdba"

select username,ACCOUNT_STATUS from dba_users;

Should list results such as

USERNAME ACCOUNT_STATUS

------------------------------ --------------------------------

SAPSR3DB OPEN

SAPSR3 OPEN

OPS$ORASID OPEN

OPS$SAPSERVICESID OPEN

OPS$SIDADM OPEN

DBSNMP EXPIRED & LOCKED

SYS OPEN

ORACLEDBA OPEN

OPS$SR3ADM OPEN

USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SYSTEM EXPIRED(GRACE)
 

Probably your user SAPSR3 and SAPSR3DB will be locked.

Do this to reset password.

select password from user$ where name='SYSTEM';

result:

PASSWORD
------------------------------
99938476

alter user system identified by values '99938476'

Now do a select again.

select username,ACCOUNT_STATUS from dba_users;

Account should be open.

Also for your SAPSR3 users if the accounts are locked implement sapuprof.

Implement note Note 1519872 - SAP Database User Profile SAPUPROF

Also is this error post change i.e. upgrade or system copy?

Johan

[Former Member]

Dear Johan.

This issue is after DB refresh.

Thanks

Khan

[Former Member]

Hi Khan,

As advised by Marius.

Note 361641 - Creating OPS$ users on UNIX

562863 - FAQ: Logon mechanisms

Regards,

Johan

[former_member144433]

Hi Khan,

When calling brbackup you can use the -a option to define the options for the subsequently called brarchive. You can find more info about it in the online help:

http://help.sap.com/saphelp_nw70ehp3/helpdata/en/47/1d8a59fbb112b7e10000

000a1553f7/content.htm

For example brbackup -u / -m all -t online -c -a -u / -ssd -c

will call first brbackup -u/-m all -t online -c and after that

                brarchive  -u/-ssd -c

If you call brbackup manually please add the option -a -u / so that the OPS$ connect is used.

If you schedule brbackup in DB13, then please check the psstring in tables SDBAC for BRARCHIVE and ensure  that a -u / option is used to enable the OPS$ mecahnism.

Alternative you can try to reset the system user's password to the default manager, so that the connect with the system user works.

alter user system identified by manager;

Thank You.

Regards,

Deepika

[former_member206552]

Hi Khan

where are you executing the backup from OS or DB13 ?

also windows, linux, unix ?

Best Regards

Marius

[Former Member]

Dear Marius.

I am executing in DB13, my O.S is Redhat Linux

Thanks

[Former Member]

Dear Marius.

I am executing in DB13, my O.S is Redhat Linux

Thanks