山上有风

客户的应用程序在插入记录时遇到ora-03106错误。alert文件没有错误信息。

1. 查oracle错误信息
Cause: The communication path between Oracle and the user task has stopped. This is an internal error message not usually issued.
Action: Contact Oracle Support Services.
2. 要alert文件
没有相应错误记录
3. 查google

Troubleshooting
===============
Two-task common errors are generally RDBMS related issues, but could be caused by a problem with SQL*Net, or an application (i.e. Pro*C).

RDBMS - Troubleshooting two-task common errors
==============================================
1. Check for NLS incompatibilities between the client and server. This is a very popular cause of the ORA-03106 error.
2. Check the alert log and RDBMS trace files for any pertinent information / errors that coincide with receiving the ORA-03106 error.
3. Try to get a testcase and reproduce the problem in-house.
4. If a testcase is not possible, ask for SQL*Net trace files, SQL traces, and try setting event 3106 to obtain the error stack.
5. In extreme circumstances, this could indicate that a shared memory segment used by Oracle is corrupted. You will have to shut down the database, possibly using the abort option. Make sure all semaphores have been released by using the IPCS command on Unix. Oracle uses semaphores to control concurrency between all of the background processes (pmon, smon, drwr, lgwr, and oracle shadow processes). Semaphores are also used to control Two-Task communication between the user process and the shadow process.
6. Try and narrow down the code that may be causing it. For example it might have started to happen after increasing the number of parameters in a PL/SQL block or when using certain values for bind variables, etc.
7. If we still do not make headway, file a bug under RDBMS, but we still may need some or all of the above information as indicated above

4. 让客户查客户端的NLS_LANG(服务器是GBK)
客户端zhs16cgb231280.
让客户端把客户端的修改维gbk测试。不再报错。

5. 其它

可能的进一步思路。
可以考虑客户端重启， 服务器上监控到新的oracle进程，然后trace 该进程，获得其执行的sql.
到服务器上用sqlplus连接执行测试。

--------

http://edelivery.oracle.com/EPD/Search/get_form

来自chinaunix：
Oracle Unbreakable Linux最终发布了，看来正是万众瞩目。可以从Oracle公司的官方主页上看到该项目的相关说明，该发行版就是来源于redhat企业级Linux，把其中的Red Hat的商标移除掉，然后加上一些bug的修补。虽然Oracle公司的Linux发行版计划被称为是一项支持项目，支持Oracle的Linux平台用户，而不是为了发行Linux发行版。因为来源于RHEL，所以该发行版完全兼容RHEL。目前该版本提供免费下载，另外假如有公司需要额外的支持服务，可以另外选择付费服务，由Oracle提供。

Oracle声明：
Oracle Press Release

Contact(s):

Letty Ledbetter
Oracle
+1.650.506.8071
letty.ledbetter@oracle.com

Bob Wynne
Oracle
+1.650.506.5834
bob.wynne@oracle.com
Oracle Announces The Same Enterprise Class Support For Linux As For Its Database
Dell, Intel, HP, IBM, Accenture, AMD, BP, EMC, BMC, and NetApp Join Unbreakable Linux Program
REDWOOD SHORES, Calif., 25-OCT-2006 Today Oracle announced that it would provide the same enterprise class support for Linux as it provides for its database, middleware and applications products. Oracle starts with Red Hat Linux, removes Red Hat trademarks, and then adds Linux bug fixes.

Currently, Red Hat only provides bug fixes for the latest version of its software. This often requires customers to upgrade to a new version of Linux software to get a bug fixed. Oracle's new Unbreakable Linux program will provide bug fixes to future, current, and back releases of Linux. In other words, Oracle will provide the same level of enterprise support for Linux as is available for other operating systems.

Oracle is offering its Unbreakable Linux program for substantially less than Red Hat currently charges for its best support. "We believe that better support and lower support prices will speed the adoption of Linux, and we are working closely with our partners to make that happen," said Oracle CEO Larry Ellison. "Intel is a development partner. Dell and HP are resellers and support partners. Many others are signed up to help us move Linux up to mission critical status in the data center."

"Oracle's Unbreakable Linux program is available to all Linux users for as low as $99 per system per year," said Oracle President Charles Phillips. "You do not have to be a user of Oracle software to qualify. This is all about broadening the success of Linux. To get Oracle support for Red Hat Linux all you have to do is point your Red Hat server to the Oracle network. The switch takes less than a minute."

"We think it's important not to fragment the market," said Oracle's Chief Corporate Architect Edward Screven. "We will maintain compatibility with Red Hat Linux. Every time Red Hat distributes a new version we will resynchronize with their code. All we add are bug fixes, which are immediately available to Red Hat and the rest of the community. We have years of Linux engineering experience. Several Oracle employees are Linux mainline maintainers."

DELL
"As a customer with first hand experience of Oracle's outstanding support organization, Dell will use Oracle to support Linux operating systems internally," said Michael Dell, Chairman of the Board, Dell. "Oracle's new Linux support program will help us drive standards deeper into the enterprise. Today we're announcing that Dell customers can choose Oracle's Unbreakable Linux program to support Linux environments running on Dell PowerEdge servers."

