关于skip_unusable_indexes这个参数,大家从它的名称就基本上可以理解它是做什么的,但在使用时需要注意一个小问题,非唯一索引在失效(unusable)状态是允许继续对表进行DML操作,但唯一索引在失效状态是不允许DML操作.
这个富有人性化的改进可以避免因为dba的失误而导致很多事务回退,当dba对表进行move操作时,如果表上没有唯一索引,那我们在move时,就不必担心事务回退,所以如果有些表如果insert/delete非重频繁的话,最好不要创建唯一索引.
10g中加强了对unusable索引的监控,如果你move了表,这时将会在alert日志中记录如下信息:Some indexes or index [sub]partitions of table SCOTT.DEPT have been marked unusable
| 主题: | Customer Introduction to ORA-7445 Errors | |||
| 文档 ID: | 211909.1 | 类型: | BULLETIN | |
| Modified Date: | 10-OCT-2008 | 状态: | PUBLISHED | |
The purpose of this note is to provide an introduction to ORA-7445 errors, and to suggest the next step in diagnosing the error. The note is written with a UNIX bias, although the concepts are generic.
To be used by the DBA when reporting ORA-7445 errors to Oracle Support Services.
An ORA-7445 error is raised by an Oracle server process when it has received a fatal signal from the operating system. The error may be raised in either a foreground or background process. The process will normally
There are many ‘illegal’ operations that the operating system can trap. A common example is a process writing to an invalid memory location, and so to protect the system the offending process will be sent a fatal signal.
Typically, the signals seen are SIGBUS (signal 10, bus error) and SIGSEGV (signal 11, segmentation violation). There are other UNIX signals and exceptions that may happen, however, they are likely caused by OS problems rather than an Oracle problem. Examples of other signals are: SIGINT, SIGKILL, SIGSYS. A complete list is available in Note 1038055.6.
An ORA-7445 is a generic error, and can occur from anywhere in the Oracle code. The precise location of the error is identified by the trace file it produces.
The appearance of an ORA-7445 error varies slightly from platform to platform. The two general forms are illustrated here.
从9.2版开始，Oracle公司设计实现了个别补丁安装管理工具opatch.opatch使用一
个称为inventory的系统数据结构（严格说是与oui共享inventory），集中管理所有已安装的个别补丁；个别
补丁的安装和卸载都使用opatch命令完成，冲突检测也由opatch在安装时自动完成；提供列表命令可以很方
便得到已安装个别补丁的信息。
10g（10.1和10.2）版本中，opatch作为一个标准工具，在软件安装时自动安装。（安装在
$ORACLE_HOME/OPatch下。）而对于9.2版，需要从metalink下载opatch.无论数据库是哪一个版本，系统中是
否已经安装opatch，在使用之前，应从metalink下载最新版本的opatch.很遗憾，由于系统实现的问题，10.2
使用的opatch与之前版本（10.1和9.2）使用的opatch不兼容，不能混用，这一点必须注意。
opatch是使用perl编写的脚本程序（其中也使用JAVA API）。编程使用的perl版本是5.6版，虽然在5.6
之前的版本中也可运行，但应尽可能安装5.6或以上的版本的perl.对于DBA来说一个好消息是，如果安装9.2
版软件时保留了HTTP服务器，则在$ORACLE_HOME/Apache下会自动安装perl.（10g会自动安装配置perl和
opatch.）
Read more…
在本地 IP 路由表中显示和修改条目。
语法
route [-f] [-p] [Command [Destination] [mask Netmask] [Gateway] [metric Metric]] [if Interface]]
Oracle里锁有以下几种模式:
0：none
1：null 空
2：Row-S 行共享(RS)：共享表锁，sub share
3：Row-X 行独占(RX)：用于行的修改，sub exclusive
4：Share 共享锁(S)：阻止其他DML操作，share
5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive
6：exclusive 独占(X)：独立访问使用，exclusive
数字越大锁级别越高, 影响的操作越多。
1级锁有：Select，有时会在v$locked_object出现。
2级锁有：Select for update,Lock For Update,Lock Row Share
select for update当对话使用for update子串打开一个游标时，所有返回集中的数据行都将处于行级(Row-X)独占式锁定，其他对象只能查询这些数据行，不能进行update、delete或select for update操作。
3级锁有：Insert, Update, Delete, Lock Row Exclusive
没有commit之前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。
4级锁有：Create Index, Lock Share
locked_mode为2,3,4不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会提示ora-00054错误。
00054, 00000, “resource busy and acquire with NOWAIT specified”
// *Cause: Resource interested is busy.
// *Action: Retry if necessary.
5级锁有：Lock Share Row Exclusive
具体来讲有主外键约束时update / delete … ; 可能会产生4,5的锁。
6级锁有：Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive
以DBA角色, 查看当前数据库里锁的情况可以用如下SQL语句：
col owner for a12
col object_name for a16
select b.owner,b.object_name,l.session_id,l.locked_mode
from v$locked_object l, dba_objects b
where b.object_id=l.object_id
select t2.username,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time
如果有长期出现的一列，可能是没有释放的锁。我们可以用下面SQL语句杀掉长期没有释放非正常的锁：
alter system kill session ‘sid,serial#’;
如果出现了锁的问题, 某个DML操作可能等待很久没有反应。
当你采用的是直接连接数据库的方式，也不要用OS系统命令 $kill process_num 或者 $kill -9 process_num来终止用户连接，因为一个用户进程可能产生一个以上的锁, 杀OS进程并不能彻底清除锁的问题。
|
library cache 相关的latch 等待并都是因为sql语句没有绑定变量造成的！！！
也有很多种情况会造成相同的sql_id,相同的hash_value而不能share。 例如我们上面的查询找到了sql_id:10xj8nynmpqtq 则执行下面的查询： select SQL_ID,CHILD_NUMBER, SQL_ID CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I SQL_ID CHILD_NUMBER U S O O S L S E B P I S T A B D L T R I I R L I BIND_MISMATCH – The bind metadata does not match the existing child cursor 除了这些能够查询到的原因之外，还有可能所有的列都是 N ,但是仍然还是有很多version_count . 那么恭喜你中奖了，这种情况可能的BUG: Bug 4458226 High version count with cursor_sharing=force Affects: Platforms affected Generic (all / most platforms affected) Fixed: Bug 4701527 Cursors not shared when executing procedures over a dblink Affects: Platforms affected Generic (all / most platforms affected) Fixed: Description To be seeing this problem the following must be true: Bug 3461251 V$SQL_SHARED_CURSOR shows all N with CURSOR_SHARING=FORCE Affects: Platforms affected Generic (all / most platforms affected) Fixed: Description This fix adds a new column to V$SQL_SHARED_CURSOR called HASH_MATCH_FAILED which is set to “Y” if sharing fails due to a hash mismatch, such as the case with mismatched histogram data. Workaround: Bug 5705795 Win/Linux: Many child cursors possible for SQL using BINDS This bug is alerted in Note 403616.1 Product (Component) Oracle Server (Rdbms) * 10.2.0.3 Platforms affected * Linux 32bit It is believed to be a regression in default behaviour thus: Fixed: This issue is fixed in * 10.2.0.4 (Server Patch Set) Symptoms: Description This problem is introduced in 10.2.0.3 on Windows 32bit and Linux 32bit only by the fix for bug 4752541. 10.2.0.3 on other platforms includes the fix for this bug# and so are not affected. SQL using bind variables with different bind sizes can lead to a large number of child cursors being |
|
今天碰到的： Rac其中一个节点sqlplus / as sysdba都会被挂起，另一个Node没有问题。 alertlog中有这样的信息： 看trace文件： [qasmdm2:oracle] /> more /oracle/app/oracle/admin/qasmdm/bdump/qasmdm2_lmd0_438324.trc *** 2008-07-30 00:27:17.430 PEKAX132:/>ps -ef|grep 536584 来看看lck进程是干什么的： 再看trace文件名对应的进程：qasmdm2_lmd0_438324.trc Lock Manager Daemon ?LMDn 显然是由于Rac资源的锁造成的
可知的相关的bug可以参考：BUG 6271080 |
|
There are two scripts in this document. The report generated by the second script provides the ratios of various sleeps for the latches. ttitle - col name form A25 select name,gets,misses*100/decode(gets,0,1,gets) misses, NAME GETS MISSES SPINS IGETS IMISSES 这几列： ======== SET ECHO off col name form A18 trunc select a.name spin sl01 sl02 sl03 sl04 sl05 |
|
论坛上看到有人说高级复制遭遇： ORA-1422, 00000, “exact fetch returns more than requested number of rows” 其实这个错误最好的找出问题的方法就是做trace，从而能找到引起这个问题的具体原因。 当然了，也有一些经验值：
PROBLEM DESCRIPTION:
=====================
You have a replication environment and receive the following error:
ORA-1422, 00000, "exact fetch returns more than requested number of rows"
// *Cause: The number specified in exact fetch is less than the rows returned.
// *Action: Rewrite the query or change number of rows requested
PROBLEM EXPLANATION:
====================
There may be 2 entries in the SYS.DUAL table.
See Note 1067733.6 to resolve this issue.
--OR--
Your replicated tables may not have a primary key or
alternate primary key
SEARCH WORDS:
=============
ORA-1422 replication dbms_defer_sys deftran deferror ORA-01422
SOLUTION DESCRIPTION:
======================
Because two rows may have different row IDs at different replication sites,
Oracle uses the primary key for a table to determine which rows to
compare. If you do not want to use the primary key for a table or if the
table does not have a primary key, you must use DBMS_REPCAT.SET_COLUMNS
procedure to define an alternate primary key.
SQL> describe sys.dbms_repcat.set_columns
PROCEDURE sys.dbms_repcat.set_columns
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SNAME VARCHAR2 IN
ONAME VARCHAR2 IN
COLUMN_LIST VARCHAR2 IN
Because this key is used to determine matching rows at different sites,
the columns that you specify in the COLUMN_LIST must result in a unique
identifier for the row. The procedure SET_COLUMNS will NOT enforce
uniqueness.
|
|||||||||||||||