A-A+

密码文件导致sys用户as sysdba无法登陆

2014年06月06日 TroubleShooting 暂无评论 阅读 2,006 次

密码文件导致sys用户as sysdba无法登陆
现象如下:

[oracle@test1 ~]$ sqlplus system/oracle@prod

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 30 10:58:46 2014

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@test1 ~]$ sqlplus system/oracle@prod as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 30 10:58:50 2014

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

ERROR:
ORA-01031: insufficient privileges


Enter user-name: 

注意:此处开始报错!

查看监听状态

[oracle@test1 admin]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 30-MAY-2014 10:57:02

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test1)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                30-MAY-2014 10:24:53
Uptime                    0 days 0 hr. 32 min. 8 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "plsextproc" has 1 instance(s).
  Instance "plsextproc", status UNKNOWN, has 1 handler(s) for this service...
Service "prod" has 1 instance(s).
  Instance "prod", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@test1 admin]$ lsnrctl status lsnr2

LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 30-MAY-2014 10:57:05

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test1)(PORT=1526)))
STATUS of the LISTENER
------------------------
Alias                     lsnr2
Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date                30-MAY-2014 10:25:02
Uptime                    0 days 0 hr. 32 min. 3 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/lsnr2.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test1)(PORT=1526)))
Services Summary...
Service "prod" has 1 instance(s).
  Instance "prod", status READY, has 4 handler(s) for this service...
Service "prod_XPT" has 1 instance(s).
  Instance "prod", status READY, has 4 handler(s) for this service...
The command completed successfully

查看当前用户组权限

[oracle@test1 ~]$ id
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba)

查看tnsnames.ora文件

[oracle@test1 admin]$ cat tnsnames.ora 
LSNR2=
 (DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=test1)(PORT=1526)))


prod= 
 (DESCRIPTION= 
   (ADDRESS=(PROTOCOL=tcp)(HOST=test1)(PORT=1521))
   (CONNECT_DATA= 
     (SERVICE_NAME=prod)
         (SERVER=dedicated))) 

prod_s= 
 (DESCRIPTION= 
   (ADDRESS=(PROTOCOL=tcp)(HOST=test1)(PORT=1526))
   (CONNECT_DATA= 
     (SERVICE_NAME=prod)
         (SERVER=shared))) 

[oracle@test1 admin]$ cat listener.ora 
LISTENER=
  (DESCRIPTION=
    (ADDRESS_LIST=
      (ADDRESS=(PROTOCOL=tcp)(HOST=test1)(PORT=1521))
      (ADDRESS=(PROTOCOL=ipc)(KEY=extproc))))
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
      (GLOBAL_DBNAME=prod)
      (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME=prod))
    (SID_DESC=
      (SID_NAME=plsextproc)
      (ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM=extproc)))

LSNR2=
 (DESCRIPTION=
  (ADDRESS=(PROTOCOL=tcp)(HOST=test1)(PORT=1526)))

查看sqlnet.ora文件

[oracle@test1 admin]$ cat sqlnet.ora 
NAMES.DIRECTORY_PATH = (TNSNAMES,EZCONNECT)

查看各参数设置

SQL> show parameter local

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
local_listener                       string      lsnr2
log_archive_local_first              boolean     TRUE
SQL> show parameter remote

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_archive_enable                string      true
remote_dependencies_mode             string      TIMESTAMP
remote_listener                      string
remote_login_passwordfile            string      EXCLUSIVE
remote_os_authent                    boolean     FALSE
remote_os_roles                      boolean     FALSE  

查看密码文件

[oracle@test1 dbs]$ ls -l orapwPROD 
-rw-r----- 1 oracle oinstall 5120 May 30 10:26 orapwPROD

查看oracle用户权限

SQL> sho user
USER is "SYSTEM"
SQL> select * from v$pwfile_users;

no rows selected

SQL> grant dba to system;        

Grant succeeded.

SQL> select * from v$pwfile_users;

no rows selected	
SQL> conn /as sysdba
Connected.
SQL> show user 
USER is "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@test1 dbs]$ rm -rf orapwPROD 
[oracle@test1 dbs]$ env|grep ORACLE_SID
ORACLE_SID=prod
[oracle@test1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapwprod password=oracle
[oracle@test1 dbs]$ ls -l orapwprod 
-rw-r----- 1 oracle oinstall 1536 May 30 11:09 orapwprod

SQL> show user 
USER is "SYS"
SQL> grant sysdba to sys;

Grant succeeded.

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
SQL> grant sysdba to system;

Grant succeeded.

SQL> conn system/oracle
Connected.
SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP
------------------------------ ----- -----
SYS                            TRUE  TRUE
SYSTEM                         TRUE  FALSE

如下,测试正常。

[oracle@ocm1 ~]$ sqlplus system/oracle@prod as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 30 11:21:42 2014

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
[oracle@ocm1 ~]$ sqlplus system/oracle@prod 

SQL*Plus: Release 10.2.0.1.0 - Production on Fri May 30 11:21:46 2014

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

注意:密码文件在linux下命名应该是orapw<$ORACLE_SID>,并且其中的<$ORACLE_SID> 是严格区分大小写的。
参考的文档:Troubleshooting ORA-1031: Insufficient Privileges While Connecting As SYSDBA (文档 ID 730067.1)

给我留言

Copyright © YallonKing 保留所有权利.   Theme  Ality

用户登录

分享到: