正德厚生,臻于至善

OracleEBS

Oracle row cache lock 之sequence

xuwanxin1987阅读(13)

select distinct event, p1, p1text
from v$active_session_history ash
where ash.event = 'row cache lock'
and ash.sample_time > to_date('2023-05-26 00:00:00','yyyy-mm-dd hh24:mi:ss') 
and ash.sample_time < to_date('2023-05-27 00:00:00','yyyy-mm-dd hh24:mi:ss')
;
select session_id,sql_id,blocking_session,event,machine 
from dba_hist_active_sess_history
where event='row cache lock'
and sample_time >= to_date('2023-05-23 10:00:00','yyyy-mm-dd hh24:mi:ss') 
and sample_time <= to_date('2023-05-23 11:00:00','yyyy-mm-dd hh24:mi:ss')
;
select sql_id,count(*)
from dba_hist_active_sess_history
where event='row cache lock'
and sample_time >= to_date('2023-05-25 00:00:00','yyyy-mm-dd hh24:mi:ss') 
and sample_time <= to_date('2023-05-26 00:00:00','yyyy-mm-dd hh24:mi:ss')
group by sql_id
order by 2 desc;

https://blog.csdn.net/weixin_29326909/article/details/116325672

今天遇到一个生产库产生大量row cache lock,以下是相应步骤:

1 查询当时P1的情况
select INSTANCE_NUMBER, p1, count(*) cnt
  from dba_hist_active_sess_history
 where event = 'row cache lock'
   and SAMPLE_TIME >=
       to_date('2023-05-24 10:00:00','yyyy-mm-dd hh24:mi:ss')
   and SAMPLE_TIME <=
       to_date('2023-05-24 11:00:00','yyyy-mm-dd hh24:mi:ss')
 group by INSTANCE_NUMBER, p1
 order by cnt;

2 根据第一步查询的P1,代入到下面cache#,这里查询出来的结果是13,dc_sequences
select inst_id, cache#,type,gets,parameter from gv$rowcache where cache# in (?) order by gets;

3 查询当时用户会话量情况
select INSTANCE_NUMBER, USER_ID, count(*) cnt
  from dba_hist_active_sess_history
 where SAMPLE_TIME >=
       to_date('2023-05-24 10:00:00','yyyy-mm-dd hh24:mi:ss')
   and SAMPLE_TIME <=
       to_date('2023-05-24 11:00:00','yyyy-mm-dd hh24:mi:ss')
 group by INSTANCE_NUMBER, USER_ID
 order by 1, cnt;

4 查询对应SQL的情况
select INSTANCE_NUMBER,
       SAMPLE_TIME,
       event,
       sql_opname,
       sql_id,
       count(*) cnt
  from dba_hist_active_sess_history
 where SAMPLE_TIME >=
       to_date('2023-05-24 10:00:00','yyyy-mm-dd hh24:mi:ss')
   and SAMPLE_TIME <=
       to_date('2023-05-24 11:00:00','yyyy-mm-dd hh24:mi:ss')
 group by INSTANCE_NUMBER, SAMPLE_TIME, event, sql_opname, sql_id
 order by 2, 1;

5 查询对应节点更详细的会话信息
select INSTANCE_NUMBER,
       SAMPLE_TIME,
       session_id,
       BLOCKING_SESSION,
       current_obj#,
       user_id,
       event,
       sql_id,
       P1
  from dba_hist_active_sess_history
 where SAMPLE_TIME >=
       to_date('2023-05-24 10:00:00','yyyy-mm-dd hh24:mi:ss')
   and SAMPLE_TIME <=
       to_date('2023-05-24 11:00:00','yyyy-mm-dd hh24:mi:ss')
   and INSTANCE_NUMBER = 2
   and event = 'row cache lock';

最后定位是一个高频INSERT 语句引用SEQUENCE引起的,而这个序列是NOCAHE,改成cache恢复正常
0dupf1d2nntjz	8569
0dupf1d2nntjz select FND_SOA_REQUEST_S.nextval from dual
alter sequence APPLSYS.FND_SOA_REQUEST_S cache 1000;

P1 = cache - ID of the dictionary cache
P2 =  mode - Mode held
P3 =  request - Mode requested

https://blog.csdn.net/weixin_39760368/article/details/111819665

Oracle Sequence 性能优化
定义Sequence是很简单的,如果最大程度利用默认值的话,我们只需要定义sequence对象的名字即可。在序列Sequence对象的定义中,Cache是一个可选择的参数。默认的Sequence对象是有cache选项的,默认取值为20。这个默认值对于大多数情况下都是够用的。 除非那种每秒上万次的select。 所以具体情况要具体对待。 对于哪些大并发的系统,最好设置在100以上。像移动的BOSS系统,以1000为单位。

