undo自动调优介绍

Oracle 10gr2的后续版本中添加了撤销(UNDO)信息最短保留时间段自动调优的特性,不再仅仅依据参数UNDO_RETENTION的设定,其调优原则如下:

l  当撤销表空间(UNDO TABLESPACE)大小固定,Oracle将根据表空间的大小和实际的系统负载动态调整撤销信息保存时间,该最短保存时间的具体长短基于撤销表空间大小的一定比例值公式换算后获得;它总是比设定的UNDO_RETENTION大,当撤销表空间大量空闲情况下可能远远大于UNDO_RETENTION。

l  当撤销表空间设定为自动扩展空间情况下,Oracle将动态调整撤销信息最短保留时间为该时段最长查询时间(MAXQUERYLEN)加上300秒或参数UNDO_RETENTION间的较大者,即MAX((MAXQUERYLEN+300),UNDO_RENTION);同样的,该最短保存时间可能远远大于设定的UNDO_RETENTION。

在自动调整情况下,实际的撤销信息最短保留时间可以通过查询V$UNDOSTAT视图上的TUNED_UNDORETENTION列获得。

在无法就撤销表空间做相应修改的情况,我们可以通过修改隐式参数” _UNDO_AUTOTUNE”为FALSE关闭该自动调优特性。以上设定生效后,V$UNDOSTAT视图上TUNED_UNDORETENTION列不再更新,且撤销信息最短保留时间固定为参数UNDO_RETENTION的设定值。该参数可以不用重启数据库而动态设置生效。

Oracle Supplemental 补全日志介绍

Oracle补全日志(Supplemental logging)特性因其作用的不同可分为以下几种:最小(Minimal),支持所有字段(all),支持主键(primary key),支持唯一键(unique),支持外键(foreign key)。包括LONG,LOB,LONG RAW及集合等字段类型均无法利用补全日志。

最小(Minimal)补全日志开启后可以使得logmnr工具支持链式行,簇表和索引组织表。可以通过以下SQL检查最小补全日志是否已经开启:

SELECT supplemental_log_data_min FROM v$database;

若结果返回YES或IMPLICIT则说明已开启最小补全日志,当使用ALL,PRIMARY,UNIQUE或FOREIGN补全日志时最小补全日志默认开启(即检查结果为IMPLICIT)。

一般情况下我们在使用逻辑备库时启用主键和惟一键的补全日志,而有时表上可能没有主键,惟一键或唯一索引;我们通过以下实验总结这种情况下Oracle的表现。

首先建立相关的测试表:

alter database add supplemental log data (primary key,unique index) columns ;

create table test (t1 int , t2 int ,t3 int ,t4 int );

alter table test add constraint pk_t1 primary key (t1); –添加主键

随后使用循环插入一定量的数据

update test set t2=10;       commit;   — 更新数据

使用LOGMNR工具分析之前的操作,可以看到REDO中记录的SQL形式如下:

update “SYS”.”TEST” set “T2” = ’10’ where “T1” = ’64’ and “T2” = ’65’ and ROWID = ‘AAAMiSAABAAAOhiAA/’;

其中where字句后分别记录了主键值,被修改字段的值和原行的ROWID。

现在我们将原表上的主键去掉来观察。

alter table test drop constraint pk_t1 ;

update test set t2=11;       commit;   — 更新数据

使用LOGMNR分析可以发现,REDO中的SQL记录如下:

update “SYS”.”TEST” set “T2” = ’11’ where “T1” = ‘1’ and “T2” = ’10’ and “T3” = ‘3’ and “T4” = ‘4’ and ROWID = ‘AAAMiSAABAAAOhiAAA’;

当没有主键的情况下,where子句后记录了所有列值和ROWID。

以下实验在存在唯一索引情况下的表现

create unique index pk_t1 on test(t1);

update test set t2=15; commit;

使用LOGMNR分析可以发现,REDO中的SQL记录如下:

update “SYS”.”TEST” set “T2” = ’15’ where “T1” = ‘9’ and “T2” = ’11’ and “T3” = ’11’ and “T4” = ’12’ and ROWID = ‘AAAMiSAABAAAOhiAAI’;

以上是t1列有唯一索引但不限定not null的情况,下面我们加上not null限制

alter table test modify t1 not null;

update test set t2=21; commit;

使用LOGMNR分析可以发现,REDO中的SQL记录如下:

update “SYS”.”TEST” set “T2” = ’21’ where “T1” = ‘2’ and “T2” = ’15’ and ROWID = ‘AAAMiSAABAAAOhiAAB’;

如以上SQL所示,在存在唯一索引的情况下where子句后仍记录了所有列和ROWID;在存在唯一索引和非空约束的情况下表现与存在主键的情况一致。

当某个表上的列数量较多时且没有主键或唯一索引和非空约束的情况下,开启补全日志可能导致重做日志总量大幅提高。

首先建立一个存在250列的表:

Drop table test;

create table test (

t1 varchar2(5),

t2 varchar2(5),

t3 varchar2(5),

t4 varchar2(5),  …t250 varchar2(5))

insert into test values (‘TEST’,’TEST’ ……);   commit; –将255个列填入数据

alter database drop supplemental log data (primary key,unique index) columns;  –关闭补全日志

set autotrace on;

update test set t2=’BZZZZ’ where t1=’TEST’; commit;

可以从自动跟踪信息中看到,本条更新产生了516的重做量。

alter database add supplemental log data (primary key,unique index) columns;  –重新开启补全日志

update test set t2=’FSDSD’ where t1=’TEST’;

跟踪信息显示产生了3044的重做量。

补全日志因作用域的不同又可分为数据库级的和表级的。表级补全日志又可以分为有条件的和无条件的。有条件限制的表级补全日志仅在特定列被更新时才会起作用,有条件限制的表级补全日志较少使用,这里我们不做讨论。

下面我们来观察无条件限制表级补全日志的具体表现:

alter database drop supplemental log data (primary key,unique index) columns;

alter table test add supplemental log data (primary key,unique index) columns;

update test set t2=’ZZZZZ’; commit;

使用LOGMNR工具查看redo中的SQL:
update “SYS”.”TEST” set “T2” = ‘ZZZZZ’ where “T1” = ‘TEST’ and “T2” = ‘AAAAA’ and “T3” = ‘TEST’………

可以发现where子句之后包含了所有列值。

delete test; commit;

使用LOGMNR工具查看redo中的SQL:

delete from “SYS”.”TEST” where “T1” = ‘TEST’ and “T2” = ‘ZZZZZ’ and “T3” = ‘TEST’ and “T4” = ‘TEST’ and “T5” ……

delete操作同样在where子句之后包含了所有列值。

又我们可以针对表上字段建立特定的补全日志组,以减少where子句后列值的出现。

alter table test drop supplemental log data (primary key,unique index) columns;  –关闭表上原先的补全日志

