ORA-01031
증상 : SYS, SYSTEM 계정으로 원격에서접속이 안됨.
ORA-01031: insufficient privileges 에러발생
>sqlplus /nolog
sql> conn sys/oracle@orcl as sysdba
확인사항
첫째 : 패스워드 인증방식으로 로그인 할 수 있는 유저확인
둘째 : 패스워드 파일이 잘못되었을 경우
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
TEST_USER TRUE FALSE
SQL> show user;
USER은 "SYS"입니다
SQL> grant sysdba to system;
권한이 부여되었습니다.
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
------------------------------ ----- -----
SYS TRUE TRUE
TEST_USER TRUE FALSE
SYSTEM TRUE FALSE
AS SYSDBA 접속시 PassWord 설정
Usage: orapwd file=<fname> password=<password> entries=<users>
file - name of password file (mand),
password - password for SYS and INTERNAL (mand),
entries - maximum number of distinct DBA and OPERs (opt),
There are no spaces around the equal-to (=) character.
$orapwd file=orapw<SID> password=[password]
$orapwd file=orapw<SID> password=[password] entries=10
init<SID>.ora editor -> REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE
SQL> show parameter spfile
NAME TYPE VALUE
---------- ---------- ---------
spfile string ?/dbs/spfile@.ora
SQL> show parameter remote
NAME TYPE VALUE
------------------------------------ ----------- ---------------
remote_archive_enable boolean TRUE
remote_dependencies_mode string TIMESTAMP
remote_listener string
remote_login_passwordfile string
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
SQL> alter system set remote_login_passwordfile=exclusive scope=spfile ;
System altered.
SQL> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------
spfile string ?/dbs/spfile@.ora
SQL> show parameter remote
NAME TYPE VALUE
------------------------------------ ----------- ---------------
remote_archive_enable boolean TRUE
remote_dependencies_mode string TIMESTAMP
remote_listener string
remote_login_passwordfile string EXCLUSIVE
remote_os_authent boolean FALSE
remote_os_roles boolean FALSE
$ echo $TNS_ADMIN
/d02/rctest/app/oracle/product/9.0.1/networ/admin
SQLNET.AUTHENTICATION_SERVICES = (NONE)
SQL> connect sys/manager as sysdba
Connected.
SQL>