Intel
"Having worked with Oracle for many years in the enterprise computing space, we believe that the Oracle Unbreakable Linux program will bring tremendous value to our mutual Linux customers," said Paul Otellini, President and CEO, Intel Corporation. "Our work with Oracle on this program will be an important extension to our longstanding enterprise computing relationship."

HP
"HP and Oracle's collaboration and testing of Linux with integrated stacks of hardware, software, storage, and networking has helped create numerous best practices across the industry. HP welcomes the addition of Oracle's Unbreakable Linux program to the portfolio," said Mark Hurd, Chairman and Chief Executive Officer, HP.

IBM
"Oracle's support for Red Hat Linux will encourage broader adoption of Linux in the enterprise," said Bill Zeitler, Senior Vice President & Group Executive, IBM Systems and Technology Group. "IBM shares Oracle's goal of making Linux a reliable, highly standard, cost effective platform for mission critical applications backed by world class support."

Accenture
"Linux is important to us, and to our customers," said Don Rippert, Chief Technology Officer, Accenture. "We applaud Oracle's efforts to bring enterprise-quality support to Linux with the Oracle Unbreakable Linux program announcement. Together with Oracle, we at Accenture look forward to making the Linux experience even better for our customers."

AMD
"Oracle's Unbreakable Linux program will greatly expand the servicing options available to our AMD Linux customers," said Hector Ruiz, Chairman and Chief Executive Officer of Advanced Micro Devices. "We are excited by the program's potential to further enhance the success of AMD Linux servers in the enterprise."

Bearing Point
"It is critical that our customers have true enterprise-quality support for their Linux deployments. Oracle's Unbreakable Linux program support delivers the level of confidence our customers need to run Linux in their data centers," said Harry You, CEO, Bearing Point.

EMC
"The combined power of EMC and Oracle solutions bring superior reliability, scalability, high availability, and now, enhanced enterprise supportability to Linux users. We are confident that joint Linux solutions from EMC and Oracle will deliver enterprise scale and quality while lowering the cost of infrastructure for our customers," said Joe Tucci, Chairman, CEO, President, EMC.

BMC
"As Oracle's only systems management ISV at the highest level in Oracle's Partner Program, BMC Software is excited to see Oracle's deepening commitment to Linux," said Bob Beauchamp, BMC Software President and CEO. "Business Service Management from BMC Software with the Oracle Unbreakable Linux program meets customer demand for lower cost and higher quality support for their infrastructure."

NetApp
"The world's largest enterprises must have the flexibility to quickly and continually adapt to today's rapidly changing business requirements, without incurring risk," said Dan Warmenhoven, CEO of Network Appliance. "The Oracle Unbreakable Linux program is designed to drive the key benefits of Linux - including flexibility, reliability, and simplicity - directly into the data center. The longstanding relationship between NetApp and Oracle has enabled us to continuously deliver superior enterprise solutions to enable business agility and improve reliability - all tenets of the NetApp brand."

Oracle Support
Oracle's breadth and depth of technical expertise, advanced support technologies, and global reach includes 7,000 support staff in 17 global support centers, providing help to our customers in 27 languages, in any time zone. Oracle has recently been awarded the J.D. Power and Associates Global Technology Service and Support Certification for "an outstanding customer service experience."

"With the scale of our support organization we can provide much better Linux support at a much lower price," said Executive Vice President of Oracle Customer Services Juergen Rottler. "We have the expertise and infrastructure to improve substantially the quality of support for enterprise Linux customers."

Enterprise Linux binaries will be available for free from Oracle. Enterprise Linux Network Support will be offered for $99.00 per system / per year. Enterprise Linux Basic support, which offers Network access plus 24x7 global coverage will be offered for $399 for a 2 CPU system per year and $999 for a system with unlimited CPU's. Enterprise Linux Premier Support, which offers Basic support plus back port of fixes to earlier releases as well as Oracle Lifetime Support will be offered for $1,199 for a 2 CPU system per year and $1,999 for a system with unlimited CPU's.

Oracle and Linux
Oracle has been a long-standing, key contributor to the Linux community. Oracle produced its first commercial Linux database in 1998. Since that time Oracle has worked steadily to improve the experience of all Linux users. Oracle's Linux Engineering team is a trusted part of the Linux community, and has made major code contributions such as Oracle Cluster File System that is now part of Linux kernel 2.6.16. Oracle has been and will continue contributing Linux related innovations, modifications, documentation and fixes directly to the Linux community on a timely basis.

About Oracle
Oracle (NASDAQ: ORCL) is the world's largest enterprise software company. For more information about Oracle, visit our Web site at http://www.oracle.com.

Trademarks
Oracle, JD Edwards, PeopleSoft, and Siebel are registered trademarks of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

This announcement is provided to you solely for information purposes, is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. Many factors can materially affect Oracle's product development plans and the nature and timing of future product releases. The development, release, and timing of any features or functionality described remains at the sole discretion of Oracle. This information may not be incorporated into any contractual agreement with Oracle or its subsidiaries or affiliates. Oracle specifically disclaims any liability with respect to this information.

# # #

--------

What Is an MDDB?

