13. Extending Data Pump for Data and Object Migration
The Data Pump feature of the Oracle database is a high-speed, highly scalable mechanism for transferring data and objects from one database to another (using database links), from a database to a file (export), or from a file to a database (import). Data Pump uses direct-path loading and unloading technologies to improve performance.
Data Pump can be invoked using the following methods:
Exclusive Data Pump export and import tools, using expdp and impdp
PL/SQL code calling the DBMS_DATAPUMP package to move data and DBMS_METADATA package to extract metadata (objects)
Oracle Enterprise Manager Cloud Control
Oracle SQL Developer
Data Pump comes with various options and parameters, and when you consider the possibilities of having various combinations of parameters, the options are endless—which obviously makes providing examples for each combination difficult. This chapter shows specific situations and how Data Pump utilities can be used for day-to-day database administration activities.
Using Data Pump
Data Pump was introduced in Oracle Database 10g and has evolved over the versions with various features and performance improvements in moving data and objects. The legacy export/import (exp/imp) utility is still maintained in all versions of database, including Oracle 12c, for backward compatibility and to migrate legacy data to newer versions of the database. This chapter shows you how to extend Data Pump for your various data and object migration needs.
Before we start, let us review the basics behind the export and import tools:
There are two sets of export/import tools available in Oracle: original export and import utilities (exp/imp) are the oldest and are maintained in the newer versions. New Oracle Data Pump export and import utilities (expdp/impdp) are available since Oracle 10g.
Legacy exp/imp is a client-side tool, meaning the dump file is written to (and read from) a local directory on the machine where you run the tool. Data Pump is a server-side utility, meaning the dump file is written to (and read from) a directory on the database server. By default, the file is written to (or read from) the directory defined by the DATA_PUMP_DIR directory (in dba_directories) location.
Data Pump export and import jobs can be paused, stopped, and restarted. Legacy exp/imp does not have this option.
Data Pump provides a very powerful interactive command-line mode that allows you to monitor and control Data Pump export and import operations on a different terminal.
Data Pump jobs can be initiated using the expdp/impdp tools or by using the Procedural Language/Structured Query Language (PL/SQL) package DBMS_DATAPUMP. The expdp and impdp tools are part of the Oracle client install.
The legacy exp/imp is not discussed in this chapter, but if you are a hardcore exp/imp fan, from Oracle Database 11g Release 2 onward, you can still use the same parameters you use with exp/imp in expdp/impdp (imp or exp with help=y shows you the available options and parameters). Data Pump converts the legacy parameters to Data Pump–specific parameters internally and executes the job. This is useful for DBAs who resist change to the Data Pump tools. Although using Data Pump in legacy mode is less flexible and offers fewer options than using the full-featured product, it is a good start to using Data Pump tools.
Copying Objects
The most basic use of Data Pump is to copy objects or schemas from one database to another, regardless of whether they are using the same version or different versions. If the objects do not exist in the target, the copy job is quicker and easier because there is no preparation or cleanup required. Before starting any Data Pump export job, you must confirm a few things:
Are you writing the export dump file to a directory? If yes, do you have enough space to save the dump file?
Is there a directory object pointing to that directory? Or is your default DATA_PUMP_DIR directory location good enough to save the export dump file?
Can you skip writing the dump file and copy the objects directly using the network link feature of Data Pump, utilizing a database link?
What options or parameters should you use to do the export and import?
Is the character set the same or compatible between the databases where export/import is performed?
If you need to copy data or objects from Oracle Database 9i or older, there is no Data Pump; you have to resort to the legacy exp/imp tools. Legacy exp/imp utilities are still available in Oracle Database 12c.
This chapter discusses a large number of options that can be used with Data Pump. Due to page constraints, we cannot show sample output for all code used in this chapter.
Tip
If you are not sure how much space is required for the export dump file, you can run expdp with the ESTIMATE_ONLY=Y option, which gives you the expected export size of the tables in the dump file.
Data Pump Modes
Normally, we consider export/import (using exp/imp or expdp/impdp) when we have to
Copy a table from one database to another. Use the table mode export with the TABLES= parameter along with other parameters.
Copy a schema from one database to another, or duplicate schema objects under another user in the same database. Use the schema mode export with the SCHEMAS= parameter along with other parameters.
Copy the entire database, usually performed for platform migration or to backup. Use the full mode export with the FULL=Y parameter along with other parameters.
Though table, schema, and full are the most commonly used modes, two more modes are available in Data Pump export/import:
Tablespace mode: Using the TABLESPACES= parameter, you can specify the tablespace names, and only objects belonging to the tablespace are exported. The dependent objects, even if they belong to another tablespace, are exported. For example, when exporting a table, if the indexes belong to another tablespace, they will still be exported.
Transport tablespace mode: When the TRANSPORT_TABLESPACES= parameter is used, only the metadata for the tables and their dependent objects within a specified set of tablespaces is exported. This mode is used to copy a tablespace physically (by copying the datafiles belonging to the tablespace) from one tablespace to another.
Oracle documentation and invoking expdp/impdp with HELP=Y provides you help with the modes of export. This chapter shows examples of not-so-common object and data migration requirements accomplished using Data Pump.
Though introduction to Data Pump is not an objective of this chapter, let us quickly review the common parameters used in expdp and impdp:
DIRECTORY: This is a database directory location with permission for the Oracle database owner to read and write. The default value and location for this parameter is defined by the DATA_PUMP_DIR directory.
DUMPFLE: This is the name of the dump file. The default value is expdat.dmp.
LOGFILE: This is the name of the log file. The default value is export.log.
FULL/SCHEMAS/TABLES: These are mutually exclusive parameters to specify the export/import mode.
CONTENT: This specifies if the export/import should include metadata only, data only, or both metadata and data.
EXCLUDE/INCLUDE: These are mutually exclusive parameters to refine the object types and names of objects to include or exclude in an export/import.
Working with Private and Public Objects
Public objects do not belong to any schema, and there is no PUBLIC schema per se for you to specify PUBLIC as the schema name while exporting to get all PUBLIC objects. Almost all objects that come under a schema can be considered private to the schema owner. One very special private object is a database link, as you cannot create a database link qualifying the link name with a schema name. Pretty much all other objects can be created by a DBA qualifying the object name with a schema name to create under a specific schema. Private database links are always created under the current user only. This section shows you how to export database links and synonyms.
Saving and Restoring Database Links
During database refreshes, the database links in the target database get wiped. When there are many private database links in the database, scripting and creating the database links become difficult because you cannot qualify the database link with a schema name to create the database link. You must login as the owner on the database link to create the database link. However, Data Pump can be used to make this process simple.
The EXCLUDE and INCLUDE parameters of Data Pump decides which objects to include or exclude. These parameters are very powerful and granular enough to export or import any type of object, including a database link.
The database links for the whole database can be saved to a dump file using
$ expdp dumpfile=uatdblinks.dmp directory=clone_save full=y
include=db_link userid ="/ as sysdba"
The code exports public database links and all private database links. If you are interested only in the private database links belonging few schema names, you could export using
$ expdp dumpfile=uatdblinks.dmp directory=clone_save
schemas=APPS,XX,XXL include=db_link userid ="/ as sysdba"
The code gets clumsy when you try to include complex parameters in the command line, especially with the addition of escape character before each ', ", and , special characters. It is better to use a parameter file so you do not have to worry about the escape characters. From now on, the examples in this chapter will show only the relevant parameters. Assume the inclusion of dumpfile, logfile, directory, and userid parameters with all examples.
Exporting Public Database Links and Synonyms
What if we need to save only the public database links in the export, or we want to follow a pattern matching for schema names? The EXCLUDE and INCLUDE parameters are very flexible and powerful, and they accept a subquery instead of hard-coded values. It is easy to figure out the public synonyms or public database links by querying the DBA_SYNONYMS or DBA_DB_LINKS views. Here is how you export only the public database links:
full=y
include=db_link:"IN (SELECT db_link
from dba_db_links Where Owner = 'PUBLIC')"
Since a query is used, you can imagine the possibilities are endless if you can write the appropriate query.
The next example shows how to export the public synonyms defined on the schema HR. This is useful when you have to copy a schema from one database to another. The schema level export only copies the database objects owned by the schemas you list in the SCHEMAS parameter. Public database links and public synonyms are included in the export only if full database export (FULL=Y) is performed.
full=y
include=DATABASE_EXPORT/SCHEMA/PUBLIC_SYNONYM/SYNONYM:"IN (SELECT
synonym_name from dba_synonyms
where Owner = 'PUBLIC' and table_owner = 'HR')"
Verifying Content of the Export Dump File
How do you verify if the expdp exported the database links (or, for that matter, any object) properly; in other words, how do you validate that the INCLUDE and EXCLUDE parameters you specified did what you expected them to do? You can use the impdp import with the SQLFILE parameter:
$ impdp dumpfile=pubdblinks.dmp directory=clone_save
full=y sqlfile=testfile.txt
Instead of doing the actual import, the metadata from the dump file is written to the file name specified in the SQLFILE parameter. Reading the content of the output file shows you what is exported:
$ cat testfile.txt
-- CONNECT SYS
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: DATABASE_EXPORT/SCHEMA/DB_LINK
CREATE PUBLIC DATABASE LINK "CZPRD.BT.NET"
USING 'czprd';
CREATE PUBLIC DATABASE LINK "ERP_ARCHIVE_ONLY_LINK.BT.NET"
CONNECT TO "AM_STR_HISTORY_READ" IDENTIFIED BY VALUES '05EDC7F2F211FF3A79CD5A526EF812A0FCC4527A185146A206C88098BB1FF8F0B1'
USING 'ILMPRD1';
If the dump file is big with rows exported, and if you are not interested in the rows, make sure you specify the CONTENT=METADATA_ONLY parameter along with SQLFILE.
Finding Valid INCLUDE and EXCLUDE Values
Metadata filtering is implemented through the EXCLUDE and INCLUDE parameters, and these parameters are mutually exclusive. In the previous examples, you saw the use of the EXCLUDE parameter. You can specify any valid database object in the EXCLUDE and INCLUDE parameters. So, for example, you can exclude constraints, but how do you know if you can exclude NOT NULL constraints from being exported? That is, how do you determine what are the valid values for EXCLUDE and INCLUDE parameters?
Depending on the type of export performed, you can query a database view to find the valid values:
To find the valid object types that can be excluded and included during full database export/import, query the DATABASE_EXPORT_OBJECTS view.
To find the valid object types that can be excluded and included during schema-level export/import, query the SCHEMA_EXPORT_OBJECTS view.
Similarly, use the TABLE_EXPORT_OBJECTS view object types during table-level or tablespace-level export/import.
Following is an example query from DATABASE_EXPORT_OBJECTS, looking for valid options to exclude constraints:
SQL> SELECT object_path, comments
FROM database_export_objects
WHERE object_path like '%CONSTRAINT%'
AND object_path not like '%/%';
OBJECT_PATH
COMMENTS
---------------------------------------------------
CONSTRAINT
Constraints (including referential constraints)
REF_CONSTRAINT
Referential constraints
Output shows that it is possible to exclude and include all constraints, or just referential constraints (foreign key constraints). By the way, when you exclude or include constraints, it does not consider NOT NULL constraints.
The syntax of the EXCLUDE and INCLUDE parameters allows a name clause along with the object type. The name clause evaluates the expression. The following examples help you understand the options and power of EXCLUDE and INCLUDE:
The following exports only the foreign key constraints belonging to HR schema:
schemas=HR
include='REF_CONSTRAINT'
The following exports a full database, except HR and SH schemas; notice the use of the IN expression:
full=y
exclude=schema:"in ('HR','SH')"
The following validates the full export did exclude HR and SH (it uses the impdp with SQLFILE). This is validated if there is a CREATE USER statement for HR and SH users. INCLUDE=USER only does the user creation; no schema objects are imported.
sqlfile=validate.txt
include=USER
The following exports all tables in the database that end with TEMP and do not belong to the SYSTEM and APPS schemas. Remember, we cannot use both INCLUDE and EXCLUDE at the same time.
full=y
include=table:"IN (select table_name from dba_tables
where table_name like '%TEMP'
and owner not in ('SYSTEM', 'APPS'))"
Exporting Subsets of Data
Sometimes when copying data to a test instance, you are not interested in all of the rows in a huge table but need only current data or a sample of data. There are a couple of different methods to export a subset of data from a table instead of exporting all rows. The most common and easy method is to use the QUERY parameter. As with other parameters, it is easier to have this parameter in a parameter file so that you need not worry about using the escape characters for all quotes and parentheses when doing on command line.
The WHERE clause you specify in QUERY is applied to all tables included in the export, unless you restrict the query to a single table by specifying the table name before the WHERE clause. For example, if you want to export the GL schema, but want only the last 6 months of data in the GL_TRANSACTIONS and GL_LINES table, you would do the following:
schemas=GL
query=GL_TRANSACTIONS:"where account_date > add_months(sysdate, -6)"
query=GL.GL_LINES:" where transaction_date > add_months(sysdate, -6)"
Notice the use of QUERY twice. This is required because we cannot specify more than one table before the WHERE clause used in QUERY.
That brings up a good question: What if you have many tables to which to apply this condition; do you have to repeat each table with the QUERY parameter? Yes, and you can. Or you can do two separate exports using the EXCLUDE or INCLUDE clause.
Let’s check out another example. Expanding on the earlier example, assume we want to apply the query condition to all tables that end with TRANSACTION. You need to perform two exports. First, export with these parameters:
schemas=GL
include=table:"like '%TRANSACTION'"
query="where account_date > add_months(sysdate, -6)"
Perform the second export without the QUERY parameter:
schemas=GL
exclude=table:"like '%TRANSACTION'"
It is also possible to export a subset of data from a table using the SAMPLE parameter by specifying a percentage of rows to export. Similar to QUERY, you can limit the sample condition to just one table or to the entire export. Following is an example of using SAMPLE parameter:
schemas=GL
sample=GL_TRANSACTIONS:20
sample=GL.GL_LINES:30
All the tables in the GL schema will be exported. For GL_TRANSACTIONS, only 20 percent of the rows are exported, and for GL_LINES only 30 percent of the rows exported.
The QUERY and SAMPLE conditions operate on a single table during export. In some circumstances, you want to export data that is a result of a complex join condition between multiple tables. You may be able to create a view, but export Data Pump until Oracle Database 11g Release 2 exports only the view definition, not the view data. From Oracle Database 12c onward, there is the VIEWS_AS_TABLES parameter to export the data in a view as a table. This is another good way to export a subset of data if the dataset is based on more than one table or complex joins.
In the following example, data from a view is exported. When you import, they go into the target database as a table.
Here is the information from the source database showing the columns, data type of EMP_DEPT, and that it is a view:
SQL> desc emp_dept
Name Null? Type
---------------- -------- -------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
ENAME VARCHAR2(10)
SQL> select object_type from user_objects where object_name = 'EMP_DEPT';
OBJECT_TYPE
-----------------------
VIEW
The following parameter is used to do the export of the view data:
views_as_tables=scott.emp_dept
Here is output from export:
$ expdp parfile=p1.txt
Export: Release 12.1.0.2.0 - Production on Wed Apr 8 22:06:49 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/********@orcl parfile=p1.txt
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
Total estimation using BLOCKS method: 16 KB
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
. . exported "SCOTT"."EMP_DEPT" 6.234 KB 14 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
*************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home/oracle/app/oracle/admin/cdb1/dpdump/x1.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at
Wed Apr 8 22:06:59 2015 elapsed 0 00:00:08
Now, the following parameter file imports the contents of the dump file to another database, under the MIKE schema:
remap_schema=scott:mike
The output from import shows the view is created as a table, and rows are imported:
$ impdp parfile=p1.txt
Import: Release 12.1.0.2.0 - Production on Wed Apr 8 22:09:42 2015
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@orcl parfile=p1.txt
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE
Processing object type TABLE_EXPORT/VIEWS_AS_TABLES/TABLE_DATA
. . imported "MIKE"."EMP_DEPT" 6.234 KB 14 rows
Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at
Wed Apr 8 22:09:47 2015 elapsed 0 00:00:03
Finally, the object type and columns are validated using the same queries used before:
SQL> desc emp_dept
Name Null? Type
--------------- -------- -----------------
DEPTNO NUMBER(2)
DNAME VARCHAR2(14)
ENAME VARCHAR2(10)
SQL> select object_type from user_objects where object_name = 'EMP_DEPT';
OBJECT_TYPE
-----------------------
TABLE
Changing Object Properties
Data Pump import offers a very flexible way to change the properties of objects during import. This section uses examples to make you aware of the capabilities. These examples don’t go into a lot of detail or provide syntax, but you can always review the documentation to learn more.
Importing Partitioned Tables as Nonpartitioned
This example uses Data Pump to export the partitioned table SALES_DATA owned by SLS schema. During import, you need to create the table as non-partitioned under SLS_HIST schema. The REMAP_SCHEMA parameter changes the schema owner and the MERGE option for partitions creates the table as non-partitioned.
REMAP_SCHEMA=SLS:SLS_HIST
TABLES=SLS.SALES_DATA
PARTITIONS_OPTIONS=MERGE
Importing Table Partitions as Individual Tables
This example uses Data Pump to export the partitioned table SALES_DATA owned by the SLS schema. During import, you need to create a separate nonpartitioned table for each partition of SALES_DATA.
PARTITION_OPTIONS=DEPARTITION
TABLES=SLS.SALES_DATA
Since the partition names in SALES_DATA are SDQ1, SDQ2, SDQ3, and SDQ4, the new table names created would be SALES_DATA_SDQ1, SALES_DATA_SDQ2, SALES_DATA_SDQ3, SALES_DATA_SDQ4.
Masking Data
Often it is necessary to copy tables and schema from a production database to a nonproduction database where sensitive data needs to be scrambled or masked in the nonproduction database. Export and import in Data Pump provide a masking feature using the REMAP_DATA parameter.
Employee table HR.ALL_EMPLOYEES contains the social security numbers of employees along with other information. When copying this table from a production to a development database, you want to scramble the social security numbers, or mask them, or convert them to different values:
1. First, create a package and a function that returns the scrambled SSN when the production SSN is passed in as a parameter. Let’s call this procedure HR_UTILS.SSN_SCRAMBLE.
2. During import, use the REMAP_DATA parameter to change the data value:
TABLES=HR.ALL_EMPLOYEES
REMAP_DATA=HR.ALL_EMPLOYEES.SSN:HR.HR_UTILS.SSN_SCRAMBLE
If you want to secure the dump file, it may be appropriate to use this function and parameter in the export itself. Thus, the dump file will not have SSNs but instead the scrambled numbers only.
Renaming Tables or Different Tablespaces
Sometimes you have to bring a table back from export backup but with a different name. In this example, the table DAILY_SALES is imported to the database as DAILY_SALES_031515. The export dump is a SALES schema export. The original table is on the tablespace SALES_DATA, and the restored table needs to go on the tablespace SALES_BKUP.
TABLES=SALES.DAILY_SALES
REMAP_TABLE=SALES.DAILY_SALES:DAILY_SALES_031515
REMAP_TABLESPACE:SALES_DATA:SALES_BKUP
Using Default Storage Parameters
If you do not wish import to bring any of the attributes associated with a table or index segment, and you want to instead have the object take the defaults for the user and the destination database/tablespace, you can do so easily with a transform option. In this example, SALES schema objects are imported, but the tablespace, storage attributes, and logging clause all will default to what is defined for the user MIKE and his default tablespace:
SCHEMAS=SALES
REMAP_SCHEMA=SALES:MIKE
TRANSFORM=SEGMENT_ATTRIBUTES:N
In this example, if you would like to keep the original tablespace but only want to remove the storage attributes for the object type index, you could specify the optional object along with the TRANSFORM parameter. The valid object type values are TABLE or INDEX. In the following example, tables will use the default tablespace and storage characteristics, and indexes will go to the source index tablespace but will not use any of the source storage clauses:
SCHEMAS=SALES
REMAP_SCHEMA=SALES:MIKE
TRANSFORM=SEGMENT_ATTRIBUTES:N:TABLE
TRNASFORM=STORAGE:N:INDEX
Resizing Tablespaces during Import
Another unused but useful TRANSFORM option is the PCTSPACE parameter. When you specify a positive value for this parameter between 1 and 100, the value is used as a percentage multiplier for data files and extent allocations.
For example, the following creates each tablespace at 30 percent of its original size:
FULL=Y
TRANSFORM=PCTSPACE:30
The percentage multiplier is also applied to the extent allocations.
Consolidating Multiple Tablespaces
User data warehouse (DWH) owns several tables and indexes and materialized views. The user DWH has objects on more than 40 tablespaces in the database. It’s been a few years since DWH user objects have been reorganized, and there has been storage reduction and some performance gain when the objects are reorganized because most tables involve large amounts of INSERT and DELETE operations daily.
Say, for example, you are performing a planned reorg. To keep the tablespace management simple, you’re going to consolidate all tablespaces into two tablespaces: DWH_DATA for table and materialized views, and DWH_INDEX for indexes. Since this database got upgraded from an older version, use of the LONG datatype is widespread and hence ALTER TABLE MOVE is not an appropriate option. Therefore, you’ll use Data Pump as follows:
1. Export the DWH schema.
2. Use the LOGTIME parameter to add a timestamp to the output messages and log file. This is very useful for timing the different stages of export or import.
3. Use the METRICS parameter to show the time taken to export or import a category of objects:
SCHEMAS=DWH
LOGTIME=ALL
METRICS=YES
PARALLEL=8
DUMPFILE=dwh_exp_%U.dmp
DIRECTORY=SCRATCH_AREA
FILESIZE=4GB
STATUS=180
EXCLUDE=STATISTICS
4. Capture the grants given to the DWH schema by other users, including SYS/SYSTEM.
5. To be on the safe side, and to bring back any missing privileges or objects, perform another full export of the database without any data.
6. Use CONTENT=METADATA_ONLY to tell Data Pump to not export any data, but only the data definition language (DDL) statements:
FULL=Y
CONTENT=METADATA_ONLY
LOGTIME=ALL
METRICS=YES
DUMPFILE=full_exp.dmp
DIRECTORY=SCRATCH_AREA
STATUS=180
7. Drop the DWH schema and its objects, and drop all the tablespaces where DWH had objects. When dropping user DWH, the public synonyms will remain in the database, so no need to do anything with public synonyms.
8. Create new tablespaces DWH_DATA and DWH_INDEX. Create a DWH user, with default tablespace DWH_DATA.
9. Grant DWH privileges on other user objects including SYS/SYSTEM.
10. Perform an import of the DWH schema. The default tablespace for the DWH user is DWH_DATA, so by default objects will go to that tablespace. Do the import in two parts:
SCHEMAS=DWH
LOGTIME=ALL
METRICS=YES
PARALLEL=8
DUMPFILE=dwh_exp_%U.dmp
DIRECTORY=SCRATCH_AREA
STATUS=180
EXCLUDE=INDEX
TRANSFORM=SEGMENT_ATTRIBUTES:N
11. There are two methods to get the indexes in DWH_INDEX tablespace. Use the SQLFILE option to generate the index creation statements, and replace the tablespace clause with DWH_INDEX tablespace in the dwh_index.sql file and run the file:
SCHEMAS=DWH
DUMPFILE=dwh_exp_%U.dmp
DIRECTORY=SCRATCH_AREA
INCLUDE=INDEX
TRANSFORM=STORAGE:N
SQLFILE=dwh_index.sql
Another method is to set the default tablespace of the user DWH to DWH_INDEX and perform the import as follows:
SCHEMAS=DWH
LOGTIME=ALL
METRICS=YES
PARALLEL=8
DUMPFILE=dwh_exp_%U.dmp
DIRECTORY=SCRATCH_AREA
INCLUDE=INDEX
TRANSFORM=SEGMENT_ATTRIBUTES:N
12. Recompile all invalid objects by using ?/rdbms/admin/utlrp.sql.
13. Gather statistics on the DWH schema:
DBMS_STATS.GATHER_SCHEMA_STATS('DWH');
Using PL/SQL API with Data Pump
So far in this chapter, we have seen only the Data Pump client tools expdp and impdp used. The discussion will be incomplete if we do not talk about using SQL*Plus (PL/SQL API) to perform export and import operations. The expdp and impdp client tools use the DBMS_DATAPUMP package for all the operations. If you can use this package directly from the database, it gives you more flexibility and endless options. Refer to PL/SQL Reference Guide for complete documentation on DBMS_DATAPUMP and the procedures and functions available. For a simple export, you would use the following:
OPEN: Define an export or import job.
ADD_FILE: Define the dump file name and log file name.
METADATA_FILTER: Provide the exclude or include clauses.
START_JOB: Kick off the export or import job.
WAIT_FOR_JOB: Wait until the job completes.
DETACH: Close the job.
Listing 13.1 utilizes these subprograms and demonstrates how to export tables belonging to a schema.
Listing 13.1 Exporting a Schema Using SQL*Plus
CREATE OR REPLACE PROCEDURE XX_BACKUP_TABLES (
schema_name VARCHAR2 DEFAULT USER)
IS
/***************************************************
NAME: XX_BACKUP_TABLES
PURPOSE: Backup tables under schema
****************************************************/
BEGIN
-- Export all the tables belonging to schema to file system
DECLARE
dpumpfile NUMBER;
dpumpstatus VARCHAR2 (200);
tempvar VARCHAR2 (200);
BEGIN
EXECUTE IMMEDIATE
'CREATE OR REPLACE DIRECTORY BACKUP_DIR AS ''/u01/app/oracle/expdp''';
BEGIN
-- verify if the Data Pump job table exist.
-- drop if exist.
SELECT table_name
INTO tempvar
FROM user_tables
WHERE table_name = 'BACKUP_TABLE_EXP';
EXECUTE IMMEDIATE 'DROP TABLE BACKUP_TABLE_EXP';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
NULL;
END;
-- define job
dpumpfile :=
DBMS_DATAPUMP.open (OPERATION => 'EXPORT',
JOB_MODE => 'SCHEMA',
JOB_NAME => 'BACKUP_TABLE_EXP');
-- add dump file name
DBMS_DATAPUMP.add_file (
HANDLE => dpumpfile,
FILENAME => 'BACKUP_tabs.dmp',
DIRECTORY => 'BACKUP_DIR',
FILETYPE => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE,
REUSEFILE => 1);
-- add log file name
DBMS_DATAPUMP.add_file (
HANDLE => dpumpfile,
FILENAME => 'BACKUP_tabs.log',
DIRECTORY => 'BACKUP_DIR',
FILETYPE => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);
-- add filters to export only one schema
DBMS_DATAPUMP.metadata_filter (HANDLE => dpumpfile,
NAME => 'SCHEMA_LIST',
VALUE => schema_name);
-- start the export job
DBMS_DATAPUMP.start_job (HANDLE => dpumpfile);
-- wait until the job completes
DBMS_DATAPUMP.wait_for_job (HANDLE => dpumpfile, JOB_STATE => dpumpstatus);
-- close the job
DBMS_DATAPUMP.detach (HANDLE => dpumpfile);
EXCEPTION
WHEN OTHERS
THEN
DBMS_DATAPUMP.detach (HANDLE => dpumpfile);
RAISE;
END;
END XX_BACKUP_TABLES;
/
Monitoring and Altering Resources
Since Data Pump jobs are executed at the database server by server-side processes, you can exit out of the terminal where you started the export or import. After the job is initiated, the export or import job status is displayed on the screen, and the session is occupied until the job is complete. You may press ^C to exit out of the job status logging mode and enter interactive mode.
In interactive mode, several commands are available to control the job:
Additional dump files may be added to the export job by using the ADD_FILE command, and dump file size for newer files can be adjusted using the FILESIZE command.
The STATUS command shows a detailed status of the job, with details on what each worker job is doing. The STOP_JOB command pauses the job for a later restart, whereas KILL_JOB terminates the job completely. The PARALLEL command is used to increase or decrease the number of worker processes.
To get back to the logging mode, use the command CONTINUE_CLIENT, and if you are ready to exit out of the job status mode to the OS command prompt, use EXIT_CLIENT command. Though you exited out of the job session, the job continues to run. Maybe it is the end of your shift and you want to gracefully exit out of the logging mode of export job: press ^C and then use the EXIT_CLIENT command.
After you reach home and if you want to check the status of the job, you have to attach to the job using the ATTACH parameter of expdp or impdp. If you do not remember the job name, it can be obtained from DBA_DATAPUMP_JOBS. To attach to a running or paused job, you have to connect to Data Pump using the same credentials used to start the job.
DBA_DATAPUMP_SESSIONS view shows all data pump sessions connected to the database.
Improving Performance
While hardware resources, a faster disk system, and proper setup improve database performance, they will also help Data Pump jobs. Additionally, there are certain parameters you can consider to further boost performance:
As with any database operation, Data Pump also benefits from accurate statistics available in the database for dictionary objects. Collect DBMS_STATS.GATHER_DICTIONARY_STATS before a Data Pump job for better performance if dictionary statistics were not collected recently.
If you are using Data Pump import on Oracle Database 12c, consider using the option to not generate archive logs:
TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
Thus, the objects are created by the Data Pump in NOLOGGING mode and are switched to their appropriate LOGGING or NOLOGGING mode once import completes. If your database is running in FORCE LOGGING mode, this Data Pump NOLOGGING will have no impact.
Parallelizing Data Pump jobs using the PARALLEL parameter improves performance, but there are some objects with which Data Pump does not do parallel operation—for example, creating indexes. So when importing a large database, it is better to generate the index creation statements using the SQLFILE option, and exclude indexes during the table import. The PL/SQL objects, views, and several other objects are created after the table import while doing a schema or full database import. You may let the full import without indexes run, and as soon as the table import portion is complete, you can kick off the index creation script. If you have a large, temporary tablespace and PGA area, you could split the index creation to several scripts and run them in parallel in multiple sessions.
When performing import with a large number of tables and indexes, such as an Oracle E-Business Suite (EBS) database, it takes a while to perform import of statistics. Most of the time, you end up collecting statistics after an import, so it is better to exclude statistics from export/import altogether. You can exclude statistics in export (you may also do so during import):
EXCLUDE=STATISTICS
Usually, import jobs involve creating many indexes and thus have a need for sort operations. Having PGA of adequate size or a little larger will help with Data Pump imports.
If your database export includes a table with a large amount of BLOB data, it is better to exclude this table from the full export (for example, on an EBS database, the FND_LOBS table), and perform a separate export. Even if you use the PARALLEL parameter for the BLOB table export, Data Pump is going to use only one process. Export the BLOB table as smaller chunks, performing multiple export jobs on the same table—be sure to restrict the rows in each export. For example, if you have 500 rows in the table, and you want to split the table into 50 parallel export sessions, you need to restrict each export to 10 rows each. Use the QUERY parameter to filter the rows. When importing using these dump files, make sure you use the TABLE_EXISTS_ACTION=APPEND option.
Upgrading Databases
Though Data Pump is not considered a primary tool for database upgrades, there are situations in which database upgrade using Data Pump is suitable:
If you are upgrading from a database version that does not support direct upgrade, it may be easier to perform an export/import upgrade rather than going through two or more direct upgrades. For example, to upgrade to Oracle Database 12.0.2, the database has to be version 10.2.0.5, 11.1.0.7, or 11.2.0.2+. If the database isn’t any of these versions, export/import upgrade is preferable. Again, the decision depends on the size of the database. One advantage of using the export/import method is that the source database remains intact, so if something did not work the way it was supposed to, rolling back to an old environment is a snap.
Sometimes it is necessary to convert the character set of a database when you are planning to store multiple languages or need to store 16-byte characters. Export/import is proven to be the most efficient and worry-free method for character set conversion. If you are considering platform migration, instead of migrating the database to a new platform, you can create a new database and perform export/import. This is a common requirement if you are moving from or to Linux.
If you archived or purged large amounts of data, use the upgrade/migration opportunity to reduce the size of the database.
In Oracle database 12c, use the multitenant architecture by combining multiple Oracle 10g and 11g databases.
If the database you are upgrading from is older than Oracle 10g, then Data Pump is not available. You will have to use the legacy exp/imp tool. The good news is that the exp/imp tools are still supported in Oracle Database 12c.
When you are exporting from a higher version of database to import to a lower version of database, include the VERSION parameter so that only objects and features compatible with the target version are exported.
Detailed upgrade methods and options are beyond the scope of this chapter. See Chapter 14, “Strategies for Migrating Data Quickly between Databases,” to review the various migration methods available, which can be used to upgrade the database as well.
Summary
In this chapter, you learned the power of Data Pump and its flexibility to filter data and objects. You also learned how to use various combinations of export and import parameters to do various day-to-day and not-so-common DBA tasks.
There are various modes to export data and objects. Although the FULL mode is used to export the entire database, appropriate INCLUDE or EXCLUDE clauses can be used to filter the objects to export only what you need. Having the flexibility to use queries for metadata filtering is very powerful, enabling you to export any object, including public synonyms on a filtered object list.
The content of the export dump file can be verified by using the SQLFILE option, though this option is really meant to generate SQL commands out of the dump file.
Exporting subsets of data can be accomplished by using the QUERY parameter or by using the Oracle Database 12c feature VIEWS_AS_TABLES.
During import, many object properties can be changed. You can change the owner of the object, change the tablespace, merge partitions, adjust storage properties, and much more.
You also learned to consolidate multiple tablespace objects into a few, how to improve Data Pump performance, and how to use the DBMS_DATAPUMP API to code PL/SQL program to perform export and import.
The export/import method is used not only for data and object migration between databases but also for upgrading databases.