alter table test add supplemental log group test_lgp (t1 ,t2,t3,t4,t5,t6,t12,t250) always; –创建补全日志组

update test set t2=’XXXXX’ ; commit;

使用LOGMNR工具查看redo中的SQL:

update “SYS”.”TEST” set “T2” = ‘XXXXX’ where “T1” = ‘TEST’ and “T2” = ‘TEST’ and “T3” = ‘TEST’ and “T4” = ‘TEST’ and “T5” = ‘TEST’ and “T6” = ‘TEST’ and “T12” = ‘TEST’ and “T250” = ‘TEST’ and ROWID = ‘AAAMieAABAAAOhnAAA’;

如上所示重做日志中正确地显示了UPDATE操作中用户指定的字段值。

delete test;

使用LOGMNR工具查看redo中的SQL:

delete from “SYS”.”TEST” where “T1” = ‘TEST’ and “T2” = ‘XXXXX’ and “T3” = ‘TEST’ ……

delete操作在重做日志中仍然保留了所有列值。

针对字段较多的表,我们在能够以多个列保证数据唯一性且非空的情况下(即应用概念上的主键)来指定表上的补全日志组,以减少update操作时所产生的重做日志,而对于delete操作则无法有效改善。

Script:Generating CREATE USER DDL Statements

