내맘대로긍정이 알려주는
Oracle 23ai 신기능
무료 세미나 발표자료
다운로드
trending_flat
OS환경 : Oracle Linux 7.6 (64bit)
DB 환경 : Oracle Database 12.2.0.1
방법 : 오라클 데이터펌프 expdp 시 세부 trace log남기기
일반적인 full=y 방법으로 expdp 실행
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 107 108 109 110 111 112 113 | $ expdp system/oracle directory=ORACLE_BASE full=y Export: Release 12.2.0.1.0 - Production on Sun Feb 23 21:32:27 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/------ directory=ORACLE_BASE full=y Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS Processing object type DATABASE_EXPORT/STATISTICS/MARKER Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/TABLESPACE Processing object type DATABASE_EXPORT/PROFILE Processing object type DATABASE_EXPORT/SYS_USER/USER Processing object type DATABASE_EXPORT/SCHEMA/USER Processing object type DATABASE_EXPORT/RADM_FPTM Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT Processing object type DATABASE_EXPORT/RESOURCE_COST Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER . . exported "SYS"."KU$_USER_MAPPING_VIEW" 5.898 KB 26 rows . . exported "SYSTEM"."REDO_DB" 25.68 KB 2 rows . . exported "SYS"."AUD$" 23.62 KB 1 rows . . exported "WMSYS"."WM$WORKSPACES_TABLE$" 12.10 KB 1 rows . . exported "WMSYS"."WM$HINT_TABLE$" 9.992 KB 97 rows . . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$" 7.085 KB 11 rows . . exported "SYS"."DAM_CONFIG_PARAM$" 6.531 KB 14 rows . . exported "SYS"."TSDP_SUBPOL$" 6.328 KB 1 rows . . exported "WMSYS"."WM$NEXTVER_TABLE$" 6.382 KB 1 rows . . exported "WMSYS"."WM$ENV_VARS$" 6.023 KB 3 rows . . exported "SYS"."TSDP_PARAMETER$" 5.953 KB 1 rows . . exported "SYS"."TSDP_POLICY$" 5.921 KB 1 rows . . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$" 5.984 KB 1 rows . . exported "WMSYS"."WM$EVENTS_INFO$" 5.820 KB 12 rows . . exported "SYS"."DAM_CLEANUP_EVENTS$" 0 KB 0 rows . . exported "SYS"."DAM_CLEANUP_JOBS$" 0 KB 0 rows . . exported "SYS"."TSDP_ASSOCIATION$" 0 KB 0 rows . . exported "SYS"."TSDP_CONDITION$" 0 KB 0 rows . . exported "SYS"."TSDP_FEATURE_POLICY$" 0 KB 0 rows . . exported "SYS"."TSDP_PROTECTION$" 0 KB 0 rows . . exported "SYS"."TSDP_SENSITIVE_DATA$" 0 KB 0 rows . . exported "SYS"."TSDP_SENSITIVE_TYPE$" 0 KB 0 rows . . exported "SYS"."TSDP_SOURCE$" 0 KB 0 rows . . exported "SYSTEM"."REDO_LOG" 0 KB 0 rows . . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$" 0 KB 0 rows . . exported "WMSYS"."WM$CONSTRAINTS_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$CONS_COLUMNS$" 0 KB 0 rows . . exported "WMSYS"."WM$LOCKROWS_INFO$" 0 KB 0 rows . . exported "WMSYS"."WM$MODIFIED_TABLES$" 0 KB 0 rows . . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$RIC_LOCKING_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$RIC_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$" 0 KB 0 rows . . exported "WMSYS"."WM$UDTRIG_INFO$" 0 KB 0 rows . . exported "WMSYS"."WM$VERSION_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$VT_ERRORS_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$" 0 KB 0 rows . . exported "SYS"."AUDTAB$TBS$FOR_EXPORT" 5.960 KB 2 rows . . exported "SYS"."DBA_SENSITIVE_DATA" 0 KB 0 rows . . exported "SYS"."DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows . . exported "SYS"."FGA_LOG$FOR_EXPORT" 0 KB 0 rows . . exported "SYS"."NACL$_ACE_EXP" 0 KB 0 rows . . exported "SYS"."NACL$_HOST_EXP" 6.921 KB 1 rows . . exported "SYS"."NACL$_WALLET_EXP" 0 KB 0 rows . . exported "SYS"."SQL$TEXT_DATAPUMP" 0 KB 0 rows . . exported "SYS"."SQL$_DATAPUMP" 0 KB 0 rows . . exported "SYS"."SQLOBJ$AUXDATA_DATAPUMP" 0 KB 0 rows . . exported "SYS"."SQLOBJ$DATA_DATAPUMP" 0 KB 0 rows . . exported "SYS"."SQLOBJ$PLAN_DATAPUMP" 0 KB 0 rows . . exported "SYS"."SQLOBJ$_DATAPUMP" 0 KB 0 rows . . exported "SYSTEM"."SCHEDULER_JOB_ARGS" 0 KB 0 rows . . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS" 9.523 KB 12 rows . . exported "WMSYS"."WM$EXP_MAP" 7.718 KB 3 rows . . exported "WMSYS"."WM$METADATA_MAP" 0 KB 0 rows . . exported "EDU"."??" 1.283 MB 19171 rows . . exported "SYSTEM"."MIG_VERIFY" 13.35 KB 129 rows . . exported "EDU"."MIG_VERIFY" 0 KB 0 rows Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is: /oracle/app/oracle/expdat.dmp Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Sun Feb 23 21:33:30 2020 elapsed 0 00:01:02 |
directory 경로에 위와 같은 내용으로 .log 파일만 기록됨
bdump 경로에도 특별한 로그가 쌓이지 않음
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 | $ cd /oracle/app/oracle/diag/rdbms/orcl12/ORCL12/trace $ ls -al total 46572 drwxr-x---. 2 oracle dba 4096 Feb 23 21:33 . drwxr-x---. 16 oracle dba 4096 Oct 15 00:57 .. -rw-r-----. 1 oracle dba 47542644 Feb 23 21:33 alert_ORCL12.log -rw-r-----. 1 oracle dba 1042 Feb 23 21:33 ORCL12_arc0_743.trc -rw-r-----. 1 oracle dba 860 Feb 23 21:33 ORCL12_arc0_743.trm -rw-r-----. 1 oracle dba 1064 Feb 23 21:33 ORCL12_dm00_29080.trc -rw-r-----. 1 oracle dba 863 Feb 23 21:33 ORCL12_dm00_29080.trm -rw-r-----. 1 oracle dba 5504 Feb 23 21:36 ORCL12_mmon_25763.trc -rw-r-----. 1 oracle dba 1559 Feb 23 21:36 ORCL12_mmon_25763.trm $ cat ORCL12_dm00_29080.trc Trace file /oracle/app/oracle/diag/rdbms/orcl12/ORCL12/trace/ORCL12_dm00_29080.trc Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Build label: RDBMS_12.2.0.1.0_LINUX.X64_170125 ORACLE_HOME: /oracle/app/oracle/product/12.2.0.1/db_1 System name: Linux Node name: ORACLE12 Release: 4.14.35-1818.3.3.el7uek.x86_64 Version: #2 SMP Mon Sep 24 14:45:01 PDT 2018 Machine: x86_64 Instance name: ORCL12 Redo thread mounted by this instance: 1 Oracle process number: 51 Unix process pid: 29080, image: oracle@ORACLE12 (DM00) *** 2020-02-23T21:33:31.802078+09:00 *** SESSION ID:(79.51024) 2020-02-23T21:33:31.802106+09:00 *** CLIENT ID:() 2020-02-23T21:33:31.802111+09:00 *** SERVICE NAME:(SYS$USERS) 2020-02-23T21:33:31.802116+09:00 *** MODULE NAME:(Data Pump Master) 2020-02-23T21:33:31.802121+09:00 *** ACTION NAME:(SYS_EXPORT_FULL_01) 2020-02-23T21:33:31.802126+09:00 *** CLIENT DRIVER:() 2020-02-23T21:33:31.802130+09:00 ksvp2penabled: ep->flg = 0, rpr->slv_flg = 0 ksvp2penabled: ep = 0x7f59562c3b38, rpr = 0x6d749d90 |
테스트 덤프파일 삭제
1 2 3 4 | $ cd $ORACLE_BASE $ ls admin audit cfgtoollogs checkpoints diag expdat.dmp export.log oradata product tfa $ rm exp* |
기존 trace 로그 삭제
1 2 3 | $ cd /oracle/app/oracle/diag/rdbms/orcl12/ORCL12/trace $ rm -rf ./*.trc $ rm -rf ./*.trm |
데이터 펌프 관련 프로세스 trace 설정
1 2 3 4 5 6 7 8 9 10 11 12 13 | $ sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Sun Feb 23 21:37:11 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL> alter system set events 'sql_trace {process: pname = dw | process: pname = dm} level=12'; System altered. |
trace 설정 후 똑같은 방법으로 expdp 실행
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 107 108 109 110 111 112 113 | $ expdp system/oracle directory=ORACLE_BASE full=y Export: Release 12.2.0.1.0 - Production on Sun Feb 23 21:42:03 2020 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/ directory=ORACLE_BASE full=y Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS Processing object type DATABASE_EXPORT/STATISTICS/MARKER Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/TABLESPACE Processing object type DATABASE_EXPORT/PROFILE Processing object type DATABASE_EXPORT/SYS_USER/USER Processing object type DATABASE_EXPORT/SCHEMA/USER Processing object type DATABASE_EXPORT/RADM_FPTM Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT Processing object type DATABASE_EXPORT/RESOURCE_COST Processing object type DATABASE_EXPORT/SCHEMA/DB_LINK Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER Processing object type DATABASE_EXPORT/SCHEMA/POST_SCHEMA/PROCACT_SCHEMA Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER . . exported "SYS"."KU$_USER_MAPPING_VIEW" 5.898 KB 26 rows . . exported "SYSTEM"."REDO_DB" 25.68 KB 2 rows . . exported "SYS"."AUD$" 23.62 KB 1 rows . . exported "WMSYS"."WM$WORKSPACES_TABLE$" 12.10 KB 1 rows . . exported "WMSYS"."WM$HINT_TABLE$" 9.992 KB 97 rows . . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$" 7.085 KB 11 rows . . exported "SYS"."DAM_CONFIG_PARAM$" 6.531 KB 14 rows . . exported "SYS"."TSDP_SUBPOL$" 6.328 KB 1 rows . . exported "WMSYS"."WM$NEXTVER_TABLE$" 6.382 KB 1 rows . . exported "WMSYS"."WM$ENV_VARS$" 6.023 KB 3 rows . . exported "SYS"."TSDP_PARAMETER$" 5.953 KB 1 rows . . exported "SYS"."TSDP_POLICY$" 5.921 KB 1 rows . . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$" 5.984 KB 1 rows . . exported "WMSYS"."WM$EVENTS_INFO$" 5.820 KB 12 rows . . exported "SYS"."DAM_CLEANUP_EVENTS$" 0 KB 0 rows . . exported "SYS"."DAM_CLEANUP_JOBS$" 0 KB 0 rows . . exported "SYS"."TSDP_ASSOCIATION$" 0 KB 0 rows . . exported "SYS"."TSDP_CONDITION$" 0 KB 0 rows . . exported "SYS"."TSDP_FEATURE_POLICY$" 0 KB 0 rows . . exported "SYS"."TSDP_PROTECTION$" 0 KB 0 rows . . exported "SYS"."TSDP_SENSITIVE_DATA$" 0 KB 0 rows . . exported "SYS"."TSDP_SENSITIVE_TYPE$" 0 KB 0 rows . . exported "SYS"."TSDP_SOURCE$" 0 KB 0 rows . . exported "SYSTEM"."REDO_LOG" 0 KB 0 rows . . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$" 0 KB 0 rows . . exported "WMSYS"."WM$CONSTRAINTS_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$CONS_COLUMNS$" 0 KB 0 rows . . exported "WMSYS"."WM$LOCKROWS_INFO$" 0 KB 0 rows . . exported "WMSYS"."WM$MODIFIED_TABLES$" 0 KB 0 rows . . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$RIC_LOCKING_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$RIC_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$" 0 KB 0 rows . . exported "WMSYS"."WM$UDTRIG_INFO$" 0 KB 0 rows . . exported "WMSYS"."WM$VERSION_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$VT_ERRORS_TABLE$" 0 KB 0 rows . . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$" 0 KB 0 rows . . exported "SYS"."AUDTAB$TBS$FOR_EXPORT" 5.960 KB 2 rows . . exported "SYS"."DBA_SENSITIVE_DATA" 0 KB 0 rows . . exported "SYS"."DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows . . exported "SYS"."FGA_LOG$FOR_EXPORT" 0 KB 0 rows . . exported "SYS"."NACL$_ACE_EXP" 0 KB 0 rows . . exported "SYS"."NACL$_HOST_EXP" 6.921 KB 1 rows . . exported "SYS"."NACL$_WALLET_EXP" 0 KB 0 rows . . exported "SYS"."SQL$TEXT_DATAPUMP" 0 KB 0 rows . . exported "SYS"."SQL$_DATAPUMP" 0 KB 0 rows . . exported "SYS"."SQLOBJ$AUXDATA_DATAPUMP" 0 KB 0 rows . . exported "SYS"."SQLOBJ$DATA_DATAPUMP" 0 KB 0 rows . . exported "SYS"."SQLOBJ$PLAN_DATAPUMP" 0 KB 0 rows . . exported "SYS"."SQLOBJ$_DATAPUMP" 0 KB 0 rows . . exported "SYSTEM"."SCHEDULER_JOB_ARGS" 0 KB 0 rows . . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS" 9.523 KB 12 rows . . exported "WMSYS"."WM$EXP_MAP" 7.718 KB 3 rows . . exported "WMSYS"."WM$METADATA_MAP" 0 KB 0 rows . . exported "EDU"."??" 1.283 MB 19171 rows . . exported "SYSTEM"."MIG_VERIFY" 13.35 KB 129 rows . . exported "EDU"."MIG_VERIFY" 0 KB 0 rows Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is: /oracle/app/oracle/expdat.dmp Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Sun Feb 23 21:43:38 2020 elapsed 0 00:01:34 |
trace log 확인
1 2 3 4 5 6 7 8 9 10 11 | $ ls -al total 208316 drwxr-x---. 2 oracle dba 4096 Feb 23 21:43 . drwxr-x---. 16 oracle dba 4096 Oct 15 00:57 .. -rw-r-----. 1 oracle dba 47543001 Feb 23 21:42 alert_ORCL12.log -rw-r-----. 1 oracle dba 6748462 Feb 23 21:43 ORCL12_dm00_29763.trc -rw-r-----. 1 oracle dba 1330511 Feb 23 21:43 ORCL12_dm00_29763.trm -rw-r-----. 1 oracle dba 118200279 Feb 23 21:43 ORCL12_dw00_29767.trc -rw-r-----. 1 oracle dba 22767146 Feb 23 21:43 ORCL12_dw00_29767.trm -rw-r-----. 1 oracle dba 4128 Feb 23 21:43 ORCL12_vkrm_688.trc -rw-r-----. 1 oracle dba 1127 Feb 23 21:43 ORCL12_vkrm_688.trm |
시작과 동시에 dm, dw 프로세스에 대한 trace log가 쌓임
ORCL12_dw00_29767.trc 파일의 일부 내용 확인
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 | $ head -100 ORCL12_dw00_29767.trc Trace file /oracle/app/oracle/diag/rdbms/orcl12/ORCL12/trace/ORCL12_dw00_29767.trc Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production Build label: RDBMS_12.2.0.1.0_LINUX.X64_170125 ORACLE_HOME: /oracle/app/oracle/product/12.2.0.1/db_1 System name: Linux Node name: ORACLE12 Release: 4.14.35-1818.3.3.el7uek.x86_64 Version: #2 SMP Mon Sep 24 14:45:01 PDT 2018 Machine: x86_64 Instance name: ORCL12 Redo thread mounted by this instance: 1 Oracle process number: 52 Unix process pid: 29767, image: oracle@ORACLE12 (DW00) *** 2020-02-23T21:42:05.558069+09:00 *** CLIENT ID:() 2020-02-23T21:42:05.558110+09:00 *** SERVICE NAME:(SYS$BACKGROUND) 2020-02-23T21:42:05.558117+09:00 *** MODULE NAME:() 2020-02-23T21:42:05.558171+09:00 *** ACTION NAME:() 2020-02-23T21:42:05.558179+09:00 *** CLIENT DRIVER:() 2020-02-23T21:42:05.558184+09:00 WAIT #0: nam='PGA memory operation' ela= 6 p1=65536 p2=1 p3=0 obj#=-1 tim=11394292235364 WAIT #0: nam='PGA memory operation' ela= 8 p1=65536 p2=1 p3=0 obj#=-1 tim=11394292237724 *** SESSION ID:(50.28857) 2020-02-23T21:42:05.561695+09:00 WAIT #0: nam='class slave wait' ela= 192 slave id=0 p2=0 p3=0 obj#=-1 tim=11394292239437 WAIT #0: nam='class slave wait' ela= 128 slave id=0 p2=0 p3=0 obj#=-1 tim=11394292239780 WAIT #0: nam='KSV master wait' ela= 3771 indicator=12 p2=0 p3=0 obj#=-1 tim=11394292243638 ===================== PARSING IN CURSOR #139737648313120 len=82 dep=1 uid=0 oct=3 lid=0 tim=11394292246313 hv=3873422482 ad='87484038' sqlid='0k8522rmdzg4k' select privilege# from sysauth$ where (grantee#=:1 or grantee#=1) and privilege#>0 END OF STMT PARSE #139737648313120:c=957,e=1736,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2057665657,tim=11394292246307 BINDS #139737648313120: Bind#0 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=1000001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7f1734e3aed8 bln=22 avl=02 flg=05 value=9 EXEC #139737648313120:c=312,e=819,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=2057665657,tim=11394292248617 FETCH #139737648313120:c=951,e=1545,p=0,cr=4,cu=0,mis=0,r=1,dep=1,og=4,plh=2057665657,tim=11394292250228 ===================== PARSING IN CURSOR #139737648283904 len=226 dep=1 uid=0 oct=3 lid=0 tim=11394292250665 hv=3008674554 ad='87af39c8' sqlid='5dqz0hqtp9fru' select /*+ connect_by_filtering index(sysauth$ i_sysauth1) */ privilege#, bitand(nvl(option$, 0), 72), grantee#, level from sysauth$ connect by grantee#=prior privilege# and privilege#>0 start with grantee#=:1 and privilege#>0 END OF STMT PARSE #139737648283904:c=619,e=241,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1158320201,tim=11394292250662 WAIT #139737648283904: nam='PGA memory operation' ela= 24 p1=65536 p2=1 p3=0 obj#=-1 tim=11394292251903 WAIT #139737648283904: nam='PGA memory operation' ela= 11 p1=131072 p2=2 p3=0 obj#=-1 tim=11394292251981 BINDS #139737648283904: Bind#0 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=08 fl2=1000001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7f1734e33cb8 bln=22 avl=02 flg=05 value=4 EXEC #139737648283904:c=213,e=575,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=1158320201,tim=11394292252423 WAIT #139737648283904: nam='PGA memory operation' ela= 7 p1=65536 p2=1 p3=0 obj#=-1 tim=11394292254376 WAIT #139737648283904: nam='PGA memory operation' ela= 4 p1=65536 p2=1 p3=0 obj#=-1 tim=11394292254442 WAIT #139737648283904: nam='PGA memory operation' ela= 3 p1=65536 p2=1 p3=0 obj#=-1 tim=11394292254494 WAIT #139737648283904: nam='PGA memory operation' ela= 4 p1=65536 p2=1 p3=0 obj#=-1 tim=11394292254581 WAIT #139737648283904: nam='PGA memory operation' ela= 4 p1=65536 p2=1 p3=0 obj#=-1 tim=11394292254632 WAIT #139737648283904: nam='PGA memory operation' ela= 4 p1=65536 p2=1 p3=0 obj#=-1 tim=11394292254688 WAIT #139737648283904: nam='PGA memory operation' ela= 7 p1=65536 p2=2 p3=0 obj#=-1 tim=11394292254753 WAIT #139737648283904: nam='PGA memory operation' ela= 8 p1=131072 p2=1 p3=0 obj#=-1 tim=11394292255948 WAIT #139737648283904: nam='PGA memory operation' ela= 5 p1=131072 p2=1 p3=0 obj#=-1 tim=11394292256137 WAIT #139737648283904: nam='PGA memory operation' ela= 3 p1=131072 p2=1 p3=0 obj#=-1 tim=11394292256171 WAIT #139737648283904: nam='PGA memory operation' ela= 3 p1=131072 p2=1 p3=0 obj#=-1 tim=11394292256202 WAIT #139737648283904: nam='PGA memory operation' ela= 3 p1=131072 p2=1 p3=0 obj#=-1 tim=11394292256225 WAIT #139737648283904: nam='PGA memory operation' ela= 3 p1=131072 p2=1 p3=0 obj#=-1 tim=11394292256245 WAIT #139737648283904: nam='PGA memory operation' ela= 3 p1=131072 p2=1 p3=0 obj#=-1 tim=11394292256268 WAIT #139737648283904: nam='PGA memory operation' ela= 6 p1=131072 p2=2 p3=0 obj#=-1 tim=11394292256300 FETCH #139737648283904:c=2809,e=5528,p=0,cr=53,cu=0,mis=0,r=1,dep=1,og=4,plh=1158320201,tim=11394292257970 FETCH #139737648283904:c=0,e=6,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=1158320201,tim=11394292258107 FETCH #139737648283904:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=1158320201,tim=11394292258179 FETCH #139737648283904:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=1158320201,tim=11394292258328 FETCH #139737648283904:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=1158320201,tim=11394292258468 FETCH #139737648283904:c=0,e=4,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=1158320201,tim=11394292258565 FETCH #139737648283904:c=0,e=3,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=1158320201,tim=11394292258667 FETCH #139737648283904:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=1158320201,tim=11394292258684 FETCH #139737648283904:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=1158320201,tim=11394292258701 FETCH #139737648283904:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=4,plh=1158320201,tim=11394292258717 . . Bind#0 oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00 oacflg=00 fl2=1000001 frm=00 csi=00 siz=24 off=0 kxsbbbfp=7f172f5ec0d0 bln=22 avl=06 flg=05 value=4294951327 . . EXEC #139737555718608:c=0,e=43,p=0,cr=0,cu=0,mis=0,r=0,dep=3,og=4,plh=1072382624,tim=11394292288879 FETCH #139737555718608:c=0,e=5,p=0,cr=2,cu=0,mis=0,r=0,dep=3,og=4,plh=1072382624,tim=11394292288893 CLOSE #139737555718608:c=0,e=1,dep=3,type=3,tim=11394292288907 BINDS #139737555718608: CLOSE #139737490479984:c=0,e=2,dep=0,type=0,tim=11394386009492 CLOSE #139737487630816:c=0,e=7,dep=0,type=0,tim=11394386009514 CLOSE #139737486385632:c=0,e=2,dep=0,type=0,tim=11394386009525 CLOSE #139737552667280:c=0,e=2,dep=0,type=0,tim=11394386009536 CLOSE #139737552725104:c=47,e=45,dep=0,type=0,tim=11394386009590 CLOSE #139737556565880:c=0,e=16,dep=0,type=0,tim=11394386009619 |
datapump 실행 후 trace 설정 해제
1 2 3 | SQL> alter system set events 'sql_trace {process: pname = dw | process: pname = dm} off'; System altered. |
참조 :
https://www.ora-solutions.net/web/2012/09/12/datapump-export-suffering-from-oracle-row-migration/
'ORACLE > Performance Tuning ' 카테고리의 다른 글
| 오라클 19c Statspack 리포트 생성 가이드 (2) | 2020.07.28 |
|---|---|
| sql 실행계획 확인 및 cpu 등 성능확인 스크립트 (3) | 2020.03.26 |
| SPM(SQL Plan Management) SQL PLAN BASELINE 이용한 Export / Import 테스트 (실행계획 변경) (0) | 2019.09.19 |
| SPM(SQL Plan Management) SQLSET 이용한 Export / Import 테스트 (실행계획 변경) (0) | 2019.09.19 |
| 개발자를위한 실시간 SQL 모니터링 DBMS_SQL_MONITOR 이용 (0) | 2019.02.18 |