ORA-xxxxx – 第16页 – Database SOS

ORA-00600 [2662]

一、错误现象（alert日志中）
Errors in file /opt/oracle/admin/conner/udump/conner_ora_31607.trc:
ORA-00600: internal error code, arguments: [2662], [0], [897694446], [0], [897695488], [8388697], [], []

二、错误解释
ORA-600 [2662] “Block SCN is ahead of Current SCN”，说明当前数据库的数据块的SCN早于当前的SCN，主要是和存储在UGA变量中的dependent SCN进行比较，如果当前的SCN小于它，数据库就会产生这个ORA-600 [2662]的错误了。这个错误一共有五个参数，分别代表不同的含义
ORA-600 [2662] [a] [b] {c} [d] [e]
Arg [a] Current SCN WRAP
Arg [b] Current SCN BASE
Arg {c} dependent SCN WRAP
Arg [d] dependent SCN BASE
Arg [e] Where present this is the DBA where the dependent SCN came from.
注：897694446<897695488
三、错误原因
1.使用隐含参数_ALLOW_RESETLOGS_CORRUPTION后resetlogs打开数据库
2.硬件错误引起数据库没法写控制文件和重做日志文件
3.错误的部分恢复数据库
4.恢复了控制文件但是没有使用recover database using backup controlfile进行恢复
5.数据库crash后设置了_DISABLE_LOGGING隐含参数
6.在并行服务器环境中DLM存在问题

四、解决办法
1、如果SCN相差不多，可以通过多次重起数据库解决（每次加1）
2、通过10015 ADJUST_SCN事件来增进current SCN
1）计算level
1.1) Arg {c}* 4得出一个数值，假设为V_Wrap
1.2) 如果Arg [d]=0，则V_Wrap值为需要的level
Arg [d] < 1073741824，V_Wrap+1为需要的level Arg [d] < 2147483648，V_Wrap+2为需要的level Arg [d] < 3221225472，V_Wrap+3为需要的level 1.3）SCN被增进了1024*1024*1024*level（level*10 billion）
2）执行内部事件
alter session set events ‘10015 trace name adjust_scn level N’;
注：mount状态下执行（open下无效）
alert日志中会出现：
Sat Aug 20 15:41:07 2011
Debugging event used to advance scn to 107374182400

ORA-01244/ORA-01110解决

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-01244/ORA-01110解决

rman 恢复过程中出现以下错误

RMAN> recover database;
Starting recover at 20-AUG-11
using channel ORA_DISK_1
starting media recovery
archive log filename=/opt/oracle/product/9.2.0/db_1/dbs/arch1_13.dbf thread=1 sequence=13
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 08/20/2011 03:54:30
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover logfile '/opt/oracle/product/9.2.0/db_1/dbs/arch1_13.dbf'
ORA-00283: recovery session canceled due to errors
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 2: '/opt/oracle/oradata/xifenfei/xff01.dbf'

错误原因分析
在rman备份后，添加了数据文件，使用的是备份的控制文件进行恢复数据库导致（不能识别新的数据文件）
解决方法
通过sqlplus创建数据文件
SQL> alter database create datafile 2 as ‘/opt/oracle/oradata/xifenfei/xff01.dbf’;
Database altered.
然后继续在rman中执行恢复数据库操作
该情况说明
此中情况只有在oracle 9i中出现；在10g中，rman恢复过程会自动的创建新添加文件，见oracle 10g rman自动创建数据文件

ORA-00205问题处理

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-00205问题处理

