Export/Import Tables using BRTOOLS

Case: I want to reorganize table and rebuild index offline for table SOFFCONT1. Table SOFFCONT1 contains SAP Business Workplace/SAPoffice objects which often grow rapidly in Netweaver based component.

Procedure: Here’s the procedure to export/import tables and rebuild index using BRTOOLS,

1. Export table SOFFCONT1
2. Drop index
3. Import table SOFFCONT1
4. Run Update Statistics Optimizer

Details steps :

1. Export table SOFFCONT1

>brspace -f tbexport -t soffcont1

BR0280I BRSPACE time stamp: 2009-06-06 21.10.00

BR1009I Name of database instance: R3X

BR1010I BRSPACE action ID: seasymoz

BR1011I BRSPACE function ID: tbe

BR1012I BRSPACE function: tbexport

BR0280I BRSPACE time stamp: 2009-06-06 21.10.07

BR0657I Input menu 359 - please enter/check input values

Main options for export of tables: SAPR3X.SOFFCONT1

1 * Export utility (utility) ............... [EXP]

2 * Tablespaces for export (tablespaces) ... []

3 * Owner for export (owner) ............... []

4 - Export table rows (rows) ............... [yes]

5 - Export table indexes (indexes) ......... [yes]

6 - Export table constraints (constraints) . [yes]

7 - Export table grants (grants) ........... [yes]

8 - Export table triggers (triggers) ....... [yes]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

BR0662I Enter your choice:

BR0280I BRSPACE time stamp: 2009-06-06 21.12.18

BR0663I Your choice: 'c'

BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2009-06-06 21.12.18

BR0657I Input menu 360 - please enter/check input values

Additional options for export of tables: SAPR3X.SOFFCONT1

1 - Use direct path (direct) ................ [yes]

2 - Export buffer size in KB (buffer) ....... [10240]

3 - Compress table extents/data (compress) .. [no]

4 - Consistent export (consistent) .......... [no]

5 # Parallel degree (parallel) .............. [1]

6 - Max. size of dump file in MB (filesize) . [20000]

7 - Force table export (force) .............. [no]

8 - EXP/EXPDP command (command) ............. [E:\oracle\R3X\102\BIN\exp parfile=I:\Oracle\R3X\sapreorg\seasymoz\parfile.exp]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

BR0662I Enter your choice:

BR0280I BRSPACE time stamp: 2009-06-06 21.13.15

BR0663I Your choice: 'c'

BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2009-06-06 21.13.16

BR0370I Directory I:\Oracle\R3X\sapreorg\seasymoz created

BR0280I BRSPACE time stamp: 2009-06-06 21.13.16

BR0370I Directory I:\Oracle\R3X\sapreorg\seasymoz.edd created

BR1164I Export of tables will be started with command line: E:\oracle\R3X\102\BIN\exp parfile=I:\Oracle\R3X\sapreorg\seasymoz\parfile.exp

BR0280I BRSPACE time stamp: 2009-06-06 21.13.16

BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:

BR0280I BRSPACE time stamp: 2009-06-06 21.13.47

BR0257I Your reply: 'c'

BR0259I Program execution will be continued...

BR0278I Command output of 'E:\oracle\R3X\102\BIN\exp parfile=I:\Oracle\R3X\sapreorg\seasymoz\parfile.exp':

Export: Release 10.2.0.2.0 - Production on Sat Jun 6 21:13:47 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Username:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Export done in WE8DEC character set and UTF8 NCHAR character set

About to export specified tables via Direct Path ...

Current user changed to SAPR3X

. . exporting table                      SOFFCONT1

continuing export into file I:\Oracle\R3X\sapreorg\seasymoz.edd\expdat1.dmp

962456 rows exported

Export terminated successfully without warnings.

BR0280I BRSPACE time stamp: 2009-06-06 22.44.59

BR1160I 1 table exported by EXP utility

BR0280I BRSPACE time stamp: 2009-06-06 22.45.02

BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:

BR0280I BRSPACE time stamp: 2009-06-06 22.55.09

BR0257I Your reply: 'c'

BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2009-06-06 22.55.10

