Blog - comments

Hi goog article can we install avdf firewall with flat network if it's possible please let me know ?

Thilina
First, thank you for your interrest in this blog.Yes, the byte code will be interpreted each time bu...
BIEHLER Stephane
Pretty sure this is wrong:> already said that JVMs interprets the generated byte code - that's true...
Gs
Michael, great article, however, I would disagree on DRS/Host Affinity. You are legally only requir...
David Bradshaw
Hi lauri, db_file_multiblock_read_count is still used in exadata smartscan because it defines the si...
Blog Jérôme Witt Wie kann ich Indexes und Daten mittels Data Pump trennen?

dbi services Blog

Welcome to the dbi services Blog! This IT blog focuses on database, middleware, and OS technologies such as Oracle, Microsoft SQL Server & SharePoint, EMC Documentum, MySQL, PostgreSQL, Sybase, Unix/Linux, etc. The dbi services blog represents the view of our consultants, not necessarily that of dbi services. Feel free to comment on our blog postings.

  • Home
    Home This is where you can find all the blog posts throughout the site.
  • Categories
    Categories Displays a list of categories from this blog.
  • Tags
    Tags Displays a list of tags that have been used in the blog.
  • Bloggers
    Bloggers Search for your favorite blogger from this site.

Wie kann ich Indexes und Daten mittels Data Pump trennen?

Diese Frage habe ich in den letzten Jahren öfters gehört. Zumindest seitdem die DBAs Oracle Datapump verwenden anstatt des alten Export/Import Tools. Angeblich war es früher mit dem Import-Parameter-Tool „INDEXFILE“ viel einfacher. Tja, die Wahrheit ist: Mit Datapump geht es noch leichter!

Dies möchte ich anhand eines ganz normalen Datapump-Export–Files verdeutlichen. In folgendem Beispiel handelt es sich um einen einfachen Schema-Export, der wie folgt erzeugt wurde:

 

oracle@vmoel58:/u00/app/oracle/admin/DB112/etc/ [DB112] cat expdp_SalesApp.par
SCHEMAS=sh
DIRECTORY=data_pump_dir
DUMPFILE=expdp_SH_2013-02-17.dmp
LOGFILE=expdp_SH_2013-02-17.log
PARALLEL=4

oracle@vmoel58:/u00/app/oracle/admin/DB112/etc/ [DB112] expdp parfile=expdp_SalesApp.par

Export: Release 11.2.0.3.0 - Production on Sun Feb 17 16:08:27 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** parfile=expdp_SalesApp.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 273.8 MB
. . exported "SH"."CUSTOMERS"                            9.853 MB   55500 rows

... [Aus Formatierungsgründen wurde dieser Teil herausgefiltert]


. . exported "SH"."SALES":"SALES_Q4_2003"                    0 KB       0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
  /u00/app/oracle/admin/DB112/dpdump/expdp_SH_2013-02-17.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 16:09:00

 

Zuerst sollten die gesamten Daten und alle anderen Objekte, ausser die Indexes importiert werden. In diesem Fall ist der Parameter EXCLUDE der Schlüssel zum Erfolg:

 

oracle@vmoel58:/u00/app/oracle/admin/DB112/etc/ [DB112] impdp parfile=expdp_SalesApp.par remap_schema=sh:sh_v2 exclude=INDEX

Import: Release 11.2.0.3.0 - Production on Sun Feb 17 16:15:42 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** parfile=expdp_SalesApp.par remap_schema=sh:sh_v2 exclude=INDEX
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

... (Aus Formatierungs-Gründ wurde dieser Teil herausgefiltert)

 

In einem zweiten Schritt müssen die Indexes importiert werden. Der INCLUDE Parameter enthält den INDEX-Schlüssel, dieser umfasst die Index-Statistiken. Die Tablespace-Änderung erfolgt durch den REMAP_TABLESPACE Parameter:

 

oracle@vmoel58:/u00/app/oracle/admin/DB112/etc/ [DB112] impdp parfile=expdp_SalesApp.par remap_schema=sh:sh_v2 include=INDEX remap_tablespace=SH_DATA:SH_IDX

