【转载】Oracle常见问题及解决办法2_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 4349 | 回复: 0   主题: 【转载】Oracle常见问题及解决办法2        下一篇 
红与黑
注册用户
等级:上尉
经验:644
发帖:51
精华:1
注册:2013-2-25
状态:离线
发送短消息息给红与黑 加好友    发送短消息息给红与黑 发消息
发表于: IP:您无权察看 2013-2-25 17:58:20 | [全部帖] [楼主帖] 楼主

为什么sys超户登录失败了,报权限不够?
问题描述
在MDSP的数据库双机环境中,使用sys用户以sysdba登录,在本地登录是可以成功的。

~> sqlplus sys/oracle as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Wed Jun 27 18:26:28 2012
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options


当通过网络登录的时候失败,报权限不足。

~> sqlplus sys/oracle@sysdb as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Wed Jun 27 18:26:36 2012
Copyright (c) 1982, 2008, Oracle. All rights reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name:
ERROR:
ORA-01017: invalid username/password; logon denied


原因分析
由于本地登录与网络登录鉴权方式是不同的。使用网络登录的时候,需要有密码文件的存在,是通过密码文件的方式进行鉴权的。

SQL> show parameter remote_login
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE


查看密码文件试图,发现没有记录输出,表示密码文件不存在。这里可以发现问题的所在,由于密码文件不存在,导致了sys用户通过网络连接登录失败。

SQL> select * from v$pwfile_users;
no rows selected


解决方法
重新生成秘密文件,秘密文件需要在
1.在dbs目录下生成秘密文件。

% cd /opt/oracle/product/11g/db/dbs
oracle@mdsp1B:~/product/11g/db/dbs> orapwd file=orapwmdspdb entries=3 password=oracle force=y nosysdba=n


2.查看生成的密码文件。

oracle@mdsp1B:~/product/11g/db/dbs> ls
hc_mdspdb.dat initdw.ora initmdspdb.ora init.ora lkMDSPDB orapwmdspdb snapcf_mdspdb.f


3.查看密码文件对应的视图v$pwfile_users 。

SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE


4. 再次尝试登录,发现问题解决。

oracle@mdsp1B:~/product/11g/db/dbs> sqlplus sys/oracle@mdspdb as sysdba
SQL*Plus: Release 11.1.0.7.0 - Production on Wed Jun 27 19:26:30 2012
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options
SQL>


参考信息

SQL> alter system set remote_login_passwordfile=FALSE scope = spfile ;
alter system set remote_login_passwordfile=FALSE scope = spfile
*
ERROR at line 1:
ORA-00096: invalid value FALSE for parameter remote_login_passwordfile, must be
from among none, exclusive, shared
SQL> alter system set remote_login_passwordfile=SHARED scope = spfile ;
System altered.
SQL> show parameter remote_login
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string SHARED
oracle@mdsp1B:~/product/11g/db/network/admin> cd /opt/oracle/product/11g/db/dbs
oracle@mdsp1B:~/product/11g/db/dbs> orapwd file=orapwmdspdb entries=3 password=oracle force=y nosysdba=n
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP SYSAS
------------------------------ ----- ----- -----
SYS TRUE TRUE FALSE


创建密码文件的语法: orapwd file=orapwsidname entries=3 password=syspassword force=y nosysdba=n
示例:orapwd file=orapwmdspdb entries=3 password=oracle force=y nosysdba=n

ORACLE在线支持案例

________________________________________
Symptoms
Remote connections as SYSDBA are failing with ORA-1031 :
[oracle@oratest3-ro dbs]$ sqlplus sys/syspassword@sidname as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Dec 13 19:37:53 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
ERROR:
ORA-01031: insufficient privileges
The REMOTE_LOGIN_PASSWORDFILE parameter is set to EXCLUSIVE :
SQL> show parameter remote_login
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile string EXCLUSIVE
The view v$pfile_users shows that the user was not granted the sysdba role:
SQL> select * from v$pwfile_users;
USERNAME SYSDB SYSOP
-------- ----- -----
SYS FALSE TRUE
Changes
Created a passwordfile with nosysdba=y.
Cause
The issue is caused by the fact that the passwordfile was created with the NOSYSDBA option set to Y. When this is set to Y the remote SYSDBA connections are banned(the v$pwfile_users view shows that the user is not granted the SYSDBA role), irrespective to the fact that Database Vault is enabled or not.
The parameter nosysdba was added to the orapwd utilty as part of the Database Vault product, however it was made available with the Standard and Enterprise Edition databases that do not have Database Vault installed too, nosysdba=y can be used to lockout remote sysdba connections, when set it will still allow you to connect as SYSOPER, thus still leaving some purpose to having a remote login passwordfile at all.
Solution
1) If the privileges associated with SYSOPER are sufficient, login as SYSOPER, this will allow you to perform some basic task such as to shutdown or startup the database:
$ sqlplus sys/manager@v102 as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Aug 7 11:33:17 2008
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
ERROR:
ORA-01031: insufficient privileges
Enter user-name: sys/manager@v102 as sysoper
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>
2) Grant SYSDBA to the user.
3) Create the passwordfile with the NOSYSDBA option set to N :
[oracle@oratest3-ro dbs]$ orapwd file=orapwsidname entries=3 password=syspassword force=y nosysdba=n
[oracle@oratest3-ro dbs]$ sqlplus sys/syspassword@sidname as sysdba
SQL*Plus: Release 10.2.0.3.0 - Production on Thu Dec 13 19:41:12 2007
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
SQL>


