不必纠结于session_cached_cursors的内存损耗

session cursor cache 特性旨在减少软解析soft parse的性能损耗。通过将session_cached_cursor参数设置为一个大于0 的值启动会话session缓存游标的机制。

重复的对统一语句的解析调用经过3次以后将该语句的游标缓存移入到session cursor cache中,后续的解析调用将在session 缓存中找到对应的游标并不再需要重复打开该游标(ASKMACLEAN.COM)。

注意仅仅是那些重复引发三次解析调用的语句将被缓存,该机制避免了随机游标和硬绑定的游标浪费缓存。

主要UGA中的KKSUG结构定位缓存中的游标状态。kksug.kksugscc存放了session cached cursor信息数据。kksug.kksugscc.kkssclru 代表游标链表,kksug.kksugscc.kkssccnt代表session cursor cache中的游标数量,kksug.kksugscc.kksschit代表cursor cache的命中次数,即游标在session cursor cache中(ASKMACLEAN.COM)正好被找到的次数。

 

一般不需要特别去关心session_cached_cursors参数的内存损耗,一来这些session cursor cache中的栏位slot是可以被重用的,而且每一个slot也不会占用太多内存,一般在几十个字节左右。

同时如上所述KKSUG是位于UGA中的,所以在dedicated server mode独立服务器模式下session cursor cache在PGA=>UGA中, 在共享服务器模式下shared server mode下载SGA=>UGA中。

 

 

@+-> structkkssc @{
/* Session cached cursors */
 /* open_cursors */ 
/* List of open curdef's */ 
/* Number of cursors open */ 
/* List of closed curdef's */ 
/* Cached frames and binds list */
kkssc kksugscc; ub4 kksugmoc; 
kgglk kksugocl; ub4 kksugopc; 
kgglk kksugccl; 
kgglk kksugcfb;
@ /* front is the MRU side the tail is the LRU side */
@ kgglk kkssclru; 
/* linked list of cursors in session cursor cache */
@ kksccht *kksschst;
 /* hash table of cursors in the session cache */
@ ub4 kksscmcc; 
/* max number of cursors allowed in cache */
@ ub4 kkssccnt; 
/* count of cursors in session cursor cache */
@ ub4 kksschit; 
/* session cache cursor hits */
@ };

_CORRUPTED_ROLLBACK_SEGMENTS隐藏参数

_CORRUPTED_ROLLBACK_SEGMENTS(corrupted undo segment list)隐藏参数所独有的功能:

  • 在实例启动startup并open database的阶段_CORRUPTED_ROLLBACK_SEGMENTS所列出的undo segments(撤销段/回滚段)将不会被访问读取
  • 所有指向这些被_CORRUPTED_ROLLBACK_SEGMENTS列出的undo segments的事务都被认为已经提交了commit,和这个undo segments已经被drop时类似
  • 衷心地建议用_CORRUPTED_ROLLBACK_SEGMENTS这个参数打开数据库后导出数据并重建数据库,这个参数使用的后遗症可能很顽固
  • Oracle公司内部有叫做TXChecker的工具可以检查问题事务

 

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638   QQ号:47079569    邮箱:service@parnassusdata.com

 

_offline_rollback_segments 和 _corrupted_rollback_segments 均会造成的实例行为变化:

  • 以上2个参数所列出的Undo Segments(撤销段/回滚段)将不会被在线使用online
  • 在UNDO$数据字典基表中将体现为OFFLINE的记录
  • 在实例instance的生命周期中将不会再给新的事务分配使用
  • 参数所列出的Undo Segments列表上的活跃事务active transaction将即不被回滚亦不被标记为dead以便SMON去回滚(了解你所不知道的SMON功能(五):Recover Dead transaction)

_OFFLINE_ROLLBACK_SEGMENTS隐藏参数

_OFFLINE_ROLLBACK_SEGMENTS(offline undo segment list)隐藏参数(hidden parameter)的独有作用:

  • 在实例startup启动并open database的阶段仍将读取_OFFLINE_ROLLBACK_SEGMENTS所列出的Undo segments(撤销段/回滚段),若访问这些undo segments出现了问题则将在alert.log和其他TRACE中体现出来,但不影响实际的startup进程
    • 若查询数据块发现活跃的事务,并ITL指向对应的undo segments则:
    • 若读取undo segments的transaction table事务表发现事务已提交则做数据块的清除
    • 若读取发现事务仍活动未commit,则生成一个CR块拷贝
    • 若读取该undo segments存在问题(可能是corrupted讹误,可能是missed丢失)则产生一个错误并写出到alert.log,查询将异常终止
  • 若DML更新相关的数据块会导致服务进程为了恢复活跃事务而进入死循环消耗大量CPU,解决方法是通过可以进行的查询工作重建相关表

 

 

_offline_rollback_segments 和 _corrupted_rollback_segments 均会造成的实例行为变化:

  • 以上2个参数所列出的Undo Segments(撤销段/回滚段)将不会被在线使用online
  • 在UNDO$数据字典基表中将体现为OFFLINE的记录
  • 在实例instance的生命周期中将不会再给新的事务分配使用
  • 参数所列出的Undo Segments列表上的活跃事务active transaction将即不被回滚亦不被标记为dead以便SMON去回滚(了解你所不知道的SMON功能(五):Recover Dead transaction)

