Oracle Data Pump

We can make backup and recovery on the Oracle database using physical or logical methods.RMAN backup and recovery is one of the physical backup & recovery methods. We can also make logical backups and recoveries using the EXPORT & IMPORT utilities of the Oracle database. 

Starting from Oracle 10g, a new feature of export/ import utilities that supports data pump technology, expdp & impdp, are introduced. The old version or the standard export & import utilities are also called exp and imp respectively. The data pump comprises the features of the standard import/export utilities and has new features that enable us to efficiently backup/restore and move data among different environments. 

We can import and export specific tables, schema, tablespace, and the entire database using data pump technology, expdp, and impdp.

On this topic, we will see some practical examples of how to export and import a schema, drop a schema, and then re-create it using data pump technology. We will also do the same on a table and tablespace.

Exporting Schema

Before running the export and import jobs, we shall log in to the database through the SYS user, using SQL*PLUS the command-line tool. Then we will create a database directory and grant read & write privileges on the directory object to the database schema running the export.

For this practice, we assume that there is a schema named mere and the schema comprise 

tables, views, and other database objects inside it.

Create a database directory object named 'data_pump' 

[oracle@srv1 ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 23 17:49:25 2024

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

SQL>
SQL> conn sys/orcl@orcl as sysdba Connected.
SQL> create directory data_pump as '/u01/app/oracle/DATAPUMP_BACKUP';
Directory created.
SQL>

And then grant access to schema named 'SH' on the directory created above.

SQL> grant write, read on directory data_pump to SH;

Grant succeeded.

Finally, run the expdp utility to export the schema.

[oracle@srv1 ~]$ sqlplus /nolog

SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 23 17:49:25 2024

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

SQL>

SQL> conn sys/orcl@orcl as sysdba
Connected.
SQL> exit

$expdp system/orcl directory=data_pump schemas=SH dumpfile=SH.dmp logfile=SH.log
Export: Release 12.1.0.2.0 Production on Fri Feb 23 17:49:25 2024

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=data_pump schemas=SH dumpfile=SH.dmp logfile=SH.log
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
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
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "SH"."TBL_CUSTOMER" 6.859 KB 3 rows
. . exported "SH"."EMP_SAL" 5.914 KB 1 rows
. . exported "SH"."SH" 5.492 KB 1 rows
. . exported "SH"."STTMS_CUST_ACCOUNT" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/DATAPUMP_BACKUP/SH.DMP
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Feb 23 17:49:25 2024 elapsed 0 00:01:10

Exporting Table

The process is the same as exporting of schema that we have already done in the SQL commands above. But when we export a schema, we are exporting all the database objects inside it. And when we are talking about exporting tables, we mean exporting one or more tables only. Here below is a data pump example of exporting a single table named tbl_customer.

[oracle@srv1 ~]$sqlplus/nolog

SQL*Plus: 12.1.0.2.0 Production on Fri Feb 23 17:59:25 2024

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

SQL> conn sys/orcl@orcl as sysdba
Connected.
SQL> $expdp system/orcl directory=data_pump tables=SH.tbl_customer dumpfile=tbl_customer.dmp logfile=tbl_customer.log;

Export: Release 12.1.0.2.0 Production on Fri Feb 23 18:00:25 2024

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=data_pump tables=SH.tbl_customer dumpfile=tbl_customer.dmp logfile=tbl_customer.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "SH"."TBL_CUSTOMER" 6.859 KB 3 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/DATAPUMP_BACKUP/TBL_CUSTOMER.DMP
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Fri Feb 23 18:00:25 2024 elapsed 0 00:00:32

Export Tablespace 

A tablespace is a logical container for physical database objects. A tablespace can contain one or more schema objects. When we are talking about exporting a tablespace, we mean exporting one or more schema objects that the tablespace might encompass. Here below is a data pump example of exporting a tablespace named users.

$ expdp SH/SH directory=data_pump dumpfile=users.dmp tablespaces=users

Export:  Release 12.1.0.2.0 Production on Fri Feb 23 18:10:25 2024

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Starting "SYSTEM"."SYS_EXPORT_TABLESPACE_01": SYSTEM/******** directory=data_pump dumpfile=users.dmp tablespaces=users
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
. . exported "SH"."TBL_CUSTOMER" 6.859 KB 3 rows
. . exported "SH"."EMP_SAL" 5.914 KB 1 rows
. . exported "SH"."SH" 5.492 KB 1 rows
. . exported "AMAN"."AMAN" 0 KB 0 rows
. . exported "SH"."STTMS_CUST_ACCOUNT" 0 KB 0 rows
Master table "SH"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SH.SYS_EXPORT_TABLESPACE_01 is:
/u01/app/oracle/DATAPUMP_BACKUP/USERS.DMP
Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at Fri Feb 23 18:10:25 2024 elapsed 0 00:00:37

Import Schema

Here below is a data pump example on how to drop a schema and recreate it using the impdp utility, from the export dump file of a schema created previously.

--Dropping a schema named SH
[oracle@srv1 ~]$sqlplus/nolog

SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 23 19:10:25 2024

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

SQL> conn sys/orcl@orcl as sysdba
Connected.
SQL> drop user SH;
drop user SH
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'SH'

SQL> drop user SH cascade;

User dropped.
SQL>
---Re-creating schema SH from dumpfile SH.dmp create previously.
[oracle@srv1 ~]$sqlplus/nolog

SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 23 19:15:25 2024

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

SQL> conn sys/orcl@orcl as sysdba
Connected.
SQL> exit

$impdp system/orcl directory=data_pump schemas=SH dumpfile=SH.dmp logfile=SH.log

Import: Release 12.1.0.2.0 - Production on Fri Feb 23 19:18:25 2024

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=data_pump schemas=SH dumpfile=SH.dmp logfile=SH.log
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
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "SH"."TBL_CUSTOMER" 6.859 KB 3 rows
. . imported "SH"."EMP_SAL" 5.914 KB 1 rows
. . imported "SH"."SH" 5.492 KB 1 rows
. . imported "SH"."STTMS_CUST_ACCOUNT" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_INDEX/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Fri Feb 23 19:20:25 2024 elapsed 0 00:00:36

SQL>

Now, to confirm whether the schema is recreated or not let's run the following SQL statement.

SQL> conn SH/SH@orcl
Connected.
SQL>

[oracle@srv1 ~]$sqlplus/nolog

SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 23 19:20:25 2024

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

SQL> conn SH/SH@orcl
Connected.

---Drop table tbl_customer
SQL> drop table tbl_customer;

Table dropped.

SQL>

---Import table tbl_customer
[oracle@srv1 ~]$sqlplus/nolog

SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 23 19:21:25 2024

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

SQL> conn sys/orcl@orcl as sysdba
Connected.
SQL>exit 

$impdp system/orcl directory=data_pump tables=SH.tbl_customer dumpfile=tbl_customer.dmp

Import: Release 12.1.0.2.0 - Production on Fri Feb 23 19:22:25 2024

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

Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01": system/******** directory=data_pump tables=SH.tbl_customer dumpfile=tbl_customer.dmp
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SH"."TBL_CUSTOMER" 6.859 KB 3 rows
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Fri Feb 23 19:30:25 2024 elapsed 0 00:00:10

And again to confirm whether the table is re-created or not and let's run the following SQL statement.

SQL> select * from SH.tbl_customer;

CUST_NO    CUSTOMER_T CUST_NAME  SWIFT_CODE  COUNTRY 		
---------- ---------- ---------- ----------- ------------
########## 	I      Messi 	 John Dave 	 USA
########## 	C      Mereba 	 Technology	 UK
########## 	B      Swiss 	 Bank 		 Switzerland
SQL>

Oracle RAC Database Architecture Overview

Oracle Database can be configured in two basic ways: as a single instance (SI) database (non-cluster) or a Real Application Clusters (RAC) database. A single instance database has the Oracle software, database files, memory, and processes on one server. (link to database technical architecture diagram). A single-instance database has a one-to-one relationship between the instance (memory and processes) and the database (database files). Oracle RAC environments, however, have a one-to-many relationship between the database and instances. An Oracle RAC database can have up to 100 instances, all of which access one database. All the servers for a single database in the Oracle RAC environment must belong to the same Oracle Real Application Cluster.

Fig 1.1: Real Application Clusters (RAC) Overview

All application clusters have some common features, each cluster has Grid Infrastructure (GI) installed locally on each node. Grid Infrastructure includes several products: Automatic Storage Management, Oracle Clusterware, and others.

Each instance in an Oracle RAC cluster accesses the database files stored on shared storage. Each Node is also available and requires local storage that is used to store GI software and Oracle databases.

Each node needs at least a public interface and a private interface. Each network may use multiple network interface cards to increase bandwidth, availability, or fault tolerance. Oracle Corp. recommends that each network use multiple network interface cards (NICs) per node and multiple network switches in each network to avoid a single point of failure.

A cluster consists of several connected computers or servers that appear as they are a single server to end users and applications. Oracle RAC has an Oracle database option that allows you to connect to Oracle databases. Oracle RAC uses Oracle Clusterware for infrastructure to connect multiple servers together to work as one system

What is Oracle Real Application Clusters

• Multiple instances of accessing the same database
• One instance per node
• Physical or logical access to each database file
• Software-controlled data access
• Multiple instances have the same OS, the same database version, and the same network with shared storage.

You may see a few additional background processes associated with a RAC instance than you would with a single instance database. These processes are primarily used to maintain database consistency among each instance. 

They manage what is called the global resources:

    • LMON: Global Enqueue Service Monitor
    • LMD0: Global Enqueue Service Daemon
    • LMSx: Global Cache Service Processes, where x can range from 0 to j
    • LCK0: Lock process

    • DIAG: Diagnosability process

At the cluster level, you find the main processes of Oracle Clusterware. They provide a standard cluster interface on all platforms and perform high-availability operations. You find these processes on each node of the cluster

    • CRSD and RACGIMON: Are engines for high-availability operations

    • OCSSD: Provides access to node membership and group services

    • EVMD: Scans callout directory and invokes callouts in reaction to detected events

    • OPROCD: Is a process monitor for the cluster

Several tools are used to manage the various resources available on the cluster at a global level. These resources are the Automatic Storage Management (ASM) instances, the RAC databases, the services, and node applications. Some of the tools that you use throughout this post are Server Control (SRVCTL), DBCA, CRSCTL, ASMCMD.

Fig 1.2: Oracle Real Application Clusters (RAC) Architecture

RAC stands for Real Application Clusters, which is an Oracle database feature that allows multiple instances to access a single database, sharing the workload and providing high availability and scalability. 

The architecture of RAC involves several key components:

Clusterware: This is the foundation of the RAC architecture. It provides the infrastructure for node membership, communication, and cluster coordination. Oracle Clusterware ensures that all nodes in the cluster can work together seamlessly.

Cluster Interconnect: It's the private network used by cluster nodes for communication. This network is crucial for RAC as it ensures fast and reliable communication between instances, which is essential for maintaining data consistency and synchronization.

Shared Storage: All nodes in the RAC cluster must have access to shared storage where the database files reside. This shared storage can be in the form of a SAN (Storage Area Network) or NAS (Network Attached Storage),and it allows any instance in the cluster to access and modify the database files as needed.

Oracle Database: Each node in the cluster runs an instance of the Oracle Database software. These instances work together to provide access to the same database. Clients connecting to the database can be directed to any instance in the cluster, and Oracle's Cache Fusion technology ensures that data consistency is maintained across instances.

Global Cache Service (GCS): GCS is responsible for managing the global cache in a RAC environment. It coordinates access to data blocks across multiple instances, ensuring that each instance has the most up-to-date version of a data block when needed.

Oracle Services: RAC uses Oracle Services to provide high availability and workload management. Services define the characteristics of connections to the database, such as load balancing and failover behavior. By using services, administrators can control which nodes handle specific types of workloads and how connections are redirected in case of a node failure.

Automatic Storage Management (ASM): ASM is a feature provided by Oracle that simplifies storage management for database files. It abstracts the underlying storage hardware and provides features such as disk mirroring and striping, making it easier to manage storage in a RAC environment.

Overall, the RAC architecture is designed to provide high availability, scalability, and performance for Oracle databases by distributing workload across multiple nodes in a cluster and ensuring that data remains consistent and accessible even in the event of node failures.

Oracle RAC History

The first Oracle cluster database was released with Oracle 6 for the digital VAX, this was the first cluster database on the market. With Oracle 6.2 Oracle Parallel Server (OPS) was born, which used Oracle's own DLM (Distributed Lock Manager). Oracle 7 used vendor-supplied clusterware but this was complex to set up and manage, Oracle 8 introduced a general lock manager and this was a direction for Oracle to create its own clusterware product. Oracle's lock manager is integrated with Oracle code with an additional layer called OSD (Operating System Dependent), this was soon integrated within the kernel and became known as IDLM (Integrated Distributed Lock Manager) in later Oracle versions. Oracle Real Application Clusters 9i (Oracle RAC) used the same IDLM and relied on external clusterware software (Sun Cluster, Veritas Cluster, etc).

Overview of Oracle Data Pump

Oracle Data Pump technology enables very high-speed movement of data and metadata from one database to another. 

Data Pump Components 

Oracle Data Pump is made up of three distinct parts:

• The command-line clients,expdp and impdp
• The DBMS_DATAPUMP PL/SQL package (also known as the Data Pump API)
• The DBMS_METADATA PL/SQL package (also known as the Metadata API)

The expdp and impdp clients use the procedures provided in the DBMS_DATAPUMP PL/SQL package to execute export and import commands, using the parameters entered at the command line.

Note: All Data Pump Export and Import processing, including the reading and writing of dump files, is done on the system(server) selected by the specified database connect string. This means that, for nonprivileged users, the database administrator (DBA) must create directory objects for the Data Pump files that are read and written on that server filesystem. For privileged users, a default directory object is available.

Data Pum Export Options

Oracle provides the OS utility expdp that serves as the interface to Data Pump. Some of the options will be familiar if you have previous experience with the Export utility. However, some significant features are available only via Data Pump. Table 1-1 shows the command-line input parameters for expdp when creating a job. These parameters can be specified in a parameter file unless otherwise noted.

When you submit a job, Oracle will give the job a system-generated name. If you specify a name for the job via the JOB_NAME parameter, you must be certain that the job name will not conflict with the name of any table or view in your schema. During Data Pump jobs, Oracle will create and maintain a master table for the duration of the job. The master table will have the same name as the Data Pump job, so its name cannot conflict with existing objects. While a job is running, you can execute the commands in Table 1-2 via Data Pump’s interface.

Starting a Data Pump Export Job

Creating a Directory

Data Pump requires you to create directories for the datafiles and log files it will create and read. Use the CREATE DIRECTORY command to create the directory pointer within Oracle to the external directory you will use. Users who will access the Data Pump files must have the READ and WRITE privileges on the directory. Before you start, verify that the external directory exists and that the user who will be issuing the CREATE DIRECTORY command has the CREATE ANY DIRECTORY system privilege.

NOTE: In a default installation of Oracle Database 12c, a directory object called DATA_PUMP_DIR is created and points to the directory $ORACLE_BASE/admin/database_name/dpdump in a non-multitenant environment.

The following example creates a directory object called DPXFER in the Oracle instance dw referencing the file system directory /u01/app/oracle/DataPumpXfer and grants READ and WRITE access to the user RJB:

SQL> create directory dpxfer as '/u01/app/oracle/DataPumpXfer';

Directory created.

SQL> grant read, write on directory dpxfer to rjb;

Grant succeeded.

SQL>

The RJB user can now use the DPXFER directory for Data Pump jobs. The file system directory /u01/app/oracle/DataPumpXfer can exist on the source server, the target server, or any server on the network, as long as each server can access the directory and the permissions on the directory allow read/write access by the oracle user (the user that owns the Oracle executable files).On the server oc1, the administrator creates a directory with the same name that references the same network file system, except that privileges on the directory are granted to the HR user instead:

SQL> create directory dpxfer as '/u01/app/oracle/DataPumpXfer';

Directory created.

SQL> grant read,write on directory dpxfer to hr;

Grant succeeded.

SQL>

You can store your job parameters in a parameter file, referenced via the PARFILE parameter of expdp. 

For example, you can create a file named dp_rjb.par with the following entries:

directory=dpxfer

dumpfile=metadata_only.dmp

content=metadata_only

The logical Data Pump directory is DPXFER, the one created earlier in the chapter. The Data Pump Export will only have metadata; the name of the dump file, metadata_only.dmp, reflects the contents of the dump file. Here’s how you initiate a Data Pump job using this parameter file:

expdp rjb/rjb parfile=dp_rjb.par

Oracle will then pass the dp_rjb.par entries to the Data Pump Export job. A schema-type Data Pump Export (which is the default) will be executed, and the output (metadata only, no table rows) will be written to a file in the DPXFER directory. Here is the output from the expdp command:

[oracle@dw ~]$ expdp rjb/rjb parfile=dp_rjb.par

Export: Release 12.1.0.2.0 - Production on Thu Nov 13 09:13:10 2014

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, Automatic Storage Management, OLAP, Advanced Analytics

and Real Application Testing options

Starting "RJB"."SYS_EXPORT_SCHEMA_01": rjb/******** parfile=dp_rjb.par

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

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type SCHEMA_EXPORT/STATISTICS/MARKER

Master table "RJB"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for RJB.SYS_EXPORT_SCHEMA_01 is:

/u01/app/oracle/DataPumpXfer/metadata_only.dmp

Job "RJB"."SYS_EXPORT_SCHEMA_01" successfully completed at Thu Nov 13 09:13:50 

2014 elapsed 0 00:00:27 

[oracle@dw ~]$

HELP : Default:N.

Displays online help for the Export utility.

HELP = {y | n}

> expdp HELP = y