1、启动数据库异常
SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 1260720 bytes
Variable Size 142607184 bytes
Database Buffers 16777216 bytes
Redo Buffers 7127040 bytes
ORA-00205: error in identifying control file, check alert log for more info
alert.log日志中记录
ALTER DATABASE MOUNT
Mon Jul 18 17:32:58 2011
ORA-00202: Message 202 not found; No message file for product=RDBMS, facility=ORA; arguments: [/u01/oradata/xienfei/control01.ctl]
ORA-27037: Message 27037 not found; No message file for product=RDBMS, facility=ORA
Linux Error: 2: No such file or directory
2、根据提示缺少控制文件，第一步是看看有没有冗余的控制文件，然后修改pfile或者复制控制文件处理，如果没有利用备份控制文件恢复
RMAN> restore controlfile from ‘/tmp/rman_1kmhorc2_1_1’;
Starting restore at 18-JUL-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=/u01/oradata/xienfei/control01.ctl
Finished restore at 18-JUL-11
3、恢复控制文件后，数据库至于mount状态
RMAN> alter database mount;
database mounted
released channel: ORA_DISK_1
4、添加归档日志到控制文件
RMAN> catalog start with ‘/u01/archive’;
Starting implicit crosscheck backup at 18-JUL-11
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: sid=155 devtype=DISK
Crosschecked 29 objects
Crosschecked 12 objects
Finished implicit crosscheck backup at 18-JUL-11
Starting implicit crosscheck copy at 18-JUL-11
using channel ORA_DISK_1
using channel ORA_DISK_2
Crosschecked 1 objects
Finished implicit crosscheck copy at 18-JUL-11
searching for all files in the recovery area
cataloging files…
no files cataloged
searching for all files that match the pattern /u01/archive
List of Files Unknown to the Database
=====================================
…………
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files…
cataloging done
List of Cataloged Files
=======================
…………
RMAN> exit
[oracle@node2 tmp]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.3.0 – Production on Mon Jul 18 17:43:47 2011
Copyright (c) 1982, 2006, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production
With the Partitioning, OLAP and Data Mining options
5、使用备份控制文件恢复
SQL> recover database using backup controlfile;
ORA-00283: recovery session canceled due to errors
ORA-01111: name for data file 8 is unknown – rename to correct file
ORA-01110: data file 8: ‘/u01/oracle/dbs/UNNAMED00008’
ORA-01157: cannot identify/lock data file 8 – see DBWR trace file
ORA-01111: name for data file 8 is unknown – rename to correct file
ORA-01110: data file 8: ‘/u01/oracle/dbs/UNNAMED00008’
6、发现控制文件后又新增数据文件，需要重命名控制文件中的新增数据文件
SQL> select file#,name from v$datafile where file#=8;
FILE# NAME
———- ———————————————-
8 /u01/oracle/dbs/UNNAMED00008
SQL> alter database rename file ‘/u01/oracle/dbs/UNNAMED00008’ to ‘/u01/oradata/xienfei/cfxff01.dbf’;
Database altered.
7、继续进行不完成恢复（利用归档日志）
SQL> recover database using backup controlfile;
ORA-00279: change 1158476 generated at 07/18/2011 16:43:27 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_1_756837539.arc
ORA-00280: change 1158476 for thread 1 is in sequence #1
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1158594 generated at 07/18/2011 16:44:01 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_2_756837539.arc
ORA-00280: change 1158594 for thread 1 is in sequence #2
ORA-00278: log file ‘/u01/archive/1_1_756837539.arc’ no longer needed for this
recovery
…………
ORA-00308: cannot open archived log ‘/u01/archive/1_7_756837539.arc’
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
8、根据提示，应该是归档日志恢复完成，需要利用联机日志继续恢复
SQL> recover database using backup controlfile;
ORA-00279: change 1201601 generated at 07/18/2011 17:29:19 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_7_756837539.arc
ORA-00280: change 1201601 for thread 1 is in sequence #7
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/oradata/xienfei/redo03.log
ORA-00310: archived log contains sequence 6; sequence 7 required
ORA-00334: archived log: ‘/u01/oradata/xienfei/redo03.log’
SQL> recover database using backup controlfile;
ORA-00279: change 1201601 generated at 07/18/2011 17:29:19 needed for thread 1
ORA-00289: suggestion : /u01/archive/1_7_756837539.arc
ORA-00280: change 1201601 for thread 1 is in sequence #7
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/oradata/xienfei/redo01.log
Log applied.
Media recovery complete.
9、resetlogs打开数据库
SQL> alter database open resetlogs;
Database altered.

