CHAPTER 17

Moving and Re-organizing Data

Exam Objectives

• 062.17.1    Describe Ways to Move Data

• 062.17.2    Create and Use Directory Objects

• 062.17.3    Use SQL*Loader to Load Data from a Non-Oracle Database

• 062.17.4    Use External Tables to Move Data via Platform-Independent Files

• 062.17.5    Explain the General Architecture of Oracle Data Pump

• 062.17.6    Use Data Pump Export and Import to Move Data between Oracle Databases

• 063.15.1    Use Data Pump in a Multitenant Environment

• 063.15.2    Use SQL*Loader in a Multitenant Environment

A common need in a database environment is to move data from one database to another. Oracle provides two facilities for this: Data Pump and SQL*Loader. Data Pump can transfer data between Oracle databases (across versions and platforms), whereas SQL*Loader can read datasets generated by non-Oracle systems. The chapter ends with a discussion of using these two facilities in a multitenant environment. Multitenant container and pluggable databases are discussed in Chapter 32. Using Data Pump and SQL*Loader in a multitenant environment are measured in the Advanced Administration exam.

Describe Ways to Move Data

There are many situations where bulk transfers of data into a database or between databases are necessary. Common cases include populating a data warehouse with data extracted from transaction processing systems and copying data from live systems to test or development environments. Because entering data with standard INSERT statements is not always the best way to do large-scale operations, the Oracle database comes with facilities designed for bulk operations. These are SQL*Loader and Data Pump. Database administrators also have the option of reading data without ever actually inserting it into the database; this is accomplished through the use of external tables.

Create and Use Directory Objects

Oracle directory objects allow sessions against the database to read and write operating system files. Some Oracle utilities (such as Data Pump) require directories.

Oracle directories provide a layer of abstraction between the user and the operating system. You as the database administrator (DBA) create a directory object within the database, which points to a physical path on the file system. Permissions on these Oracle directories can then be granted to individual database users. At the operating system level, the Oracle user (the operating system account under which the Oracle instance is running) will need permissions against the operating system directories to which the Oracle directories refer.

Directories can be created from a SQL*Plus prompt with the CREATE DIRECTORY command. To see information about directories, query the view DBA_DIRECTORIES. Each directory has a name and the physical path to which it refers. Note that Oracle does not verify whether the path exists when you create the directory; if it does not exist or if the operating system user who owns the Oracle software does not have permission to read and write to it, there will be an error only when an attempt is made to use the directory. Having created a directory, you must give the Oracle database users who will be making use of the directory permission to read from and write to it, just as your system administrators must give the operating system users permission to read from and write to the physical path.

Figure 17-1 demonstrates how to create directories using SQL*Plus. In the figure, user SCOTT attempts to create a directory pointing to his operating system home directory on the database server machine. This fails because, by default, users do not have permission to do this. After being granted permission, he tries again. He then grants read permission on the directory (and therefore any files within it) to all users and grants read and write permission to one user.

Figure 17-1    Managing directories with SQL*Plus

The query of ALL_DIRECTORIES in the figure shows that the directory (like all directories) is owned by SYS; directories are not schema objects. This is why SCOTT cannot drop the directory even though he created it. Dropping a directory requires another privilege: DROP ANY DIRECTORY.

Use SQL*Loader to Load Data from a Non-Oracle Database

In many cases you will be faced with a need to do a bulk upload of datasets generated from some third-party system. This is the purpose of SQL*Loader. The input files can be generated by anything, but as long as the layout conforms to something that SQL*Loader can understand, it will upload the data successfully. Your task as the DBA is to configure a SQL*Loader controlfile that can interpret the contents of the input datafiles; SQL*Loader will then insert the data.

Using SQL*Loader

Architecturally, SQL*Loader is a user process like any other. It connects to the database via a server process, issuing a connect string that identifies a database listener, or if SQL*Loader is running on the database server machine, it can connect using the ORACLE_SID environment variable. To insert rows, it can use one of two techniques: conventional or direct path. A conventional insert uses absolutely ordinary INSERT statements. The SQL*Loader user process constructs an INSERT statement with bind variables in the VALUES clause and then reads the source datafile to execute the INSERT once for each row to be inserted. This method uses the database buffer cache and generates undo and redo data; these are INSERT statements like any others, and normal commit processing makes them permanent.

