ora-01031:insufficient privileges/ v$pwfile_users

 

ora-01031:insufficient privileges/ v$pwfile_users

2012年05月09日 14:40:31 aaaaaaaa2000 阅读数：2436

今天在做dg的时候在主库上使用sys连接主库总是报ora-01031:insufficient privileges的错误：

1、查看参数 remote_login_passwordfile is set to SHARED or EXCLUSIVE
SQL> show parameter remote_login_passwordfile


NAME                                 TYPE                              VALUE
------------------------------------ --------------------------------- ------------------------------
remote_login_passwordfile            string                            EXCLUSIVE---没有问题


2、查看使用密码文件的用户中是否有sys用户

SQL> select * from v$pwfile_users;


no rows selected


SQL> grant sysdba to sys;
grant sysdba to sys
*
ERROR at line 1:
ORA-01994: GRANT failed: password file missing or disabled
没有密码文件？？？？马上查看是否存在密码文件

[oracle@master-new dbs]$ pwd
/disk2/oracle/product/11.2.0/dbs

发现密码文件竟然是：orapwdmaildata---哎密码文件错误啊，应该是orapw开头，结果多了d，重建密码文件

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID entries=10 password=password

那这里面的这个视图 v$pwfile_users到底有什么作用那？？？？

http://www.xifenfei.com/2025.html--转载
 

一、V$PWFILE_USERS定义

V$PWFILE_USERS lists all users in the password file, and indicates whether the user has been granted the SYSDBA, SYSOPER, and SYSASM privileges. Column      Datatype    Description USERNAM    VARCHAR2(30) Name of the user that is contained in the password file SYSDBA     VARCHAR2(5)  Indicates whether the user can connect with SYSDBA privileges (TRUE) or not (FALSE) SYSOPER    VARCHAR2(5)  Indicates whether the user can connect with SYSOPER privileges (TRUE) or not (FALSE) SYSASM     VARCHAR2(5)  Indicates whether the user can connect with SYSASM privileges (TRUE) or not (FALSE)

二、v$pwfile_users与密码文件关系

