Oracle 10g New Features for
Active Session History (ASH) _________________ 18
Administrators (Summary Sheets) v. 2.0
Server-Generated Alerts_____________________ 19
Including Release 2 Features
Adaptive Thresholds ________________________ 20
The Management Advisory Framework _________ 21
Installation, Server Configuration, and Database
Upgrades _______________________________ 3
Application Tuning ______________________ 22
Comparison Between 10.1 and 10.2 ____________ 3
Using the New Optimizer Statistics ____________ 22
About Grid Computing _______________________ 3
Using the SQL Tuning Advisor ________________ 22
Installation New Features Support ______________ 3
Using the SQL Access Advisor ________________ 23
Performance Enhancements to the Installation
Performance Pages in the Database Control _____ 23
Process ___________________________________ 4
Indexing Enhancements _____________________ 23
Simplified Instance Configuration_______________ 4
Managing Database Control ___________________ 5
Viewing Database Feature Usage Statistics _______ 5
Space and Storage Management Enhancements 24
Supported Upgrade Paths to Oracle 10g _________ 5
Proactive Tablespace Management_____________ 24
Using New Utility to Perform Pre-Upgrade Validation
Reclaiming Unused Space____________________ 25
Checks ___________________________________ 5
Object Size Growth Analysis__________________ 25
Using the Simplified Upgrade Process ___________ 5
Using the Undo and Redo Logfile Size Advisors ___ 26
Manual Upgrade Process______________________ 6
Rollback Monitoring ________________________ 26
Reverting Upgraded Database _________________ 7
Tablespace Enhancements ___________________ 26
Using Sorted Hash Clusters __________________ 28
Loading and Unloading Data ________________ 7
Partitioned IOT Enhancements ________________ 28
Introduction to the Data Pump Architecture_______ 7
Redefine a Partition Online ___________________ 29
Using Data Pump Export and Import ____________ 8
Copying Files Using the Database Server________ 29
Monitoring a Data Pump Job__________________ 10
Dropping Partitioned Table ___________________ 30
Creating External Tables for Data Population _____ 11
Dropping Empty Datafiles____________________ 30
Transporting Tablespaces Across Platforms ______ 11
Renaming Temporary Files ___________________ 30
Transport Tablespace from Backup ____________ 13
Loading Data from Flat Files by Using EM _______ 14
Oracle Scheduler and the Database Resource
DML Error Logging Table ____________________ 14
Manager ______________________________ 30
Asynchronous Commit ______________________ 14
Simplifying Management Tasks Using the Scheduler
________________________________________ 30
Managing the Basic Scheduler Components ______ 30
Automatic Database Management ___________ 14
Managing Advanced Scheduler Components _____ 31
Using the Automatic Database Diagnostic Monitor
Database Resource Manager Enhancements _____ 35
(ADDM)__________________________________ 14
Using Automatic Shared Memory Management
(ASMM)__________________________________ 16
Backup and Recovery Enhancements ________ 36
Using Automatic Optimizer Statistics Collection ___ 16
Using the Flash Recovery Area ________________ 36
Database and Instance Level Trace ____________ 17
Using Incremental Backups __________________ 38
Using Automatic Undo Retention Tuning ________ 17
Enhancements in RMAN _____________________ 38
Automatically Tuned Multiblock Reads __________ 17
Oracle Secure Backup_______________________ 40
Cross-Platform Transportable Database _________ 40
Manageability Infrastructure _______________ 18
Restore Points ____________________________ 41
Types of Oracle Statistics ____________________ 18
Placing All Files in Online Backup Mode _________ 42
The Automatic Workload Repository (AWR) ______ 18
Page 1
Oracle 10g New Features for Administrators (Summary Sheets)

---

Flashback Technology Enhancements ________ 42

Using the Flashback Technology_______________ 42
Copyright
Anyone is authorized to copy this document to any
General Flashback Technology ________________ 42
means of storage and present it in any format to any
Flashback Database ________________________ 42
individual or organization for free. There is no warranty
of any type for the code or information presented in this
Flashback Drop ____________________________ 43
document. The editor is not responsible for any loses or
damage resulted from using the information or
Flashback Table ___________________________ 44
executing the code in this document.
Row Level Flashback Features ________________ 44
If any one wishes to correct a statement or a typing
error or add a new piece of information, please send the
request to ahmed_b72@yahoo.com . If the
Automatic Storage Management ____________ 45
modification is acceptable, it will be added to the
document, the version of the document will be
Introduction to Automatic Storage Management __ 45
incremented and the modifier name will be listed in the
version history list.
ASM Instance Architecture ___________________ 45
Managing the ASM Instance __________________ 45

Managing ASM Disk Groups __________________ 47
Version History
Managing ASM Files ________________________ 48
Version
Individual
Date
Updates
Name
Database Instance Parameter Changes _________ 48
1.o
Ahmed Baraka
Sept,
Initial document.
Migrating a Database to ASM _________________ 48
2005
ASM and Transportable Tablespaces ___________ 49
2.0
Ahmed Baraka
May,
Release 2
2007
features included
ASM Command-Line Interface ________________ 49
FTP and HTTP Access _______________________ 50
Enhancements in Analytical SQL and Materialized
Views _________________________________ 50
Enhancements in the MERGE Statement ________ 50
Using Partitioned Outer Joins _________________ 51
Using the SQL MODEL Clause_________________ 51
Materialized View Enhancements ______________ 51
Database Security _______________________ 52
XML Audit Trail ____________________________ 52
VPD and Auditing Enhancements ______________ 53
Oracle Transparent Data Encryption (TDE) ______ 54
Secure External Password Store_______________ 55
Connect Role Privilege Reduction ______________ 55
Miscellaneous New Features _______________ 55
Enhancements in Managing Multitier Environments 55
SQL and PL/SQL Enhancements _______________ 56
Enhancements in SQL*Plus __________________ 56
Miscellaneous Enhancements _________________ 57






Page 2
Oracle 10g New Features for Administrators (Summary Sheets)

---

• Kernel parameters
• Sufficient memory and file space
Installation, Server Configuration, and
• Oracle Home
Database Upgrades
New File Storage Options
The OUI now offers three choices for configuring the file
Comparison Between 10.1 and 10.2
systems for any new starter database that you may
create:

Version
10.1
10.2
• Automatic Storage Management (ASM): ASM is
Supported Parameters
255
258
integration of a traditional file system with a built-in
Logical Volume Manager (LVM). The database
Unsupported Parameters
918
1127
automatically stripes and mirrors your data across
Dynamic Performance Views (V$)
340
396
the available disks in the disk groups.
Fixed Views (X$)
529
597
• Raw Devices: If you use RAC, and a Clustered File
System (CFS) is available on your operating system,
Events (Waits)
811
874
Oracle recommends using either CFS or ASM for your
Statistics 332
363
file storage. If a CFS is unavailable, Oracle
recommends that you use raw, or “uncooked,” file
Latches 348
382
systems or ASM.
Background Processes (Fixed
109 157
• File Systems: Choosing this option will mean that
SGA)
you are using the traditional operating system files

and directories for your database storage.
About Grid Computing
Backup and Recovery Options
The following three attributes lie at the heart of grid
• Do not enable automatic backups
computing:
• Enable automatic backups
• Virtualization between the layers of the computing
stack and the users
Database User Password Specification
• Dynamic provisioning of work among the available
You have to set passwords for the following schemas:
resources, based on changing needs
SYS, SYSTEM, DBSNMP, and SYSMAN.
• Pooling of resources to maximize availability and
It’s DBA job to unlock the other standard user accounts
utilization
and set new passwords for them.
Cluster Ready Services
Installation New Features Support
The Oracle 10g installation supports several Real
Database Management Choices
Application Clusters (RAC) features, including the
installation of the Cluster Ready Services (CRS) feature.
• You can manage your databases locally using the
OEM Database Control, which is part of the Oracle
10g server software.
MetaLink Integration
•
In Oracle 10g, you can directly link the OEM to the
You can manage your databases centrally, through
the OEM Grid Control, which is available on separate
OracleMetaLink service. Through this built-in MetaLink
CDs.
integration, OEM can then automatically track any new
software patches for you. You can arrange to receive
The Grid Control includes:
alerts whenever the OEM spots new patches.
• Oracle Management Agent
•
Oracle Software Cloning
Oracle Management Service
•
The OEM Grid Control enables you to easily duplicate
Oracle Management Repository
Oracle Database 10g software installations (Oracle
• Grid Control console
Homes) from a master installation to one more servers.
If you create a database manually, you must configure
and install the OEM Database Control using the Oracle-
Database Cloning
supplied build script (EM Configuration Assistant):
Using the OEM, you can now easily clone databases.
• $ORACLE_HOME/bin/emca for UNIX
OEM performs database cloning by using RMAN. You use
•
the OEM Clone Database wizard, also known as the
$ORACLE_HOME\bin\emca.bat for Windows.
Clone Database Tool, to perform the various steps in a
Note: In order to access the OEM Database Control
database cloning operation.
from your browser, you must first have the dbconsole
process running on your system.
Automatic Pre-Install Checks
Oracle Universal Installer (OUI) now manages the entire
pre-install requirements check automatically. Common
checks performed are the following:
• Correct operating system version and compatibility
level
• Operating system patches
Page 3
Oracle 10g New Features for Administrators (Summary Sheets)

---