SEQUENCE创建基本语法:
CREATE SEQUENCE [schema.]sequence
[INCREMENT BY integer]
[START WITH integer]
[MAXVALUE integer | NOMAXVALUE]
[MINVALUE integer | NOMINVALUE]
[CYCLE | NOCYCLE]
[CACHE integer | NOCACHE]
[ORDER | NOORDER]

eg:
CREATE SEQUENCE emp_seq
INCREMENT BY 1-- 每次加几个
START WITH 1-- 从1开始计数
NOMAXVALUE-- 不设置最大值
NOCYCLE-- 一直累加,不循环 CACHE N / NOCACHE --其中n代表一个整数,默认值为20。

如果指定CACHE值,Oracle就可以预先在内存里面放置一些Sequence,这样存取的快些。cache里面的取完后,Oracle自动再取一组到cache。使用cache或许会跳号, 比如数据库突然不正常down掉(shutdown abort),cache中的Sequence就会丢失。举个例子:比如你的sequence中cache 100,那当你sequence取到90时突然断电,那么在你重启数据库后,sequence的值将从101开始。

如果指定NOCACHE值,Oracle就不会预先在内存里面存放Sequence,当然这也就可以避免数据库不正常down掉的sequence丢失。不过会产生一些问题:创建nocache sequence在高并发访问时,容易导致row cache lock等待事件,主要原因是每次获取nextval时都需要修改rowcache中的字典信息。使用nocache sequence,还会导致如下问题:

由于每次修改字典信息都需要commit,可能导致log file sync等待,nocache sequence在RAC环境下,会对基于sequence生成的列创建的索引造成实例间大量索引块争用
基于以上问题,避免创建nocache sequence。

再来看看sequence相关保护机制:
row cache lock:在调用sequence.nextval情况下需要修改数据字典时发生,对应row cache lock事件
SQ lock:在内存缓存(并非rowcache)上获取sequence.nextval时发生,对应enq:SQ-contention事件
SV lock:RAC环境下获取cache+order属性的sequence.nextval时发生,对应DFS lock handle事件

什么情况下使用cache什么时间上使用nocache?
个人感觉应该尽量使用cache,因为现在的数据库很多都是在高并发的情况下运行的,首先这样可以搞性能,并且也不会产生row cache lock等待事件。可能有些人会担心数据库不正常的down掉会产生序列号间断,但这也是很少的情况。当然如果你的业务要求是绝不能产生间断的序列号,那就要使用nochache了。

序列的并发访问 :
序列总是在数据库中生成唯一值,即使当多个用户并发地引用同一序列时也没有可察觉的等待或锁定。当多个用户使用 NEXTVAL 来增长序列时,每个用户生成一个其他用户不可见的唯一值。
当多个用户并发地增加同一序列时,每个用户看到的值是有差异的。例如,一个用户可能从一个序列生成一组值,如 1、4、6 和 8,而另一个用户并发地从同一序列生成值 2、3、5 和 7。

假如在创建sequence时,有意不选用cache选项,有2点需要注意:
1. 访问效率降低,没有cache功能的sequence取值将无法直接访问内存
2. 不论是nocache还是cache , 每次访问nextval的过程都是不可逆的,在同一session中,在执行一系列dml和sequence的操作后,用户执行rollback,希望将操作回滚,但是sequence此时就显得异常顽固,用掉的nextval将无法被重现。当下一次试图读取nextval时,sequence的指针又移动到下一位了。