A multidimensional database, or MDDB, is a specialized storage facility that allows data to be pulled from a data warehouse or other data sources for storage in a matrix-like format. The process of building an MDDB summarizes the raw data; the data stored in the MDDB is thus said to be presummarized. The MDDB enables users to quickly retrieve multiple levels of presummarized data through a multidimensional view. An MDDB is not a SAS data file; instead of the traditional structure used for SAS tables, an MDDB stores its data as an NWAY cube and zero or more subcubes.

参看

--------

一直以为，这个第三方是针对甲方，乙方之外的。
今天看到一个说法：

一般来说，IT厂商为客户提供的服务可分为三种形式：IT厂商自身为用户提供服务 ，称之为第一方服务；通过广泛的渠道商为用户提供服务，称之为第二方服务；再有就 是将服务业务外包给独立的第三方服务商，我们称之为第三方服务。

这里出来了第一方服务，第二方服务， 感觉定义有些牵强。

又有：

第三方服务是指由独立提供的专业服务商，以第三方的角色为客户提供系列的专业性服务过程，该过程以合同的形式来界定供需二者之间的职责。

这种说法和我以前理解的比较接近。

我觉得还是这种理解比较合理。

--------

周五(06-10-20)昆山通宵，周六上午返回，休息了下午。朋友约周日游玩崇明岛，友已很久没有见面，都忙。他买车已很久，这次蹭车出游。

路上友顺路办些事，到达轮渡时已然午时。

一小时后到达崇明。

陪友看房（其欲投资于此），已涨至5800/m2, 想起自己购买时不过5100，感慨。

然后驱车直奔湿地，路上到一酒家就餐，无他，但酸辣汤酸尔。

于下午3时到达湿地，有“xxx湿地欢迎你”为证。

车停至一门前，工作人员问：“侬哪能”？

始知车不能进。于是登记，得一通行证，问是否回来要交回，答曰可留作纪念。于是欣欣然进入湿地。
于是拍照，拍照，拍照。

于是见一白鹭状飞鸟，于泥泞中散步。

于是见水牛数头行进于泥泞。

然有无边芦苇，风吹芦苇声声，一境。欲录之而不可得。

于是离开。友迷失归来路。几撞于一货车。

于五时抵码头，等。

于八时上船。

于十时到地铁。

于十时四十抵家。

颓然曰，金窝银窝不如自己狗窝。 况非狗窝乎！

昨夜与妻散步于小区，说起邻居因婆媳交恶而夫妻失和，仰头一轮弯月，感慨人生短暂，而因为各种琐事蹉跎岁月。 想起张若虚的《春江花月夜》中感慨天际久远，人生短暂。看到这小区楼房，十年后，二十年后可能不复存在，月亮若有知，能看到这历历的人事更迭，世事变化。那么短暂人生的些许苦恼，又算的了什么？

当时总既不起这句诗，有暇google之：

江畔何人初见月？

是谁和我一样，第一次有这样的感慨？

江月何年初照人？

江月是什么时候看到这芸芸众生？

--------

输入的半角单引号，在显示时都变为全角了。
查数据库的数据，都还是半角的，备份导出的数据也是半角的。
分析是显示时进行了转换。

目前没有办法，贴出的脚本，需要修改后才能运行。

--------

今天提交metalink时， 发现hp-ux的9000系列有 2个选择，以前没有见过：

HP 9000 Series HP-UX BLS (Secure)

HP 9000 Series HP-UX CMW (Secure)
google了一下：

questions:

what difference between the HP platforms?
a. HP 9000 HP-UX 11.0(32 bit)
b. HP 9000 Series HP-UX BLS(Secure)
c. HP 9000 Series HP-UX CMW(Secure)
d. HP IA64 HPUNIX
e. HP Tru64 UNIX
f. HP-UX Itanium
g. HP-UX PA-RISC(64-bit)

answer:
a) 32 bit OS revision 11.00 of HP-UX
b) B Level security revision of 11.00 of HP-UX
c) C Level security revision of 11.00 of HP-UX
d and f) 64 bit OS of HP-UX running IA64 hardware
e) pre-merger compaq version of Unix
g) 64 bit version of "a)"

--------

一个同事msn登录不了，删除后重新安装时失败。

用7.5安装，提示下载最新版本，下载最新版本安装提示1061错误。

到google上查了一下，发现好多人遇到这个问题，查到这个解决方法：

http://www.bigblueball.com/forums/msn-messenger-support/17420-error-code-1601-1603-solutions.html

1601 Fix - courtesy of naRc

1. Click START, and then click RUN.
2. In the OPEN box, Type msiexec /unreg, and then click OK
3. When this process is complete, click START, and then click RUN.
4. In the OPEN box, type msiexec /regserver, and then click OK.
5. Now install MSN 6

我测试安装7.5,live messenger都可以。 

不知道还以为自己人品问题。（啊，msn都安装不上?!)

表数据200万，使用一个字段查询，该字段上有索引，客户说没有使用索引。

1.要表结构