Performance Enhancements to the
Simplified Instance Configuration
Installation Process
Database Configuration Assistant (DBCA)
Single CD Installation
Enhancements
Although the Oracle Database 10g server software
Using the DBCA ensures that DBA is reminded about all
comes in a pack of CD-ROMs, you need only a single CD
the important options, rather than needing to remember
to complete your Oracle 10g server installation. It takes
them and perform them all manually. Following are
only about 20 minutes to complete the entire
some of the DBCA enhancements:
installation.
1. The SYSAUX Tablespace: This is a new tablespace
introduced in Oracle 10g used as a central location
Hardware Requirements
for the metadata of all tools like the OEM and RMAN.
• Memory: You need 256MB for the basic database,
2. Flash Recovery Area: This is a unified storage
and 512MB if you are using the stand-alone version
location on your server that Oracle reserves
of the OEM (the OEM Database Control).
exclusively for all database recovery-related files and
•
activities.
Disk space: You need a maximum of about 2.5GB
of disk space for the Oracle software. In addition,
3. Automatic Storage Management (ASM)
you need 1GB of swap space and about 400MB of
4. Management Options: like alert notification, job
disk space in the /tmp directory.
scheduling, and software management.
Easier and Cleaner Deinstallation
Policy-Based Database Configuration Framework
In the deinstallation process, related software files and
Oracle 10g enables you to monitor all of your databases
Widows registry entries are removed.
to see if there are any violations of the predetermined
To deinstall your Oracle 10g software, follow
configuration policies. This can be managed in the
these steps:
Database Control using following sections:
1. Shut down all databases and ASM instances running
o Diagnostic Summary: shows you if there are any
under the Oracle Home you want to remove, and
policy violations anywhere
then remove the databases.
o Policy Violations: summarizes all policy violations in
2. Stop all the relevant processes running under this
your databases and hosts.
Oracle Home, by running the following commands:
o Manage Policy Library: to disable any policy.
$ORACLE_HOME/bin/emctl stop dbconsole – shuts
down the OEM.
Simplified Initialization Parameters
$ORACLE_HOME/bin/lsnrctl stop – brings down the
Oracle listener
• Basic initialization parameters: This set consists
of about 25 to 30 of the most common parameters
$ORACLE_HOME/bin/isqlplusctl stop – brings
that you need for an Oracle database.
down the iSQL*Plus server
•
3.
Advanced initialization parameters: These are
Start the OUI.
parameters you’ll need to deploy only rarely, to
4. Click Deinstall Products in the Welcome window.
improve your database’s performance or to overcome
5. In the Inventory window, select the correct Oracle
some special performance problems.
Home that contains the software you want to
Changes in the Initialization Parameters
deinstall, and then click Remove.
Deprecated Parameters
6. Manually remove the Home directory that you just
deinstalled.
MTS_DISPATCHERS
UNDO_SUPPRESS_ERRORS
Automatic Launching of Software
PARALLEL_AUTOMATIC_TUNING
The following products will launch automatically
Obsolete Parameters
immediately after you complete the server installation:
Oracle Management Agent, the OEM Database Control,
DISTRIBUTED_TRANSACTIONS
and the iSQL*Plus server.
ORACLE_TRACE_COLLECTION_NAME
MAX_ENABLED_ROLES
Response File Improvements
New Parameters
The following are the new Oracle 10g improvements in
RESUMABLE_TIMEOUT
the response file, which help you perform a truly “silent”
Oracle installation:
SGA_TARGET
•
PLSQL_OPTIMIZE_LEVEL
The file has a new header format, which makes the
response file easier to edit.
Irreversible Datafile Compatibility
• You don’t need to specify an X server when
performing installations in a character mode console.
The minimum value of the COMPATIBILE initialization parameter
is 9.2.0. The default value, however, is 10.0.0. If value of the
• You don’t need to set the DISPLAY variable on UNIX
parameter was 10.0.0, this means that you won’t be able to
systems.
downgrade the Oracle 10g database to a prior release; the
• No GUI classes are instantiated, making this a truly
datafile is irreversible.
silent method of installing software.
The ALTER DATABASE RESET COMPATIBILITY command is
obsolete in Oracle 10g.
Page 4
Oracle 10g New Features for Administrators (Summary Sheets)

---

Managing Database Control
Database Usage Statistics in the OEM