oracle示例手动删除
1.用dbca删除单机数据库实例
从图形界面运行dbca,选择删除,按提示一路next;
或者去后台执行dbca -silent -deleteDatabase -sourceDB ${oracle_sid}

Look at the log file "/opt/oracle/cfgtoollogs/dbca/mdspdb.log" for ls


检查了一下卸载了很干净:/etc/oratab里面没有了SID的信息;
/opt/oracle/oracle/oradata/mdspdb目录下的数据文件了没有了;
/opt/oracle/oradata/目录下的东西也删干净了;
2.手动删除单机数据库实例
(1)关闭所有oracle进程,其实只要shutdown abort即可。因我的机器oracle没设置为开机自动启动,所以重启机器后oracle进程一个也没有,系统很是干净。
(2)删除实例数据文件和dump文件;

find $ORACLE_BASE/ -name $ORACLE_SID


在我系统里面显示如下,将这些目录直接删除;

/u01/app/oracle/admin/testdb
/u01/app/oracle/oradata/testdb
/u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/testdb
/u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/dbca/testdb


(3)删除其他配置文件

find $ORACLE_BASE/* -name '*[Bb][Tt][Ss][Dd][Bb]2*' | grep -v admin| grep -v oradata


将查找结果出现的文件也一一删除,当然你可以用xarg结合rm -rf删除。
例如:find ./ -type f | xargs rm

find $ORACLE_BASE/* -name '*[Mm][Dd][Ss][Pp][Dd][Bb][Nn][Jj]*' | grep -v admin| grep -v oradata|xargs rm -rf


注意:这里的*[Bb][Tt][Ss][Dd][Bb]2*是SID的全拼,如'*[Mm][Dd][Ss][Pp][Dd][Bb]*'
我find结果如下:

/u01/app/oracle/flash_recovery_area/testdb
/u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/emca/testdb
/u01/app/oracle/product/10.2.0/db_1/cfgtoollogs/dbca/testdb
/u01/app/oracle/product/10.2.0/db_1/oc4j/j2ee/OC4J_DBConsole_host_testdb
/u01/app/oracle/product/10.2.0/db_1/rdbms/log/alert_testdb.log
/u01/app/oracle/product/10.2.0/db_1/host_testdb
/u01/app/oracle/product/10.2.0/db_1/host_testdb/sysman/emd/state/A190EE260BF6B09EB580580728916A3B.alert_testdb.log
/u01/app/oracle/product/10.2.0/db_1/host_testdb/sysman/log/nmctestdb1521
/u01/app/oracle/product/10.2.0/db_1/dbs/alert_testdb.log
/u01/app/oracle/product/10.2.0/db_1/dbs/lktestdb
/u01/app/oracle/product/10.2.0/db_1/dbs/hc_testdb.dat
/u01/app/oracle/product/10.2.0/db_1/dbs/orapwtestdb
/u01/app/oracle/product/10.2.0/db_1/dbs/spfiletestdb.ora


(4)最重要的一步,如果你要重建的实例和刚删除实例的实例名一样的话,删除
/etc/oratab文件最后一段。
至此,已干净删除linux系统的oracle实例!
3.手动删除双机数据库实例
方法一:文明卸载
(1)去备机的上删除:rm -f "${oracle_home}/dbs/init${oracle_sid}.ora"
(2)去主机上执行dbca -silent -deleteDatabase -sourceDB ${oracle_sid}
(3)重设定时任务:crontab –l 先查看一下
编辑删除数据库的定时任务:mdsp1A:/etc # crontab -e
(4)去上机上删除双机资源组db_rg
(5)卸载浮动IP:ifconfig ${nic_info} down
先以root用户通过ifconfig查看一下

ORA-12505:监听不认识网络连接符中的SID
问题描述
执行sqlplus 从客户端连接���据库服务端,报如下错误。

% sqlplus sysdb/sysdb@sysdb
SQL*Plus: Release 11.1.0.7.0 - Production on Wed Mar 14 11:25:58 2012
Copyright (c) 1982, 2008, Oracle. All rights reserved.
ERROR:
ORA-12505: TNS:listener does not currently know of SID given in connect
descriptor
Enter user-name:


原因分析
查看错误码描述如下,根据错误描述,从下面几个纬度分析这个问题:
• 监听不能识别连接描述符中的SID,检查连接描述符的SID信息与数据库服务端的SID信息是否一致。
• 当监听刚启动,实例还没有注册到监听中时候,会发生这种情况。如果是这种情况,等待一会,再次尝试连接。
• 检查当前监听注册的SID信息。参考命令lsnrctl status 或lsnrctl services LISTENER
• 检查描述中的SID信息是否与监听中的一致。

~> oerr ora 12505
12505, 00000, "TNS:listener does not currently know of SID given in connect descriptor"
// *Cause: The listener received a request to establish a connection to a
// database or other service. The connect descriptor received by the listener
// specified a SID for an instance (usually a database instance) that either
// has not yet dynamically registered with the listener or has not been
// statically configured for the listener. This may be a temporary condition
// such as after the listener has started, but before the database instance
// has registered with the listener.
// *Action:
// - Wait a moment and try to connect a second time.
// - Check which instances are currently known by the listener by executing:
// lsnrctl services <listener name>
// - Check that the SID parameter in the connect descriptor specifies
// an instance known by the listener.
// - Check for an event in the listener.log file.


按照上面的思路检查,发现连接描述中的SID与监听中注册的SID信息不一致。

解决方法
修改连接描���符中的SID与监听中的SID信息一致。
再次尝试连接,成功。

启动监听报TNS-12571与TNS-00530错误
【问题描述】
启动监听报TNS-12571与TNS-00530错误,具体信息如下。

TNS for Linux: Version 11.1.0.7.0 - Production
TCP/IP NT Protocol Adapter for Linux: Version 11.1.0.7.0 - Production
Time: 20-SEP-2011 11:33:12
Tracing not turned on.
Tns error struct:
ns main err code: 12571
TNS-12571: TNS:packet writer failure
ns secondary err code: 12560
nt main err code: 530
TNS-00530: Protocol adapter error
nt secondary err code: 111


【原因分析】
查看oracle的帮助系统,获得关于错误码TNS-00530信息如下。

Known Issues:
TNS 530, SVR4 Error: 80 Name not unique on network
when starting the listener on Unix SVR4:
A file is created for each unique listener started in
the /var/tmp/o directory. If the listener is not shutdown
properly, the existence of the file will give you the
above described error. Simply delete the file: ie:
s{LISTENER NAME} in the /var/tmp/o directory.
Start the listener.
NB: SQL*Net 2.2 may use the directory /var/tmp/.oracle
instead - this is a hidden directory.


从上面的描述信息看出,当监听启动的时候,会在/var/tmp/.oracle目录下面创建文件。如果监听异常关闭后,再次启动时,会由于/var/tmp/.oracle目录下已经存在文件而导致启动监听失败,报TNS-00530错误。
所以删除/var/tmp目录下面的.oracle目录即可。

【解决方法】
1.以root用户登录数据库服务器。
2.删除/var/tmp目录下的.oracle文件夹。
注意:.oracle是隐藏目录

oracle & 和 ' 特殊字符处理 ( like 'GAC/&_%' escape '&'; 这里面的 / 居然将& 转义了 为什么?)
今天在导入一批数据到Oracle时,碰到了一个问题:Toad提示(plsql 也一样)要给一个自定义变量AMP赋值,一开始我很纳闷,数据是一系列的Insert语句,怎么会有自定义变量呢?后来搜索了一下关键字AMP发现,原来是因为在插入数据中有一个字段的内容如下:

http://mobile.three.com.hk/3DX?uid=0676&amp;sid=rt_060908


Oracle把这里的URL的参数连接符&amp;当成是一个自定义变量了,所以要求我给变量AMP赋值。怎么办呢?方法有三:

• 处理 & 字符
•方法一:在要插入的SQL语句前加上Set define off;与原SQL语句一起批量执行
我们在SQL*PLUS下执行 SQL> show all命令时,可以发现一个参数:define "&" (hex 26),如下图所示

......
concat "." (hex 2e)
copycommit 0
copytypecheck ON
define "&" (hex 26)
describe DEPTH 1 LINENUM OFF INDENT OFF
echo OFF
......


这个是Oracle里面用来识别自定义变量的设置,现在我们在SQL*PLUS下将其关闭:

SQL> Set define OFF;


然后再次执行导入脚本,OK!问题搞定。
注意:如果是在TOAD(plsql 也一样)中执行,建议在每一个要导入的脚本第一行加上前面那句关闭define的话,否则当你导入第二个含有特殊字符的脚本的时候,又会出错。
如果是在SQL*PLUS中执行,则只需要设置一次define OFF,后面就可以连续导入了。直到你重新设置define ON为止。

SET DEFINE OFF 的用法;
在SQL*Plus中默认的"&"表示替代变量,也就是说,只要在命令中出现该符号,SQL*Plus就会要你输入替代值。这就意味着你无法将一个含有该符号的字符串输入数据库或赋给变量,如字符串“SQL& 北京联动北方科技有限公司lus”系统会理解为以“SQL”打头的字符串,它会提示你输入替代变量Plus的值,如果你输入ABC,则最终字符串转化为“SQLABC”���
set define off 则关闭该功能,“&”将作为普通字符,如上例,最终字符就为“SQL& 北京联动北方科技有限公司lus”
set define off关闭替代变量功能
set define on 开启替代变量功能
set define * 将默认替代变量标志符该为“*”(也可以设为其它字符)

•方法二:在SQL语句中将'&'替换成chr(3 北京联动北方科技有限公司,因为chr(3 北京联动北方科技有限公司是‘&’的ASCII码
SQL> Select 'Tom' || chr(3 北京联动北方科技有限公司 || 'Jerry' from dual;

•方法三:分拆原来的字符串
SQL> Select 'Tom' || '&' || 'Jerry' from dual;

我们可以看到,方法一最为简便,而且效率也最高。方法二因为有一个调用函数的过程,所以性能稍差。方法三需要两次连接字符串,效率最差!

• 处理 ' 字符

那么如果字段的内容中包含了单引号要怎么插入呢?例如:It's fine。方法同样有三

•方法一:使用转义字符
SQL > Select 'test' || '''' from dual;
注意:这里的''''四个单引号是什么意思呢?首先第一个和最后一个都是Oracle中的字符串连接符,这个没有异议。那么第二个'和第三'又表示什么意思呢?第二个'是一个转义字符
第三个'才是我们真正的内容

•方法二:同样是使用转义字符,只不过方式不同而已
SQL > Select 'test ''' from dual;
注意:这里的第二个,第三个'就是我们上面方法一中提到的转义符和真正的内容

•方法三:在SQL中将'替换成chr(39),因为chr(39)是'的ASCII码

SQL > Select 'It' || chr(39) || 'fine' from dual;


以上 转自: http://lichangwei.**.com/?page=3&show_full=true (本文稍作了修改)

以上的 ' 转义字符,只能转变 ' 的意思,或者说' 只能转变 ' 为字符,不能对其他字符进行转义 如通配符的转义

• 通配符的转义
通配符的转义没有专门的字符,或者说,统配符的转义字符用 关键字: ESCAPE 指定,

如下面的t 为转义字符,

SQL> select * from user_tables where table_name like 'GACt_%' escape 't';
TABLE_NAME TABLESPACE_NAME CLUSTER_NAME
------------------------------ ------------------------------ ------------------------------
GAC_NITIN SYSTEM


而 ' 也可以指定为 通配符的转义字符 (必须先通过 ' 将 ' 转义)

SQL> select * from user_tables where table_name like 'GAC''_%' escape '''';
TABLE_NAME TABLESPACE_NAME CLUSTER_NAME
------------------------------ ------------------------------ ------------------------------
GAC_NITIN SYSTEM


以上两种情况可以理解,下面两种情况却费解了,
下面这条语句执行时,没有弹出对话框,也就是说plsql 没有将 & 当作表示替代变量的符号, 是 / 将其转义了吗? 为什么 / 能将其转义? escape 指定的不是 / 啊? 为什么他还能转义?

SQL> select * from user_tables where table_name like 'GAC/&_%' escape '&';
TABLE_NAME TABLESPACE_NAME CLUSTER_NAME
------------------------------ ------------------------------ --------------
GAC_NITIN SYSTEM


而下面的语句在执行是却弹出对话框要求为 _ 赋替代值, 这是否能说明 escape 没有将其转义, 还是因为到第一个& 时还不知道 escape
已经期指定为:转义字符?

SQL> select * from user_tables where table_name like 'GAC&_%' escape '&';
TABLE_NAME TABLESPACE_NAME CLUSTER_NAME
------------------------------ ------------------------------ ----------------
GAC_NITIN SYSTEM


很奇怪, like 'GAC/&_%' escape '&'; 这里面的 / 居然将& 转义了 为什么?

Oracle中强行断开用户连接的方法

首先查找目标用户的当前进程,注意是serial#而不是serial,网上有的介绍漏掉了#:

select sid,serial# from v$session where username='ERP';


使用此语句会返回一个进程列表,每行有两个数字,然后用数字替代下面的sid和serial

alter system kill session 'sid,serial';


例如

alter system kill session '222,123';


通过select 语句可能返回多行记录,所以要多次执行alter语句




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论