Oracle Reverting Upgraded Database

Oracle Tips by Burleson Consulting

This is an excerpt from "Oracle 10g New Features for Administrators" by Ahmed Baraka.

Instructing DBUA to perform a backup of your database (with the RMAN) will provide you the option to revert the database to the older version by the end of the upgrade process.

You can also revert back manually to the older database by using the DB_Name_restore.bat file (under Windows), providing that you have a cold backup of the
database.

Loading and Unloading Data - Introduction to the Data Pump Architecture

Using Export and Import Data Pump utilities you can:

� export and import data faster than Old export/import utilities

� estimate job times

� perform fine-grained object selection

� monitor jobs effectively

� directly load one database from a remote instance

� call the utilities from PL/SQL using Data Dump API

� stop, resume and restart the utilities

� attach a running job to monitor jobs, as well as to modify certain parameters interactively.

� have fine-grained data import capability

� remap objects of a specific schema to another schema

Note : the export Data Pump user process launches a server-side process that writes data to disks on the server node, not the client that launches the utility.

Note: The new Data Pump technology lets you export data only to disk. You cannot use a tape drive when performing a Data Pump export.

Data Pump Components

� The DBMS_DATAPUMP package: this is the main engine of the Data Pump utilities. It contains procedures that do the export and import actions.

� The DBMS_METADATA package: this package is used to extract and modify data dictionary metadata.

� The command-line clients, expdp and impdp.

Data-Access Methods

� Direct path: the direct path internal stream format is the same format as the data stored in Oracle dump files.

� External tables: Oracle reads data from and write data to operating system files that lie outside the database.

Data Pump automatically selects the most appropriate access method for each table. It always tries to first use the direct-path method. Under some conditions, such as the following, it may not able to use the direct method:

o Clustered tables

o Presence of active triggers in the tables

o Export of a single partition in a table with a global index

o Presence of referential integrity constraints

o Presence of domain indexes on LOB columns

o Tables with fine-grained access control enabled in the insert mode

o Tables with BFILE or opaque type columns

Note: The datafile format is identical in external tables and the direct-access methods.

Data Pump Files

� Dump files: These hold the data for the Data Pump job.

� Log files: These are the standard files for logging the results of Data Pump operations.

� SQL files: Data Pump import uses a special parameter called SQLFILE, which will write all the Data Definition Language (DDL) statements it will execute during the import job to a file.

Using Directory Objects

You can�t use absolute directory path location for Data Pump jobs; you must always use a directory object.

To create a directory, a user must have the CREATE ANY DIRECTORY privilege:

CREATE DIRECTORY dpump_dir1 as
'c:\oracle\product\10.1.0\oradata\export'

In order for a user to use a specific directory, the user must have access privileges to the directory object:

GRANT READ, WRITE ON DIRECTORY dpump_dir1 TO
salapati

Note: In Oracle 10g Release 2, a directory object named DATA_PUMP_DIR as created by default in the database. In Windows, it is mapped to <ORACLE_BASE>\admin\<sid>\dpdump\ directory. By default, it is available only to privileged users.

1. Using the DIRECTORY:FILE Notation:

expdp LOGFILE=dpump_dir2:salapati.log �

2. Using the DIRECTORY parameter You can use the DIRECTORY parameter to specify the name of the directory object:

expdp hr/hr DIRECTORY=dpump_dir1 �

3. Using the default directory DATA_PUMP_DIR You can create a default directory with the name DATA_PUMP_DIR, and then not need to specify the DIRECTORY parameter in your export and import commands. Data Pump will write all dump files, SQL files, and log files automatically to the directory specified for DATA_DUMP_DIR.

4. Using the DATA_DUMP_DIR Environment Variable. You can use the DATA_DUMP_DIR environment variable on the client to point to the directory object on the server. Data Pump will automatically read and/or write its files from that directory object. In Windows, this variable is set in the Registry.

Order of Precedence for File Locations

As in the order indicated above.

The Mechanics of a Data Pump Job - The Master Process

The master process, or more accurately, the Master Control Process (MCP), has a process name of DMnn.

The full master process name is of the format

The master process performs the following tasks:

o Creates jobs and controls them

o Creates and manages the worker processes

o Monitors the jobs and logs the progress

o Maintains the job state and restart information in the master table

o Manages the necessary files, including the dump file set

Oracle creates the master table in the schema of the user who is running the Data Pump job at the beginning of every export job. The master table has the same name as the export job, such as SYS_EXPORT_SCHEMA_01. Master table will be automatically deleted by end of a successful export or import job.

Note: The master table contains all the necessary information to restart a stopped job. It is thus the key to Data Pump�s job restart capability, whether the job stoppage is planned or unplanned.

The Worker Process

The worker process is the process that actually performs the heavy-duty work of loading and unloading data, and has the name DWnn (<instance>_DWnn_<pid>). MCP(DMnn) may create number of DWnn, if you choose the PARALLEL option for load. DWnn process maintains the object rows of the master table.

Shadow Process

The shadow process creates the job consisting of the master table as well as the master process.

Client Processes

The client processes call the Data Pump�s API. You perform export and import with the two clients, expdp and impdp.