ORA-01578坏块解决(2)

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-01578坏块解决(2)

ORA-01578坏块解决(1)续集
如果在坏块之前，有rman备份，可以使用rman的备份来进行恢复，确保数据不会被丢失
1、使用rman进行恢复
[oracle@ECP-UC-DB1 ~]$ $ORACLE_HOME/bin/rman target /
Recovery Manager: Release 10.2.0.4.0 – Production on Sun Aug 14 22:21:13 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: TEST (DBID=2056006906)
RMAN> blockrecover datafile 6 block 1477;
Starting blockrecover at 2011-08-14 22:21:16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00006
channel ORA_DISK_1: reading from backup piece /tmp/0fmk0ii5_1_1
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/tmp/0fmk0ii5_1_1 tag=TAG20110814T213357
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:02
starting media recovery
media recovery complete, elapsed time: 00:00:03
Finished blockrecover at 2011-08-14 22:21:23
2、检查坏块是否被恢复
RMAN> backup check logical validate datafile 6;
Starting backup at 2011-08-14 22:22:11
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00006 name=/opt/oracle/oradata/test/xifenfei01.dbf
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2011-08-14 22:22:12
RMAN> exit
Recovery Manager complete.
[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Aug 14 22:22:17 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
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> select file#,block#,blocks from v$database_block_corruption;
no rows selected
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ECP-UC-DB1 ~]$ dbv file =/opt/oracle/oradata/test/xifenfei01.dbf
DBVERIFY: Release 10.2.0.4.0 – Production on Sun Aug 14 22:22:38 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY – Verification starting : FILE = /opt/oracle/oradata/test/xifenfei01.dbf
DBVERIFY – Verification complete
Total Pages Examined : 2560
Total Pages Processed (Data) : 1372
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 48
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1140
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Highest block SCN : 1256690 (0.1256690)
3、验证数据是否正确
[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Aug 14 22:34:18 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
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> select count(*) from t_rep;
COUNT(*)
———-
49857
和ORA-01578坏块解决(1)中的模拟环境比较，数据恢复正确，坏块问题解决

ORA-01578坏块解决(1)

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-01578坏块解决(1)

一、创建测试表

SQL> create table t_rep as

2 select * from all_objects;

Table created.

SQL> select count(*) from t_rep;

COUNT(*)

———-

49857

二、使用bbed修改数据块

具体见bbed破坏数据文件

三、错误现象

1、sqlplus窗口

SQL> select count(*) from t_rep;

select count(*) from t_rep

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 6, block # 1477)

ORA-01110: data file 6: ‘/opt/oracle/oradata/test/xifenfei01.dbf’

2、alert.log文件中

Sun Aug 14 22:01:14 2011

Hex dump of (file 6, block 1477) in trace file /opt/oracle/admin/test/udump/test_ora_10785.trc

Corrupt block relative dba: 0x018005c5 (file 6, block 1477)

Bad check value found during buffer read

Data in bad block:

type: 6 format: 2 rdba: 0x018005c5

last change scn: 0x0000.001328ef seq: 0x2 flg: 0x04

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x28ef0602

check value in block header: 0x493

computed block checksum: 0x44b9

Reread of rdba: 0x018005c5 (file 6, block 1477) found same corrupted data

Sun Aug 14 22:01:15 2011

Corrupt Block Found

TSN = 6, TSNAME = XFF

RFN = 6, BLK = 1477, RDBA = 25167301

OBJN = 52727, OBJD = 52728, OBJECT = T_REP, SUBOBJECT =

SEGMENT OWNER = SYS, SEGMENT TYPE = Table Segment

四、验证是否真的坏块

1、dbv验证

[oracle@ECP-UC-DB1 ~]$ dbv file =/opt/oracle/oradata/test/xifenfei01.dbf

DBVERIFY: Release 10.2.0.4.0 – Production on Sun Aug 14 22:08:37 2011

DBVERIFY – Verification starting : FILE = /opt/oracle/oradata/test/xifenfei01.dbf

Page 1477 is marked corrupt

Corrupt block relative dba: 0x018005c5 (file 6, block 1477)

Bad check value found during dbv:

Data in bad block:

type: 6 format: 2 rdba: 0x018005c5

last change scn: 0x0000.001328ef seq: 0x2 flg: 0x04

spare1: 0x0 spare2: 0x0 spare3: 0x0

consistency value in tail: 0x28ef0602

check value in block header: 0x493

computed block checksum: 0x44b9

DBVERIFY – Verification complete

Total Pages Examined : 2560

Total Pages Processed (Data) : 1371

Total Pages Failing (Data) : 0

Total Pages Processed (Index): 0

Total Pages Failing (Index): 0

Total Pages Processed (Other): 48

Total Pages Processed (Seg) : 0

Total Pages Failing (Seg) : 0

Total Pages Empty : 1140

Total Pages Marked Corrupt : 1

Total Pages Influx : 0

Highest block SCN : 1256043 (0.1256043)

2、rman验证

RMAN> backup check logical validate datafile 6;

Starting backup at 2011-08-14 22:09:51

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=157 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00006 name=/opt/oracle/oradata/test/xifenfei01.dbf

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 2011-08-14 22:09:53

RMAN> exit

Recovery Manager complete.

[oracle@ECP-UC-DB1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Sun Aug 14 22:10:00 2011

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> select file#,block#,blocks from v$database_block_corruption;SQL> exec dbms_repair.skip_corrupt_blocks(‘SYS’,’T_REP’);

FILE# BLOCK# BLOCKS

———- ———- ———-

6 1477 1

五、跳过坏块读取其他数据

SQL> exec dbms_repair.skip_corrupt_blocks(‘SYS’,’T_REP’);

PL/SQL procedure successfully completed.

SQL> select skip_corrupt from dba_tables where table_name=’T_REP’;

SKIP_COR

——–

ENABLED

SQL> select count(*) from t_rep;

COUNT(*)

———-

49794

说明：数据发生丢失6号文件的1477块中的数据丢失

物化视图on prebuilt table

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：物化视图on prebuilt table

1、ORA-12059
执行语句：
CREATE MATERIALIZED VIEW mv_t2
on prebuilt TABLE
AS
SELECT * FROM SCOTT.emp
错误提示：
ORA-12059: prebuilt table “CHF”.”MV_T2″ does not exist
错误原因：
物化视图对应的表不存在（物化视图需要和表同名，结构相同）
解决方法：
CREATE TABLE mv_t2 AS
SELECT * FROM scott.emp WHERE 1=0;
2、ORA-23413
执行语句：
CREATE MATERIALIZED VIEW mv_t2
on prebuilt TABLE
WITH REDUCED PRECISION
refresh FAST on demand
AS
SELECT * FROM SCOTT.emp
错误提示：
ORA-23413: table “SCOTT”.”EMP” does not have a materialized view log
错误原因：
使用fast模式刷新物化视图，需要有物化视图日志
解决方法：
1）创建物化视图日志
CREATE MATERIALIZED VIEW LOG ON scott.emp ;
2）刷新模式改为force，其实实质是采用了complete刷新模式
3、ORA-12058
执行语句（mv log是基于rowid，表无主键）
CREATE MATERIALIZED VIEW mv_t2
on prebuilt TABLE
WITH REDUCED PRECISION
refresh FAST on DEMAND
WITH ROWID
AS
SELECT * FROM SCOTT.emp
错误提示：
ORA-12058: materialized view cannot use prebuilt table
错误原因：
fast刷新模式不能基于rowid进行
解决方法：
1）删除mv log，表添加主键，采用基于主键模式重新建mv log和物化视图
DROP MATERIALIZED VIEW mv_t1;
ALTER TABLE mv_t2 ADD PRIMARY KEY (EMPNO);
CREATE MATERIALIZED VIEW LOG ON scott.emp;
CREATE MATERIALIZED VIEW mv_t2
on prebuilt TABLE
WITH REDUCED PRECISION
refresh FAST on DEMAND
–WITH ROWID
AS
SELECT * FROM SCOTT.emp;
2）刷新模式改为force，其实实质是采用了complete刷新模式
4、总结
1）在执行创建物化视图之前，需要先创建同名、同结构表
2）如果使用fast模式刷新（疑惑在线重定义可以通过rowid实现，为什么直接通过物化视图就不可以），物化视图同表需要主键，需要物化视图日志
3）通过drop mv，发现同名表还存在，和on prebuilt table本质区别

ORA-01012: not logged on

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-01012: not logged on

今天接到现场反馈，数据库出现ORA-01012: not logged on错误提示，这个问题上次也出现过一次，当时按照网上说的方法，直接重启数据库了，问题解决了，同时也导致因为数据库重启，现场破坏，而alert日志中无任何异常信息，所以不知从何处下手分析。这次我上数据库准备查看时，发现数据库已经正常，监控也显示正常，说明数据库已经恢复正常。从此我推理这个问题应该是外部因素导致，而不是数据库本身的bug，从而决定要找出该问题的原因来。有个重要的因素，该数据库是我几个月前因为undo损坏做过恢复的，查看相关参数，发现processes是默认值150，是不是该值导致的不敢肯定，因为一般process超了会报ORA-00020错误，而这次只有ORA-01012。但是心中还是没有底，总感觉这个的可能性最大，于是想通过试验来证实下自己的想法
1、数据库版本（10g结果相同）
SQL> select * from v$version where rownum=1;
BANNER
—————————————————————-
Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production
2、创建pfile，修改processes，重启数据库，查看processes值
SQL> select name,value from v$parameter where name=’processes’;
NAME VALUE
——————– ——————–
processes 30
3、查看系统processes数量
SQL> select count(*) from v$process;
COUNT(*)
———-
13
4、添加数据库连接数（为了增加process数目）
[oracle@localhost ~]$ sqlplus chf/xifenfei
SQL*Plus: Release 9.2.0.4.0 – Production on Wed Jul 27 23:11:50 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 – Production
SQL> !
5、重复3和4，发现当process数量为29时出现激动人心一幕
5.1)继续访问数据库
[oracle@localhost ~]$ sqlplus system/xifenfei
SQL*Plus: Release 9.2.0.4.0 – Production on Wed Jul 27 23:12:06 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:
ORA-00020: maximum number of processes (%s) exceeded
Enter user-name:
5.2)使用其他用户访问
[oracle@localhost ~]$ sqlplus /nolog
SQL*Plus: Release 9.2.0.4.0 – Production on Wed Jul 27 23:12:49 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-01012: not logged on
SQL> conn chf/xifenfei
ERROR:
ORA-00020: maximum number of processes (%s) exceeded
[oracle@localhost ~]$ sqlplus “/ as sysdba”
SQL*Plus: Release 9.2.0.4.0 – Production on Wed Jul 27 23:29:52 2011
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected.
SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-01012: not logged on
现象说明：
1)终于发现了ORA-01012错误，期待了很久。发现只有当sys登录系统，对数据库进行查询或者操作之时才会出现ORA-01012，其他用户只要一登录数据库就会提示ORA-00020错误。
2)当process达到数据库最大限时，sysdba登录系统是也显示不正常
2.1）当使用sqlplus “/ as sysdba”登录时，缺少：
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 – Production
2.2）当使用conn / as sysdba登录时，提示为：
Connected to an idle instance.

