Troubleshooting ORA-00600: internal error code [6006] corruption block

Oracle is undoing an index leaf key operation. If the key is not found, ORA-00600 [6006] is logged.
ORA-600[6006] is usually caused by a media corruption problem related to either a lost write to disk or a corruption on disk.

BBED simulates and fixes ORA-08102 error (Oracle 19c) (二)

方法一使用了bbed修改 index key的方法, 因为表列上只有这一个索引,所以只改一个索引就可以。这里还使用相同的方法模拟ora-8102,使用第二种方法,删除bootstrap$中的index I_OBJ4 记录解决。

,

BBED simulates and fixes ORA-08102 error (Oracle 19c)

Sometimes due to sudden power failure and other reasons, the database data dictionary is inconsistent, such as hits ora-8102, the indexes key and the table key value does not match, often delete the index, rebuild the index can be resolved, but if the object_id <60 bootstrap$ internal The index is damaged, and the normal situation needs to be backed up and restored, because some of the indexes in these bootstrap $ cannot be rebuilt by setting event 38003

,

Oracle数据库许可(License)

Oracle产品可从Oracle网站免费下载。但是,在使用它之前,您需要同意Oracle Technology Network(OTN)开发人员许可条款。最近几年虚拟化、云化环境的普及,Oracle的license许可有些疑惑,在这里特意整理一下。Oracle文档中关于此主题的信息确实很少,稍不注意可能会带了百万级的费用消耗, 前提是你有严格对待ORACLE的软件许可,当然还是以Oracle合同中指定条款为主。

DB Time 去哪了? Oracle 12C AWR 增加了on cpu runqueue

是否遇到过在分析AWR报告时,明明AAS很高,但从Top 10 Foreground Events by Total Wait Time 上看top event使用的百分比加起来离100%很远? 那DB TIME去哪了?下面我附一个11G(11.2.0.4 RAC on AIX) AWR 案例,这个问题在12c的AWR中提供了TOP EVENT。

,

Oracle 20c新特性: dbms_xplan.display_awr 增加了谓词信息

执行计划中的谓词信息非常的重要,有助于我们判断是否进行了隐式转换,为什么没有使用索引等, 使用dbms_xplan.display_cursor可以从shared_pool中取到sq cursor的谓词,但是在20c之前dbms_xplan.display_AWR 在之前的版本中并不能, 但是Oracle社区的投票和诸多人的推动下,终于在oracle 20c所谓词信息也在dbms_xplan.display_AWR中显示

,

Alert: Remember to specify the table name alias for column of the subquery (不存在列名)

前几日同事那遇到的灾难性的SQL, 一个简单的子查询,不知是一开始就写错了子查询中的列名,还是子查询的表后期有改列名,导致主查询表所有数据被更改,在未指定列所属表名时,列的查询顺序先检查子查询再检查主查询,这是oracle的预期行为,并不会报列不存在,只是在后期书写时子查询记的增加列上的所属别名。

Wait Event: Wait for Table Lock

Wait for Table Lock
Meaning:
Wait for exclusive TM enqueue in materialized view operations.

Troubleshooting ORA-00600 [ORA_NPI_ERROR] ORA-00600: internal error code [kffilCreate01]

Oracle 11.2.0.3 2nodes RAC on AIX, node1 DB instance crash and db alert log show “ORA-00600 [ORA_NPI_ERROR] ORA-00600: internal error code [kffilCreate01]” and “ORA-15064: communication failure with ASM instance” then Instance terminated by ASMB.

Enable DDL logging in Oracle databaase (安全审计)

Oracle database use DDL statements to define structures such as tables to store data and functions to store code. By default Oracle database does not log any DDL operations performed by any user.When someone do some destructive DDL in DB, we often need the DDL log..In this article I will only record the method of using Oracle Database Lifecycle Management Pack( Enable_ddl_logging)

Oracle 12c Alert log show ” ADVISORY: Please collect redo for investigation of ORA-8103″ frequently

叕一个oracle 12c频繁生成日志文件的问题,最近一套12.2的RAC突然又文件系统告警,生成了大量的trace文件,db alert log也在不停的显示如下内容:
ADVISORY: Please collect redo for investigation of ORA-8103. Use command:
ALTER SYSTEM DUMP REDO scn min 1 scn max 16716635042430 dba min 32 2094058 dba max 32 2094058;