BR1022I Number of tables processed: 1

BR1003I BRSPACE function 'tbexport' completed

BR1008I End of BRSPACE processing: seasymoz.tbe 2009-06-06 22.55.10

BR0280I BRSPACE time stamp: 2009-06-06 22.55.12

BR1005I BRSPACE completed successfully

2. Drop index

>sqlplus “/as sysdba”

SQL>drop index “SAPR3X”.”SOFFCONT1~0″;

Index dropped

3. Import table SOFFCONT1

>brspace -f tbimport -t soffcont1

BR0280I BRSPACE time stamp: 2009-06-06 23.00.00

BR1009I Name of database instance: R3X

BR1010I BRSPACE action ID: seasywiu

BR1011I BRSPACE function ID: tbi

BR1012I BRSPACE function: tbimport

BR0280I BRSPACE time stamp: 2009-06-06 23.00.00

BR0656I Choice menu 361 - please make a selection

Import tables main menu

1 = Import tables

2 - Show BRSPACE export runs

3 * Exit program

4 - Reset program status

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

BR0662I Enter your choice:

BR0280I BRSPACE time stamp: 2009-06-06 23.00.14

BR0663I Your choice: 'c'

BR0259I Program execution will be continued...

BR0699I Reading log file I:\Oracle\R3X\sapreorg\spaceR3X.log ...

BR0280I BRSPACE time stamp: 2009-06-06 23.00.15

BR0658I List menu 362 - please select one entry

List of BRSPACE exports for import

Pos.  Run           Date                   Tables  Dumps       Size[KB]  Util.

1 = seasymoz.tbe  2009-06-06 21.09.57        1      2       25519234   EXP

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

BR0662I Enter your selection:

BR0280I BRSPACE time stamp: 2009-06-06 23.01.22

BR0663I Your selection: 'c'

BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2009-06-06 23.01.22

BR0657I Input menu 363 - please enter/check input values

Main options for import from dump file I:\Oracle\R3X\sapreorg\seasymoz.edd\expd        at.dmp,... (2 dumps)

1 * Import utility (utility) ............... [IMP]

2 - Import type (type) ..................... [full]

3 # Owner for import (owner) ............... []

4 # Tables for import (tables) ............. [SOFFCONT1]

5 - Import table rows (rows) ............... [yes]

6 - Import table indexes (indexes) ......... [yes]

7 - Import table constraints (constraints) . [yes]

8 - Import table grants (grants) ........... [yes]

9 # Import table triggers (triggers) ....... [yes]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

BR0662I Enter your choice:

BR0280I BRSPACE time stamp: 2009-06-06 23.02.42

BR0663I Your choice: 'c'

BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2009-06-06 23.02.42

BR0657I Input menu 364 - please enter/check input values

Additional options for import from dump file I:\Oracle\R3X\sapreorg\seasymoz.ed        d\expdat.dmp,... (2 dumps)

1 - Import buffer size in KB (buffer) ....... [10240]

2 - Commit after each array insert (commit) . [yes]

3 - Ignore creation errors (ignore) ......... [yes]

4 # Table exists action (action) ............ [skip]

5 # Parallel degree (parallel) .............. [1]

6 # Max. size of dump file in MB (filesize) . [20000]

7 - Force table import (force) .............. [no]

8 - IMP/IMPDP command (command) ............. [E:\oracle\R3X\102\BIN\imp parfile=I:\Oracle\R3X\sapreorg\seasywiu\parfile.imp]

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

BR0662I Enter your choice:

BR0280I BRSPACE time stamp: 2009-06-06 23.03.06

BR0663I Your choice: 'c'

BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2009-06-06 23.03.07

BR0370I Directory I:\Oracle\R3X\sapreorg\seasywiu created

BR1172I Import of tables will be started with command line: E:\oracle\R3X\102\BIN\imp parfile=I:\Oracle\R3X\sapreorg\seasywiu\parfile.imp

BR0280I BRSPACE time stamp: 2009-06-06 23.03.07

BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:

BR0280I BRSPACE time stamp: 2009-06-06 23.03.34

BR0257I Your reply: 'c'