如何给其他会话设置session级别的参数

之前有同学问我如何才能给非自身的其他会话设置会话级别(alter session)的参数;在实际的Oracle管理过程中我们往往希望在不修改实例级别参数的情况下, 让部分session使用指定的参数值。

如果仅仅是修改本地会话的参数值,那么很好办,只需要alter session set parameter 即可,如:

 

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

SQL> select * from global_name;

GLOBAL_NAME
-------------------------------------------------------
www.askmaclean.com & www.askmaclean.com

SQL> alter session set workarea_size_policy=MANUAL;

Session altered.

 

但是如果是要给非本地会话的其他会话设置session级别的参数的话,那么就比较麻烦了。

 

就我所知,存在2种方法,这里抛砖引玉一下:

 

1.  通过 AFTER LOGON ON Trigger登录后触发器实现部分用户或程序登录即触发session级别的参数被设置,如:

 

CREATE OR REPLACE TRIGGER AUTO_SESSION_PARAMETER
  AFTER LOGON ON MACLEAN.SCHEMA
BEGIN
    execute immediate 'alter session set optimizer_mode=RULE';
END;
/

Trigger created.

 

当有用户以MACLEAN账号登录时,会触发该Trigger在session级别设置optimizer_mode=RULE使用RBO优化器。

 

2.使用DBMS_SYSTEM包的SET_INT_PARAM_IN_SESSION和SET_BOOL_PARAM_IN_SESSION 2个存储过程,实际我在利用<dbms_system包加速imp导入数据时的索引建立> 一文中已经介绍过了如何利用SET_INT_PARAM_IN_SESSION优化imp导入数据时创建索引相关的会话级别参数。

 

SET_INT_PARAM_IN_SESSION 过程用以给其他SESSION设置值为整形类的参数,如db_file_multiblock_read_count,其具体用法:

 

dbms_system.set_int_param_in_session(
sid     IN NUMBER,
serial# IN NUMBER,
parnam  IN VARCHAR2,
intval  IN BINARY_INTEGER);

exec dbms_system.set_int_param_in_session(10, 161, 'sort_area_size', 1048576);

 

SET_BOOL_PARAM_IN_SESSION 过程用以给其他SESSION设置布尔类型的参数,如skip_unusable_indexes:

 

dbms_system.set_bool_param_in_session(
sid     IN NUMBER,
serial# IN NUMBER,
parnam  IN VARCHAR2,
bval    IN BOOLEAN);

exec dbms_system.set_bool_param_in_session(10, 161, 'skip_unusable_indexes', TRUE);

 

注意虽然有些parameter的值是TRUE或者False,但是这些参数实际可能是string字符串类型,而非boolean类型,具体可以通过show parameter来查看,如star_transformation_enabled参数默认虽然是FALSE,但是实际是一个String type的Parameter:

 

SQL> show parameter star_transformation_enabled

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
star_transformation_enabled          string      FALSE

 

遗憾的是DBMS_SYSTEM包仅仅提供了设置整形和布尔类值的过程, 对于字符类值的参数尚不支持对其他session的修改。 换句话说就是如star_transformation_enabled这类的会话参数,DBMS_SYSTEM包没有提供一个可用的过程来对非本地的其他会话修改, 到目前为止(版本11.2.0.3)都不支持这种做法。针对这类字符值参数,据我所知如果要达到我们上述的目的那么只能用触发器的方法。

 

实际上Metalink上已经有相关的Note说明了该软件缺陷”SET A CHARACTER-STRING VALUED PARAMETER IN ANOTHER USERS SESSION”:

 

Hdr: 8939043 11.1.0.7 RDBMS 11.1.0.7 SQL LANGUAGE PRODID-5 PORTID-267
Abstract: SET A CHARACTER-STRING VALUED PARAMETER IN ANOTHER USERS SESSION

Using DBMS_SYSTEM you can set a Boolean parameter with the
SET_BOOL_PARAM_IN_SESSION procedure.  Similarly, you can also set an Integer
parameter with the SET_INT_PARAM_IN_SESSION procedure. 

There is no procedure in dbms_system or in other package or any other direct
method to set this parameter.
so, it should be a direct method to simulate something like 

alter session <number_of_session> set cursor_sharing='EXACT';

We have a specific process that is running very slowly since upgrading to
11g.  If we set optimizer_features_enabled =11.1.0.7 the process runs well.  
We can not set this parameter at the database level though as it causes
online errors in other parts of the system.

Oracle中加速索引创建或重建的方法

Important parameters For Oracle BRM Application in 11gR2

什么是Oracle BRM?

BRM 指的是Billing and Revenue Management (BRM) ,是一套专门针对通信行业设计的集成的终端到终端的企业软件套件。

Oracle 公司在2006年收购了Portal Software软件公司后,成为了Portal BRM产品的拥有者。 BRM最早被叫做Infranet(版本6.5, 6.7和更早),之后被称作Portal(在版本7.0, 7.2, 7.3, 7.4时代), 官方第一次使用BRM这一代号是从版本7.3.1开始。

客户有一套BRM系统运行在4节点的Exadata X2-8 Half Rack上,数据库版本是11.2.0.1 。