The direct path load bypasses the database buffer cache. SQL*Loader reads the source datafile and sends its contents to the server process. The server process then assembles blocks of table data in its Program Global Area (PGA) and writes them directly to the datafiles. The write is above the high water mark of the table and is known as a data save. The high water mark is a marker in the table segment above which no data has ever been written; the space above the high water mark is space allocated to the table that has not yet been used. Once the load is complete, SQL*Loader shifts the high water mark up to include the newly written blocks, and the rows within them are then immediately visible to other users. This is the equivalent of a COMMIT. No undo is generated, and if you want, you can switch off the generation of redo as well. For these reasons, direct path loading is extremely fast, and furthermore it should not impact your end users because interaction with the System Global Area (SGA) is kept to a minimum.

Direct path loads are fast, but they do have drawbacks:

•  Referential integrity constraints must be dropped or disabled for the duration of the operation.

•  Insert triggers do not fire.

•  The table will be locked against DML from other sessions.

•  It is not possible to use direct path for clustered tables.

These limitations are a result of the lack of interaction with the SGA while the load is in progress.

SQL*Loader uses a number of files. The input datafiles are the source data that it will upload into the database. The controlfile is a text file with directives telling SQL*Loader how to interpret the contents of the input files and what to do with the rows it extracts from them. Log files summarize the success (or otherwise) of the job, with details of any errors. Rows extracted from the input files may be rejected by SQL*Loader (perhaps because they do not conform to the format expected by the controlfile) or by the database (for instance, insertion might violate an integrity constraint); in either case, they are written to a bad file. If rows are successfully extracted from the input but rejected because they did not match some record-selection criterion, they are written to a reject file.

The controlfile is a text file instructing SQL*Loader on how to process the input datafiles. It is possible to include the actual data to be loaded in the controlfile, but you would not normally do this; usually, you will create one controlfile and reuse it, on a regular basis, with different input datafiles. The variety of input formats that SQL*Loader can understand is limited only by your ingenuity in constructing a controlfile.

Consider this table:

Then consider this source datafile, named DEPT.DAT:

A SQL*Loader controlfile (with line numbers added) that could load this data is DEPTS.CTL, shown here:

To perform the load, run this command from an operating system prompt:

This command launches the SQL*Loader user process, connects to the local database as user SCOTT password TIGER, and then performs the actions specified in the controlfile DEPTS.CTL. Adding a DIRECT=TRUE argument would instruct SQL*Loader to use the direct path rather than a conventional insert (which is the default). The following explains the controlfile line by line:

This is a simple example. The syntax of the controlfile can handle a wide range of formats with intelligent parsing to fix any deviations in format, such as length and data types. In general, you can assume that it is possible to construct a controlfile that will understand just about any input datafile. However, do not think that it is always easy.

SQL*Loader Express Mode

There is a simple way to use SQL*Loader that requires no controlfile. This is the express mode, demonstrated (on Windows) in Figure 17-2.

Figure 17-2    SQL*Loader running in express mode

The directory listing in Figure 17-2 shows that there is one file present: dept.dat. This has been created according to the description given previously. Then the sqlldr.exe executable is launched, connecting as user SCOTT with the connect string orclz and nominating the DEPT table and nothing else. The output shows that two rows were loaded, even though there are three in the datafile. The next directory listing shows that a file suffixed BAD has been generated that, when typed, contains one row from the input datafile. This is the third row, which was incomplete because there is no value for the LOC column of the table.

Express mode is easy to use but is demanding in its requirements:

•  There must be a datafile with the same name as the table to be loaded, suffixed with .DAT.

•  The columns must be a scalar data type: character, number, or date.

•  The fields in the file must be comma delimited and not enclosed in quotes.

•  The input rows must have values for every column of the table.

•  The user must have the CREATE ANY DIRECTORY privilege.

The log files generated by express mode can be instructive. Among other things, they include a SQL*Loader controlfile definition, which can be used (or edited) for subsequent jobs.

Use External Tables to Move Data via Platform-Independent Files

An external table is visible to SELECT statements like any other table, but you cannot perform Data Manipulation Language (DML) statements against it. This is because it does not exist as a segment in the database. It exists only as a data dictionary construct, pointing toward one or more operating system files. The operating system files of external tables are located through Oracle directory objects.

A common use of external tables is to avoid needing to use SQL*Loader to read data into the database. This can give huge savings in the extract-transform-load (ETL) cycle typically used to update a decision support system (DSS) with data from a feeder system. Consider the case where a feeder system regularly generates a dataset as a flat American Standard Code for Information Interchange (ASCII) file, which should be merged into existing database tables. One approach would be to use SQL*Loader to load the data into a staging table and then use a separate routine to merge the rows from the staging table into the DSS tables. This second routine cannot start until the load is finished. Using external tables, the merge routine can read the source data from the operating system files without having to wait for it to be loaded.

