ITPub博客

请访问：http://www.qiuyb.com

看惯Oracle 10g的crs_stat -t的输出，11gR2相应的输出真是太多太乱，需精简一下。[@more@]实测如下资源精简掉是木有问题的[root@racdb1 ~]# crsctl delete type ora.registry.acfs.type[root@racdb1 ~]# crsctl delete resource ora.ons[root@racdb1 ~]# crsctl delete resource ora.eons[root@racdb1 ~]# crsctl delete resource ora.oc4j[root@racdb1 ~]# crsctl delete resource ora.racdb.gsdCRS-2721: ‘ora.racdb.gsd’ is a resource alias for ‘ora.gsd’ and cannot be unregisteredCRS-4000: Command Delete failed, or comple

just as title.[@more@]impdp qiuyb/qiuyb network_link=source_qiuyb schemas="HR" remap_schema="HR:QIUYB" nologfile=y parallel=8 REMAP_TABLESPACE="TBS_DATA1:HR_DATA1,TBS_DATA2:HR_DATA2" EXCLUDE=="CONSTRAINT,GRANT,INDEX,STATISTICS"

这是11g 的新特性之一,可以很好的减化DBA的工作。[@more@]主要操作步骤如下：RMAN> validate database; <-- 为了减少健康监视器的延迟，可以运行这个命令让系统立刻找到这个failureRMAN> list failure detail; <-- 列出数据库中遭遇的故障RMAN> advise failure all; <-- 提供解决故障的自动和手动修复方案RMAN> repair failure; <-- 执行修复的动作RMAN> list failure all; <-- 再次检查是否还存在故障

