weejar zhang

oracle ACE, A DBA, blogger.

Troubleshooting Oracle 19c RAC db crash with ORA-00600 [kcbbxsv_nwp]

by

ORA-00600: internal error code, arguments: [kcbbxsv_nwp], [], [], [], [], [], [], [], [], [], [], []
kge_experr : Found error ORA-600 not in expected list.
kge_experr: Dumping error frames [top = 1 : barrier top = 0]
kge_experr : [0] : Error = ORA-600 :
Call stack = ksedsts()+426<-kge_snap_callstack()+77<-kgeadse()+557<-kgerinv_internal()+44 <-kgerinv()+40<-kserin()+180<-kcbbxsv()+17478<-kcbb_coalesce_int()+326<-kcbb_coalesce()+438< -kcbbwthc()+817<-kcbbdrv()+8765<-ksb_act_run_int()+117<-ksb_act_run()+130<-ksbcti()+18

升级Oracle 19c经验: TTS/XTTS时ORA-39083和ORA-00942案例

by

上周在使用TTS传输表空间从11.2.0.4到19C， 在最后impdp metadata的环节提示ora-942 结果提示有大量索引没有创建成功，但是查看报错的表实际是存在的， 后来发现这是一个收权问题导致的。后分析这种场景是发生在如一开始给了一个用户如create ANY table/index的系统权限或者dba 角色或是on object上的权限，后来创建了跨SCHEMA的index（indexes and tables differenct owners）或FK 约束时，再后来安全整改收回大权限，就导致了这个问题。

How to disable DRM? Oracle 10G, 11G, 12C-19C

by

DRM is driven by the following
1.) _gc_affinity_time = Time in minutes at which statistics will be evaluated (default = 10 mins)
2.) _gc_affinity_limit = # of times a node accesses a file/object (default = 50)
3.) _gc_affinity_minimum = minimum # of times per minute a file/object is accessed before affinity kicks in
(default = 600 per minute per cpu )
4.) _lm_drm_disable = LEVEL disable drm in different level( default=0 )

Oracle Internal BLOCK CHANGE TRACKING（BCT）

by

Oracle RMAN was able to take incremental backups already in 9i. However, prior to introduction of Oracle 10g block change tracking (BCT), RMAN had to scan the whole datafile to and filter out the blocks that were not changed since base incremental backup and overhead or incremental backup was as high as full backup.

Oracle数据库中的timeout超时及12c以后 Automatic terminal/kill session feature& DCD

by

数据库会话同样会占用数据库资源，如客户端异常断开在客户端成为一个dead session会永远存在，如果客户端没有断开也没有活动就是一个idle session， 如果这个idle session做了一些修改未提交，然后下班或去吃饭、上WC、开会等，这时就会堵塞其他人对相同的数据做修改，这类会话可以叫做idle blocker session. 在自治数据库中这些session 都可以被释放或者kill / teminal 终结掉，下面对不同的session如何被释放

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

by

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)

by

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

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

by

执行计划中的谓词信息非常的重要，有助于我们判断是否进行了隐式转换，为什么没有使用索引等， 使用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 (不存在列名)

by

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

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

by

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)