ORA-01031: insufficient privileges

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-01031: insufficient privileges

今天接到开发反馈，说数据库动态sql创建表语句无法执行，提示ORA-01031: insufficient privileges
1、数据库版本
SQL> select * from v$version where rownum=1;
BANNER
—————————————————————-
Oracle9i Enterprise Edition Release 9.2.0.4.0 – Production
2、创建用户并授权
SQL> create user test identified by xifenfei;
User created.
SQL> grant connect,resource to test;
Grant succeeded.
3、查看权限
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE=’TEST’;
GRANTEE PRIVILEGE ADM
—————————— —————————————- —
TEST UNLIMITED TABLESPACE NO
SQL> SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE=’TEST’;
GRANTEE GRANTED_ROLE ADM DEF
—————————— —————————— — —
TEST CONNECT NO YES
TEST RESOURCE NO YES
SQL> CONN TEST/XIFENFEI
Connected.
SQL> SELECT * FROM SESSION_PRIVS;
PRIVILEGE
—————————————-
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER
PRIVILEGE
—————————————-
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
14 rows selected.
注：目前create table权限是通过resource role授予test用户的
4、创建存储过程（Authid Current_User）
SQL> create or replace procedure create_table(t varchar2) Authid Current_User
2 is
3 P_SQL VARCHAR2(300);
4 BEGIN
5 P_SQL := ‘create table ‘||t||’ as select * from user_tables where 1=0′;
6 EXECUTE IMMEDIATE P_SQL;
END; 7
8 /
Procedure created.
5、执行存储过程
SQL> EXEC CREATE_table(‘a’);
PL/SQL procedure successfully completed.
6、重建存储过程（不包括Authid Current_User）
SQL> create or replace procedure create_table(t varchar2)
2 –Authid Current_User
3 is
4 P_SQL VARCHAR2(300);
5 BEGIN
6 P_SQL := ‘create table ‘||t||’ as select * from user_tables where 1=0′;
7 EXECUTE IMMEDIATE P_SQL;
8 END;
9 /
Procedure created.
SQL> EXEC CREATE_table(‘b’);
BEGIN CREATE_table(‘b’); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at “TEST.CREATE_TABLE”, line 7
ORA-06512: at line 1
7、授权（create table权限）
SQL> conn / as sysdba
Connected.
SQL> grant create table to test;
Grant succeeded.
SQL> conn test/xifenfei
Connected.
SQL> EXEC CREATE_table(‘c’);
PL/SQL procedure successfully completed.
8、查看结果
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
—————————— ——- ———-
A TABLE
C TABLE
9、查询资料
默认情况下，在调用存储过程用户的角色是不起作用的，即在执行存储过程时只有Public权限。所以如果被调用的存储过程中如果有execute immediate ‘create table..’语句，将会引发ORA-01031: insufficient privileges错误。
存储过程分为两种，即DR(Definer’s Rights ) Procedure和IR(Invoker’s Rights ) Procedure。为什么会有两种存储过程呢？其实考虑完下面的问题就清楚了。比如说用户hrch创建了删除表tar_table的存储过程drop_table()，当用户hrch调用时，即删除用户hrch下的表tar_table；如果是另一个用户scott调用呢？是删除用户scott下的tar_table表呢，还是删除用户hrch下的tar_table呢？另外，如果存储过程中包含建表语句，不管是用户hrch还是用户scott调用都会失败，因为Public没有建表权限，除非为Public grant建表权限。
10、动态sql中执行create table解决方案（10g也存在同样问题）
1)加上Authid Current_User
2)直接赋予create table to 该用户
11、匿名块的动态sql不在此限制中
SQL> conn / as sysdba
Connected.
SQL> SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE=’TEST’;
GRANTEE PRIVILEGE ADM
—————————— —————————————- —
TEST UNLIMITED TABLESPACE NO
SQL> CONN TEST/XIFENFEI
Connected.
SQL> DECLARE
2 P_SQL VARCHAR2(300);
3 BEGIN
4 P_SQL := ‘create table t_t as select * from user_tables where 1=0’;
5 EXECUTE IMMEDIATE P_SQL;
6 END;
7 /
PL/SQL procedure successfully completed.