Following are the steps to view database usage statistics
Important EM Agent Directories
in the OEM Database Control:
When you install Oracle Database 10g, a set of
1. Go the Database Control home page. Click the
directories and files related to Enterprise Manager is
Administration link and go to the Configuration
created in the Oracle Home directory:
Management group (in release 2 it is named as
•
Database Configuration). Click the Database
emca and emctl utilities are installed in the
ORACLE_HOME/bin
Usage Statistics link.
• Files that are shared among all instances of the
database are stored in ORACLE_HOME/sysman
Supported Upgrade Paths to Oracle 10g
• Files that are unique to each instance of the
database are stored in ORACLE_HOME/hostname_sid/
You can migrate directly to the Oracle Database 10g
•
version only if your database is one of the following
The log files for the Management Agent for that
instance are installed in
versions: 8.0.6, 8.1.7, 9.0.1, or 9.2.
ORACLE_HOME/hostname_sid/sysman/log/
You can upgrade to Oracle Database 10g in two ways:
• The files required to deploy the Database Control
• the traditional manual mode
application are installed in the
ORACLE_HOME/oc4j/j2ee directory structure.
• by using the Database Upgrade Assistant (DBUA)
•
Note: The DBUA is a GUI tool, but you can also run it
The emd.properties and emoms.properties files
in the silent mode, by using the following command at
store agent run-time parameters, and targets.xml
the operating system level:
lists the configured targets.
dbua
Configuring Database Control
Using New Utility to Perform Pre-Upgrade
You can use the operating system command line to
Validation Checks
configure Database Control. You can use Enterprise
Manager Configuration Assistant (EMCA) to perform the
Oracle now includes a brand-new tool, called the
following tasks:
Upgrade Information Tool, to help you collect various
• specify the automatic daily backup options.
pieces of critical information before you start the
emca -backup
upgrade process.
The Upgrade Information Tool provides important
• add or remove the Enterprise Manager configuration,
information and actions you should do before upgrading
including the management repository.
the existing database.
emca –config dbcontrol db [–repos
If you are performing a manual upgrade, you need to
create|recreate]
invoke the tool by running the SQL script utlu10*i.sql.
emca -deconfig dbcontrol db [–repos drop]
The DBCA automatically runs it as part of the pre-
•
upgrade check.
reconfigure the default ports used by Enterprise
Manager
Note: In Oracle 10g Release 2, the Pre-Upgrade
emca -reconfig ports -DBCONTROL_HTTP_PORT
Information Utility (utlu102i.sql) has been enhanced
5500
to provide improved resource estimations for tablespace
space usage and elapsed upgrade runtime.
Viewing Database Feature Usage Statistics
The Post-Upgrade Status Tool
Oracle Database 10g also provides a Post-Upgrade
The Statistics Collection Process
Status Tool (utlu10*s.sql), which gives you an
Oracle Database 10g introduces a new database process
accurate summary of the upgrade process and any
called Manageability Monitor Process (MMON), which
necessary corrective steps to be taken.
records both the database usage statistics and the HWM
You can restart a failed database upgrade job from the
statistics for various objects.
point where you failed.
MMON process is primarily responsible for:
If you use the DBUA to upgrade, the script runs
o issuing database alerts
automatically. If you are performing a manual upgrade,
o collecting statistics
you need to run the script yourself, after the upgrade
process is finished.
o taking snapshots of data into disks
MMON records the various statistics inside the Automatic
Workload Repository (AWR), which is a new Oracle
Using the Simplified Upgrade Process
Database 10g innovation that stores database
Oracle provides the DBUA to facilitate the database
performance data.
upgrade process. You can use the DBUA to upgrade any
The related views are:
database configuration, including RAC and standby
databases.
o DBA_FEATURE_USAGE_STATISTICS to find out the
usage statistics of various features that MMON has
The DBUA takes care of the following tasks for you:
stored in the AWR.
• Deletes all obsolete initialization parameters
o DBA_HIGH_WATER_MARK_STATISTICS to see the HWM
• Changes the ORACLE_HOME settings automatically
statistics and a description of all the database
• Runs the appropriate upgrade scripts for your
attributes that the database is currently monitoring.
current release
• Configures your listener.ora file
Page 5
Oracle 10g New Features for Administrators (Summary Sheets)

---

Starting DBUA
Using the startup upgrade command tells Oracle to
On Windows: Programs | Oracle | Configuration and
automatically modify certain parameters, including
Migration Tools | Database Upgrade Assistant.
initialization parameters that cause errors otherwise
On a UNIX system: simply type dbua
13. If you are upgrading from a release other than
Silent startup:
10.1, create the
dbua -silent –dbName nina
SYSAUX Tablespace. The Pre-
Upgrade Information Tool provides an estimate of
the minimum required size for the SYSAUX
Manual Upgrade Process
tablespace in the SYSAUX Tablespace section.
CREATE TABLESPACE sysaux DATAFILE
Steps in the Manual Upgrade Process
'sysaux01.dbf' SIZE 500M
1. Start a Spool File
EXTENT MANAGEMENT LOCAL
SQL> spool upgrade.log
SEGMENT SPACE MANAGEMENT AUTO
ONLINE
2. Run the Upgrade Information Tool
SQL> @$ORACLE_HOME/rdbms/admin/utlu101i.sql
14. If you upgrading to release 1, run the Upgrade
Script. Run the Upgrade Script corresponding to
SQL> spool off
the Oracle version you would like to upgrade:
3. Back Up Your Database
o 8.0.6: u0800060.sql
At this point, shut down and back up your current
o 8.1.7: u0801070.sql
database, by using either the RMAN or by using user-
o 9.0.1: u0900010.sql
managed backup techniques.
o 9.2: u0902000.sql
4. Copy Your init.ora File
15. If you upgrading to Oracle Database 10g Release
Copy your present init.ora file to the new Oracle
2, only one common SQL script has to be invoked
Database 10g default location:
when performing a database upgrade. Oracle
o
automatically determines what version is being
%ORACLE_HOME%\database on Windows with the
name:
upgraded and runs the appropriate upgrade
init%ORACLE_SID%.ora
scripts for that database and all of its included
o $ORACLE_HOME/dbs under UNIX with the name:
components:
init$ORACLE_SID.ora
SQL> SPOOL upgrade.log
Make all the necessary changes in your init.ora
SQL> @catupgrd.sql
parameter file, as per the Upgrade Information Tool’s
recommendations.
16. Depending of the release you are upgrading to,
run utlu10*s.sql (Post-Upgrade Status Tool) to
5. If you are upgrading a cluster database and your
display the results of the upgrade:
initdb_name.ora file resides within the old
SQL> @utlu101s.sql TEXT
environment's Oracle home, then move or copy the
SQL> @utlu102s.sql
initdb_name.ora file to the new Oracle home.
SQL> SPOOL OFF
Make modifications in the file in the same way as
made in the init.ora file.
Note that the utlu101s.sql script is followed by the
word TEXT, to enable the printing of the script output.
6. If you are upgrading a cluster database, then set
the CLUSTER_DATABASE initialization parameter to
The tool simply queries the DBA_SERVER_REGISTRY
false. After the upgrade, you must set this
table to determine the upgrade status of each
initialization parameter back to true.
individual component.
7. Shut down the instance:
17. Check the spool file and verify that the packages
SQL> SHUTDOWN IMMEDIATE
and procedures compiled successfully. Rerun the
8. Completely remove any Windows-Based Oracle
catupgrd.sql script, if necessary.
Instances
18. Restart the instance
C:\>net stop oracleservicefinance
SQL> SHUTDOWN IMMEDIATE
C:\>oradim -delete -sid finance
C:\>oradim -new -sid finance -intpwd finance1
SQL> STARTUP
-startmode auto –pfile
19. If Oracle Label Security is in your database:
c:\oracle\product\10.1.0\Db_1\database\initfi
nance.ora
SQL> @olstrig.sql
9. If your operating system is UNIX, then make sure
20. Run utlrp.sql to recompile any remaining invalid
that your ORACLE_SID is set correctly and that the
stored PL/SQL and Java code.
following variables point to the new release
SQL> @utlrp.sql
directories:
ORACLE_HOME,PATH,ORA_NLS10,LD_LIBRARY_PATH
21. Verify that all expected packages and classes are
valid:
10. Log in to the system as the owner of the Oracle
SQL> SELECT count(*) FROM dba_objects WHERE
home directory of the new Oracle Database 10g
status='INVALID';
release.
SQL> SELECT distinct object_name FROM
11.
dba_objects WHERE status='INVALID';
At a system prompt, change to the
ORACLE_HOME/rdbms/admin directory.
22. Exit SQL*Plus
12. Start Up the New Database
sqlplus /nolog
SQL> connect / as sysdba
SQL> startup upgrade
Page 6
Oracle 10g New Features for Administrators (Summary Sheets)

