ORACLE 9i-23ai – Page 39

Adaptive Log File sync was introduced in 11.2. the feature is exactly enabled since release 11.2.0.3 , It’s enabled through an underscore parameter called _use_adaptive_log_file_sync and the description of this parameter is: adaptively switch between post/wait and polling. Oracle can switches between the 2 methods: Post/wait, traditional method for posting completion of writes to redo … Read more

How to enable SQL*Net Tracing with Oracle Instant Client 11G

2013-12-032013-12-03 by weejar zhang

Sometimes we need to enable sql*net trace to diagnose sqlplus connection problems such as ora-125*
Below are the steps to configure Sql*Net verbose Client side tracing:

How to get call stack of oracle processes ?

2013-11-23 by weejar zhang

Each time your program performs a function call, information about the call is generated. That information includes the location of the call in your program, the arguments of the call, and the local variables ..

Script: Display the sessions distribution and top 50 idle sessions

2013-11-22 by weejar zhang

This script displays the top 50 longest idle times and list distribution of sessions on the instance..

I prefer the version 11.2.0.3 parse_calls (我比较喜欢parse_calls在11203版本上的显示)

2020-07-042013-11-22 by weejar zhang

高parse calls表示这些SQL不可以re-used 并且每次执行都要重新解析，理想的最佳状态是一次解析多次执行，你也知道频繁的解析会给library cache带来沉重的负担，一个sql的执行必须先parse，解析过程包含sql 加载到shared pool, 语法检查…

Logon Storms on 11g ,audit maybe cause performance issue.(当创建很多连接时审计可能带来性能问题)

2013-11-14 by weejar zhang

When multiple connection created will insert a lot of rows in aud$ table, there may be contention in the segment header for the table. This contention can manifest itself as a buffer busy wait or a freelist wait.

Unable to change MEMORY_MAX_TARGET (无法修改MEMORY_MAX_TARGET)

2013-10-312013-10-31 by weejar zhang

alter system set memory_max_target=0 scope=spfile;
shutdown immediate
startup
SQL> show parameter target
…
memory_max_target big integer 3824M

Logon Storm and Memory shortage issue ORA-07445 dbgtrReserveSpace (记录一次登录风暴致内存不足的问题)

2013-10-312013-10-21 by weejar zhang

Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x10] [PC:0x938E8C5, dbgtrReserveSpace()+99] [flags: 0x0, count: 1]
Errors in file /oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_13864.trc (incident=60801):
ORA-07445: exception encountered: core dump [dbgtrReserveSpace()+99] [SIGSEGV] [ADDR:0x10] [PC:0x938E8C5] [Address not mapped to object] []
Incident details in: /oracle/diag/rdbms/ora11g/ora11g/incident/incdir_60801/ora11g_ora_13864_i60801.trc
Use ADRCI or Support Workbench to package the incident.

scn format (scn格式)

2013-10-312013-10-16 by weejar zhang

scn(system change number) 结构主要维护 oracle数据库内部的数据一致性，SCN 有两部分组成: Base and wrap，wrap 是16bit的数字，base是32bit的数字,这样其实就可以算scn的有效范围，它的格式(redo dump trace)是wrap.base，当base超过了2的32次方,然后wrap 就会加1，其实用sql 很好验证。

SQL Test Case Builder generate test case automatically(自动生成SQL所需测试数据)

2013-10-15 by weejar zhang

Why SQL Test Case Builder? For most SQL problems, the single most important factor for a speedy bug resolution is to obtain a reproducible test case. However, this is normally the longest and most painful step for customers. The goal of the SQL Test Case Builder (TCB) is to automatically gather as much information as … Read more

ORA-00313,ORA-00312,ORA-27037,ORA-19527 DATAGARD 11G r2

2013-10-12 by weejar zhang

Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
DDE: Problem Key ‘ORA 312’ was flood controlled (0x5) (no incident)
ORA-00312: online log 2 thread 1: ‘/oracle/oradata/icme/redo02.log’
Error 19527 creating/clearing online redo logfile 2

Send email using utl_smtp in Oracle 11g (results in ORA-24247)(11g ACL 网络访问控制列表)

2013-09-29 by weejar zhang

ORA-24247: network access denied by access control list (ACL)
ORA-06512: at “SYS.UTL_TCP”, line 17
ORA-06512: at “SYS.UTL_TCP”, line 267

Oradebug Security

2014-11-042013-09-25 by weejar zhang

DEMO 1,How to use oradebug to call OS commands via the database
DEMO 2,Disable sys audit

How to imp job to another schema in same db(同一数据库导入JOB到另外一用户)

2013-09-25 by weejar zhang

ORA-39083: Object type JOB failed to create with error:
ORA-00001: unique constraint (SYS.I_JOB_JOB) violated

Dynamically Changing shared_pool_size fail!(动态修改Shared pool size失败)

2013-09-10 by weejar zhang

因安装数据库时启用了ASMM,过了几天发现分配不太理想想禁用ASMM，手动调整shared_pool_size, 这是一个动态参数，修改后立即生效无需重启数据库实例，但修改时遇到了比较奇怪的现象。

ORA-07445 [kggchk()+77] [SIGSEGV] [Address not mapped to object]

2013-08-15 by weejar zhang

今天有个项目系统负载突然变大，查到top sql是一个存储过程,执行计划缺少合理的索引，SQL是个update操作，于是kill 调用的几个session 建立一个索引，结果发现kill几个用户进程后实例挂了.下面是alert日志，可见instance crash前出现过死锁,就是那个update sql Thu Aug 15 11:08:04 2013 ORA-00060: Deadlock detected. More info in file /oracle/admin/icme/udump/icme_ora_24011.trc. Thu Aug 15 11:27:00 2013 ORA-00060: Deadlock detected. More info in file /oracle/admin/icme/udump/icme_ora_26527.trc. Thu Aug 15 11:27:03 2013 ORA-00060: Deadlock detected. More info in file /oracle/admin/icme/udump/icme_ora_26912.trc. Thu Aug 15 11:32:46 2013 ORA-00060: Deadlock detected. More info … Read more