ITPub博客

今天发生了好几次，杀进程不释放锁的情况。大部分是由于使用 alter system kill session 命令杀不干净导致的，这种情况对于生产库危害很大，锁的长期持有会严重影响业务正常运行。为此问题也曾不止一次开SR，但都没有得到满意的结果。最近在DSI上面看到了关于这方面的一些内部解释，留此备忘：[@more@]Note: When a session is marked for kill (alter session), PMON rolls back that transaction anumber of undo entries at a time (determined by _CLEANUP_ROLLBACK_ENTRIES). Afterthat, PMON continues with its regular cycle to p

Prevent SMON from offlining idle Undo Segments using event 10511.Set the following parameter in init.ora/spfileevent="10511 trace name context forever, level 2"Then restart the database.After the first onlinining of an Undo Segment, it will never be offlined and re-onlined.So we will only get the "US" serialization once (on the first online after a db restart.)[@more@]

_row_cache_buffer_size _row_cache_instance_locks[@more@]

最近看到库上的很多sql 的version 特别多，上metalink学习并分析了一下：原来跟cursor_sharing=similar有关，如果表分析了，那么同样的sql在绑定变量的时候会根据条件输入的不同值而生成不同的子游标。这对sql本身的执行计划的正确性是有好处的，但是对服务器是会带来更多硬解析和cpu资源消耗的。看来凡事都有2面啊！metalink参考：261020.1 和 418348.1[@more@]

情景：想在system下创建视图，其中引用另外一个用户的表，创建时报错ORA-01031: insufficient privileges解决过程如下：[@more@]SQL> show userUSER is "SYSTEM"SQL> create view v_haocp as select count(1) FROM tmsfzwf.t_workitem;create view v_haocp as select count(1) FROM tmsfzwf.t_workitem *ERROR at line 1:ORA-01031: insufficient privilegesSQL> select count(1) FROM tmsfzwf.t_workitem; COUNT(1)---------- 0虽然查询权限有了，但是创

select spid from v$process where addr in (select paddr from v$session where serial# in (select * from (select session_num from v$tempseg_usage order by blocks desc ) where rownum < 11)[@more@]

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-rac[@more@]Sequences 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 with the current date and some other columns. He

最近库遇到了不少的DFS LOCK HANDLE等待，oracle给出的解释，留此备忘：[@more@]The DFS lock Handle:-The session waits for the lock handle of a global lock request. The lock handle identifies a global lock. With this lock handle, other operations can be performed on this global lock (to identify the global lock in future operations such as conversions or release). The global lock is maintained by the DLM.Wait Time: The session waits in a loop until it has obtained the lock handle from the DLM. Inside

hash分区 可以有效的将表的数据进行均匀分布，对于查询来说，分区过滤可以大大的降低逻辑IO，从而提高查询性能。下面进行测试：[@more@]create table TEST( ID NUMBER, NAME VARCHAR2(10))partition by hash (ID)( partition SYS_P1 tablespace SYSTEM, partition SYS_P2 tablespace SYSTEM, partition SYS_P3 tablespace SYSTEM, partition SYS_P4 tablespace SYSTEM);-- 4个hash 分区SQL> desc test 名称 是否为空? 类型 ----------------------------------------- -----

这个事件可以跟踪SMON的行为，留此备忘。[@more@]

redhat linux ：getconf WORD_BIT32[@more@]

前几天，有个库宕机了，无法启动，开SR，oracle怀疑是undo坏了，让我加event="10015 trace name context forever, level 10" 参数验证一下是否是undo坏了。这又是一个与数据库启动和undo相关的参数，而且挺游泳，留此备忘。[@more@]

情景：数据库无法连接，listener报错：TNS-12500: TNS:listener failed to start a dedicated server processTNS-12540: TNS:internal limit restriction exceededTNS-12560: TNSrotocol adapter errorTNS-00510: Internal limit restriction exceededIBM/AIX RISC System/6000 Error: 11: Resource temporarily unavailable初步判断是操作系统资源短缺造成的，在GOOGLE上搜了一下，跟AIX的参数maxuproc有关，这个参数控制操作系统用户开启的最大进程数，经查目前这个参数是256，而目前数据库session有217个，显然是这个参数过小导致数据库

众所周知，cursor_sharing=force可以在数据库端强行绑定变量，以减少shared pool latch 和 cpu 的压力。但通过下面的测试，发现这个参数的设置不是任何环境下都有用的：[@more@]1、sql语句的测试SQL> select sql_text from v$sql where sql_text like 'select /* abc */ %';未选定行SQL> select /* abc */ username from all_users where rownum = 1;USERNAME------------------------------------------------------------SYSSQL> select sql_text from v$sql where sql_text like 'select /* abc */ %';SQL_TEXT--------------

查看rman备份数据文件量的sql，留此备忘。[@more@]比较某2天的数据文件的备份量select file#,recid,to_char(creation_time,'yyyymmdd hh24:mi:ss'),datafile_blocks,blocks,to_char(completion_time,'yyyymmdd hh24:mi:ss') from v$backup_datafile where trunc(completion_time)=to_date('20060126','yyyymmdd') or trunc(completion_time)=trunc(sysdate) order by file#,recid;查看某天 数据文件总的备份量select sum(blocks) from v$backup_datafile where trunc(completion_time)=to_date('20060126','yyyymmdd');

背景：windows2000上的9201库，一个文件损坏，没有备份，需要用dul抽取数据。操作步骤：[@more@]be首先创建2个配置文件：init.dul 和 control.dulinit.dul 是dul工具的参数文件，dul启动的时候要读取这些参数进行配置。control.dul 记录所有数据文件的位置和编号，dul要通过这个文件找到数据文件。init.dul的内容(不同情况不同版本的库内容不一样)：osd_big_endian_flag=falseosd_dba_file_bits=10osd_c_struct_alignment=32osd_file_leader_size=1osd_word_size=32control_file=Y:DUL20070115_contentinfocontrol.duldb_block_size=8192c

前不久刚刚经历ORA-01115的折磨，导致数据库异常宕机而且无法open(详见http://haochunpeng.itpub.net/post/385/248099)。今天又遇到了ORA-01115，但这次是另外一套库，windows2000上的9201。出现错误后，数据库自动把这个出错的文件脱机了。启动数据库报数据文件需要介质恢复，recover datafile的过程中又报错ORA-01115，有个数据块无法读写，但是这个文件在操作系统级别是可以cp的。更棘手的是，这个库没有任何备份。后来用dul把数据导出(详见http://haochunpeng.itpub.net/post/385/251710)，重建了表空间。呵呵。[@more@]

控制SMON行为的2个事件：8105 和 10052描述如下：[@more@]08105, 00000, "Oracle event to turn off smon cleanup for online index build"// *Cause: set this event only under the supervision of Oracle development// *Action: debugging only 10052, 00000, "don't clean up obj$"// *Cause:// *Action:

近期数据库遇到了ORA-01115错误，导致数据库自己立刻把库关闭了。alert log 如下：[@more@]Thu Dec 28 10:23:12 2006Errors in file /oracle/admin/*****/bdump/*****_smon_11633.trc:ORA-01115: IO error reading block from file 2261 (block # 16313)ORA-01110: data file 2261: '/dev/*****/rlv_*****'ORA-65535: Message 65535 not found; product=RDBMS; facility=ORASMON: terminating instance due to error 1115Instance terminated by SMON, pid = 11633Thu Dec 28 10:28:22 2006对这个行为有些疑惑，因为只是一个数据块无法读取