Troubleshooting ORA-01578 ORA-01110 ORA-26040 NOLOGGING corrupted block

The following errors have recently occurred in our database( Oracle 19c RAC):
ORA-01578: ORACLE data block corrupted (file # xxx, block # yyyyy)
ORA-01110: data file xxx: ‘xxxxxxxxxxxxxxxxx.dbf’
ORA-26040: Data block was loaded using the NOLOGGING option

, , , ,

Oracle 哪些进程可以KILL不会导致实例重启?

oracle后台进程当出现问题时,有些进程kill会导致实例立即重启,像smon, pmon,ckpt.. , 而有些进程kill并不会影响实例可用性, 甚至会立即做进程级重启从db alert log可以观测到,如mmon,rec,jnnn, pnnn等, 前两天看到Poder在其BLOG分享v$process的基表X$KSUPR中中有记录哪些是oracle的致命进程,在X$KSUPR.KSUPRFLG第3位, 下面我做个测试, kill 点X$KSUPR.KSUPRFLG第3位都不为1的进程。

,

Troubleshooting failed to generate AWR snapshots at WRH$_SERVICE_STAT(gv$service_stats many ‘–UNKNOWN–‘)

此数据库存在大量的expdp JOB, 查看库的AWR时发现已经很久没有生成snapshot, 分析后发现在收集gv$service_stats(X$KEWSSVCV) 超时, X$KEWSSVCV 有百万行”–UNKNOWN–” service name的统计信息,虽然这个问题是因为datapump 的”BUG”((non-existent on MOS) )导致,但这算是一个数据库长期不重启带来的“不好的影响”。

,

Troubleshooting Expdp fail with ORA-31638 ,ORA-39077, ORA-06502

这是一个经常执行expdp做表级备份的数据库,版本Oracle 11.2.0.4 2nodes RAC,在12.2之前存在一个bug就是expdp调用的内部sequence在达到6位数后而导致的失败ORA-31638 \ ORA-39077\ORA-06502 ,bug修复后是增加了当此sequence达到6位数后drop并自动re-create。 下面记录一下这个问题。下一篇我会分享这个库同样expdp导致的另一个问题。

, ,

Wait Event: L1 validation

The ‘L1 validation’ looks like a segment space management issue. Although it’s not documented (yet) v$event_name suggests that the p1 and p2 parameter are the “segheader” and “l1bmb”. L1 BMB – stands for L1 bitmap block.

Oracle多租户环境,如何直接登录到PDB, 不指定TNS? 18c、 19c、20c

Oracle 20C non-CDB将不再支持,多租户架构(multitenant architecture )已深入Oracle内核, 没有理由再拒绝CDB,有时计划核心业务库上在CDB中只有一个PDB, DBA在主机本地登录数据库,感觉每次都要sqlplus 直接登录cdb$root后,还要再alter session set container=xx切换到PDB,或者使用pdb的service_name,很麻烦,那有没有办法可以像非多租一样直接登录到PDB呢? 有!

,

Oracle 18c新特性:PDB Snapshot Carousel (PDB快照 旋转木马)

在一些生产容灾环境中有一种定期clone一份数据库的需求,如BCV存储快照技术 ,当然最近几年CDP, CDM技术的出现也是基于存储快照实现数据库历史时间的恢复。Oracle 在18c中同样提供了一种自动生成并清理历史数据库快照的技术PDB Snapshot Carousel…

,

Troubleshooting query v$asm_disk v$asm_diskgroup hang

An Oracle 11.2.0.4.7 4-nodes RAC on linux env, the fourth instance has this problem just, When the monitoring tool queries the usage of ASM DISKGroup, the query of V$asm_disk and v$asm_diskgroup has not been completed. from ASM INSTANCE found that waiting for ‘enq: DD – contention’ and final blocker session is waiting for ‘GPnP Get Item’ event. but query v$asm_diskgroup_stat work fine.

, ,

Troubleshooting ORA-01640: can not make tablespace read-only

近期客户环境中有友商在测试XTTS时遇到了在把表空间修改为read only只读时遇到的ora-1640错误,说明与此表空间相关存在事务未结束,最终确认最在一个PREPARED状态的分布式事务…
ORA-01640: cannot make tablespace read-only with changes by in-doubt transactions

,