还有sequence不属于某个表,也不属于某个字段,sequence仅仅属于某个用户。
其实在创建了sequence后,每个表都可以使用这个sequence,但是这样会引起应用的很多麻烦,因此,建议每个表都使用一个sequence。
关于Sequence order 参数的说明:
序参数:oracle默认是NOORDER,如果设置为ORDER;在单实例环境没有影响,在RAC环境此时,多实例实际缓存相同的序列,此时在多个实例并发取该序列的时候,会有短暂的资源竞争来在多实例之间进行同步。因次性能相比noorder要差,所以RAC环境非必须的情况下不要使用ORDER,尤其要避免NOCACHE ORDER组合。
order确保按照请求次序递增生成序列数字,在RAC环境中建议使用该选项。noorder不保证按请求次序生成,默认是noorder。
Cache参数对Sequence的使用带来什么好处?如果不设置,会有什么问题 ?
1、Sequence Cache简析
简单的说,Cache 就是Oracle每次向Sequence进行请求时,分配出的独立数字数量。例如,当我们使用.nextval获取一个 独立值时,Oracle需要将sequence对象的数据字典信息更新。如果我们设置cache为10,那么第一次请求nextval的时候,就更新数据 字典信息增加10,取出的10个号放在Oracle服务器的缓存中。
在以后每次请求nextval的时候,Oracle就从服务器缓存中去获取序列值。而不需要更新数据字典信息。只有在分配到缓存的10个数字都已经分配完,或者因为缓存刷新操作剩余数字被清理的情况下,才会再次调用sequence分配机制,再次分出cache个数字。
在cache问题上,我们经常会疑惑为什么我们sequence生成的数字序列会“跳号”。这种跳号现象实际上就是因为cache的数字在缓存中因为各种原因被flush出,这样才导致生成的数字序列不连续。
注意:在有cache的情况下,sequence只能保证每次获取到的数字都是唯一、递增的,从来没有保证过数字的连续性。
如果我们不设置cache,也就是不启用序列数字缓存机制,有什么缺点呢?
1、过多的Redo Log生成
a),对nocache的序列对象而言,每次操作都会有600左右的redo log生成。
b),对cache的sequence对象而言,redo size生成的频率显然是低得多(到达cahce size最大值时才会创建redo log)。
设置cache之后,Oracle似乎不用为每次的nextval进行数据字典修改,生成redo log记录。只有cache在内存中使用结束之后,才会进行获取。
在实际的生产环境中,我们对redo size无必要的生成是要尽力避免的。首先,过多的redo log生成,容易造成online redo log的写入量增加,切换频繁。第二,redo size和nocache的使用,可能是伴随着频繁的commit动作,进而是频繁的log buffer写入online log file的过程。同时归档量增加。同时,在进行恢复的时候,也要消耗更多的时间。
所以,设置cache可以有效减少redo log的大小。
说明:如果我们处在一个高并发的情况下,系统频繁的多会话请求sequence取值。如果我们的sequence没有设置cache,那么每次都要更新数据字典,都要进行commit操作。多个会话还会出现该sequence记录的争用,出现等待事件row lock contention。
在RAC环境下,cache和noorder选项的作用更大。在RAC中,多个实例争用情况会让sequence设计的不合理效果放大。所以,在没有特殊情况下,还是设置合理的cache值,减少系统潜在性能瓶颈。

http://blog.csdn.net/cymm_liu/article/details/7451009

清理trace目录

xuwanxin1987阅读(26)

cd trace
rm -rf *_ora_10*
rm -rf *_ora_11*
rm -rf *_ora_12*
rm -rf *_ora_13*
rm -rf *_ora_14*
rm -rf *_ora_15*
rm -rf *_ora_16*
rm -rf *_ora_17*
rm -rf *_ora_18*
rm -rf *_ora_19*

rm -rf *_ora_20*
rm -rf *_ora_21*
rm -rf *_ora_22*
rm -rf *_ora_23*
rm -rf *_ora_24*
rm -rf *_ora_25*
rm -rf *_ora_26*
rm -rf *_ora_27*
rm -rf *_ora_28*
rm -rf *_ora_29*

rm -rf *_ora_30*
rm -rf *_ora_31*
rm -rf *_ora_32*
rm -rf *_ora_33*
rm -rf *_ora_34*
rm -rf *_ora_35*
rm -rf *_ora_36*
rm -rf *_ora_37*
rm -rf *_ora_38*
rm -rf *_ora_39*

rm -rf *_ora_40*
rm -rf *_ora_41*
rm -rf *_ora_42*
rm -rf *_ora_43*
rm -rf *_ora_44*
rm -rf *_ora_45*
rm -rf *_ora_46*
rm -rf *_ora_47*
rm -rf *_ora_48*
rm -rf *_ora_49*

rm -rf *_ora_50*
rm -rf *_ora_51*
rm -rf *_ora_52*
rm -rf *_ora_53*
rm -rf *_ora_54*
rm -rf *_ora_55*
rm -rf *_ora_56*
rm -rf *_ora_57*
rm -rf *_ora_58*
rm -rf *_ora_59*

rm -rf *_ora_60*
rm -rf *_ora_61*
rm -rf *_ora_62*
rm -rf *_ora_63*
rm -rf *_ora_64*
rm -rf *_ora_65*
rm -rf *_ora_66*
rm -rf *_ora_67*
rm -rf *_ora_68*
rm -rf *_ora_69*

rm -rf *_ora_70*
rm -rf *_ora_71*
rm -rf *_ora_72*
rm -rf *_ora_73*
rm -rf *_ora_74*
rm -rf *_ora_75*
rm -rf *_ora_76*
rm -rf *_ora_77*
rm -rf *_ora_78*
rm -rf *_ora_79*

rm -rf *_ora_80*
rm -rf *_ora_81*
rm -rf *_ora_82*
rm -rf *_ora_83*
rm -rf *_ora_84*
rm -rf *_ora_85*
rm -rf *_ora_86*
rm -rf *_ora_87*
rm -rf *_ora_88*
rm -rf *_ora_89*

