今天上班同事说医保数据库expdp导出没有完成,同时医保业务人员报告登录系统有时能登录,有时不能登录。Expdp导出日志如下:
[IBMP740-1:root:/yb_oradata/RLZYbak/dpdump]#cat insur_changde_150921_2330.log Export: Release 10.2.0.4.0 - 64bit Production on Monday, 21 September, 2015 23:30:00 Copyright (c) 2003, 2007, Oracle. All rights reserved. ;;; Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "INSUR_CHANGDE"."SYS_EXPORT_SCHEMA_07": insur_changde/******** directory=dump_RLZY dumpfile=insur_changde_150921_2330.dmp logfile=insur_changde_150921_2330.log Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 492.0 GB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC Processing object type SCHEMA_EXPORT/DB_LINK Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/COMMENT Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION Processing object type SCHEMA_EXPORT/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE Processing object type SCHEMA_EXPORT/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE Processing object type SCHEMA_EXPORT/VIEW/VIEW Processing object type SCHEMA_EXPORT/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Processing object type SCHEMA_EXPORT/TYPE/TYPE_BODY Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/TRIGGER Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Processing object type SCHEMA_EXPORT/JOB . . exported "INSUR_CHANGDE"."MT_BIZ_SCENE_FIN" 51.86 GB 1111873243 rows . . exported "INSUR_CHANGDE"."MT_FEE_FIN" 22.76 GB 133817090 rows
从上面的expdp日志信息来看并没有错误,更像理导出进程停止,如果查看dba_datapump_job视图来查看有没有被异常终止的epxdp导出job
从上面的信息可以看到insur_changde用户出现了多个expdp导出异常终止的job。从job命名规则可以看到最近的异常终止job是sys_export_schema_07,而且状态是空闲的。那么重新连接sys_export_schema_07这个job来查看job状态。
[IBMP740-1:oracle:/yb_oradata/RLZYbak]$expdp 'insur_changde/"power$20140224"' attach=SYS_EXPORT_SCHEMA_07 Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 22 September, 2015 16:51:51 Copyright (c) 2003, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Job: SYS_EXPORT_SCHEMA_07 Owner: INSUR_CHANGDE Operation: EXPORT Creator Privs: FALSE GUID: 20448E2327C5015EE053C0A80201015E Start Time: Tuesday, 22 September, 2015 16:51:56 Mode: SCHEMA Instance: RLZY Max Parallelism: 1 EXPORT Job Parameters: Parameter Name Parameter Value: CLIENT_COMMAND insur_changde/******** directory=dump_RLZY dumpfile=insur_changde_150921_2330.dmp logfile=insur_changde_150921_2330.log State: IDLING Bytes Processed: 80,139,523,792 Percent Done: 41 Current Parallelism: 1 Job Error Count: 0 Dump File: /yb_oradata/RLZYbak/dpdump/insur_changde_150921_2330.dmp bytes written: 80,145,354,752 Worker 1 Status: State: UNDEFINED Object Schema: INSUR_CHANGDE Object Name: LV_INDIPAR Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA Completed Objects: 3 Total Objects: 1,225 Completed Rows: 288,824,659 Worker Parallelism: 1 Worker 1 Status: State: UNDEFINED Object Schema: INSUR_CHANGDE Object Name: LV_INDIPAR Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA Completed Objects: 3 Total Objects: 1,225 Completed Rows: 288,824,659 Worker Parallelism: 1
从上面的信息可以看出现在expdp job正在导出的表为LV_INDIPAR表,状态为UNDEFINED,也没有其它有用信息。那么为什么expdp job会异常终止了。检查alert.log文件在执行expdp导出时出现了以下错误信息。
Starting control autobackup Control autobackup written to SBT_TAPE device comment 'API Version 2.0,MMS Version 1.2.0.0', media 'backup_nw.023.RO' handle 'c-1589671076-20150921-00' Mon Sep 21 23:30:02 2015 The value (30) of MAXTRANS parameter ignored. kupprdp: master process DM00 started with pid=188, OS id=23527444 to execute - SYS.KUPM$MCP.MAIN('SYS_EXPORT_SCHEMA_07', 'INSUR_CHANGDE', 'KUPC$C_1_20150921233002', 'KUPC$S_1_20150921233002', 0); kupprdp: worker process DW01 started with worker id=1, pid=189, OS id=1704856 to execute - SYS.KUPW$WORKER.MAIN('SYS_EXPORT_SCHEMA_07', 'INSUR_CHANGDE'); Tue Sep 22 00:24:18 2015 ksvcreate: Process(q001) creation failed Tue Sep 22 00:24:38 2015 Process startup failed, error stack: Tue Sep 22 00:24:39 2015 Errors in file /oracle/admin/RLZY/bdump/rlzy_psp0_7471450.trc: ORA-27300: OS system dependent operation:fork failed with status: 12 ORA-27301: OS failure message: Not enough space ORA-27302: failure occurred at: skgpspawn3 Tue Sep 22 00:24:39 2015 Process q001 died, see its trace file Tue Sep 22 00:24:39 2015 ksvcreate: Process(q001) creation failed Tue Sep 22 00:24:51 2015
从上面信息可以看到expdp job是在21号的23:30开始执行,在22号的00:24:39出现了故障并在/oracle/admin/RLZY/bdump/rlzy_psp0_7471450.trc文件中生成了错误信息如下。
*** 2015-09-20 00:24:36.347 Process startup failed, error stack: ORA-27300: OS system dependent operation:fork failed with status: 12 ORA-27301: OS failure message: Not enough space ORA-27302: failure occurred at: skgpspawn3
根据MOS文章Troubleshooting ORA-27300 ORA-27301 ORA-27302 errors (Doc ID 579365.1),出现这种错误信息主要是因为内存或交换区被用尽的原因,如是检查系统内存与交换区的使用情况
[IBMP740-1:root:/]#topas_nmon lqtopas_nmonqqh=HelpqqqqqqqqqqqqqHost=IBMP740-1qqqqqqRefresh=2 secsqqq16:58.25 x Memory x x Physical PageSpace | pages/sec In Out | FileSystemCache x x% Used 99.8% 68.0% | to Paging Space 0.0 0.0 | (numperm) 49.6% x x% Free 0.2% 32.0% | to File System 586.6 11.7 | Process 42.7% x xMB Used 63572.0MB 11142.9MB | Page Scans 126.5 | System 7.6% x xMB Free 108.0MB 5241.1MB | Page Cycles 0.0 | Free 0.2% x xTotal(MB) 63680.0MB 16384.0MB | Page Steals 126.5 | ------ x x | Page Faults 1317.1 | Total 100.0% x x------------------------------------------------------------ | numclient 49.6% x xMin/Maxperm 1853MB( 3%) 55589MB( 90%) < --% of RAM | maxclient 90.0% x xMin/Maxfree 960 1088 Total Virtual 78.2GB | User 89.3% x xMin/Maxpgahead 2 8 Accessed Virtual 33.6GB 43.0%| Pinned 9.4% x x | lruable pages 15811872.0
从上面的信息可以看到物理内存为63680.0MB,交换区为16384.0M了,物理内存使用了63572.0M,交换区使用了11142.9M,物理内存了可用内存只有108.0M占总物理内存的0.2%,交换区是5241.1M占总交换区的32%。FileSystemCache (numperm) 49.6% 说明AIX 文件系统缓存占用了物理内存的49.6%,Process 42.7%说明进程占用了物理内存的42.7%,System 7.6%说明系统占用了物理内存的7.6%, Free 0.2%说明了可用的物理内存只有0.2%。并且可以看到Maxperm=90%,maxclient=90%,说明文件系统缓存使用物理内存的最大限制为物理内存的90%。
检查AIX系统中消耗内存前10的进程,如下所示大部分是Oracle相关进程
[IBMP740-1:root:/]#ps -ealf | head -1 ; ps -ealf | sort -rn +9 | head F S UID PID PPID C PRI NI ADDR SZ WCHAN STIME TTY TIME CMD 240001 A oracle 6553662 1 0 60 20 a31123590 115936 Jun 27 - 22:05 ora_lgwr_RLZY 240001 A oracle 57671750 1 0 60 20 c41744590 111768 f1000e0004ee48c8 Sep 16 - 28:48 oracleRLZY (LOCAL=NO) 240001 A oracle 61735218 1 0 60 20 c44fc4590 109912 f1000e00100440c8 Sep 16 - 31:40 oracleRLZY (LOCAL=NO) 240001 A oracle 58982776 1 0 60 20 fb447b590 109528 f1000e0004a0b8c8 Sep 16 - 12:57 oracleRLZY (LOCAL=NO) 240001 A oracle 26935684 1 0 60 20 f416f4590 108264 Jun 27 - 2:07 ora_arc1_RLZY 240001 A oracle 26870144 1 0 60 20 cf16cf590 108264 Jun 27 - 2:37 ora_arc0_RLZY 240001 A oracle 7536818 1 0 60 20 a71127590 108248 Jun 27 - 15:59 ora_cjq0_RLZY 240001 A oracle 7733430 1 0 60 20 8a0e0a590 106096 Jun 27 - 8:54 ora_dbw0_RLZY 240001 A oracle 8913722 1 24 72 20 864c86590 104764 Sep 16 - 18:14 oracleRLZY (LOCAL=NO) 240001 A oracle 26214712 1 0 60 20 944194590 104584 16:51:55 - 0:00 ora_dm00_RLZY
[IBMP740-1:root:/]#topas -M Topas Monitor for host: IBMP740-1 Interval: 2 Tue Sep 22 17:13:05 2015 ================================================================================ REF1 SRAD TOTALMEM INUSE FREE FILECACHE HOMETHRDS CPUS -------------------------------------------------------------------------------- 0 0 60.4G 60.3G 106.5 30.8G 748 0-31 1 1 0.0 0.0 0.0 0.0 625 32-63 ================================================================================ CPU SRAD TOTALDISP LOCALDISP% NEARDISP% FARDISP% ------------------------------------------------------------ 36 1 439 100.0 0.0 0.0 60 1 345 100.0 0.0 0.0 56 1 184 100.0 0.0 0.0 0 0 144 100.0 0.0 0.0 32 1 93 100.0 0.0 0.0 16 0 88 100.0 0.0 0.0 8 0 54 100.0 0.0 0.0 40 1 43 100.0 0.0 0.0 12 0 36 100.0 0.0 0.0 20 0 28 100.0 0.0 0.0 4 0 28 100.0 0.0 0.0 28 0 21 100.0 0.0 0.0 44 1 18 100.0 0.0 0.0 24 0 12 100.0 0.0 0.0 52 1 11 100.0 0.0 0.0 48 1 1 100.0 0.0 0.0 17 0 0 0.0 0.0 0.0 18 0 0 0.0 0.0 0.0 19 0 0 0.0 0.0 0.0 10 0 0 0.0 0.0 0.0 21 0 0 0.0 0.0 0.0 22 0 0 0.0 0.0 0.0 23 0 0 0.0 0.0 0.0 9 0 0 0.0 0.0 0.0 25 0 0 0.0 0.0 0.0 26 0 0 0.0 0.0 0.0 27 0 0 0.0 0.0 0.0 7 0 0 0.0 0.0 0.0 29 0 0 0.0 0.0 0.0 30 0 0 0.0 0.0 0.0 31 0 0 0.0 0.0 0.0 6 0 0 0.0 0.0 0.0 33 1 0 0.0 0.0 0.0 5 0 0 0.0 0.0 0.0
从上面的信息可知除了系统所用的物理内存之外,总的可用物理内存是60.4G,使用了60.3G,可用106.5M,文件系统缓存是30.8G。
使用操作系统命令vmo -a –F来查看操作系统参数
[IBMP740-1:root:/]#vmo -a -F ame_cpus_per_pool = n/a ame_maxfree_mem = n/a ame_min_ucpool_size = n/a ame_minfree_mem = n/a ams_loan_policy = n/a enhanced_affinity_affin_time = 1 enhanced_affinity_vmpool_limit = 10 esid_allocator = 0 force_relalias_lite = 0 kernel_heap_psize = 65536 lgpg_regions = 0 lgpg_size = 0 low_ps_handling = 1 maxfree = 1088 maxperm = 14230680 maxpin = 13137354 maxpin% = 80 memory_frames = 16302080 memplace_data = 0 memplace_mapped_file = 0 memplace_shm_anonymous = 0 memplace_shm_named = 0 memplace_stack = 0 memplace_text = 0 memplace_unmapped_file = 0 minfree = 960 minperm = 474353 minperm% = 3 nokilluid = 0 npskill = 32768 npswarn = 131072 num_locks_per_semid = 1 numpsblks = 4194304 pinnable_frames = 14750156 relalias_percentage = 0 scrub = 0 v_pinshm = 0 vmm_default_pspa = 0 vmm_klock_mode = 1 wlm_memlimit_nonpg = 1 ##Restricted tunables ame_sys_memview = n/a cpu_scale_memp = 8 data_stagger_interval = 161 defps = 1 enhanced_affinity_attach_limit = 100 enhanced_affinity_balance = 100 enhanced_affinity_private = 40 enhanced_memory_affinity = 1 framesets = 2 htabscale = n/a kernel_psize = 65536 large_page_heap_size = 0 lru_file_repage = 0 lru_poll_interval = 10 lrubucket = 131072 maxclient% = 90 maxperm% = 90 mbuf_heap_psize = 65536 memory_affinity = 1 multiple_semid_lists = 0 munmap_npages = 16384 npsrpgmax = 262144 npsrpgmin = 196608 npsscrubmax = 262144 npsscrubmin = 196608 num_sem_undo_lists = 0 num_sems_per_lock = 1 num_spec_dataseg = 0 numperm_global = 1 page_steal_method = 1 psm_timeout_interval = 20000 relalias_lockmode = 1 rpgclean = 0 rpgcontrol = 2 scrubclean = 0 shm_1tb_shared = 12 shm_1tb_unsh_enable = 1 shm_1tb_unshared = 256 soft_min_lgpgs_vmpool = 0 spec_dataseg_int = 512 strict_maxclient = 1 strict_maxperm = 0 sync_npages = 0 thrpgio_inval = 1024 thrpgio_npages = 1024 vm_mmap_areload = 0 vm_modlist_threshold = -1 vm_pvlist_dohard = 0 vm_pvlist_szpcnt = 0 vmm_fork_policy = 1 vmm_mpsize_support = 2 vmm_vmap_policy = 0 vtiol_avg_ms = 200 vtiol_minreq = 25 vtiol_minth_active = 1 vtiol_mode = 0 vtiol_pgin_mode = 2 vtiol_pgout_mode = 2 vtiol_q_cpu_pct = 2500 vtiol_thread_cpu_pct = 5000
主要是maxclient% = 90,maxperm% = 90参数,说明文件系统缓存使用物理内存的最大限制为物理内存的90%。所以这里只需要将maxclient%与maxperm%参数调小,让系统有空闲内存来分配给新产生的进程来执行特定操作。调整maxclient%与maxperm%参数。
[IBMP740-1:root:/]#vmo -p -o maxclient%=20 Modification to restricted tunable maxclient%, confirmation required yes/no yes Setting maxclient% to 20 in nextboot file Setting maxclient% to 20 Warning: a restricted tunable has been modified [IBMP740-1:root:/]#vmo -p -o maxperm%=20 Modification to restricted tunable maxperm%, confirmation required yes/no yes Setting maxperm% to 20 in nextboot file Setting maxperm% to 20 Warning: a restricted tunable has been modified
调整后再次查看操作系统参数
[IBMP740-1:root:/]#vmo -a -F ame_cpus_per_pool = n/a ame_maxfree_mem = n/a ame_min_ucpool_size = n/a ame_minfree_mem = n/a ams_loan_policy = n/a enhanced_affinity_affin_time = 1 enhanced_affinity_vmpool_limit = 10 esid_allocator = 0 force_relalias_lite = 0 kernel_heap_psize = 65536 lgpg_regions = 0 lgpg_size = 0 low_ps_handling = 1 maxfree = 1088 maxperm = 3162370 maxpin = 13137354 maxpin% = 80 memory_frames = 16302080 memplace_data = 0 memplace_mapped_file = 0 memplace_shm_anonymous = 0 memplace_shm_named = 0 memplace_stack = 0 memplace_text = 0 memplace_unmapped_file = 0 minfree = 960 minperm = 790590 minperm% = 5 nokilluid = 0 npskill = 32768 npswarn = 131072 num_locks_per_semid = 1 numpsblks = 4194304 pinnable_frames = 14770780 relalias_percentage = 0 scrub = 0 v_pinshm = 0 vmm_default_pspa = 0 vmm_klock_mode = 1 wlm_memlimit_nonpg = 1 ##Restricted tunables ame_sys_memview = n/a cpu_scale_memp = 8 data_stagger_interval = 161 defps = 1 enhanced_affinity_attach_limit = 100 enhanced_affinity_balance = 100 enhanced_affinity_private = 40 enhanced_memory_affinity = 1 framesets = 2 htabscale = n/a kernel_psize = 65536 large_page_heap_size = 0 lru_file_repage = 0 lru_poll_interval = 10 lrubucket = 131072 maxclient% = 20 maxperm% = 20 mbuf_heap_psize = 65536 memory_affinity = 1 multiple_semid_lists = 0 munmap_npages = 16384 npsrpgmax = 262144 npsrpgmin = 196608 npsscrubmax = 262144 npsscrubmin = 196608 num_sem_undo_lists = 0 num_sems_per_lock = 1 num_spec_dataseg = 0 numperm_global = 1 page_steal_method = 1 psm_timeout_interval = 20000 relalias_lockmode = 1 rpgclean = 0 rpgcontrol = 2 scrubclean = 0 shm_1tb_shared = 12 shm_1tb_unsh_enable = 1 shm_1tb_unshared = 256 soft_min_lgpgs_vmpool = 0 spec_dataseg_int = 512 strict_maxclient = 1 strict_maxperm = 0 sync_npages = 0 thrpgio_inval = 1024 thrpgio_npages = 1024 vm_mmap_areload = 0 vm_modlist_threshold = -1 vm_pvlist_dohard = 0 vm_pvlist_szpcnt = 0 vmm_fork_policy = 1 vmm_mpsize_support = 2 vmm_vmap_policy = 0 vtiol_avg_ms = 200 vtiol_minreq = 25 vtiol_minth_active = 1 vtiol_mode = 0 vtiol_pgin_mode = 2 vtiol_pgout_mode = 2 vtiol_q_cpu_pct = 2500 vtiol_thread_cpu_pct = 5000
从上面的结果看到修改生效了,文件系统缓存最大可以使用20%的物理内存。
[IBMP740-1:root:/]#topas_nmon lqtopas_nmonqqh=HelpqqqqqqqqqqqqqHost=IBMP740-1qqqqqqRefresh=2 secsqqq17:44.52 x Memory x x Physical PageSpace | pages/sec In Out | FileSystemCache x x% Used 69.8% 67.2% | to Paging Space 0.5 0.0 | (numperm) 19.3% x x% Free 30.2% 32.8% | to File System 9455.5 8.4 | Process 42.9% x xMB Used 44476.3MB 11010.5MB | Page Scans 9562.1 | System 7.6% x xMB Free 19203.7MB 5373.5MB | Page Cycles 0.0 | Free 30.2% x xTotal(MB) 63680.0MB 16384.0MB | Page Steals 9510.6 | ------ x x | Page Faults 7478.9 | Total 100.0% x x------------------------------------------------------------ | numclient 19.3% x xMin/Maxperm 3088MB( 5%) 12353MB( 20%) < --% of RAM | maxclient 20.0% x xMin/Maxfree 960 1088 Total Virtual 78.2GB | User 59.3% x xMin/Maxpgahead 2 8 Accessed Virtual 33.6GB 43.0%| Pinned 9.4% x x | lruable pages 15811872.0
可以看到现在空闲物理内存为30.2%,文件系统缓存(FileSystemCache) 19.2%,maxperm,maxclient为20%。现在执行expdp导出正常。
通过这个问题可以看出,AIX为了提高系统IO能力将空闲的物理内存作为文件系统缓存来使用,而且缺省参数可以使用物理内存的90%,这个缺省值在实际的生产环境中是很容易将内存耗尽的,所以AIX推荐的缺省值也是有问题的。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26015009/viewspace-1806629/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26015009/viewspace-1806629/