实际工作中经常出现SQL执行计划总变的状况,在9i及之前版本常用OUTLINE稳定执行计划，10G后可以使用更优的sql profile[@more@]1、获取某一sql_id的OUTLINESQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('ct47hsv47278w', 0, 'outline'));这里只贴出了outline的部分，其余略Outline Data-------------/*+BEGIN_OUTLINE_DATAIGNORE_OPTIM_EMBEDDED_HINTSOPTIMIZER_FEATURES_ENABLE('10.2.0.5')ALL_ROWSOUTLINE_LEAF(@"SEL$1")INDEX_RS_ASC(@"SEL$1" "A"@"SEL$1" "IDX_TF_B_PAYLOG_1&

官方文档未有这方面设置，本设置取自于最佳实践[@more@]1、永久生效请将如下几行添加到/etc/rc.config.d/nddconf文件中TRANSPORT_NAME[0]=socketsNDD_NAME[0]=socket_udp_rcvbuf_defaultNDD_VALUE[0]=1048576TRANSPORT_NAME[1]=socketsNDD_NAME[1]=socket_udp_sndbuf_defaultNDD_VALUE[1]=65535TRANSPORT_NAME[2]=socketsNDD_NAME[2]=socket_buf_maxNDD_VALUE[2]=10485762、临时生效ndd -set /dev/sockets socket_udp_rcvbuf_default 1048576ndd -set /dev/sockets socket_udp_sndbuf_default 65535ndd -set /dev/sockets socket_buf_max

记录常用操作[@more@]1、设置cleaning tape清洗次数方法设置某一槽位为清洗槽位nsrjb -d -S <slot number>eg:nsrjb -d -S 260设置可用清洗次数,例如50次nsrjb -U 50 -S 2602、磁带换槽位将磁带从292号弹出到1号邮箱位nsrjb -j IBM@2.1.1 -w -S 292-292 -P 1-1将磁带从邮箱位1号位加载到300槽位nsrjb -j IBM@2.1.1 -d -P 1-1 -S 300-300

收集统计信息的目的是为了使执行计划准确，有时统计信息收集后执行计划发生了改变，我们期待回归之前的执行计划。ORACLE提供了相应的过程可以回退到之前的统计信息，进而执行计划也会回退。[@more@]1、查一下某张表统计信息的历史SQL> SELECT OWNER,2 TABLE_NAME,3 TO_CHAR(STATS_UPDATE_TIME, 'YYYYMMDD HH24:MI:SS')4 FROM DBA_TAB_STATS_HISTORY5 WHERE OWNER = 'UCR_ACT4'6 AND TABLE_NAME = 'TI_O_CREDIT_WORK'7 ORDER BY 38 /OWNER TABLE_NAME TO_CHAR(STATS_UPDATE_TIME,'YYY------------------------------ ------------------

原文作者JaneZhang(张建英)当数据库出现严重的性能问题或者hang了的时候，我们非常需要通过systemstate dump来知道进程在做什么，在等待什么，谁是资源的持有者，谁阻塞了别人。在出现上述问题时，及时收集systemstate dump非常有助于问题原因的分析。在一些情况下，数据库会自动生成systemstate dump, 比如出现了"WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK"[@more@]systemstate dump大部分时候需要手工生成，具体的命令为：如果连接很多，比如几千个连接，那么生成dump可能需要几十分钟，而且会占用几百M磁盘空间）1. 用s

在早期版本以裸设备做数据文件会有4k头部偏移量，严重情况会引起坏块[@more@]1、检查eg:lslv data_001.crm|grep DEVICESUBTYPEDEVICESUBTYPE : DS_LVZ看类型是不是DS_LVZ或dbfsize /dev/rdata_001.crm如表有Database file type: raw device without 4K starting offset这样的提示也表明是没有4k头部偏移的2、AIX卷组类型说明<1>Big VG,如果我们指定-T O，则创建DS_LVZ类型的LV，否则，创建普通类型的LV<2>普通的VG，用什么命令创建,都是普通的DS_LV类型的LV<3>calable-typeVG，用什么方式的命令创建,都是DS_LVZ类型的LV

Oracle exp导出带有LOB字段表时出现ORA-01555和ORA-22924报错，给出解决方法。[@more@]1、问题描述环境为aix5.3系统，oracle9.2.0.1的库，库内rhxt用户有一rk_zp表,这张表zp字段是blob型的，这张表43GB,每次exp至几个G时就报ORA-01555和ORA-22924错了。2、问题解决通过实际的测试及查MOS，发现这是因为blob某些行有问题触发了oracle BUG官方给的解决方式如下：<1>建一张临时表存储所有corrupted LOBs行的rowid.SQL> create table corrupt_lobs (corrupt_rowid rowid, err_num number); <2>运行如下PLSQL block:declareerro

Oracle自动收集统计信息JOB总是失败，原因大都因为数据量较大,在预计的时间窗口内统计信息未收集完毕。[@more@]1、查看JOB运行状态SELECT LOG_ID, JOB_NAME, STATUS, ADDITIONAL_INFO FROM DBA_SCHEDULER_JOB_LOG WHERE JOB_NAME = 'GATHER_STATS_JOB' ORDER BY 1ADDITIONAL_INFO原因基本为：REASON="Stop job called because associated window was closed"2、查看下dbms_stats默认的参数设定SELECT SNAME AS PARAMETER, NVL(SPARE4, SVAL1) AS DEFAULT_VALUE FROM SYS.OPTSTAT_HIST_CONTROL$3、用如下命令可以调整一下参数设

For troubleshooting ORA-29740 errors, the 'reason' will be very important.In the above example, the first section indicates the reason for theinitiated reconfiguration. The reasons are as follows:[@more@]Reason:0 = No reconfigurationReason1 = The Node Monitor generated the reconfiguration.Reason2 = An instance death was detected.Reason3 = Communications FailureReason4 = Reconfiguration after suspend

Enqueue Waits[@more@]文章来源于:http://dbpedias.com/wiki/Oracle:Wait_Events_-_Enqueue_WaitsEnqueues are local locks that serialize access to various resources. This wait event indicates a wait for a lock that is held by another session (or sessions) in an incompatible mode to the requested mode. The table below lists the details of which lock modes are compatible with which. Enqueues are usually represented in the format "TYPE-ID1-ID2" where: "TYPE" is a 2 character text stri

introduce how to config raw devices[@more@]1、/etc/sysconfig/rawdevices：文件中加入类似如下行/dev/raw/raw1 /dev/sdb1/dev/raw/raw2 /dev/sdc12、重启rawdevices服务，生成相应设备文件/sbin/service rawdevices restart3、改权限chown oracle:dba /dev/raw/*chmod 660 /dev/raw/*4、永久使权限生效较普遍方式：修改/etc/udev/permissions.d/50-udev.permissions文件把raw/*:root:disk:0660改为：raw/*:oracle:dba:0660如下所示：# raw devices ram*:root:disk:0660 #raw/*:root:disk:0660raw/*:oracle:dba:0660RHEL5:编辑/etc/udev

just as title[@more@]shutdown:srvctl stop asm -n raclinux1 -o normal:immediate:transactional:abortexport ORACLE_SID=+ASM1export ORACLE_HOME=$ASM_HOME (in 11gR1)export ORACLE_HOME=$ORA_GRID_HOME (in 11gR2)sqlplus / as sysasmSQL> SHUTDOWN NORMAL:IMMEDIATE:TRANSACTIONAL:ABORTstartup:srvctl start asm -n raclinux1 -o nomount:mount:restrictexport ORACLE_SID=+ASM1export ORACLE_HOME=$ASM_HOME (in 11gR1)export ORACLE_HOME=$ORA_GRID_HOME (in 11gR2)sqlplus / as sysasmSQL> STARTUP NOMOUNT:MOUNT:RESTRI

scn最大值为281474976710656[@more@]16进制的ffffffffffff按每秒16k的速度至少可用于500年

取自：http://www.oracle-base.com/articles/10g/automatic-sql-tuning-10g.php[@more@]Automatic SQL Tuning in Oracle Database 10g1、授相应用户权限CONN sys/password AS SYSDBAGRANT ADVISOR TO scott;CONN scott/tiger2、建立taskSET SERVEROUTPUT ON-- Tuning task created for specific a statement from the AWR.DECLARE l_sql_tune_task_id VARCHAR2(100);BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( begin_snap => 764, end_snap => 938,

Oracle 10g All versions.[@more@]SQL> ALTER SESSION SET sql_trace=TRUE;SQL> ALTER SESSION SET sql_trace=FALSE;SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => TRUE);SQL> EXEC DBMS_SESSION.set_sql_trace(sql_trace => FALSE);SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 8';SQL> ALTER SESSION SET EVENTS '10046 trace name context off';SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);SQL> EXEC DBMS

转载索引，判端是不是根块[@more@]Once we get the object_id from dba_segments, here's how to dump an index:1、dump indexalter session set events 'immediate trace name treedump level :ObjectId';...----- begin tree dumpbranch: 0x3ec4d5f4 1053087220 (0: nrow: 5, level: 2)branch: 0x58515106 1481724166 (-1: nrow: 491, level: 1)leaf: 0x3ec4d5f5 1053087221 (-1: nrow: 46 rrow: 46)leaf: 0x3ec4d5f6 1053087222 (0: nrow: 45 rrow: 45)leaf: 0x3ec4d5f7 1053087223 (1: nrow: 44 rrow: 44)leaf: 0x3ec4d5f8 1053087224