Skip to Content

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

/wp-content/uploads/2013/07/image002_241003.png

                   

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.

  1. 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>

  1. Select the Output Directory and create it in Oracle:
    1. Save attached file presplitter.txt from OSS note 1043380 to PRESPLITTER.sql
    2. 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.

  1. Connected.

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

  1. Install PL/SQL Package
    1. Save attached file splitter_hint.txt from OSS note 1043380 to SPLITTER.sql
    2. 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:

  1. Connected.

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

/wp-content/uploads/2013/07/image003_241002.png

 

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.

  1. parallelIndexImport.txt

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

     …

  /wp-content/uploads/2013/07/image004_241004.png

  • 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.

  1. OrderBy.txt

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.

  1. distribution.txt

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   /wp-content/uploads/2013/07/image006_241000.png
  • Due to tablespace architecture adjustment, a special check has to be validated : correct TABART should be assigned to DDL statements (highlight in yellow)

/wp-content/uploads/2013/07/image007_241001.png

 

#

# 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

 

/wp-content/uploads/2013/07/image008_240999.png

/wp-content/uploads/2013/07/image009_240997.png 

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 ».

 

/wp-content/uploads/2013/07/image010_240996.png

4.5.    Préparation

Command to launch DISTMON preparation execution

  • Source directory is /export/DISTMON

./distribution_monitor.sh -p

4.6.    Unix permissions

  1. 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

  1. 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.

 

  /wp-content/uploads/2013/07/image011_240987.png

/wp-content/uploads/2013/07/image012_240988.png

/wp-content/uploads/2013/07/image013_240992.png

/wp-content/uploads/2013/07/image014_240993.png

/wp-content/uploads/2013/07/image015_240994.png

/wp-content/uploads/2013/07/image016_240995.png

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

To report this post you need to login first.

8 Comments

You must be Logged on to comment or reply to a post.

    1. Mathieu GRAVIL Post author

      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.

      (0) 
      1. Pierre Viel

        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?

        (0) 
        1. Mathieu GRAVIL Post author

          See :

          Note 1043380 – Efficient Table Splitting for Oracle Databases.

           

          Specific ROWID table splitting limitations:

          • ROWID table splitting MUST be performed during downtime of the SAP system. No table changes are allowed for ROWID splitted tables after ranges have been calculated and export was completed. Any table change before the export requires a recalculation of the rowid ranges.
          • ROWID splitted tables MUST be imported with the loadprocedure fast option of R3load
          • ROWID table splitting works only for transparent and non-partitioned tables.
          • ROWID table splitting CANNOT be used if the target database is a non Oracle database


          (0) 
          1. Pierre Viel

            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.

            (0) 
  1. Yves KERVADEC

    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…


    (0) 

Leave a Reply