ORA-01031: insufficient privileges

在一次清理过程中将sys用户的密码文件清除了

也就是$ORACLE_HOME/dbs/orapw($ORACLE_SID)文件弄没了

然后登录的时候出现了

ORA-01031: insufficient privileges

SQL> connect / as sysdba
Connected.
SQL> show parameter service_name

NAME          TYPE     VALUE
------------------------------------ ----------- ------------------------------
service_names          string     caitest
SQL> connect sys/cai@caitest as sysdba
ERROR:
ORA-01031: insufficient privileges


Warning: You are no longer connected to ORACLE.

然后进行陪查了一圈也没看出来有什么错误system登录也没问题

SQL> show parameter remote;

NAME                     TYPE     VALUE
------------------------------------ ----------- ------------------------------
remote_dependencies_mode         string     TIMESTAMP
remote_listener              string
remote_login_passwordfile         string     EXCLUSIVE
remote_os_authent             boolean     FALSE
remote_os_roles              boolean     FALSE
result_cache_remote_expiration         integer     0

然后查sys用户的密码文件

[oracle@oracle ~]$ ll $ORACLE_HOME/dbs/orapw*

ls: /oracle/software/dbs/orapw*: No such file or directory

看来是被删了

创建之用orapwd命令

[oracle@oracle ~]$ orapwd
Usage: orapwd file=<fname> entries=<users> force=<y/n> ignorecase=<y/n> nosysdba=<y/n>

  where
    file - name of password file (required),
    password - password for SYS will be prompted if not specified at command line,
    entries - maximum number of distinct DBA (optional),
    force - whether to overwrite existing file (optional),
    ignorecase - passwords are case-insensitive (optional),
    nosysdba - whether to shut out the SYSDBA logon (optional Database Vault only).
    
  There must be no spaces around the equal-to (=) character.

创建文件：

[oracle@oracle ~]$ cd $ORACLE_HOME/dbs/

[oracle@oracle dbs]$ orapwd file=orapwtest password=cai entries=8   #file选项是文件名命名一般为orapw（sid）命名 我的sid是test 所以文件命名为orapwtest

                                                                                                                               #password选项是密码

                                                                                                                               #entries选项是这个密码文件中存放多少个类似于sys这样的dba用户（重复）
SQL> grant sysdba to SYS;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME               SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS                   TRUE  TRUE  FALSE

SQL> !
[oracle@oracle dbs]$ exit
exit

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

在用密码登录：

[oracle@oracle dbs]$ sqlplus "sys/cai@caitest as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Fri Aug 30 02:58:26 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

成功连接

##################################

迷途小运维随笔

作者：john

转载请注明出处