触发器引起ORA-04091

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：触发器引起ORA-04091

原因：在行级触发器中，不能查询自身表
场景重现：通过触发器实现test_count表中统计test表中行数

--创建子表
create table TEST
(id NUMBER, name varchar2(100), primary key (id));
--创建统计表
create table test_count (test_count int);
--创建触发器
CREATE OR REPLACE TRIGGER T_TEST
  AFTER INSERT OR DELETE ON TEST
  FOR EACH ROW
DECLARE
  A NUMBER;
BEGIN
  SELECT  COUNT(*) INTO A FROM TEST;
  UPDATE TEST_COUNT SET TEST_COUNT = A;
END T_TEST;

模拟错误：

INSERT INTO TEST (ID,NAME)VALUES(2,'abc');
ORA-04091: table CHF.TEST is mutating, trigger/function may not see it
ORA-06512: at "CHF.T_TEST", line 2
ORA-04088: error during execution of trigger 'CHF.T_TEST'

处理方法：
通过自治事务实现（修改触发器）

CREATE OR REPLACE TRIGGER T_TEST
  AFTER INSERT OR DELETE ON TEST
  FOR EACH ROW
DECLARE
  A NUMBER;
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  SELECT  COUNT(*) INTO A FROM TEST;
  UPDATE TEST_COUNT SET TEST_COUNT = A;
  COMMIT;
