Oracle 10g/11g Data and Database Management Utilities
Does your database seem complicated? Are you finding it difficult to work with it
efficiently? Database administration is part of a daily routine for all database
professionals. Using Oracle Utilities, administrators can benefit from improved
maintenance windows, optimized backups, faster data transfers, and more reliable
security, and can in general do more with the same time and resources.
You don’t have to reinvent the wheel, just learn how to use Oracle Utilities properly to
achieve your goals. That is what this book is about; it covers topics which are oriented
towards data management, session management, batch processing, massive deployment,
troubleshooting, and how to make the most out of frequently used DBA tools to improve
your daily work.
Data management is one of the most frequently required tasks; doing a backup is a mustdo
task for any company. Data management includes several tasks such as data transfers,
data uploading and downloading, reorganizing data, and data cloning, among many
others. If people learn to use a tool and things appear to go well, few will question if their
approach is optimal. Often it is only when maintenance windows start shrinking; due to
the ever increasing amount of data and need for business availability, that problems with
any particular approach get identified. People tend to get used to using the old
export/import utilities to perform data management and if it works, they probably will
consider the problem solved and continue to use an obsolete tool. This book explores
further possibilities and new tools. It makes the user question if his/her current
environment is optimized and teaches the reader how to adopt more optimized data
management techniques focusing on the tools and requirements most frequently seen in
modern production environments.
What This Book Covers
Chapter 1 deals with Data Pump. Data Pump is a versatile data management tool. It is
much more than just an exp/imp upgrade; it allows remapping, dump file size estimation,
restartable tasks, network transfers, advanced filtering operations, recovering data after a
commit has been issued, and transferring data files among different oracle versions. It
includes a PL/SQL API so it can be used as a base to develop data pump-based systems.
Chapter 2 involves a description of the SQL*Loader. It describes how SQL* Loader
is the tool to upload plain text format files to the database. If SQL* Loader properly
configured, you can greatly increase the speed with which uploads are completed.
Loading data to take care of the character set will avoid unnecessary headaches, and
you can optimize your loading window. There are several tips and tricks to load
different character sets to the database and load binary data to BLOB fields. This tool
can be used to load data on the fl y and you will learn how to proactively configure it to
get a smooth load.
Chapter 3 is all about External Tables. The external table is a concept Oracle introduced
in 9i to ease the ETL (Extraction Transformation and Loading) DWH process. An
external table can be created to map an external file to the database so you can seamlessly
read it as if it was a regular table. You can extend the use of the external tables concept
to analyze log files such as the alert.log or the network log files inside the database.
The external table concept can be implemented with the Data Pump drivers; this way you
can easily and selectively perform data transfers among databases spanning different
Oracle versions.
Chapter 4 specializes in advanced techniques involved in optimizing the Recovery
Manager. Recovery Manager can be optimized to minimize the impact in production
environments; or it can run faster using parallel techniques. It can be used to clone a
database on the same OS or transport it over different platforms, or even change the
storage method between ASM and conventional file system storage and vice versa.
Chapter 5 talks about the Recovery Manager. Recovery manager first appeared back
in 8.0, but it was not until 9i that it began to gain popularity among DBAs as the default
backup/recover tool. It is simple and elegant and the most frequently used commands
are pretty simple and intuitive. This chapter presents several practical database
recovery scenarios.
Chapter 6 is about Session Management. The users are the main reason why the DBA
exists. If it were not for the users, there would be no database activity and there would be
no problems to be solved. How can you easily spot a row lock contention problem? What
should be done to diagnose and solve this problem? What does it mean to kill a user
session? Managing sessions means you can regulate them by means of Oracle profiles;
this may sooner or later lead to snipped sessions; what are those snipped sessions? How
do you get rid of them? This chapter discusses several user session management issues.
Chapter 7 talks about the Oracle Scheduler. The Oracle Scheduler is a powerful tool used
to schedule tasks in Oracle. This tool can perform simple schedules as well as complex
schedules; you need to understand time expressions and the Oracle scheduler architecture
to take advantage of this utility.
Chapter 8 will teach you about Oracle Wallet Manager. Oracle Wallet Manager is the
cornerstone and entry point for advanced security management. You can use it to manage
certificates and certificate requests. You can store identity certificates and retrieve them
from a central location, or you can use the registry in a Windows environment. You can
hide passwords without using OS Authentication mechanisms by storing the user
password inside the wallet.
Chapter 9 deals with security of the system. Most people worry about having a valid
backup that can be used to effectively recover data, but not all of them are concerned
about the backup security; if a backup can be used to recover data, this doesn’t actually
mean the data will be recovered at the same site where it was taken from. OWM is a tool
which can be used to have the backup encrypted, so sensitive data can be secured not
only from the availability point of view, but also from the confidentiality point of view.
Security has to do also with identifying who the real user is; this can be achieved with the
enterprise user. This chapter explains step by step how to set up an environment with
enterprise identity management using the Enterprise Security Manager.
Chapter 10 talks about Database Configuration Assistant. Creating a database is one
of the first tasks the user performs when installing Oracle, but this tool goes far beyond
the simple task of creating the database; it can be used to manage templates, create a
database in silent mode, and configure services in an RAC environment. Configuring
database options and enabling the Enterprise Manager DB Control can be done here.
DBCA is also the easy way to start up and configure an Automatic Storage Management
(ASM) environment.
Chapter 11 provides details about the Oracle Universal Installer. Installing Oracle is
more than just a next → next button pressing activity; OUI is a tool to manage software.
Most people care about database backups, as well as configuration file backups, but what
about the Oracle installer repository? This set of files is most often underestimated unless
a hardware failure makes the DBA understand what Oracle software maintenance is. OUI
can perform silent and batch installations; it can also perform installations from a central
software depot accessible through the Web.
Chapter 12 is about the Enterprise Manager Configuration Assistant. Most DBAs use EM
as the basic DBA administration tool; it is a very intuitive database management console.
Most people depend on it to easily perform most of the administration and operation tasks
that otherwise would be time consuming to complete through character console mode.
But what happens when it is not available, either because of a change in the network
topology or a firewall that restricts access to the managing port? Then the user needs to
have the console reconfigured to bring it back into operation. EMCA is the character
mode tool used to perform this task.
Chapter 13 talks about OPatch. Patching the RDBMS is required to keep the software up
to date. When a patchset is to be applied OUI is used, but when a single patch or a CPU is
to be applied OPatch must be used. You will learn how to perform a basic patch
application task, list the patch inventory, find out if a patch has already been applied,
maintain the software and the software inventory, and learn how and when to perform a
patch application while the database is up and running.
External Tables
When working in data warehouse environments, the Extraction—Transformation—
Loading (ETL) cycle frequently requires the user to load information from external
sources in plain file format, or perform data transfers among Oracle database in
a proprietary format. This requires the user to create control files to perform the
load. As the format of the source data regularly doesn’t fit the one required by the
Data Warehouse, a common practice is to create stage tables that load data into the
database and create several queries that perform the transformation from this point
on, to take the data to its final destination.
A better approach, would be to perform this transformation ‘on the fl y’ at load time.
That is what External Tables are for. They are basically external files, managed either
by means of the SQL*Loader or the data pump driver, which from the database
perspective can be seen as if they were regular read only tables.
This format allows the user to think about the data source as if the data was already
loaded into its stage table. This lets the user concentrate on the queries to perform
the transformation, thus saving precious time during the load phase.
External Tables can serve not only as improvements to the ETL process, but also as
a means to manage database environments, and a means of reducing the complexity
level of data management from the user’s point of view.
The External Table basics
An External Table is basically a file that resides on the server side, as a regular fl at
file or as a data pump formatted file. The External Table is not a table itself; it is
an external file with an Oracle format and its physical location. This feature first
appeared back in Oracle 9i Release 1 and it was intended as a way of enhancing the
ETL process by reading an external fl at file as if it was a regular Oracle table. On its
initial release it was only possible to create read-only External Tables, but, starting
with 10g—it is possible to unload data to External Tables too.
In previous 10g Releases there was only the SQL*Loader driver could be used to
read the External Table, but from 10g onwards it is now possible to load the table
by means of the data pump driver. The kind of driver that will be used to read the
External Table is defined at creation time. In the case of ORACLE_LOADER it is the same
driver used by SQL*Loader. The fl at files are loaded in the same way that a fl at file
is loaded to the database by means of the SQL*Loader utility, and the creation script
can be created based on a SQL*Loader control file. In fact, most of the keywords that
are valid for data loading are also valid to read an external fl at file table.
The main differences between SQL*Loader and External Tables are:
- When there are several input datafiles SQL*Loader will generate a bad file
and a discard file for each datafile. - The CONTINUEIF and CONCATENATE keywords are not supported by
External Tables. - The GRAPHIC, GRAPHIC EXTERNAL, and VARGRAPHIC are not supported for
External Tables. - LONG, nested tables, VARRAY, REF, primary key REF, and SID are
not supported. - For fields in External Tables the character set, decimal separator, date mask
and other locale settings are determined by the database NLS settings. - The use of the backslash character is allowed for SQL*Loader, but for
External Tables this would raise an error. External Tables must use quotation
marks instead.For example: SQL*Loader FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY "\" External Tables TERMINATED BY ',' ENCLOSED BY "'"
A second driver is available, the ORACLE_DATAPUMP access driver , which uses
the Data Pump technology to read the table and unload data to an External Table.
This driver allows the user to perform a logical backup that can later be read back
to the database without actually loading the data. The ORACLE_DATAPUMP access
driver utilizes a proprietary binary format for the external file, so it is not possible
to view it as a fl at file.
Let’s setup the environment
Let’s create the demonstration user, and prepare its environment to create an
External Table. The example that will be developed first refers to the External Table
using the ORACLE_LOADER driver.
create user EXTTABDEMO
identified by ORACLE
default tablespace USERS;
alter user exttabdemo
quota unlimited on users;
grant CREATE SESSION,
CREATE TABLE,
CREATE PROCEDURE,
CREATE MATERIALIZED VIEW,
ALTER SESSION,
CREATE VIEW,
CREATE ANY DIRECTORY
to EXTTABDEMO;
A simple formatted spool from this query will generate the required external
table demonstration data. The original source table is the demonstration
HR.EMPLOYEES table.
select
EMPLOYEE_ID ||','||
DEPARTMENT_ID ||','||
FIRST_NAME ||','||
LAST_NAME ||','||
PHONE_NUMBER ||','||
HIRE_DATE ||','||
JOB_ID ||','||
SALARY ||','||
COMMISSION_PCT ||','||
MANAGER_ID ||','||
EMAIL
from HR.EMPLOYEES
order by EMPLOYEE_ID
The above query will produce the following sample data:
The External Table directory is defined inside the database by
means of a DIRECTORY object. This object is not validated at
creation time, so the user must make sure the physical directory
exists and the oracle OS user has read/write privileges on it.
$ mkdir $HOME/external_table_dest
SQL> CREATE DIRECTORY EXTTABDIR AS '/home/oracle/external_table_dest';
The above example was developed in a Linux environment, on Windows platforms
the paths will need to be changed to correctly refl ect how Oracle has been set up.
Now, the first External Table can be created.
A basic External Table
Here is the source code of the External Table creation.
The create table command syntax is just like any other regular table creation (A), (B),
up to the point where the ORGANIZATION EXTERNAL (C) keyword appears,
this is the point where the actual External Table definition starts. In this case the
External Table is accessed by the ORACLE_LOADER driver (D). Next, the external
fl at file is defined, and here it is declared the Oracle DIRECTORY (E) where the fl at
file resides. The ACCESS PARAMETERS (F) section specifies how to access the fl at
file and it declares whether the file is a fixed or variable size record, and which other
SQL*Loader loading options are declared. The LOCATION (H) keyword defines the
name of the external data file. It must be pointed out that as this is an External Table
managed by the SQL_LOADER driver the ACCESS_PARAMETERS section must
be defined, in the case of External Tables based on the DATAPUMP_DRIVER this
section is not required.
The columns are defined only by name (G), not by type. This is permitted from the
SQL*Loader perspective, and allows for dynamic column definition. This column
schema definition is more fl exible, but it has a drawback—data formats such as those
in DATE columns must match the database date format, otherwise the row will be
rejected. There are ways to define the date format working around this requirement.
Assuming the date column changes from its original default format mask
“DD-MON-RR” to “DD-MM-RR”, then the column definition must change from a
simple CHAR column to a DATE with format mask column definition.
Original format:
"HIRE_DATE" CHAR(255)
Changed format:
"HIRE_DATE" DATE "DD-MM-RR"
When working with an External Table, the access parameter is
not validated at creation time, so if there are malformed rows,
or if there are improperly defined access parameters, an error is
shown, similar to the one below.
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52
Once the data is created and all required OS privileges have been properly validated,
the data can be seen from inside the database, just as if it were a regular Oracle table.
This table is read only, so if the user attempts to perform any DML operation against
it, it will result in this error:
SQL> delete ext_employees;
delete ext_employees
*
ERROR at line 1:
ORA-30657: operation not supported on external organized table
As the error message clearly states, this kind of table is only useful for read
only operations.
This kind of table doesn’t support most of the operations available for regular tables,
such as index creation, and statistics gathering, and these types of operations will
cause an ORA-30657 error too. The only access method available for External Tables
is Full Table Scan, so there is no way to perform a selective data retrieval operation.
The External Tables cannot be recovered, they are just metadata definitions stored
in the dictionary tables. The actual data resides in external files, and there is no
way to protect them with the regular backup database routines, so it is the user’s
sole responsibility to provide proper backup and data management procedures. At
the database level the only kind of protection the External Table receives is at the
metadata level, as it is an object stored as a definition at the database dictionary level.
As the data resides in the external data file, if by any means it were to be corrupted,
altered, or somehow modified, there would be no way to get back the original data.
If the external data file is lost, then this may go unnoticed, until the next SELECT
operation takes place.
This metadata for an External Table is recorded at the {USER | ALL | DBA}_TABLES
view, and as this table doesn’t actually require physical database storage, all storage
related columns appear as null, as well as the columns that relate to the statistical
information. This table is described with the {USER | ALL | DBA}_EXTERNAL_TABLES
view, where information such as the kind of driver access,
the reject_limit, and the access_parameters, amongst others, are described.
SQL> DESC USER_EXTERNAL_TABLES
Name
Null?
Type
--------------------------
------------
--------------
TABLE_NAME
NOT NULL
VARCHAR2(30)
TYPE_OWNE
CHAR(3)
TYPE_NAME
NOT NULL
VARCHAR2(30)
DEFAULT_DIRECTORY_OWNER
CHAR(3)
DEFAULT_DIRECTORY_NAME
NOT NULL
VARCHAR2(30)
REJECT_LIMIT
VARCHAR2(40)
ACCESS_TYPE
VARCHAR2(7)
ACCESS_PARAMETERS
VARCHAR2(4000)
PROPERTY
VARCHAR2(10)
This is the first basic External Table, and as previously shown, its creation is pretty
simple. It allows external data to be easily accessed from inside the database,
allowing the user to see the external data just as if it was already loaded inside
a regular stage table.