Import: Release 11.2.0.3.0 - Production on Sun Feb 17 16:23:58 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: system
Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01":  system/******** parfile=expdp_SalesApp.par remap_schema=sh:sh_v2 include=INDEX remap_tablespace=USERS:SH_IDX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-31684: Object type INDEX:"SH_V2"."PROMO_PK" already exists
ORA-31684: Object type INDEX:"SH_V2"."CUSTOMERS_PK" already exists
ORA-31684: Object type INDEX:"SH_V2"."PRODUCTS_PK" already exists
ORA-31684: Object type INDEX:"SH_V2"."TIMES_PK" already exists
ORA-31684: Object type INDEX:"SH_V2"."CHANNELS_PK" already exists
ORA-31684: Object type INDEX:"SH_V2"."COUNTRIES_PK" already exists
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"SH_V2"."TIMES_PK" already exists
ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"SH_V2"."PRODUCTS_PK" already exists
ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"SH_V2"."CHANNELS_PK" already exists
ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"SH_V2"."PROMO_PK" already exists
ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"SH_V2"."CUSTOMERS_PK" already exists
ORA-39111: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"SH_V2"."COUNTRIES_PK" already exists
Processing object type SCHEMA_EXPORT/TABLE/INDEX/BITMAP_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/BITMAP_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/DOMAIN_INDEX/INDEX
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 12 error(s) at 16:24:05

 

Die Primär Schlüssen Indexes sind durch den EXLUDE Paramater wegen dem "Constraint" nicht beeinflusst. Deswegen muss ebenso jeweils in EXCLUDE und INCLUDE der Schlüssel CONSTRAINT mitangegeben werden:

 

oracle@vmoel58:/u00/app/oracle/admin/DB112/etc/ [DB112] impdp parfile=expdp_SalesApp.par remap_schema=sh:sh_v2 include=INDEX,CONSTRAINT remap_tablespace=SH_DATA:SH_IDX

Import: Release 11.2.0.3.0 - Production on Sun Feb 17 17:08:50 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

...

 

Folgendes SQL-Statement stellt die Objekttrennung dar:

 

SQL> SELECT segment_type,tablespace_name FROM dba_segments
WHERE owner='SH_V2' AND segment_type IN ('TABLE','INDEX') GROUP BY segment_type,tablespace_name;
SEGMENT_TYPE       TABLESPACE_NAME
------------------ ------------------------------
TABLE           SH_DATA
INDEX           SH_IDX

Et voila, das wars! Übrigens mit anderen Data-Pump-Parametern wie zum Beispiel TABLE_EXISTS_ACTION kann man vieles mehr erreichen. Ein Blick in der Oracle Dokumentation „Oracle Database Utilities“ lohnt sich hier sehr.

Bei Anmerkungen oder Fragen freue ich mich auf einen Kommentar.

Viel Spass mit Oracle Data Pump!

Jérôme

Rate this blog entry:
1

Jérôme Witt is Senior Consultant at dbi Services. He started his Consultant career a few years ago. He is specialized in database and infrastructure management, engineering, and optimization. He is very skilled in Oracle high availability, backup & recovery, and tuning technologies. His expertise also includes the open source field (Linux/Unix), advanced Perl, Shell, Windows PowerShell programming, and Automation tools (UC4). Jérôme Witt is Oracle Certified Professional 11g (OCP 11g), Oracle Certified Expert Tuning (OCE), and ITIL V3 Foundation certified. Prior to joining dbi services, Jérôme Witt was Consultant at Trivadis in Basel. He also worked as a Junior Automation specialist at Selmoni AG in Basel. Jérôme Witt holds a BTS degree in Information Systems and Industrial Networks from France. His branch-related experience covers Pharma, Health Care, Banking & Financial Services, Energy, Automotive etc.

Comments

  • No comments made yet. Be the first to submit a comment

Leave your comment

Guest Saturday, 25 October 2014
AddThis Social Bookmark Button
Deutsch (DE-CH-AT)   French (Fr)

Contact

Contact us now!

Send us your request!

Our workshops

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

Expert insight from insiders!

Fixed Price Services

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

A safe investment: our IT services at fixed prices!

Your flexible SLA

dbi FlexService SLA - ISO 20000 certified.

dbi FlexService SLA ISO 20000

ISO 20000 certified & freely customizable!

dbi services Newsletter