Maclean’s Oracle Database Tech Blog Archives

以下为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…

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…

set timing on; alter session set workarea_size_policy=MANUAL; alter session set workarea_size_policy=MANUAL; alter session set db_file_multiblock_read_count=512; alter session set db_file_multiblock_read_count=512; alter session set events ‘10351 trace name context forever, level 128’; alter session set hash_area_size=524288000; alter session set hash_area_size=524288000; alter session set “_hash_multiblock_io_count”=128; alter session set “_hash_multiblock_io_count”=128; alter session enable parallel query; select /*+ pq_distribute(a hash,hash)…

Question: What is between between “unique index vs non-unique index”. Which one is faster. The customer using non-unique and sql is getting delay If we change tp non-unique. Is it work ? Answer: Indexes can be unique or non-unique. Unique indexes guarantee that no two rows of a table have duplicate values in the key…

OPROCD introduced in 10.2.0.4 Linux and other Unix platform. Fencing Cluster handling of nodes that should not have access to shared resources STONITH – Power cycle the node PCW – nodes fence themselves through the reboot(8) command Fabric Fencing from Polyserve Healthy nodes send SNMP msgs to Fabric switch to disable SAN access from unhealthy…

Active Session History (ASH) performed an emergency flush. This may mean that ASH is undersized. If emergency flushes are a recurring issue, you may consider increasing ASH size by setting the value of _ASH_SIZE to a sufficiently large value. Currently, ASH size is 4194304 bytes. Both ASH size and the total number of emergency flushes…

Single resource deadlock: blocking enqueue which blocks itself, f 0 Single resource deadlock: blocking enqueue which blocks itself, f 0 Granted global enqueue 0xd8578490 ———-enqueue 0xd8578490———————— lock version : 1 Owner inst : 2 grant_level : KJUSERCW req_level : KJUSERPW bast_level : KJUSERNL notify_func : 0x4fe4b6e resp : 0xd9c7ad50 procp : 0xd9971780 pid : 0…

在分布式事务环境中数据库的SCN可能瞬间暴增，这种行为被称作SCN jump in distributed transaction，注意这种现象是正常的。在发生分布式事务的2个数据库中，SCN较低的DB会将SCN和较高的那个数据库同步，这有时候会造成我们的一些误解认为可能是Hot backup等操作引起了SCN的猛增。 我们来具体看一下这种现象: SQL> select * from v$version; BANNER ——————————————————————————– Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production PL/SQL Release 11.2.0.1.0 – Production CORE 11.2.0.1.0 Production TNS for 32-bit Windows: Version 11.2.0.1.0 – Production NLSRTL Version 11.2.0.1.0 – Production SQL> select * from global_name; GLOBAL_NAME ——————————————————————————– www.askmac.cn SQL> select current_scn…

1. The Total size of the log buffer is determined by LOG_BUFFER parameter. 2. Only Server process may pin a data block in exclusive mode. 3. LGWR writes to the redo log files when: The redo log buffer is 1/3 full. 1 MB of data have been written to the redo log buffer. A 3-second…

通过下图定义的规则，可确定分区索引类型: Hash Global分区索引介绍 HASH-Partitioned Global索引是Oracle 10g开始提供的新特性。而在以前的版本中 ，Oracle只支持Range-Partitioned Global索引。HASH-Partitioned Global索引的好处如下： 比Range-Partitioned Global索引易于实施。HASH-Partitioned Global索引是根据 索引字段值，通过Oracle内部的HASH算法自动均匀散列到定义的分区中。而 Range-Partitioned Global索引需要根据索引字段值的范围进行分区，因此实施和 维护的难度都大。 HASH-Partitioned Global索引适合于在并发量、吞吐量很大的交易系统（OLTP） 中，对某些字段的访问冲突。尤其是sequence字段值。 HASH-Partitioned Global索引适合于大批量的数据查询。HASH-Partitioned Global索引不仅可以提供分区之间的并行查询， 而且在分区内也可进行并行查询的处理。 建立分区索引必须指定表空间，并且指定的表空间要与数据表空间分开， 这样便于管理，同时尽可能分开索引和数据的IO访问，提高效率