BR0259I Program execution will be continued...

BR0278I Command output of 'E:\oracle\R3X\102\BIN\imp parfile=I:\Oracle\R3X\sapreorg\seasywiu\parfile.imp':

Import: Release 10.2.0.2.0 - Production on Sat Jun 6 23:03:35 2009

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Username:

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via direct path

import done in WE8DEC character set and UTF8 NCHAR character set

. importing SYS's objects into SYS

. importing SAPR3X's objects into SAPR3X

. . importing table                    "SOFFCONT1"     962456 rows imported

Import terminated successfully without warnings.

BR0280I BRSPACE time stamp: 2009-06-07 00.43.12

BR1166I 1 table imported by IMP utility

BR0280I BRSPACE time stamp: 2009-06-07 00.43.14

BR0256I Enter 'c[ont]' to continue, 's[top]' to cancel BRSPACE:

BR0280I BRSPACE time stamp: 2009-06-07 00.46.19

BR0257I Your reply: 'c'

BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2009-06-07 00.46.19

BR0656I Choice menu 361 - please make a selection

Import tables main menu

1 * Import tables

2 - Show BRSPACE export runs

3 = Exit program

4 - Reset program status

Standard keys: c - cont, b - back, s - stop, r - refr, h - help

BR0662I Enter your choice:

BR0280I BRSPACE time stamp: 2009-06-07 00.46.25

BR0663I Your choice: 'c'

BR0259I Program execution will be continued...

BR0280I BRSPACE time stamp: 2009-06-07 00.46.25

BR0680I Do you really want to exit BRSPACE? Enter y[es]/n[o]:

BR0280I BRSPACE time stamp: 2009-06-07 00.46.33

BR0257I Your reply: 'y'

BR0280I BRSPACE time stamp: 2009-06-07 00.46.33

BR1022I Number of tables processed: 1

BR1003I BRSPACE function 'tbimport' completed

BR1008I End of BRSPACE processing: seasywiu.tbi 2009-06-07 00.46.33

BR0280I BRSPACE time stamp: 2009-06-07 00.46.34

BR1005I BRSPACE completed successfully

4. Run Update Statistic Optimizer in SAP

Execute or Schedule this via DB13 then it will create a new index for table SOFFCONT1.

Note: Tables SOFFCONT1 need to be reorganized offline as they contain LONG RAW fields and must not be converted to LOBS with the online reorganization.

 

Reorganize Tables in SAP

Why do we need to reorganize table in SAP system?

This is the important question before we plan to reorganize table in SAP system. Since Data Management Strategy is the key factor to boost system performance and to keep data accessibility more efficient, we need to maintain or organize the data itself. One of the strategy is to reorg certain table which can impact on system performance. Reorganizing table can also be used to improve database structure, transform dictionary managed to locally managed table space, merge data in table space from many small files into larger files and perform segments defragmentation.

What types of reorganization is provided by SAP using BR*Tools?
You can perform the following types of reorganization:                                      

• Reorganize tables online
• Check tables for reorganization
• Clean up tables after aborted reorganization
• Convert LONG and LONG RAW fields to CLOB or BLOB online (Oracle 10g or higher)
• Stop reorganization (command-line mode only)
• Suspend reorganization (command-line mode only)
• Resume reorganization (command-line mode only)

What is the procedure to perform reorganization using BR*Tools ?

1. Run BRTOOLS. Choose Segment Management or run from the command prompt brspace –f tbreorg.
2. Enter table name in Table names (table).
3. Choose Reorganize Tables.
4. Set the required option like new destination table space or set the parallel thread which will be used to perform reorganization. The more you set the thread, the faster your process will be. But this limited to the CPU specification in OS level.
5. Choose continue to start processing.
6. Check the detail log in s<encoded timestamp>.tbr.

Please take note that this online reorganization will impact on system performance, therefore you need to run this when the system load is low.

 

What are the functions of SAPOSCOL?

What are the functions of SAPOSCOL?

SAPOSCOL is an operating system collector. It is a standalone program that runs in operating system background.

SAPOSCOL collects data about operating system resources like

·         CPU Utilization