To create an external table, use the CREATE TABLE command with the keywords ORGANIZATION EXTERNAL. This tells Oracle that the table does not exist as a segment. Then specify the layout and location of the operating system file. Here is an example:

This command creates an external table that will be populated by the DEPTS.DAT file shown in the section “Using SQL*Loader,” earlier in this chapter. The syntax for ACCESS PARAMETERS is virtually identical to the SQL*Loader controlfile syntax and is used because TYPE has been set to ORACLE_LOADER. The specification for DEFAULT DIRECTORY gives the Oracle directory where Oracle will look for the source datafile and where it will write the log and other files.

External tables can be queried in the same way as internal tables. Any SQL involving a SELECT will function against an external table; external tables can be used in joins, views, and subqueries. They cannot have indexes, constraints, or triggers.

Exercise 17-1: Use Directories, SQL*Loader, and External Tables    In this exercise, you will install and use SQL*Loader to insert data into a table and also to generate the CREATE TABLE script for an external table.

1.  Connect to your database as user SYSTEM (in the examples, the SYSTEM password is oracle) with SQL*Plus.

2.  Create a table to use for the exercise.

3.  Using any editor that will create plain-text files, create a file called names.txt with these values (or similar):

4.  Using the editor, create a controlfile called names.ctl with these settings:

Note that this controlfile will truncate the target table before carrying out the insert.

5.  From an operating system prompt, run SQL*Loader as follows:

6.  Study the log file names.log that is generated.

7.  With SQL*Plus, confirm that the rows have been inserted.

8.  To generate a statement that will create an external table, you can use SQL*Loader and an existing controlfile.

9.  This will have generated a CREATE TABLE statement in the log file names.log, which will look something like this:

10.  From your SQL*Plus session, create an Oracle directory pointing to the operating system directory where your names.txt file is. Here is an example:

11.  Make any edits you want to the command shown in step 9. For example, you might want to change the name of the table being created (SYS_SQLLDR_X_EXT_NAMES isn’t very useful) to something more meaningful. You will need to change both the DEFAULT DIRECTORY and BADFILE settings to point to the directory created in step 10.

12.  Run the statement created in step 11 from your SQL*Plus session.

13.  Query the table with a few SELECT and DML statements. You will find that a log file is generated for every SELECT and that DML is not permitted.

14.  Tidy up by deleting the names.txt and names.ctl files, dropping the tables, and, as SYS, dropping the directory.

Explain the General Architecture of Oracle Data Pump

Data Pump is a server-side utility. You initiate Data Pump jobs from a user process, but all the work is done by server processes. This improves performance dramatically over the old Export/Import utilities because the Data Pump processes running on the server have direct access to the datafiles and the SGA; they do not have to go via a session. Also, it is possible to launch a Data Pump job and then detach from it, leaving it running in the background. You can reconnect to the job to monitor its progress at any time.

Involved in a Data Pump job are a number of processes, two queues, a number of files, and one table. The user processes are expdp and impdp (for Unix) or expdp.exe and impdp.exe (for Windows). These are used to launch, control, and monitor Data Pump jobs. The expdp or impdp user process establishes a session against the database through a normal server process, either locally or via a listener. This session then issues commands to control and monitor Data Pump jobs. When a Data Pump job is launched, at least two processes are started: a Data Pump master process (the DMnn) and one or more worker processes (named DWnn). If multiple Data Pump jobs are running concurrently, each will have its own DMnn process and its own set of DWnn processes. As the name implies, the master process controls the workers.

Two queues are created for each Data Pump job: a control queue and a status queue. The DMnn divides up the work to be done and places individual tasks that make up the job on the control queue. The worker processes pick up these tasks and execute them—perhaps making use of parallel execution servers. This queue operates on a deliver-exactly-once model: Messages are enqueued by the DMnn and dequeued by the worker that picks them up. The status queue is for monitoring purposes; the DMnn places messages on it describing the state of the job. This queue operates on a publish-and-subscribe model; any session (with appropriate privileges) can query the queue to monitor the job’s progress.

