Mathieu GRAVIL

July 12, 2013 4 minute read

brspace export and import using datapump

Test done on oracle 10 with brtool 700 (51à.

DATA pump use directories define in :

select * from dba_directories;

Some view which are usefull :
DBA_DATAPUMP_SESSIONS
DBA_DATAPUMP_JOBS

1. Export table APQD from T32 system with brspace using datapump :

in ora{sid} user:

brspace -u system/Bricolo01 -f tbexport -l expdp -t “APQD” -u /oracle/T32/sapdata4/reorg
`BR1001I BRSPACE 7.00 (51) BR1002I Start of BRSPACE processing: sehxxmwr.tbe 2012-02-20 17.42.13 BR0484I BRSPACE log file: /oracle/T32/sapreorg/sehxxmwr.tbe` BR0280I BRSPACE time stamp: 2012-02-20 17.42.14 BR1009I Name of database instance: T32 BR1010I BRSPACE action ID: sehxxmwr BR1011I BRSPACE function ID: tbe BR1012I BRSPACE function: tbexport BR0280I BRSPACE time stamp: 2012-02-20 17.42.15 BR0657I Input menu 359 – please enter/check input values ——————————————————————————- Main options for export of tables: SAPR3.APQD 1 * Export utility (utility) …………… [EXPDP] 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: c BR0280I BRSPACE time stamp: 2012-02-20 17.42.17 BR0663I Your choice: ‘c’ BR0259I Program execution will be continued… BR0280I BRSPACE time stamp: 2012-02-20 17.42.17 BR0657I Input menu 360 – please enter/check input values ——————————————————————————- Additional options for export of tables: SAPR3.APQD 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) …………. [/oracle/T32/102_64/bin/expdp parfile=/oracle/T32/sapreorg/sehxxmwr/parfile.exp] Standard keys: c – cont, b – back, s – stop, r – refr, h – help ——————————————————————————- BR0662I Enter your choice: c BR0280I BRSPACE time stamp: 2012-02-20 17.42.18 BR0663I Your choice: ‘c’ BR0259I Program execution will be continued… BR0280I BRSPACE time stamp: 2012-02-20 17.42.19 BR0370I Directory /oracle/T32/sapreorg/sehxxmwr created BR0280I BRSPACE time stamp: 2012-02-20 17.42.19 BR0370I Directory /oracle/T32/sapdata4/reorg/sehxxmwr.edd created BR1164I Export of tables will be started with command line: /oracle/T32/102_64/bin/expdp parfile=/oracle/T32/sapreorg/sehxxmwr/parfile.exp BR0280I BRSPACE time stamp: 2012-02-20 17.42.19 BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort: c BR0280I BRSPACE time stamp: 2012-02-20 17.42.21 BR0257I Your reply: ‘c’ BR0259I Program execution will be continued… BR0278I Command output of ‘/oracle/T32/102_64/bin/expdp parfile=/oracle/T32/sapreorg/sehxxmwr/parfile.exp’: Export: Release 10.2.0.2.0 – 64bit Production on Monday, 20 February, 2012 17:42:21 Copyright (c) 2003, 2005, Oracle. All rights reserved. Username: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – 64bit Production With the Partitioning and Data Mining options Starting “SYSTEM”.”BR_EXPORT_SEHXXMWR”: system/****** parfile=/oracle/T32/sapreorg/sehxxmwr/parfile.exp Estimate in progress using BLOCKS method… Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 450.0 MB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported “SAPR3″.”APQD” 385.0 MB 2332525 rows Master table “SYSTEM”.”BR_EXPORT_SEHXXMWR” successfully loaded/unloaded **************************************************************************** Dump file set for SYSTEM.BR_EXPORT_SEHXXMWR is: /oracle/T32/sapdata4/reorg/sehxxmwr.edd/expdat.dmp Job “SYSTEM”.”BR_EXPORT_SEHXXMWR” successfully completed at 17:45:53 BR0280I BRSPACE time stamp: 2012-02-20 17.45.55 BR1160I 1 table exported by EXPDP utility

brspace -u system/Bricolo01 -f tbexport -l expdp -t “APQD” -u /oracle/T32/sapdata4/reorg

BR1001I BRSPACE 7.00 (51) BR1002I Start of BRSPACE processing: sehxxmwr.tbe 2012-02-20 17.42.13 BR0484I BRSPACE log file: /oracle/T32/sapreorg/sehxxmwr.tbe

BR0280I BRSPACE time stamp: 2012-02-20 17.42.14
BR1009I Name of database instance: T32
BR1010I BRSPACE action ID: sehxxmwr
BR1011I BRSPACE function ID: tbe
BR1012I BRSPACE function: tbexport

BR0280I BRSPACE time stamp: 2012-02-20 17.42.15
BR0657I Input menu 359 – please enter/check input values
——————————————————————————-
Main options for export of tables: SAPR3.APQD

1 * Export utility (utility) …………… [EXPDP]
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:
c
BR0280I BRSPACE time stamp: 2012-02-20 17.42.17
BR0663I Your choice: ‘c’
BR0259I Program execution will be continued…

BR0280I BRSPACE time stamp: 2012-02-20 17.42.17
BR0657I Input menu 360 – please enter/check input values
——————————————————————————-
Additional options for export of tables: SAPR3.APQD

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) …………. [/oracle/T32/102_64/bin/expdp parfile=/oracle/T32/sapreorg/sehxxmwr/parfile.exp]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
c
BR0280I BRSPACE time stamp: 2012-02-20 17.42.18
BR0663I Your choice: ‘c’
BR0259I Program execution will be continued…

BR0280I BRSPACE time stamp: 2012-02-20 17.42.19
BR0370I Directory /oracle/T32/sapreorg/sehxxmwr created

BR0280I BRSPACE time stamp: 2012-02-20 17.42.19
BR0370I Directory /oracle/T32/sapdata4/reorg/sehxxmwr.edd created

BR1164I Export of tables will be started with command line: /oracle/T32/102_64/bin/expdp parfile=/oracle/T32/sapreorg/sehxxmwr/parfile.exp

BR0280I BRSPACE time stamp: 2012-02-20 17.42.19
BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort:
c
BR0280I BRSPACE time stamp: 2012-02-20 17.42.21
BR0257I Your reply: ‘c’
BR0259I Program execution will be continued…

BR0278I Command output of ‘/oracle/T32/102_64/bin/expdp parfile=/oracle/T32/sapreorg/sehxxmwr/parfile.exp’:

Export: Release 10.2.0.2.0 – 64bit Production on Monday, 20 February, 2012 17:42:21

Username:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – 64bit Production
With the Partitioning and Data Mining options
Starting “SYSTEM”.”BR_EXPORT_SEHXXMWR”: system/********
parfile=/oracle/T32/sapreorg/sehxxmwr/parfile.exp
Estimate in progress using BLOCKS method…
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 450.0 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported “SAPR3″.”APQD” 385.0 MB 2332525 rows
Master table “SYSTEM”.”BR_EXPORT_SEHXXMWR” successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.BR_EXPORT_SEHXXMWR is:
/oracle/T32/sapdata4/reorg/sehxxmwr.edd/expdat.dmp
Job “SYSTEM”.”BR_EXPORT_SEHXXMWR” successfully completed at 17:45:53

BR0280I BRSPACE time stamp: 2012-02-20 17.45.55
BR1160I 1 table exported by EXPDP utility

2 . Import table APQD from T32 system with brspace using datapump :

brspace -u system/Bricolo01 -f tbimport -x /oracle/T32/sapdata4/reorg/sehxxmwr.edd/expdat.dmp -a replace -p 4
BR1001I BRSPACE 7.00 (51) BR1002I Start of BRSPACE processing: sehxxnuv.tbi 2012-02-20 17.52.41 BR0484I BRSPACE log file: /oracle/T32/sapreorg/sehxxnuv.tbi BR0280I BRSPACE time stamp: 2012-02-20 17.52.41 BR1009I Name of database instance: T32 BR1010I BRSPACE action ID: sehxxnuv BR1011I BRSPACE function ID: tbi BR1012I BRSPACE function: tbimport BR0280I BRSPACE time stamp: 2012-02-20 17.52.41 BR0657I Input menu 363 – please enter/check input values ——————————————————————————- Main options for import from dump file /oracle/T32/sapdata4/reorg/sehxxmwr.edd/expdat.dmp 1 * Import utility (utility) …………… [IMPDP] 2 – Import type (type) ………………… [full] 3 # Owner for import (owner) …………… [] 4 # Tables for import (tables) …………. [] 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: c BR0280I BRSPACE time stamp: 2012-02-20 17.52.43 BR0663I Your choice: ‘c’ BR0259I Program execution will be continued… BR0280I BRSPACE time stamp: 2012-02-20 17.52.43 BR0657I Input menu 364 – please enter/check input values ——————————————————————————- Additional options for import from dump file /oracle/T32/sapdata4/reorg/sehxxmwr.edd/expdat.dmp 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) ………… [replace] 5 – Parallel degree (parallel) ………….. [4] 6 # Max. size of dump file in MB (filesize) . [] 7 – Force table import (force) ………….. [no] 8 – IMP/IMPDP command (command) …………. [/oracle/T32/102_64/bin/impdp parfile=/oracle/T32/sapreorg/sehxxnuv/parfile.imp] Standard keys: c – cont, b – back, s – stop, r – refr, h – help ——————————————————————————- BR0662I Enter your choice: c BR0280I BRSPACE time stamp: 2012-02-20 17.52.44 BR0663I Your choice: ‘c’ BR0259I Program execution will be continued… BR0280I BRSPACE time stamp: 2012-02-20 17.52.44 BR0370I Directory /oracle/T32/sapreorg/sehxxnuv created BR1172I Import of tables will be started with command line: /oracle/T32/102_64/bin/impdp parfile=/oracle/T32/sapreorg/sehxxnuv/parfile.imp BR0280I BRSPACE time stamp: 2012-02-20 17.52.44 BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort: c BR0280I BRSPACE time stamp: 2012-02-20 17.53.04 BR0257I Your reply: ‘c’ BR0259I Program execution will be continued… BR0278I Command output of ‘/oracle/T32/102_64/bin/impdp parfile=/oracle/T32/sapreorg/sehxxnuv/parfile.imp’: Import: Release 10.2.0.2.0 – 64bit Production on Monday, 20 February, 2012 17:53:04 Copyright (c) 2003, 2005, Oracle. All rights reserved. Username: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – 64bit Production With the Partitioning and Data Mining options Master table “SYSTEM”.”BR_IMPORT_SEHXXNUV” successfully loaded/unloaded Starting “SYSTEM”.”BR_IMPORT_SEHXXNUV”: system/******** parfile=/oracle/T32/sapreorg/sehxxnuv/parfile.imp Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/TABLE_DATA . . imported “SAPR3″.”APQD” 385.0 MB 2332525 rows Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS Job “SYSTEM”.”BR_IMPORT_SEHXXNUV” successfully completed at 17:53:43 BR0280I BRSPACE time stamp: 2012-02-20 17.53.44 BR1166I 1 table imported by IMPDP utility `BR0280I BRSPACE time stamp: 2012-02-20 17.53.44 BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:`

brspace -u system/Bricolo01 -f tbimport -x /oracle/T32/sapdata4/reorg/sehxxmwr.edd/expdat.dmp -a replace -p 4

BR1001I BRSPACE 7.00 (51)
BR1002I Start of BRSPACE processing: sehxxnuv.tbi 2012-02-20 17.52.41
BR0484I BRSPACE log file: /oracle/T32/sapreorg/sehxxnuv.tbi

BR0280I BRSPACE time stamp: 2012-02-20 17.52.41
BR1009I Name of database instance: T32
BR1010I BRSPACE action ID: sehxxnuv
BR1011I BRSPACE function ID: tbi
BR1012I BRSPACE function: tbimport

BR0280I BRSPACE time stamp: 2012-02-20 17.52.41
BR0657I Input menu 363 – please enter/check input values
——————————————————————————-
Main options for import from dump file /oracle/T32/sapdata4/reorg/sehxxmwr.edd/expdat.dmp

1 * Import utility (utility) …………… [IMPDP]
2 – Import type (type) ………………… [full]
3 # Owner for import (owner) …………… []
4 # Tables for import (tables) …………. []
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:
c
BR0280I BRSPACE time stamp: 2012-02-20 17.52.43
BR0663I Your choice: ‘c’
BR0259I Program execution will be continued…

BR0280I BRSPACE time stamp: 2012-02-20 17.52.43
BR0657I Input menu 364 – please enter/check input values
——————————————————————————-
Additional options for import from dump file /oracle/T32/sapdata4/reorg/sehxxmwr.edd/expdat.dmp

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) ………… [replace]
5 – Parallel degree (parallel) ………….. [4]
6 # Max. size of dump file in MB (filesize) . []
7 – Force table import (force) ………….. [no]
8 – IMP/IMPDP command (command) …………. [/oracle/T32/102_64/bin/impdp parfile=/oracle/T32/sapreorg/sehxxnuv/parfile.imp]

Standard keys: c – cont, b – back, s – stop, r – refr, h – help
——————————————————————————-
BR0662I Enter your choice:
c
BR0280I BRSPACE time stamp: 2012-02-20 17.52.44
BR0663I Your choice: ‘c’
BR0259I Program execution will be continued…

BR0280I BRSPACE time stamp: 2012-02-20 17.52.44
BR0370I Directory /oracle/T32/sapreorg/sehxxnuv created

BR1172I Import of tables will be started with command line: /oracle/T32/102_64/bin/impdp parfile=/oracle/T32/sapreorg/sehxxnuv/parfile.imp

BR0280I BRSPACE time stamp: 2012-02-20 17.52.44
BR0670I Enter ‘c[ont]’ to continue, ‘b[ack]’ to go back, ‘s[top]’ to abort:
c
BR0280I BRSPACE time stamp: 2012-02-20 17.53.04
BR0257I Your reply: ‘c’
BR0259I Program execution will be continued…

BR0278I Command output of ‘/oracle/T32/102_64/bin/impdp parfile=/oracle/T32/sapreorg/sehxxnuv/parfile.imp’:

Import: Release 10.2.0.2.0 – 64bit Production on Monday, 20 February, 2012 17:53:04

Username:
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 – 64bit Production
With the Partitioning and Data Mining options
Master table “SYSTEM”.”BR_IMPORT_SEHXXNUV” successfully loaded/unloaded
Starting “SYSTEM”.”BR_IMPORT_SEHXXNUV”: system/******** parfile=/oracle/T32/sapreorg/sehxxnuv/parfile.imp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “SAPR3″.”APQD” 385.0 MB 2332525 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “SYSTEM”.”BR_IMPORT_SEHXXNUV” successfully completed at 17:53:43

BR0280I BRSPACE time stamp: 2012-02-20 17.53.44
BR1166I 1 table imported by IMPDP utility

BR0280I BRSPACE time stamp: 2012-02-20 17.53.44 BR0670I Enter 'c[ont]' to continue, 'b[ack]' to go back, 's[top]' to abort:

3. Stats computation :
`brconnect -u / -c -f stats -t missing -p 4`

Be the first to leave a comment

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

brspace export and import using datapump

1. Export table APQD from T32 system with brspace using datapump :

2 . Import table APQD from T32 system with brspace using datapump :

3. Stats computation :brconnect -u / -c -f stats -t missing -p 4

Assigned Tags

3. Stats computation :
`brconnect -u / -c -f stats -t missing -p 4`