有时候，我们没有xwindows的界面，需要在silent模式下创建数据库，我们可以用如下的方式创建。 下面的例子是以windows xp，oracle 10201版本。 修改安装目录中的response目录下的custom.rsp文件，可以copy一个后修改。 注意下面的#need change! […]
AQ导致不能drop user cascade
由于工程割接，在新数据库上测试imp一个用户需要多少时间，在把imp的报错慢慢修正后，再一次drop user进行重新导入，却发现不能drop user了：
|
1 2 3 4 5 6 |
SQL> drop user newsims cascade; drop user newsims cascade * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables |
但是在当前用户下查询user_query_tables会发现没有记录： [c […]
truncate异常中断后，发生DFS LOCK HANDLE
环境： oracle 9206 三节点rac。hp 11.11。 【10月29日，故障发生】 在一次truncate一个大表的过程中（以下操作均在节点1（gdrac1）上操作）：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
SQL> select sum(bytes)/1024/1024/1024 size_g from user_segments where segment_name='ALERTBUFF'; SIZE_G ---------- 329.8125 1* select tablespace_name,PARTITIONED from user_tables where table_name='ALERTBUFF' SQL> / TABLESPACE_NAME PAR ------------------------------ --- DATA_DYNAMIC NO SQL> select EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from user_tablespaces where tablespace_name='DATA_DYNAMIC'; EXTENT_MAN SEGMEN ---------- ------ LOCAL MANUAL SQL> select index_name,tablespace_name,PARTITIONED from user_indexes where table_name='ALERTBUFF'; INDEX_NAME TABLESPACE_NAME PAR ------------------------------ ------------------------------ --- IDX_ALERTBUFF_ALERTTIME DATA_DYNAMIC NO PK_ALERTBUFF DATA_DYNAMIC NO |
我们执行了truncate table xxx reu […]
DSI学习笔记
DSI的全称是DATA SERVER INTERNALS，是oracle为售后服务工程师进行的培训教程。 DSI含以下课程：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
DSI301:ADVANCED SERVER SUPPORT SKILLS DSI302:DATA MANAGEMENT DSI303:DATABASE BACKUP AND RECOVERY DSI304:QUERY MANAGEMENT DSI305:DATABASE TUNING DSI306:VERY LARGE DATABASES DSI307:DISTRIBUTION AND REPLICATION DSI308:PARALLEL SERVER DSI401E:ADVANCE SUPPORT SKILLS DSI401:DUMPS CRASHES AND CORRUPTIONS DSI402E:DATA TYPE AND BLOCK STRUCTURES DSI402:SPACE AND TRANSACTION MANAGEMENT DSI403E:RECOVERY ARCHITECTURE COMPONTS DSI404E:QUERY OPTIMIZER DSI405:INSTANCE TUNING DSI408:REAL APPLICATION CLUSTERS INTERNALS |
301笔记：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
设置event的方法： 1.通过初始化参数event="<event name> <action> [:<event name> <action> ]" 2.alter session set events '<event name> <action> [:<event name> <action> ]' 3.alter system set events '<event name> <action> [:<event name> <action> ]' 4.exec dbms_system.SET_EV(sid,serial#,event name,level,'action'); 5.oradebug <command>; 注：event="<event name> <action> [:<event name> <action> ]"的说明如下： 一.event name： （1）可以是一个event的名称，如果不是event的名称，就默认为immediate。（但是immediate关键字不能出现在初始化文件中） （2）可以是event的数字，如10046，10053，范围是10000至10999。每个event号码的含义后续会介绍。 二.当设定了event name后，action有可能有3种取值：crash、debugger、trace，其中trace我们比较常用。这里具体说说trace的这种用法： 指定trace时，语法为： <event name> "trace" "name" <trace name> <trace qualifier>[,<trace qualifier>]... 这里<trace name>是一个内部的trace 标记，如context。 这里<trace qualifier>可以是forever、off、level n，一般情况下n是trace的级别，n越高级别越高；但是如果是dump数据块的时候，n指dba（data block address，数据块的地址）。 综上，可以写event="10046 trace name context forever:10053 trace name context forever,level 10" 注意，当初始化文件中有2个event="xxxxx"，以后面一个为准，前面一个失效。 |
event的种类： [cra […]
即将参加Oracle 11g产品升级及应用体验大会
这个月底，我将参加在深圳的oracle的11g产品升级及应用体验大会。 该会议是由Oracle举办，AMD/Novell公司协办，是专门针对ISVs 的应用升级/移植的活动。Oracle 公司资深技术人员将解答有关升级过程中的疑难问题，同时还可以和两个厂家资深技术专家共同探讨最新产品的技术特点。 不 […]
十一游香江野生动物园
十一期间，陪琪琪回了趟东莞，去见了琪琪的父母。琪琪的妈妈很热心，爸爸也很好。琪琪家的教育方式和我家有很大不同，琪琪比较独立，父母对她都很放心。对我也比较信任，呵呵。在拜访了琪琪的父母之后，我们一起去了香江野生动物园。里面10只大熊猫，100多只白虎，13只考拉，460余种2万余只珍奇动物野生动物，确 […]
一次悬挂事务的处理思路
前几天看技术通信的时候，看到一篇《一次悬挂事务处理的全过程》，觉得分析的很好，和大家来分享下。其中具体的过程就不细说了，主要介绍下思路。 问题是这样的，某数据库新建了一个undotbs2，准备将系统默认的undotbs1（已经使用了2年了）切到undotbs2中。但是很长时间undotbs1中仍然有 […]
hash join 读书笔记
hash join必须使用cbo（因此必须表分析）
|
1 2 3 |
NLJ： 根据连接键，把小表的每一行，和大笔的每一行做对比。 一般情况下会对大表连接键上建index。 成本计算：读小表的行+（小表的每一行×读取大表的行） |
|
1 2 3 4 5 6 7 8 9 |
SMJ: 读取小表和大表读的行，根据连接键排序，然后根据排序后的数据集（小表的和大表的）合进行连接。 理想状态：2个表的排序操作都能在内存进行 常规情况：2阶段进行： 1.sort run阶段：数据读取到内存，排序，写出到临时表空间。直到所有的row sourse完成排序。 2.merge阶段：之前每次写到临时表空间的数据（即sort run）被重新读入到内存，进行merge。 成本计算：读取小表的行+写小表的run sort到temp表空间+ 读取大表的行+写大表的run sort到temp表空间+ cpu对小表和大表的排序消耗 |
|
1 2 |
join连接中的并行机制： 能在NLJ和SMJ中使用。并发查询的执行计划是一个树形结构（DFO），每个树上的DFO节点是一个sql操作过程，并且能把该操作过程能指派到一个query slave进程中。 |
[crayon-68cb80e7ae4b41854 […]
遭遇ora-600[20022]
在近期进行十一数据库巡检的时候，某省的同事发现在数据库的alertlog中有关于ora-600的报错：
|
1 2 3 4 5 6 7 |
Sun Sep 21 19:20:52 2008 Errors in file /oracle/app/oracle/admin/hbmisc/udump/hbmisc_ora_13668.trc: ORA-00600: internal error code, arguments: [20022], [2], [], [], [], [], [], [] Sun Sep 21 19:21:15 2008 Errors in file /oracle/app/oracle/admin/hbmisc/udump/hbmisc_ora_13072.trc: ORA-00600: internal error code, arguments: [20022], [2], [], [], [], [], [], [] Sun Sep 21 19:38:09 2008 |
进一步查看了trace文件，发现是一个sql有关：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
*** SESSION ID:(172.1955) 2008-09-21 19:20:52.752 *** 2008-09-21 19:20:52.752 ksedmp: internal or fatal error ORA-00600: internal error code, arguments: [20022], [2], [], [], [], [], [], [] Current SQL statement for this session: select count(*) into :b0 from (select icpcode ,icpservid ,a.subsid subsid1 ,to_char(startdate,'YYYYMMDDHH24MISS') startdatetime ,to_char(enddate,'YYYYMMDDHH24MISS') enddatetime ,tab_a.status status1 ,b.subsid subsid2 ,decode(nvl(d.status,'A'),'A',0,2) status2 ,tab_a.visitcount visitcount from tab_a ,tab_b a ,icp ,service ,tab_b b ,tab_c d where (((((((nvl(tab_a.chargemid,tab_a.mid)=a.mid and tab_a.icpid=icp.icpid) and tab_a.servid=service.servid) and tab_a.status<>'C') and (tab_a.mid=:b1 or tab_a.chargemid=:b1)) and tab_a.mid=b.mid) and tab_a.mid=d.mid(+)) and tab_a.servid=d.servid(+)) union select icpcode ,icpservid ,a.subsid subsid1 ,to_char(startdate,'YYYYMMDDHH24MISS') startdatetime ,to_char(startdate,'YYYYMMDDHH24MISS') enddatetime ,'A' status1 ,a.subsid subsid2 ,1 status2 ,tab_a_freeuse.visitcount visitcount from tab_a_freeuse ,tab_b a ,icp ,service where (((tab_a_freeuse.icpid=icp.icpid and tab_a_freeuse.servid=service.servid) and tab_a_freeuse.mid=:b1) and tab_a_freeuse.mid=a.mid)) ----- Call Stack Trace ----- calling call entry argument values in hex location type point (? means dubious value) -------------------- -------- -------------------- ---------------------------- ksedmp()+184 ? ksedst() 40000000017F5B37 ? 000000032 ? 800003FAC0006570 ? 000000000 ? …… |
为什么一个s […]
新版metalink，你觉得好用么？
本来也是不知道今天metalink会换脸的，只是下午遇到过ora-600 [2022] 的问题的时候，登录了metalink准备查资料，发现login的界面已经换了，默认是登录新版的metalink。当然，你也可以选择classic登录老版的metakink。 对于这个变化，好多dba朋友已经感受到 […]
Google Chrome遭遇SPA崩溃
昨天晚上google的浏览器Chrome 放出来了，兴冲冲的下载来试了一下，发现打开就立即崩溃： 后来在公司的论坛上看到高人指点，原来是和公司的网络终端软件SPA（Symantec Protection Agent）有冲突。用下面的方法即可解决： [crayon-68cb80e7b051252872 […]
谨慎的使用shutdown abort
今天在重启一个库的时候，由于等了超过半小时，仍然没有完成数据库的close，于是就用shutdown abort命令关闭数据库。但是在起来的时候，发现在alertlog中有大量的SMON的报错，而且还在持续不断的报错出来。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 |
<--- 正常的启动信息 begin here ---> Mon Sep 1 16:32:02 2008 Starting ORACLE instance (normal) LICENSE_MAX_SESSION = 0 LICENSE_SESSIONS_WARNING = 0 SCN scheme 1 Using log_archive_dest parameter default value LICENSE_MAX_USERS = 0 SYS auditing is disabled Starting up ORACLE RDBMS Version: 9.2.0.6.0. System parameters with non-default values: processes = 1200 timed_statistics = TRUE shared_pool_size = 419430400 sga_max_size = 2108652208 large_pool_size = 117440512 java_pool_size = 117440512 spfile = /dev/vg_ora01/rspfile_128m_01 control_files = /dev/vg_ora01/rctrl_128m_01, /dev/vg_ora02/rctrl_128m_02 db_block_size = 8192 db_cache_size = 1258291200 compatible = 9.2.0.0.0 log_archive_start = TRUE log_archive_dest_1 = location=/arch log_archive_format = arch_%t_%s.arc log_buffer = 10485760 db_files = 800 db_file_multiblock_read_count= 16 fast_start_mttr_target = 300 undo_management = AUTO undo_tablespace = UNDOTBS1 undo_suppress_errors = TRUE undo_retention = 10800 remote_login_passwordfile= EXCLUSIVE db_domain = instance_name = gdmocs job_queue_processes = 6 hash_join_enabled = TRUE background_dump_dest = /oracle/app/oracle/admin/gdmocs/bdump user_dump_dest = /oracle/app/oracle/admin/gdmocs/udump core_dump_dest = /oracle/app/oracle/admin/gdmocs/cdump sort_area_size = 524288 db_name = gdmocs open_cursors = 500 star_transformation_enabled= FALSE query_rewrite_enabled = TRUE pga_aggregate_target = 524288000 PMON started with pid=2 DBW0 started with pid=3 LGWR started with pid=4 CKPT started with pid=5 SMON started with pid=6 RECO started with pid=7 CJQ0 started with pid=8 Mon Sep 1 16:32:03 2008 ARCH: STARTING ARCH PROCESSES ARC0 started with pid=9 ARC0: Archival started ARC1 started with pid=10 ARC1: Archival started Mon Sep 1 16:32:03 2008 ARCH: STARTING ARCH PROCESSES COMPLETE Mon Sep 1 16:32:03 2008 ARC1: Thread not mounted Mon Sep 1 16:32:03 2008 ARC0: Thread not mounted Mon Sep 1 16:32:03 2008 ALTER DATABASE MOUNT Mon Sep 1 16:32:07 2008 Successful mount of redo thread 1, with mount id 2193310019 Mon Sep 1 16:32:07 2008 Database mounted in Exclusive Mode. Completed: ALTER DATABASE MOUNT Mon Sep 1 16:32:07 2008 <--- 正常的启动信息 end here ---> <--- 开始打开数据，发现需要做实例恢复 begin here ---> ALTER DATABASE OPEN Mon Sep 1 16:32:08 2008 Beginning crash recovery of 1 threads Mon Sep 1 16:32:08 2008 Started redo scan Mon Sep 1 16:32:09 2008 Completed redo scan 27274 redo blocks read, 46702 data blocks need recovery Mon Sep 1 16:35:20 2008 <--- 开始打开数据，发现需要做实例恢复 end ---> <--- 开始实例恢复 ---> Started recovery at Thread 1: logseq 24462, block 231548, scn 0.0 Mon Sep 1 16:35:20 2008 Recovery of Online Redo Log: Thread 1 Group 1 Seq 24462 Reading mem 0 Mem# 0 errs 0: /dev/vg_ora01/rredo_256m_01 Mem# 1 errs 0: /dev/vg_ora02/rredo_256m_11 Mon Sep 1 16:35:22 2008 Completed redo application Mon Sep 1 16:35:32 2008 Ended recovery at Thread 1: logseq 24462, block 258822, scn 13.3684259989 46702 data blocks read, 46201 data blocks written, 27274 redo blocks read Crash recovery completed successfully Mon Sep 1 16:35:33 2008 LGWR: Primary database is in CLUSTER CONSISTENT mode Thread 1 advanced to log sequence 24463 Thread 1 opened at log sequence 24463 Current log# 3 seq# 24463 mem# 0: /dev/vg_ora01/rredo_256m_03 Current log# 3 seq# 24463 mem# 1: /dev/vg_ora02/rredo_256m_13 Successful open of redo thread 1 Mon Sep 1 16:35:33 2008 <--- 开始前滚 ---> SMON: enabling cache recovery Mon Sep 1 16:35:33 2008 ARC0: Evaluating archive log 1 thread 1 sequence 24462 ARC0: Beginning to archive log 1 thread 1 sequence 24462 Creating archive destination LOG_ARCHIVE_DEST_1: '/arch/arch_1_24462.arc' Mon Sep 1 16:35:34 2008 Successfully onlined Undo Tablespace 1. Mon Sep 1 16:35:34 2008 <--- 开始回滚 ---> SMON: enabling tx recovery Mon Sep 1 16:35:34 2008 Database Characterset is ZHS16GBK Mon Sep 1 16:35:34 2008 <--- 开始出现大量的SMON报错 ---> SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available Mon Sep 1 16:35:35 2008 replication_dependency_tracking turned off (no async multimaster replication found) Mon Sep 1 16:35:35 2008 SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available <--- 实例恢复完成，数据库open ---> Mon Sep 1 16:35:35 2008 Completed: ALTER DATABASE OPEN Mon Sep 1 16:35:35 2008 SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available SMON: about to recover undo segment 60 SMON: mark undo segment 60 as available |
此时，数据 […]