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.
2 Answers 2
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.
@Balazs Papp is right. My case was in a ONE NODE RAC environment, I could not connect to NODE1 with sqlplus sys/password@tnsalias as sysdba from NODE2. It was because I had changed the password of sys from NODE1, but the orapw$ORACLE_SID file in NODE2 kept the old password. So I copied the orapw$ORACLE_SID from NODE1 to NODE2, and the problem got solved.