create table STAT_SALE
(
  OIDD          NUMBER(20) not null,
  CODE          VARCHAR2(20) not null,
  SHOPCODE      VARCHAR2(20) not null,
  SHOPNAME      VARCHAR2(50) not null,
  GOODSCODE     VARCHAR2(20) not null,
  GOODSNAME     VARCHAR2(100),

...

create index U_STATSALE_GOODSCODE on STAT_SALE (GOODSCODE)  ... 

2.要查询语句

select * from stat_sale a where a.goodscode=290957

聪明的你现在应该可以看出没有使用索引的原因了。

愚蠢的我没有发现。

3.执行

select count(*) from stat_sale;
select num_rows from user_tables where table_name = 'STAT_SALE';

2086984  和 null
分析是没有analyze的原因

3.analyze

analyze table STAT_SALE estimate statistics sample 5000 rows;
analyze index U_STATSALE_GOODSCODE compute statistics;
analyze table stat_sale estimate statistics sample 5000 rows for all columns;

4.再执行上述语句看执行计划

仍然没有使用索引 。

faint.

此时才发现，类型是varchar2，而=后面是数字，发生了类型转换，没有使用索引。

数字加上''后，使用了索引。

今天有兴趣，想把palm上网的事情搞一下，因为同事的Nokia都可以上网干好多事情，掌迅通啦，看小说啦，目前palm虽然利用率挺高，但是主要就是看书，asktom,参禅日记，历史的经验诸如此类，而一次和朋友用palm传输文件时茫然不知所措，被讥"需要再学学"，伤害至今。趁今天事情较少，天气不热之午后，升级一下我的palm的功能。

先上全球通，看了一下开通的功能，又看了一下我的上月花费

赶紧弄个套餐。

咨询了几个同事后，决定申请20元的gprs了，包含50M,怎么看也购了。超过部分1分/k。

而且也不用设置乱七八糟的网关。

msn应该也可以上。

然后把以前安装的掌迅通恢复出来， http://www2.handcn.com/

天气，咨询，股票(虽然不用），hoho，都有。前面订的0121天气短信可以取消了（每天早上7点发一次，烦不烦啊）

测试了一下gprs连接时打电话，没问题。

great.

enjoying.

--------

老婆怀孕已经21周了，总的来说，身体没有特别的反应，作息都正常。

最近感觉大拇指不能动，动的时候连着的筋特别疼，老婆到网上论坛看（她已经能充分利用互联网这个东西了），好多孕妇都会有，于是有些释然。然而越来越重，遂到中医院求治，医生认为没有什么办法，吃药打针，针灸之类对孕妇都不行，只能忍着。（医生给出这样的建议是不是有点不称职）。

于是忍着。

不料最近今天左手又开始发作，老婆大骇，"我还要生活啊"。

于是到西医求治，西医开初药方“理疗”，于是理疗。

今天已经第二天，医生说要4天以后 才有效果。

静观其效。

昨天的事情。

一个客户在rhel3u4上安装oracle 817.

runInstaller时oui出来后就没有了。

set -x 跟踪runInstaller,发现java 执行时异常退出。

后来通过qq远程桌面共享上去， 用vnc连接，启动runInstaller 正常，安装正常。

分析可能是Xwindows配置的有问题。

后来和朋友聊起来，windows的netmeeting也不错，他们远程技术支持用这个，有机会试试。

比较起来，oracle的 owc(oracle web conference)最强，基于web的，只要能上网就能

被远程共享。

第一次使用owc时，也是给帮客户解决问题， oracle的技术支持是印度的，说得英语就像方言，根本不知所云。不幸中的万幸是听到了一个wc,估计是他想用owc.就赶紧说Ok。

用owc,交流方便多了。oracle本身也建议使用owc，比起metalink解决问题的速度快。

--------

当我终于有时间，有精力来总结分析那晚的经过时，事情已经过去了3天。

最大的教训是，在没有彻底弄清楚要执行的操作前，不要按回车键。

事情是这样的。

客户要删除指定时间以前的数据，有partition表， 有非partition表。对于partition表，客户要求用删除分区的方式，非pairtition表用delete命令。

ok,这个要求很好。

于是我准备sql

1）取出所有part表

select table_name, partitioning_type from user_part_tables;

select index_name, partitioning_type,locality from user_part_indexes;

 set heading off
set echo off
set feedback off
set pages off
set long 90000
set linesize 300

spool all_part_new.sql

select dbms_metadata.get_ddl('TABLE','t1','user1') from dual;

select dbms_metadata.get_ddl('INDEX','IDX_1' ,'user1') from dual;   

...

4)用ue查找PARTITION " ，把结果粘贴出来，修改成删除partition的sql。

alter table t1 drop partition "b1_P1";

...

5)执行sql

删除了几个表的分区，忽然客户反映应用异常，不能处理事务。

有索引不能访问，查看此索引，发现状态为INVALID.

alter index idx_a  rebuild;

把所有invalid索引都重建，应用恢复正常。

为什么会出现索引invalid?

再查看9i文档

Managing Partitioned Tables and Indexes部分：

Leave the global indexes in place during the ALTER TABLE ... DROP PARTITION statement. Afterward, you must rebuild any global indexes (whether partitioned or not) because the index (or index partitions) will have been marked UNUSABLE

前面因为如果是local partition的索引，那么就直接删除了,没有关系，但是一些索引，比如主键，就变为invalid了。

最好的使用第三种方法：