The files generated by Data Pump come in three forms: SQL files, dump files, and log files. SQL files are Data Definition Language (DDL) statements describing the objects included in the job. You can choose to generate them (without any data) as an easy way of getting this information out of the database, perhaps for documentation purposes or as a set of scripts to re-create the database. Dump files contain the exported data. This is formatted in a fashion resembling Extensible Markup Language (XML) tags. This means that there is considerable overhead in dump files for describing the data. A small table like the REGIONS table in the HR sample schema will generate a 94KB dump file, but although this overhead may seem disproportionately large for a tiny table like that, it becomes trivial for larger tables. The log files describe the history of the job run.

Finally, there is the control table. This is created for you by the DMnn when you launch a job and is used both to record the job’s progress and to describe it. It is included in the dump file as the final item of the job.

Data Pump has two methods for loading and unloading data: the direct path and the external table path. The direct path bypasses the database buffer cache. For a direct path export, Data Pump reads the datafile blocks directly from disk, extracts and formats the content, and writes it out as a dump file. For a direct path import, Data Pump reads the dump file, uses its content to assemble blocks of table data, and writes them directly to the datafiles. The write is above the high water mark of the table, with the same benefits as those described earlier for a SQL*Loader direct load.

The external table path uses the database buffer cache. Even though Data Pump is manipulating files that are external to the database, it uses the database buffer cache as though it were reading and writing an internal table. For an export, Data Pump reads blocks from the datafiles into the cache through a normal SELECT process. From there, it formats the data for output to a dump file. During an import, Data Pump constructs standard INSERT statements from the content of the dump file and executes them by reading blocks from the datafiles into the cache, where the insert is carried out in the normal fashion. As far as the database is concerned, external table Data Pump jobs look like absolutely ordinary (though perhaps rather large) SELECT or INSERT operations. Both undo and redo are generated, like they would be for any normal DML statement. Your end users may well complain while these jobs are in progress. Commit processing is absolutely normal.

So, what determines whether Data Pump uses the direct path or the external table path? You as DBA have no control; Data Pump itself makes the decision based on the complexity of the objects. Only simple structures, such as heap tables without active triggers, can be processed through the direct path; more complex objects such as clustered tables force Data Pump to use the external table path because it requires interaction with the SGA in order to resolve the complexities. In either case, the dump file generated is identical.

Use Data Pump Export and Import to Move Data Between Oracle Databases

Data Pump is commonly used for extracting large amounts of data from one database and inserting it into another, but it can also be used to extract other information such as PL/SQL code or various object definitions. There are several interfaces: command-line utilities, Enterprise Manager Cloud Control, and a PL/SQL application programming interface (API). Whatever purpose and technique are used, the files are always in the Data Pump proprietary format. It is not possible to read a Data Pump file with any tool other than Data Pump.

Capabilities

Whatever interface is used, Data Pump has these capabilities:

•  Fine-grained object and data selection facilities mean that Data Pump can export either the complete database or any part of it. It is possible to export table definitions (with or without their rows), PL/SQL objects, views, sequences, or any other object type.

•  If exporting a table, it is possible to apply a WHERE clause to restrict the rows exported (although this may make the direct path impossible) or to instruct Data Pump to export a random sample of the table expressed as a percentage.

•  Parallel processing can speed up Data Pump operations. Parallelism can come at two levels: the number of Data Pump worker processes and the number of parallel execution servers each worker process uses.

•  An estimate facility can calculate the space needed for a Data Pump export, without actually running the job.

•  The Network Mode allows transfer of a Data Pump dataset from one database to another without ever staging it on disk. This is implemented by a Data Pump export job on the source database writing the data over a database link to the target database, where a Data Pump import job reads the data from the database link and inserts it.

•  Remapping facilities mean that objects can be renamed or transferred from one schema to another and, in the case of data objects, moved from one tablespace to another as they are imported.

•  When data is being exported, the output files can be compressed and encrypted.

Using Data Pump with the Command-Line Utilities

The executables expdb and impdp are installed into the ORACLE_HOME/bin directory. The following are several examples of using them. Note that in all cases the command must be a single one-line command; the line breaks are purely for readability.

Here is how to export the entire database:

This command will connect to the database as user SYSTEM and launch a full Data Pump export, using two worker processes working in parallel. Each worker will generate its own set of dump files, uniquely named according to the %U template, which generates unique strings of eight characters. Each worker will break up its output into files of 2GB (perhaps because of underlying file system restrictions) of compressed data.

A corresponding import job (which assumes that the files generated by the export have all been placed in one directory) would be as follows:

This command makes a selective export of the PL/SQL objects belonging to two schemas.

