11 Oracle Utilities

Introduction to Oracl e Utilities

Oracle's database utilities let you perform the following tasks:

• High-speed m ovement of data and metadata from one database to another using Data Pump Export and Import

• Extract and manipulate complete representations of the metadata for database objects, using the Metadata API

• Move al l or part of the data and metadata for a site from one database to another, using the Data Pump API

• Load data into Oracle tables from operating system files using SQL*Loader or from external sources using external tables

• Query redo log files through a SQL interface with LogMiner

• Perform physical data structure integrity checks on an offline (for example, backup) database or datafile with DBVERIFY.

• Maintain the in ternal database identifier (DBID) and the database name (DBNAME) for an operational database, using the DBNEWID utility

  
  

  See Also: Oracle Database Utilities

  
  

Overview of Data Pump Export and Import

Oracle Data Pu mp technology enables very high-speed movement of data and metadata from one database to another. This technology is the basis for Or acle's new data movement utilities, Data Pump Export and Data Pump Import.

Data Pump enables you to specify whether a job shou ld move a subset of the data and metadata. This is done using data filters and metadata filt ers, which are implemented through Export and Import parameters.

Overview of the Data Pump API

The Data Pump API provides a high-speed mechanism to move all or part of the data and metadata for a site from one database to another. T o use the Data Pump API, you use the procedures provided in the DBMS_DATAPUMP PL/SQL package. The Data Pump Export and D ata Pump Import utilities are based on the Data Pump API.

Overview of the Metadata API

The Metadata application pr ogramming interface (API), provides a means for you to do the following:

• Retrieve an object's metadata a s XML

• Transform the XML in a variety of ways, including transforming it into SQL DDL

• Submit the XML to re-create the object extracted by the retrieval

To use the Metadata API, you use the pro cedures provided in the DBMS_METADATA PL/SQL package. For the purposes of the Metadata API, every entity in the database is modeled as an object that belongs to an object type. For example, the table scott.emp is an object and its object ty pe is TABLE. When you fetch an object's metadata you must specify the object type.

Overview of SQL*Loader

SQL*Loader l oads data from external files into tables of an Oracle database. It has a powerful data parsing engine that puts little limitation on the format of the data in the datafile. You can use SQL*Loader to do the following:

• Load data from mult iple datafiles during the same load session.

• Load data into multiple tables during the same load session .

• Specify the character set of the data.

• Selectively load data (you can loa d records based on the records' values).

• Manipulate the data before loading it, using SQL functions.

• Generate unique sequential key values in specified columns.

• Use the operating system's file system to access the datafiles.

• Load data from disk, tape, or named pipe.

• Generate sophisticated error reports, which greatly aids troubleshooting.

• Load arbitrarily complex object-relational data.

• Use secondary datafiles for loading LOBs and collections.

• Use either conventional or direct path loading. While conventional path loading is very flexible, direct path loading provides superior loading performance.

A typical SQL*Loader session takes as input a control file, which controls th e behavior of SQL*Loader, and one or more datafiles. The output of SQL*Loader is an Oracle database (where the data is loaded), a log file, a bad file, and potentially, a discard file.

Overview of LogMiner

Oracle LogMiner enables you to query redo log files through a SQL interface. All changes made to user data or to the database dictionary are recorded in the Oracle redo log files. There fore, redo log files contain all the necessary information to perform recovery operations.

LogMiner functionality is available through a command-line interface or through the Oracle LogMiner Viewer graphical user interface (GUI). The LogMiner Viewer is a part of Oracle Enterprise Manager.

The following are some of the potential uses for data contained in redo log files:

• Pinpointing when a logical corruption to a database, such as errors made at the application level, may have begun. T his enables you to restore the database to the state it was in just before corruption.

• Detecting and whe never possible, correcting user error, which is a more likely scenario than logical corruption. User errors include deleting the wron g rows because of incorrect values in a WHERE clause, updating rows with incorrect values, dropping the wrong index, and so forth.

• Determining what actions you would have to take to perform fine-grained recovery at the trans action level. If you fully understand and take into account existing dependencies, it may be possible to perform a table-based undo o peration to roll back a set of changes.

• Performance tuning and capacity planning through trend analysis. You can determine which tables get the most updates and inserts. That information provides a historical perspective on disk access s tatistics, which can be used for tuning purposes.

• Performing post-auditing. The redo log files contain a ll the information necessary to track any DML and DDL statements run on the database, the order in which they were run, and who execu ted them.

Overview of DBVERIFY Utility

DBVERIFY i s an external command-line utility that performs a physical data structure integrity check. It can be used on offline or online datab ases, as well on backup files. You use DBVERIFY primarily when you need to ensure that a backup database (or datafile) is valid befor e it is restored or as a diagnostic aid when you have encountered data corruption problems.

Because DBVERIFY can be run agains t an offline database, integrity checks are significantly faster.

DBVERIFY checks are limited to cache-managed blocks (that is , data blocks). Because DBVERIFY is only for use with datafiles, it will not work against control files or redo logs.

There ar e two command-line interfaces to DBVERIFY. With the first interface, you specify disk blocks of a single datafile for checking. With the second interface, you specify a segment for checking.