但是这套系统使用了Exadata默认的配置参数,而没有使用BRM系统专用的初始化参数。

这里我有必要提一下,一般来说大型的应用程序(Application)特别是Oracle自身的产品都会有经过Oracle公司自身验证过的一套推荐参数,譬如说Oracle Ebs Suite 11i 最早是在Oracle database 8i/9i 上设计的,一般来说在安装介质上就会附带有11i 在databse 8i/9i上的推荐配置参数,而如果你要将11i 迁移到10g上那么就需要到MOS上找出是否这一软件组合已经经过Oracle公司的认证,如果认证了那么一般都会有推荐参数。

假设计划在Oracle Database 11gR2上部署Oracle EBS R12的应用,那么可以从MOS上找到<Interoperability Notes EBS R12 with Database 11gR2 [ID 1058763.1]> 这个指南。

其他一些非Oracle的应用程序产品,如Sap这类流行的大型应用,Oracle也会进行一系列的认证,但是未必就有非常完整的Recommended Parameter列表了,当然如果您是SAP的用户的话,也可以从Sap哪里获得必要的支持。

<Questions About BRM Support For Oracle DB 11GR2 And Exadata [ID 1319678.1]>介绍了BRM应用程序在11GR2+Exadata上的一些常见问题和推荐的参数设置,这里引用一下:

Applies to:

Oracle Communications Billing and Revenue Management – Version: 7.3.1.0.0 to 7.4.0.0.0 – Release: 7.3.1 to 7.4.0
Information in this document applies to any platform.

Purpose

The purpose is to address some common queries around the supportability of BRM with respect to 11gR2 plus Exadata.

Questions and Answers

1) About 10G client support: BRM(731 and 74) is certified against 11G R2 DB environment using the provided 10G R2 client software. We would like to know about the support for the client itself; because the 10G server environment is out of support end of this year(2011), so does this out of support phrase also apply to the client software.

From BRM point of view , YES , we will support customers using BRM 731 + 11gR2(server) + 10gR2(client)

2) We would like to know of any pre-requisite patches of the BRM application to support the 11G database.

On BRM, there is no specific patches needed for 11G support. You would still continue to use the dm_oracle10g.so library (using the 10gR2 DB client).

3) Is it correct that a base installation of oracle 11G Release 2 is needed, and no additional patches are needed on the DB ( to successfully support pipeline batch processing on the BRM side) ?

Yes, 11gR2 is enough for BRM. Please check with the database group if they would advice any later patches for performance improvements.

4) This question is more on the relationship between the Application (BRM) connecting with ExaData ; are there are no specific patches on Exadata with respect to BRM?

BRM is certified on Exadata . It is certified against 11gR2 only and with BRM 7.3.1 onwards. No DB patches are required from a BRM perspective. You would need to check with the Exadata team/documentation to see if that particular product has any pre-requisites.

5) The certification overview does not specially mention RAC support on 11G, can you confirm if RAC is supported on the 11G database environment?

Yes, we do support RAC also on 11G database.

6) Can you elaborate on the AQ consequences in running them on the 11G environment?

There is nothing about consequences that we can tell. However we do support it ( AQ ) even in 11g.

7) As per the recent press release at : http://www.oracle.com/us/corporate/press/364536 ; about the BRM certification and benchmarking against Exadata; this is referring to BRM 7.4 and not to 7.3.1 .  How about the same with respect to BRM731 in combination with Exadata?

We did not benchmark BRM 731 on Exadata; therefore, we cannot tell if it will show the same performance figures as seen in the tests on 74. However, the reason of good performance on BRM 7.4 is because Exadata offers an excellent database server capability, not that BRM 7.4 has made specific code change to take advantage of the Exadata hardware. For example, the optimization technique such as using FLASH disk to handle Redo log is equally applicable to BRM 731. Therefore, we envision that Exadata will also provide performance benefit to BRM 731.

8) Are there any specific settings we need to configure in BRM 7.3.1 or in Exadata itself besides the logical ones like DB hostname to work properly with Exadata and gain (more) of the advantages of Exadata?

Please see the white paper at the location “http://www.oracle.com/us/industries/communications/brm-exadata-performance-wp-362789.pdf”. This is also part of the press release mentioned before.
While there are no general guidelines about specific settings on BRM or Exadata ; below is a set of the DB configuration for the benchmark, if you may be interested. However, please note that this configurations work well for the benchmark, with the hardware setup that is specific to the benchmark. While doing similar testing at your end, it is advisable to review this configuration and obtain professional services opinion before deploying the configuration in a production system.

BRM DB Configuration :
Important parameters from init.ora in alphabetic order are:

_b_tree_bitmap_plans=false
_disk_sector_size_override=true
_file_size_increase_increment=2143289344
_gc_policy_time=0
_optimizer_skip_scan_enabled=false
cluster_database_instances=2
compatible=’11.2.0.2.0′
db_block_size=8192
db_cache_size=511101108224
fast_start_mttr_target=3600
lock_sga=true
log_buffer=1073741824
open_cursors=2000
optimizer_index_caching=90
optimizer_index_cost_adj=25
pga_aggregate_target=102400M
pre_page_sga=false
processes=2048
session_cached_cursors=400
sga_target=524288M
shared_pool_size=32768M
_b_tree_bitmap_plans=false so the BRM sqls don’t get executed using plans that involve bitmap conversions, which in general result in longer sql execution time for BRM.
_disk_sector_size_override=true so db objects like redo logs can be created in the Flash Disk Group using larger BLOCKSIZE like 4KB, which improves log flush efficiency from the default 512B BLOCKSIZE.
_file_size_increase_increment=2143289344 for faster db backup / restore.
_gc_policy_time=0 to disable DRM.
_optimizer_skip_scan_enabled=false so the BRM SQL does not get executed using plans that opt for index skip scan, which is usually not the optimal access path for BRM SQL.
compatible must be ‘11.2.0.2.0’ to get the DB created. Because the ASM Disk Groups were created
to be 11.2.0.2 compatible which enabled extra functionality, but restricted usage to databases with
this level of compatibility or higher.
pre_page_sga=false. False is the default setting of this parameter. Hugepage was configured for
the DB SGA.

 

可以看到以上对几个隐藏参数有了更具体的描述,值得注意的是_disk_sector_size_override和_gc_policy_time;

_gc_policy_time是11.1之后才出现的参数,他的前身是_gc_affinity_time(_gc_policy_time in 11g),最大的作用是禁用DRM
_disk_sector_size_override决定了创建在Flash Disk Group 上 redo log的BlockSize。

Script:Diagnostic Resource Manager

以下脚本可以用于诊断Oracle 10g以后的Resource Manager信息:

set echo on;
    set linesize 300;
    set pages 1000;
    set numwidth 10;
    set trimspool on;
    col VALUE for a30;
    col ATTRIBUTE for a15;
    col GRANTEE for a25;
    col CPU_METHOD for a15;
    col COMMENTS for a30;
    col MGMT_METHOD for a15;
    col STATUS for a10;
    alter session set nls_date_format='yyyy/mm/dd hh24:mi:ss';
    spool info.lst;
    SELECT SYSDATE FROM DUAL;
    SELECT * FROM DBA_RSRC_MANAGER_SYSTEM_PRIVS;
    SELECT * FROM DBA_RSRC_CONSUMER_GROUP_PRIVS;
    SELECT * FROM DBA_RSRC_CONSUMER_GROUPS;
    SELECT * FROM DBA_RSRC_PLANS;
    SELECT * FROM DBA_RSRC_PLAN_DIRECTIVES;
    SELECT * FROM V$RSRC_CONSUMER_GROUP;
    SELECT * FROM V$RSRC_CONSUMER_GROUP_CPU_MTH;
    SELECT * FROM V$RSRC_PLAN;
    SELECT * FROM V$RSRC_PLAN_CPU_MTH;
    SELECT * FROM DBA_RSRC_MAPPING_PRIORITY;
    SELECT * FROM DBA_RSRC_GROUP_MAPPINGS;
    -- For 10gR2 -------
    SELECT * FROM V$RSRC_CONS_GROUP_HISTORY;
    SELECT * FROM V$RSRC_PLAN_HISTORY;
    SELECT * FROM V$RSRC_SESSION_INFO;
    -- FOR 11gR1 -------
    SELECT * FROM V$RSRCMGRMETRIC
    order by BEGIN_TIME,END_TIME,CONSUMER_GROUP_ID;
    SELECT * FROM V$RSRCMGRMETRIC_HISTORY
    order by BEGIN_TIME,END_TIME,CONSUMER_GROUP_ID;
    spool off;

 create_sample_plan.sql
    ----------------------------------------------------------------------------
    set echo on
    begin
       dbms_resource_manager.create_pending_area();
    end;
    /
    begin
       dbms_resource_manager.create_plan(
          plan => 'ONLINE_PLAN',
          comment => 'Resource plan/method for Day Time On-Line sessions');
       dbms_resource_manager.create_plan(
          plan => 'BATCH_PLAN',
          comment => 'Resource plan/method for Night Time Batch sessions');
    end;
    /

opatch lsinventory -detail

 top -b -d 1 -n 3600 >> toplog
 sar -P ALL 1 3600 >> sarlog

ALTER SYSTEM SET resource_manager_plan='';

Oracle内部错误:ORA-07445[kcflfi()+466] [INT_DIVIDE_BY_ZERO]一例

一套Windows上的11.2.0.1单实例数据库在database open阶段出现了ORA-07445:core dump [kcflfi()+466] [INT_DIVIDE_BY_ZERO] [] [PC:0x500282E] [] []内部错误,具体的出错日志如下:

LOG CONTENT

=======================ALERT.LOG============================

Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
ARCH: Warning; less destinations available than specified
by LOG_ARCHIVE_MIN_SUCCEED_DEST init.ora parameter
Autotune of undo retention is turned on. 
IMODE=BR
ILAT =84
2011-08-01 13:13:47.068000 +08:00
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile C:\APP\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEG11R2.ORA
System parameters with non-default values:
  _spin_count              = 2000
  processes                = 500
  event                    = "10500 trace name context forever,level 8:10013 trace name context forever,level 10:
10015 trace name context forever,level 10"
  sga_max_size             = 600M
  shared_pool_size         = 152M
  large_pool_size          = 32M
  java_pool_size           = 4M
  streams_pool_size        = 0
  _db_file_direct_io_count = 12
  sga_target               = 0
  memory_target            = 0
  control_files            = "C:\APP\ORADATA\G11R2\CONTROLFILE\O1_MF_6VWCSH9J_.CTL"
  control_files            = "C:\APP\FLASH_RECOVERY_AREA\G11R2\CONTROLFILE\O1_MF_6VWCSHNF_.CTL"
  db_block_checksum        = "TRUE"
  db_block_size            = 8192
  db_cache_size            = 196M
  _shared_io_pool_size     = 0
  compatible               = "11.2.0.0.0"
  log_archive_dest_2       = "service=stdby optional lgwr sync affirm valid_for=(online_logfiles,all_roles)"
  log_buffer               = 10485760
  db_create_file_dest      = "C:\app\oradata"
  db_recovery_file_dest    = "C:\app\flash_recovery_area"
  db_recovery_file_dest_size= 500000M
  undo_tablespace          = "UNDOTBS1"
  _kgl_bucket_count        = 2
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  session_cached_cursors   = 300
  audit_file_dest          = "C:\APP\ADMIN\G11R2\ADUMP"
  optimizer_features_enable= "10.2.0.4"
  audit_trail              = "DB"
  cell_offload_plan_display= "ALWAYS"
  db_name                  = "G11R2"
  open_cursors             = 3000
  _optimizer_extended_cursor_sharing_rel= "NONE"
  pga_aggregate_target     = 300M
  diagnostic_dest          = "C:\APP"
2011-08-01 13:13:48.164000 +08:00
PMON started with pid=2, OS id=984 
VKTM started with pid=3, OS id=3656 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
GEN0 started with pid=4, OS id=5824 
DIAG started with pid=5, OS id=5832 
DBRM started with pid=6, OS id=2784 
PSP0 started with pid=7, OS id=2500 
DIA0 started with pid=8, OS id=5320 
MMAN started with pid=9, OS id=4128 
DBW0 started with pid=10, OS id=5852 
LGWR started with pid=11, OS id=3960 
CKPT started with pid=12, OS id=4472 
SMON started with pid=13, OS id=5788 
RECO started with pid=14, OS id=6036 
MMON started with pid=15, OS id=5740 
MMNL started with pid=16, OS id=2112 
ORACLE_BASE from environment = C:\app
alter database mount exclusive
2011-08-01 13:13:52.390000 +08:00
Sweep [inc][135908]: completed
NSS2 started with pid=19, OS id=2728 
Sweep [inc][135901]: completed
Successful mount of redo thread 1, with mount id 2704081164
Database mounted in Exclusive Mode
2011-08-01 13:13:53.413000 +08:00
Lost write protection disabled
2011-08-01 13:13:54.578000 +08:00
Sweep [inc][135897]: completed
Sweep [inc2][135908]: completed
Sweep [inc2][135901]: completed
Sweep [inc2][135897]: completed
2011-08-01 13:13:55.788000 +08:00
Completed: alter database mount exclusive
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 3 processes
2011-08-01 13:13:56.959000 +08:00
Started redo scan
Completed redo scan
 read 0 KB redo, 0 data blocks need recovery
Started redo application at
 Thread 1: logseq 867, block 88140, scn 9122496
Recovery of Online Redo Log: Thread 1 Group 3 Seq 867 Reading mem 0
  Mem# 0: C:\APP\ORADATA\G11R2\ONLINELOG\O1_MF_3_6VWCSMPO_.LOG
  Mem# 1: C:\APP\FLASH_RECOVERY_AREA\G11R2\ONLINELOG\O1_MF_3_6VWCSNGX_.LOG
Completed redo application of 0.00MB
Completed crash recovery at
 Thread 1: logseq 867, block 88140, scn 9142497
 0 data blocks read, 0 data blocks written, 0 redo k-bytes read