This command will extract everything from a Data Pump export that was in the HR schema and import it into the DEV schema.

Tablespace Export and Import

A variation on Data Pump export/import is the tablespace transport capability. This is a facility whereby entire tablespaces and their contents can be copied from one database to another. This is the routine:

1.  Make the source tablespaces read-only.

2.  Use Data Pump to export the metadata describing the tablespaces and the contents.

3.  Copy the datafiles and Data Pump export file to the destination system.

4.  Use Data Pump to import the metadata.

5.  Make the tablespaces read-write on both the source and the destination.

An additional step that may be required when transporting tablespaces from one platform to another is to convert the endian format of the data. A big-endian platform (such as Solaris on SPARC chips) stores a multibyte value such as a 16-bit integer with the most significant byte first. A little-endian platform (such as Windows on Intel chips) stores the least significant byte first. Transporting tablespaces across platforms with a different endian format requires converting the datafiles. You do this with the Recovery Manager (RMAN) command CONVERT.

To determine the platform on which a database is running, query the column PLATFORM_NAME in V$DATABASE. Then to see the list of currently supported platforms (which will vary depending on your exact release) and their endianness, query the view V$TRANSPORTABLE_PLATFORM.

When transporting tablespaces, there are certain restrictions:

•  The tablespaces should be self-contained. This means that the objects within the tablespaces must be complete, in other words, not dependent on any other objects. For instance, if tables are in one tablespace and indexes on the tables in another, both tablespaces must be included in the set to be transported.

•  The destination database must use the same (or a compatible) character set as the source database.

•  The schemas that own the objects in the tablespaces must exist in the destination database, or the operation will fail.

•  Any objects in the destination database with the same owner and object name as objects in the transportable tablespace set will not be lost; they will be ignored during the import.

•  A tablespace of the same name must not already exist. Remember that it is possible to rename tablespaces.

Figure 17-3 shows the steps to generate a transport set. In Figure 17-3, the first command is the PL/SQL procedure call to confirm that a set of tablespaces (in the example, just one tablespace: TS1) is self-contained. Then the tablespace is made read-only. The Data Pump job, launched with the expdp command-line utility, connects as user SYSTEM and then specifies the tablespace to be transported. This will generate a dump file with metadata describing the contents of the TS1 tablespace in the Oracle directory DP_OUT. Then, while the tablespace is still read-only, copy its datafiles and the Data Pump dump file to a suitable location on the destination database server.

Figure 17-3    Using command-line utilities to create a transportable tablespace set

If the destination database is on a platform with a different endianness from the source, the files must be converted. To do this on the source, connect to the source database with RMAN and run a command such as this:

This command will write out a copy of the file with the endianness changed. Alternatively, copy the unchanged file to the destination database, connect with RMAN, and run a command such as this:

This command will read the nominated datafile and convert it from the named platform format to a new file in the format that is required for the destination database.

To import the tablespaces on the destination system, use a command such as that shown in Figure 17-4.

Figure 17-4    Using the impdp utility to import a transported tablespace

The impdp command in Figure 17-4 reads a dump file to determine the name and contents of the tablespace consisting of the nominated datafile (previously converted, if necessary).

A generalization of the transportable tablespace feature makes it possible to transport an entire database from one machine to another.

Exercise 17-2: Use Data Pump Export/Import    In this exercise, use the Data Pump command-line utilities to copy a table from one schema to another.

1.  Connect to the database as user SYSTEM.

2.  Create two schemas to use for this exercise.

3.  Create a table and index in one schema.

4.  There is a directory created by default for the use of Data Pump, named DATA_PUMP_DIR, which the Data Pump clients will use if no other directory is specified. Confirm its existence with this query and create it (using any suitable operating system path) if it does not exist.

5.  Export the ARTEM schema with this command:

6.  Import the ARTEM schema into the IVANA schema:

7.  Confirm that the objects have been imported.

Use Data Pump in a Multitenant Environment

In Oracle Database 12c, Data Pump fully supports import to and export from previous versions of Oracle Database (those that aren’t container databases [CDBs]) and Oracle Database 12c non-CDBs and pluggable databases (PDBs). Since Data Pump is a logical export and import tool, the only operation not supported in a multitenant environment is import into or export from a container database (CDB$ROOT).

The key to using Data Pump in a multitenant environment is to use the PDB service name as the source or target of the Data Pump operation. In this way, the type of database (non-CDB or PDB) is not a factor in the operation. Figure 17-5 shows several scenarios using Data Pump in a multitenant environment.