END T_TEST;

PRAGMA AUTONOMOUS_TRANSACTION
当前的触发器作为已有事务的子事务运行，子事务自治管理，子事务的commit、rollback操作不影响父事务的状态

WARNING: inbound connection timed out (ORA-3136)

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：WARNING: inbound connection timed out (ORA-3136)

1、现象
alert文件中
Mon Jun 27 11:12:34 2011
WARNING: inbound connection timed out (ORA-3136)
sqlnet.log文件中
Fatal NI connect error 12170.
VERSION INFORMATION:
TNS for Linux: Version 10.2.0.4.0 – Production
Oracle Bequeath NT Protocol Adapter for Linux: Version 10.2.0.4.0 – Production
TCP/IP NT Protocol Adapter for Linux: Version 10.2.0.4.0 – Production
Time: 27-JUN-2011 11:12:34
Tracing not turned on.
Tns error struct:
ns main err code: 12535
TNS-12535: TNS:operation timed out
ns secondary err code: 12606
nt main err code: 0
nt secondary err code: 0
nt OS err code: 0
Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=211.155.227.20)(PORT=2104))
2、原因
Whenever default timeouts are assigned to a parameter, there may be cases where this default does not work well with a particular application. However, some type of timeout on the connection establishment is necessary to combat Denial of Service attacks on the database. In this case, SQLNET.INBOUND_CONNECT__TIMEOUT and INBOUND_CONNECT_TIMEOUT_listenername were given default values of 60 seconds in Oracle 10.2. It is these timeout values that can cause the errors described in this note.
Also note that it is possilbe the reason the database is slow to authenticate, may be due to an overloaded Oracle database or node.
3、解决
1). set INBOUND_CONNECT_TIMEOUT_listenername=0 in listener.ora
2). set SQLNET.INBOUND_CONNECT_TIMEOUT = 0 in sqlnet.ora of server.
3). stop and start both listener and database.
4). Now try to connect to DB and observe the behaviour
4、具体操作
4.1）修改INBOUND_CONNECT_TIMEOUT_listenername
4.1.1)lsnrctl命令修改
LSNRCTL> set inbound_connect_timeout 0
LSNRCTL>save_config
4.1.2)vi修改
修改listener.ora文件，加入: INBOUND_CONNECT_TIMEOUT_listenername=0
4.2）修改SQLNET.INBOUND_CONNECT__TIMEOUT
修改sqlnet.ora文件，加入: SQLNET.INBOUND_CONNECT__TIMEOUT=0

分类目录归档：ORA-xxxxx