Troubleshooting ORA-1031: Insufficient Privileges While Connecting As SYSDBA [ID 730067.1]

		修改时间 05-SEP-2011 类型 TROUBLESHOOTING 状态 PUBLISHED

In this Document
Purpose
Last Review Date
Instructions for the Reader
Troubleshooting Details
Generic Concepts
SYSDBA OS Authentication
SYSDBA Password File Authentication
Troubleshooting ORA-1031 when connecting as SYSDBA using OS Authentication
1. Check whether the OS user is part of the OSDBA group.
2. Check the value of the SQLNET.AUTHENTICATION_SERVICES parameter in file $ORACLE_HOME/network/admin/SQLNET.ORA
Troubleshooting ORA-1031 seen while using password file authentication
1.Check the value of parameter remote_login_passwordfile. This has to be set to either EXCLUSIVE or SHARED:
2. Check whether the password file with the correct name exists in the right directory/folder and has the right ownership and permissions
3. Check whether the user was granted the SYSDBA privilege

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 11.2.0.2 - Release: 9.2 to 11.2
Information in this document applies to any platform.

Purpose

This document is intended to supply some commonly used steps for diagnosing error ORA-1031 encountered while connecting as sysdba.

Last Review Date

September 2, 2011

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

Troubleshooting Details

Generic Concepts

A user can connect AS SYSDBA from the Oracle Server host machine using one of the following methods:

OS authentication
password file authentication

A user should connect to the database AS SYSDBA from a remote machine only by using password file authentication . When the Oracle Server runs on Windows it is possible to connect remotely as sysdba without providing a username and a password since in this case the connection might be secure.

Starting with Oracle 11g it is also possible to use strong authentication for SYSDBA. See Note 457083.1 for details.

SYSDBA OS Authentication

The OS authentication is the process of verifying the identity of the user connecting to the database with the information managed by the OS. An OS user is able to use this authentication method if the following conditions are met:

1. the user is a member of a special group.
2. the OS authentication is allowed by the server settings(sqlnet.authentication_services is set correctly)

The OS user should belong to the OSDBA group in order to login as sysdba. On Unix the default name of these group is dba. On Windows the name of the group is ORA_DBA.

On Unix Parameter sqlnet.authentication_services must be set to (ALL) or to (BEQ, ) for this to work. On Windows this parameter must be set to (NTS).

SYSDBA Password File Authentication

The credentials provided when connecting remotely as sysdba are compared to the contents of the passwordfile.

Password file authentication is enabled by setting the database parameter remote_login_password file to "shared" or "exclusive".

SQL> alter system set remote_login_passwordfile=exclusive scope=spfile;

Note: When both OS authentication and password file authentication are enabled then the OS Authentication will be used. This means that you can connect with any username/password combination. See Note 242258.1 for details.

Troubleshooting ORA-1031 when connecting as SYSDBA using OS Authentication

1. Check whether the OS user is part of the OSDBA group.

On Unix

A. See what are the groups of the user:

[oracle@seclin4 ~]$ id
uid=500(oracle) gid=500(oinstall) groups=500(oinstall),501(dba) context=user_u:system_r:unconfined_t

B. See what is the OSDBA group defined in file $ORACLE_HOME/rdbms/lib/config.[cs]

Example for Linux:

[oracle@seclin4 ~]$ cat $ORACLE_HOME/rdbms/lib/config.c

/*  SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access.  */
/*  Refer to the Installation and User's Guide for further information.  */

/* IMPORTANT: this file needs to be in sync with
              rdbms/src/server/osds/config.c, specifically regarding the
              number of elements in the ss_dba_grp array.
 */

#define SS_DBA_GRP "dba"
#define SS_OPER_GRP "dba"
#define SS_ASM_GRP ""

char *ss_dba_grp[] = {SS_DBA_GRP, SS_OPER_GRP, SS_ASM_GRP};

Example for AIX:

[celcaix3]/grdbms/64bit/app/oracle/product/1120/rdbms/lib> cat config.s
#  SS_DBA_GRP defines the UNIX group ID for sqldba adminstrative access.
#  Refer to the Installation and User's Guide for further information.

        .rename H.10.NO_SYMBOL{TC},""
        .rename H.11.NO_SYMBOL{TC},""
        .rename H.12.NO_SYMBOL{TC},""
        .rename H.13.NO_SYMBOL{RO},""
        .rename H.14.NO_SYMBOL{RO},""
        .rename H.15.NO_SYMBOL{RO},""
        .rename H.16.ss_dba_grp{TC},"ss_dba_grp"

        .lglobl H.13.NO_SYMBOL{RO}
        .lglobl H.14.NO_SYMBOL{RO}
        .lglobl H.15.NO_SYMBOL{RO}
        .globl  ss_dba_grp{RW}