[oracle@node1 ~]$ sqlplus / as sysdba   SQL*Plus: Release 11.2.0.3.0 Production on Sun Dec 4 19:08:06 2011   Copyright (c) 1982, 2011, Oracle.  All rights reserved.   Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options   --查看密码文件用户权限 SQL> select * from v$pwfile_users;   USERNAME                       SYSDB SYSOP SYSAS ------------------------------ ----- ----- ----- SYS                            TRUE  TRUE  FALSE   SQL> show parameter instance_name;          NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------ instance_name                        string      ora11g   --查看系统级别查看密码文件内容 SQL> !strings $ORACLE_HOME/dbs/orapwora11g ]\[Z ORACLE Remote Password file INTERNAL A1174901D667F113 18698BFD1A045BCC   --创建新sysdba用户，查看视图和密码文件变化 SQL> create user xff01 identified by xifenfei;   User created.   SQL> grant sysdba to xff01;   Grant succeeded.   SQL>  select * from v$pwfile_users;   USERNAME                       SYSDB SYSOP SYSAS ------------------------------ ----- ----- ----- SYS                            TRUE  TRUE  FALSE XFF01                          TRUE  FALSE FALSE   SQL>  !strings $ORACLE_HOME/dbs/orapwora11g ]\[Z ORACLE Remote Password file INTERNAL A1174901D667F113 18698BFD1A045BCC XFF01 D32693095588EF4F   --删除密码文件 SQL> ! mv $ORACLE_HOME/dbs/orapwora11g $ORACLE_HOME/dbs/orapwora11g_bak   SQL> !ls $ORACLE_HOME/dbs/orapwora11g ls: /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g: 没有那个文件或目录   --查看视图 SQL>  select * from v$pwfile_users;   no rows selected   SQL>  ! mv $ORACLE_HOME/dbs/orapwora11g_bak $ORACLE_HOME/dbs/orapwora11g   SQL> !ls $ORACLE_HOME/dbs/orapwora11g /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g   SQL> select * from v$pwfile_users;   USERNAME                       SYSDB SYSOP SYSAS ------------------------------ ----- ----- ----- SYS                            TRUE  TRUE  FALSE XFF01                          TRUE  FALSE FALSE   --改变sysdba用户权限，视图内容变化 SQL> grant sysoper to xff01;   Grant succeeded.   SQL>  select * from v$pwfile_users;   USERNAME                       SYSDB SYSOP SYSAS ------------------------------ ----- ----- ----- SYS                            TRUE  TRUE  FALSE XFF01                          TRUE  TRUE  FALSE   --密码文件内容无变化 SQL> !strings $ORACLE_HOME/dbs/orapwora11g ]\[Z ORACLE Remote Password file INTERNAL A1174901D667F113 18698BFD1A045BCC XFF01 D32693095588EF4F   SQL> revoke sysdba from xff01;   Revoke succeeded.   SQL> select * from v$pwfile_users;   USERNAME                       SYSDB SYSOP SYSAS ------------------------------ ----- ----- ----- SYS                            TRUE  TRUE  FALSE XFF01                          FALSE TRUE  FALSE   SQL> revoke sysoper  from xff01;   Revoke succeeded.   SQL> select * from v$pwfile_users;   USERNAME                       SYSDB SYSOP SYSAS ------------------------------ ----- ----- ----- SYS                            TRUE  TRUE  FALSE XFF01                          FALSE FALSE FALSE   SQL> revoke sysoper  from xff01;   Revoke succeeded.   --回收sysdba，sysoper权限后，视图记录消失 SQL> select * from v$pwfile_users;   USERNAME                       SYSDB SYSOP SYSAS ------------------------------ ----- ----- ----- SYS                            TRUE  TRUE  FALSE   --密码文件内容无变化 SQL>  !strings $ORACLE_HOME/dbs/orapwora11g ]\[Z ORACLE Remote Password file INTERNAL A1174901D667F113 18698BFD1A045BCC XFF01 D32693095588EF4F   --删除其中sysdba用户 SQL> drop user xff01;   User dropped.   --密码文件内容还是无变化 SQL>  !strings $ORACLE_HOME/dbs/orapwora11g ]\[Z ORACLE Remote Password file INTERNAL A1174901D667F113 18698BFD1A045BCC XFF01 D32693095588EF4F   --重启数据库密码文件依然无变化 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> !strings $ORACLE_HOME/dbs/orapwora11g ]\[Z ORACLE Remote Password file INTERNAL A1174901D667F113 18698BFD1A045BCC XFF01 D32693095588EF4F

1、如果密码文件不存在或者名称错误，查询v$pwfile_users将得到空记录
2、添加sysdba等权限用户，会记录到密码文件和v$pwfile_users中
3、到回收sysdba等权限用户，密码文件记录依然存在，但是v$pwfile_users中无对应记录

三、远程登录测试

--密码文件记录存在，视图不记录不存在，登录失败 [oracle@node1 ~]$ sqlplus xff01/xifenfei@ora11g as sysdba   SQL*Plus: Release 10.2.0.5.0 - Production on Sun Dec 4 19:42:26 2011   Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.   ERROR: ORA-01031: insufficient privileges   Enter user-name   --密码文件视图记录均存在，登录成功 [oracle@node1 ~]$ sqlplus sys/xifenfei@ora11g as sysdba   SQL*Plus: Release 10.2.0.5.0 - Production on Sun Dec 4 19:42:10 2011   Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.   Connected to an idle instance.

是否能远程登录，依照v$pwfile_users为准

四、创建密码文件

win： orapwd file=%ORACLE_HOME%\database\PWD%ORACLE_SID%.ora entries=3 password=manager force=y linux: orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID entries=3 password=manager force=y

 

1、验证密码文件已经修改

SQL> create user xff01 identified by xifenfei;   User created.   SQL> grant sysdba to xff01;   Grant succeeded.   SQL> !md5sum $ORACLE_HOME/dbs/orapwora11g 04cedb56b62d94fd7e14124619722348  /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g   SQL> !strings /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g ]\[Z ORACLE Remote Password file INTERNAL A1174901D667F113 18698BFD1A045BCC XFF01 D32693095588EF4F   SQL> revoke sysdba from xff01;   Revoke succeeded.   SQL> !strings /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g ]\[Z ORACLE Remote Password file INTERNAL A1174901D667F113 18698BFD1A045BCC XFF01 D32693095588EF4F   SQL> !md5sum $ORACLE_HOME/dbs/orapwora11g 1f6d120acb913a1877cfb0ab57702744  /opt/oracle/product/11.2.0/db_1/dbs/orapwora11g

2、查看基表

SQL> col owner for a20 SQL> col object_name for a30 SQL> SELECT owner,object_name,object_type FROM DBA_objects WHERE object_NAME ='V$PWFILE_USERS';   OWNER                OBJECT_NAME                    OBJECT_TYPE -------------------- ------------------------------ ------------------- PUBLIC               V$PWFILE_USERS                 SYNONYM   SQL> SELECT table_owner,TABLE_name FROM Dba_Synonyms a WHERE a.synonym_name='V$PWFILE_USERS';   TABLE_OWNER                    TABLE_NAME ------------------------------ ------------------------------ SYS                            V_$PWFILE_USERS   SQL> SELECT owner,object_name,object_type FROM dba_objects WHERE object_name='V_$PWFILE_USERS';   OWNER                OBJECT_NAME                    OBJECT_TYPE -------------------- ------------------------------ ------------------- SYS                  V_$PWFILE_USERS                VIEW   SQL> set long 1000 SQL> set line 200 SQL> SELECT dbms_metadata.get_ddl('VIEW','V_$PWFILE_USERS','SYS') FROM DUAL;   DBMS_METADATA.GET_DDL('VIEW','V_$PWFILE_USERS','SYS') --------------------------------------------------------------------------------     CREATE OR REPLACE FORCE VIEW "SYS"."V_$PWFILE_USERS" ("USERNAME", "SYSDBA", "SYSOPER", "SYSASM") AS   select "USERNAME","SYSDBA","SYSOPER","SYSASM" from v$pwfile_users   SQL> select * from v$fixed_table where name ='V$PWFILE_USERS';   NAME                            OBJECT_ID TYPE   TABLE_NUM ------------------------------ ---------- ----- ---------- V$PWFILE_USERS                 4294951116 VIEW       65537   SQL> select VIEW_DEFINITION from v$fixed_view_definition where view_name='V$PWFILE_USERS';   VIEW_DEFINITION -------------------------------------------------------------------------------------------------------- select  USERNAME , SYSDBA , SYSOPER, SYSASM from GV$PWFILE_USERS where inst_id = USERENV('Instance')   SQL> select VIEW_DEFINITION from v$fixed_view_definition where view_name='GV$PWFILE_USERS';   VIEW_DEFINITION ------------------------------------------------------------------------------------------------------------ select inst_id,username,decode(sysdba,1,'TRUE','FALSE'),  decode(sysoper,1,'TRUE','FALSE'),  decode(sysasm,1,'TRUE','FALSE')  from x$kzsrt where valid=1  and username != 'INTERNAL'   SQL> set line 100 SQL> desc x$kzsrt  Name                                                  Null?    Type  ----------------------------------------------------- -------- ------------------------------------  ADDR                                                           RAW(8)  INDX                                                           NUMBER  INST_ID                                                        NUMBER  USERNAME                                                       VARCHAR2(30)  SYSDBA                                                         NUMBER  SYSOPER                                                        NUMBER  SYSASM                                                         NUMBER  VALID                                                          NUMBER   SQL> col username for a10 SQL> select * from x$kzsrt;   ADDR                   INDX    INST_ID USERNAME       SYSDBA    SYSOPER     SYSASM      VALID ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- 00002B9563678690          0          1 INTERNAL            1          1          0          1 00002B9563678690          1          1 SYS                 1          1          0          1 00002B9563678690          2          1 XFF01               0          0          0          0

从这里可以看出V$PWFILE_USERS视图的基表是x$kzsrt，这里和我们查看的密码文件一样，这些记录都存在，而是在密码文件中有不被strings显示的字符表示了账号是否启用，x$kzsrt.valid用1和0标示

https://blog.csdn.net/aaaaaaaa2000/article/details/7549901