0

When I create a user in an 11204 single instance DB and afterwards grant the sysdba privilege to that user I can connect remotely without any problems using:

sqlplus testuser/testuserpwd@testdb as sysdba

However, when I do this against a RAC DB I keep getting an invalid username and password error for some reason. I can login locally with that username and password so they are correct. I can login remotely as well using:

sqlplus testuser/testuserpwd@racdb

It's only when I add 'as sysdba' at the end that it gives me the invalid username/password error with the RAC DB.

Any ideas? Many thanks.

0

Remote SYSDBA login uses password file authentication. A 11g RAC database uses local password files on each node in the local filesystem ($ORACLE_HOME/dbs). When you grant SYSDBA to a user, or change the password of this user, the password file is automatically updated on the node where the command was executed, but not on the other nodes. You need to manually copy the updated password file to the other nodes as well, otherwise you will not be able to log in to the other nodes.

Starting with 12c, the database password file can be stored in ASM, and it is not needed to perform such manual maintenance steps anymore.

  • Thanks for that explanation. Is it possible to connect remotely as sysdba to just that one node where the local password file was updated without creating a specific local tnsnames.ora entry for this? I use ldap for names resolution so I connect normally using the instance name ... which of course won't work in this case since the various password files differ. – Franco Jan 3 '17 at 14:17
  • @Franco Of course, just use the EZCONNECT syntax. sqlplus /nolog, then connect testuser/testuserpwd@node_name/service_name/instance_name as sysdba or connect testuser/testuserpwd@scan_name/service_name/instance_name as sysdba – Balazs Papp Jan 3 '17 at 14:37
  • Yes, that works perfectly. Many thanks for your help. – Franco Jan 6 '17 at 23:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.