Figure 17-5    Oracle Data Pump scenarios in a multitenant environment

Export from Non-CDB and Import into PDB

Using Data Pump export from a non-CDB to a PDB is an easy way to convert a non-CDB from Oracle Database 11g or earlier to a PDB in Oracle Database 12c without upgrading the non-CDB to Oracle Database 12c and then converting the database to a PDB. If the target PDB does not exist, you must create it first. In this example, you have a non-CDB called HR, and you want to migrate the users and tablespaces to a PDB in the container CDB01.

The basic steps are as follows:

1.  Create the Oracle and OS directory for the Data Pump dump file.

2.  Export the database.

3.  Create the target PDB if it does not already exist.

4.  Import the database from the dump file into the PDB.

During the import, any tablespaces that already exist will not be re-created. In the target PDB, all users from the non-CDB are re-created as local users in the new PDB.

Export and Import Between PDBs

Exporting from an existing PDB to a new PDB, either in the same container or in a new container, follows most of the steps as in the previous section. If the target PDB does not exist, you create it ahead of time in the target CDB. For both the source and target PDBs, you need to create a directory object for the dump file. The directory object must be at the PDB, not CDB, level because the new PDB will not have visibility to that directory object; as you’d expect, the same directory name can exist at the CDB level and in every PDB.

When you export from an existing PDB, you may have common users who own objects in the source PDB. That user and their objects won’t be imported successfully into a new target PDB because the Data Pump import is at the PDB level and you can’t create local users in a PDB with the C## prefix as part of the Data Pump import. For example, if an existing PDB’s common user C##RJB owns objects, the import to a new PDB in a different CDB without the common user C##RJB will fail with this error message:

To fix this issue, you can do one of two things. The first option is to create the same common user in the target CDB before starting the Data Pump import. If you don’t want to have the same common user in the target CDB, use the REMAP_SCHEMA option on the impdp command line to create a local user who will own the objects from the common user in the source PDB.

On a similar note, if you have tablespace name conflicts and you don’t want the source PDB’s objects to end up in the tablespace with the same name, use the REMAP_TABLESPACE option.

Export from PDB and Import into Non-CDB

Importing an export from a PDB into a non-PDB is supported for all types of exports: full, conventional, schema, and transportable. The only exception is that you cannot import a common user’s objects into a non-CDB. You would get the same error message as if you were trying to import another PDB’s common users into a new PDB. The workaround is the same: Use the REMAP_SCHEMA option.

If you are importing a PDB export into an Oracle 11g non-CDB, however, you will get the common users’ schemas and objects imported successfully. The # character is valid in schema names; therefore, you can create the user C##RJB in an Oracle 11g database, and it will be treated no differently than any other user in that database since the multitenant option exists only from Oracle Database 12c on.

Full Transportable Export and Import

As you recall, one of the new features of Oracle Database 12c is full transportable export and import. This operation is more like a transportable tablespace operation than a Data Pump operation. The expdp command creates only the metadata for the database, and the actual datafiles are copied or moved as-is to the target destination.

In a multitenant environment, you can leverage full transportable export/import both to move tablespaces quickly and to avoid upgrading an existing database in place; instead, you use an existing PDB or create a new PDB and transport the datafiles from an 11g database (or newer) to the PDB.

Of course, since full transportable export/import would normally include the SYSTEM and SYSAUX tablespaces, those are not included in a transportable import operation if the target is a PDB.

Transporting a Database Over the Network

When using transportable export/import over the network to transport a database into a PDB, you follow these steps:

1.  Create the new PDB in the target container.

2.  Create a database link in the target PDB to the source database with the appropriate permissions granted to the user defined in the database link.

3.  Change the status of the nonsystem tablespaces (SYSTEM and SYSAUX) in the source database to READ ONLY.

4.  Copy the datafiles from the source location to the target location accessible to the new PDB.

5.  Convert the datafiles if necessary (for endian conversion).

6.  Import into the target database.

Change the source database’s tablespaces back to READ WRITE.

Your impdp operation will look something like this:

In Oracle Database 11g Data Pump, you could perform import/export over the network as well, and the same applies to Oracle Database 12c Data Pump in a multitenant scenario. No dump file is required, saving both time and disk space. Only the metadata file for the source database is created on the file system.

Use SQL*Loader in a Multitenant Environment

Using SQL*Loader in a multitenant environment is just as easy as using SQL*Loader in a non-CDB environment. The key to ensuring success is to specify the service name of the new or existing PDB when loading data into one or more tables.

