Maclean’s Oracle Database Tech Blog Archives

从今天起正式学习perl,立帖为证。。。 启蒙教育是小骆驼书，perl的上下文好搞哦!

我们知道hostid作为一台主机的唯一标示符(hostname本身可能重复),而许多付费软件通过鉴别hostid发给相关的license. hostname的修改较为简单，只需要修改/etc/sysconfig/network中的hostname并重启即可。 hostid的修改就不那么方便了，下面介绍一种方法： 编辑一个c文件，是的之后你还需要修改它,就叫做host.c吧！ #include <stdio.h> #include <unistd.h> int main() { long id,res; // get real (default) hostid id = gethostid(); printf(“current hostid is: %x\n”,id); // set new hostid if is superuser res = sethostid(0xa090d01); //括号内填入你想要的hostid if (res == 0) printf(“if result is zero – success! (%d) \n”,res); // check if it is changed…. id…

在版本10.2.0.4未打上相关one-off补丁的情况下，分别对ASSM和MSSM管理模式表空间进行索引分裂测试，经过测试的结论如下： l  在10gr2版本中MSSM方式是不能避免索引分裂引起交易超时问题； l  10.2.0.4上的one-off补丁因为目前仅存在Linux版本，可以考虑声请补丁后具体测试（因目前没有补丁所以处于未知状态）。 l  合并索引是目前最具可行性的解决方案(alter index coalesce)。 l  最新的11gr2中经测试仍存在该问题。 具体测试过程如下： 1．    自动段管理模式下的索引块分裂 SQL> drop tablespace idx1 including contents and datafiles; Tablespace dropped. SQL> create tablespace idx1 datafile ‘?/dbs/idx1.dbf’ size 500M 2  segment space management AUTO 3  extent management local uniform size 10M; —创建自动段管理的表空间 Tablespace created. SQL> create table idx1(a number) tablespace idx1;…

Oracle 10g 中引入了v$osstat 视图方便了dba了解主机负载情况，同时也可以通过oem网页观察到一段时间内主机上负载较高的进程；但如果db未开启oem管理界面，则无法了解过去时段内高负载服务进程的相关信息。以下脚本可以给予一定的帮助。 CREATE TABLE “SYS”.”HIGHLOAD_HISTORY” ( “SAMPLE_TIME” DATE, “SPID” NUMBER(10,0), “LOAD” VARCHAR2(7 BYTE), “SID” VARCHAR2(30 BYTE), “USERNAME” VARCHAR2(40 BYTE), “MACHINE” VARCHAR2(64 BYTE), “PROGRAM” VARCHAR2(48 BYTE), “SQL_ID” VARCHAR2(13 BYTE), “SQL_FULLTEXT” CLOB, “INST_ID” NUMBER(2,0), “STATUS” VARCHAR2(8 BYTE) ) –建立记录高负载进程信息的表，内容包括了cpu使用率，及sql(并不十分准确,因为获取spid后需要进行查询) ps aux|grep $ORACLE_SID|awk ‘{ if($3>=0.3) print “insert into highload_history select sysdate rec_time,”$2,”,\47″$3″%\47″,”, ss.sid,ss.username,ss.machine,ss.program,ss.sql_id,(select sql_fulltext from v$sqlarea…

set parameter _in_memory_undo = FALSE to disable IMU Workaround: Disable IMU (set _in_memory_undo=FALSE) PLEASE NOTE: This bug applies to single instance databases and not RAC as IMU is not enabled in RAC. 注意在RAC系统中IMU是不可用的，所以也就不必要去设置_in_memory_undo=FALSE The workaround will prevent the problem, but will not fix it. Note: _in_memory_undo is a dynamic parameter for 10g with values of…

The NOLOGGING clause doesn’t prevent redo on all operations, but rather only on a subset. I searched the documentation for examples of this… http://st-doc.us.oracle.com/11/112/server.112/e16541/parallel007.htm?term=nologging+generate+redo#VLDBG1536 [NO]LOGGING Clause The [NO]LOGGING clause applies to tables, partitions, tablespaces, and indexes. Virtually no log is generated for certain operations (such as direct-path INSERT) if the NOLOGGING clause is used. The…

Question: 在RAC环境中节点之间的OS操作系统时钟一致是clusterware能够稳定运行的重要因素之一，但是如果我们确实有调整OS时间的需求，那么是否真的会影响到RAC的正常运行呢？ 具体的影响是如何的呢？  又需要注意哪些方面的因素呢？ Answer: RAC: Frequently Asked Questions (Doc ID 220970.1) Does Oracle RAC work with NTP (Network Time Protocol)? YES! NTP and Oracle RAC are compatible, as a matter of fact, it is recommended to setup NTP in an Oracle RAC cluster, for Oracle 9i Database, Oracle Database 10g, and Oracle Database 11g…

Can you kindly define and explain of  lock_sga  parameter?each flatform(HP, IBM, SUN) recommended value of both parameter and why it is recommended like that value.   LOCK_SGA locks the entire SGA into physical memory. It is usually advisable to lock the SGA into real (physical) memory, especially if the use of virtual memory would include…

Can you kindly define and explain of this parameter? _os_sched_high_priority   Regarding _os_sched_high_priority : Setting LMS priority automatically via _os_sched_high_priority It has been proven that performance is increased when LMS processes are running in the real time priority class, as opposed to the timesharing class. The parameter _os_sched_high_priority was introduced in Oracle 10g Release 2,…

v$lock是常用的enqueue lock队列锁动态性能视图，不管是用户自己部署的监控脚本也好、还是enterprise manager都多少会使用到该V$LOCK视图， 但是在10g中遇到了v$lock查询缓慢的问题， 例如下面的查询会等待较多direct path write temp等待事件：     select count(*) from v$lock; COUNT(*) ———- 163 Elapsed: 00:00:60.90 Execution Plan ———————————————————- Plan hash value: 2384831130 ————————————————————————————– | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ————————————————————————————– | 0 | SELECT STATEMENT | | 1 | 50 | 1…