rm -rf *_ora_90*
rm -rf *_ora_91*
rm -rf *_ora_92*
rm -rf *_ora_93*
rm -rf *_ora_94*
rm -rf *_ora_95*
rm -rf *_ora_96*
rm -rf *_ora_97*
rm -rf *_ora_98*
rm -rf *_ora_99*

rm -rf *_ora_1*
rm -rf *_ora_2*
rm -rf *_ora_3*
rm -rf *_ora_4*
rm -rf *_ora_5*
rm -rf *_ora_6*
rm -rf *_ora_7*
rm -rf *_ora_8*
rm -rf *_ora_9*

rm -rf cdmp* *.tr*

Oracle 单表记录上限,单表理论最大记录数

xuwanxin1987阅读(23)

不考虑硬件诸如内存,存储等硬件的限制。
一张表理论能存储多少条记录呢?

假设:
一个tablespace中包含1022个datafiles,
单个datafiles的最大是32G
假设每个block是16k,每个block中有160条记录,
每个表可以有(1024*1024)-1个分区表
那么一张表理论最大存储记录数约为:

1.一个表空间tablespace的最大值:datafile个数 × 单个datafile的大小
1022 × 32G  =32704 G

2.换算成k
(32704 G *1024*1024 )=34,292,629,504 K

3.假设每个块16k,算出共有多少个块
34,292,629,504 K /16 K =2143289344  块

4.假设每个块存160记录。总记录数=块数×每个块的记录数
2143289344×160=342,926,295,040  记录

5. 最大分区表的个数 ×  最大记录数
SQL> select ((1024*1024)-1 )* 342926295040  maxnum from dual;
MAXNUM
-------------------
3.59583939821568E17

按Oracle设计:1个表空间(tablespace),最多由1022个文件数据文件组成(datafile),每个文件最大32GB,那么

1)一个表空间占用约:32TB空间。
如程序员设计:1条记录占用0.1KB空间,那么
2)一个表空间包括约:320G条记录(=32TB/.1KB),就是3千亿条记录。
DECLARE
	CURSOR CUR_01 IS SELECT * FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='CUX_16_PJVS_SPLIT_SUBACCT_ALL';
	  CUR_02 CUR_01%ROWTYPE;
	  A1 NUMBER;
	  SQL01 VARCHAR2(150);

	BEGIN
		FOR CUR_02 IN CUR_01 LOOP
       SQL01 := 'SELECT COUNT(1) FROM CUX_16_PJVS_SPLIT_SUBACCT_ALL PARTITION('||CUR_02.PARTITION_NAME||') ';
       EXECUTE IMMEDIATE SQL01 INTO A1 ;
        DBMS_OUTPUT.PUT_LINE('分区名:'||CUR_02.PARTITION_NAME ||'  数量:'|| A1);
      END LOOP;
  END;

sysaux表空间异常增长之统计信息数据未自动清理

xuwanxin1987阅读(26)

首先还是去查sysaux表空间中占用空间最多的组件和对象
select OCCUPANT_NAME,OCCUPANT_DESC,SPACE_USAGE_KBYTES/1024 USAGE_MB
from V$SYSAUX_OCCUPANTS order by SPACE_USAGE_KBYTES desc;
 
SELECT D.SEGMENT_NAME, D.SEGMENT_TYPE,SUM(BYTES)/1024/1024 SIZE_MB
  FROM DBA_SEGMENTS D
 WHERE D.TABLESPACE_NAME = 'SYSAUX'
 GROUP BY D.SEGMENT_NAME, D.SEGMENT_TYPE
 ORDER BY SIZE_MB DESC;
如果发现是统计信息的问题,可以按照如下方法清理:

法一:purge(底层用delete删除)
Show the current history level:

select dbms_stats.get_stats_history_availability from dual;
Assuming  history is 100 days old and you want to purge it until 10 days old:

begin
for i in reverse 61..100
loop
dbms_stats.purge_stats(sysdate-i);
end loop;
end;
/
Show the new history level

select dbms_stats.get_stats_history_availability from dual;

法二:truncate
只有当purge的情况下,还是没有释放空间,或者需删除量过大时,才建议用truncate方式进行清理。

备份待删除基表中最新数据
create table SYS.WRI$_OPTSTATHISTHEADHISTORYBAK as (select * from sys.wri$_optstat_histhead_history where savtime > SYSDATE - 14);
create table SYS.WRI$_OPTSTATHISTGRMHISTORYBAK as (select * from sys.WRI$_OPTSTAT_HISTGRM_HISTORY where savtime > SYSDATE - 14);
create table SYS.WRI$_OPTSTAT_TAB_BAK as (select * from sys.wri$_optstat_tab_history where savtime > SYSDATE - 14);
create table SYS.WRI$_OPTSTAT_IND_BAK as (select * from sys.wri$_optstat_ind_history where savtime > SYSDATE - 14);