Method 3:

Specify UPDATE GLOBAL INDEXES in the ALTER TABLE statement. This causes the global index to be updated at the time the partition is dropped.

ALTER TABLE sales DROP PARTITION dec98      UPDATE GLOBAL INDEXES;

alter table t1 drop partition "b1_P1" update global indexes; 

6）遗憾的是，当时没有发现这个。

客户的客户，要求使用delete删除。

一级压一级。

于是使用删除命令。

因为有数据量较大的表（超过千万），所以想把比较大的表分阶段删除。

把前面取出的记录数保存到excel文件里面，安装大小排序，查找表，看其记录数，如果比较小就直接全部删除，

否则分阶段，三个月删除一次。

生成sql,执行。

7）执行时发现，一个千万级的表，没有分阶段删除。

（原因是查找表的时候，2个表的名字前面几个字符一样，结果看了表名更长的，

但是记录数为0的表，就没有加分段日期）。

担心执行时间很长，于是决定停止删除。

用top看占用cpu较多的oracle进程，通过

 select sql_text from v$sqltext a,v$session b,v$process c 
  where a.hash_value=b.sql_hash_value and 
        b.paddr=c.addr and 
        c.spid=&1 order by piece;

(spid输入oracle的unix进程号）确定是否是正在执行删除的sql.

然后alter system kill session 'sid,serial#';停止

停止时提示：已经被标识为删除，但是进程还在，查看oracle帮助，提示因为这个进程的

事务没有完成，所以没有马上停止。

这时候是在执行回滚。

在kill时，已经执行了30分钟左右。

等待回滚，大概1个小时。

7）回滚完成后，修改此sql,改为每3个月删除一次。

删除过程中，oracle crash。

查看alert文件，发现有不能扩展undo segment的提示，然后就是ora-600错误。

初步判断是undo segment不够引起的。

尝试启动instance,发现oracle的vg不可访问，发现vg已经处于deactive状态。

查看vg的一些命令，特别想直接vgchange -a -s vgdata,启动，而且客户也在催。

不过仔细想想，这个命令以前没有用过，是不是我理解的，如果不对，导致另外一个instance crash，

整个服务停止，那就麻烦更大了。

所以没有操作，要客户请求hp工程师支持。 此时是凌晨3点半左右。

等待...

到凌晨6点多，hp工程师到现场。

查看日志，了解情况，大概20分钟左右，因为关系重大，hp工程师也没有马上active vgdata,

在大概7点10分左右， 第二个instance crash。

现在已经没有更坏的情况了，hp工程师重启cluster,cluster自动启动oracle.

oracle启动正常，服务恢复正常。 （此时庆幸没有自己启动vgdata)

8)接下来要查第二个instance crash的原因

查看alert和相关文件，发现

*** 2006-10-13 07:06:55.757
*** SESSION ID:(10.1) 2006-10-13 07:06:55.709
TIMEOUT ON CONTROL FILE ENQUEUE
mode=X, type=0, wait=1, eqt=900

到此，应用正常，已经是上午12点，从9点到现场，到现在，已经是筋疲力尽。搭车回家。

后来查metalink ,

https://metalink.oracle.com/metalink/plsql/ml2_graphics.ShowProcessingMessage?p_ml2_text_code=PROGRESS_MSG&p_content_page=f?p=130:14:3374539482892071369::::p14_database_id,p14_docid,p14_show_header,p14_show_help,p14_black_frame,p14_font:NOT,153788.1,1,1,1,helvetica
 
oracle建议：
 
Check the alert log to see how often a logfile switch is taking place.
 
  Try and ensure that REDO log files are sized such that a log switch 
  takes place approximately every half hour during heavy load. This will 
  reduce the number of times we need to obtain the control file enqueue.
 
  Tune your datafile layout to resolve I/O contention.
  Check with the Hardware vendor to ensure there are no hardware problems
  and that you are on the latest OS patch level. 
  



检查alert日志，归档只有一次，不是归档频繁引起 controlfile enqueue wait. 

 






问hp工程师，也没有查出明显的cluster问题。 




往下查，没有思路了。 只能提交oracle metalink。 


 


非常郁闷的2天。




  




 


  

exp/imp参数
ignore: 忽略表存在，继续导入，这个对于需要调整表的存储参数时很有用，我们可以先根据实际情况用合理的存储参数建好表，然后直接导入数据。
grants: 是否导入授权,一般为Y
indexes:是否导入索引。如果为了使用新的存储参数重建索引，或者为了加快导入速度，可以考虑将indexes设置为N.
feedback: 每多少行回显一次进度
commit: COMMIT=Y will cause the import to commit after each buffer.  The buffer size is controlled by the import BUFFER parameter.

--------

跨版本exp/imp

 术语：源数据库：要导出数据的数据库；目标数据库： 要导入数据的数据库；

原则：

1）导入时，使用目标数据库的imp版本，无论目标数据库版本比源数据库版本高还是低。 2）导出时，使用源数据库和目标数据库中的较低的版本。

例子：