2011-08-01 13:13:58.738000 +08:00
LGWR: STARTING ARCH PROCESSES
ARC0 started with pid=22, OS id=4784 
2011-08-01 13:13:59.765000 +08:00
ARC0: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC0: STARTING ARCH PROCESSES
ARC1 started with pid=24, OS id=2780 
ARC2 started with pid=25, OS id=1288 
ARC1: Archival started
LGWR: Primary database is in MAXIMUM AVAILABILITY mode
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
LGWR: Destination LOG_ARCHIVE_DEST_1 is not serviced by LGWR
ARC3 started with pid=26, OS id=3876 
2011-08-01 13:14:00.828000 +08:00
ARC3: Archival started
ARC0: STARTING ARCH PROCESSES COMPLETE
NSS2 started with pid=19, OS id=5156 
2011-08-01 13:14:29.008000 +08:00
ORA-16198: LGWR received timedout error from KSR
2011-08-01 13:14:35.980000 +08:00
Errors in file c:\app\diag\rdbms\g11r2\g11r2\trace\g11r2_lgwr_3960.trc:
ORA-16198: Timeout incurred on internal channel during remote archival
LGWR: Error 16198 verifying archivelog destination LOG_ARCHIVE_DEST_2
Destination LOG_ARCHIVE_DEST_2 is UNSYNCHRONIZED
LGWR: Continuing...
ARCH: LGWR is scheduled to archive destination LOG_ARCHIVE_DEST_2 after log switch
2011-08-01 13:14:38.629000 +08:00
Trying to expand controlfile section 11 for Oracle Managed Files
Exception [type: INT_DIVIDE_BY_ZERO, ] [] [PC:0x500282E, __VInfreq__kcflfi()+466]
Errors in file c:\app\diag\rdbms\g11r2\g11r2\trace\g11r2_arc0_4784.trc  (incident=136091):
ORA-07445: exception encountered: core dump [kcflfi()+466] [INT_DIVIDE_BY_ZERO] [] [PC:0x500282E] [] []
Incident details in: c:\app\diag\rdbms\g11r2\g11r2\incident\incdir_136091\g11r2_arc0_4784_i136091.trc
2011-08-01 13:14:40.283000 +08:00
Trace dumping is performing id=[cdmp_20110801131440]
2011-08-01 13:14:52.417000 +08:00
Sweep [inc][136091]: completed
Sweep [inc2][136091]: completed
2011-08-01 13:14:59.805000 +08:00
ARC2: Detected ARCH process failure
ARC2: STARTING ARCH PROCESSES
ARC0 started with pid=19, OS id=5016 
2011-08-01 13:15:00.836000 +08:00
ARC0: Archival started
ARC2: STARTING ARCH PROCESSES COMPLETE
2011-08-01 13:15:36.689000 +08:00
Deleted Oracle managed file C:\APP\FLASH_RECOVERY_AREA\G11R2\ARCHIVELOG\2011_08_01\O1_MF_1_866_73DFKWRK_.ARC
2011-08-01 13:15:38.013000 +08:00
Error 12154 received logging on to the standby
Errors in file c:\app\diag\rdbms\g11r2\g11r2\trace\g11r2_ora_4852.trc:
ORA-12154: TNS:could not resolve the connect identifier specified
ARCH: Error 12154 Creating archive log file to 'stdby'
Trying to expand controlfile section 11 for Oracle Managed Files
Exception [type: INT_DIVIDE_BY_ZERO, ] [] [PC:0x500282E, __VInfreq__kcflfi()+466]
Errors in file c:\app\diag\rdbms\g11r2\g11r2\trace\g11r2_ora_4852.trc  (incident=136051):
ORA-07445: exception encountered: core dump [kcflfi()+466] [INT_DIVIDE_BY_ZERO] [] [PC:0x500282E] [] []
Incident details in: c:\app\diag\rdbms\g11r2\g11r2\incident\incdir_136051\g11r2_ora_4852_i136051.trc
2011-08-01 13:15:39.680000 +08:00
Trace dumping is performing id=[cdmp_20110801131539]
2011-08-01 13:15:42.782000 +08:00
PMON (ospid: 984): terminating the instance due to error 397
2011-08-01 13:15:50.520000 +08:00
Instance terminated by PMON, pid = 984

=============================g11r2_ora_4852_i136051.trc=============================

Dump file c:\app\diag\rdbms\g11r2\g11r2\incident\incdir_136051\g11r2_ora_4852_i136051.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V6.1 Service Pack 1 
CPU                 : 4 - type 586, 2 Physical Cores
Process Affinity    : 0x0x00000000
Memory (Avail/Total): Ph:2122M/3566M, Ph+PgF:5413M/7130M, VA:1084M/2047M 
Instance name: g11r2
Redo thread mounted by this instance: 1
Oracle process number: 17
Windows thread id: 4852, image: ORACLE.EXE (SHAD)

*** 2011-08-01 13:15:38.527
*** SESSION ID:(197.1) 2011-08-01 13:15:38.527
*** CLIENT ID:() 2011-08-01 13:15:38.527
*** SERVICE NAME:() 2011-08-01 13:15:38.527
*** MODULE NAME:(oradim.exe) 2011-08-01 13:15:38.527
*** ACTION NAME:() 2011-08-01 13:15:38.527

Dump continued from file: c:\app\diag\rdbms\g11r2\g11r2\trace\g11r2_ora_4852.trc
ORA-07445: exception encountered: core dump [kcflfi()+466] [INT_DIVIDE_BY_ZERO] [] [PC:0x500282E] [] []

========= Dump for incident 136051 (ORA 7445 [kcflfi()+466]) ========
----- Beginning of Customized Incident Dump(s) -----
Exception [type: INT_DIVIDE_BY_ZERO, ] [] [PC:0x500282E, __VInfreq__kcflfi()+466]

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
Process Id: 0x000010bc  Thread Id : 0x000012f4    Time : Mon Aug 01 13:15:38 
Excp. Code: 0xc0000094  Excp. Type: INT_DIVIDE    Flags: 0x00000000

------------------- Registers ----------------------------
eip = 0500282e esp = 0d9f525c ebp = 0d9f577c edi = 37eefe00 esi = 00000265
eax = 00000265 ebx = 00000000 ecx = 089ee234 edx = 00000000
ecs = 0000001b eds = 00000023 ees = 00000023 ess = 00000023
egs = 00000000 efs = 0000003b
eflags = 00010246
------------------- End of Registers ---------------------

