SYSDBA cannot log in remotely this is also a very common problem.
How are we supposed to solve this problem?
When we log in with SYSDBA, we use it to manage our database instances, especially when the server is no longer the machine, which is even more necessary.
When we use Sqlplus "/as SYSDBA"
Is possible to log in.
But if we use Sqlplus "Sys/[email protected" as SYSBDA "this way we can't log in.
Here our mistakes are generally divided into two categories
First class: Prompt ora-01031:insufficient privileges (insufficient permissions)
Reference:
sqlplus "Sys/[email protected] as SYSDBA"
ERROR:
ORA-01031: Insufficient Permissions
If this is the error, you are clearly logged in with this sys, and is indeed the SYSDBA permissions, why the login is not enough to prompt your permission.
The problem here is in the password file, because your password file is missing or does not exist, and the Oracle instance's permissions to SYSDBA are based on this
Password file to determine if your users are grant to SYSDBA, then this user's password and user name are also in this password file.
This also found that you are not the role of SYSDBA, of course as SYSDBA will be prompted insufficient authority yo.
Why do we do this, if our database does not have open, then for some of the dynamic views can not be opened, so Oracle must put these
The user's password and username in a separate file, so that the database is not open in the state of writing, there is a place to judge.
How to fix it. Let's start by judging
Execute SQL
Sql> select * from V$pwfile_users;
USERNAME Sysdb SYSOP
------------------------------ ----- -----
SYS true True
If there is no record here, the password file is not in Dalat.
The solution here is to rebuild the Orapwd file.
Use the password file orapwd command orapwd-c file= "$ORACLE _home/dbs/orapwd$oracle_sid" entity=5 password=changeoninstall
Check v$pwfile_users again, there's a record.
Now try again, our problem is basically solved.
In another case, the hint is ORA-01017: User name password error
Such a mistake, but your password and user name clearly entered is correct. This is why
Check the value of the system parameter Remote_login_passwordfile,
If you need to start a password file here, the prerequisite is this value, where the value can be none,exclusive and share
Exclusive and share both, exclusive is an exclusive file, share is a shared mode in a multi-instance system
This problem is basically the result of this value.
Sql>alter system set remote_login_passwordfile=exclusive scope=spfile;
Try restarting the service now.
OK, it's settled.
We've talked about these two issues before.
Can look at the separate discussion of these two issues
ORA-01017: User name password error failure instance
Resolves ora-01031:insufficient privileges issues with non-native Sqlplus conn as SYSDBA on 10g
SYSDBA can't log in remotely, what should we do (reprint)