标签云
asm 恢复 asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (100)
- 数据库 (1,598)
- DB2 (22)
- MySQL (70)
- Oracle (1,463)
- Data Guard (49)
- EXADATA (7)
- GoldenGate (21)
- ORA-xxxxx (158)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (13)
- ORACLE 21C (3)
- Oracle ASM (65)
- Oracle Bug (7)
- Oracle RAC (47)
- Oracle 安全 (6)
- Oracle 开发 (27)
- Oracle 监听 (27)
- Oracle备份恢复 (530)
- Oracle安装升级 (84)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (75)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (36)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (19)
-
最近发表
- PostgreSQL解析wal日志之—walminer
- Oracle 19c/21c最新patch信息-202404
- PostgreSQL恢复系列:pg_filedump批量处理
- PostgreSQL部分主要字典信息
- PostgreSQL恢复系列:pg_filedump恢复字典构造
- PostgreSQL 16 源码安装
- ORA-00742 ORA-00312 恢复
- 数据库open成功后报ORA-00353 ORA-00354错误引起的一系列问题(本质ntfs文件系统异常)
- ORA-600 ktsiseginfo1故障
- ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
- 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
- ORA-600 2662快速恢复之Patch scn工具
- TNS-12518: TNS:listener could not hand off client connection
- ora.storage无法启动报ORA-12514故障处理
- 断电引起文件scn异常数据库恢复
- ORA-16188: LOG_ARCHIVE_CONFIG settings inconsistent with previously started instance
- .[hudsonL@cock.li].mkp勒索加密数据库完美恢复
- 模拟带库实现rman远程备份
- 又一例:ORA-600 kclchkblk_4和2662故障
- Oracle误删除数据文件恢复
月归档:四月 2012
关于linux中oracle用户进程占用内存猜测
本文是针对linux下面显示oracle用户进程占用大量内存的一个猜想性说明,希望各位专家和我一起继续探讨该问题
ORACLE用户进程占用私有内存分析
top命令结果
[oracle@ora02 31500]$ top -c top - 12:13:16 up 254 days, 12:14, 2 users, load average: 1.53, 1.62, 1.33 Tasks: 293 total, 3 running, 290 sleeping, 0 stopped, 0 zombie Cpu(s): 3.4% us, 0.8% sy, 0.0% ni, 94.7% id, 1.1% wa, 0.0% hi, 0.0% si Mem: 4147172k total, 4129724k used, 17448k free, 20348k buffers Swap: 4192956k total, 217772k used, 3975184k free, 2575320k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 12505 oracle 17 0 1561m 972m 970m R 100 24.0 3:45.89 oracletxzldb (LOCAL=NO) 12475 oracle 16 0 1561m 931m 929m S 4 23.0 4:08.65 oracletxzldb (LOCAL=NO) 12477 oracle 16 0 1561m 945m 942m S 2 23.3 3:36.92 oracletxzldb (LOCAL=NO) 12479 oracle 16 0 1561m 944m 941m S 2 23.3 4:27.29 oracletxzldb (LOCAL=NO) 12483 oracle 16 0 1561m 939m 937m R 2 23.2 3:49.00 oracletxzldb (LOCAL=NO) 12493 oracle 16 0 1561m 958m 955m S 2 23.7 3:35.35 oracletxzldb (LOCAL=NO)
补充说明:
VIRT 进程使用的虚拟内存总量 RES 进程使用的、未被换出的物理内存大小 SHR 共享内存大小
通过这里可以得出几个信息
12505进程实际使用内存情况:972-970=2M
12505进程%MEM:972*1024/4147172=0.24000162
12505进程在数据库中占用内存
SQL> select PGA_ALLOC_MEM/1024/1024 MEM from v$process where spid=12505; MEM ---------- 1.90997028
通过这里可以看出12505进程实际上是占用了970M的共享内存,占用2M的PGA内存
ORACLE用户进程占用共享内存分析
分析12505进程的当前进程状态
[oracle@ora02 31500]$ cd /proc/12505 [oracle@ora02 12505]$ cat status Name: oracle State: S (sleeping) SleepAVG: 92% Tgid: 12505 Pid: 12505 PPid: 1 TracerPid: 0 Uid: 501 501 501 501 Gid: 502 502 502 502 FDSize: 32 Groups: 501 502 VmSize: 1599004 kB <--使用内存(包括虚拟内存)总量1599004/1024=1561.52734和top中VIRT基本吻合 VmLck: 0 kB VmRSS: 996132 kB <--实际使用内存996132/1024 =972.785156和top看到RES基本吻合 VmData: 832 kB VmStk: 120 kB VmExe: 37307 kB VmLib: 4641 kB StaBrk: 0ad6e000 kB Brk: 0adf2000 kB StaStk: bffff850 kB ExecLim: ffffffff Threads: 1 SigPnd: 0000000000000000 ShdPnd: 0000000000000000 SigBlk: 0000000000000000 SigIgn: 0000000006005203 SigCgt: 00000001c9802cfc CapInh: 0000000000000000 CapPrm: 0000000000000000 CapEff: 0000000000000000
pmap命令分析
[oracle@ora02 12505]$ pmap -d 12505 12505: oracletxzldb (LOCAL=NO) Address Kbytes Mode Offset Device Mapping 0013f000 88 r-x-- 0000000000000000 008:00002 ld-2.3.4.so 00155000 4 r-x-- 0000000000015000 008:00002 ld-2.3.4.so 00156000 4 rwx-- 0000000000016000 008:00002 ld-2.3.4.so 00159000 1176 r-x-- 0000000000000000 008:00002 libc-2.3.4.so 0027f000 8 r-x-- 0000000000125000 008:00002 libc-2.3.4.so 00281000 8 rwx-- 0000000000127000 008:00002 libc-2.3.4.so 00283000 8 rwx-- 0000000000283000 000:00000 [ anon ] 00287000 132 r-x-- 0000000000000000 008:00002 libm-2.3.4.so 002a8000 4 r-x-- 0000000000020000 008:00002 libm-2.3.4.so 002a9000 4 rwx-- 0000000000021000 008:00002 libm-2.3.4.so 002ac000 8 r-x-- 0000000000000000 008:00002 libdl-2.3.4.so 002ae000 4 r-x-- 0000000000001000 008:00002 libdl-2.3.4.so 002af000 4 rwx-- 0000000000002000 008:00002 libdl-2.3.4.so 003b5000 56 r-x-- 0000000000000000 008:00002 libpthread-2.3.4.so 003c3000 4 r-x-- 000000000000d000 008:00002 libpthread-2.3.4.so 003c4000 4 rwx-- 000000000000e000 008:00002 libpthread-2.3.4.so 003c5000 8 rwx-- 00000000003c5000 000:00000 [ anon ] 00ba4000 72 r-x-- 0000000000000000 008:00002 libnsl-2.3.4.so 00bb6000 4 r-x-- 0000000000011000 008:00002 libnsl-2.3.4.so 00bb7000 4 rwx-- 0000000000012000 008:00002 libnsl-2.3.4.so 00bb8000 8 rwx-- 0000000000bb8000 000:00000 [ anon ] 08048000 37308 r-x-- 0000000000000000 0fd:00001 oracle 0a4b7000 8804 rwx-- 000000000246f000 0fd:00001 oracle 0ad50000 648 rwx-- 000000000ad50000 000:00000 [ anon ] 50000000 1540096 rwxs- 0000000000000000 000:00006 [ shmid=0x9000e ] ae000000 4 r-xs- 000000005e000000 000:00006 [ shmid=0x9000e ] ae001000 1156 rwxs- 000000005e001000 000:00006 [ shmid=0x9000e ] ae122000 4 r-xs- 000000005e122000 000:00006 [ shmid=0x9000e ] ae123000 2932 rwxs- 000000005e123000 000:00006 [ shmid=0x9000e ] b79d4000 1024 rwx-- 00000000000f4000 000:0000d zero b7ad4000 512 rwx-- 0000000000074000 000:0000d zero b7b54000 512 rwx-- 0000000000000000 000:0000d zero b7bd4000 36 r-x-- 0000000000000000 008:00002 libnss_files-2.3.4.so b7bdd000 4 r-x-- 0000000000008000 008:00002 libnss_files-2.3.4.so b7bde000 4 rwx-- 0000000000009000 008:00002 libnss_files-2.3.4.so b7bdf000 148 rwx-- 00000000b7bdf000 000:00000 [ anon ] b7c04000 2940 r-x-- 0000000000000000 0fd:00001 libjox9.so b7ee3000 1088 rwx-- 00000000002de000 0fd:00001 libjox9.so b7ff3000 8 rwx-- 00000000b7ff3000 000:00000 [ anon ] b7ff5000 4 r-x-- 0000000000000000 0fd:00001 libskgxn9.so b7ff6000 8 rwx-- 0000000000000000 0fd:00001 libskgxn9.so b7ff8000 4 r-x-- 0000000000000000 0fd:00001 libskgxp9.so b7ff9000 4 --x-- 0000000000001000 0fd:00001 libskgxp9.so b7ffa000 4 rwx-- 0000000000001000 0fd:00001 libskgxp9.so b7ffb000 4 r-x-- 0000000000000000 0fd:00001 libodmd9.so b7ffc000 4 rwx-- 0000000000000000 0fd:00001 libodmd9.so b7ffd000 4 r-x-- 0000000000000000 008:00002 libcwait.so b7ffe000 4 rwx-- 0000000000000000 008:00002 libcwait.so b7fff000 4 rwx-- 00000000b7fff000 000:00000 [ anon ] bffe2000 120 rwx-- 00000000bffe2000 000:00000 [ stack ] ffffe000 4 ----- 0000000000000000 000:00000 [ anon ] mapped: 1599008K writeable/private: 12944K shared: 1544192K
补充说明:
mapped :映射到文件的内存数量 writable/private :进程所占用的私有地址空间数量 shared :与其它进程共享的地址空间数量
ipcs 命令
[oracle@ora02 12505]$ ipcs -m ------ Shared Memory Segments -------- key shmid owner perms bytes nattch status 0x0000cace 65536 root 666 2 0 0x4d4e5251 98305 root 644 330752 0 0x55315352 131074 root 666 4096 0 0x44525354 163843 root 644 632832 0 0x53494152 196612 root 644 1024 0 0x00005643 229381 root 666 1024 1 0x00005654 262150 root 666 1024 1 0x992ad3dc 589838 oracle 640 1581252608 595
结合pmap和ipcs分析(shmid=0x9000e)
SQL> select to_number('9000e','xxxxxxxx') from dual; TO_NUMBER('9000E','XXXXXXXX') ----------------------------- 589838 SQL> select 1540096+4+1156+4+2932 from dual; 1540096+4+1156+4+2932 --------------------- 1544192 SQL> select 1581252608/1024 from dual; 1581252608/1024 --------------- 1544192
通过这里可以得出12505进程中的共享内存,主要是数据库SGA中的共享内存
补充猜测
SQL> show sga; Total System Global Area 1561926292 bytes Fixed Size 453268 bytes Variable Size 603979776 bytes Database Buffers 956301312 bytes Redo Buffers 1191936 bytes SQL> select 1561926292/1024 from dual; 1561926292/1024 --------------- 1525318.64
这里显示数据库配置的sga比ipcs中配置共享内存段小,但是进程在分配总的共享内存时候,使用的是ipcs设定的内存段大小,实际使用的内存可能是sga设置大小(未得到权威资料)
impdp报ORA-00904: “ORIGINAL_OBJECT_NAME”: invalid identifier
发现问题
impdp导入数据库不成功,一直在报ORA-00904: “ORIGINAL_OBJECT_NAME”: invalid identifier错误
[oracle@back1 backup]$ impdp username/password schemas=center_admin dumpfile=center_admin20120427.dmp > logfile=center_admin20120427.log directory=impdir parallel=10 job_name=center_admin08; Import: Release 11.1.0.6.0 - 64bit Production on Friday, 27 April, 2012 21:35:06 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-39002: invalid operation ORA-31694: master table "USERNAME"."CENTER_ADMIN08" failed to load/unload ORA-02354: error in exporting/importing data ORA-02373: Error parsing insert statement for table "USERNAME"."CENTER_ADMIN08". ORA-00904: "ORIGINAL_OBJECT_NAME": invalid identifier
分析问题
看到这个错误,我第一个感觉根据ORA-31694,怀疑是没有创建相关用户,或者是该用户无权限权限CENTER_ADMIN08表.等我登陆目标数据库查看时候发现该用户存在,并且已经授予了DBA权限,所以不存在是用户相关问题导致.ORA-02354错误我怀疑是expdp导出来的文件在传输过程中发生意外(如使用ftp传输未使用二进制模式),当我使用md5sum命令检查发现两边一致,证明该文件传输正常.目标端不能检查明显故障,怀疑导出文件本身存在问题检查导出文件日志
[oracle@fcdb2 backup]$ more center_admin20120427.log ;;; Export: Release 11.1.0.7.0 - 64bit Production on Friday, 27 April, 2012 17:32:30 Copyright (c) 2003, 2007, Oracle. All rights reserved. ;;; Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production With the Partitioning, Real Application Clusters, OLAP, Data Mining and Real Application Testing options Starting "USERNAME"."CENTER_ADMIN08":USERNAME/**** schemas=center_admin directory=expdir dumpfile=center_admin20120427.dmp er_admin20120427.log parallel=10 job_name=center_admin08 Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 158.2 GB
发现新大陆,两边的数据库版本不一致,源端版本是11.1.0.7,目标端版本是11.1.0.6.这个时候我对问题的定位是可能版本兼用问题(毕竟是高版本到低版本)或者是bug.先查询datapump版本之间兼容性列表
Version Written by Can be imported into Target: Data Pump database with 10gR1 10gR2 11gR1 11gR2 Dumpfile Set compatibility 10.1.0.x 10.2.0.x 11.1.0.x 11.2.0.x ------------ --------------- ---------- ---------- ---------- ---------- 0.1 10.1.x supported supported supported supported 1.1 10.2.x no supported supported supported 2.1 11.1.x no no supported supported 3.1 11.2.x no no no supported
这里可以看出11.1.0.7和11.1.0.6之间是相互兼容的,不应该会存在上述问题,那么现在对于该问题的解释很可能是bug导致,继续查询资料发现[ID 752374.1]描述的正是该问题.
解决问题
1. Apply 11.1.0.7 Patch:6890831 on the target database. 2. Workaround this issue by re-running an expdp from the 11.1.0.7 database with an additional parameter VERSION=10.2. This will create a new dump file compatible to be imported into 10gR2, which will also import successfully into 11.1.0.6. Unfortunately, please be aware that the new 11g specific features will not be exported if expdp is run from 11.1.0.7 with the parameter VERSION=10.2. If only normal objects and features are involved, this could be a good workaround.
ORA-00001 Unique Constraint SYS.I_JOB_JOB Violated
IMPDP导入数据发现ORA-00001 Unique Constraint SYS.I_JOB_JOB Violated错误
ORA-39083: Object type JOB failed to create with error: ORA-00001: unique constraint (SYS.I_JOB_JOB) violated Failing sql is: BEGIN DBMS_JOB.ISUBMIT( JOB=> 63, NEXT_DATE=> TO_DATE('2012-04-27 00:00:00', 'YYYY-MM-DD:HH24:MI:SS'), INTERVAL=> 'TRUNC(SYSDATE+1)', WHAT=> 'GBEAS1.UPDATE_EMP_INFO;', NO_PARSE=> TRUE); END; Job "GBEAS3"."SYS_IMPORT_FULL_01" completed with 8 error(s) at 16:05:58
错误原因(该job=63已经存在数据库中)
select job, what from dba_jobs where job=63; JOB WHAT ----- -------- 63 proc_xifenfei
注意:如果该job正在运行,可能需要查询DBA_JOBS_RUNNING
解决办法
1.手工创建job,指定一个不存在的job 号 declare m_job number; begin select max (job) + 1 into m_job from dba_jobs; BEGIN DBMS_JOB.ISUBMIT( JOB=> m_job, NEXT_DATE=> TO_DATE('2012-04-27 00:00:00', 'YYYY-MM-DD:HH24:MI:SS'), INTERVAL=> 'TRUNC(SYSDATE+1)', WHAT=> 'GBEAS1.UPDATE_EMP_INFO;', NO_PARSE=> TRUE); END; end; / 2.删除原存在job exec dbms_job.remove (63);
这样的情况,一般发生在expdp导出数据包含了job(如:全库导出,用户导出),然后导入到目标库,而该job号已经存在导致