1）升级数据库 (低版本到高版本） 

--在低版本服务器上,用低版本的exp导出

--在高版本服务器上上执行用高版本的imp导入

2）降级数据库，从高版本导出到低版本

--在低版本数据库上，用低版本的exp通过NET8连接到高版本数据库，导出

--在低版本数据库上，用低版本的imp导入

--------

真是不比不知道，一比真需要。

招行推出   住房贷款 随借随还  业务：

不仅仅是还款方便，还能直接借钱。

想想去工行提前还贷那个麻烦，真是不可同日而语。

很重要的一点， 已办理其他银行按揭的仍可申办

这些市民只要到该行网点提出转按揭和住房循环授信申请，招行就会发放同名转按揭贷款，来帮市民向原按揭银行还清贷款，同时将该笔贷款转入招行名下，最终为市民办好“随借随还”业务。

估计招行能从其他银行那里拉不少客户。

反正我动心了。

原文：http://www.southcn.com/news/gdnews/nanyuetuijian/200610110445.htm

--------

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:3058895998401

好好研究一下这个，对于性能调优重要。

--------

原文见： http://asktom.oracle.com/pls/ask/f?p=4950:8:15133282895453016021::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:801662252143 

1.理解parse
There are two types of parses (well, actually "three" these days).

They are

o HARD parse -- the query has never been seen before, isn't in the shared pool. 
We must parse it, hash it, look in the shared pool for it, don't find it,
security check it, optimize it, etc (lots of work).

o SOFT parse -- the query has been seen before, is in the shared poo.  We have
to parse it, hash it, look in the shared pool for it and find it (less work then
a hard parse but work none the less)

o a kinder, softer SOFT parse -- you are using session_cached_cursors (search
this site for that word for info).  We take your query, look in the sessions
cursor cache -- find the cursor and reuse it.  Very very little work.

So, after "startup", you go into sqlplus and do this:

SQL> alter session set session_cached_cursors=0; -- just making sure this is off
SQL> select * from emp;  -- hard parse
SQL> select * from emp; -- soft parse
SQL> select * from emp; -- soft parse
SQL> alter session set session_cached_cursors=100; -- enable this feature
SQL> select * from emp;  -- soft parse
SQL> select * from emp; -- kinder, gentler, soft parse

2.避免soft parse
how you avoid a soft parse varies by language.

In PLSQL -- you get it for free.  If you have code that does:
  for i in 1 .. 100
  loop
        for x in ( selct * from t where zzz = i )
        loop
           .....

the query "select * from t where zzz = :BIND" is parsed ONCE per session -- not
per execution.
In Java -- you would use a prepared/callable statement and prepare it ONCE per
session -- not once per execution.

In VB -- same, don't be in such a hurry to close those statments, reuse them
over and over and over.
In SQLPlus -- being a very generic tool -- it canot cache open cursors for us
like that.  You can play with session_cached_cursors to achieve some caching by
the server. 

PLSQL being very very specific -- can cache the (static) cursors for us. (but
not ref cursors)

3.在plsql中必须使用store procedure才能避免soft parse

Hi

 When you execute multiple times BEGIN END block with insert in we get :
INSERT INTO SIEBEL.S_ACCNT_POSTN  (
      CREATED,
      ASGN_MANL_FLG,
      LAST_UPD_BY,
      OU_EXT_ID,
      MODIFICATION_NUM,
      ROW_ID,
      CONFLICT_ID,
      ASGN_DNRM_FLG,
      ROW_STATUS,
      LAST_UPD,
      CREATED_BY,
      ASGN_SYS_FLG,
      POSITION_ID)
VALUES ( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       19      0.46       1.40          0          0          0           0
Execute     19      0.02       0.02          0         22        285          19
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       38      0.48       1.42          0         22        285          19

Misses in library cache during parse: 0
Optimizer goal: RULE
It means 19 soft parsing of insert INSIDE plsql block...

                                      
Followup: 
Sorry -- should have been more specific.

Use a stored procedure, only stored procedures can do this caching.  If you
execute:

begin
   insert into t .....
end;

it'll parse the insert once for every execute.  OTOH, if you put the insert into
a procedure:

create or replace procedure p( ... )
as
begin
   insert into ....;
end;
/

then the insert will be parsed upon the first execution of the procedure in your
session and not again.
 

--------

Hi Tom,
Can you give us an idea as to how to estimate size of SGA? An experienced dba
told me that the best way to estimate size for SGA is to set aside 40% of memory
for sga i.e for 2gb memory 800mb is sufficient. Is this correct?
If yes, then if i add another 1gb ram how can i justify this to customer saying
ONLY 40% will be used by oracle.

Thanks a lot for all your help 

 －－－－－－－－－－－－

so, that is the way to estimate the SGA size.  Interesting.

I always thought it is a function of what you need to do, not how much ram just
happened to be installed.

But basically you need to understand how Oracle uses memory (my book Expert one
on one Oracle goes into that).  There is the SGA and PGA memory.

Your DBA's rule of thumb (ROT) here is "you want to use 40/50% of RAM for the
SGA leaving the other 50% for the dedicated servers (processes -- they allocate
PGA) and 10% or so for the OS and related processes"

That is, their ROT is how to maximize the use of memory for a typical dedicated
server configuration.

That ROT is totally wrong if you are using shared server (there the SGA might be
80-90% of RAM)

That ROT is totally wrong if you are not the only game in town (not the only
thing on the server).

That ROT does not mean you are using only 40% of the additional gig of ram you
are putting in, it means "40% of it will go towards the SGA, the remaining will
be available for dedicated servers to do sorts and hashes and stuff"

but the major considerations here are:

a) how much do you want to assign to your buffer cache for maximum performance
b) how big is your shared/java pool (a function of how much sql/plsql/java you
run in your database, no magical number for all to use)
c) do you run in shared server (than the large pool is used and will be large --
that is part of the sga) or in dedicated server -- then you need to leave OS
memory for dynamic allocations
d) what else is going on in the machine.

