九乡新闻网陈琦gre填空24套有用吗:解决Ora-01031的问题完美解决办法，可惜我的失败了。。
来源：百度文库 编辑：九乡新闻网 时间：2024/04/20 13:44:09
SQL>; connect / as sysdba
ERROR:
ORA-01031: insufficient privileges
什么情况下可以不输入用户名/密码？
zorrol 回复于：2003-08-07 10:19:46
小弟有些总结请参考，并提宝贵意见
#############################################
#
# NAME: troubleshoot connect internal.txt
#
# DESCRIPTION:
# connect internal
# connect / as sysdba 要口令问题：
# refer (METALINK,ORACLEDOC),
# metalink search words(connect internal)
# NOTE:
#
# modifiy (MM/DD/YY) AUTHOR
# 05/03/03 ZORROL
#############################################
————————————————————
1。检查sqlnet.ora 文件.
sqlnet.ora 文件损坏或格式不对可以导致出现该问题。
sqlnet.ora 文件可能存放路径为
$TNS_ADMIN/sqlnet.ora
如果没有设置$TNS_ADMIN默认在$ORACLE_HOME/network/admin/sqlnet.ora
或
$HOME/sqlnet.ora
(1). 可以从别的机器拷贝一个文件过来，注意备份原来的sqlnet.ora。
---检查sqlnet.ora 文件内容
(2). 检查SQLNET.AUTHENTICATION_SERVICES
如果没有使用dblink.检查该行并设置
SQLNET.AUTHENTICATION_SERVICES = (BEQ,NONE)
(3). SQLNET.CRYPTO_SEED
在unix 下不需要该参数。如果存在该行，注释掉或删掉
(4).AUTOMATIC_IPC
如果该参数为 ON，将强制使用"TWO_TASK" 连接
最好设置为OFF
AUTOMATIC_IPC = OFF
2.检查相关文件的权限配置。
找到$TNS_ADMIN/*
$ cd $TNS_ADMIN
$ chmod 644 sqlnet.ora tnsnames.ora listener.ora
$ ls -l sqlnet.ora tnsnames.ora listener.ora
-rw-r--r-- 1 oracle dba 1628 Jul 12 15:25 listener.ora
-rw-r--r-- 1 oracle dba 586 Jun 1 12:07 sqlnet.ora
-rw-r--r-- 1 oracle dba 82274 Jul 12 15:23 tnsnames.ora
3.检查操作系统相关设置。
(1). $ORACLE_HOME环境变量是否设置正确
% cd $ORACLE_HOME
% pwd
如果错误，请重新设置：
sh or ksh: ----------
$ ORACLE_HOME=;
$ export ORACLE_HOME
Example:
$ ORACLE_HOME=/u01/app/oracle/product/7.3.3
$ export ORACLE_HOME
csh: ----
% setenv ORACLE_HOME; Example:
% setenv ORACLE_HOME /u01/app/oracle/product/7.3.3
另外$ORACLE_HOME路径应为实际路径，不应是目录连接（ln -s)
(2) $ORACLE_SID是否设置正确；
% echo $ORACLE_SID
(3).确信没有设置$TWO_TASK
检查 "TWO_TASK" 是否设置：
sh, ksh or on HP/UX only csh:
-----------------------------------
env |grep -i two
- or -
echo $TWO_TASK
csh:
----
setenv |grep -i two
如果有返回行比如:
TWO_TASK=
- or -
TWO_TASK=PROD
就需要取消着这些环境变量设置 ：
sh or ksh:
----------
unset TWO_TASK
csh:
----
unsetenv TWO_TASK
(4) 检查oracle 文件的权限:
% cd $ORACLE_HOME/bin
% ls -l oracle
权限应为：rwsr-s--x, or 6751.
如果不是：
% chmod 6751 oracle
(5). 检查当前所连接的操作系统用户是否是"osdba" 并且已经定义在：
"$ORACLE_HOME/rdbms/lib/config.s"
or
"$ORACLE_HOME/rdbms/lib/config.c".
通常应为dba
% id uid=1030(oracle) gid=1030(dba)
可以如果"gid" 是 "dba" ， "config.s" or "config.c"
里面应该有: /* 0x0008 15 */ .ascii "dba\0"
如果没有添加目前的操作系统用户到dba 组，或则手工编辑更改config.c并且:%relink oracle
(6).所需要的文件系统是否正确mount
%mount
(7) 目前身份是否是"root" 并且操作系统环境变量 "USER", "USERNAME", and "LOGNAME" 没有设置成"root".
root用户是特例,除非当前组是dba 组,否则不能connect internal.
把root用户当前组改为dba组：
# newgrp dba
-----最好不要以root管理数据库;
(8).检查"/etc/group" :
是否存在重复行
% grep dba /etc/group
dba::1010:
dba::1100:
如果有，删掉没有用的。
(9).确信停掉的instance没有占用内存资源
比如：ipcs -b
T ID KEY MODE OWNER GROUP SEGSZ
Shared Memory:
m 0 0x50000ffe --rw-r--r-- root root 68
m 1601 0x0eedcdb8 --rw-r----- oracle dba 4530176
可以看到1601 被oracle 使用，删掉.
-------注意是否启动了多个instance
% ipcrm -m 1601
(10).如果同时还有ora-12705 错误检查一下环境变量：
"ORA_NLS", "ORA_NLS32", "ORA_NLS33" ，"NLS_LANG".
(11).检查 "ORACLE_HOME" and "LD_LIBRARY_PATH 环境变量：
$ LD_LIBRARY_PATH=$ORACLE_HOME/lib
$ export LD_LIBRARY_PATH
$ ORACLE_HOME=/u01/app/oracle/product/8.0.4
$ export ORACLE_HOME
(12).当前的instance 所再的磁盘是否有足够的磁盘空间
df -k
(13).用户对/etc/passwd 是否有读权限。
(14).如果使用mts 方式，确信你的连接使用dedicade server 方式。
(15).安装ORACLE所需操作系统补丁是否打全。ORACLE 是否已经补丁到最新
txfy 回复于：2003-08-07 10:25:15
最简单方法将init(sid).ora里的remote_login_passwordfile = exclusive改为remote_login_passwordfile = none重起数据库应该就可以了利用操作系统权限认证来进入sqlplus不用输用户名口令了。
airlily 回复于：2003-08-07 14:55:02
select * from v$parameter；
O7_DICTIONARY_ACCESSIBILITY的值应该是 TRUE。
二二 回复于：2003-08-07 17:50:41
Solving ORA-01031 errors
In order to start up an Oracle database using the connect internal or connect / as sysdba syntax, the currently logged in user must be in the ORA_DBA Windows group, if the operating system is Windows NT or 2000, or in the "dba" group if the operating system is Unix.
This error message frequently occurs after an initial installation of Oracle on Windows NT or 2000 as, if you've followed the Oracle installation instructions correctly, you've probably installed Oracle using the Windows userid Administrator, so this userid will have been automatically added to the ORA_DBA group, but your normal login userid won't have been.
If you are not in the appropriate group you will get the following error when you try to start Oracle:
C:\>;sqlplus /nolog
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Sep 17 10:12:32 2001
(c) Copyright 2000 Oracle Corporation. All rights reserved.
SQL>; connect / as sysdba
ERROR:
ORA-01031: insufficient privileges
or maybe an Enter password prompt.
See also What does connect / as sysdba mean for information about other variants of this syntax which may also give Enter password prompts.
To add yourself (or someone else) to the ORA_DBA group on Windows.
Select the Windows Control Panel, then Users and Passwords
If you're not currently logged in as Administrator, or as a user in the local Administrator group on this PC/server, you will be prompted to enter the userid and password of Administrator, or someone in this group, to obtain sufficient rights to perform user administration tasks. (Hint: some organisations to do not set local administrator passwords, so leaving it blank may work. Other organisations might use the password: administrator !)
You will be presented with a panel showing currently defined users for this PC/server. Click the Advanced tab, and select Advanced User Management.
You will be presented with a panel showing a tree structure of local users and groups. Click on the Groups folder.
This displays a list of the currently defined groups. There should be an ORA_DBA group. Double-click this. Select Add member.
You will be presented with a panel of locally defined userids. If the userid you want to add to the group is defined here, just select it.
If the userid you want is not defined here, it may be a network-defined userid you need. Change the location used to select users from by selecting a new location from the pull-down menu for the "Look in" entry field. Select the network you normally log in to.
As you're currently working as the local Administrator id, you may now need to enter your own network userid and password to prove that you belong to that network and are therefore authorised to select names from it.
You will now be presented with a list of network names, which (hopefully) should contain yours. Select it.
OK everything and close all windows.
You should now be able to connect internal or connect / as sysdba when logged in normally, and so should be able to stop and start the database etc.
To add yourself (or someone else) to the "dba" group on Unix.
This is easy: just edit the /etc/passwd file to change the default group for the user to be the groupid of the "dba" group, or edit the /etc/group file to add the specified userid to the list of users under the "dba" group entry.
You will need to be root to do this.
What does connect / as sysdba mean?
connect / as sysdba is current (Oracle 8i, and later) syntax which replaces the older connect internal syntax, and has exactly the same function.
This syntax is used to connect to the Oracle database with very powerful privileges. In particular, it allows the user to stop and start the database.
When it is used, Oracle does not check the password held for the user in the database's data-dictionary but instead checks that the current operating system userid is defined in the ORA_DBA group (if the OS is Windows NT/2000) or the "dba" group (if the OS is Unix). If the user is in this group, then he is allowed to connect. If he isn't in this group, he will get a ORA-01031: insufficient privileges error message, or an Enter password prompt.
The reason the data dictionary password is not used is that Oracle needs some mechanism of checking that the user is sufficiently authorised to start the database which works even when the database is down. Being a member of this group is sufficient proof that you are authorised to stop and start the database, since only the system administrator (Administrator on Windows, root on Unix) can add users to this group.
The connect / as sysdba technique can therefore be used as a method of logging in as SYS or SYSTEM when you've forgotten both their passwords: Use connect / as sysdba to connect as SYS, (assuming that your operating system userid is in the Windows ORA_DBA or Unix dba group), then change the SYS and SYSTEM passwords.
Note that there are other variants of the connect / as sysdba syntax, which I recommend you do not use, as they have misleading syntax
The statement:
connect anything/anything as sysdba
has exactly the same effect as connect / as sysdba, ie. it logs you in as SYS with stop and start database privileges. The supplied userid and password are ignored. No password prompt is given providing the current operating system userid is in the appropriate group.)
The statement:
connect sys as sysdba
(or, indeed, connect anything as sysdba) causes an Enter password prompt to be issued (even if the operating system userid is in the appropriate group), and the password held in the data-dictionary must then be entered to complete the login. This is probably a bug: I suspect the command parser detects that there is no / in the command and therefore that no password has been supplied, before it recognises that no password is, in fact, necessary because the as sysdba syntax has been used.) If the database is up, the valid password for the user must be entered. I suspect, but haven't tested it yet, that if the database is down, then whatever is entered as the password will be rejected because Oracle will be unable to validate it.
ERROR:
ORA-01031: insufficient privileges
什么情况下可以不输入用户名/密码？
zorrol 回复于：2003-08-07 10:19:46
小弟有些总结请参考，并提宝贵意见
#############################################
#
# NAME: troubleshoot connect internal.txt
#
# DESCRIPTION:
# connect internal
# connect / as sysdba 要口令问题：
# refer (METALINK,ORACLEDOC),
# metalink search words(connect internal)
# NOTE:
#
# modifiy (MM/DD/YY) AUTHOR
# 05/03/03 ZORROL
#############################################
————————————————————
1。检查sqlnet.ora 文件.
sqlnet.ora 文件损坏或格式不对可以导致出现该问题。
sqlnet.ora 文件可能存放路径为
$TNS_ADMIN/sqlnet.ora
如果没有设置$TNS_ADMIN默认在$ORACLE_HOME/network/admin/sqlnet.ora
或
$HOME/sqlnet.ora
(1). 可以从别的机器拷贝一个文件过来，注意备份原来的sqlnet.ora。
---检查sqlnet.ora 文件内容
(2). 检查SQLNET.AUTHENTICATION_SERVICES
如果没有使用dblink.检查该行并设置
SQLNET.AUTHENTICATION_SERVICES = (BEQ,NONE)
(3). SQLNET.CRYPTO_SEED
在unix 下不需要该参数。如果存在该行，注释掉或删掉
(4).AUTOMATIC_IPC
如果该参数为 ON，将强制使用"TWO_TASK" 连接
最好设置为OFF
AUTOMATIC_IPC = OFF
2.检查相关文件的权限配置。
找到$TNS_ADMIN/*
$ cd $TNS_ADMIN
$ chmod 644 sqlnet.ora tnsnames.ora listener.ora
$ ls -l sqlnet.ora tnsnames.ora listener.ora
-rw-r--r-- 1 oracle dba 1628 Jul 12 15:25 listener.ora
-rw-r--r-- 1 oracle dba 586 Jun 1 12:07 sqlnet.ora
-rw-r--r-- 1 oracle dba 82274 Jul 12 15:23 tnsnames.ora
3.检查操作系统相关设置。
(1). $ORACLE_HOME环境变量是否设置正确
% cd $ORACLE_HOME
% pwd
如果错误，请重新设置：
sh or ksh: ----------
$ ORACLE_HOME=
$ export ORACLE_HOME
Example:
$ ORACLE_HOME=/u01/app/oracle/product/7.3.3
$ export ORACLE_HOME
csh: ----
% setenv ORACLE_HOME
% setenv ORACLE_HOME /u01/app/oracle/product/7.3.3
另外$ORACLE_HOME路径应为实际路径，不应是目录连接（ln -s)
(2) $ORACLE_SID是否设置正确；
% echo $ORACLE_SID
(3).确信没有设置$TWO_TASK
检查 "TWO_TASK" 是否设置：
sh, ksh or on HP/UX only csh:
-----------------------------------
env |grep -i two
- or -
echo $TWO_TASK
csh:
----
setenv |grep -i two
如果有返回行比如:
TWO_TASK=
- or -
TWO_TASK=PROD
就需要取消着这些环境变量设置 ：
sh or ksh:
----------
unset TWO_TASK
csh:
----
unsetenv TWO_TASK
(4) 检查oracle 文件的权限:
% cd $ORACLE_HOME/bin
% ls -l oracle
权限应为：rwsr-s--x, or 6751.
如果不是：
% chmod 6751 oracle
(5). 检查当前所连接的操作系统用户是否是"osdba" 并且已经定义在：
"$ORACLE_HOME/rdbms/lib/config.s"
or
"$ORACLE_HOME/rdbms/lib/config.c".
通常应为dba
% id uid=1030(oracle) gid=1030(dba)
可以如果"gid" 是 "dba" ， "config.s" or "config.c"
里面应该有: /* 0x0008 15 */ .ascii "dba\0"
如果没有添加目前的操作系统用户到dba 组，或则手工编辑更改config.c并且:%relink oracle
(6).所需要的文件系统是否正确mount
%mount
(7) 目前身份是否是"root" 并且操作系统环境变量 "USER", "USERNAME", and "LOGNAME" 没有设置成"root".
root用户是特例,除非当前组是dba 组,否则不能connect internal.
把root用户当前组改为dba组：
# newgrp dba
-----最好不要以root管理数据库;
(8).检查"/etc/group" :
是否存在重复行
% grep dba /etc/group
dba::1010:
dba::1100:
如果有，删掉没有用的。
(9).确信停掉的instance没有占用内存资源
比如：ipcs -b
T ID KEY MODE OWNER GROUP SEGSZ
Shared Memory:
m 0 0x50000ffe --rw-r--r-- root root 68
m 1601 0x0eedcdb8 --rw-r----- oracle dba 4530176
可以看到1601 被oracle 使用，删掉.
-------注意是否启动了多个instance
% ipcrm -m 1601
(10).如果同时还有ora-12705 错误检查一下环境变量：
"ORA_NLS", "ORA_NLS32", "ORA_NLS33" ，"NLS_LANG".
(11).检查 "ORACLE_HOME" and "LD_LIBRARY_PATH 环境变量：
$ LD_LIBRARY_PATH=$ORACLE_HOME/lib
$ export LD_LIBRARY_PATH
$ ORACLE_HOME=/u01/app/oracle/product/8.0.4
$ export ORACLE_HOME
(12).当前的instance 所再的磁盘是否有足够的磁盘空间
df -k
(13).用户对/etc/passwd 是否有读权限。
(14).如果使用mts 方式，确信你的连接使用dedicade server 方式。
(15).安装ORACLE所需操作系统补丁是否打全。ORACLE 是否已经补丁到最新
txfy 回复于：2003-08-07 10:25:15
最简单方法将init(sid).ora里的remote_login_passwordfile = exclusive改为remote_login_passwordfile = none重起数据库应该就可以了利用操作系统权限认证来进入sqlplus不用输用户名口令了。
airlily 回复于：2003-08-07 14:55:02
select * from v$parameter；
O7_DICTIONARY_ACCESSIBILITY的值应该是 TRUE。
二二 回复于：2003-08-07 17:50:41
Solving ORA-01031 errors
In order to start up an Oracle database using the connect internal or connect / as sysdba syntax, the currently logged in user must be in the ORA_DBA Windows group, if the operating system is Windows NT or 2000, or in the "dba" group if the operating system is Unix.
This error message frequently occurs after an initial installation of Oracle on Windows NT or 2000 as, if you've followed the Oracle installation instructions correctly, you've probably installed Oracle using the Windows userid Administrator, so this userid will have been automatically added to the ORA_DBA group, but your normal login userid won't have been.
If you are not in the appropriate group you will get the following error when you try to start Oracle:
C:\>;sqlplus /nolog
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Sep 17 10:12:32 2001
(c) Copyright 2000 Oracle Corporation. All rights reserved.
SQL>; connect / as sysdba
ERROR:
ORA-01031: insufficient privileges
or maybe an Enter password prompt.
See also What does connect / as sysdba mean for information about other variants of this syntax which may also give Enter password prompts.
To add yourself (or someone else) to the ORA_DBA group on Windows.
Select the Windows Control Panel, then Users and Passwords
If you're not currently logged in as Administrator, or as a user in the local Administrator group on this PC/server, you will be prompted to enter the userid and password of Administrator, or someone in this group, to obtain sufficient rights to perform user administration tasks. (Hint: some organisations to do not set local administrator passwords, so leaving it blank may work. Other organisations might use the password: administrator !)
You will be presented with a panel showing currently defined users for this PC/server. Click the Advanced tab, and select Advanced User Management.
You will be presented with a panel showing a tree structure of local users and groups. Click on the Groups folder.
This displays a list of the currently defined groups. There should be an ORA_DBA group. Double-click this. Select Add member.
You will be presented with a panel of locally defined userids. If the userid you want to add to the group is defined here, just select it.
If the userid you want is not defined here, it may be a network-defined userid you need. Change the location used to select users from by selecting a new location from the pull-down menu for the "Look in" entry field. Select the network you normally log in to.
As you're currently working as the local Administrator id, you may now need to enter your own network userid and password to prove that you belong to that network and are therefore authorised to select names from it.
You will now be presented with a list of network names, which (hopefully) should contain yours. Select it.
OK everything and close all windows.
You should now be able to connect internal or connect / as sysdba when logged in normally, and so should be able to stop and start the database etc.
To add yourself (or someone else) to the "dba" group on Unix.
This is easy: just edit the /etc/passwd file to change the default group for the user to be the groupid of the "dba" group, or edit the /etc/group file to add the specified userid to the list of users under the "dba" group entry.
You will need to be root to do this.
What does connect / as sysdba mean?
connect / as sysdba is current (Oracle 8i, and later) syntax which replaces the older connect internal syntax, and has exactly the same function.
This syntax is used to connect to the Oracle database with very powerful privileges. In particular, it allows the user to stop and start the database.
When it is used, Oracle does not check the password held for the user in the database's data-dictionary but instead checks that the current operating system userid is defined in the ORA_DBA group (if the OS is Windows NT/2000) or the "dba" group (if the OS is Unix). If the user is in this group, then he is allowed to connect. If he isn't in this group, he will get a ORA-01031: insufficient privileges error message, or an Enter password prompt.
The reason the data dictionary password is not used is that Oracle needs some mechanism of checking that the user is sufficiently authorised to start the database which works even when the database is down. Being a member of this group is sufficient proof that you are authorised to stop and start the database, since only the system administrator (Administrator on Windows, root on Unix) can add users to this group.
The connect / as sysdba technique can therefore be used as a method of logging in as SYS or SYSTEM when you've forgotten both their passwords: Use connect / as sysdba to connect as SYS, (assuming that your operating system userid is in the Windows ORA_DBA or Unix dba group), then change the SYS and SYSTEM passwords.
Note that there are other variants of the connect / as sysdba syntax, which I recommend you do not use, as they have misleading syntax
The statement:
connect anything/anything as sysdba
has exactly the same effect as connect / as sysdba, ie. it logs you in as SYS with stop and start database privileges. The supplied userid and password are ignored. No password prompt is given providing the current operating system userid is in the appropriate group.)
The statement:
connect sys as sysdba
(or, indeed, connect anything as sysdba) causes an Enter password prompt to be issued (even if the operating system userid is in the appropriate group), and the password held in the data-dictionary must then be entered to complete the login. This is probably a bug: I suspect the command parser detects that there is no / in the command and therefore that no password has been supplied, before it recognises that no password is, in fact, necessary because the as sysdba syntax has been used.) If the database is up, the valid password for the user must be entered. I suspect, but haven't tested it yet, that if the database is down, then whatever is entered as the password will be rejected because Oracle will be unable to validate it.
解决Ora-01031的问题完美解决办法，可惜我的失败了。。
ORA-16038的解决(日志无法归档)
10G RAC DBCA报错 ORA-27504问题的解决
word启动失败的解决办法
错误解决：ORA-01036: 非法的变量名/编号
ORA-00604,ORA-12705解决办法
ASP.NET Post页面及验证视图状态MAC失败问题的正确解决办法
笔记本散热问题的解决办法!
真正让你读懂人民币汇率问题的好文章ora...
GDB调试例子（确实不错，帮助我解决了我遇到的问题）
如何解决TIME_WAIT过多的解决办法
如何解决IE窗口变小的解决办法
终于解决了ACTIVESYNC的连接问题
可惜不是你要的完美
可惜不是你要的完美
[转载]我在教育孩子问题上失败的体会
使用appfuse遇到的问题及解决办法
博客中常遇到的问题及解决办法
我解决了“月有圆缺”的谜团
要解决的问题
Excel解决的问题
针对ORA-01990的解决方法
[转]ORA-00257的解决之道
我的失败生活