# .text section

# .data section

        .toc
T.16.ss_dba_grp:
        .tc     H.16.ss_dba_grp{TC},ss_dba_grp{RW}
T.10.NO_SYMBOL:
        .tc     H.10.NO_SYMBOL{TC},H.13.NO_SYMBOL{RO}
T.11.NO_SYMBOL:
        .tc     H.11.NO_SYMBOL{TC},H.14.NO_SYMBOL{RO}
T.12.NO_SYMBOL:
        .tc     H.12.NO_SYMBOL{TC},H.15.NO_SYMBOL{RO}

        .csect  ss_dba_grp{RW}, 3
        .llong  H.13.NO_SYMBOL{RO}
        .llong  H.14.NO_SYMBOL{RO}
        .llong  H.15.NO_SYMBOL{RO}
# End   csect   ss_dba_grp{RW}

        .csect  H.13.NO_SYMBOL{RO}, 3
        .string "dba"
# End   csect   H.13.NO_SYMBOL{RO}

        .csect  H.14.NO_SYMBOL{RO}, 3
        .string "dba"
# End   csect   H.14.NO_SYMBOL{RO}

        .csect  H.15.NO_SYMBOL{RO}, 3
        .string ""
# End   csect   H.15.NO_SYMBOL{RO}
        .llong  0x00000000

# .bss section

In the above examples we see that the user is a member of the "dba" group which matches the value of SS_DBA_GRP in config.c. If the groups seem to be correctly configured but the connection is still failing use the script from Note 67984.1 to see whether Oracle evaluates correctly the group membership.

On Windows

When using OS authentication on Windows the OS user must be a member of one of the following two groups:

ORA_DBA
ORA_<%ORACLE_SID%>_DBA

The membership to the second group allows the OS user to use OS authentication while connecting to the instance with the name %ORACLE_SID%> only.

Check whether the OS user is a member of any of these two local groups:

Get the name of the OS user:


D:oracleproduct10.2.0db_1NETWORKADMIN>echo %username%
dbadmin

Obtain the list of the members of the local group:

D:oracleproduct10.2.0db_1NETWORKADMIN>NET LOCALGROUP ORA_DBA
Alias name     ORA_DBA
Comment        Members can connect to the Oracle database as a DBA without a password
Members

-------------------------------------------------------------------------------
dbadmin
NT AUTHORITYSYSTEM
The command completed successfully.

D:oracleproduct10.2.0db_1NETWORKADMIN>set oracle_sid=d1v10204

D:oracleproduct10.2.0db_1NETWORKADMIN>NET LOCALGROUP ORA_%ORACLE_SID%_DBA
Alias name     ORA_d1v10204_DBA
Comment Members can connect to instance d1v10204 as a DBA without a password

Members
-------------------------------------------------------------------------------
dbadmin
The command completed successfully.

If the user is a member of these groups but the connection is still failing run the script from the following page to see whether Oracle evaluates the group membership correctly:

http://msdn.microsoft.com/en-us/library/aa370655(VS.85).aspx

2. Check the value of the SQLNET.AUTHENTICATION_SERVICES parameter in file $ORACLE_HOME/network/admin/SQLNET.ORA

On Unix

This parameter should not be set if no strong authentication method is used. If such a method is being used then set the parameter to one of the following values:

SQLNET.AUTHENTICATION_SERVICES = (ALL)

or

SQLNET.AUTHENTICATION_SERVICES = (BEQ,)

Where can be any combination of the following values: TCPS, KERBEROS5, RADIUS

On Windows

This parameter should be set to NTS:

SQLNET.AUTHENTICATION_SERVICES = (NTS)

If needed you can add other strong authentication methods besides NTS:


SQLNET.AUTHENTICATION_SERVICES = (NTS,TCPS)

Note: If the parameter is set to NONE then the OS authentication will be disabled and the user will have to provide a valid username/password combination to be able to connect to the database.