·         Usage of Physical Memory

·         Usage of Virtual Memory

·         Usage of file systems

·         Usage of Physical disks

·         Usage of running processes

SAPOSCOL runs exactly once per host and runs independently of the sap system.

By default, SAPOSCOL collects data every 10seconds and records it. It also records the hourly average statistics for the last 24hours in shared memory. This data will be fetched by background job “SAP_COLLECTOR_FOR_PERFORMANCE” and writes it to the MONI performance database table.

DB post steps

DB post steps:

1. Create OPS$ users

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME         STATUS
------------------------------ ---------
SYSTEM                         ONLINE
SYSAUX                         ONLINE
PSAPUNDO                    ONLINE
PSAPTEMP                     ONLINE
PSAPCRM                       ONLINE
PSAPCRM731                 ONLINE
PSAPCRMUSR                ONLINE



SQL> create user OPS$BCRADM DEFAULT TABLESPACE PSAPCRMUSR temporary tablespace psaptemp identified externally;

User created.

SQL> create user OPS$ORABCR DEFAULT TABLESPACE PSAPCRMUSR temporary tablespace psaptemp identified externally;

User created.

SQL> grant connect, resource to ops$bcradm;

Grant succeeded.

SQL> grant sapdba to ops$orabcr;

Grant succeeded.

2. Drop exBIDing table ops$sidadm.sapuser (Source <sid>)

SQL> drop table ops$c2radm.sapuser;

Table dropped.

3. Create table ops$sidadm.sapuser (target <sid>)

SQL> create table ops$BCRadm.sapuser (userid varchar2(256), passwd varchar2(256));

Table created.

SQL> insert into ops$BCRadm.sapuser values ('SAPCRM','a234678z');

1 row created.


SQL> alter user sapcrm profile default;

User altered.

SQL> alter user system identified by a234678z;

User altered.

4. Update schema owner password using brconnect.

brconnect -u system/a234678z -f chpass -o sapcrm -p a234678z

5. Run scripts sapdba_role.sql and sapconn_role.sql

 sapdba_role.sql :

 sqlplus /nolog @sapdba_role <SAPSCHEMA_ID>
 <SAPSCHEMA_ID>: for owner SAPR3: R3, SAP<SID>: <SID>, SAP<SID>DB: <SID>DB

sapconn_role.sql :
sqlplus /nolog @sapconn_role

Starting SAP

1. Verify <sid>adm environment pinting to 11g binaries, 11g client

DIR_LIBRARY
LD_LIBRARY_PATH
TNS_ADMIN
ORACLE_HOME_PATH

2. Go to cdexe and run ldd dboraslib.so , should be able to load libraries

hostname:bcradm 11> cdexe
hostname:bcradm 12> ldd dboraslib.so
        linux-vdso.so.1 =>  (0x00007fff1e9ff000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007f43e8d81000)
        librt.so.1 => /lib64/librt.so.1 (0x00007f43e8b78000)
        libclntsh.so.11.1 => /oracle/client/10x_64/instantclient/libclntsh.so.11.1 (0x00007f43e61c2000)
        libc.so.6 => /lib64/libc.so.6 (0x00007f43e5e21000)
        libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00007f43e5b1a000)
        libm.so.6 => /lib64/libm.so.6 (0x00007f43e5896000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007f43e5680000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f43e5463000)
        /lib64/ld-linux-x86-64.so.2 (0x0000003a7b400000)
        libnnz11.so => /oracle/client/10x_64/instantclient/libnnz11.so (0x00007f43e5097000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007f43e4e7e000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007f43e4c7c000)

3.Run R3trans –d using <sid>adm, return code to be 000

If return code other than 000 analyse r3trans.log
hostname:bcradm 13> R3trans -d
This is R3trans version 6.23 (release 720 - 17.09.12 - 20:12:05 ).
unicode enabled version
R3trans finished (0000).

4. Startsap and verify startup logs

Ps –eaf| grep dw

Ps –eaf| grep sap

Ps –eaf| grep ms ,

ps –eaf| grep scs,

ps –eaf| grep ers

