周末有个加班，需要在一台sever上升级22个instance，数据库从9207升级到9208，在打一个CPUApr2010。在这里简单记录一下升级的过程和经验。 从这个架构来看，这个server上的数据库属于库小且多的类型，虽然每个库都很小，也就几个G，但是数量不少。所以在做9207升级到9208 […]
关于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 […]
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的操作系统版本。在正常安 […]
写正确了tnsnames文件却tnsping无法ping通
今天遇到个问题比较奇怪，tnsnames.ora明明配置正确，在其中写的IP是10.1.2.40，但是在tnsping中却解析到10.1.2.45：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
[oracle@si_adm02 admin]$ cat tnsnames.ora # TNSNAMES.ORA.SI_WWW01 Network Configuration File: /ora9i/app/oracle/product/9.2.0/network/admin/tnsnames.ora.si_www01 # Generated by Oracle configuration tools. EXTPROC_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC)) ) (CONNECT_DATA = (SID = PLSExtProc) (PRESENTATION = RO) ) ) dev2 = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.2.40)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME =misc1) ) ) [oracle@si_adm02 admin]$ |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
[oracle@si_adm02 admin]$ tnsping dev2 TNS Ping Utility for Linux: Version 9.2.0.4.0 - Production on 06-1Ղ -2010 15:03:15 Copyright (c) 1997 Oracle Corporation. All rights reserved. Used parameter files: /oracle/product/9.2.0/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.3.54)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ora9i))) OK (0 msec) [oracle@si_adm02 admin]$ |
而且我把tnsna […]
legato备份时不执行备份直接写index
今天某省的同事来找，说备份有问题。从log上看备份似乎成功了，但是在legato的界面中看还是一个红叉。于是登录上legato的界面再次测试了备份，发现monitor中日志如下：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
01/05/10 16:46:23 nsrd: savegroup info: starting OracleArch (with 1 client(s)) 01/05/10 16:46:42 savegrp: jx_db:/oracle/app/oracle/product/9.2.0/bin/oraclearch will retry 1 more time(s) 01/05/10 16:46:56 savegrp: jx_db:/oracle/app/oracle/product/9.2.0/bin/oraclearch will retry 0 more time(s) 01/05/10 16:46:56 nsrd: media info: suggest mounting 000700L2 on jx_bak01 for writing to pool 'index' 01/05/10 16:46:56 nsrd: media waiting event: Waiting for 1 writable volumes to backup pool 'index' tape(s) on jx_bak01 01/05/10 16:46:56 nsrd: media info: loading volume 000700L2 into \\.\Tape0 01/05/10 16:47:06 nsrmmd #13: Start nsrmmd #13, with PID 8020, at HOST jx_bak01 01/05/10 16:47:13 nsrd: \\.\Tape0 1:Verify label operation in progress 01/05/10 16:47:34 nsrd: \\.\Tape0 1:Mount operation in progress 01/05/10 16:48:00 nsrd: media event cleared: Waiting for 1 writable volumes to backup pool 'index' tape(s) on jx_bak01 01/05/10 16:48:00 nsrd: jx_bak01:index:jx_db saving to pool 'index' (000700L2) 01/05/10 16:48:01 nsrd: jx_bak01:index:jx_db done saving to pool 'index' (000700L2) 93 KB 01/05/10 16:48:06 nsrd: jx_bak01:bootstrap saving to pool 'index' (000700L2) 01/05/10 16:48:07 nsrmmdbd: media db is saving its data. This may take a while. 01/05/10 16:48:07 nsrmmdbd: media db is open for business. 01/05/10 16:48:08 nsrd: jx_bak01:bootstrap done saving to pool 'index' (000700L2) 2645 KB 01/05/10 16:48:15 nsrd: savegroup info: Added 'jx_bak01' to the group 'OracleArch' for bootstrap backup. 01/05/10 16:48:15 nsrd: savegroup alert: OracleArch completed, total 2 client(s), 0 Hostname(s) Unresolved, 1 Failed, 1 Succeeded. (jx_db Failed) |
从日志上看，legato的备份在没有进行rman的备份 […]
Legato备份还原测试
最近的一段时间，某省的备份出了点问题，legato的备份在还原时检查不到索引，通过scanner扫描之后还是检查不到，问题已经开case提交到emc那边，目前还在处理。为了防止万一，还是对全网用legato备份的省进行了一次还原测试。以下是还原测试中遇到的一些问题小结。 目前现网大部分机器都是采用h […]
9i不能实现silent安装
今天尝试在一个hp 安腾的机器上安装9202的数据库，并且升级到9206的版本，但是当安装的时候，配置好了response file，还是会有报错：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
$ ./runInstaller -silent -responseFile /opt/ora_soft/Disk1/response/IA_custom.rsp $ Initializing Java Virtual Machine from /tmp/OraInstall2009-12-11_04-43-32PM/jre/bin/java. Please wait... Exception in thread "main" java.lang.InternalError: Can't connect to X11 window server using '211.137.180.5:0.0' as the value of the DISPLAY variable. at sun.awt.X11GraphicsEnvironment.initDisplay(Native Method) at sun.awt.X11GraphicsEnvironment.<clinit>(X11GraphicsEnvironment.java:58) at java.lang.Class.forName0(Native Method) at java.lang.Class.forName(Unknown Source) at java.awt.GraphicsEnvironment.getLocalGraphicsEnvironment(GraphicsEnvironment.java:53) at java.awt.Window.<init>(Window.java:183) at java.awt.Frame.<init>(Frame.java:310) at java.awt.Frame.<init>(Frame.java:289) at oracle.ewt.popup.PopupFrame.<init>(Unknown Source) at oracle.ewt.lwAWT.BufferedFrame.<init>(Unknown Source) at oracle.sysman.oii.oiif.oiifm.OiifmMainFrame.<init>(OiifmMainFrame.java:296) at oracle.sysman.oii.oiic.OiicInstaller.<init>(OiicInstaller.java:231) at oracle.sysman.oii.oiic.OiicInstaller.main(OiicInstaller.java:599) $ |
从报错的log中，我们看到，虽然使用搜silent模式安装，但是还是去启动的xser […]
rac修改异步IO导致挂死
今天遇到个rac的异步IO的问题，环境是oracle 10201的双节点rac，存储使用ASM，机器是HP 的PA的机器，操作系统是11.23. 在udump下的日志中有大量的报错：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
oracle@rp8440a:/oracle/oracle/admin/gzxnh/bdump> cat gzxnh1_ora_19552.trc /oracle/oracle/admin/gzxnh/bdump/gzxnh1_ora_19552.trc Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP and Data Mining options ORACLE_HOME = /oracle/oracle/product/10.2.0/db_2 System name: HP-UX Node name: rp8440a Release: B.11.23 Version: U Machine: 9000/800 Instance name: gzxnh1 Redo thread mounted by this instance: 1 Oracle process number: 0 Unix process pid: 19552, image: oracle@rp8440a Ioctl ASYNC_CONFIG error, errno = 1 |
看到这个报错，觉得很轻松，之前也遇到过，是orac […]