ITPub博客

很小的一件事，从服务器直接exp就应该搞定的，没想到哪位大哥把9201升级至9206时未运行catpatch.sql，结果数据库本身还是9201,找个9201的客户端exp吧。9201 client导出9206数据库,出现EXP-00003: 未找到段xxx的存储定义以下内容摘自互联网：导出的时候,我们有时候会遇到一种错误EXP-00003: no storage definition found for segment这个是属于数据库的bug.如果客户端版本低于9205,而服务器高于或者等于9205,也可能遭遇这个bug,出问题的是client,而非server除了打补丁以外,还有种解决方法1.先找到这个文件,$ORACLE_HOME/rdbms/admin/catexp

DEADLOCK DETECTEDCurrent SQL statement for this session: insert into tableName(custidl_pk, custidl_fk, certtpstr, ifaloneidl, custtpidl_pk, custstr, custnamestr, addrcodestr, setupaddrstr, familyaddrstr, certcodestr, workstr, agestr, nationstr, mailstr, telstr, mobilestr, sexstr, zipcodestr, unitnamestr, hfccuststr, ifvalididl, pwd, mem, creatorstr, belongareastr,USERTPCODESTR, hfcmainidl,hfcextidl,sub_description,agentstr) values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10, :11,:12,:13,:14,:15,:16,:17

LISTENER = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = [ip|hostname|localhost|127.0.0.1])(PORT = 1521)) )1.设置IP的话tnsping localhost|127.0.0.1不行。2.设置hostname的话tnsping ip|hostname|localhost|127.0.0.1都没有问题3.设置127.0.0.1|localhost的话tnsping ip|hostname都不行。[@more@]

rac下load balance分为两种：1.client端load balancetnsName =(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = vip2)(PORT = 1521))(LOAD_BALANCE = yes)(CONNECT_DATA =(SERVICE_NAME = dbServiceName)))2.server端监听load balance用REMOTE_LISTENER参数,指向tnsname.ora中的监听LISTENERS_dbname =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = vip1)(PORT = 1521))(ADDRESS = (PROTOCOL = TCP)(HOST = vip2)(PORT = 1521)))[@more@]