查看SM/OPTSTAT(用于存储老的统计信息)保留天数
select dbms_stats.get_stats_history_retention from dual; 

设置SM/OPTSTAT保留的时间(-1表示无限)
exec dbms_stats.alter_stats_history_retention(-1);

truncate较大的TABLE
truncate table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY;
truncate table sys.WRI$_OPTSTAT_HISTGRM_HISTORY;
truncate table  sys.wri$_optstat_ind_history;
truncate table  sys.wri$_optstat_tab_history;

清理历史统计信息
exec dbms_stats.purge_stats(sysdate-30);
 
将历史统计信息保留时间设为30天
exec dbms_stats.alter_stats_history_retention(30);
select dbms_stats.get_stats_history_retention from dual;

将历史统计信息相关的表进行MOVE
alter table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace sysaux;
alter index sys.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild online;
alter index sys.I_WRI$_OPTSTAT_HH_ST rebuild online;
...
alter table sys.WRI$_OPTSTAT_IND_HISTORY  move tablespace sysaux;
alter index sys.I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild online;
alter index sys.I_WRI$_OPTSTAT_IND_ST rebuild online;

对MOVE表的统计信息进行收集
 EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_HISTGRM_HISTORY',cascade => TRUE);
 ...
 EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_HISTHEAD_HISTORY',cascade => TRUE);

参考
http://zxf261.blog.51cto.com/701797/776496
http://otn-world.blogspot.com/2014/06/purging-optimizer-stats.html
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_stats.htm#ARPLS68644

EBS 12.2 19c数据库克隆步骤

xuwanxin1987阅读(67)

1.源环境数据库主节点DB1运行预克隆
su – oracle
. /u01/SIT/db/19.0.0/SITCDB_r122sitdb.env
perl adpreclone.pl dbTier

2.目标数据库节点运行克隆技术堆栈
su – oracle
cd /u01/SIT/db/19.0.0/appsutil/clone/bin
perl adcfgclone.pl dbTechStack

3.利用rman或NBU恢复源库备份到目标数据库节点

4.修改CDB db_name
su – oracle
export ORACLE_SID=PRODCDB
sqlplus / as sysdba
startup mount
exit

nid target=sys/oracle dbname=SITCDB setname=yes

5.修改PDB name
su - oracle
. 19c_cdb.env
sqlplus / as sysdba

alter pluggable database PROD close;
alter pluggable database PROD open restricted;
alter session set container=PROD;
alter pluggable database rename global_name to SIT;
alter pluggable database close immediate;
alter pluggable database SIT open;

6.创建并启动 PDB service
su - oracle
. 19c_cdb.env
sqlplus / as sysdba

alter session set container=SIT;
exec dbms_service.create_service('ebs_SIT','ebs_SIT');
exec dbms_service.delete_service('ebs_PROD');

exec dbms_service.create_service('SIT_ebs_patch','SIT_ebs_patch');
exec dbms_service.delete_service('PROD_ebs_patch');

exec dbms_service.start_service('ebs_SIT');

7.检查并测试连接PDB
su - oracle
. 19c_cdb.env
sqlplus / as sysdba

alter session set container=SIT;
set linesize 4000;
select name,pdb from cdb_services order by 2;
exit

sqlplus system/system_passwd@SIT
sqlplus apps/apps_passwd@SIT

8.切换到pdb环境变量,清理节点信息
source /u01/SIT/db/19.0.0/SIT_r122sitdb.env
sqlplus apps/<apps_passwd>@SIT
exec fnd_conc_clone.setup_clean;
commit;

9.切换到pdb环境变量,运行数据库节点autoconfig
source /u01/SIT/db/19.0.0/SIT_r122sitdb.env
ls -l $CONTEXT_FILE
cd $ORACLE_HOME/appsutil/bin

/*修复
The following report lists errors encountered during CVM Phase
      <filename>  <return code where appropriate>
  /u01/SIT/db/19.0.0/appsutil/bin/txkCfgUtlfileDir.sh  1
 No. of scripts failed in CVM phase: 1
*/

SIT_utlfiledir.txt

perl $ORACLE_HOME/appsutil/bin/txkCfgUtlfileDir.pl -contextfile=$CONTEXT_FILE -oraclehome=$ORACLE_HOME -outdir=$ORACLE_HOME/appsutil/log -mode=setUtlFileDir -skipdirvalidation=Yes

$ORACLE_HOME/appsutil/bin/adconfig.sh contextfile=$CONTEXT_FILE appspass=<apps_passwd>

DDL锁

xuwanxin1987阅读(83)