---

Reverting Upgraded Database
o Tables with fine-grained access control enabled in
the insert mode
Instructing DBUA to perform a backup of your database
o Tables with BFILE or opaque type columns
(with the RMAN) will provide you the option to revert
the database to the older version by the end of the
Note: The datafile format is identical in external
upgrade process.
tables and the direct-access methods.
You can also revert back manually to the older database
Data Pump Files
by using the DB_Name_restore.bat file (under
Windows), providing that you have a cold backup of the
• Dump files: These hold the data for the Data Pump
database.
job.
• Log files: These are the standard files for logging
the results of Data Pump operations.
Loading and Unloading Data
• SQL files: Data Pump import uses a special
parameter called SQLFILE, which will write all the
Data Definition Language (DDL) statements it will
Introduction to the Data Pump Architecture
execute during the import job to a file.
Using Export and Import Data Pump utilities you can:
Using Directory Objects
• export and import data faster than Old export/import
You can’t use absolute directory path location for Data
utilities
Pump jobs; you must always use a directory object.
• estimate job times
To create a directory, a user must have the CREATE ANY
• perform fine-grained object selection
DIRECTORY privilege:
• monitor jobs effectively
CREATE DIRECTORY dpump_dir1 as
•
'c:\oracle\product\10.1.0\oradata\export'
directly load one database from a remote instance
• call the utilities from PL/SQL using Data Dump API
In order for a user to use a specific directory, the user
•
must have access privileges to the directory object:
stop, resume and restart the utilities
•
GRANT READ, WRITE ON DIRECTORY dpump_dir1 TO
attach a running job to monitor jobs, as well as to
modify certain parameters interactively.
salapati
• have fine-grained data import capability
Note: In Oracle 10g Release 2, a directory object
•
named DATA_PUMP_DIR as created by default in the
remap objects of a specific schema to another
schema
database. In Windows, it is mapped to
<ORACLE_BASE>\admin\<sid>\dpdump\ directory. By
Note : the export Data Pump user process launches a
default, it is available only to privileged users.
server-side process that writes data to disks on the
server node, not the client that launches the utility.
1. Using the DIRECTORY:FILE Notation:
Note: The new Data Pump technology lets you export
expdp LOGFILE=dpump_dir2:salapati.log …
data only to disk. You cannot use a tape drive when
2.
performing a Data Pump export.
Using the DIRECTORY parameter
You can use the DIRECTORY parameter to specify the
name of the directory object:
Data Pump Components
expdp hr/hr DIRECTORY=dpump_dir1 …
• The DBMS_DATAPUMP package: this is the main
3.
engine of the Data Pump utilities. It contains
Using the default directory DATA_PUMP_DIR
procedures that do the export and import actions.
You can create a default directory with the name
DATA_PUMP_DIR, and then not need to specify the
• The DBMS_METADATA package: this package is used
DIRECTORY parameter in your export and import
to extract and modify data dictionary metadata.
commands. Data Pump will write all dump files, SQL
• The command-line clients, expdp and impdp.
files, and log files automatically to the directory
specified for DATA_DUMP_DIR.
Data-Access Methods
4. Using the DATA_DUMP_DIR Environment Variable
• Direct path: the direct path internal stream format
You can use the DATA_DUMP_DIR environment variable
is the same format as the data stored in Oracle dump
on the client to point to the directory object on the
files.
server. Data Pump will automatically read and/or write
• External tables: Oracle reads data from and write
its files from that directory object. In Windows, this
data to operating system files that lie outside the
variable is set in the Registry.
database.
Data Pump automatically selects the most appropriate
Order of Precedence for File Locations
access method for each table. It always tries to first use
As in the order indicated above.
the direct-path method. Under some conditions, such as
the following, it may not able to use the direct method:
The Mechanics of a Data Pump Job
o Clustered tables
The Master Process
o Presence of active triggers in the tables
The master process, or more accurately, the Master
o Export of a single partition in a table with a global
Control Process (MCP), has a process name of DMnn.
index
The full master process name is of the format
o
<instance>_DMnn_<pid>
Presence of referential integrity constraints
o
The master process performs the following tasks:
Presence of domain indexes on LOB columns
o Creates jobs and controls them
Page 7
Oracle 10g New Features for Administrators (Summary Sheets)

