DB在linux下面使用图形化界面安装非常方便，绝对不会像oracle那样的恶心，需要配置没完没了参数，简单到安装过程用户都是用过图形化界面给你，让你输入密码就可以了。现在分享一组安装截图
关于SMON_SCN_TIME若干问题说明
1.SMON_SCN_TIME表基础知识
0)作用
由smon收集scn和time映射关系，用于flashback/查询scn和time对应关系等操作
1)保留条数
官方文档给出说明instance number N * 12 times per hour * 24 hours * 5 days = 1440N rows，因为每次的时间间隔不是非常准确的5分钟，所以在具体的条数在实际生产环境中有一定的出入
2)采集和删除
smon进程没5分钟采集一次插入到SMON_SCN_TIME表中，同时将删除历史数据(超过5天前数据)，采用下面语句
delete from smon_scn_time where thread=0 and time_mp = (select min(time_mp) from smon_scn_time where thread=0)，如果有时候index出了问题，导致该语句执行很慢
3)当查询scn对应time，如果scn超过SMON_SCN_TIME表范围，将提示错误；或者查询time对应的scn，如果超过范围也同样报错。
2.当SMON_SCN_TIME表出现问题时，truncate操作语句
--找出CLUSTER
Select dbms_metadata.get_ddl('TABLE','SMON_SCN_TIME','SYS') FROM DUAL ;
9i
truncate CLUSTER "SYS"."SMON_SCN_TO_TIME";
10g
truncate CLUSTER "SYS"."SMON_SCN_TO_TIME";
11g
truncate CLUSTER "SYS"."SMON_SCN_TO_TIME_AUX";
3.停止/开启smon进程收集scn信息
stop alter system set events '12500 trace name context forever, level 10'; start alter system set events '12500 trace name context off';
4.index异常处理
--找出index
Select index_name,index_type,owner from dba_indexes where table_name='SMON_SCN_TIME';
--对应index创建语句
Select dbms_metadata.get_ddl('INDEX','SMON_SCN_TIME_TIM_IDX','SYS') FROM DUAL;
--重建index
drop index smon_scn_time_scn_idx;
drop index smon_scn_time_tim_idx;
create unique index smon_scn_time_scn_idx on smon_scn_time(scn);
create unique index smon_scn_time_tim_idx on smon_scn_time(time_mp);
analyze table smon_scn_time validate structure cascade online;
--rebuild index
alter index sys.smon_scn_time_scn_idx rebuild online;
alter index sys.smon_scn_time_tim_idx rebuild online;
analyze table smon_scn_time validate structure cascade online;
补充知识点scn计算方法SCN=(SCN_WRP * 4294967296) + SCN_BAS
授权用户访问数据字典三种方式
在很多时候，希望给用户最小的权限，让其访问系统数据字典，检查数据库的运行状态。这种事情在乙方的工作中非常常见。下面介绍三种方法处理这个问题
0.select any table权限
这里说明select any table不能直接访问数据字典
SQL> conn / as sysdba
Connected.
SQL> create user xff_any identified by xifenfei;
User created.
SQL> grant connect,select any table to xff_any;
Grant succeeded.
SQL> conn xff_any/xifenfei
Connected.
SQL> select count(*) from dba_users;
select count(*) from dba_users
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select count(*) from chf.t_xff;
COUNT(*)
----------
2770
select any table默认情况下，只能访问业务的表，但是不能访问数据字典的数据。所以单纯的这个属性不能满足需求。
1.SELECT ANY DICTIONARY权限
SQL> conn / as sysdba
Connected.
SQL> create user xff_DICTIONARY identified by xifenfei;
User created.
SQL> grant connect to xff_DICTIONARY;
Grant succeeded.
SQL> conn xff_DICTIONARY/xifenfei
Connected.
SQL> select count(*) from dba_users;
select count(*) from dba_users
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn / as sysdba
Connected.
SQL> grant SELECT ANY DICTIONARY to xff_DICTIONARY;
Grant succeeded.
SQL> conn xff_DICTIONARY/xifenfei
Connected.
SQL> select count(*) from dba_users;
COUNT(*)
----------
32
SQL> select count(*) from chf.t_xff;
select count(*) from chf.t_xff
*
ERROR at line 1:
ORA-00942: table or view does not exist
这里可以看出SELECT ANY DICTIONARY权限只能访问数据字典，不能访问业务的表，访问业务的表需要另外授权
2.SELECT_CATALOG_ROLE角色
SQL> conn / as sysdba
Connected.
SQL> create user xff_CATALOG identified by xifenfei;
User created.
SQL> grant connect,SELECT_CATALOG_ROLE to xff_CATALOG;
Grant succeeded.
SQL> conn xff_CATALOG/xifenfei
Connected.
SQL> select count(*) from dba_users;
COUNT(*)
----------
33
SQL> select count(*) from chf.t_xff;
select count(*) from chf.t_xff
*
ERROR at line 1:
ORA-00942: table or view does not exist
这里可以看出SELECT_CATALOG_ROLE权限只能访问数据字典，不能访问业务的表，访问业务的表需要另外授权
3.O7_DICTIONARY_ACCESSIBILITY参数
SQL> conn / as sysdba
Connected.
SQL> create user xff_O7 identified by xifenfei;
User created.
SQL> grant connect to xff_o7;
Grant succeeded.
SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true;
alter system set O7_DICTIONARY_ACCESSIBILITY=true
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set O7_DICTIONARY_ACCESSIBILITY=true scope=spfile;
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 368263168 bytes
Fixed Size 1345016 bytes
Variable Size 306186760 bytes
Database Buffers 54525952 bytes
Redo Buffers 6205440 bytes
Database mounted.
Database opened.
SQL> conn xff_o7/xifenfei
Connected.
SQL> select count(*) from dba_users;
COUNT(*)
----------
34
这里通过O7_DICTIONARY_ACCESSIBILITY和SELECT ANY TABLE权限，实现访问业务数据和数据字典
跟踪ORACLE非当前会话
使用oradebug
session 1 --查询出需要跟踪会话v$process.pid session 2 SQL> oradebug SETORAPID 15 Unix process pid: 14851, image: oracle@xifenfei (TNS V1-V3) SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 4 Statement processed. --执行session 1 session 1 SQL> oradebug EVENT 10046 trace name context off Statement processed. SQL> oradebug TRACEFILE_NAME /u01/oracle/admin/xifenfei/udump/xff_ora_14851.trc
使用dbms_system.set_sql_trace_in_session
session 1 --查询出需要跟踪会话的sid，SERIAL# session 2 SQL> exec dbms_system.set_sql_trace_in_session(12,130,true); PL/SQL procedure successfully completed. --session 1执行sql session2关闭跟踪 SQL> exec dbms_system.set_sql_trace_in_session(12,130,false); PL/SQL procedure successfully completed. --通过session 1找出trace文件
ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified
SCN说明
1、Oracle的SCN在每秒16384次commit的情况下可以维持534年，每秒16384次commit是Oracle早先认为的任何系统的极限commit强度；
2、Oracle里SCN的起点是1988年1月1日；
3、_minimum_giga_scn=n的含义是把SCN往前推进到nG，但请注意，只有在SCN小于nG的时候才会用到这个隐含参数，反之则Oracle会置这个隐含参数于不顾。
求模拟_minimum_giga_scn值
这里通过时间差，大概的模拟_minimum_giga_scn小于当前时间和1988年1月1日的scn最大值(300>290)
SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')from dual;
TO_CHAR(SYSDATE,'YYYY-MM-DDHH24:MI:SS'
--------------------------------------
2012-03-18 04:27:50
SQL> select months_between(sysdate,to_date('19880101','yyyymmdd')) from dual;
MONTHS_BETWEEN(SYSDATE,TO_DATE('19880101','YYYYMMDD'))
------------------------------------------------------
290.55443
SQL> select 16384*60*60*24*31*300/(1024*1024*1024) SCN from dual;
SCN
----------
12260.7422
启动数据库测试
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. -------------------------------- *._minimum_giga_scn=12260 -------------------------------- SQL> startup pfile='/tmp/pfile' ORACLE instance started. Total System Global Area 236000356 bytes Fixed Size 451684 bytes Variable Size 201326592 bytes Database Buffers 33554432 bytes Redo Buffers 667648 bytes Database mounted. ORA-01052: required destination LOG_ARCHIVE_DUPLEX_DEST is not specified
分析ORA-01052
SQL> !oerr ora 1052 01052, 00000, "required destination LOG_ARCHIVE_DUPLEX_DEST is not specified" // *Cause: A valid destination for parameter LOG_ARCHIVE_DUPLEX_DEST was not // specified when parameter LOG_ARCHIVE_MIN_SUCCEED_DEST was set to // two. // *Action: Either specify a value for parameter LOG_ARCHIVE_DUPLEX_DEST, or // reduce the value for parameter LOG_ARCHIVE_MIN_SUCCEED_DEST to one. SQL> show parameter LOG_ARCHIVE_DUPLEX_DEST; NAME TYPE VALUE ------------------------------------ ---------- ------------------------------ log_archive_duplex_dest string SQL> show parameter LOG_ARCHIVE_MIN_SUCCEED_DEST NAME TYPE VALUE ------------------------------------ ---------- ------------------------------ log_archive_min_succeed_dest integer 1
这里可以看出，不是以为我上面的两个参数设置错误导致ORA-01052，而是因为推进scn过大导致ORA-01052的错误
减小_minimum_giga_scn测试
这里选择_minimum_giga_scn小于当前时间和1988年1月1日的scn最大值(280<290)
[sql]
SQL> select 16384*60*60*24*31*280/(1024*1024*1024) SCN from dual;
SCN
----------
11443.3594
SQL> shutdown immediate
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
-----------------------------
*._minimum_giga_scn=11443
-----------------------------
SQL> startup pfile='/tmp/pfile'
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> select to_char(dbms_flashback.get_system_change_number(),
2 '9999999999999999') from dual;
TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_
----------------------------------
12286827692251
SQL> select dbms_flashback.get_system_change_number()/(1024*1024*1024) from dual;
DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER()/(1024*1024*1024)
----------------------------------------------------------
11443
[/sql]
通过试验可以发现，在我们的数据库遇到异常，需要恢复通过推进scn来恢复的时候，不是推进的越大越好；如果推进的太大可能导致ORA-01052错误，一般建议是比当前不一致的scn稍微大一点
参考:http://www.dbsnake.net/two-scn-internal-points.html
收集统计信息出现ORA-00600[ksxprqfre3]
alert日志出现ORA-00600[ksxprqfre3]错误
Sun Mar 18 22:11:44 2012 Errors in file /oracle9/app/admin/offon/bdump/offon2_p001_8220790.trc: ORA-00600: internal error code, arguments: [ksxprqfre3], [0x11033D5A8], [], [], [], [], [], [] Sun Mar 18 22:11:44 2012 Errors in file /oracle9/app/admin/offon/bdump/offon2_p001_8220790.trc: ORA-00600: internal error code, arguments: [ksxprqfre3], [0x11033E178], [], [], [], [], [], []
通过这里可以发现，是由于并发进程导致异常
trace文件内容
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /oracle9/app/product/9.2.0
System name: AIX
Node name: zwq_offon2
Release: 3
Version: 5
Machine: 00CA56A44C00
Instance name: offon2
Redo thread mounted by this instance: 2
Oracle process number: 328
Unix process pid: 8220790, image: oracle@zwq_offon2 (P001)
*** 2012-03-18 22:11:43.994
*** SESSION ID:(381.10338) 2012-03-18 22:11:43.989
krqh = 11033d5a8
rqh trace:
Slot: 6 Op: 114 ksxpirqh: irqh requested
Slot: 7 Op: 130 ksxpirqh: rqh SUCC
Slot: 8 Op: 123 ksxpwait: receive mv to doneq
Slot: 9 Op: 121 ksxprcv: rqh found on doneq
Slot: 10 Op: 102 ksxprqfre: freed to queue
Slot: 11 Op: 101 ksxprqalo: allocated
Slot: 12 Op: 105 ksxpdteq: enqueue to delta q
Slot: 13 Op: 117 ksxpvsnd: send via ksxpisnd
Slot: 14 Op: 106 ksxpisnd: send requested
Slot: 0 Op: 107 ksxpisnd: send submit OK
Slot: 1 Op: 153 pre send completion cbk
Slot: 2 Op: 102 ksxprqfre: freed to queue
Slot: 3 Op: 114 ksxpirqh: irqh requested
Slot: 4 Op: 130 ksxpirqh: rqh SUCC
Slot: 5 Op: 153 pre send completion cbk
Dump of memory from 0x000000011033D5A8 to 0x000000011033D660
11033D5A0 00000001 1033D5A8 [.....3..]
11033D5B0 00000001 1033D5A8 00000001 1033D5B8 [.....3.......3..]
11033D5C0 00000001 1033D5B8 00000006 00000001 [.....3..........]
11033D5D0 00000000 00000000 00000000 00000000 [................]
11033D5E0 00000572 00000000 07000000 EE473B28 [...r.........G;(]
11033D5F0 00010000 00000000 00000001 1029CEA8 [.............)..]
11033D600 00000001 104382A0 07000000 F279B5A8 [.....C.......y..]
11033D610 00000000 00000572 00000000 00000000 [.......r........]
11033D620 00000000 00000000 00000000 13DCD225 [...............%]
11033D630 13DA7A25 00010001 26750000 00000000 [..z%....&u......]
11033D640 00000001 10339C70 6B996672 82997282 [.....3.pk.fr..r.]
11033D650 7B796665 69756A06 00000001 1033E228 [{yfeiuj......3.(]
*** 2012-03-18 22:11:43.996
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [ksxprqfre3], [0x11033D5A8], [], [], [], [], [], []
Current SQL statement for this session:
SELECT /*+ PIV_SSF */ SYS_OP_MSR(SYS_OP_COUNTCHG(SUBSTRB(ROWIDTOCHAR(A1.C0),1,15),1),COUNT(DISTINCT A1.C3),COUNT(*)) C0 FROM (SELECT /*+ INDEX_RRS(A2 "PK_TINEXWORK_DATA
") */ A2.ROWID C0,A2."WORKSHTSN" C1,A2."EXWORKSN" C2,SYS_OP_LBID(8574,'L',A2.ROWID) C3 FROM "OFFON"."TINEXWORK_DATA" PX_GRANULE(0, BLOCK_RANGE, DYNAMIC) A2) A1
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp+0148 bl ksedst 102973B94 ?
ksfdmp+0018 bl 01FD34D8
kgerinv+00e8 bl _ptrgl
kgeasnmierr+004c bl kgerinv 127C7F9D7226029B ?
9770A0AFAF4BDC0 ?
12E0BE826D694B2F ?
000000000 ? 000000010 ?
ksxprqfre+0054 bl kgeasnmierr 110006728 ? 110338C88 ?
1030069D4 ? 100000001 ?
000000002 ? 11033D5A8 ?
080000000 ?
7F7F7F7F7F7F7F7F ?
ksxpwait+06bc bl ksxprqfre 7000000D6D793D8 ?
ksliwat+02e0 bl ksxpwait 1014F4508 ? 1101FB1C8 ?
kslwaitns+001c bl ksliwat FFFFFFFFFFFC4B0 ? 000000004 ?
FFFFFFFFFFFC590 ? 11030BC60 ?
FFFFFFFFFFFC530 ?
7000000DDF1FBE0 ? 110006868 ?
kskthbwt+01b4 bl kslwaitns 000000002 ? 7000000EAEEB7A0 ?
000000000 ? 000000000 ?
000000868 ? 7000000DAFEFB30 ?
7000000F4979B10 ?
kslwait+005c bl kskthbwt FFFFFFFFFFFC6E0 ?
2422424800000000 ?
1015EDD34 ? 110305194 ?
FFFF0000000003 ? 000000000 ?
000000001 ? FFFFFFFFFFFF920 ?
ksxprcv+0184 bl kslwait 102970928 ? 000000006 ?
000000000 ? 000000000 ?
110305148 ? 110006728 ?
kxfpqidqr+0600 bl ksxprcv 11029CEA8 ? 000000008 ?
FFFFFFFFFFFD020 ?
FFFFFFFFFFFCFC0 ?
162DFEF4180 ? 000000000 ?
000000000 ? 000000000 ?
kxfpqdqr+02c0 bl kxfpqidqr 11029CF80 ? 11029CF20 ?
FFFFFFFFFFFD7C0 ?
7000000EAE2DA30 ?
kxfxgs+0050 bl kxfpqdqr 101518AF0 ? 000002000 ?
FFFFFFFFFFFD9F0 ? 000000000 ?
kxfxmai+0100 bl kxfxgs 102970928 ? 000000060 ?
7000000EEF059F0 ?
70000000006D670 ?
kxfprdp+05b0 bl _ptrgl
opirip+0390 bl kxfprdp
opidrv+0300 bl opirip 000000018 ? 0101FB1C8 ?
000000000 ?
sou2o+0028 bl opidrv 320C000000 ? 0A00E8B50 ?
000000000 ?
main+01a4 bl 01FD2EF0
__start+0098 bl main 000000000 ? 000000000 ?
--------------------- Binary Stack Dump ---------------------
SO: 7000000cf267198, type: 4, owner: 7000000cd2a53e0, flag: INIT/-/-/0x00
(session) trans: 0, creator: 7000000cd2a53e0, flag: (48000041) USR/- -/-/-/-/-/-
DID: 0002-0148-003F8809, short-term DID: 0000-0000-00000000
txn branch: 0
oct: 0, prv: 0, sql: 0, psql: 0, user: 25/MONITOR
O/S info: user: maint1, term: , ospid: 8220790, machine: zwq_acc2
program: oracle@zwq_offon2 (P001)
application name: SQL*Plus, hash value=3669949024
waiting for 'PX Deq: reap credit' blocking sess=0x0 seq=182 wait_time=0
=0, =0, =0
temporary object counter: 0
这里可以得出信息如下：
1)系统环境AIX 5.3 9.2.0.8.0 RAC
2)报错的语句是收集TINEXWORK_DATA表相关统计信息
3)是由于SQL*Plus收集统计信息使用并发导致该错误
查询MOS发现
Bug 5887047: ORA-00600 [KSXPRQFRE3] TRACE WHEN GATHERING OPTIMIZER STATISTICS描述相符：
1）数据库版本9.2.0.8
2）收集统计信息操作导致
3）STACK TRACE一致
处理建议
针对官方没有给出明确的解决方案，因为该问题出现概率比较低，如果经常出现需要考虑升级数据库版本
Oracle Database常用补丁集Patch号
Patchset / PSU Patch Number
11.2.0.4 13390677 11.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER 11.2.0.3.8 16902043 DATABASE PATCH SET UPDATE 11.2.0.3.8 (INCLUDES CPUOCT2013) 11.2.0.3.7 16619892 DATABASE PATCH SET UPDATE 11.2.0.3.7 (INCLUDES CPUJUL2013) 11.2.0.3.6 16056266 DATABASE PATCH SET UPDATE 11.2.0.3.6 (INCLUDES CPUAPR2013) 11.2.0.3.5 14727310 DATABASE PATCH SET UPDATE 11.2.0.3.5 (INCLUDES CPUJAN2013): 11.2.0.3.4 14275605 DATABASE PATCH SET UPDATE 11.2.0.3.4 (INCLUDES CPUOCT2012) 11.2.0.3.3 13923374 DATABASE PATCH SET UPDATE 11.2.0.3.3 (INCLUDES CPU JUL2012): 11.2.0.3.2 13696216 DATABASE PATCH SET UPDATE 11.2.0.3.2 (INCLUDES CPU APR2012) 11.2.0.3.1 13343438 DATABASE PATCH SET UPDATE 11.2.0.3.1 (INCLUDES CPU JAN2012) 11.2.0.3 10404530 11.2.0.3.0 PATCH SET FOR ORACLE DATABASE SERVER 11.2.0.2.11 16619893 DATABASE PATCH SET UPDATE 11.2.0.2.11 (INCLUDES CPUJUL2013) 11.2.0.2.10 16056267 DATABASE PATCH SET UPDATE 11.2.0.2.10 (INCLUDES CPUAPR2013) 11.2.0.2.9 14727315 DATABASE PATCH SET UPDATE 11.2.0.2.9 (INCLUDES CPUJAN2013): 11.2.0.2.8 14275621 DATABASE PATCH SET UPDATE 11.2.0.2.8 (INCLUDES CPUOCT2012) 11.2.0.2.7 13923804 DATABASE PATCH SET UPDATE 11.2.0.2.7 (INCLUDES CPU JUL2012) 11.2.0.2.6 13696224 DATABASE PATCH SET UPDATE 11.2.0.2.6 (INCLUDES CPU APR2012) 11.2.0.2.5 13343424 DATABASE PATCH SET UPDATE 11.2.0.2.5 (INCLUDES CPU JAN2012) 11.2.0.2.4 12827726 DATABASE PSU 11.2.0.2.4 (INCLUDES CPUOCT2011) 11.2.0.2.3 12419331 DATABASE PSU 11.2.0.2.3 (INCLUDES CPUJUL2011) 11.2.0.2.2 11724916 DATABASE PSU 11.2.0.2.2 (INCLUDES CPUAPR2011) 11.2.0.2.1 10248523 DATABASE PSU 11.2.0.2.1 11.2.0.2 10098816 11.2.0.2.0 PATCH SET FOR ORACLE DATABASE SERVER 11.2.0.1.6 12419378 DATABASE PSU 11.2.0.1.6 (INCLUDES CPUJUL2011) 11.2.0.1.5 11724930 DATABASE PSU 11.2.0.1.5 (INCLUDES CPUAPR2011) 11.2.0.1.4 10248516 DATABASE PSU 11.2.0.1.4 (INCLUDES CPUJAN2011) 11.2.0.1.3 9952216 DATABASE PSU 11.2.0.1.3 (INCLUDES CPUOCT2010) 11.2.0.1.2 9654983 DATABASE PSU 11.2.0.1.2 (INCLUDES CPUJUL2010) 11.2.0.1.1 9352237 DATABASE PSU 11.2.0.1.1 11.1.0.7.16 16619896 [*] DATABASE PATCH SET UPDATE 11.1.0.7.16 (INCLUDES CPUJUL2013) 11.1.0.7.15 16056268 [*] DATABASE PATCH SET UPDATE 11.1.0.7.15 (INCLUDES CPUAPR2013) 11.1.0.7.14 14739378 [*] DATABASE PATCH SET UPDATE 11.1.0.7.14 (INCLUDES CPUJAN2013) 11.1.0.7.13 14275623 [*] DATABASE PATCH SET UPDATE 11.1.0.7.13 (INCLUDES CPUOCT2012) 11.1.0.7.12 13923474 DATABASE PATCH SET UPDATE 11.1.0.7.12 (INCLUDES CPU JUL2012) 11.1.0.7.11 13621679 DATABASE PATCH SET UPDATE 11.1.0.7.11 (INCLUDES CPU APR2012) 11.1.0.7.10 13343461 DATABASE PATCH SET UPDATE 11.1.0.7.10 (INCLUDES CPU JAN2012) 11.1.0.7.9 12827740 DATABASE PSU 11.1.0.7.9 (INCLUDES CPUOCT2011) 11.1.0.7.8 12419384 DATABASE PSU 11.1.0.7.8 (INCLUDES CPUJUL2011) 11.1.0.7.7 11724936 DATABASE PSU 11.1.0.7.7 (INCLUDES CPUAPR2011) 11.1.0.7.6 10248531 DATABASE PSU 11.1.0.7.6 (INCLUDES CPUJAN2011) 11.1.0.7.5 9952228 DATABASE PSU 11.1.0.7.5 (INCLUDES CPUOCT2010) 11.1.0.7.4 9654987 DATABASE PSU 11.1.0.7.4 (INCLUDES CPUJUL2010) 11.1.0.7.3 9352179 DATABASE PSU 11.1.0.7.3 (INCLUDES CPUAPR2010) 11.1.0.7.2 9209238 DATABASE PSU 11.1.0.7.2 (INCLUDES CPUJAN2010) 11.1.0.7.1 8833297 DATABASE PSU 11.1.0.7.1 (INCLUDES CPUOCT2009) 11.1.0.7 6890831 11.1.0.7.0 PATCH SET FOR ORACLE DATABASE SERVER 10.2.0.5.12 16619894 [*] DATABASE PATCH SET UPDATE 10.2.0.5.12 (INCLUDES CPUJUL2013): 10.2.0.5.11 16056270 [*] DATABASE PATCH SET UPDATE 10.2.0.5.11 (INCLUDES CPUAPR2013) 10.2.0.5.10 14727319 [*] DATABASE PATCH SET UPDATE 10.2.0.5.10 (INCLUDES CPUJAN2013): 10.2.0.5.9 14275629 [*] DATABASE PATCH SET UPDATE 10.2.0.5.9 (INCLUDES CPUOCT2012) 10.2.0.5.8 13923855 [*] DATABASE PATCH SET UPDATE 10.2.0.5.8 (INCLUDES CPU JUL2012) 10.2.0.5.7 13632743 [*] DATABASE PATCH SET UPDATE 10.2.0.5.7 (INCLUDES CPU APR2012) 10.2.0.5.6 13343471 [*] DATABASE PATCH SET UPDATE 10.2.0.5.6 (INCLUDES CPU JAN2012) 10.2.0.5.5 12827745 [*] DATABASE PSU 10.2.0.5.5 (INCLUDES CPUOCT2011) 10.2.0.5.4 12419392 DATABASE PSU 10.2.0.5.4 (INCLUDES CPUJUL2011) 10.2.0.5.3 11724962 DATABASE PSU 10.2.0.5.3 (INCLUDES CPUAPR2011) 10.2.0.5.2 10248542 DATABASE PSU 10.2.0.5.2 (INCLUDES CPUJAN2011) 10.2.0.5.1 9952230 DATABASE PSU 10.2.0.5.1 (INCLUDES CPUOCT2010) 10.2.0.5 8202632 10.2.0.5.0 PATCH SET FOR ORACLE DATABASE SERVER 10.2.0.4.17 16619897 [*] DATABASE PSU 10.2.0.4.17 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2013): 10.2.0.4.16 16056269 [*] DATABASE PSU 10.2.0.4.16 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUAPR2013) 10.2.0.4.15 14736542 [*] DATABASE PSU 10.2.0.4.15 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJAN2013): 10.2.0.4.14 14275630 [**] DATABASE PSU 10.2.0.4.14 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUOCT2012) 0.2.0.4.13 13923851 [*] DATABASE PSU 10.2.0.4.13 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2012) 10.2.0.4.12 12879933 [*] DATABASE PSU 10.2.0.4.12 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUAPR2012) 10.2.0.4.11 12879929 [*] DATABASE PATCH SET UPDATE 10.2.0.4.11 (PRE-REQ 10.2.0.4.4 | INCLUDES CPUJAN2012) 10.2.0.4.10 12827778 DATABASE PSU 10.2.0.4.10 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUOCT2011) 10.2.0.4.9 12419397 DATABASE PSU 10.2.0.4.9 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2011) 10.2.0.4.8 11724977 DATABASE PSU 10.2.0.4.8 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUAPR2011) 10.2.0.4.7 10248636 DATABASE PSU 10.2.0.4.7 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJAN2011) 10.2.0.4.6 9952234 DATABASE PSU 10.2.0.4.6 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUOCT2010) 10.2.0.4.5 9654991 DATABASE PSU 10.2.0.4.5 (REQUIRES PRE-REQUISITE 10.2.0.4.4 | INCLUDES CPUJUL2010) [overlay PSU] 10.2.0.4.4 9352164 DATABASE PSU 10.2.0.4.4 (INCLUDES CPUAPR2010) 10.2.0.4.3 9119284 DATABASE PSU 10.2.0.4.3 (INCLUDES CPUJAN2010) 10.2.0.4.2 8833280 DATABASE PSU 10.2.0.4.2 (INCLUDES CPUOCT2009) 10.2.0.4.1 8576156 DATABASE PSU 10.2.0.4.1 (INCLUDES CPUJUL2009) 10.2.0.4 6810189 10.2.0.4.0 PATCH SET FOR ORACLE DATABASE SERVER 10.2.0.3 5337014 10.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER 10.2.0.2 4547817 10.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER 10.1.0.5 4505133 10.1.0.5 PATCH SET FOR ORACLE DATABASE SERVER 10.1.0.4 4163362 10.1.0.4 PATCH SET FOR ORACLE DATABASE SERVER 10.1.0.3 3761843 10.1.0.3 PATCH SET FOR ORACLE DATABASE SERVER 9.2.0.8 4547809 9.2.0.8 PATCH SET FOR ORACLE DATABASE SERVER 9.2.0.7 4163445 9.2.0.7 PATCH SET FOR ORACLE DATABASE SERVER 9.2.0.6 3948480 9.2.0.6 PATCH SET FOR ORACLE DATABASE SERVER 9.2.0.5 3501955 ORACLE 9I DATABASE SERVER RELEASE 2 - PATCH SET 4 VERSION 9.2.0.5.0 9.2.0.4 3095277 9.2.0.4 PATCH SET FOR ORACLE DATABASE SERVER 9.2.0.3 2761332 9.2.0.3 PATCH SET FOR ORACLE DATABASE SERVER 9.2.0.2 2632931 9.2.0.2 PATCH SET FOR ORACLE DATABASE SERVER 9.0.1.5 3301544 9.0.1.5 PATCHSET 9.0.1.4 2517300 9.0.1.4 PATCH SET FOR ORACLE DATABASE SERVER 9.0.1.3 2271678 9.0.1.3. PATCH SET FOR ORACLE DATA SERVER 8.1.7.4 2376472 8.1.7.4 PATCH SET FOR ORACLE DATA SERVER 8.1.7.3 2189751 8.1.7.3 PATCH SET FOR ORACLE DATA SERVER 8.1.7.2 1909158 8.1.7.2.1 PATCH SET FOR ORACLE DATA SERVER
NOTE:
[*] 10.2.0.4 and 10.2.0.5 are now in extended support mode and PSU’s released after Aug 01,2011 will need ES License to download them.
参考:Quick Reference to Patchset Patch Numbers [ID 753736.1]
10.2.0.5出现ORA-00600[kcblasm_1]
今天朋友执行一条如下sql
SELECT YF,
KHBH,
MAX(KHXM),
MAX(YYBDM),
MAX(YYBMC),
MIN(ZJZH),
SUM(CASE
WHEN RQ = 20120321 THEN
NVL(ZJYE, 0)
ELSE
0
END),
SUM(CASE
WHEN RQ = 20120301 THEN
NVL(ZJYE, 0)
ELSE
0
END),
SUM(CASE
WHEN RQ = 20120321 THEN
NVL(ZQSZ, 0)
ELSE
0
END),
SUM(CASE
WHEN RQ = 20120301 THEN
NVL(ZQSZ, 0)
ELSE
0
END),
SUM(CASE
WHEN RQ = 20120321 THEN
NVL(ZC, 0)
ELSE
0
END),
SUM(CASE
WHEN RQ = 20120301 THEN
NVL(ZC, 0)
ELSE
0
END),
SUM(CASE
WHEN RQ = 20120321 THEN
NVL(DWJZ, 1)
ELSE
0
END),
SUM(CASE
WHEN RQ = 20120301 THEN
NVL(DWJZ, 1)
ELSE
0
END),
MAX(NVL(ZC, 0)) ZGZC,
SUM(NVL(ZRZC, 0)) ZRZC,
SUM(NVL(ZCZC, 0)) ZCZC,
SUM(NVL(FDYK, 0)) FDYK,
SUM(NVL(ZRGF, 0)) ZRGF,
SUM(NVL(ZCGF, 0)) ZCGF,
AVG(NVL(ZC, 0)) PJZC,
SUM(NVL(CJJE, 0)) CJJE,
SUM(NVL(YJ, 0)) YJ,
SUM(NVL(ZJFSS, 0)) ZJFSS,
SUM(NVL(ZYJ, 0)) ZYJ,
SUM(NVL(CJCS, 0)) CJCS
FROM CRM_FX2_KHZJHZ P
WHERE YF = 201203
GROUP BY YF, KHBH;
出现ORA-00600: internal error code, arguments: [kcblasm_1], [103], [], [], [], [], []错误
数据库版本
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi PL/SQL Release 10.2.0.5.0 - Production CORE 10.2.0.5.0 Production TNS for Linux: Version 10.2.0.5.0 - Production NLSRTL Version 10.2.0.5.0 - Production
通过MOS发现该错误在10.2.0.5上有相关描述
This problem is hit only in 10.2.0.5, up to PSU 10.2.0.5.4 in which problem is fixed. Problem is affecting 10.2.0.5 to 10.2.0.5.3. Cause This is is a known and common problem hit in 10.2.0.5, investigated and corrected in unpublished Bug 7612454. The problem was introduced in 10.2.0.5, by the fix of Bug:7523755. This has been corrected in PSU 10.2.0.5.4 and 11.2.
解决方法
To quickest way to the solution of this problem is to apply PSU 10.2.0.5.4, Patch 12419392 or later. All alternative solutions for this problem are listed below: • Upgrade the database to 11.2. OR • Apply 10.2.0.5.4 Patch Set Update (Patch 12419392) or later PSUs where bug is fixed. The available PSUs are mentioned in "10.2.0.5 Patch Set Updates - List of Fixes in each PSU" (Document 1337394.1) OR • Apply interim Patch 7612454 on top of 10.2.0.5 (10.2.0.5.0-10.2.0.5.3): o For UNIX / Linux platforms apply Patch 7612454 available for download on MOS. o For Windows platforms apply Patch 3 or higher. Please check Document 342443.1 for latest patches available for Windows on top of 10.2.0.5.
这个错误在10.2.0.5上很容易发生，官方也没有给出发生这个错误的原因，以及如何改写相关sql，给出的方案是升级到10.2.0.5.4或者更高。这里可以看出升级需要慎重，可能导致原库有小部分sql不能正常执行。
lob类型数据转换为系统文件
1.插入一条blob数据
SQL> create directory ULTLOBDIR as '/home/oracle';
Directory created.
SQL> create table blobtest(col1 BLOB);
Table created.
SQL> declare
a_blob BLOB;
2 3 bfile_name BFILE := BFILENAME('ULTLOBDIR','tt.txt.bak');
4 begin
5 insert into blobtest values (empty_blob())
6 returning col1 into a_blob;
7 dbms_lob.fileopen(bfile_name);
8 dbms_lob.loadfromfile(a_blob, bfile_name, dbms_lob.getlength(bfile_name));
9 dbms_lob.fileclose(bfile_name);
10 commit;
11 end;
12 /
PL/SQL procedure successfully completed.
SQL> select dbms_lob.getlength(col1) from blobtest;
DBMS_LOB.GETLENGTH(COL1)
------------------------
4829
SQL> !pwd
/home/oracle
SQL> !ls -l tt.txt.bak
-rw-r--r-- 1 oracle oinstall 4829 03-19 17:26 tt.txt.bak
2.创建存储过程
CREATE OR REPLACE PROCEDURE RETRIEVE_LOB_TO_FILE(TEMP_BLOB IN BLOB,
FILE_PATH IN VARCHAR2,
FILE_NAME IN VARCHAR2) IS
DATA_BUFFER RAW(32767);
POSITION INTEGER := 1;
FILEHANDLE UTL_FILE.FILE_TYPE;
ERROR_NUMBER NUMBER;
ERROR_MESSAGE VARCHAR2(100);
BLOB_LENGTH INTEGER;
CHUNK_SIZE BINARY_INTEGER := 32767;
BEGIN
BLOB_LENGTH := DBMS_LOB.GETLENGTH(TEMP_BLOB);
FILEHANDLE := UTL_FILE.FOPEN(FILE_PATH, FILE_NAME, 'wb', 1024);
WHILE POSITION < BLOB_LENGTH LOOP
DBMS_LOB.READ(TEMP_BLOB, CHUNK_SIZE, POSITION, DATA_BUFFER);
UTL_FILE.PUT_RAW(FILEHANDLE, DATA_BUFFER);
POSITION := POSITION + CHUNK_SIZE;
DATA_BUFFER := NULL;
END LOOP;
UTL_FILE.FCLOSE(FILEHANDLE);
EXCEPTION
WHEN OTHERS THEN
BEGIN
ERROR_NUMBER := SQLCODE;
ERROR_MESSAGE := SUBSTR(SQLERRM, 1, 100);
DBMS_OUTPUT.PUT_LINE('Error #: ' || ERROR_NUMBER);
DBMS_OUTPUT.PUT_LINE('Error Message: ' || ERROR_MESSAGE);
UTL_FILE.FCLOSE_ALL;
END;
END;
/
3.测试读取blob到系统
SQL> declare 2 tmp_blob blob default empty_blob(); 3 begin 4 dbms_lob.createtemporary(tmp_blob, true); 5 select col1 into tmp_blob from blobtest; 6 retrieve_lob_to_file (tmp_blob, 'ULTLOBDIR','xifenfei.txt'); 7 end; 8 / PL/SQL procedure successfully completed. SQL> !pwd /home/oracle SQL> !ls -l xifenfei.txt -rw-r--r-- 1 oracle oinstall 4829 03-20 23:44 xifenfei.txt
坏块分类与检测
很多人都对oracle都知道oracle坏块有逻辑坏块和物理坏块之分，其实根据物理和逻辑坏块的分类，还可以继续分下去；对于坏块的检查，很多人知道物理和逻辑坏块使用dbv和rman检测，那其他种类的坏块该怎么办呢？这篇文章整理自MOS，给大家整理个分析坏块的思路，在后续文章中，会给出各种坏块的解决思路
http://www.xifenfei.com/wp-content/uploads/2012/03/Corruption_block_type_and_analysis.pdf
附件:hout和hcheck