最近的工作中总结了一些小技巧，利用这些小技巧，可以在平时的工作中促进效率，但是其实这些小技巧说实在话，还是对数据库的安全性有一定的影响。 1.在9i中，或者说在8i，9i，10gR1中，dblink的用户密码被以明文的形式保留在link$表中： [crayon-678c642d290c2086930 […]
exp full报错SYS.DBMS_DEFER_IMPORT_INTERNAL
今天在进行一个全库exp的导出的时候，遇到一个报错：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
EXP-00008: ORACLE error 6550 encountered ORA-06550: line 1, column 18: PLS-00201: identifier 'SYS.DBMS_DEFER_IMPORT_INTERNAL' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored ORA-06512: at "SYS.DBMS_SYS_SQL", line 1120 ORA-06512: at "SYS.DBMS_SQL", line 323 ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 97 ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 126 ORA-06512: at line 1 . . exporting table DEF$_AQERROR EXP-00008: ORACLE error 6510 encountered ORA-06510: PL/SQL: unhandled user-defined exception ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 50 ORA-06512: at "SYS.DBMS_EXPORT_EXTENSION", line 126 ORA-06512: at line 1 |
这个库是在aix的上一个9208的库，前段时间刚刚打了CPUApr2010。应该来说，做全库导出，是不会有这样的报错的，做全库导出的用户也给了connect，resource，db […]
AIX上升级CPUApr2010
周末有个加班，需要在一台sever上升级22个instance，数据库从9207升级到9208，在打一个CPUApr2010。在这里简单记录一下升级的过程和经验。 从这个架构来看，这个server上的数据库属于库小且多的类型，虽然每个库都很小，也就几个G，但是数量不少。所以在做9207升级到9208 […]
深圳卓望招聘高级Oracle DBA
公司在招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考试-grid control中的job system
grid control中的job system在gc界面中首页的job菜单中，这个job可以建立很多类型的job，用途远远大于db control中的job或者schedual，在这里我们简单模拟几个常用的job的建立。（注意本文多图杀猫） 一、写在前面： 1、gc的登录用户，不能用一般的crea […]
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 […]