---

o Creates and manages the worker processes
Data Pump Export Parameters
o Monitors the jobs and logs the progress
File- and Directory-Related Parameters
o Maintains the job state and restart information in
DIRECTORY
the master table
specifies the location of the dump and other files.
o Manages the necessary files, including the dump file
set
DUMPFILE
provides the name of the dump file to which the export
Oracle creates the master table in the schema of the
dump should be written.
user who is running the Data Pump job at the beginning
You can provide multiple dump filenames in several
of every export job. The master table has the same
ways:
name as the export job, such as
o by specifying the %U substitution variable. Using this
SYS_EXPORT_SCHEMA_01. Master table will be
method, the number of files you can create is equal
automatically deleted by end of a successful export or
to the value of the PARALLEL parameter.
import job.
o using a comma-separated list.
Note: The master table contains all the necessary
information to restart a stopped job. It is thus the key to
o specifying the DUMPFILE parameter multiple times
Data Pump’s job restart capability, whether the job
FILESIZE
stoppage is planned or unplanned.
this optional parameter specifies size of export file. The
The Worker Process
export job will stop if your dump file reaches its size
The worker process is the process that actually performs
limit.
the heavy-duty work of loading and unloading data, and
PARFILE
has the name DWnn (<instance>_DWnn_<pid>).
used to specify the parameter file. Every parameter
MCP(DMnn) may create number of DWnn, if you choose
should be in a line.
the PARALLEL option for load. DWnn process maintains
Note: The directory object is not used by this
the object rows of the master table.
parameter. The directory path is an operating system-
Shadow Process
specific directory specification. The default is the user's
The shadow process creates the job consisting of the
current directory.
master table as well as the master process.
LOGFILE and NOLOGFILE
Client Processes
You can use the LOGFLE parameter to specify a log file
The client processes call the Data Pump’s API. You
for your export jobs. If you don’t specify this
perform export and import with the two clients, expdp
parameter, Oracle will create a log file named
and impdp.
export.log. If you specify the parameter NOLOGFILE,
Oracle will not create its log file.
Export Mode-Related Parameters
Using Data Pump Export and Import
The export mode-related parameters are the FULL,
Data Pump Export Interfaces
SCHEMAS, TABLES, TABLESPACES,
TRANSPORT_TABLESPACES, and TRANSPORT_FULL_CHECK
Using the Command Line
parameters. The TRANSPORT_FULL_CHECK parameter
expdp system/manager directory=dpump_dir1
simply checks to make sure that the tablespaces you are
dumpfile=expdat1.dmp
trying to transport meet all the conditions to qualify for
Using a Parameter File
the job.
expdp parfile=myfile.txt
Export Filtering Parameters
Using Interactive-command Data Pump Export
CONTENT
In Data Pump export, you use the interactive-command
It controls contents of exported data. The possible
interface for one purpose only: when you decide you
values are:
need to change some export parameters midstream,
o ALL exports data and definitions (metadata).
while the job is still running. Note that the export or
import job keeps running throughout, without any
o DATA_ONLY exports only table rows.
interruption.
o METADATA_ONLY exports only metadata (this is
This mode is enabled by pressing [Ctrl] + [C] during an
equivalent to rows=n ).
export operation started with the command-line
EXCLUDE and INCLUDE
interface or the parameter file interface.
Those are mutually exclusive parameters. The EXCLUDE
Using EM Database Control
parameter is used to omit specific database object
Start the Database Control and go to the Maintenance |
types from an export or import operation. The INCLUDE
Utilities page.
parameter enables you to include only a specific set of
objects.
Data Pump Export Modes
The syntaxes of using them are as follows:
o Full export mode: using FULL parameter
EXCLUDE=object_type[:name_clause]
INCLUDE=object_type[:name_clause]
o Schema mode: using SCHEMAS parameter
Examples:
o Tablespace mode: using TABLESPACES and/or
EXCLUDE=INDEX
TRANSPORT_TABLESPACES parameters
EXCLUDE=TABLE:"LIKE 'EMP%'"
o Table mode: using TABLES parameter
EXCLUDE=SCHEMA:"='HR'"
INCLUDE=TABLE:"IN ('EMP', 'DEPT')"
Page 8
Oracle 10g New Features for Administrators (Summary Sheets)