Two-Minute Drill

Describe Ways to Move Data

•  Data Pump can transfer data between Oracle databases.

•  SQL*Loader can read files generated by third-party products.

Create and Use Directory Objects

•  An Oracle directory maps a database object to an operating system path.

•  The Oracle OS user must have permissions on the OS directory.

•  Database users must be granted permissions on the Oracle directory.

Use SQL*Loader to Load Data from a Non-Oracle Database

•  SQL*Loader reads operating system text files generated by any third-party system.

•  Express mode simplifies usage.

Use External Tables to Move Data via Platform-Independent Files

•  External tables are operating system text files defined with SQL*Loader syntax.

•  No segment exists for an external table.

•  External tables can be queried, but DML or indexes are not possible.

Explain the General Architecture of Oracle Data Pump

•  Data Pump processes run on the instance, not the client.

•  All files are accessed through Oracle directory objects.

•  Direct path bypasses the buffer cache; external table path goes through the cache.

Use Data Pump Export and Import to Move Data Between Oracle Databases

•  Data Pump dump files are compatible across versions and platforms.

•  Network mode avoids the need to stage data on disk.

•  Tablespace transport mode permits copying of datafiles between databases.

Use Data Pump in a Multitenant Environment

•  Using the database instance’s service name is the key to shielding the export/import process from the details of the CDB.

•  Data Pump export and import are supported from non-CDB to PDB, PDB to PDB, and 11g to non-CDB or PDB, respectively.

•  Full transportable export and import from a non-CDB to a PDB copies all tablespaces except for SYSTEM and SYSAUX.

•  For common users with objects in a Data Pump export from a non-CDB or PDB in Oracle Database 12c, the impdp command line must include an REMAP_SCHEMA clause to ensure that schemas starting with C## will be imported into the target database.

Use SQL*Loader in a Multitenant Environment

•  SQL*Loader works as in previous versions when you use the database’s service name.

Self Test

1.  Which of these methods of moving data can transfer data from one platform to another? (Choose all correct answers.)

A.  Using CREATE TABLE AS with a SELECT statement that reads from a database link

B.  A Data Pump network mode export/import

C.  A Data Pump tablespace transport

D.  Using the legacy exp and imp export/import utilities

E.  Using RMAN backup and restore with backup sets, not image copies

2.  You create a directory with the statement

but when you try to use it with Data Pump, there is an error. Which of the following could be true? (Choose three answers.)

A.  The Oracle software owner has no permissions on c: mp.

B.  The Oracle database user has no permissions on dp_dir.

C.  The path c: mp does not exist.

D.  The path c: mp must exist or else the CREATE DIRECTORY statement would have failed.

E.  If you use Data Pump in network mode, there will be no need for a directory.

F.  Issuing the command grant all on 'c: mp' to public; may solve some permission problems.

3.  What is a necessary condition to import a Data Pump file from a client? (Choose the best answer.)

A.  A directory object must be created pointing to the operating system directory where the file exists.

B.  A controlfile must exist that accurately describes the format of the file.

C.  The client and server operating systems must use the same endian format.

D.  It is not possible to import a file from a client.

4.  Which of the following is not a SQL*Loader file? (Choose the best answer.)

A.  Bad file

B.  Controlfile

C.  Discard file

D.  Good file

E.  Log file

5.  You run SQL*Loader on your PC to insert data into a remote database. Which of the following is true? (Choose the correct answer.)

A.  The input datafiles must be on your PC.

B.  The input datafiles must be on the server.

C.  Direct load is possible only if the input datafiles are on the server.

D.  Direct load is possible only if you run SQL*Loader on the server, not on the PC.

6.  Study this SQL*Loader command:

What will be the result? (Choose the best answer.)

A.  The load will fail unless there is a controlfile present named EMP.CTL.

B.  The load will succeed if there is a file present named EMP.DAT.

C.  The EMP table will be created if it does not exist or appended to if it does.

D.  The user will be prompted for missing arguments, such as the datafile name.

7.  Which of these SQL commands can reference an external table? (Choose two answers.)

A.  SELECT

B.  INSERT, UPDATE, DELETE

C.  CREATE VIEW

D.  CREATE INDEX

8.  Which of the following is not a Data Pump file type? (Choose the best answer.)

A.  Dump file

B.  Log file

C.  Controlfile

D.  SQL file

9.  You are using Data Pump to upload rows into a table, and you want to use the direct path. Which of the following statements is correct? (Choose two answers.)