--------

基地组织曾派五人潜入中国制造恐怖事件，
一人炸立交桥，转晕于桥上；
一人炸公交车，没挤上车；
一人炸超市，遥控器被盗；
一人炸高楼，被保安狂揍："叫你讨薪，叫你上访"；
最后一人成功炸矿，死伤数百人。潜回基地后，半年都没见新闻报道，遂被基地组织以"撒谎罪"处决。  

--------

一不小心弄大了，成了老婆监管范围之一：周一到周五不能玩。

对这个决定，我是双手赞成的（其实自己也感觉不大好），主要是一旦失败，总想翻盘，而往往是连着输，导致赌徒心理出现（好像在晚上11点以后特别强烈）。

解决方案是强制在10点左右休息。

以观后效。

以上评论针对星际争霸(starcraft)。

在vmware试用了suse10一段时间后，感觉可以替代xp了。

于是划分出一个分区，安装suse. 有机会就尽量在suse下。

msn，office 常用的工具都没有问题；

读写原来的文档分区也都可以；

Firefox浏览网站，基本上没有问题；

fttb拨号也可以；

ssh天然有；

 不足：

qq始终没有弄好，测试了几个包都不行，(虽然极少使用)；

perl分析log日志自动生成word文档不能在linux下运行，必须切换，而经常要写有这样的报告；

有个项目要写个进度安排，试用了一下Plan，打印不理想，不能设置字体，在一张大纸上打印出小小的一块。

损失：

原来在windows上的vmware安装的软件都要重新安装；

其他损失：

为了划出更大的分区，用pqmagic分区移动硬盘时失败，导致移动硬盘没有盘符，大骇，到suse10下还能找到，狂喜，导出，郁闷，空间要80G,删来删去 ，suse 的samba配置，ftp都搞的不爽。后来发现windows下硬盘管理中还是能看到移动硬盘，加上盘符后就可以了，又郁闷一次。

感慨一声，算了，还是转到xp上吧。

 总之，一次失败的尝试。

1. 一个准客户在做一个系统的性能测试，前面打电话聊过，他从metalink，bbs等了解到rac的insert 比单节点性能低，并希望能协助调整。


2. 首先感觉他的思路有问题， 对一个系统的性能测试，应该用专门的压力测试工具，用TPC指标进行测试，看能否达到应用的需求。对单独的insert等操作，有什么说服力呢？


3. 根据他的要求， 对rac（连接到某个节点）， 单节点（cluster_database=false)分别进行插入20000条记录测试（1.sql,内容见下面）， 用set timing on 记录执行时间。 测试发现，2个时间基本是一样的。详细情况参看下面的测试记录


4. 后来，使用swingbench进行压力测试。因为2个node配置差别很大，一个2cpu，4GRAM（node1），一个4cpu, 8GRAM (node2) ,负载均衡时， 一个node已经满复合，另外一个还只有20％，总不能达到整个系统的峰值。后修改客户端的tnsnames.ora （见下面附录），在ADDRESS列表中增加2个node2,负载均衡时， node2将得到3/4的连接， 压力也增加3/4, 这样测试，基本达到了系统的峰值。

l         single node.

$ sqlplus cqtest/cqtest

SQL*Plus: Release 9.2.0.6.0 - Production on Mon Oct 9 11:31:00 2006

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production

With the Partitioning, Real Application Clusters, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

SQL> set time on

11:31:03 SQL> set timing on

11:31:05 SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.00

11:32:05 SQL> @1

11:32:07   2  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.69

11:32:16 SQL> alter session set events '10046 trace name context off';

Session altered.

Elapsed: 00:00:00.00

11:32:30 SQL>

l         rac

11:34:13 SQL> conn cqtest/cqtest

Connected.

11:34:47 SQL> truncate table free_item_no;

Table truncated.

Elapsed: 00:00:00.35

11:34:56 SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.01

11:35:12 SQL> @1

11:35:15   2  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:07.98

11:35:24 SQL> alter session set events '10046 trace name context off';

Session altered.

Elapsed: 00:00:00.00

11:35:34 SQL>

 后又在vmware-linux的rac (10.2.0.1)上测试， 测试结果：

linux-rac

l         rac

SQL> conn lyf/lyf

Connected.

SQL> create table te1 (id number);

Table created.

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

SQL> set timing on

SQL> set time on

01:32:23 SQL> begin  for no in 1..20000 loop    insert into te1 values (no*11);  end loop;  commit;end;

01:32:35   2  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.49

01:32:41 SQL> alter session set events '10046 trace name context off';