---

QUERY
ADD_FILE (interactive parameter)
This parameter lets you selectively export table row
Use this parameter to add a dump file to your job.
data with the help of a SQL statement.
expdp> ADD_FILE=hr2.dmp, dpump_dir2:hr3.dmp
QUERY=OE.ORDERS: "WHERE order_id > 100000"
HELP (can be used in interactive mode)
Estimation Parameters
Displays online help.
ESTIMATE
The ESTIMATE parameter will tell you how much space
STATUS (can be used in interactive mode)
your new export job is going to consume.
This parameter displays detailed status of the job,
along with a description of the current operation. An
By default, Oracle will used the blocks method to do its
estimated completion percentage for the job is also
estimation.
returned.
Total estimation using BLOCKS method: 654 KB
In logging mode, you can assign an integer value (n)
When you set ESTIMATE=statistics, Oracle will use
to this parameter. In this case, job status is displayed
the statistics of the database objects to calculate its
on screen every n second.
estimation.
JOBNAME
Total estimation using STATISTICS method:
Use this parameter to provide your own job name for a
65.72 KB
given Data Pump export/import job. If not provided,
ESTIMATE_ONLY
Oracle will give it a name of the format
Use this parameter to estimate the required export file
<USER>_<OPERATION>_<MODE>_%N.
size without starting an actual export job.
Example: SYSTEM_EXPORT_FULL_01
The Network Link Parameter
PARALLEL
NETWORK_LINK
This parameter lets you specify more than a single
You can initiate an export job from your server and
active execution thread for your export job. You should
have Data Pump export data from a remote database
specify number of dump files equal to the PARALLEL
to dump files located on the instance from which you
value.
initiate the Data Pump export job.
expdp system/manager full=y
expdp hr/hr DIRECTORY=dpump_dir1
parallel=4
NETWORK_LINK=source_database_link
dumpfile=
DUMPFILE=network_export.dmp
DIR1:full1%U.dat,
DIR2:full2%U.dat,
Interactive Mode Export Parameters
DIR3:full3%U.dat,
You can enter the interactive mode of Data Pump export
DIR4:full4%U.dat
in either of two ways:
filesize = 2G
o To get into the interactive mode, press Ctl+C while
impdp system/manager
the job is running.
directory = MYDIR
o You can also enter the interactive mode of
parallel = 4
operation by using the ATTACH command.
dumpfile = full1%U.dat,full2%U.dat,
expdp salapati/sammyy1
full3%U.dat,full4%U.dat
attach=SALAPATI.SYS_EXPORT_SCHEMA_01
Dumpfile Compression Parameter
You must be a DBA, or must have EXP_FULL_DATABASE
COMPRESSION =(METADATA_ONLY | NONE)
or IMP_FULL_DATABASE roles, in order to attach and
This parameter applies from Oracle 10.2. It specifies
control Data Pump jobs of other users.
whether to compress metadata before writing to the
dump file set. Compression reduces the amount of disk
CONTINUE_CLIENT (interactive parameter)
space consumed by dump files.
This parameter will take you out of the interactive
mode. Your client connection will still be intact, and
you’ll continue to see the export messages on your
Data Pump Import Parameters
screen.
You’ll need the IMPORT_FULL_DATABASE role to perform
an import if the dump file for the import was created
EXIT_CLIENT (interactive parameter)
using the EXPORT_FULL_DATABASE role.
This parameter will stop the interactive session, as well
as terminate the client session.
File- and Directory-Related Parameters
The Data Pump import utility uses the PARFILE,
STOP_JOB (interactive parameter)
DIRECTORY,
DUMPFILE,
LOGFILE, and NOLOGFILE
This parameter stops running Data Pump jobs.
commands in the same way as the Data Pump export
START_JOB (interactive parameter)
utility.
This parameter resumes stopped jobs. You can restart
SQLFILE
any job that is stopped, whether it’s stopped because
This parameter enables you to extract the DDL from the
you issued a STOP_JOB command or due to a system
export dump file, without importing any data.
crash, as long as you have access to the master table
and an uncorrupted dump file set.
impdp salapati/sammyy1 DIRECTORY=dpump_dir1

DUMPFILE=finance.dmp
KILL_JOB (interactive parameter)
SQLFILE=dpump_dir2:finance.sql
This parameter kills both the client and the Data Pump.
REUSE_DATAFILES
If a job is killed using the KILL_JOB interactive
This parameter tells Data Pump whether it should use
command, the master table is dropped and the job
existing datafiles for creating tablespaces during an
cannot be restarted.
import.
Page 9
Oracle 10g New Features for Administrators (Summary Sheets)

