Heteregeneous copy with distribution monitor
1. Context
1.1. Scenario
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.
1.2. Big Pictures
1.3. Remarks
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 |
- Source database server SAPTEP08
- Target database server FRRMTEUSAPB01
2. Prerequisites
2.1. Operation System level
Several prerequisites should be check on each used server:
- Java virtual machine 1.6 should be installed in /usr/jre1.6.0_43
- Oracle client 11 should be installed in /oracle/client/11x_64
- OS groups sapsys and dba should have same GID on all source/target AS and CI/DB servers (normally 200 and 201)
- OS users <sid_src>adm and <sid_tgt>adm
- should have same UID on all source/target AS and CI/DB servers
- should belong to groups sapsys and dba
- should use ksh shell
- should have home directory in nfs share /export/SAP/users/<sid>adm
- Theses home directories should contains a exe sub-directory for sap kernel and a tnsping program
- Non Unicode for source system
- Unicode for target system (in case of Unicode migration)
- .profile
|
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 ^? |
- .profile
|
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 ^? |
- Home directories should contain a copy of CIDB network directory (/oracle/SID/112_64/network)
- File /export/SAP/oracle/tnsnames.ora should contain tnsname entries to be able to connect both source and target database
|
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) ) ) |
2.2. Oracle
2.2.1. VBOX Indexes
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; |
2.2.2. PL/SQL Splitter
Oracle specific PL/SQL Splitter is used for transparent tables splitting. This package needs to be installed first.
- Create necessary privileges for the schema user which is the owner of the tables to be split such as sapr3 by connecting to the database as SYSDBA:
|
sqlplus connect / as sysdba
SQL> grant select on dba_extents to <sap-schema>; Grant succeeded. SQL> |
- Select the Output Directory and create it in Oracle:
- Save attached file presplitter.txt from OSS note 1043380 to PRESPLITTER.sql
- Launch SQL script
|
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 |
- Install PL/SQL Package
- Save attached file splitter_hint.txt from OSS note 1043380 to SPLITTER.sql
- Launch SQL script
|
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> |
2.3. DISTMON tool
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
2.1. SAP tools
SAP tools is available under directories /export/SAP/R3xx_nonunicode
Versions used for each run are:
- R3load 720_EXT_REL Patch Level 413
- R3ldctl 720_EXT_REL Patch Level 413
- R3szchk 720_EXT_REL Patch Level 413
- R3ta 720_EXT_REL Patch Level 413
3. DISTMON Configuration
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.
3.1. File package_splitter_cmd.properties
This file allows specifying how packages and tables are splitted.
- Parameter top is commented; list of tables is managed by tableFile parameter
- Parameter tableLimit is commented; list of tables is managed by tableFile parameter
- Parameter packageLimit is set to 2000
- Parameter tableFile specify tables be excluded from packages
|
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 |
3.2. File 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
- Transparent tables
- Cluster tables
- VBOX table (partitionned table)
|
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 |
3.3. File unsorted_export.txt
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.
3.4. File parallel_index.txt / parallelIndexImport.txt
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 |
3.5. TABLES SPLITTING
3.5.1. File table_splitter_tables.txt
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 :
- /export/SAP/R3xx_nonunicode/SOL/R3ta -f /export/SAP/commDir/PkgSplit/VAKPA.STR -table VAKPA%2 -o /export/SAP/commDir/R3ta/VAKPA.WHR -check_utf8 -l tableSplitter.VAKPA.log &
3.5.2. Table VBOX
Splits for VBOX table are generated manually.
Split rule is base on partition to have 1 WHR clause by partition:
- 25 partitions of 25 Go (280 Go of data)
- 1 partition of 500 Go (250 Go of data – 100Go of data on P3B)
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 … |
- File VBOX .WHR is filled manually with formatted sentences:
|
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’) |
- The file need to be splitted so that each WHR file contains only one WHR condition
- From server FRRMTEUSAPB01
- In directory /export/SAP/commDir/R3ta_VBOX
- File VBOX.WHR is rename VBOX.WHR.old
- Generated files are named VBOX-nnn.WHR
|
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> |
3.5.3. Tables cluster & pool
Splits for the 6 biggest tables cluster are generated online with R3ta tool
Splitting can be performed before SAP upgrade.
3.5.3.1. R3ta
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.
- Table cluster R3ta splitting is performed
- From server FRRMTEUSAPB01
- In directory
|
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 & |
- Files need to be splitted so that each WHR file contains only one WHR condition
- From server FRRMTEUSAPB01
- In directory
- Files <table>.WHR are rename <table>.WHR.old
- Generated files are named <table>-nnn.WHR
|
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> |
3.5.4. Tables transparent
All others biggest tables are splitted by PL/SQL Splitter (Cf. OSS note n° 1043380 – Efficient Table Splitting for Oracle Databases)
- Tables transparent splitting is performed
- From server
- In directory
- Split command sqlplus sapr3/<pwd> @plsql_splitter_TRANSP.sql
- Target directory for PL/SQL splitter is /tmp/ms_split (should be change during next package installation)
- <table>.WHR files contain several where clauses but should not be splitted again like VBOX / cluster tables.
- Splitting for all biggest transparent tables : 13 mns
|
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; |
3.5.5. Consolidation
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 . |
3.6. File OrderBy.txt
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 |
3.7. File distribution.txt
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 |
3.8. Report SMIGR_CREATE_DDL
This report should be executed before DISTMON preparation. It generates DDL statements for “special” tables like partionned tables (VBOX).
- Target directory is /export/SAP/commDir/MigrDdl
- If Unicode migration is confirmed, check the box
- Due to tablespace architecture adjustment, a special check has to be validated : correct TABART should be assigned to DDL statements (highlight in yellow)
|
# # 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 ; |
3.9. File distribution_monitor_cmd.properties
This file is the central configuration file used by DISTMON.
- hostNames Pool of servers used by DISTMON
- hostNames.dataDirs Data dump directory for each server
- commDir Central DISTMON directory
- r3ldctlExe Path to R3ldctl
- r3szchkExe Path to R3szchk
- r3szchkArgs Refer to § 6.2.5 od DISTMON User’s Guide
- r3taExe Path to R3ta
- parallelR3ta Number of R3ta process in paralell mode
- dataCodepage Should be specified if export is in non-Unicode mode
- dbCodepage Should be specified if export is in non-Unicode mode
- r3load.import.loadArgs Should be specified to use Direct Path
- migrationKey Should be generated on SAP Marketplace for each migration
- timeDir
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:
- Parameters <host>.exportJobNum and <host>.importJobNum are used to distribute packages on pool of servers
- They should be adjusted regarding servers caracteristics : CPU (number/frequency) and memory
|
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 |
4. DISTMON preparation
4.1. Prerequisite
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
|
4.2. Files DDLORA.TPL and DDL_LRG.TPL
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
- String @SCHEMAID@ should be replace by target schema ID: SR3 in our case
- For table and index storage parameters
|
BEFORE |
AFTER |
|
|
|
4.3. File APPL1.SQL
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 »)
- Target directory is /export/SAP/commDir/MigrDdl
- Vérifier le contenu (DATA CLASSE à
|
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> |
4.4. Tables compressions : exclusion
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 ».
4.5. Préparation
Command to launch DISTMON preparation execution
- Source directory is /export/DISTMON
|
./distribution_monitor.sh -p
|
4.6. Unix permissions
- Due to ours N@!@N# UID/GID issues, change permissions on all contents of /export/SAP directory
|
chmod –R 777 /export/SAP
|
4.7. Additionnal Checks
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.
- Source directory is /export/DISTMON
|
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. |
4.8. Distribution of file distribution_monitor_*
Program and last version of configuration file distribution_monitor_cmd.properties should be copied on all pool of servers
- Source directory is /export/SAP
|
cd /export/SAP cd <host>_DISTMON sapteus2;pil01;ctmtest2;saptecf4;sapteup08 cp /export/SAP/DISTMON/dist* . cp /export/SAP/DISTMON/*.jar .
|
5. DISTMON export / import
5.1. Oracle – Indexes
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); |
5.1. Prerequisite
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:
- Source directory is /export/SAP/scripts/nettoyage.ksh
A less good solution is to it manually:
- Source directory is /export/SAP
|
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 *
|
5.2. Checks
Following script check that each table is define only once in splitted packages
- Source directory is /export/SAP/scripts/check.ksh
|
b6badm@pil01 >ksh check.ksh il y a 90774 tables dans les fichiers STR |
5.3. Environment variables
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> |
5.4. EXPORT
On each server:
- Source directory is /export/DISTMON
|
nohup ./distribution_monitor.sh -e &
|
5.4.1. Unix permissions
- Due to ours N@!@N# UID/GID issues, change permissions on following files/directories before lunching import:
|
cd /export/DISTMON chmod 777 R3load* cd /export/SAP/<host> chmod –R 777 *
|
5.5. IMPORT
On each server:
- Source directory is /export/DISTMON
|
nohup ./distribution_monitor.sh -i &
|
6. DISTMON monitoring
6.1. DISTMON monitor
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.
6.1. Scripts
6.1.1. Running and waiting packages – I
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 |
6.1.1. Running and waiting packages – II
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 |
6.1.2. Long Operations
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; |
6.1.3. Overview Status
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 |
Nice one! Never tried the Oracle splitter package you mentioned. Is it really worth it?
yes. It is very efficitient. With it you are sure that oracle take the quickest way to get data of package (no wrong execution plan as you can have with R3ta).
The only inconvenient is that you have to calculate the split just before the extract (in downtime, no activity between the split calculation and the extract). But it is not a big problem because the splitting computation is quicker that R3ta.
It sure is quick! I was surprised to see how fast the splits where calculated. I have one more wondering. It is more efficient to use oracle splitter in case of cluster table that can't be splitted by rowid?
See :
Note 1043380 - Efficient Table Splitting for Oracle Databases.
Specific ROWID table splitting limitations:
Yes, I already read that. Thanks. I did not get my question right. I meant is the Oracle splitter splitting more efficient than R3ta's when using a column instead of rowid. I think I will check that myself
. Thank you for your time.
The PL/SQL is the more efficient than R3TA (you can't have better execution plan!!).
Hello Mathieu,
Great work, thanks for sharing it !
One little remark.
Watch out when rebuilding index with parallelism (step 2.2.1).
After rebuilding an index that way you should reset the parallelism or side effects can happen...
By default no segment has parallelism setup on SAP systems.
Thus you should add the following to command after rebuilding the index:
Alter index "SAPSR3"."VBOX~0" noparallel;
651060 - FAQ: Oracle Parallel Execution
Parallel DDL activates segment parallelism
If a parallel DDL operation like ALTER INDEX REBUILD PARALLEL is performed, the parallelism degree for the index remains even after the DDL operation is finished. As a consequence unintentionally parallel query might be used. In order to avoid problems you have to make sure that you reset the parallelism degree of the concerned segments to 1 after the DDL operation.
EDIT:
=> oups, I did reach step 5.1 of your document and it seems that you were already aware of that point...
A nice doc from MS on that topic
http://blogs.msdn.com/cfs-filesystemfile.ashx/__key/communityserver-components-postattachments/00-10-42-25-14/Oracle-to-SQL-Migration-FAQ-_2D00_v5.0.docx