V$ACCESS 显示数据库中当前被锁定的对象以及正在访问这些对象的会话。
V$ACCESS displays objects in the database that are currently locked and the sessions that are accessing them.
Column Datatype Description
SID NUMBER Session number that is accessing an object
OWNER VARCHAR2(64) Owner of the object
OBJECT VARCHAR2(1000) Name of the object
TYPE VARCHAR2(24) Type identifier for the object
--RAC
select 'alter system kill session ''' || s.sid || ',' || s.serial# || ',@' || s.inst_id || ''' immediate;'
from gv$session s,gv$access a
where s.inst_id=a.inst_id
and s.sid=a.sid
and a.object='CUX_5_FND_COMMON_PKG'
and a.owner='APPS';
--Single instance
select 'alter system kill session ''' || s.sid || ',' || s.serial# || ''' immediate;'
from v$session s,v$access a
where s.sid=a.sid
and a.object='CUX_5_FND_COMMON_PKG'
and a.owner='APPS';
--Single instance
SELECT SID,
         SERIAL#,
         PADDR,
         'alter system kill session ''' || sid || ',' || serial# || ''' immediate;'
    FROM V$SESSION
   WHERE SID in (select /*+ rule*/
      SID
       from V$ACCESS
      WHERE object='CUX_5_FND_COMMON_PKG' and a.owner='APPS';
set lines 200 pages 50000
Select distinct 'ALTER SYSTEM DISCONNECT SESSION  ''' || b.sid ||','||b.SERIAL# || ',@' || b.INST_ID || ''' immediate;'
From dba_ddl_locks a, gv$session b
Where a.session_id=b.SID
And a.name =upper('CUX_5_FND_COMMON_PKG') and b.username=upper('APPS');

FRM-92102

xuwanxin1987阅读(246)

Description List for Parameters Affecting Timeout in Webforms (Doc ID 549735.1)
Relogin Screen Not Appear at Forms Timeout (Doc ID 2595618.1)
Primary note on Known Causes of FRM-92102 Error in Forms (Doc ID 756369.1)
Why Does Forms Only Attempt To Reconnect 1 Time, When NetWorkRetries Is Set To 30 (Doc ID 1272984.1)
Tuning Performance on E-Business suite (Doc ID 744143.1)
R12.1.3: frmweb Processes Exhaust CPU After Retrieving Large Number of Records (Doc ID 2303970.1)
Understanding How networkRetries Works (Doc ID 332942.1)
FRM-92101 Error When Trying To Open Any Form Using OAM Login (Doc ID 1682634.1)
Appsweb.cfg Heartbeat Parameter Needs to be Autoconfig Enabled (Doc ID 1533995.1)
R12 E-Business Suite Applications Technology Stack – Autoconfig Context File Entries Including OA_VAR, Variable Name, And Defined Values (Doc ID 458282.1)
What Is The ‘FORMS60_TIMEOUT’ Parameter And How To Use It ? (Doc ID 221172.1)
11i: How To Fix The Forms Timeout Issue In Oracle Applications (Doc ID 269884.1)
Master Note For Known Causes of FRM-92101 Error In Forms 10gR2 (Doc ID 604633.1)
Master Note for: Forms: 9.x and 10.x: Troubleshooting FRM-92050, FRM-921xx (Doc ID 189867.1)
Primary Note for Forms FRM-92050, FRM-92100, FRM-92101, FRM-92102 Reference Guide (Doc ID 444690.1)
R12: Forms Runtime Diagnostics (FRD), Tracing And Logging For Forms In Oracle Applications (Doc ID 438652.1)

https://blog.csdn.net/iteye_21199/article/details/82370366

proxyHost=null
proxyPort=0
connectMode=HTTP, native.
Forms Applet 版本为: 10.1.2.3
正在重新连接 (尝试第 1 次, 共 5 次)...
正在重新连接 (尝试第 2 次, 共 5 次)...
正在重新连接 (尝试第 3 次, 共 5 次)...
正在重新连接 (尝试第 4 次, 共 5 次)...
正在重新连接 (尝试第 5 次, 共 5 次)...
oracle.forms.net.ConnectionException: 500
	at oracle.forms.net.ConnectionException.createConnectionException(Unknown Source)
	at oracle.forms.net.HTTPNStream.getResponse(Unknown Source)
	at oracle.forms.net.HTTPNStream.doFlush(Unknown Source)
	at oracle.forms.net.HTTPNStream.flush(Unknown Source)
	at java.io.DataOutputStream.flush(Unknown Source)
	at oracle.forms.net.StreamMessageWriter.run(Unknown Source)
java.io.EOFException
	at java.io.DataInputStream.readUnsignedByte(Unknown Source)
	at oracle.forms.engine.Message.readDetails(Unknown Source)
	at oracle.forms.engine.Message.readDetails(Unknown Source)
	at oracle.forms.net.StreamMessageReader.run(Unknown Source)
FRM-92102 is Gerneric Error maybe occur for more than one reasons :
1-Network
2-Proxy
3-http
4-Session Time
But today i will discuss the problem On oracle Application server 10g .
Description for the problem like the following when you try to connect on your deploy application On OAS 10g it's gives the above error from 1-5 minutes.

I will give you more than one solution maybe it will be related to the above problem and you try them separately to see which one will be valid for you :
sed -i 's#networkRetries=0#networkRetries=30#g' $ORACLE_HOME/forms/server/formsweb.cfg
sed -i 's#networkRetries=0#networkRetries=30#g' $FORMS_WEB_CONFIG_FILE
sed -i 's#>.*</forms_network_retries>#>30</forms_network_retries>#g' $CONTEXT_FILE

egrep 'heartBeat|networkRetries' $FORMS_WEB_CONFIG_FILE
egrep 's_forms_heartbeat|forms_network_retries' $CONTEXT_FILE
egrep networkRetries $ORACLE_HOME/forms/server/formsweb.cfg

adformsctl.sh stop
adformsctl.sh start
结果一样,几分钟就掉线,这是因为networkRetries仅适用于在客户端上引发tcp/ip错误的情况
Understanding the networkRetries Parameter for Webforms

Under some circumstances the client receives an FRM-92102 error which is accompanied by the message that a network error has occurred and the forms client has attempted to re-establish its connection to the server 5 times without success. The reconnect is attempted only five times even if the networkRetries parameter is set to a different value.

This is because the networkRetries only applies where a tcp/ip error is raised on the client, which is propagated back to the forms applet as a java exception. However, not all network errors result in a tcp/ip error on the client. The five retries scenario occurs when there is no tcp/ip error raised. This is a situation where a connection is made with the servlet but the servlet returns an
error in the content, typically because the servlet session is invalid. There will be no tcp/ip error raised in this case, so it will be handled by different code and the networkRetries code will not be executed. This could occur, for example, when the JVM has crashed and been restarted by oprocmgr, so the servlet session ids are no longer recognized. It wouldn't actually help to try and
re-establish the connection by using networkRetries. If the servlet doesn't recognize the session id, retrying more times isn't going to make any difference. The internal not published Enhancement Request Bug:4285058 - ENHANCE SERVLET RETRY FUNCTIONALITY FOR NETWORK "HANGS" has been filed asking that the servlet retry functionality be enhanced to include network errors which occur for reasons other than a tcp/ip error on the client side.
egrep -i 's_sesstimeout|s_forms_time|s_oc4j_sesstimeout' $CONTEXT_FILE
s_sesstimeout = 1800000 (30 minutes)
s_forms_time = 60 (60 minutes)
s_oc4j_sesstimeout = 30 (30 minutes)
egrep -i 'enabled|disabled' $ORA_CONFIG_HOME/10.1.3/j2ee/forms/application-deployments/forms/formsweb/orion-web.xml
grep -i forms_tracking_cookies $CONTEXT_FILE

sed -i 's#disabled#enabled#g' $ORA_CONFIG_HOME/10.1.3/j2ee/forms/application-deployments/forms/formsweb/orion-web.xml
sed -i 's#>.*</forms_tracking_cookies>#>enabled</forms_tracking_cookies>#g' $CONTEXT_FILE
adformsctl.sh stop
adformsctl.sh start
结果一样,几分钟就掉线,并且可能会引发如下FRM-92101错误

Note:
After the above changes ensure users restart the client PC and clear browser and jre cache. Otherwise the following error may be received:
'FRM-92101: There was a failure in the Forms Server during startup'
select SESSION_COOKIE_NAME,WINDOW_COOKIE_NAME,SESSION_COOKIE_DOMAIN,SESSION_COOKIE  from icx_parameters;

update icx_parameters
set SESSION_COOKIE_NAME='',WINDOW_COOKIE_NAME='';

select SESSION_COOKIE_NAME,WINDOW_COOKIE_NAME,SESSION_COOKIE_DOMAIN,SESSION_COOKIE  from icx_parameters;
结果一样,几分钟就掉线
结果一样,几分钟就掉线

https://learn.microsoft.com/zh-cn/internet-explorer/ie11-deploy-guide/auto-detect-settings-for-ie11

这个设置是当前所使用的局域网中可能有服务器会向网内计算机自动发送网络配置文件和信息。开启该选项后会自动获得相关配置。 有些局域网配置相对静态,一般不更改设置,那我们手动配置完就可以了。但有些局域网可能经常会变动设置,并且服务器会下发这些配置更改,那开启自动检测设置就可以节省手动配置的时间,会比较方便,去掉自动检测的设置后不再报FRM-92102

SLA服务可用性

xuwanxin1987阅读(66)

SLA服务可用性
1年 = 365天 = 8760小时
99.9 = 8760 * 0.1% = 8760 * 0.001 = 8.76小时
99.95 = 8760 * 0.0005 = 0.876小时 = 4.38小时
99.99 = 8760 * 0.0001 = 0.876小时 = 0.876 * 60 = 52.6分钟
99.999 = 8760 * 0.00001 = 0.0876小时 = 0.0876 * 60 = 5.26分钟

Oracle ASM磁盘组不均衡

xuwanxin1987阅读(92)

ASM External Redundancy Diskgroups Present Large Imbalance (V$ASM_DISK.FREE_MB) In 11.2 or 12.1 Releases (Doc ID 1980292.1)
Top Issues : Space Imbalance in ASM Diskgroup (Doc ID 2247747.1)
Space Imbalance in ASM Normal Redundancy Diskgroup Having a Quorum Failgroup (Doc ID 1379665.1)

问题 #1 ASM 外部冗余磁盘组存在严重的不平衡 (V$ASM_DISK.FREE_MB)
问题 #2 具有仲裁故障组的 ASM 正常冗余磁盘组中的空间不平衡
问题 #3 不平衡的 ASM 磁盘组,它不是均匀平衡(自动或手动)

set lines 200 pages 50000
col file_name for a80
col TABLESPACE_NAME for a30
select file_id,file_name,tablespace_name,autoextensible,BYTES/1024/1024,MAXBYTES/1024/1024,status from dba_data_files order by 1;
select file_id,file_name,tablespace_name,autoextensible,BYTES/1024/1024,MAXBYTES/1024/1024,status from dba_temp_files order by 1;

set lines 200 pages 50000
col name for a25
col path for a55
select group_number,disk_number,name,path,total_mb/1024 total_G,mode_status,repair_timer from v$asm_disk order by group_number,disk_number;

alter diskgroup DATA check all repair;
alter diskgroup DATA rebalance power 11;

select * from v$asm_operation;

SYSAUX表空间清理(仅供参考)

xuwanxin1987阅读(81)

truncate table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY;
truncate table sys.WRI$_OPTSTAT_HISTGRM_HISTORY;

exec dbms_stats.purge_stats(sysdate-101);
exec dbms_stats.purge_stats(sysdate-51);
exec dbms_stats.purge_stats(sysdate-5);
exec dbms_stats.alter_stats_history_retention(10);

alter table sys.WRI$_OPTSTAT_HISTHEAD_HISTORY move tablespace sysaux;
alter index sys.I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST rebuild online;
alter index sys.I_WRI$_OPTSTAT_HH_ST rebuild online;
alter table sys.WRI$_OPTSTAT_HISTGRM_HISTORY move tablespace sysaux;
alter index sys.I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST rebuild online;
alter index sys.I_WRI$_OPTSTAT_H_ST rebuild online;
alter table sys.WRI$_OPTSTAT_IND_HISTORY move tablespace sysaux;
alter index sys.I_WRI$_OPTSTAT_IND_OBJ#_ST rebuild online;
alter index sys.I_WRI$_OPTSTAT_IND_ST rebuild online;
alter table sys.WRI$_OPTSTAT_TAB_HISTORY move tablespace sysaux;
alter index sys.I_WRI$_OPTSTAT_TAB_OBJ#_ST rebuild online;
alter index sys.I_WRI$_OPTSTAT_TAB_ST rebuild online;
ALTER TABLE SYS.WRI$_OPTSTAT_OPR MOVE TABLESPACE SYSAUX;
ALTER TABLE SYS.WRI$_OPTSTAT_AUX_HISTORY MOVE TABLESPACE SYSAUX;
ALTER INDEX SYS.I_WRI$_OPTSTAT_AUX_ST REBUILD ONLINE;
ALTER INDEX SYS.I_WRI$_OPTSTAT_OPR_STIME REBUILD ONLINE;

EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_HISTHEAD_HISTORY',cascade => TRUE);
EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_HISTGRM_HISTORY',cascade => TRUE);
EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_IND_HISTORY',cascade => TRUE);
EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname =>'WRI$_OPTSTAT_TAB_HISTORY',cascade => TRUE);
EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_OPR',cascade => TRUE);
EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRI$_OPTSTAT_AUX_HISTORY',cascade => TRUE);

delete from sys.wrh$_active_session_history a where not exists (select 1 from sys.wrm$_snapshot b where a.snap_id=b.snap_id and a.dbid=b.dbid and a.instance_number=b.instance_number);

alter table sys.wrh$_active_session_history enable row movement;
alter table sys.wrh$_active_session_history shrink space cascade;
alter table sys.wrh$_active_session_history disable row movement;
EXEC dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'WRH$_ACTIVE_SESSION_HISTORY',cascade => TRUE);

联系我们

登录

找回密码

注册