*** 2011-08-01 13:15:38.536
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x3, level=3, mask=0x0)
----- Current SQL Statement for this session (sql_id=a01hp0psv0rrh) -----
alter database open
----------- messages from pre-loading .sym files:
Symbol file C:\app\product\11.2.0\dbhome_1\RDBMS\ADMIN\oracommon11.SYM does not match binary.
 Symbol TimeStamp=4bb5eaac, Module TimeStamp=0 are different
Symbol file C:\app\product\11.2.0\dbhome_1\RDBMS\ADMIN\oraclsra11.SYM does not match binary.
 Symbol TimeStamp=4bb4cf99, Module TimeStamp=0 are different
----------- end of messages from pre-loading .sym files
----- Call Stack Trace -----
calling              call     entry                argument values in hex      
location             type     point                (? means dubious value)     
-------------------- -------- -------------------- ----------------------------
Symbol file C:\app\product\11.2.0\dbhome_1\BIN\oracommon11.SYM does not match binary.
 Symbol TimeStamp=4bb5eaac, Module TimeStamp=0 are different
Symbol file C:\app\product\11.2.0\dbhome_1\BIN\oraclsra11.SYM does not match binary.
 Symbol TimeStamp=4bb4cf99, Module TimeStamp=0 are different
EnumerateLoadedModules64 failed with error -1073741819
Symbol file oraclsra11.SYM does not match binary.
 Symbol TimeStamp=4bb4cf99, Module TimeStamp=0 are different
Symbol file oracommon11.SYM does not match binary.
 Symbol TimeStamp=4bb5eaac, Module TimeStamp=0 are different
__VInfreq__kcflfi()           00000000             
+466                                               
_kccrszf()+287       CALLrel  _kcflfi()            0 318345B8 34 31C0DD40 4000
                                                   265 4 7FFFFFFF 1 0 0
_kccrsd_expd()+1418  CALLrel  _kccrszf()           D9F7CEC 268 264
_kccwnc_reuse_expan  CALLrel  _kccrsd_expd()       D9F7CEC B 38
d()+640                                            
__VInfreq__kccwnc()  CALLrel  _kccwnc_reuse_expan  D9F7CEC B 26
+235                          d()                  
_krse_arc_complete(  CALLrel  _kccwnc()            D9F7CEC D9F6D38 B
)+1615                                             
_krse_arc_driver_co  CALLrel  _krse_arc_complete(  D9F78AC
re()+1307                     )                    
_krse_arc_driver()+  CALLrel  _krse_arc_driver_co  D9F7CEC 1 D9F7C6C 0 0 D9F7CC8
274                           re()                 0 0 0 0 0 0 0
_krsq_arch_to_force  CALLrel  _krse_arc_driver()   D9F7CEC 1 D9F7C6C 0 0 D9F7CC8
_switch()+196                                      0 0 0 0 0 0 0
__VInfreq__kcttsc()  CALLrel  _krsq_arch_to_force  D9F7CEC 1
+129                          _switch()            
_kcfopd()+1504       CALLrel  _kcttsc()            2
_adbdrv()+16700      CALLrel  _kcfopd()            0 0 0 0 D9FBBF8
_opiexe()+13594      CALLrel  _adbdrv()            4A C0000094 33644518 D9FBD38
                                                   6D60697 2F3FC5F0
_opiosq0()+6248      CALLrel  _opiexe()            4 0 D9FC704
_kpooprx()+277       CALLrel  _opiosq0()           3 E D9FC970 A4 0
_kpoal8()+632        CALLrel  _kpooprx()           D9FF074 D9FD3F8 13 1 0 A4
_opiodr()+1248       CALLreg  00000000             5E 1C D9FF070
___dyn_tls_init_cal  CALLreg  00000000             5E 1C D9FF070 1
lback()+2935122                                    
_opitsk()+1404       CALL???  00000000             C9A10E8 5E D9FF070 0 D9FED00
                                                   D9FF19C 53E52E 0 D9FF1C8
_opiino()+980        CALLrel  _opitsk()            0 0
_opiodr()+1248       CALLreg  00000000             3C 4 D9FFBC4
_opidrv()+1201       CALLrel  _opiodr()            3C 4 D9FFBC4 0
_sou2o()+55          CALLrel  _opidrv()            3C 4 D9FFBC4
_opimai_real()+124   CALLrel  _sou2o()             D9FFBD4 3C 4 D9FFBC4
_opimai()+125        CALLrel  _opimai_real()       2 D9FFBFC
_OracleThreadStart@  CALLrel  _opimai()            2 D9FFF3C 0 70 FFFFFFFF
4()+830                                            FFFFFFFF
___dyn_tls_init_cal  CALLptr  00000000             901FF6C D9FFFD4 776437F5
lback()+366382316                                  901FF6C 765D34CB 0
___dyn_tls_init_cal  CALLreg  00000000             901FF6C 765D34CB 0 0 901FF6C
lback()+367384440                                  0
___dyn_tls_init_cal  CALLrel  ___dyn_tls_init_cal  401326 901FF6C 0 0 0 0
lback()+367384392             lback()+367384403    
00000000             CALL???  00000000             

--------------------- Binary Stack Dump ---------------------
..................

从以上日志中可以看到在”Trying to expand controlfile section 11 for Oracle Managed Files“扩扎控制文件过程中出现了
_kccwnc_reuse_expan->_kccrsd_expd->_kccrszf->_kcflfi->_VInfreq__kcflfi()
函数的7445错误,kcf意为(manages and coordinates operations on the control file(s),kcf.c),是在处理日志文件中引发了INT_DIVIDE_BY_ZERO除数为零的代码bug。