Title: Generating CREATE USER DDL Statements
Author:Ted Martin, a database administrator in Ottawa, Ontario, Canada.
These scripts will generate SQL DDL statements related to the creation of user accounts. The types of statements generated
are as follows:
1. CREATE USER and ALTER USER...QUOTA x ON [tabspace] (GENUSER.SQL)
2. CREATE role (GENROLE.SQL)
3. GRANT [role|priv] TO user (GRANTPRIV.SQL)
All three scripts ask for execution parameters. If you leave such a parameter blank, the script will generate for all. The
exception is the prompt for the output filename.
Source/Text/Comments
REM
REM    PROGRAM-ID : GENUSER.SQL
REM    WRITTEN BY : Ted Martin
REM  DATE WRITTEN : 26-AUG-1998
REM
clear screen
PROMPT GENUSER.SQL           Generates CREATE USER commands
PROMPT
PROMPT Includes ALTER USER...QUOTA x ON tabspace commands
PROMPT
accept uname prompt 'Enter User Name : '
accept outfile prompt  ' Output filename : '
col username noprint
col lne newline
set heading off pagesize 0 verify off feedback off
spool &&outfile..gen
prompt genuser.log
prompt set term on echo off
prompt prompt Creating User Accounts...
prompt set term off echo on
SELECT username, 'CREATE USER '||username||' '||
DECODE(password, 'EXTERNAL', 'IDENTIFIED EXTERNALLY',
'IDENTIFIED BY '''||password||''' ') lne,
'DEFAULT TABLESPACE '||default_tablespace lne,
'TEMPORARY TABLESPACE '||temporary_tablespace||';' lne
FROM DBA_USERS
WHERE USERNAME LIKE UPPER('%&&uname%')
OR UPPER('&&uname') IS NULL
ORDER BY USERNAME;
prompt set term on echo off
prompt prompt Granting Tablespace Quotas...
prompt set term off echo on
SELECT username, 'ALTER USER '||username||' QUOTA '||
DECODE(MAX_BYTES, -1, 'UNLIMITED', TO_CHAR(ROUND(MAX_BYTES/1024))||'K')
||' ON TABLESPACE '||tablespace_name||';' lne
FROM DBA_TS_QUOTAS
WHERE USERNAME LIKE UPPER('%&&uname%')
OR UPPER('&&uname') IS NULL
ORDER BY USERNAME;
spool off
PROMPT
PROMPT File &&outfile..GEN generated. Please review before using
PROMPT
EXIT
=============================================================
REM
REM      PROGRAM-ID : GENROLE.SQL
REM      WRITTEN BY : Ted Martin
REM    DATE WRITTEN : 6-APR-1996
REM
set term on  echo off   linesize 132  pagesize 0  heading off
set verify off
clear screen
prompt GENROLE.SQL V1.0            Generate CREATE ROLE statements
prompt
prompt
accept rname   prompt  '      Grant Role : '
accept outfile prompt  ' Output filename : '
set feedback off pagesize 0 heading off
col lne newline
spool &&outfile..gen
prompt prompt Run Parameters
prompt prompt . . Role = &&rname
prompt spool &&outfile..log
prompt set term on  echo off  feedback on
select 'CREATE ROLE '||role||';' lne
from dba_roles
where role like UPPER('%&&rname%')
and role not in ('CONNECT', 'RESOURCE', 'DBA',
'EXP_FULL_DATABASE', 'IMP_FULL_DATABASE')
ORDER BY ROLE;
prompt spool off
prompt exit
spool off
prompt Script &&outfile..gen ready. Review before using it.
exit
=====================================================
REM
REM      PROGRAM-ID : GRANTPRIVS.SQL
REM      WRITTEN BY : Ted Martin
REM    DATE WRITTEN : 26-AUG-1998
REM
clear screen
set term on  echo off   linesize 132  pagesize 0  heading off
set verify off
prompt GRANTPRIVS.SQL             Generate Existing GRANT role/priv statements
prompt
prompt Handles both Roles and System Privs. Excludes SYS and SYSTEM accounts
prompt
accept rname   prompt  '      Grant Priv : '
accept towner   prompt '         To User : '
accept outfile prompt  ' Output filename : '
set feedback off  verify off
spool &&outfile..gen
prompt prompt Run Parameters
prompt prompt . . Priv = &&rname
prompt prompt . . User = &&towner
prompt spool &&outfile..log
prompt set term on  echo on  feedback on
col grantee noprint
col granted_priv noprint
select grantee, granted_role granted_priv,
'GRANT '||granted_role||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_role_privs
where (granted_role like upper('%&&rname%') or '&&rname' IS NULL)
or (grantee like upper('%&&towner%') or '&&towner' is null)
and grantee not in ('SYS', 'SYSTEM')
UNION
select grantee, privilege granted_priv,
'GRANT '||privilege||' to '||grantee||
DECODE(ADMIN_OPTION, 'Y', ' WITH ADMIN OPTION;', ';')
from dba_sys_privs
where (privilege like upper('%&&rname%') or '&&rname' IS NULL)
or (grantee like upper('%&&towner%') or '&&towner' is null)
and grantee not in ('SYS', 'SYSTEM')
order by 1, 2;
prompt spool off
prompt exit
spool off
prompt Script &&outfile..gen ready. Review before using it.
exit

Script:Diagnostic ORA-01000 maximum open cursors exceeded

以下脚本可以用于诊断ORA-01000打开游标过多错误:

set linesize 140 pagesize 1400
select
to_char(100 * sess / calls, '999999999990.00') || '%' cursor_cache_hits,
to_char(100 * (calls - sess - hard) / calls, '999990.00') || '%' soft_parses,
to_char(100 * hard / calls, '999990.00') || '%' hard_parses
from
( select value calls from v$sysstat where name = 'parse count (total)' ),
( select value hard from v$sysstat where name = 'parse count (hard)' ),
( select value sess from v$sysstat where name = 'session cursor cache hits' )
/
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%cursor ca%'
/
select sum(a.value), b.name,a.sid
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
group by rollup (b.name,a.sid)
order by 1
/
select a.value, s.username, s.sid, s.serial#
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'session cursor cache count' 
order by 1 
/
select sid, count(*) from v$open_cursor group by sid
order by 2 
/
Exec   DBMS_WORKLOAD_REPOSITORY.create_snapshot(); 
exec dbms_lock.sleep(300);
Exec   DBMS_WORKLOAD_REPOSITORY.create_snapshot(); 
@?/rdbms/admin/awrrpt
upload the awr report
or 
select dbms_workload_repository.awr_report_text(l_dbid     => dbid,
l_inst_num => instance_number,
l_bid      => mid - 1,
l_eid      => mid)
from (select vd.dbid, vi.instance_number, mid
from v$database vd,
v$instance vi,
(select max(snap_id) mid from dba_hist_snapshot dhs))
/

Oracle Event 10357 and 10351

[oracle@rh2 ~]$ oerr ora 10357
10357, 00000, "turn on debug information for direct path"
// *Cause:
// *Action:  turn on debug information for direct path
The cause of this issue was identified as unpublished Bug 9650718
In the bug, it was found that during cleanup from a direct path load, qesmm context was freed
without the pointer to the context being set to zero. Because the pointer was non zero,
there was code that tried to use the context.
To help in diagnosing the issue, the following was used:
This command will enable tracing for direct path operations in the server:
ALTER SYSTEM SET EVENTS 'TRACE [DIRPATH_LOAD] DISK=high';
This command will enable tracing for the I/O component of direct path load:
alter system set events '10357 trace name context forever, level 12';
klmalp: heap=0x7f9bd14efea8 size=72 comment=klcliti:klclih+kltb+klpt mem=0x7f9bd14eab98
klmalp: heap=0x7f9bd14efea8 size=224 comment=klcliti:klclih+kltb+klpt mem=0x7f9bd14eabe0
klmalp: heap=0x7f9bd14efea8 size=336 comment=klcliti:klclih+kltb+klpt mem=0x7f9bd14eacc0
klmalp: heap=0x7f9bd14efea8 size=80 comment=klcliti:klclih+kltb+klpt mem=0x7f9bd14eae10
klmalp: heap=0x7f9bd14efea8 size=8168 comment=kdobjrbb mem=0x7f9bd14edbc0
klmalp: heap=0x7f9bd14efea8 size=1150 comment=ktbbhs:kdbh mem=0x7f9bd14eb230
klmalp: heap=0x7f9bd14efea8 size=432 comment=kcbl_structure_instance mem=0x7f9bd14eb6b0
klmalp: heap=0x7f9bd14efea8 size=64 comment=kllcqc:kllcq mem=0x7f9bd14eb860
klmalp: heap=0x7f9bd14efea8 size=1312 comment=kllcqc:kllcqslt mem=0x7f9bd14ecb40
kcblin: lbs=0x7f9bd14eb6b0 flg=104 slt=(nil) cnt=2 sz=262144 st_obj=0x8b0cc8c0 fb=0
kcblin: state objects are: Call=8b0cc8c0, Current Call=8b0cc8c0, Session=8b082f18, Proc=8bae46c8
kdblil2<-klclil1r<-qerltFRop<-qercoRop<-kdstf0000101km<-kdsttgr<-qertbFetch<-qercoFetch<-rwsfcd<-qerltFetch
<-insdlexe<-insExecStmtExecIniEngine<-insexe<-opiexe<-kpoal8<
-opiodr<-ttcpip<-opitsk<-opiino<-opiodr
klmalp: heap=0x7f9bd14efea8 size=48 comment=kdblix:klixllkey mem=0x7f9bd14ed060
klmalp: heap=0x7f9bd14efea8 size=528 comment=kcblinlm mem=0x7f9bd14ed090
kcblnb: lbs=d14eb6b0, slt=0, d=10dd2a9, nwr=0, cnt=0
klmalp: heap=0x7f9bd14efea8 size=262656 comment=kllcqgf:kllsltba mem=0x7f9bd135de00
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2aa, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ab, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ac, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ad, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ae, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2af, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2b0, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2b1, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2b2, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2b3, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2b4, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2b5, nwr=0, cnt=0
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2b6, nwr=0, cnt=0
kcblcow: lbs=d14eb6b0, nslots=2, nwr=1
kcblco:lbs=d14eb6b0 slt=d14ecb40 typ=1 afn=4 blk=905897 d=10dd2a9 cnt=17 buf=d135de00 rq=d14ecc18fob=898375c8
kcblco ret: lbs=d14eb6b0 slt=d14ecb40 type=1 afn=4 blk=905897 cnt=17 buf=d135de00 rq=d14ecc18 fob=898375c8, wait=0, more=-783370504, wrc=0
kcblcow: slt=d14ecb40 dba=10dd2a9, sz=32, blks=23, st=1, idx=0
kcblio: lbs=d14eb6b0 slt=d14ecdd0 nslt=2, d=10dd2c0, st=2, fbon=0, flg=104
kcblio: Logging lbs=d14eb6b0 slt=d14ecdd0 nslt=2, d=10dd2c0, st=2
kcblgr: lbs=d14eb6b0 slt=d14ecdd0, rd=10dd2c0, flg=104, blks=32
kcbldio:lbs=d14eb6b0 flg=104 slt=d14ecdd0 typ=1 async=16 afn=4 blk=905920 cnt=20 buf=d13ade00 rq=d14ecea8 fob=0 blks=32 st=2
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e1, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e2, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e3, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e4, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e5, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e6, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e7, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e8, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2e9, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ea, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2eb, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ec, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ed, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ee, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ef, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f0, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f1, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f2, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f3, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f4, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f5, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f6, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f7, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f8, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2f9, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2fa, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2fb, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2fc, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2fd, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2fe, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd2ff, nwr=1, cnt=23
kcblnb: lbs=d14eb6b0, slt=d14ecb40, d=10dd300, nwr=1, cnt=23
[oracle@rh2 ~]$ oerr ora 10351
10351, 00000, "size of slots"
// *Cause:
// *Action:  sets the size of slots to use
// *Comment: a slot is a unit of I/O and this factor controls the size
// *Comment: of the IO.
alter session set events '10351 trace name context forever, level 128';
level 128 -> direct path write max block 128
I generated a new run of the big testcase with event 10357, Patch 4417285
applied, manual workarea_size_policy, sort_area_size=50000000,
db_file_multiblock_read_count=16 and event 10351 with level 128.
I tried it with disk_asynch_io=TRUE and FALSE just to be certain this is not
something related to the async.
In the trace files I see something very peculiar. The slots size is 128 as
expected and I see many writes of 128 blocks but not all of them are and they
look like the they come in clusters. A few 128 writes, then a lot smaller of
different sizes but mainly less than 16 blocks and then another cluster of
big ones and so on.
kcblcow: dba=100c91b, sz=128, blks=117, st=3, idx=14
kcblcow: dba=100c91b, sz=128, blks=117, st=3, idx=14
kcblcow: dba=100c991, sz=128, blks=1, st=3, idx=15
kcblcow: dba=100c91b, sz=128, blks=117, st=3, idx=14
kcblcow: dba=100c991, sz=128, blks=1, st=3, idx=15
kcblcow: dba=100c990, sz=128, blks=1, st=3, idx=0
kcblcow: dba=100c992, sz=128, blks=128, st=3, idx=1
kcblcow: dba=100c992, sz=128, blks=128, st=3, idx=1
kcblcow: dba=100ca12, sz=128, blks=39, st=3, idx=2
kcblcow: dba=100c992, sz=128, blks=128, st=3, idx=1
kcblcow: dba=100ca12, sz=128, blks=39, st=3, idx=2
kcblcow: dba=100ca3a, sz=128, blks=1, st=3, idx=3
kcblcow: dba=100ca12, sz=128, blks=39, st=3, idx=2
kcblcow: dba=100ca3a, sz=128, blks=1, st=3, idx=3
kcblcow: dba=100ca39, sz=128, blks=1, st=3, idx=4
but
it is possible that there are other factor out of our control that forces
Oracle to stop adding blocks to the slot and write small batches.
In conclusion, in order to have the least ammount of direct operations and
have the maximum possible read/write batches these are the parameters to set
:
alter session set events '10351 trace name context forever, level 128';
alter session set workarea_size_policy=manual;
alter session set sort_area_size=50000000;

Audit Logon above 9i

1. Enable audit. Set the parameter to
audit_trail=db (or db,extended)
2. Restart the database instance to enable the audit settings.
3. Set up audit for session:
audit session whenever successful;
4. After a relevant period of time, check the DBA_AUDIT_SESSION view, in the documentation

LOGOFF_LREAD Logical reads for the session
LOGOFF_PREAD Physical reads for the session
LOGOFF_LWRITE Logical writes for the session
SESSION_CPU Amount of CPU time used by each Oracle session

A query example:

select username,sum(logoff_lread) "TOTAL READS",
sum(logoff_pread) "TOTAL PHYS READS",
sum(logoff_lwrite) "TOTAL WRITES",
sum(session_cpu) "TOTAL CPU",
sum(logoff_pread)/count(*) "READS/SESSIO",
sum(logoff_lwrite)/count(*) "PHYS_READS/SESSION",
sum(logoff_lwrite)/count(*) "WRITES/SESSION",
sum(session_cpu)/count(*) "CPU/SESSION"
from dba_audit_session group by username;

The range of values can be restricted using the TIMESTAMP and/or LOGOFF_TIME columns (which are the logon and logoff interval ends) to have the results for a specific period of time.

[oracle@rh2 ~]$
[oracle@rh2 ~]$ sqlplus maclean/fdsfds
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Jul 8 12:48:05 2009
Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
ERROR:
ORA-01017: invalid username/password; logon denied
select * from dba_audit_session
OS_USERNAME
--------------------------------------------------------------------------------------------------------------------------------------------
USERNAME
------------------------------
USERHOST
--------------------------------------------------------------------------------------------------------------------------------
TERMINAL
--------------------------------------------------------------------------------------------------------------------------------------------
TIMESTAMP ACTION_NAME                  LOGOFF_TI LOGOFF_LREAD LOGOFF_PREAD LOGOFF_LWRITE LOGOFF_DLOCK                              SESSIONID
--------- ---------------------------- --------- ------------ ------------ ------------- ---------------------------------------- ----------
RETURNCODE CLIENT_ID                                                        SESSION_CPU
---------- ---------------------------------------------------------------- -----------
EXTENDED_TIMESTAMP                                                          PROXY_SESSIONID GLOBAL_UID                       INSTANCE_NUMBER
--------------------------------------------------------------------------- --------------- -------------------------------- ---------------
OS_PROCESS
----------------
oracle
MACLEAN
rh2.oracle.com
pts/0
08-JUL-09 LOGOFF                       08-JUL-09          655           51            16 0                                            960800
0                                                                            9
08-JUL-09 12.45.42.813460 PM +08:00                                                                                                        0
6159
oracle
MACLEAN
rh2.oracle.com
pts/0
08-JUL-09 LOGON                                                                                                                       960801
1017
08-JUL-09 12.46.17.938293 PM +08:00                                                                                                        0
6168
oracle
MACLEAN
rh2.oracle.com
pts/0
08-JUL-09 LOGON                                                                                                                       960802
1017
08-JUL-09 12.48.05.234442 PM +08:00                                                                                                        0
6176
oracle
MACLEAN
rh2.oracle.com
pts/0
08-JUL-09 LOGON                                                                                                                       960803
0
08-JUL-09 12.48.40.687569 PM +08:00                                                                                                        0
6181

手动递增SCN号的几种方法:How to increase System Change Number by manual

 

 

手动递增SCN号的几种方法

 

除去下面几种,还有一种方法直接修改 实例的Global Lamport SCN,在SGA中由kcsgscn变量存储,对于一个实例来说这是唯一的源SCN,所有其他的SCN均由这个source scn所驱动。 这种递增方式是直接用oradebug 修改该Global Lamport SCN kcsgscn

 

 

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

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

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

 

 

SQL> oradebug setmypid
Statement processed.

 

SQL> select to_char(current_scn,’XXXXXXXXXXXX’) from v$database;

TO_CHAR(CURRE
————-
D3E1E

SQL>
SQL> oradebug DUMPvar SGA kcsgscn
kcslf kcsgscn_ [060012658, 060012688) = 000D3E1E 00000000 00000000 00000000 0000162D 00000000 00000000 00000000 00000000 00000000 60012338 00000000

 

ORADEBUG POKE 0x060012658 4 0xfffff

poke 命令的语法

<address> <length> <value>” allows you to modify a given region of memory (length of memory is limited to size of scalar C types)

 

SQL> select current_scn from v$database;

CURRENT_SCN
———–
1048583

 

SQL> select to_char(current_scn,’XXXXXXXXXXXX’) from v$database;

TO_CHAR(CURRE
————-
10000B

SQL> oradebug DUMPvar SGA kcsgscn
kcslf kcsgscn_ [060012658, 060012688) = 00100010 00000000 00000000 00000000 0000004E 00000000 00000000 00000000 00000000 00000000 60012338 00000000

 

 

 

 

 

How to jump SCN  by manual ,  this could be a problem:

1. We can bump up the SCN by using the procedure from Note: 386830.1

 

Bump the system SCN on the primary database to fix any metadata index corruptions for both
primary and physical standby databases. Set the following parameters in the init.ora and restart
the database in restricted mode to bump the system SCN of the primary database.
For Real Application Clusters, perform the steps on only one node of the cluster. 
Init.ora syntax:
*._allow_error_simulation = TRUE
*._smu_debug_mode = 268435456
If using an spfile, Oracle recommends creating a temporary init.ora using the CREATE PFILE SQL command.
SQL> create pfile='/tmp/initTMP.ora' from spfile='';
Then add the parameters to this temporary file.
To use the temporary init.ora file when starting the instance, include the 'PFILE' clause with the STARTUP SQL command i.e.
SQL> startup restrict pfile=
WARNING: These parameters should only be used for this fix and must be removed immediately afterwards in step 2.
If the above parameters are used through multiple database restarts, a complete database rebuild will be required.
To know the system SCN has been bumped, monitor the instance's alert.log for the following message:
advance SCN to wrap base xxxx
Where xxx represents the new wrap SCN.

 

 

 

 

2.EVENT: ADJUST_SCN – Quick Reference (Doc ID 30681.1)

 

 

 

WORKAROUND:
-----------
Searched in webiv (ora-1555, ora-604 see note:1063408.6 ) suggests workaround
is to adjust serial number using event
Will try : (30681.1)
alter session set events 'IMMEDIATE trace name ADJUST_SCN level 1';
ora-600 [2256][0][1073741824][1][293672646]
ERROR:
ORA-600 [2256][a][b][c][d][e]
VERSIONS:
versions 7.3.X, 8.0.X, 8.1.X
DESCRIPTION:
This exception indicates that you attempted to ADJUST_SCN but the level
supplied would be less that the current SCN.
ARGUMENTS:
a.  Requested SCN WRAP
b.  Requested SCN BASE
c.  Current SCN WRAP
d.  Current SCN BASE = [293672646]
*4 = 1174690584
will try level 2
ora-600 2256[0][2147483648][1][293672646]
level 3
ora-600 2256[0][3221225472][1][293672646]
Now, we increase the SCN on DST7 by using the ADJUST_SCN event -
(Note: do not use this event outside of your test environment)
set the following hidden parameter in init.ora on DST7 database and bounce
the database.
_allow_error_simulation=true
Now, the SCN is increased by doing -
alter system set events 'immediate trace name adjust_scn level ';
where  translates to (n*0x40000000) as the target SCN value. ie. n = 1
will set the SCN as 0x0000.40000000. For the testcase pick a value that is
larger than the current SCN on both databases by atleast 100000 SCNs (our
earlier SCN adjust threshold value).
alter system set events 'immediate trace name adjust_scn level 2';
select to_char(current_scn, 'xxxxxxxxxxxx') from v$database;

 

 

 

3.Note 552438.1 How To Adjust the SCN using parameter _MINIMUM_GIGA_SCN

 

 

 

 

Parameter: MINIMUM_GIGA_SCN
~~~~~~~~~~~~~~~~~~~~~~~~~~~
@Oracle8i:	HIDDEN
@Identifier:	kcmmsn
@Versions:	See <IVERS.MINIMUM_GIGA_SCN>
Values:
Related:
Description:	Minimum SCN to start with in 2^30 units
~~~~~~~~~~~~
Articles:	
<Event:ADJUST_SCN>
Overview of Init.Ora Parameter Reference notes

终极方案:
使用bbed修改datafile header实现修改scn

主要是修改system01.dbf datafile header kcvfh.kcvfhckp.kcvcpscn

helpbkup_us@oracle.com
helpkern_us@oracle.com
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
1* select name,CHECKPOINT_CHANGE# from v$datafile
SQL> /
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf                  3905523
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_undotbs1_9f2flf52_.dbf                3905523
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_sysaux_9f2flf2v_.dbf                  3905523
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_users_9f2flf5g_.dbf                   3905523
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_example_9f2fmfto_.dbf                 3905523
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_youchuan_9f2fqwr4_.dbf                3905523
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_guobao_9f2fwkkt_.dbf                  3905523
/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_tsoa_d_bjx6j57v_.dbf                  3905523
SQL>  select to_char(3905523,'XXXXXXXXXXXXX') from dual;
TO_CHAR(390552
--------------
3B97F3
SQL> oradebug tracefile_name
/s01/oracle/product/10.2.0/db_1/admin/MACLEAN1/udump/maclean1_ora_9468.trc
[oracle@vrh8 ~]$ cp /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf  /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf.bak
[oracle@vrh8 ~]$ bbed filename=/s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf password=blockedit
BBED: Release 2.0.0.0.0 - Limited Production on Tue Mar 24 16:50:39 2015
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
************* !!! For Oracle Internal Use only !!! ***************
BBED> set mode edit
MODE            Edit
BBED>  set blocksize 8192
BLOCKSIZE       8192
BBED> set block 1
BLOCK#          1
BBED> map
File: /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system (0)
Block: 1                                     Dba:0x00000000
------------------------------------------------------------
Data File Header
struct kcvfh, 676 bytes                    @0       
ub4 tailchk                                @8188    
BBED> p kcvfh
struct kcvfh, 676 bytes                     @0       
struct kcvfhbfh, 20 bytes                @0       
ub1 type_kcbh                         @0        0x0b
ub1 frmt_kcbh                         @1        0xa2
ub1 spare1_kcbh                       @2        0x00
ub1 spare2_kcbh                       @3        0x00
ub4 rdba_kcbh                         @4        0x00400001
ub4 bas_kcbh                          @8        0x00000000
ub2 wrp_kcbh                          @12       0x0000
ub1 seq_kcbh                          @14       0x01
ub1 flg_kcbh                          @15       0x04 (KCBHFCKV)
ub2 chkval_kcbh                       @16       0x4fc6
ub2 spare3_kcbh                       @18       0x0000
struct kcvfhhdr, 76 bytes                @20      
ub4 kccfhswv                          @20       0x00000000
ub4 kccfhcvn                          @24       0x0a200100
ub4 kccfhdbi                          @28       0x157f2927
text kccfhdbn[0]                      @32      M
text kccfhdbn[1]                      @33      A
text kccfhdbn[2]                      @34      C
text kccfhdbn[3]                      @35      L
text kccfhdbn[4]                      @36      E
text kccfhdbn[5]                      @37      A
text kccfhdbn[6]                      @38      N
text kccfhdbn[7]                      @39      1
ub4 kccfhcsq                          @40       0x00000675
ub4 kccfhfsz                          @44       0x00010900
s_blkz kccfhbsz                       @48       0x00
ub2 kccfhfno                          @52       0x0001
ub2 kccfhtyp                          @54       0x0003
ub4 kccfhacid                         @56       0x00000000
ub4 kccfhcks                          @60       0x00000000
text kccfhtag[0]                      @64       
text kccfhtag[1]                      @65       
text kccfhtag[2]                      @66       
text kccfhtag[3]                      @67       
text kccfhtag[4]                      @68       
text kccfhtag[5]                      @69       
text kccfhtag[6]                      @70       
text kccfhtag[7]                      @71       
text kccfhtag[8]                      @72       
text kccfhtag[9]                      @73       
text kccfhtag[10]                     @74       
text kccfhtag[11]                     @75       
text kccfhtag[12]                     @76       
text kccfhtag[13]                     @77       
text kccfhtag[14]                     @78       
text kccfhtag[15]                     @79       
text kccfhtag[16]                     @80       
text kccfhtag[17]                     @81       
text kccfhtag[18]                     @82       
text kccfhtag[19]                     @83       
text kccfhtag[20]                     @84       
text kccfhtag[21]                     @85       
text kccfhtag[22]                     @86       
text kccfhtag[23]                     @87       
text kccfhtag[24]                     @88       
text kccfhtag[25]                     @89       
text kccfhtag[26]                     @90       
text kccfhtag[27]                     @91       
text kccfhtag[28]                     @92       
text kccfhtag[29]                     @93       
text kccfhtag[30]                     @94       
text kccfhtag[31]                     @95       
ub4 kcvfhrdb                             @96       0x00400179
struct kcvfhcrs, 8 bytes                 @100     
ub4 kscnbas                           @100      0x00000008
ub2 kscnwrp                           @104      0x0000
ub4 kcvfhcrt                             @108      0x221e01bf
ub4 kcvfhrlc                             @112      0x31dd7868
struct kcvfhrls, 8 bytes                 @116     
ub4 kscnbas                           @116      0x00080634
ub2 kscnwrp                           @120      0x0000
ub4 kcvfhbti                             @124      0x00000000
struct kcvfhbsc, 8 bytes                 @128     
ub4 kscnbas                           @128      0x00000000
ub2 kscnwrp                           @132      0x0000
ub2 kcvfhbth                             @136      0x0000
ub2 kcvfhsta                             @138      0x2000 (NONE)
struct kcvfhckp, 36 bytes                @484     
struct kcvcpscn, 8 bytes              @484     
ub4 kscnbas                        @484      0x003b97f3
ub2 kscnwrp                        @488      0x0000
ub4 kcvcptim                          @492      0x342a9561
ub2 kcvcpthr                          @496      0x0001
union u, 12 bytes                     @500     
struct kcvcprba, 12 bytes          @500     
ub4 kcrbaseq                    @500      0x00000052
ub4 kcrbabno                    @504      0x00013fc0
ub2 kcrbabof                    @508      0x0010
ub1 kcvcpetb[0]                       @512      0x02
ub1 kcvcpetb[1]                       @513      0x00
ub1 kcvcpetb[2]                       @514      0x00
ub1 kcvcpetb[3]                       @515      0x00
ub1 kcvcpetb[4]                       @516      0x00
ub1 kcvcpetb[5]                       @517      0x00
ub1 kcvcpetb[6]                       @518      0x00
ub1 kcvcpetb[7]                       @519      0x00
ub4 kcvfhcpc                             @140      0x00000087
ub4 kcvfhrts                             @144      0x3427f1a8
ub4 kcvfhccc                             @148      0x00000086
struct kcvfhbcp, 36 bytes                @152     
struct kcvcpscn, 8 bytes              @152     
ub4 kscnbas                        @152      0x00000000
ub2 kscnwrp                        @156      0x0000
ub4 kcvcptim                          @160      0x00000000
ub2 kcvcpthr                          @164      0x0000
union u, 12 bytes                     @168     
struct kcvcprba, 12 bytes          @168     
ub4 kcrbaseq                    @168      0x00000000
ub4 kcrbabno                    @172      0x00000000
ub2 kcrbabof                    @176      0x0000
ub1 kcvcpetb[0]                       @180      0x00
ub1 kcvcpetb[1]                       @181      0x00
ub1 kcvcpetb[2]                       @182      0x00
ub1 kcvcpetb[3]                       @183      0x00
ub1 kcvcpetb[4]                       @184      0x00
ub1 kcvcpetb[5]                       @185      0x00
ub1 kcvcpetb[6]                       @186      0x00
ub1 kcvcpetb[7]                       @187      0x00
ub4 kcvfhbhz                             @312      0x00000000
struct kcvfhxcd, 16 bytes                @316     
ub4 space_kcvmxcd[0]                  @316      0x00000000
ub4 space_kcvmxcd[1]                  @320      0x00000000
ub4 space_kcvmxcd[2]                  @324      0x00000000
ub4 space_kcvmxcd[3]                  @328      0x00000000
word kcvfhtsn                            @332      0
ub2 kcvfhtln                             @336      0x0006
text kcvfhtnm[0]                         @338     S
text kcvfhtnm[1]                         @339     Y
text kcvfhtnm[2]                         @340     S
text kcvfhtnm[3]                         @341     T
text kcvfhtnm[4]                         @342     E
text kcvfhtnm[5]                         @343     M
text kcvfhtnm[6]                         @344      
text kcvfhtnm[7]                         @345      
text kcvfhtnm[8]                         @346      
text kcvfhtnm[9]                         @347      
text kcvfhtnm[10]                        @348      
text kcvfhtnm[11]                        @349      
text kcvfhtnm[12]                        @350      
text kcvfhtnm[13]                        @351      
text kcvfhtnm[14]                        @352      
text kcvfhtnm[15]                        @353      
text kcvfhtnm[16]                        @354      
text kcvfhtnm[17]                        @355      
text kcvfhtnm[18]                        @356      
text kcvfhtnm[19]                        @357      
text kcvfhtnm[20]                        @358      
text kcvfhtnm[21]                        @359      
text kcvfhtnm[22]                        @360      
text kcvfhtnm[23]                        @361      
text kcvfhtnm[24]                        @362      
text kcvfhtnm[25]                        @363      
text kcvfhtnm[26]                        @364      
text kcvfhtnm[27]                        @365      
text kcvfhtnm[28]                        @366      
text kcvfhtnm[29]                        @367      
ub4 kcvfhrfn                             @368      0x00000001
struct kcvfhrfs, 8 bytes                 @372     
ub4 kscnbas                           @372      0x00000000
ub2 kscnwrp                           @376      0x0000
ub4 kcvfhrft                             @380      0x00000000
struct kcvfhafs, 8 bytes                 @384     
ub4 kscnbas                           @384      0x00000000
ub2 kscnwrp                           @388      0x0000
ub4 kcvfhbbc                             @392      0x00000000
ub4 kcvfhncb                             @396      0x00000000
ub4 kcvfhmcb                             @400      0x00000000
ub4 kcvfhlcb                             @404      0x00000000
ub4 kcvfhbcs                             @408      0x00000000
ub2 kcvfhofb                             @412      0x000a
ub2 kcvfhnfb                             @414      0x000a
ub4 kcvfhprc                             @416      0x221e01a8
struct kcvfhprs, 8 bytes                 @420     
ub4 kscnbas                           @420      0x00000001
ub2 kscnwrp                           @424      0x0000
struct kcvfhprfs, 8 bytes                @428     
ub4 kscnbas                           @428      0x00000000
ub2 kscnwrp                           @432      0x0000
ub4 kcvfhtrt                             @444      0x00000000
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
struct kcvcpscn, 8 bytes                 @484     
ub4 kscnbas                           @484      0x003b97f3
ub2 kscnwrp                           @488      0x0000
ub4 kcvcptim                             @492      0x342a9561
ub2 kcvcpthr                             @496      0x0001
union u, 12 bytes                        @500     
struct kcvcprba, 12 bytes             @500     
ub4 kcrbaseq                       @500      0x00000052
ub4 kcrbabno                       @504      0x00013fc0
ub2 kcrbabof                       @508      0x0010
ub1 kcvcpetb[0]                          @512      0x02
ub1 kcvcpetb[1]                          @513      0x00
ub1 kcvcpetb[2]                          @514      0x00
ub1 kcvcpetb[3]                          @515      0x00
ub1 kcvcpetb[4]                          @516      0x00
ub1 kcvcpetb[5]                          @517      0x00
ub1 kcvcpetb[6]                          @518      0x00
ub1 kcvcpetb[7]                          @519      0x00
BBED> set offset 484
OFFSET          484
BBED> modify /x 0xF397
File: /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system (0)
Block: 1                Offsets:  484 to  995           Dba:0x00000000
------------------------------------------------------------------------
f397f4f3 00000000 61952a34 01000000 52000000 c03f0100 1000a865 02000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
0d000d00 0d000100 00000000 00000000 00000000 02004000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
<32 bytes per line>
BBED> set offset 486
OFFSET          486
BBED> modify /x 0x4B00
File: /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system (0)
Block: 1                Offsets:  486 to  997           Dba:0x00000000
------------------------------------------------------------------------
4b000000 00006195 2a340100 00005200 0000c03f 01001000 a8650200 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000d00 
0d000d00 01000000 00000000 00000000 00000200 40000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000 
<32 bytes per line>
BBED> p kcvfhckp
struct kcvfhckp, 36 bytes                   @484     
struct kcvcpscn, 8 bytes                 @484     
ub4 kscnbas                           @484      0x004b97f3
ub2 kscnwrp                           @488      0x0000
ub4 kcvcptim                             @492      0x342a9561
ub2 kcvcpthr                             @496      0x0001
union u, 12 bytes                        @500     
struct kcvcprba, 12 bytes             @500     
ub4 kcrbaseq                       @500      0x00000052
ub4 kcrbabno                       @504      0x00013fc0
ub2 kcrbabof                       @508      0x0010
ub1 kcvcpetb[0]                          @512      0x02
ub1 kcvcpetb[1]                          @513      0x00
ub1 kcvcpetb[2]                          @514      0x00
ub1 kcvcpetb[3]                          @515      0x00
ub1 kcvcpetb[4]                          @516      0x00
ub1 kcvcpetb[5]                          @517      0x00
ub1 kcvcpetb[6]                          @518      0x00
ub1 kcvcpetb[7]                          @519      0x00
BBED> sum
Check value for File 0, Block 1:
current = 0x4fc6, required = 0x4fb6
BBED> sum apply
Check value for File 0, Block 1:
current = 0x4fb6, required = 0x4fb6
BBED> verify
DBVERIFY - Verification starting
FILE = /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf
BLOCK = 1
DBVERIFY - Verification complete
Total Blocks Examined         : 1
Total Blocks Processed (Data) : 0
Total Blocks Failing   (Data) : 0
Total Blocks Processed (Index): 0
Total Blocks Failing   (Index): 0
Total Blocks Empty            : 0
Total Blocks Marked Corrupt   : 0
Total Blocks Influx           : 0
SQL> select file#,name,checkpoint_change# from v$datafile;
1 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_system_9f2flf09_.dbf                  3905523
2 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_undotbs1_9f2flf52_.dbf                3905523
3 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_sysaux_9f2flf2v_.dbf                  3905523
4 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_users_9f2flf5g_.dbf                   3905523
5 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_example_9f2fmfto_.dbf                 3905523
6 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_youchuan_9f2fqwr4_.dbf                3905523
7 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_guobao_9f2fwkkt_.dbf                  3905523
8 /s01/oracle/product/10.2.0/db_1/oradata/MACLEAN1/datafile/o1_mf_tsoa_d_bjx6j57v_.dbf                  3905523
8 rows selected.
SQL> select file#,FUZZY,CHECKPOINT_CHANGE#        from v$datafile_header;
1 NO             4954099
2 NO             3905523
3 NO             3905523
4 NO             3905523
5 NO             3905523
6 NO             3905523
7 NO             3905523
8 NO             3905523
8 rows selected.
SQL> alter database open;
Database altered.
SQL> select file#,FUZZY,CHECKPOINT_CHANGE#        from v$datafile_header;
1 YES            4954100
2 YES            4954100
3 YES            4954100
4 YES            4954100
5 YES            4954100
6 YES            4954100
7 YES            4954100
8 YES            4954100
8 rows selected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area  591396864 bytes
Fixed Size                  2022536 bytes
Variable Size             180355960 bytes
Database Buffers          402653184 bytes
Redo Buffers                6365184 bytes
Database mounted.
Database opened.

ORA-4030 PGA Usage Diagnostic Script

REM  Locate the top PGA user
set lines 75
set pages 999
set serveroutput on
spool topuser.out
select * from gv$version;
declare a1 number;
a2 number;
a3 varchar2(30);
a4 varchar2(30);
a5 number;
a6 number;
a7 number;
a8 number;
blankline varchar2(70);
cursor code is select pid, spid, substr(username,1,20) "USER" , substr(program,1,30) "Program",
PGA_USED_MEM, PGA_ALLOC_MEM, PGA_FREEABLE_MEM, PGA_MAX_MEM
from v$process where pga_alloc_mem=
(select max(pga_alloc_mem) from v$process
where program not like '%LGWR%');
begin
blankline:=chr(13);
open code;
fetch code into a1, a2, a3, a4, a5, a6, a7, a8;
dbms_output.put_line(blankline);
dbms_output.put_line('               Top PGA User');
dbms_output.put_line(blankline);
dbms_output.put_line('PID:   '||a1||'             '||'SPID:   '||a2);
dbms_output.put_line('User Info:           '||a3);
dbms_output.put_line('Program:            '||a4);
dbms_output.put_line('PGA Used:            '||a5);
dbms_output.put_line('PGA Allocated:        '||a6);
dbms_output.put_line('PGA Freeable:             '||a7);
dbms_output.put_line('Maximum PGA:            '||a8);
end;
/
set lines 132
col value format 999,999,999,999,999
select * from v$pgastat;
spool off
REM
REM  Investigate memory from the database side
REM
col TTL format 999,999,999,999 heading "Total Memory"
break on report
compute sum on report of TTL
select bytes TTL from v$sgainfo where name='Maximum SGA Size'
union
select value from v$pgastat where name='total PGA allocated'
/
set lines 132
set pages 999
spool workareaoverview.out
REM overview of PGA usage
col name format a40 head "Name"
col value format 999,999,999 head "Total"
col unit format a10 head "Units"
col pga_size format a25 head "PGA Size"
col optimal_executions format 999,999,999,999 head "Optimal"
col onepass_executions format 999,999,999,999 head "One-Pass"
col multipasses_executions format 999,999,999,999 head "Multi-Pass"
col optimal_count format 999,999,999,999 head "Optimal Count"
col optimal_perc format 999 head "Optimal|PCT"
col onepass_count format 999,999,999,999 head "One-Pass Count"
col onepass_perc format 999 head "One|PCT"
col multipass_count format 999,999,999,999 head "Multi-Pass Count"
col multipass_perc format 999 head "Multi|PCT"
col sid format 999,999 Head "SID"
col operation format a30 head "Operation"
col esize format 999,999,999 head "Expected Size"
col mem format 999,999,999 head "Actual Mem"
col "MAX MEM" format 999,999,999 head "Maximum Mem"
col pass format 999,999 head "Passes"
col tsize format 999,999,999,999,999 head "Temporary|Segment Size"
spool workareaoverview.out
SELECT  name,  decode(unit, 'bytes', trunc(value/1024/1024), value) value ,
decode(unit, 'bytes', 'MBytes', unit) unit FROM V$PGASTAT
/
REM Review workarea buckets to see how efficient memory is utilized
REM  Ideal to see OPTIMAL EXECUTIONS vs. ONE-PASS and Multi-PASS
select case when low_optimal_size < 1024*1024
then to_char(low_optimal_size/1024,'999999') || 'kb  0
order by low_optimal_size
/
REM Review workarea buckets as percentages overall
REM      this script assuming 64K optimal size
SELECT optimal_count, round(optimal_count*100/total, 2) optimal_perc,
onepass_count, round(onepass_count*100/total, 2) onepass_perc,
multipass_count, round(multipass_count*100/total, 2) multipass_perc
FROM
(SELECT decode(sum(total_executions), 0, 1, sum(total_executions)) total,
sum(OPTIMAL_EXECUTIONS) optimal_count,
sum(ONEPASS_EXECUTIONS) onepass_count,
sum(MULTIPASSES_EXECUTIONS) multipass_count
FROM   v$sql_workarea_histogram
WHERE  low_optimal_size > 64*1024)
/
REM   Review current activity in Work Areas
SELECT to_number(decode(SID, 65535, NULL, SID)) sid,
operation_type OPERATION,trunc(EXPECTED_SIZE/1024) ESIZE,
trunc(ACTUAL_MEM_USED/1024) MEM, trunc(MAX_MEM_USED/1024) "MAX MEM",
NUMBER_PASSES PASS, trunc(TEMPSEG_SIZE/1024) TSIZE
FROM V$SQL_WORKAREA_ACTIVE
ORDER BY 1,2
/
alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
select sysdate from dual;
select * from v$pgastat;
col time form a30
col name form a30
select a.BEGIN_INTERVAL_TIME time, b.*
from DBA_HIST_SNAPSHOT a, DBA_HIST_PGASTAT b
where a.SNAP_ID=b.SNAP_ID
and b.name='total PGA allocated'
order by a.BEGIN_INTERVAL_TIME desc
/
show parameter parallel_execution_message_size
show parameter memory
show parameter pga
spool off
clear col
#collect AIX info
ls -al $ORACLE_HOME/bin/oracle >> /tmp/support.txt
oslevel -s
whoami >> /tmp/support.txt
ulimit -a >> /tmp/support.txt
svmon -O unit=MB >> /tmp/support.txt
/usr/sbin/lsps -a >> /tmp/support.txt
/usr/sbin/lsattr -HE -l sys0 -a realmem >> /tmp/support.txt
ipcs -m >> /tmp/support.txt
opatch lsinventory -detail
#collect Linux info
arch
cat /etc/issue
whoami
ulimit -a
df -h /dev/shm
ipcs -ma
cat /etc/sysctl.conf
cat /proc/meminfo
cat /proc/swaps
cat /proc/vmstat
opatch lsinventory -detail

Oracle内部错误ORA-600:[1112]

以下为ORA-600[1112]内部错误的相关诊断信息:

ERROR:
ORA-600 [1112] [a] [b] [c] [d] [e]
VERSIONS:
versions 7.3 to 9.2
DESCRIPTION:
ORA-600 [1112] is getting raised while trying to add a
row cache enqueue to a transaction state object during
lookup of the default tablespace number during table
creation.
FUNCTIONALITY:
STATE OBJECT MANAGEMENT
IMPACT:
PROCESS FAILURE
NON CORRUPTIVE - No underlying data corruption.
Bug 2489130 - OERI:1112 can occur while dumping PROCESSSTATE informatio (Doc ID 2489130.8)
Bug 4126973: ORA-600[504] AND ORA-600[1112] OCCURED WHEN GETTING "ERRORSTACK"
Base Bug 2489130
Bug 3954753: ORA-600 [1112] AND SESSION CRASH
The cause for the ORA-00600 [1112] appears due to Bug 2489130
This error can occur on dumping of process state which is what occurred here.
The primary issue is the WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK!
This then triggers a system state and process state to be dumped due to nature of the problem.
The ORA-00600 [1112] gets dumped out when process state is done.
Stack for trace very similar to Bug 2489130 and this is only known bug on 9.2 like this with a fix.
A fix for bug 2489130 is included in the 9.2.0.7 patchset.
Recommend applying 9.2.0.8 patchset to have this and other bug fixes.
This would only prevent the ORA-00600 [1112] from occurring on state dumps.
The primary row cache issue still to be investigated by performance team.

How many LMS processes for Oracle Rac 9i?

Question:

How many LMS processes for Oracle 9i Rac?

Can you check if you have increased the CPU in the machine during the upgrade.
Also please let me know if you have changed any underscore parameter.

Do you have some formula for calculate LMS processes and Num of CPU ?? Let say 2 CPU for 1 LMS process ??

 

Answer:

This can go dynamically as and when required and the # of startup of lms is also controlled by
_lm_lms,_lm_max_lms _lm_min_lms , later 2 seems to be for dynamic control of this numbers.
These parameters should not be set manually.
Also the no. of lms process should be one less than the number of CPU on the node.

沪ICP备14014813号

沪公网安备 31010802001379号