关于SMON_SCN_TIME若干问题说明

Posted on 2012 年 03 月 28 日 by 惜分飞

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

授权用户访问数据字典三种方式

Posted on 2012 年 03 月 28 日 by 惜分飞

在很多时候，希望给用户最小的权限，让其访问系统数据字典，检查数据库的运行状态。这种事情在乙方的工作中非常常见。下面介绍三种方法处理这个问题
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非当前会话

Posted on 2012 年 03 月 27 日 by 惜分飞

使用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

Posted on 2012 年 03 月 27 日 by 惜分飞

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>  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

通过试验可以发现，在我们的数据库遇到异常，需要恢复通过推进scn来恢复的时候，不是推进的越大越好；如果推进的太大可能导致ORA-01052错误，一般建议是比当前不一致的scn稍微大一点
参考:http://www.dbsnake.net/two-scn-internal-points.html

收集统计信息出现ORA-00600[ksxprqfre3]

Posted on 2012 年 03 月 26 日 by 惜分飞

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一致

处理建议
针对官方没有给出明确的解决方案，因为该问题出现概率比较低，如果经常出现需要考虑升级数据库版本