5. Login to SAP via sapgui and apply sap license

Record hardware key to generate saplicense via sap market place

hostname:bcradm 16> saplicense -get

saplicense: HARDWARE KEY = xxxxxxxxxxx

6. Set below paaremter to login with user sap* 000 client

DEFAULT.PFL:login/no_automatic_user_sapstar = 0

Set below paramters relevant to transport

Example:

BIR_DVEBMGS01_sapbirci:DIR_EPS_ROOT = /usr/sap/trans/isu/BIR/EPS

BIR_DVEBMGS01_sapbirci:DIR_TRANS = /usr/sap/trans/isu/BIR

7. Reinitialize TMS after DB copy

logon to 000  as DDIC

Run transaction SE06 -->database copy or database migration

Reinstall CTS -->  yes

change original <S_System <SID>>to <T_System <SID>> -->yes

cts not configured --> ok

delete the TMS Configuration --> yes

delete all versions of transports --> yes

delete transport routs --> yes

Configure TMS

Logon to 000 Client with user DDIC and password

Go to STMS and configure TMS as self domain, verify domain.cfg tp_domain.PFl creation at right location at OS level.

Go to system overview, create a virtual system

Go to transport routes and add routes, adjust and dBIRribute

8. Suspend background jobs, run report BTCTRNS1

9. Configure SAP Profile Parameter Files

After the database restore, the active profiles lBIRed in the SAP system will be from the old hostname.  These profiles need to be deleted and the profiles from the OS need to be re-imported.  Proceed as follows:

Run transaction RZ10.

Delete source system profiles:

Click the Profile drop-down box and choose a profile (this process must be done for each profile).

Select menu items: Profile -> Delete -> All versions of a profile.

Click Yes to first prompt to update the database

Click No to delete the files from the OS Level

Click green check on all versions deleted screen

Repeat steps 1-3 for all source system profiles lBIRed in the Profile drop-down lBIR.

Import all profiles from UNIX by selecting menu items: Utilities -> Import Profiles -> of active servers.

Green back arrow.

10. Fix Logon Group Names

Run transaction SMLG

Add the newly added systems to exBIRing LOGON groups.

Highlight the New Hostname Entries.

Click the Assign Group button and assign the Logon groups respectively.

Highlight the Old Hostname Entries.

Click the Delete button.

Save.  

Note: Do not delete the groups instead of that delete only instances assigned. If you delete the groups both the groups and instances will be deleted.

Re-execute transaction SMLG once again and validate the logon group configuration.

11.Fix RFC Server Group

Run transaction RZ12

Add the newly added systems to exBIRing server groups.

Click create assignment provide exBIRing server group name and hostname

Choose apply current quotos and save

12. Configure Op Modes

After the database restore, the target system will contain Operation Modes from the old hostname.  These Op Modes need to be deleted and the correct Op Modes need to be imported.  Proceed as follows:

Run transaction RZ04

Choose exiting operation mode and delete

Click create operation mode

Select a server name and press SHIFT F2.

Do this for each source system op mode lBIRed.

Click the SAVE button and provide day mode

Click yes to add other operation mode (night), select no and save.

13. Create RFC’s between CRM, ISU and BW systems

CRM --> ISU & ISU --> CRM

Use user rfcuser or copy rfcuser with in the rfc and maintain the rfc’s, perform authorization test

CRM --> BW & ISU --> BW

Use user KIWALE with in the rfc connections

BW --> CRM & BW -->ISU

Use user BIWREMOTE, ALEREMOTE or rfcuser relevant to system

14. Logical system conversion

Go to SCC4 and record the logical system

Go to WE20 and record logical systems relevant to ISU, CRM and BW

      

1. Disconnect from previously connected BWR (EX : BWR – source , BWS – target) 

(For all systems that are source systems of BW: ISU, CRM)

Run Transaction SE37

Enter RSAP_BIW_DISCONNECT

Hit Test/Execute (F8)

Enter data into the following fields:

I_BIW_LOGSYS:  BWRCLNT500

I_OLTP_LOGSYS: <Logical system for your Production System>

I_FORCE_DELETE: X

Hit execute (F8)