通过7445和kcflfi关键词在MOS上搜索没有太大的发现,说明该Bug的处罚几率非常低,正好让我碰到说明是某些特殊参数的设置引起了该问题。

目标锁定启动日志中的非默认隐藏参数”_db_file_direct_io_count”,该参数决定了直接路径读写的IO大小,从9i开始该参数的单位调整为bytes而非原先的blocks,之前因为对该参数进行一些测试所以设置了一个较小值。

Parameter: DB_FILE_DIRECT_IO_COUNT
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Versions:	8.0 - 8.1
                This parameter is hidden in 9.0 onwards.

 Parameter type:        integer
 Parameter class:       dynamic, scope = ALTER SYSTEM DEFERRED
 Default value:         64
 Range of values:       operating system-dependent

Description:
~~~~~~~~~~~~
DB_FILE_DIRECT_IO_COUNT is used to specify the number of blocks to be used
for IO operations done by backup, restore or direct path read and write
functions. The IO buffer size is a product of DB_FILE_DIRECT_IO_COUNT and
DB_BLOCK_SIZE. The IO buffer size cannot exceed max_IO_size for your
platform.

Assigning a high value to this parameter results in greater use of PGA or
SGA memory.

o In Oracle8i, minimize the number of I/O requests by setting the
  DB_FILE_DIRECT_IO_COUNT instance parameter so that

  DB_BLOCK_SIZE x DB_FILE_DIRECT_IO_COUNT = max_io_size of system

  In Oracle8i the default for this is 64 blocks.

  (In Oracle9i, it is replaced by _DB_FILE_DIRECT_IO_COUNT which governs
   the size of direct I/Os in BYTES (not blocks). The default is 1Mb but
   will be sized down if the max_io_size of the system is smaller.)

ORA-19863 during RMAN duplicate

Applies to:
Oracle Server - Enterprise Edition - Version: 10.2.0.3
This problem can occur on any platform.
Symptoms
-- Problem Statement:
Duplicate failed during the datafile restore stage:

Starting restore at 2008-Apr-09 09:28:24
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00003 to /u06/oradata/hcmprdc/sysaux01.dbf
...
restoring datafile 00121 to /u06/oradata/hcmprdc/waapp.dbf
channel ORA_AUX_DISK_1: reading from backup piece
/u04/oradata/flash_recovery_area/HCMPRD/mdjd8s5v_1_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 04/09/2008 09:28:28
RMAN-03015: error occurred in stored script Memory Script
ORA-19870: error reading backup piece /u04/oradata/flash_recovery_area/HCMPRD/mdjd8s5v_1_1
ORA-19863: device block size 1040384 is larger than max allowed: 262144

Cause
The database parameter _db_file_direct_io_count in the target and auxiliary instance does not match.
Solution

-- To implement the solution:

Ensure that parameter _db_file_direct_io_count on the target and auxiliary database the same

_DB_FILE_DIRECT_IO_COUNT need to be set to the same value between the source database 
where the backup was taken and the target database where the backup is being restored.

2.0 Size of Input/Output Buffers
================================

a. input buffers
----------------

NOTE : DB_FILE_DIRECT_IO_COUNT is not available in Oracle9i onwards.
       In Oracle9i, it is replaced by a hidden _DB_FILE_DIRECT_IO_COUNT which 
       governs the size of direct I/Os in BYTES (not blocks). The default is 
       1Mb butwill be sized down if the max_io_size of the system is smaller.

The input buffer size is:
  buffersize = db_block_size * db_file_direct_io_count

As there are 4 input buffers, the total input buffer memory use per channel is:
 memory(input) = #buffers * #files * buffersize
               = 4 * #files * buffersize

For example, if 2 channels are used, and each of these channels backs up 3 
files, then for each channel

 memory(input) = 4 * 3 * db_block_size * db_file_direct_io_count

b. output buffers
-----------------

For disk channels, the output buffer size is:
  buffersize = db_block_size * db_file_direct_io_count

For SBT_TAPE channels, the output buffer size in Oracle8/8i is o/s dependant. (On Solaris,
this defaults to 64k) On 9i/10g it defaults to 256k for all platforms. The BLKSIZE argument to 'allocate channel...' can be
used to override the default value.

As there are 4 output buffers,
  memory(output) = #buffers * buffersize
                 = 4 * buffersize

一般来说使用该隐藏参数的默认值即可,通过重置该参数后修复启动问题:

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> alter system reset "_db_file_direct_io_count" scope=spfile;

System altered.

SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ
 FROM SYS.x$ksppi x, SYS.x$ksppcv y
 WHERE x.inst_id = USERENV ('Instance')
 AND y.inst_id = USERENV ('Instance')
 AND x.indx = y.indx
AND x.ksppinm LIKE '%db_file_direct_io_count%'
/

NAME                           VALUE                DESCRIB
------------------------------ -------------------- ------------------------------
_db_file_direct_io_count       1048576              Sequential I/O buf size

windows上的11gr2默认该参数为1MB

沪公网安备 31010802001379号

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569