---

Import Mode-Related Parameters
value, retrieves the data, and writes it directly back to
You can perform a Data Pump import in various modes,
the target database. There are no dump files involved.
using the TABLE, SCHEMAS, TABLESPACES, and FULL
impdp hr/hr TABLES=employees
parameters, just as in the case of the Data Pump export
DIRECTORY=dpump_dir1
utility.
NETWORK_LINK=source_database_link
EXCLUDE=CONSTRAINT
Filtering Parameters
The log file is written to dpump_dir1, specified on the
The Data Pump import utility uses the CONTENT, EXCLUDE
DIRECTORY parameter.
and INCLUDE parameters in the same way as the Data
Pump export utility. If you use the CONTENT=DATA_ONLY
The TRANSFORM Parameter
option, you cannot use either the EXCLUDE or INCLUDE
TRANSFORM
parameter during an import.
This parameter instructs the Data Pump import job to
modify the storage attributes of the DDL that creates
QUERY can also be used but in this case Data Pump will
use only the external table data method, rather than the
the objects during the import job.
direct-path method, to access the data.
TRANSFORM = transform_name:value[:object_type]
TABLE_EXISTS_ACTION
transform_name: takes one of the following values:
Use this parameter to tell Data Pump what to do when
SEGMENT_ATTRIBUTES
a table already exists.
If the value is specified as y, then segment
o SKIP (the default), Data Pump will skip a table if it
attributes (physical attributes, storage attributes,
exists.
tablespaces, and logging) are included, with
o
appropriate DDL. The default is
APPEND value appends rows to the table.
y.
o TRUNCATE value truncates the table and reloads the
STORAGE
data from the export dump file.
If the value is specified as y, the storage clauses
are included, with appropriate DDL. The default is
o
y.
REPLACE value drops the table if it exists, re-
creates, and reloads it.
This parameter is ignored if
SEGMENT_ATTRIBUTES=n.
Job-Related Parameters
OID
The JOB_NAME, STATUS, and PARALLEL parameters carry
If the value is specified as n, the assignment of the
identical meanings as their Data Pump export
exported OID during the creation of object tables
counterparts.
and types is inhibited. Instead, a new OID is
Import Mode-Related Parameters
assigned. This can be useful for cloning schemas,
but does not affect referenced objects. The default
You can perform a Data Pump import in various modes,
is y.
using the TABLES, SCHEMAS, TABLESPACES, and FULL
parameters, just as in the case of the Data Pump export
PCTSPACE
utility.
It accepts a greater-than-zero number. It
represents the percentage multiplier used to alter
Remapping Parameters
extent allocations and the size of data files.
REMAP_SCHEMA
Using this parameter, you can move objects from one
object_type: It can take one of the following values:
CLUSTER,CONSTRAINT,INC_TYPE,INDEX,ROLLBACK_SEG
schema to another.
MENT,TABLE,TABLESPACE,TYPE
impdp system/manager dumpfile=newdump.dmp
REMAP_SCHEMA=hr:oe
impdp hr/hr TABLES=employees \
DIRECTORY=dp_dir DUMPFILE=hr_emp.dmp \
REMAP_DATAFILE
TRANSFORM=SEGMENT_ATTRIBUTES:n:table
Changes the name of the source datafile to the target

datafile name in all SQL statements where the source
datafile is referenced: CREATE TABLESPACE, CREATE
impdp hr/hr TABLES=employees \
DIRECTORY=dp_dir DUMPFILE=hr_emp.dmp \
LIBRARY, and CREATE DIRECTORY.
TRANSFORM=STORAGE:n:table
Remapping datafiles is useful when you move
databases between platforms that have different file
naming conventions.
Monitoring a Data Pump Job
impdp hr/hr FULL=y DIRECTORY=dpump_dir1
DUMPFILE=db_full.dmp
Viewing Data Pump Jobs
REMAP_DATAFILE='DB1$:[HRDATA.PAYROLL]tbs6.f':'
The
/db1/hrdata/payroll/tbs6.f'
DBA_DATAPUMP_JOBS view shows summary
information of all currently running Data Pump jobs.
REMAP_TABLESPACE
OWNER_NAME : User that initiated the job
This parameter enables you to move objects from one
tablespace into a different tablespace during an
JOB_NAME : Name of the job
import.
OPERATION : Type of operation being performed
impdp hr/hr
REMAP_TABLESPACE='example_tbs':'new_tbs'
JOB_MODE : FULL, TABLE, SCHEMA, or TABLESPACE
DIRECTORY=dpump_dir1 PARALLEL=2
STATE : UNDEFINED, DEFINING, EXECUTING, and NOT
JOB_NAME=cf1n02 DUMPFILE=employees.dmp
NOLOGFILE=Y
RUNNING.
DEGREE : Number of worker processes performing the
The Network Link Parameter
operation
NETWORK_LINK
In case of network import, the server contacts the
ATTACHED_SESSIONS : Number of sessions attached to
remote source database referenced by the parameter
the job.
Page 10
Oracle 10g New Features for Administrators (Summary Sheets)

---

Document Outline

• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ
• ÿ

---