公司在招dba，哪位有兴趣的，可以发简历给我jimmyhe1981[at]gmail.com。 公司名称：卓望数码技术(深圳)有限公司，主要股东为中国移动、HP、沃达丰、美林证券 地点：深圳南山区科技园 人数：1人 截至时间：2010年6月11日 岗位职责：专职、全网、维护dba，中国移动31省数据 […]
关于shutdown的时候存在active进程的问题
oracle在shutdown immediate的时候，如果遇到活动的事务，比如job还在跑，那么该shutdown操作就会挂起，直到job完成为止。我们在alertlog中可以看到：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
ARC0: Completed archiving log 2 thread 1 sequence 68549 Thu May 20 01:18:52 2010 Shutting down instance: further logons disabled Shutting down instance (immediate) License high water mark = 530 Thu May 20 01:23:59 2010 Active call for process 6622 user 'oracle' program 'oracle@my_dc01 (J000)' Active call for process 6246 user 'oracle' program 'oracle@my_dc01 (TNS V1-V3)' SHUTDOWN: waiting for active calls to complete. Thu May 20 02:13:28 2010 Thread 1 advanced to log sequence 68551 Current log# 1 seq# 68551 mem# 0: /dev/vg_ora1/rredo_256m_01 Current log# 1 seq# 68551 mem# 1: /dev/vg_ora2/rredo_256m_11 Current log# 1 seq# 68551 mem# 2: /dev/vg_ora3/rredo_256m_21 Thu May 20 02:13:28 2010 ARC0: Evaluating archive log 3 thread 1 sequence 68550 ARC0: Beginning to archive log 3 thread 1 sequence 68550 Creating archive destination LOG_ARCHIVE_DEST_1: '/oraarchlog/arch_1_68550.arc' ARC0: Completed archiving log 3 thread 1 sequence 68550 |
如果在一小时内，job没有跑完，那么该shut […]
ocm考试-调优包的使用
摘要：本文主要分2部分，一是介绍statspack，这个主要是为了在10g中延续9i的使用习惯。二是介绍awr、addm、ash报告的生成。这是在10g中常用的调优方法，可以在gc或者dbconsole界面中点击查看，也可以用命令行在生成。生成这些报告不难，难的是分析这些报告。 一、statspac […]
ocm考试-grid control中的job system
grid control中的job system在gc界面中首页的job菜单中，这个job可以建立很多类型的job，用途远远大于db control中的job或者schedual，在这里我们简单模拟几个常用的job的建立。（注意本文多图杀猫） 一、写在前面： 1、gc的登录用户，不能用一般的crea […]
OCM考试-安装Grid Control
ocm考试的重头戏之一，是安装grid control（以下简称gc）。 gc的安装分成3步骤：
|
1 2 3 |
1、在奇数机建立repository database 2、在偶数机安装gc 3、在奇数机安装agent |
根据考试要求，gc的建立是using a exsit database作为repository database的。因此，题目会要求建立 […]
ocm考试小贴士
（1）图形化界面中比如做gc、rac，一定要检查之后再开始运行，因为后续很少时间给你排错。尽量在一次之内就顺利安装。 （2）如果gc来不及，或者在长时间的等待config，而gc后面的有些题题可以用手工命令做，可以不用gc，以节约时间。 （3）虽然可以用gc做，但是万一要是gc不行，可以用手工做，冷 […]
rac中修改sys密码
sys是数据库库中权限最高的用户，在登录时，我们可以用os认证的方式直接登录，也可以利用sqlplus “sys/your_password@your_tnsnames as sysdba”来登录。特别是对于后面的这种远程登录，在第三方的备份备份软件中需要配置。因此这个文件对 […]
hp开启异步IO后仿佛没生效
按理说，在正常情况下，hp主机开启了异步IO之后，主机的wio会有较大幅度的下降，在我们的系统中，wio一般会下降到10%以下。但是最近遇到的情况就非常奇怪，开启异步IO之后，观察wio却仍然很高，在30%～50%，业务忙时cpu idle在个位数。仿佛和没开wio一样。 检查的时候发现，相关参数都 […]
解决Legato问题，相信LTT吧！
最近遇到某省的legato备份问题，这个问题在春节后一来就遇到了。在legato界面，我们看到的报错是：
|
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 |
…… 02/24/10 12:01:00 nsrd: savegroup info: starting db_arch (with 1 client(s)) 02/24/10 12:01:15 nsrd: Jukebox 'msl6000' failed: Input/output error 02/24/10 12:01:15 nsrd: Jukebox 'msl6000' failed: Input/output error 02/24/10 12:01:53 nsrd: Jukebox 'msl6000' failed: No such file or directory 02/24/10 12:01:53 nsrd: Jukebox 'msl6000' failed: No such file or directory 02/24/10 12:01:54 nsrd: media info: suggest mounting QWB507L2 on ah-db01 for writing to pool 'miscdb' 02/24/10 12:01:54 nsrd: media waiting event: Waiting for 1 writable volumes to backup pool 'miscdb' tape(s) on ah-db01 02/24/10 12:01:56 nsrd: Jukebox 'msl6000' failed: No such file or directory 02/24/10 12:01:56 nsrd: Jukebox 'msl6000' failed: No such file or directory 02/24/10 12:02:05 nsrd: Jukebox 'msl6000' failed: Input/output error 02/24/10 12:02:05 nsrd: Jukebox 'msl6000' failed: Input/output error 02/24/10 12:02:14 nsrd: Jukebox 'msl6000' failed: Input/output error 02/24/10 12:02:14 nsrd: Jukebox 'msl6000' failed: Input/output error 02/24/10 12:02:21 nsrd: Jukebox 'msl6000' failed: Input/output error 02/24/10 12:02:21 nsrd: Jukebox 'msl6000' failed: Input/output error 02/24/10 12:02:40 nsrd: Jukebox 'msl6000' failed: Input/output error 02/24/10 12:02:40 nsrd: Jukebox 'msl6000' failed: Input/output error 02/24/10 12:02:44 nsrd: shutting down remote daemons 02/24/10 12:02:45 nsrd: Jukebox 'msl6000' failed: Input/output error 02/24/10 12:02:45 nsrd: Jukebox 'msl6000' failed: Input/output error 02/24/10 12:02:45 nsrd: media info: Suggest manually labeling a new writable volume for pool 'dbreport' 02/24/10 12:02:45 nsrd: media event cleared: Waiting for 1 writable volumes to backup pool 'miscdb' tape(s) on ah-db01 02/24/10 12:02:45 nsrd: media waiting event: Waiting for 1 writable volumes to backup pool 'miscdb' tape(s) on ah_bak01 02/24/10 12:02:53 savegrp: group db_arch aborted. …… |
基本看到的情况是在备份的时候，开始做verify label的时候，报错No such file or directory和In […]
操作系统内核参数限制导致ora-4030
最近同事遇到一个问题，某个报表程序报错ora-4030的错误：
|
1 |
错误信息：[Error] **** Dbupdate.sqlUpdateSubs Function:begin ? := trt_subs_1_i_update_target (?, ?, ?, ?, ?); end;, Execute GD:1li run finished with error, errcode:-4030,errmsg:ORA-04030: out of process memory when trying to allocate 16408 bytes (koh-kghu call ,pmucalm coll),errlog:GD_1li TableA_S.(SCS or ADS) Update TableP_S, circle= 2. |
通过trace，发现是以下语句的问题：
|
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 |
declare type subs_mobile_no_arr is table of trt_subs_GD_1li_wap_S.subs_mobile_no%type index by pls_integer; type icp_id_arr is table of trt_subs_GD_1li_wap_S.icp_id%type index by pls_integer; type icp_code_arr is table of trt_subs_GD_1li_wap_S.icp_code%type index by pls_integer; type channel_id_arr is table of trt_subs_GD_1li_wap_S.channel_id%type index by pls_integer; type serv_id_arr is table of trt_subs_GD_1li_wap_S.serv_id%type index by pls_integer; type brand_id_arr is table of trt_subs_GD_1li_wap_S.brand_id%type index by pls_integer; type fee_mobile_no_arr is table of trt_subs_GD_1li_wap_S.fee_mobile_no%type index by pls_integer; type action_id_arr is table of trt_subs_GD_1li_wap_S.action_id%type index by pls_integer; type crt_his_date_arr is table of trt_subs_GD_1li_wap_S.crt_his_date%type index by pls_integer; type city_id_arr is table of trt_subs_GD_1li_wap_S.city_id%type index by pls_integer; type attr_arr is table of trt_subs_GD_1li_wap_S.attr%type index by pls_integer; type srh_channel_id_arr is table of trt_subs_GD_1li_wap_S.srh_channel_id%type index by pls_integer; arr_subs_mobile_no subs_mobile_no_arr; arr_icp_id icp_id_arr; arr_icp_code icp_code_arr; arr_channel_id channel_id_arr; arr_serv_id serv_id_arr; arr_brand_id brand_id_arr; arr_fee_mobile_no fee_mobile_no_arr; arr_action_id action_id_arr; arr_crt_his_date crt_his_date_arr; arr_city_id city_id_arr; arr_attr attr_arr; arr_srh_channel_id srh_channel_id_arr; begin select subs_mobile_no, icp_id, icp_code, channel_id, serv_id, brand_id, fee_mobile_no, decode(action_id, 'SCS', 'C', 'ADS', 'D') action_id, crt_his_date, city_id, attr, srh_channel_id bulk collect into arr_subs_mobile_no, arr_icp_id, arr_icp_code, arr_channel_id, arr_serv_id, arr_brand_id, arr_fee_mobile_no, arr_action_id, arr_crt_his_date, arr_city_id, arr_attr, arr_srh_channel_id from trt_subs_GD_1li_wap_S where action_id = 'SCS' or action_id = 'ADS'; if arr_subs_mobile_no.last > 0 then arr_srh_channel_id srh_channel_id_arr; begin select subs_mobile_no, icp_id, icp_code, channel_id, serv_id, brand_id, fee_mobile_no, decode(action_id, 'SCS', 'C', 'ADS', 'D') action_id, crt_his_date, city_id, attr, srh_channel_id bulk collect into arr_subs_mobile_no, arr_icp_id, arr_icp_code, arr_channel_id, arr_serv_id, arr_brand_id, arr_fee_mobile_no, arr_action_id, arr_crt_his_date, arr_city_id, arr_attr, arr_srh_channel_id from trt_subs_GD_1li_wap_S where action_id = 'SCS' or action_id = 'ADS'; if arr_subs_mobile_no.last > 0 then forall i in arr_subs_mobile_no.first .. arr_subs_mobile_no.last update f_wap_subs_GD_p_s c set c.city_id = arr_city_id(i), c.icp_id = arr_icp_id(i), c.icp_code = arr_icp_code(i), c.subs_cancel_channel_id = arr_channel_id(i), c.brand_id = arr_brand_id(i), c.subs_status_id = arr_action_id(i), c.fee_mobile_no = arr_fee_mobile_no(i), c.usubs_date = arr_crt_his_date(i), c.usubs_day = to_char(arr_crt_his_date(i), 'YYYYMMDD'), c.idx_day = to_char(arr_crt_his_date(i), 'YYYYMMDD'), c.is_subs_valid = 0, c.flow_time = sysdate, c.srh_channel_id = arr_srh_channel_id(i) where subs_mobile_no = arr_subs_mobile_no(i) and serv_id = arr_serv_id(i) and is_subs_valid = 1; end if; end; |
这个sql在执行的时候，pga的内存被迅速的消耗，通过v$ses […]
新版本的opatch使用lsinventory报错
今天在给一个省打一个9208侦听内存泄漏的patch，打的时候，没有报错，但是在opatch lsinventory的时候，却报错了：
|
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 |
oracle@tj_dc01:/oracle/app/oracle/product/9.2.0 > opatch lsinventory Oracle Interim Patch Installer version 1.0.0.0.63 Copyright (c) 2009 Oracle Corporation. All Rights Reserved.. Oracle recommends you to use the latest OPatch version and read the OPatch documentation available in the OPatch/docs directory for usage. For information about the latest OPatch and other support-related issues, refer to document ID 293369.1 available on My Oracle Support (https://myoraclesupport.oracle.com) Oracle Home : /oracle/app/oracle/product/9.2.0 Oracle Home Inventory : /oracle/app/oracle/oraInventory Central Inventory : /oracle/app/oracle/oraInventory from : /var/opt/oracle/oraInst.loc OUI location : /oracle/app/oracle/oui OUI shared library : /oracle/app/oracle/oui/bin/hpunix/liboraInstaller.so Java location : /oracle/app/oracle/jre/1.3.1/bin/java Log file location : /oracle/app/oracle/product/9.2.0/.patch_storage/<patch ID>/*.log Creating log file "/oracle/app/oracle/product/9.2.0/.patch_storage/LsInventory__01-25-2010_16-25-28.log" Exception in thread "main" java.lang.NoClassDefFoundError: javax/xml/transform/Source at opatch.LsInventory.getOneOffList(LsInventory.java:675) at opatch.LsInventory.main(LsInventory.java:247) Result: There is no Interim Patch OPatch succeeded. |
这个opatch是从metalink上下载的最新的opatch，而且也是对应了HP IA的os平台 […]
在hp pa 11.23上安装9206patch
hp pa算是一个比较老版本的hpux操作系统，目前hp已经不出hp pa的版本，只出hp ia的版本。目前ia的os版本为11.23和11.31，在之前的hp pa的主要os版本是11.00和11.11。 这次在某省安装的机器比较奇怪，是hp pa的机器，却是安装11.23的操作系统版本。在正常安 […]