During ECC6 upgrade project, a sub-project to migrate platform, from Solaris to AIX, should be performed.
Database is not changed and will remain Oracle database.
Unicode migration is not yet formally decided but will have little impact on this procedure.
To be able to minimize downtime, this migration is performed with Distribution Monitor tool.
By this way, resources from multiple servers are usable.
All optimization and configuration values in this document are closely dependant of used infrastructures.
For Next export runs, these optimization and optimization should be adjusted to match the new infrastructure configuration.
Hostname | Type Serveur | Processeur | OS | NB CPU Physique | NB CPU Virtuel | Fréquence (Mhz) | Mémoire (GB) |
PIL01 | T6320-T2 | UltraSPARC-T2 | Solaris 10 | 1 | 32 | 1165 | 32 |
SAPTECF4 | T6320-T2 | UltraSPARC-T2 | Solaris 10 | 1 | 32 | 1165 | 32 |
SAPTEUS2 | T6320-T2 | UltraSPARC-T2 | Solaris 10 | 1 | 32 | 1165 | 32 |
CTMTEST2 | T6320-T2 | UltraSPARC-T2 | Solaris 10 | 1 | 32 | 1165 | 32 |
SAPTEP08 | M3000 | SPARC64-VII | Solaris 10 | 1 | 8 | 2750 | 32 |
FRRMTEUSAQ01 | T3-1 | SPARC-T3 | Aix7.1 | 1 | 128 | 1649 | 32 |
Several prerequisites should be check on each used server:
export SAPSYSTEMNAME=<SID_SRC> export dbms_type=ORA export dbs_ora_tnsname=<SID_SRC> export ORACLE_SID=<SID_SRC> export ORACLE_BASE=/oracle export ORACLE_HOME=/export/SAP/users/<sid_src>adm export dbs_ora_schema=SAPR3 export NLS_LANG=AMERICAN_AMERICA.WE8DEC export JAVA_HOME=/usr/jre1.6.0_43 export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib:/lib:/oracle/client/11x_64/instantclient:/export/SAP/users/<sid_src>adm/exe export TNS_ADMIN=/export/SAP/oracle export DIR_LIBRARY=/export/SAP/users/<sid_src>adm/exe export PATH=$PATH:/export/SAP/users/<sid_src>adm/exe set -o emacs alias __A=`echo "20"` alias __B=`echo "16"` alias __C=`echo "06"` alias __D=`echo "02"` alias __H=`echo "01"` export PS1=$LOGNAME"@`hostname`:"$PWD">" stty erase ^? |
export SAPSYSTEMNAME=<SID_TGT> export dbms_type=ORA export dbs_ora_tnsname=<SID_TGT> export ORACLE_SID=<SID_TGT> export ORACLE_BASE=/oracle export ORACLE_HOME=/export/SAP/users/<sid_tgt>adm export dbs_ora_schema=SAPSR3 # si non unicode :export NLS_LANG=AMERICAN_AMERICA.WE8DEC export NLS_LANG=AMERICAN_AMERICA.UTF8 export JAVA_HOME=/usr/jre1.6.0_43 export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib:/lib:/oracle/client/11x_64/instantclient:/export/SAP/users/<sid_tgt>adm/exe export TNS_ADMIN=/export/SAP/oracle export DIR_LIBRARY=/export/SAP/users/<sid_tgt>adm/exe export PATH=$PATH:/export/SAP/users/<sid_tgt>adm/exe set -o emacs alias __A=`echo "20"` alias __B=`echo "16"` alias __C=`echo "06"` alias __D=`echo "02"` alias __H=`echo "01"` export PS1=$LOGNAME"@`hostname`:"$PWD">" stty erase ^? |
b6badm@pil01:/export/SAP/users/b6badm>cat /export/SAP/oracle/tnsnames.ora T32.WORLD= (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = SAP.WORLD) (PROTOCOL = TCP) (HOST = frrmteusapb01) (PORT = 3700) ) ) (CONNECT_DATA = (SID = T32) (GLOBAL_NAME = T32.WORLD) ) ) B6B.WORLD = (DESCRIPTION = (ADDRESS = (COMMUNITY = SAP.WORLD)(PROTOCOL = TCP)(HOST = saptep08)(PORT = 1529)) (CONNECT_DATA = (SID = B6B) (GLOBAL_NAME = B6B.WORLD) ) ) |
VBOX indexes have to be rebuilded and compressed ; This operation is being implemented on P3B.
Status of this operation has to be check for each P3B database copy usage and for P3B database itself with production system will be migrated.
Table | Operation | Oracle commande |
VBOX | Rebuilding and compression of all the partitions of the index VBOX~0 This operation is beeing implemented on P3B | For each partitions: ALTER INDEX sapr3."VBOX~0" rebuild partition <PARTITION_NAME> COMPRESS nologging parallel 8; ALTER INDEX sapr3."VBOX~0" MODIFY PARTITION <PARTITION_NAME> LOGGING; |
Oracle specific PL/SQL Splitter is used for transparent tables splitting. This package needs to be installed first.
sqlplus connect / as sysdba SQL> grant select on dba_extents to <sap-schema>; Grant succeeded. SQL> |
sqlplus /nolog @PRESPLITTER.sql <sap-schema> SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 19 15:33:56 2013 Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Directory created. Procedure created. old 1: grant read, write on directory "~TABLE_SPLITTER_RANGES_DIR" to &&1 new 1: grant read, write on directory "~TABLE_SPLITTER_RANGES_DIR" to SAPR3 Grant succeeded. old 1: grant select on dba_extents to &&1 new 1: grant select on dba_extents to SAPR3 Grant succeeded. old 1: grant execute on table_splitter_directory to &&1 new 1: grant execute on table_splitter_directory to SAPR3 Grant succeeded. Disconnected from Oracle Database 10g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Data Mining and Real Application Testing options |
sqlplus /nolog SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 19 15:33:56 2013 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. SQL> connect sapc47 Enter password:
SQL> start SPLITTER Table dropped. Table created. Package created. No errors. Package body created. No errors. SQL> |
DISTMON tool and its User’s Guide are available as attachment in OSS note n°855772
Current DISTMON version available, and used, is 1.9.2
File DISTMON.SAR should be uncared in directory /export/DISTMON
SAP tools is available under directories /export/SAP/R3xx_nonunicode
Versions used for each run are:
Main configuration directory, used during POC, is /export/DISTMON of target CIDB server; ie frrmteusapb01.
Main configuration file is distribution_monitor_cmd.properties
It use and refer to several others files, on same directory, which are described first.
This file allows specifying how packages and tables are splitted.
package_splitter_cmd.properties |
# Package Splitter options # # Largest N tables are extracted from packages # top=20 # Table size limit in MB # tableLimit=1000 # Package size limit in MB packageLimit=2000 tableFile=package_splitter_tables.txt |
This file is filled with table names that should be put in separate packages.
It contains 100 biggest tables identified in B6B.
These tables will be processed in next parts of this document depending on their categories
package_splitter_tables.txt |
ACCTCR ACCTIT ANLP ARFCSDATA ARIX_BKPF BALDAT BKPF BSAD BSAS BSE_CLR BSIS CDCLS CDHDR CE1Z001 CE3Z001 CE4Z001 CE4Z001_ACCT CKMI1 CMFP COBK COEP DBTABLOG E071K EDI40 EDIDS EPIDXC ESLL FILCA GLIDXA GLPCA GLSP IDOCREL KOCLU KONH KONP LIKP LIPS LIPSO2 MLCR MLCRP MLIT MLPP MSEG MSEGO1 MSEGO2 NAST OIB_DEFAULTS OICQ8 OIK01 OIRADBT OIRC_GMMH OIRC_SOCDIP REGUC REGUH RF048 RFBLG RSEG S033 S410 S603 SOC3 SOFFCONT1 SRRELROLES STXH STXL SWW_CONT SWWLOGHIST SWWWIHEAD TST03 VAKPA VBAK VBAP VBFA VBKD VBOX VBPA VBRK VBRP VBUK VBUP VRKPA VRPMA ZARIXBC1 ZARIXBC2 ZARIXCO2 ZARIXFI1 ZARIXFI2 ZARIXFI4 ZARIXIS17 ZARIXIS18 ZARIXIS19 ZARIXIS32 ZARIXIS37 ZARIXMM4 ZARIXSD3 ZARIXSD6 ZCO_PRICE_COND ZFI_GLPCA ZFI_Z9BSA ZSD_VBAP ZSY_ZCRIP ZZPCA1A |
This file is filled with table names that should be exported in unsorted mode.
This file is considerer with parameter unsortedExport=file
Rem : For first POC the file was filled with the100 biggest tables
Starting with second POC, all tables are exported by unsorted mode with parameter unsortedExport=all
In case of Unicode migration, cluster tables should be exported in sorted mode
R3load is able to identify automatically cluster tables and to export them in sorted mode without any other action.
By consequence, the file unsorted_export.txt is no more useful.
Rem: This file name is dependent of DISTMON version
This file is filled with table that all their indexes have to be created in parallel mode.
This file is considerer with parameter parallel_index_import=file
It contains same entries that package_splitter_tables.txt file.
|
ACCTCR ACCTIT ANLP ... ZSY_ZCRIP ZZPCA1A |
This file is filled with table that should be splitted
R3ta splits are based on file R3_hints.txt which should be in same directory as distmon tool.
It contains same entries that package_splitter_tables.txt file except for VBOX table which is performed manually.
table_splitter_tables.txt |
ACCTCR ACCTIT ANLP ... ZSY_ZCRIP ZZPCA1A |
R3ta splitting is perform by DISTMON tool itself (if parameter skipR3ta is not set).
Template of R3ta sentence :
Splits for VBOX table are generated manually.
Split rule is base on partition to have 1 WHR clause by partition:
Partitions timeframes are collected from file APPL1.SQL (generated by report SMIGR_CREATE_DDL) and then processed by Excel macro.
... COMPRESS FOR OLTP STORAGE (INITIAL 0000102400 K NEXT 0000102400 K MINEXTENTS 0000000001 MAXEXTENTS 2147483645 PCTINCREASE 0000 FREELISTS 001 FREELIST GROUPS 01) PARTITION BY RANGE ("FBUDA") ( PARTITION "VBOX_MIN_PART" VALUES LESS THAN ('20101201') TABLESPACE "&USER6&" COMPRESS FOR OLTP , PARTITION "VBOX_201012_PART" VALUES LESS THAN ('20110101') COMPRESS FOR OLTP , PARTITION "VBOX_201101_PART" VALUES LESS THAN ('20110201') COMPRESS FOR OLTP ... |
tab: VBOX WHERE ("FBUDA" < '20050101') tab: VBOX WHERE ("FBUDA" >= '20050101') AND ("FBUDA" < '20050701') tab: VBOX WHERE ("FBUDA" >= '20050701') AND ("FBUDA" < '20060101') tab: VBOX WHERE ("FBUDA" >= '20060101') AND ("FBUDA" < '20060701') tab: VBOX WHERE ("FBUDA" >= '20060701') AND ("FBUDA" < '20070101') tab: VBOX WHERE ("FBUDA" >= '20070101') AND ("FBUDA" < '20070701') tab: VBOX WHERE ("FBUDA" >= '20070701') AND ("FBUDA" < '20080101') tab: VBOX WHERE ("FBUDA" >= '20080101') AND ("FBUDA" < '20080201') tab: VBOX WHERE ("FBUDA" >= '20080201') AND ("FBUDA" < '20080301') ... tab: VBOX WHERE ("FBUDA" >= '20130901') AND ("FBUDA" < '20131001') tab: VBOX WHERE ("FBUDA" >= '20131001') AND ("FBUDA" < '20131101') tab: VBOX WHERE ("FBUDA" >= '20131101') AND ("FBUDA" < '20131201') tab: VBOX WHERE ("FBUDA" >= '20131201') AND ("FBUDA" < '20140101') tab: VBOX WHERE ("FBUDA" >= '20140101') |
frrmteusapb01:b6badm 9> ./where_splitter.sh -whereDir /export/SAP/commDir/R3ta_VBOX -whereLimit 1 + /usr/java6/jre/bin/java -showversion -cp ./.:./split.jar com.sap.inst.split.PackageSplitter -where -whereDir /export/SAP/commDir/R3ta_VBOX -whereLimit 1 java version "1.6.0" Java(TM) SE Runtime Environment (build pap3260sr9fp2-20110627_03(SR9 FP2)) IBM J9 VM (build 2.4, JRE 1.6.0 IBM J9 2.4 AIX ppc-32 jvmap3260sr9-20110624_85526 (JIT enabled, AOT enabled) J9VM - 20110624_085526 JIT - r9_20101028_17488ifx17 GC - 20101027_AA) JCL - 20110530_01 File not found: /export/SAP/commDir/R3ta_VBOX/package_splitter_cmd.properties Processing of 'VBOX.WHR' WHERE file: OK frrmteusapb01:b6badm 10> frrmteusapb01:b6badm 11> ls *WHR VBOX-1.WHR VBOX-14.WHR VBOX-19.WHR VBOX-23.WHR VBOX-28.WHR VBOX-32.WHR VBOX-37.WHR VBOX-6.WHR VBOX-10.WHR VBOX-15.WHR VBOX-2.WHR VBOX-24.WHR VBOX-29.WHR VBOX-33.WHR VBOX-38.WHR VBOX-7.WHR VBOX-11.WHR VBOX-16.WHR VBOX-20.WHR VBOX-25.WHR VBOX-3.WHR VBOX-34.WHR VBOX-39.WHR VBOX-8.WHR VBOX-12.WHR VBOX-17.WHR VBOX-21.WHR VBOX-26.WHR VBOX-30.WHR VBOX-35.WHR VBOX-4.WHR VBOX-9.WHR VBOX-13.WHR VBOX-18.WHR VBOX-22.WHR VBOX-27.WHR VBOX-31.WHR VBOX-36.WHR VBOX-5.WHR frrmteusapb01:b6badm 12> |
Splits for the 6 biggest tables cluster are generated online with R3ta tool
Splitting can be performed before SAP upgrade.
R3ta splits are based on file R3_hints.txt which should be in same working. Check that it contain right key field that should be used for splitting.
R3TA splitting |
nohup /export/SAP/R3xx_nonunicode/SOL/R3ta -f /export/SAP/commDir/PkgSplit/STXL.STR -table STXL%15 -o /export/SAP/commDir/R3ta_cluster/REGUC.WHR -check_utf8 -l /export/SAP/commDir/R3ta_cluster/tableSplitter.REGUC.log & nohup /export/SAP/R3xx_nonunicode/SOL/R3ta -f /export/SAP/commDir/PkgSplit/EDI40.STR -table EDI40%20 -o /export/SAP/commDir/R3ta_cluster/EDI40.WHR -check_utf8 -l /export/SAP/commDir/R3ta_cluster/tableSplitter.EDI40.log & nohup /export/SAP/R3xx_nonunicode/SOL/R3ta -f /export/SAP/commDir/PkgSplit/KOCLU.STR -table KOCLU%50 -o /export/SAP/commDir/R3ta_cluster/KOCLU.WHR -check_utf8 -l /export/SAP/commDir/R3ta_cluster/tableSplitter.KOCLU.log & nohup /export/SAP/R3xx_nonunicode/SOL/R3ta -f /export/SAP/commDir/PkgSplit/EPIDXC.STR -table EPIDXC%50 -o /export/SAP/commDir/R3ta_cluster/EPIDXC.WHR -check_utf8 -l /export/SAP/commDir/R3ta_cluster/tableSplitter.EPIDXC.log & nohup /export/SAP/R3xx_nonunicode/SOL/R3ta -f /export/SAP/commDir/PkgSplit/CDCLS.STR -table CDCLS%100 -o /export/SAP/commDir/R3ta_cluster/CDCLS.WHR -check_utf8 -l /export/SAP/commDir/R3ta_cluster/tableSplitter.CDCLS.log & nohup /export/SAP/R3xx_nonunicode/SOL/R3ta -f /export/SAP/commDir/PkgSplit/RFBLG.STR -table RFBLG%160 -o /export/SAP/commDir/R3ta_cluster/RFBLG.WHR -check_utf8 -l /export/SAP/commDir/R3ta_cluster/tableSplitter.RFBLG.log & nohup /export/SAP/R3xx_nonunicode/SOL/R3ta -f /export/SAP/commDir/PkgSplit/GLSP.STR -table GLSP%150 -o /export/SAP/commDir/R3ta_cluster/GLSP.WHR -check_utf8 -l /export/SAP/commDir/R3ta_cluster/tableSplitter.GLSP.log & |
frrmteusapb01:b6badm 9> ./where_splitter.sh -whereDir /export/SAP/commDir/R3ta_cluster -whereLimit 1 + /usr/java6/bin/java -showversion -cp ./.:./split.jar com.sap.inst.split.PackageSplitter -where -whereDir /export/SAP/commDir/R3ta_cluster -whereLimit 1 java version "1.6.0" Java(TM) SE Runtime Environment (build pap3260sr9fp2-20110627_03(SR9 FP2)) IBM J9 VM (build 2.4, JRE 1.6.0 IBM J9 2.4 AIX ppc-32 jvmap3260sr9-20110624_85526 (JIT enabled, AOT enabled) J9VM - 20110624_085526 JIT - r9_20101028_17488ifx17 GC - 20101027_AA) JCL - 20110530_01 File not found: /export/SAP/commDir/R3ta_cluster/package_splitter_cmd.properties Processing of 'CDCLS.WHR' WHERE file: OK Processing of 'EPIDXC.WHR' WHERE file: OK Processing of 'KOCLU.WHR' WHERE file: OK Processing of 'REGUC.WHR' WHERE file: OK Processing of 'RFBLG.WHR' WHERE file: OK Processing of 'EDI40.WHR' WHERE file: OK Processing of 'GLSP.WHR' WHERE file: OK b6badm@frrmteusapb01:/home/b6badm> |
All others biggest tables are splitted by PL/SQL Splitter (Cf. OSS note n° 1043380 - Efficient Table Splitting for Oracle Databases)
plsql_splitter_TRANSP.sql |
set echo off; set termout off; whenever sqlerror exit sql.sqlcode whenever sqlerror continue spool plsql_splitter.log start splitter; SELECT 'Calling PL/SQL -- ON -- ' || TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') FROM dual; exec table_splitter.ranges ('CE4Z001_ACCT', 'ROWID', 20); exec table_splitter.ranges ('BSIS', 'ROWID', 20); exec table_splitter.ranges ('TST03', 'ROWID', 20); exec table_splitter.ranges ('SOC3', 'ROWID', 10); exec table_splitter.ranges ('KONP', 'ROWID', 10); … exec table_splitter.ranges ('E071K', 'ROWID', 4); exec table_splitter.ranges ('RSEG', 'ROWID', 2); SELECT 'Calling PL/SQL -- OFF -- ' || TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') FROM dual; spool off; |
All tables splitting should be consolidated into the single directory /export/SAP/commDir/R3ta
frrmteusapb01:b6badm 9> cp ../R3ta_VBOX/*WHR . frrmteusapb01:b6badm 10> cp ../R3ta_cluster/*WHR . frrmteusapb01:b6badm 11> cp /tmp/ms_split/*WHR . |
This file is used to specify the tables that need to be addressed first. Largest tables larger and/or those ones having longest processing times are relevant.
For splitted tables, you must specify all of their packages (note: the program R3ta tends to add an additional package to the number requested).
Presence of the file in DISTMON directory is enough to take into account its.
|
VBOX-1 … VBOX-39 RFBLG-1 … RFBLG-161 CDCLS-1 … CDCLS-101 KOCLU-1 … KOCLU-51 EPIDXC-1 … EPIDXC-51 GLSP-1 … GLSP-150 REGUC-1 … REGUC-16 EDI41-1 … EDI40-21 REPOSRC-1 … REPOSRC-20 STXL-1 … STXL-15 DBTABLOG-1 DBTABLOG-2 CKMI1-1 … CKMI1-4 ZSY_ZCRIP-1 … ZSY_ZCRIP-5 CE1Z001-1 … CE1Z001-5 LIPS-1 … LIPS-5 ZARIXCO2-1 … ZARIXCO2-20 |
This file is used to distribute specifics tables on dedicated servers. Tables having longest processing times (largest, cluster, …) are relevant.
For splitted tables, you must specify only table name, not all of their packages.
Presence of the file in DISTMON directory is enough to take into account its.
|
VBOX=frrmteusapb01 RFBLG=saptep08 CDCLS=sapteus2 EPIDXC=saptecf4 KOCLU=pil01 REGUC=frrmteusapb01 EDI40=pil01 GLSP=ctmtest2 ZSY_ZCRIP=ctmtest2 ZARIXIS19=ctmtest2 CE1Z001=saptecf4 CKMI1=saptecf4 MLIT=saptecf4 |
This report should be executed before DISTMON preparation. It generates DDL statements for “special” tables like partionned tables (VBOX).
# # ORACLE : NATIVE SQL EXPORT GENERATED AT 20130305170243 # tab: VBOX sql: CREATE TABLE "VBOX" ("MANDT" VARCHAR2 (000003) DEFAULT '000' NOT NULL, "KAPPL" VARCHAR2 (000002) DEFAULT ' ' NOT NULL, "KOTABNR" VARCHAR2 (000003) DEFAULT '000' NOT NULL, "VAKEY" VARCHAR2 (000050) DEFAULT ' ' NOT NULL, "FBUDA" VARCHAR2 (000008) DEFAULT '00000000' NOT NULL, "VBELN" VARCHAR2 (000010) DEFAULT ' ' NOT NULL, "POSNR" VARCHAR2 (000006) DEFAULT '000000' NOT NULL) PCTFREE 10 PCTUSED 40 INITRANS 001 TABLESPACE &USR14& COMPRESS FOR OLTP STORAGE (INITIAL 0000102400 K NEXT 0000102400 K MINEXTENTS 0000000001 MAXEXTENTS 2147483645 PCTINCREASE 0000 FREELISTS 001 FREELIST GROUPS 01) PARTITION BY RANGE ("FBUDA") ( PARTITION "VBOX_MIN_PART" VALUES LESS THAN ('20101201') TABLESPACE "&USER6&" COMPRESS FOR OLTP , PARTITION "VBOX_201012_PART" VALUES LESS THAN ('20110101') COMPRESS FOR OLTP , PARTITION "VBOX_201101_PART" VALUES LESS THAN ('20110201') COMPRESS FOR OLTP , … , PARTITION "VBOX_201311_PART" VALUES LESS THAN ('20131201') COMPRESS FOR OLTP , PARTITION "VBOX_MAX_PART" VALUES LESS THAN (MAXVALUE) TABLESPACE "&USER6&" COMPRESS FOR OLTP ) ; ind: VBOX~0 sql: CREATE UNIQUE INDEX "VBOX~0" ON "VBOX" ("MANDT", "KAPPL", "KOTABNR", "VAKEY", "FBUDA", "VBELN", "POSNR") LOCAL PCTFREE 10 INITRANS 002 TABLESPACE &USR14& COMPRESS 4 STORAGE (INITIAL 0000000064 K NEXT 0000102400 K MINEXTENTS 0000000001 MAXEXTENTS UNLIMITED PCTINCREASE 0000 FREELISTS 001) PARALLEL ; ALTER INDEX "VBOX~0" NOPARALLEL ; ind: VBOX~A sql: CREATE INDEX "VBOX~A" ON "VBOX" ("MANDT", "KAPPL", "VBELN") LOCAL PCTFREE 10 INITRANS 002 TABLESPACE &USR14& COMPRESS 3 STORAGE (INITIAL 0000000064 K NEXT 0000102400 K MINEXTENTS 0000000001 MAXEXTENTS UNLIMITED PCTINCREASE 0000 FREELISTS 001) PARALLEL ; ALTER INDEX "VBOX~A" NOPARALLEL ; |
This file is the central configuration file used by DISTMON.
should be the set when all others optimization tips have been activated
-rwxrwxrwx 1 xl024097 xl024097 24609 Apr 15 15:48 export_time_SAPTEUS2.txt -rwxrwxrwx 1 xl024097 xl024097 16189 Apr 15 15:48 export_time_SAPTEP08.txt -rwxrwxrwx 1 xl024097 xl024097 15500 Apr 15 15:48 export_time_SAPTECF4.txt -rwxrwxrwx 1 xl024097 xl024097 15953 Apr 15 15:48 export_time_PIL01.txt -rwxrwxrwx 1 xl024097 xl024097 35055 Apr 15 15:48 export_time_FRRMTEUSAPB01.txt -rwxrwxrwx 1 xl024097 xl024097 13347 Apr 15 15:48 export_time_CTMTEST2.txt |
Remark:
commDir=/export/SAP/commDir # Si migration Unicode, les paramètres dataCodepage et dbCodepage ne sont pas à specifier # dataCodepage=1100 # dbCodepage=1100 unsortedExport=all parallel_index_import=file # Skipper les étapes R3ldctl / R3szchk / R3ta en fonction du statut de préparation skipR3ldctl r3ldctlExe=/export/SAP/R3xx_nonunicode/SOL/R3ldctl r3ldctlArgs= skipR3szchk r3szchkExe=/export/SAP/R3xx_nonunicode/SOL/R3szchk r3szchkArgs=-s DB -t ora B6B 731 skipR3ta r3taExe=/export/SAP/R3xx_nonunicode/SOL/R3ta parallelR3ta=20 # Commenter le paramètres checkWhrFile et spécifier le paramètre skipWhrChk pour être compatible avec les fichiers WHR générés par le PL/SQL splitter #checkWhrFiles skipWhrChk monitorTimeout=30 #Additional R3load arguments for the TASK phase r3load.export.taskArgs= r3load.import.taskArgs= #Additional R3load arguments for the LOAD phase r3load.export.loadArgs= r3load.import.loadArgs=-loadprocedure fast timeDir=/export/SAP/commDir/time_DIR hostNames=saptep08;sapteus2;pil01;frrmteusapb01;ctmtest2;saptecf4 saptep08.dataDirs=/export/SAP/saptep08 saptep08.exportJobNum=40 saptep08.importJobNum=5 saptep08.exportR3loadExe=/export/SAP/R3xx_nonunicode/SOL/R3load saptep08.importR3loadExe=/export/SAP/R3xx_unicode/SOL/R3load sapteus2.dataDirs=/export/SAP/sapteus2 sapteus2.exportJobNum=5 sapteus2.importJobNum=5 sapteus2.exportR3loadExe=/export/SAP/R3xx_nonunicode/SOL/R3load sapteus2.importR3loadExe=/export/SAP/R3xx_unicode/SOL/R3load pil01.dataDirs=/export/SAP/pil01 pil01.exportJobNum=5 pil01.importJobNum=5 pil01.exportR3loadExe=/export/SAP/R3xx_nonunicode/SOL/R3load pil01.importR3loadExe=/export/SAP/R3xx_unicode/SOL/R3load ctmtest2.dataDirs=/export/SAP/ctmtest2 ctmtest2.exportJobNum=5 ctmtest2.importJobNum=5 ctmtest2.exportR3loadExe=/export/SAP/R3xx_nonunicode/SOL/R3load ctmtest2.importR3loadExe=/export/SAP/R3xx_unicode/SOL/R3load saptecf4.dataDirs=/export/SAP/saptecf4 saptecf4.exportJobNum=5 saptecf4.importJobNum=5 saptecf4.exportR3loadExe=/export/SAP/R3xx_nonunicode/SOL/R3load saptecf4.importR3loadExe=/export/SAP/R3xx_unicode/SOL/R3load frrmteusapb01.dataDirs=/export/SAP/frrmteusapb01 frrmteusapb01.exportJobNum=10 frrmteusapb01.importJobNum=10 frrmteusapb01.exportR3loadExe=/export/SAP/R3xx_nonunicode/AIX/R3load frrmteusapb01.importR3loadExe=/export/SAP/R3xx_unicode/AIX/R3load migrationKey=17qgf5M50Dk01eqtd1Uw1aq2 |
A best practice to avoid any former log/status files is to clean them before launching DISTMON preparation phase
cd /export/SAP/commDir
rm -rf SAPTEUS2 PIL01 FRRMTEUSAPB01 CTMTEST2 SAPTECF4 SAPTEP08 info *_time* *.SGN
|
After each R3ldctl execution, these files should be checked and adjusted by modifying « schema id ».
Substitution is not done correctly and should be executed manually before launching DISTMON preparation
BEFORE | AFTER |
|
|
Like this file is move in another directory during DISTMON preparation execution, it could be useful to check (again) the existing of file APPL1.SQL before launching again DISTMON preparation.
In same time, a additonnal check regarding right data class could be done 😉 (cf. § 3.8 « Report SMIGR_CREATE_DDL »)
root@frrmteusapb01:/export/SAP/commDir/MigrDdl> pwd /export/SAP/commDir/MigrDdl root@frrmteusapb01:/export/SAP/commDir/MigrDdl> ls -ltr total 24 -rwxrwxrwx 1 root system 95 Mar 18 15:47 SQLFiles.LST -rwxrwxrwx 1 b6badm 200 5772 Mar 18 15:47 APPL1.SQL root@frrmteusapb01:/export/SAP/commDir/MigrDdl> |
Just for information, list of table that should not be compressed in target database are specified in files DDLORA_TPL and DDLORA_LRG.TPL in part « negcpr ».
Command to launch DISTMON preparation execution
./distribution_monitor.sh -p
|
chmod –R 777 /export/SAP
|
Control the distribution of packages on different servers is useful to move towards an end of treatment packages at the same time on each of these servers.
grep "The following number of packages were assigned" distribution_monitor_prepare.log The following number of packages were assigned: CTMTEST2 : 82. The following number of packages were assigned: SAPTECF4 : 83. The following number of packages were assigned: SAPTEP08 : 71. The following number of packages were assigned: SAPTEUS2 : 77. The following number of packages were assigned: PIL01 : 72. The following number of packages were assigned: FRRMTEUSAPB01 : 72. Rem: avec le nb R3load sapteb08=20 & frrmteusapb01=60 (au lieu de 40 / 40) The following number of packages were assigned: FRRMTEUSAPB01 : 120. The following number of packages were assigned: CTMTEST2 : 90. The following number of packages were assigned: SAPTECF4 : 90. The following number of packages were assigned: SAPTEUS2 : 80. The following number of packages were assigned: PIL01 : 56. The following number of packages were assigned: SAPTEP08 : 20.
Rem: avec le nb R3load sapteb08=20 & frrmteusapb01=60 (au lieu de 40 / 40) et avec le paramètre de distribution timeDir=/export/SAP/commDir/time_DIR APRES le POC2.2 The following number of packages were assigned: FRRMTEUSAPB01 : 156. The following number of packages were assigned: CTMTEST2 : 88. The following number of packages were assigned: SAPTECF4 : 113. The following number of packages were assigned: SAPTEUS2 : 1. The following number of packages were assigned: PIL01 : 97. The following number of packages were assigned: SAPTEP08 : 1. |
Program and last version of configuration file distribution_monitor_cmd.properties should be copied on all pool of servers
cd /export/SAP cd <host>_DISTMON sapteus2;pil01;ctmtest2;saptecf4;sapteup08 cp /export/SAP/DISTMON/dist* . cp /export/SAP/DISTMON/*.jar .
|
Some Oracle adjustments are required, mainly on larges cluster and pool tables to optimize data export.
Be careful, all indexes creation should not be perform during real production activity: They might have impact on end users activity.
I advise to create them after R3ldctl program has run. By this way these additional indexes will not be created on target database.
Table | Operation | Oracle commande |
CDCLS | Create a new index on the key split and collect histograms on indexed columns. | CREATE INDEX sapr3.CDCLS_TEST ON sapr3.CDCLS (CHANGENR) TABLESPACE PSAPCDLSD nologging parallel 8; ALTER INDEX sapr3.GLSP_TEST logging noparallel; execute DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SAPR3',tabname => 'CDCLS',estimate_percent =>1,method_opt => 'for all indexed columns size auto', cascade => true,degree=>8); |
GLSP | Create a new index on the key split and collect histograms on indexed columns. | CREATE INDEX sapr3.GLSP_TEST ON sapr3.GLSP (VARKEY) TABLESPACE PSAPREORGD nologging parallel 8; ALTER INDEX sapr3.GLSP_TEST logging noparallel; execute DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SAPR3',tabname => 'GLSP',estimate_percent =>1,method_opt => 'for all indexed columns size auto', cascade => true,degree=>8); |
RFBLG | Create a new index on the key split and collect histograms on indexed columns. | CREATE INDEX sapr3. RFBLG_TEST ON sapr3.RFBLG (BELNR) TABLESPACE PSAPCDLSI nologging parallel 8; ALTER INDEX sapr3. RFBLG_TEST logging noparallel; execute DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SAPR3',tabname => 'RFBLG',estimate_percent =>1,method_opt => 'for all indexed columns size auto', cascade => true,degree=>8); |
KOCLU | Create a new index on the key split and collect histograms on indexed columns. | CREATE INDEX sapr3.KOCLU_TEST ON sapr3.KOCLU (KNUMV) TABLESPACE PSAPCDLSI nologging parallel 8; ALTER INDEX sapr3.KOCLU_TEST logging noparallel; execute DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SAPR3',tabname => 'KOCLU',estimate_percent =>1,method_opt => 'for all indexed columns size auto', cascade => true,degree=>8); |
EPIDXC | Create a new index on the key split and collect histograms on indexed columns. | CREATE INDEX sapr3.EPIDXC_TEST ON sapr3.EPIDXC (DOCNR) TABLESPACE PSAPLONGI nologging parallel 8; ALTER INDEX sapr3.EPIDXC_TEST logging noparallel; execute DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SAPR3',tabname => 'EPIDXC',estimate_percent =>1,method_opt => 'for all indexed columns size auto', cascade => true,degree=>8); |
REGUC | Create a new index on the key split and collect histograms on indexed columns. | CREATE INDEX sapr3.REGUC_TEST ON sapr3.REGUC (LAUFD) TABLESPACE PSAPLONGI nologging parallel 8; ALTER INDEX sapr3.REGUC_TEST logging noparallel; execute DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SAPR3',tabname => 'REGUC',estimate_percent =>1,method_opt => 'for all indexed columns size auto', cascade => true,degree=>8); |
EDI40 | Create a new index on the key split and collect histograms on indexed columns. | CREATE INDEX sapr3.EDI40_TEST ON sapr3.EDI40 (DOCNUM) TABLESPACE PSAPSOCI nologging parallel 8; ALTER INDEX sapr3.EDI40_TEST logging noparallel; execute DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SAPR3',tabname => 'EDI40',estimate_percent =>1,method_opt => 'for all indexed columns size auto', cascade => true,degree=>8); |
A best practice to avoid any former dump/log/status files is to clean them before launching DISTMON export phase.
A good solution is to used dedicated script:
A less good solution is to it manually:
cd /export/SAP
cd <host>_DISTMON sapteus2;pil01;ctmtest2;saptecf4 ;saptep08 rm R3load* *.lck *state* *_time* *display.log *console.log *export.log *import.log *monitor.log
cd /export/DISTMON rm R3load* *.lck *state* *_time* *display.log *console.log *export.log *import.log *monitor.log
cd /export/SAP/<host> sapteus2;pil01;frrmteusapb01;ctmtest2;saptecf4;saptep08 rm –rf *
|
Following script check that each table is define only once in splitted packages
b6badm@pil01 >ksh check.ksh il y a 90774 tables dans les fichiers STR |
On each server, check java setting:
b6badm@frrmteusapb01:/home/b6badm> b6badm@frrmteusapb01:/home/b6badm>echo $JAVA_HOME /usr/java6 b6badm@frrmteusapb01:/home/b6badm> b6badm@frrmteusapb01:/home/b6badm>which java /usr/java6/jre/bin/java b6badm@frrmteusapb01:/home/b6badm> b6badm@frrmteusapb01:/home/b6badm>java -version java version "1.6.0" Java(TM) SE Runtime Environment (build pap3260sr9fp2-20110627_03(SR9 FP2)) IBM J9 VM (build 2.4, JRE 1.6.0 IBM J9 2.4 AIX ppc-32 jvmap3260sr9-20110624_85526 (JIT enabled, AOT enabled) J9VM - 20110624_085526 JIT - r9_20101028_17488ifx17 GC - 20101027_AA) JCL - 20110530_01 b6badm@frrmteusapb01:/home/b6badm> |
On each server:
nohup ./distribution_monitor.sh -e &
|
cd /export/DISTMON chmod 777 R3load* cd /export/SAP/<host> chmod –R 777 *
|
On each server:
nohup ./distribution_monitor.sh -i &
|
This tool is launch with following command:
./distribution_monitor.sh -d
|
It’s not very “user friendly“ and very slow … but have different interesting views.
To follow running and waiting packages on current server:
b6badm@pil01:/export/SAP/users/b6badm> /usr/xpg4/bin/grep -E '=0|=\?' /export/SAP/*_DISTMON/export_state.properties | cut -d- -f1 | sort| uniq -c 1 /export/SAP/ctmtest2_DISTMON/export_state.properties:BKPF 5 /export/SAP/ctmtest2_DISTMON/export_state.properties:CE1Z001 1 /export/SAP/ctmtest2_DISTMON/export_state.properties:VBFA 1 /export/SAP/frrmteusapb01_DISTMON/export_state.properties:BSAD 2 /export/SAP/frrmteusapb01_DISTMON/export_state.properties:CKMI1 2 /export/SAP/frrmteusapb01_DISTMON/export_state.properties:COEP 2 /export/SAP/frrmteusapb01_DISTMON/export_state.properties:LIPS 5 /export/SAP/frrmteusapb01_DISTMON/export_state.properties:MSEG 1 /export/SAP/frrmteusapb01_DISTMON/export_state.properties:VBAK 1 /export/SAP/frrmteusapb01_DISTMON/export_state.properties:VBKD 14 /export/SAP/frrmteusapb01_DISTMON/export_state.properties:VBRP 1 /export/SAP/frrmteusapb01_DISTMON/export_state.properties:VRKPA 1 /export/SAP/frrmteusapb01_DISTMON/export_state.properties:ZARIXSD6 2 /export/SAP/pil01_DISTMON/export_state.properties:ZSY_ZCRIP 2 /export/SAP/sapteus2_DISTMON/export_state.properties:S033 2 /export/SAP/sapteus2_DISTMON/export_state.properties:VBPA |
By looking in file /export/SAP/commDir/*_time.txt (ou html) you can follow progress of package export.
Remark: in case of package splitted by PL/SQL splitter, “end-date” is filled when first where clause is processed and might be wrong in case of all where clauses are not yet processing.
dataDirs=/export/SAP/ctmtest2/DATA: export= html= installDir=/export/SAP/ctmtest2/exportInstallDir/ top=50 trace=all ------------------------------------------------------------------------------- package time start date end date size MB MB/min ------------------------------------------------------------------------------- GLSP-148 26:54:47 2013-03-26 19:33 2013-03-27 22:28 220.88 0.14 GLSP-149 26:27:49 2013-03-26 19:41 2013-03-27 22:09 218.84 0.14 BKPF-6 23:48:38 2013-03-27 01:11 2013-03-28 01:00 890.40 0.62 LIKP-2 23:31:36 2013-03-27 07:30 2013-03-28 07:01 1136.01 0.80 BKPF-1 23:16:37 2013-03-27 00:46 2013-03-28 00:02 827.47 0.59 ACCTCR-2 23:16:30 2013-03-27 00:34 2013-03-27 23:50 659.94 0.47 GLSP-147 23:11:54 2013-03-26 19:25 2013-03-27 18:37 217.80 0.16 BKPF-7 21:37:00 2013-03-27 01:21 2013-03-27 22:58 883.22 0.68 BKPF-5 19:50:58 2013-03-27 01:07 2013-03-27 20:58 801.16 0.67 CE1Z001-14 18:21:33 2013-03-27 04:44 2013-03-27 23:06 634.97 0.58 CE1Z001-15 16:37:37 2013-03-27 06:03 2013-03-27 22:40 648.32 0.65 ACCTCR-3 16:13:28 2013-03-27 00:34 2013-03-27 16:48 659.54 0.68 BKPF-2 16:11:59 2013-03-27 00:46 2013-03-27 16:58 809.33 0.83 BKPF-3 16:04:36 2013-03-27 00:52 2013-03-27 16:56 803.48 0.83 BKPF-4 15:01:13 2013-03-27 01:00 2013-03-27 16:02 782.15 0.87 GLSP-150 14:41:49 2013-03-26 19:41 2013-03-27 10:23 219.39 0.25 CE1Z001-13 14:31:38 2013-03-27 03:45 2013-03-27 18:16 635.44 0.73 GLSP-113 11:45:29 2013-03-26 14:13 2013-03-27 01:58 234.04 0.33 LIKP-3 10:51:53 2013-03-27 07:37 2013-03-27 18:29 1151.05 1.77 COBK-1 10:40:17 2013-03-27 06:23 2013-03-27 17:04 532.60 0.83 GLSP-134 9:46:12 2013-03-26 16:12 2013-03-27 01:58 201.72 0.34 SOC3-2 9:15:29 2013-03-27 11:19 2013-03-27 20:35 7903.10 14.23 |
To follow running Oracle long operation:
select LOGON_TIME,STATUS,ELAPSED_SECONDS, TIME_REMAINING, trunc(100*ELAPSED_SECONDS/(ELAPSED_SECONDS+TIME_REMAINING),2) AS PERCENT, SQL_TEXT from v$session, v$sqlarea, v$session_longops WHERE v$session.SQL_ID = v$sqlarea.SQL_ID AND v$session.SID = v$session_longops.SID AND TIME_REMAINING > 0 ORDER by TIME_REMAINING DESC; |
To have a global overview of export status, use script /export/SAP/scripts/status.ksh
ksh /export/SAP/scripts/status.ksh A faire 99 saptep08 55 sapteus2 En cours 1 ctmtest2 2 pil01 2 saptecf4 40 saptep08 39 sapteus2 Fait 302 ctmtest2 172 frrmteusapb01 167 pil01 204 saptecf4 152 saptep08 155 sapteus2 Failed |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
5 | |
2 | |
2 | |
2 | |
2 | |
2 | |
2 | |
2 | |
1 | |
1 |