1.为什么复制只能进行不完全恢复？由于联机日志不能由rman进行备份，只能恢复由rman已备的归档日志。2.什么是复制当中的辅助数据库?rman将目标（主）数据库复制到该数据库实例的数据库。需要创建其参数文件，路径和口令文件，由于没有控制文件，所以须以nomount方式启动。2.什么是复制（duplicate)?从其他数据库的rman备份创建一个新数据库的过程。从rman角度来说，目标数据库被复制到副本数据库。3.步骤(1)执行目标数据库的全备，包括归档日志。run{allocate channel c1 type disk maxpiecesize 300m;backup database format 'c:ackupdf

9i下，shared_pool_size不完全控制共享池中的所有组件，而只是控制大部分，因此，shared_pool_size获取的真正总和10g,shared_pool_size已经完全控制共享池中的所有组件，因此和v$sgastat值一致。[@more@]

analyze table/index validate structure会加DML级S锁，因此可能会影响到正常的DML操作，如果该语句执行时间过长的话，后果会比较严重。[@more@]

问题描述：24颗CPU/48G内存的RAC系统上，只跑了一点应用，CPU却占到50%以上。查一下吧，我晕，全是一些大表全表扫描，表的个数也不多，全系统总共才400张，一看索引，基本上没建几个。这些SQL还真是缺索引，基本上是update,delete,select的简单sql,而且selectivity即佳，COST降得令人振奋，一个一个建吧,直至建了20多个索引。效果：效果不错，CPU降到20%左右了，那些select ** 内部又order by 的大型分页没办法了，算了，不管了，把这个去掉的话估计CPU要降到0了，呵呵。心情：这些开发大人，估计连索引是个啥都没弄清楚，哎，越来越趋向于

Oracle Error :: NID-00135There are number active threadsCauseis that the database crashed the last time it was shut down.ActionEnsure that all threads are closed before retrying the operation. Start and open the database to perform crash recovery, then shut down with the NORMAL or IMMEDIATE options to close it cleanly. Finally, try running the utility again.[@more@]

10G在sys用户中已经建立好PLAN_TABLE$表，并把增，删，改，查的权限赋给了public,并在其上建立一PLAN_TABLE同义词。这下好了，什么都不用做了。直接按F5就可以看到执行计划了。呵呵[@more@]

安装10.2.0.3补丁时提示无法安装，找不到原来的10201软件，查看$ORACLE_BASE/inventory正常，一头雾水。原因：/var/opt/oracle目录的owner并不是oracle，晕倒。[@more@]

RAC下的序列造成的，加大cache的大小会有所改善，以下是转载DFS LOCK HANDLE 是无法获得 global cache lock的handle时候所记录的等待事件，如果在RAC环境下使用sequence，2个实例都有并发的session使用sequence，这时有可能遭遇 DFS LOCK HANDLE等待。参考文档如下：http://www.pythian.com/blogs/383/sequences-in-oracle-10g-racSequences in Oracle 10g RACJanuary 31st, 2007 - by Christo Kutrovsky Just recently I got a call from a developer. He had a table with a primary key populated by a sequence, a timestamp column w

大表建索引，rman备份等耗费的时间通常比较长？还有多长时间才能执行完？利用v$session_longops的time_remaining列就可以得到这个估算值，>6秒钟的操作都会记录与此。select username,sid,opname,round(sofar*100 / totalwork,0)||'%' as progress,time_remainingfrom v$session_longopswhere time_remaining>0 and opname like 'xxx'; [@more@]

windows环境下，%ORACLE_HOME%database下有spfile和pfile，用服务启动数据库时不用spfile启动数据库，只用pfile启动,原来oradim里有参数指定pfile,删掉重建服务就行了。[@more@]

现象：10G em环境，其它用户均可以以normal方式自动登录，而sys无法登录，原因：密码文件不存在。解决办法：unix:orapwd file=orapwSIDwin:orapwd file=pwdSID.ora[@more@]

客户环境为weblogic连接池方式，误设置了idle_time profile资源限制参数，导致超时session变为snipped状态，只好手工alter system kill掉。Note:1061189.6"init.ora" resource_limit = true and idle_time set in profile.When idle_time exceeds, the session status becomes 'sniped' in v$session, butsniped sessions never get cleaned up.[@more@]

Rman Backup Fails: PLS-00302: component 'GETREQUIREDSCN' must be declared客户数据库环境：10.1.0.2->10.1.0.5做的升级。原因分析：这是利用rman备份时产生的错误，原因是升级后未运行catpatch.sql脚本。SELECT comp_name, versionfrom dba_registry;可以看到组件的版本。[@more@]

http://blog.csdn.net/coolyl/archive/2004/11/27/195919.aspx[@more@]

1.关闭所有实例及监听,还有oracle_home下的程序,ps -ef|grep oracle。2.在原安装oracle软件的节点上打补丁。It is important that you install the patch set on the same set of nodes, which had the base release installation. Choosing a different set of nodes during the patch set installation than were chosen during the base release installation will cause unwanted results in your Real Application Clusters environment, as some nodes will be upgraded to the new software versions, while others will not.3.启动

环境如下：Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit ProductionSystem name: HP-UXRelease: B.11.11Version: UMachine: 9000/800且cursor_sharing=similar/force错误描述：ksedmp: internal or fatal errorORA-07445: exception encountered: core dump [kxspoac()+868] [SIGBUS] [unknown code] [0x000000000] [] []Current SQL statement for this session:select NVL(FLG_FWB_RDY,:"SYS_B_0") ,COD_FDS ,:"SYS_B_1" into :b0,:b1,:b2 from AWB_MST where (cod_awb_pfx=:b3 and cod_awb_num=:b4)明显