A.  You must include the DIRECT keyword in the Data Pump controlfile.

B.  This is not possible if the table is in a cluster.

C.  You have no control over this; Data Pump will use the direct path automatically if it can.

D.  Direct path is slower than the external table path because it doesn’t cache data in memory.

10.  You intend to transport a tablespace from database A on Windows to database B on AIX. These are the steps:

a.  Convert the files from little endian to big endian.

b.  Copy the files from A to B.

c.  Export the metadata describing the tablespace.

d.  Import the metadata describing the tablespace.

e.  Make the tablespace read-only in A.

f.  Make the tablespace read-write in B.

In what order could the steps be carried out?

A.  c, e, b, f, a, d

B.  e, c, b, a, d, f

C.  e, c, d, a, b, f

D.  c, e, b, a, d, f

11.  Which of the following operations are supported with Oracle Database 12c Data Pump export and import in a multitenant environment? (Choose all that apply.)

A.  You must always upgrade a pre-12c database to version 12c before performing an export and importing to a 12c non-CDB.

B.  An Oracle 11g database can be exported and imported into an Oracle 12c PDB as a full transportable database operation.

C.  Only PDBs can be exported and imported into another PDB.

D.  Only PDBs can be exported and imported into the root container (CDB$ROOT).

E.  Only non-CDBs can be imported into another non-CDB.

F.  You can export a PDB and import it into a different PDB within the same CDB.

Self Test Answers

1.      A, B, C, and D. All of these techniques have a cross-platform capability. C (tablespace transport) may require converting the files if the target platform is a different endian from the source.
    E is incorrect. This is incorrect because backup sets are not portable across platforms, although image copies may be.

2.      A, B, and C. These conditions could all cause problems when using the directory, but not when creating it.
    D, E, and F are incorrect. D is incorrect because the existence of the directory is not checked at creation time. E is incorrect because although network mode does not need a directory for the dump files, it will need a directory for the log files. F is incorrect because it confuses the issue of Oracle permissions on directories with operating system permissions on physical paths.

3.      D. It is not possible to import a file from the client; the dump must exist on the server.
    A, B, and C are incorrect. A is incorrect because a directory object can point only to a directory on the server. B is incorrect because a controlfile is used by SQL*Loader, not by Data Pump. C is incorrect because endianness is relevant to transportable tablespaces, not data import.

4.      D. There is no “good” file—the acceptable rows are inserted into the table and are not logged by SQL*Loader.
    A, B, C, and E are incorrect. These are the file types that SQL*Loader can generate.

5.      A. SQL*Loader is a client-server process; the input files must be local to the user process.
    B, C, and D are incorrect. B is incorrect because the input files must be on the PC, accessible to the client-side process. C and D are incorrect because direct load is not relevant to the location of the files.

6.      B. Express mode relies on many defaults, one of which is that the datafile name must be the table name suffixed with .DAT.
    A, C, and D are incorrect. A is incorrect because express mode does not use a controlfile. C is incorrect because express mode cannot create a table; it can only append to an existing table. D is incorrect because express mode does not prompt.

7.      A and C. Anything related to SELECT, including creating a view, can be executed against an external table.
    B and D are incorrect. DML is impossible against an external table, as is indexing.

8.      C. SQL*Loader can use a controlfile; Data Pump does not.
    A, B, and D are incorrect. Data Pump export generates a dump file, an import can generate a SQL file, and both export and import generate log files.

9.      B and C. Clusters are complex structures that cannot be directly loaded. Data Pump determines whether a direct load is possible automatically.
    A and D are incorrect. There is no DIRECT keyword because the choice is automatic. Direct is faster because it bypasses the SGA.

10.      B. This is the correct sequence.
    A, C, and D are incorrect. All these sequences are wrong. The only acceptable alternative would have been to convert the endianness on step a before copying to step b.

11.      B and F. You can perform an export of any type (schema, tablespace, full transportable) from database version 11g and import it into a PDB. Also, you can use Data Pump export/import with the full transportable option to copy a PDB within the same CDB.
    A, C, D, and E are incorrect. A is incorrect because you do not need to upgrade a database before using Data Pump export/import as long as the version of Oracle Database is 11g or newer. C is incorrect because you can always import a non-CDB into a new PDB as long as you use the service name when connecting to the PDB. D is incorrect because you cannot use Data Pump import into the root container of a CDB. E is incorrect because non-CDBs can be imported into another non-CDB or a PDB as long as the source database version is 11g or newer.