Session altered.

Elapsed: 00:00:00.01

01:33:12 SQL>

l         node

SQL> conn lyf/lyf

Connected.

SQL> set timing on

SQL> alter session set events '10046 trace name context forever, level 12';

Session altered.

Elapsed: 00:00:00.02

SQL> begin  for no in 1..20000 loop    insert into te1 values (no*11);  end loop;  commit;end;

  2  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.73

SQL> alter session set events '10046 trace name context off';

Session altered.

 通过2个平台， 2个版本比较， 感觉rac和单节点在insert上速度可以认为基本一样。

tnsnames.ora

ORCLnew =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.102)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.102)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.102)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.101)(PORT = 1521))
      (LOAD_BALANCE = yes)
      (FAILOVER = on)
    )
     (CONNECT_DATA =
       (SERVER = DEDICATED)
       (SERVICE_NAME = orcl)
       (FAILOVER_MODE=
           (TYPE=session)
            (METHOD=basic)
       )
     )
   )

1.sql

begin  for no in 1..20000 loop    insert into te1 values (no*11);  end loop; 

--------

1.跟踪自己的session

会话已更改。

已用时间: 00: 00: 00.00
22:43:39 SQL> declare
22:44:03 2 v_num integer;
22:44:03 3 begin
22:44:03 4 v_num := 0;
22:44:03 5 while v_num < 10000
22:44:03 6 loop
22:44:03 7 insert into t2 values(v_num,'def');
22:44:03 8 v_num := v_num + 1;
22:44:03 9 end loop;
22:44:03 10 commit;
22:44:03 11 end;
22:44:03 12 /

PL/SQL 过程已成功完成。

已用时间: 00: 00: 01.18
22:44:06 SQL> alter session set events '10046 trace name context off';

会话已更改。

已用时间: 00: 00: 00.02
22:44:28 SQL> select d.value||'/'||lower(rtrim(i.instance, chr(0)))||'_ora_'
||p.spid||'.trc' trace_file_name
23:02:28 2 from ( select p.spid from v$mystat m,v$session s,v$process
p
23:02:28 3 where m.statistic# = 1 and s.sid = m.sid and p.addr = s.paddr) p,
23:02:28 4 ( select t.instance from v$thread t,v$parameter v
23:02:28 5 where v.name = 'thread' and (v.value = 0 or t.thread# = to_num
ber(v.value))) i,
23:02:28 6 ( select value from v$parameter where name = 'user_dump_dest
') d
23:02:29 7
23:02:30 SQL> /

TRACE_FILE_NAME
--------------------------------------------------------------------------------

D:ORACLEPRODUCT10.2.0ADMINORCLUDUMP/orcl_ora_2536.trc

D:oracleproduct10.2.0adminorcludump>tkprof orcl_ora_2536 lyf3.txt

TKPROF: Release 10.2.0.1.0 - Production on 星期日 10月 8 22:39:36 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

D:oracleproduct10.2.0adminorcludump>

2.了解raw trace的更多内容

可以参看

3． 3． 跟踪其他用户进程
在很多时候我们需要跟踪其他用户的进程，而不是当前用户，这可以通过Oracle提供的系统包DBMS_SYSTEM. SET_SQL_TRACE_IN_SESSION
来完成

SET_SQL_TRACE_IN_SESSION过程序要提供三个参数:

SQL> desc dbms_system
…
PROCEDURE SET_SQL_TRACE_IN_SESSION
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SID NUMBER IN
SERIAL# NUMBER IN
SQL_TRACE BOOLEAN IN
…

通过v$session我们可以获得sid、serial#等信息:

获得进程信息，选择需要跟踪的进程:

SQL> select sid,serial#,username from v$session where username is not null;

SID SERIAL# USERNAME
---------- ---------- ------------------------------
10 617 LYF
11 18 SYS

设置跟着:
SQL> exec dbms_system.set_sql_trace_in_session(10,617,true);

PL/SQL 过程已成功完成。

….
可以等候片刻，跟踪session执行任务,捕获sql操作…
在10,617上执行insert into t2 values('cc');

….

停止跟踪
SQL> exec dbms_system.set_sql_trace_in_session(10,617,false);

PL/SQL 过程已成功完成。

到udump目录下，按照日期排序可以看到新生成的文件。

--------

1.在sql提示符显示当前时间

set time on,缺省是off

2.显示sql执行消耗的时间

set timing on,缺省是off

3.使用

SQL> show time

time OFF

SQL> show timing

timing OFF

SQL> select * from t2 where rownum < 2;

        ID NAME

---------- --------------------

         0 abc

SQL> set time on

21:46:46 SQL> select * from t2 where rownum < 2;

        ID NAME

---------- --------------------

         0 abc

21:46:53 SQL> set timing on

21:46:58 SQL> select * from t2 where rownum < 2;

        ID NAME

---------- --------------------

         0 abc

已用时间:  00: 00: 00.00

21:47:05 SQL>

--------

desc(from customer):

solaris, oracle 9206 rac；单独另外一台机器9206.

测试插入1000条记录， rac耗时2秒，单机耗时1秒。

把rac的cluster_database改为false，仍然耗时2秒.

 ques:

如何调整rac?