Note:
On Windows the user who is not able to connect as sysdba using OS authentication might be a domain user. It is important that this user is a direct member of the local ORA_DBA group( See Note 1065023.1 for details). Furthermore in this case the Oracle Service must be started as a user who is able to check the group membership for any domain user who might be connecting as sysdba locally( See Note 1071638.1 for details).

3. After checking the configuration as per steps 1) and 2) review the problems described in the following notes:

Note 69642.1 - UNIX: Checklist for Resolving Connect AS SYSDBA Issues
Note 114384.1 - WIN: Checklist for Resolving CONNECT AS SYSDBA (INTERNAL) Issues

4. If the problem is not solved after reviewing the above notes open a service request with Oracle Support and provide the following information:

For Unix

A) File $ORACLE_HOME/network/admin/sqlnet.ora
B) File $ORACLE_HOME/rdbms/lib/config.c(or config.s)
C) The output of command "id"
D) A trace file obtained with the following commands:

Linux: 
strace -o /tmp/strace_sysdba.output -cfT sqlplus / as sysdba

AIX, Solaris: 
truss -fea -o /tmp/truss_sysdba.output sqlplus / as sysdba


HP-UX: 
tusc -afpo /tmp/tusc_sysdba.output -aef sqlplus / as sysdba

For Windows

A) File %ORACLE_HOME%/network/admin/sqlnet.ora
B) The output of the commands "echo %username% and "NET LOCALGROUP ORA_DBA"
C) A pair of client/server SQL*Net traces obtained while reproducing the problem as per Note 395525.1 and Note 374116.1.

Troubleshooting ORA-1031 seen while using password file authentication

1.Check the value of parameter remote_login_passwordfile. This has to be set to either EXCLUSIVE or SHARED:

SQL> show parameter remote_login_passwordfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

If the parameter is not set correctly then modify it and then restart the database:

SQL> ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

2. Check whether the password file with the correct name exists in the right directory/folder and has the right ownership and permissions

On Unix

The password file with the name orapw must exist in directory $ORACLE_HOME/dbs. If it does not exist then recreate it using the orapwd command

$ > orapwd file=$ORACLE_HOME/dbs/orapw password= force=y ignorecase=n

The permissions of the file should be the following:

-rw-r----- 1 oracle oinstall 1536 Jul 21 13:50 orapwdv11201

More information about using the orapwd command can be seen in Note 1029539.6.

On Windows

The default location of the password file on Windows is folder %ORACLE_HOME%/database and the name of the password file must be pwd<%ORACLE_SID%>.ora. When the passwordfile authentication is being used Oracle searches for the password file in the following locations(in this exact order):

The folder pointed to by the registry key HKEY_LOCAL_MACHINESOFTWAREORACLEHomeXXORA_<%ORACLE_SID%>_PWFILE
The folder pointed to by the registry key HKEY_LOCAL_MACHINESOFTWAREORACLEHomeXXORA_PWFILE
The default location ( %ORACLE_HOME%/database)

Note: Make sure that the password file exists in the folder specified by the registry keys if these are set.

If the password file does not exist in the right folder then create it using the orapwd command:

D:> cd %ORACLE_HOME/database 
D:> orapwd file=pwd.ora password= force=y nosysdba=n

More information about using the orapwd command can be seen in Note 1029539.6.

3. Check whether the user was granted the SYSDBA privilege

SQL> select * from v$pwfile_users;

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

If the user is not granted the privilege then run:

grant SYSDBA to myadminuser;

where myadminuser is the user you want to use to connect as sysdba.

[@more@]

ITPub博客

Troubleshooting ORA-1031: Insufficient Privileges While Connecting As SYSDBA [ID 730067.1]

Applies to:

Purpose

Last Review Date

Instructions for the Reader

Troubleshooting Details

Generic Concepts

SYSDBA OS Authentication

SYSDBA Password File Authentication

Troubleshooting ORA-1031 when connecting as SYSDBA using OS Authentication

1. Check whether the OS user is part of the OSDBA group.

On Unix

On Windows

2. Check the value of the SQLNET.AUTHENTICATION_SERVICES parameter in file $ORACLE_HOME/network/admin/SQLNET.ORA

On Unix

On Windows

For Unix

For Windows

Troubleshooting ORA-1031 seen while using password file authentication

1.Check the value of parameter remote_login_passwordfile. This has to be set to either EXCLUSIVE or SHARED:

2. Check whether the password file with the correct name exists in the right directory/folder and has the right ownership and permissions

On Unix

On Windows

3. Check whether the user was granted the SYSDBA privilege

最新文章