Oracle 10g/11g Data and Database Management Utilities

Table of Contents

Oracle 10g/11g Data and Database Management Utilities

Credits

About the Author

About the Reviewer

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code for the book

Errata

Piracy

Questions

1. Data Pump

Data Pump features

Data Pump architecture

Setting up the practical scenarios

Data Pump export

Data Pump export modes

A simple Data Pump export session

Data Pump export filtering operations

Use of parameter file

Retrieve original data

Data Pump export space estimation

Dump file multiplexing

Transporting data among different versions

Data Pump export interactive mode

Data Pump restart capability

Getting information about the export job

Data Pump import

Remap function

Data Pump import network mode

Improving performance with Data Pump

Working with the Data Pump API

Data Pump 11g new features

Compression

Encrypted dump file sets

Enhancements for Data Pump External Tables

Single partition transportable for Oracle Data Pump

Overwrite dump files

XML Datatypes

Summary

2. SQL*Loader

SQL*Loader basics

Preparing the demo environment

Our first simple load

The SQL*Loader log file

Fixed record size format load

Variable record size format load

Stream record format load

Specifying a particular character set

Load on the fly

Direct path versus Conventional path load

Direct path load pros

Direct path load cons

Loading Large Objects (LOBs)

Loading multimedia files

Resumable load

Parallel load

General performance booster tips

Summary

3. External Tables

The External Table basics

Let's setup the environment

A basic External Table

Creating External Table metadata, the easy way

Unloading data to External Tables

Inter-version compatibility

Data transformation with External Tables

Extending the alert.log analysis with External Tables

Reading the listener.log from the database

Connect String

Protocol

Mapping XML files as External Tables

Dynamically changing the external reference

Oracle 11g External Table enhancements

Summary

4. Recovery Manager Advanced Techniques

Recovery Manager basics

Getting started with a Recovery Manager session

Format masks used by recovery manager

What happens in a user-managed online backup?

Myths related to the online backup method

Configuring a multiplexed backup

Configuring the RMAN recovery catalog

A simple backup session

Backup compression

Fast backup compression

Improving data set compression with the ZLIB algorithm (11g only)

Faster backups through intra-file parallel backup and restore operations (11g only)

Block media recovery

Backup duration and throttling

Database cloning

Database cloning procedure

Database cloning on the fly (11g only)

Inter-platform database migration

Migrate to and from an ASM environment

General backup advices

Summary

5. Recovery Manager Restore and Recovery Techniques

Oracle database recovery

Instance failure

Media failure

Complete recovery

Incomplete recovery

Loss of data files

Queries used to diagnose data files

Loss of a non-critical datafile

Loss of a temporary datafile

Managing temporary datafiles

Loss of a critical datafile

Loss of redo log files

Loss of the inactive redo log group

Loss of the current redo log group

Test restore

Crosscheck command

Nologging considerations

Summary

6. Session Management

User sessions in a dedicated server architecture

Instance self registration process

Blocking sessions

Optimistic versus pessimistic locking

Row lock contention monitoring

Killing sessions

Deadlock handling

Sniped sessions

Orakill

Services

Resource Manager

Resource Manager Elements

Configuring resources assigned to users

Configuring resources assigned to services

Creating the database user

Service names definition

Listener verification

TNS entry configuration

Resource consumer group creation

Service mapping

Resource plan definition

Resource manager plan activation

Testing and monitoring

Active Session History (ASH)

Session monitoring, the traditional way

Summary

7. Oracle Scheduler

Oracle Scheduler concepts

Getting started with the Oracle Scheduler

Required privileges

Scheduling our first job

Creating the job

Specifying procedure arguments

Enabling the job schedule

Using Enterprise Manager

Time expression syntax

The repeat interval

Regular schedule

Combined schedule

Exclude scenario

Include scenario

Intersect scenario

Time expression examples

Programs

Creating programs manually

Defining a program using Enterprise Manager

Schedules

Jobs and Job Classes

Managing the Scheduler

Enable or disable components

Managing job logs

Monitor a Job Execution

Purging the job log

Data dictionary related views

Summary

8. Oracle Wallet Manager

The Oracle Wallet Manager

Creating the Oracle Wallet

Enabling Auto Login

mkwallet, the CLI OWM version

Managing Wallets with orapki

Oracle Wallet Manager CSR generation

Storing the Oracle Wallet in the Windows registry

Save Wallet to the registry

Open the Wallet from the registry

Save as to a different registry location

Open the Wallet from the registry, save it to the file system and vice versa

Delete the Wallet from the registry

Configuring the Wallet location

Storing the Wallet in an LDAP server

Uploading the Wallet to an LDAP server

Downloading the Wallet from LDAP

Using certificates for authentication

Public Key Infrastructure tools

Using the Oracle Wallet to store database credentials

Summary

9. Security Management

Using the Oracle Wallet to encrypt backups

Recovery Manager encryption

Using the transparent mode

Using the password mode

Using the dual mode

RMAN backup shredding (11g only)

Data pump encryption

The enterprise user

Configuring the environment

How Oracle SSO works

Configure access to the LDAP directory

Registering the database against the OID

Shared schema

Summary

10. Database Configuration Assistant

DBCA

Database creation

Database templates

Database identification

Management options

Database credentials

Storage options

Database file locations

Database content

Initialization parameters

Memory

Character sets

Connection mode

Database storage

Creation options

Database edition

Database template management

Template management operations

Creating a seed database out of a current database

Database related file location

Migrating a single instance database to RAC

Automatic Storage Management configuration

ASM

How to setup ASM using DBCA

Select the Configure ASM option

Run the localconfig shell script as root

Set the SYS password and the ASM instance parameters

Setup disk groups

ASM disk group's validation

Setting up ASM in a Windows environment

ASM setup

Disk layout

Logical partitions

Setup ASM

DBCA, Batch mode

DBCA response file example

Where can you get a DBCA response file

Summary

11. Oracle Universal Installer

OUI basics

OUI components

Setting up a stage area

DVD distribution

Troubleshooting an installation session

Oracle Universal Installer JRE

OUI system requirements

OUI basic and advanced installation modes

OUI Basic Installation

Licensed installed options

OUI Advanced Installation

Modes of installation

OUI command line parameters

Command line variables usage

Silent installation mode

The response file structure and syntax

Customizing a response file

Creating a response file out from an actual installation

The Batch installation, step by step

Creating a response file to perform a batch deinstallation

The oraparam.ini file

OUI return codes

Installing Oracle from the Web

Recovering a lost Inventory

Cloning Oracle Home using OUI

Summary

12. Enterprise Manager Configuration Assistant

Enterprise Manager Components

Differences between EM DB Control and EM Grid Control

Enterprise Manager configuration

How to find out if the console components are currently installed

Console setup prerequisites

Configuring EM using DBCA

Manually configuring Enterprise Manager with EMCA

Manually assigning EM managing ports

EMCA Command Line Interface

EMCA commands

EMCA flags

EMCA general Command-Line Parameters

EMCA backup parameters

EMCA ASM parameters

EMCA Cluster (RAC) parameters

EMCA 10g Release 1

EMCA 10gR1 syntax

EMCA 10gR1 options

EMCA 10gR1 parameters

EMCA 10gR1 RAC parameters

EMCA silent mode

EM directory structure

EMCA log files

The SYSMAN configuration files

The SYSMAN log files

Environment changes

Changing the IP address or host name

Changing administrative passwords

Changing SYSMAN password

Changing DBSNMP password

Securing Enterprise Manager

Summary

13. OPatch

OPatch

Downloading the latest OPatch version

OPatch requirements

OPatch syntax

OPatch options

Oracle maintenance using OPatch

Applying a single patch using OPatch

Querying the Oracle inventory

Rolling back a failed OPatch session

Considerations after applying a patch

OPatch in Oracle 11g

Oracle Configuration Manager Registration

Critical Patch Updates

Find out the installed patches

Critical Patch Advisory

Hot patching (11g only)

Troubleshooting OPatch

PATH environment variable

OPatch log files

Using Enterprise Manager for software maintenance

Enterprise Manager Metalink configuration

Refresh from Metalink Job

Downloading and staging patches

The Patch Cache

Managing Patches in EM 11g

Patch Advisor

Critical Security Patches

Feature based patching

View Patch Cache

Patch prerequisites

Stage patch

Apply patch

Summary

Index

Oracle 10g/11g Data and Database Management Utilities

Hector R. Madrid

Oracle 10g/11g Data and Database Management Utilities

Copyright © 2009 Packt Publishing

All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.

Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, Packt Publishing, nor its dealers or distributors will be held liable for any damages caused or alleged to be caused directly or indirectly by this book.

Packt Publishing has endeavored to provide trademark information about all the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.

First published: June 2009

Production Reference: 1220609

Published by Packt Publishing Ltd.

32 Lincoln Road

Olton Birmingham, B27 6PA, UK.

ISBN 978-1-847196-28-6

www.packtpub.com

Cover Image by Vinayak Chittar (<vinayak.chittar@gmail.com>)

Credits

Author

Hector R. Madrid

Reviewers

Hans Forbrich

Peter McLarty

Ulises Lazarini

Acquisition Editor

James Lumsden

Development Editor

Dhiraj Chandiramani

Technical Editor

John Antony

Indexer

Rekha Nair

Editorial Team Leader

Gagandeep Singh

Project Team Leader

Priya Mukherji

Project Coordinator

Leena Purkait

Proofreader

Lesley Harrison

Production Coordinator

Dolly Dasilva

Cover Work

Dolly Dasilva

About the Author

Hector R. Madrid is a highly respected Oracle professional with 20 years of experience as a full time DBA. He has been working with Oracle databases from version 5.0 up to the latest 11g release. He was the first Oracle Certified Master in Latin America and he holds the Oracle Certified Professional certificate for all Oracle versions starting with 7.3 up to 11g.

He obtained a bachelor's degree in Electronic Engineering from the Metropolitan Autonomous University in 1992, with a major in Digital Systems and Computers. He obtained a Master's degree in Computer Science from the same University. He has collaborated with Oracle Corp. as an instructor teaching the database track since 1996.

Hector works as a database consultant for several major firms, dealing with a wide range of DBA requirements, ranging from daily DBA tasks to defining and maintaining mission critical and high availability systems.

He has presented different technical papers at several Oracle conferences. He is the author of the Blog 'Oracle by Madrid' a Blog specializing in Oracle database topics.

To my parents Higinio Rivera and Teresa Madrid who taught me the basis of who I am now.

My wife Claudia and my daughter Alexandra for their extraordinary patience and support during the development of this book

Hans Forbrich, a respected Oracle ACE Director, who has forged each letter of this title with knowledge and experience, for his valuable technical feedback and all his comments both in this book and in general in the Oracle community.

About the Reviewer

Hans Forbrich has been around computers for 40 years. Indeed, while studying for his BSc EE in the 1970s he worked as a contract programmer to help pay for school. Hans has been working with Oracle products since 1984. In the field service group at Nortel he was introduced to Oracle Database version 4. He joined Oracle Canada to work in the Communications vertical from 1996 to 2002. In 2003 Hans started Forbrich Computer Consulting Ltd., which has become a successful international Oracle consultancy and Oracle training partner based in St. Albert, near Edmonton, Alberta, Canada.

As an Oracle ACE Director and OCP, Hans frequently responds in various Oracle Forums, teaches for Oracle University, consults with Oracle customers on maximizing value from Oracle licenses, and speaks at Oracle User Group conferences around the world. He holds a strong belief that Oracle products provide significant value and the key to extracting that value—and reducing the effective cost of the product—is in understanding the product and using the right tool for the job.

I thank my wife of 27 years for her patience, especially while I experiment in the lab. And also, I thank my two sons for their patience, their assistance at computer setups, and help with those same experiments. (I am proud to note that Son #1, aka Employee #2, aka Chief Network & Systems Administrator, has achieved his MSc EE this past year!) Finally I thank Edmonton Opera and my colleagues there for allowing me to break away from computers and unwind on stage with the Edmonton Opera Chorus.

Peter McLarty has worked with technology for over 25 years. He has been working with Unix and databases for over 10 years with 8 years experience as an Oracle DBA. Peter has worked with Oracle 7.3 through to Oracle 11. Peter has a number of years experience supporting Oracle Application Server. He has experience with RAC and Oracle Maximum Availability Architecture. Peter maintains his own web site with articles about many topics of interest to him and not always about databases or Oracle. Peter has a diverse background in IT supporting his DBA skills and is now involved in Architecture and System Assurance. Peter works for Pacific DBMS, whose office is in Margate, Queensland. Peter is married with 2 children, and several pets to support. When he is not doing things with computers he likes to follow his football team or study things about Asia and learn Thai.

I would like to thank my family for giving me peace to review this book.

Ulises Lazarini is the president of Consultoria Informatica Lazarini, and a partner of Oracle with more than 10 years experience of working with Oracle databases. He has also been an OCP member since Oracle 7.3.4, 8, 8i, 9i, 10g, and so on.

He has been an Oracle instructor in the kernel field for more than 12 years. Ulises has been a speaker at Oracle Open World (September 2008, Migration from Siebel 7.8 running on SQL Server to Oracle 10g RAC) and a DBA Consultant on two successful Oracle database cases. He has been very active in installing and monitoring RAC environments for OLTP and data warehouse databases.

He has been responsible for high availability on global databases.

Preface

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 must-do 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 fly 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.

What you need for this book

This book requires the reader to know the basics of SQL, and have some experience with Oracle 10g and 11g databases.

This book covers an Oracle database installation on Linux, although the techniques detailed are equally applicable to other operating systems.

Who this book is for

This book is aimed at all Oracle professionals who wish to employ must-use data and database utilities, and optimize their database interactions.

Entry-level users can acquaint themselves with the best practices needed to get jobs done in a timely and efficient manner. Advanced users will find useful tips and How-Tos that will help them focus on getting the most out of the database, utilities, and fine-tune batch process.

Conventions

In this book, you will find a number of styles of text that distinguish between different kinds of information. Here are some examples of these styles, and an explanation of their meaning.

Code words in text are shown as follows: We can include other contexts through the use of the include directive.

A block of code will be set as follows:

BEGIN

dbms_resource_manager_privs.grant_switch_consumer_group(

grantee_name => 'SCOTT',

consumer_group => 'OLTP',

grant_option => FALSE

);

END;

When we wish to draw your attention to a particular part of a code block, the relevant lines or items will be shown in bold:

ALPHA

=

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = alpha)(PORT = 1522))

(ADDRESS = (PROTOCOL = TCP)(HOST = alpha)(PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = alpha)

)

)

Any command-line input or output is written as follows:

orapki wallet create -wallet

New terms and important words are shown in bold. Words that you see on the screen, in menus or dialog boxes for example, appear in our text like this: From the main menu choose the Operations menu and then select the Add Certificate Request submenu, a form as shown in the following screenshot will be displayed where you can capture specific information..

Note

Warnings or important notes appear in a box like this.

Tip

Tips and tricks appear like this.

Reader feedback

Feedback from our readers is always welcome. Let us know what you think about this book—what you liked or may have disliked. Reader feedback is important for us to develop titles that you really get the most out of.

To send us general feedback, simply drop an email to <feedback@packtpub.com>, and mention the book title in the subject of your message.

If there is a book that you need and would like to see us publish, please send us a note in the SUGGEST A TITLE form on www.packtpub.com or email .

If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, see our author guide on www.packtpub.com/authors.

Customer support

Now that you are the proud owner of a Packt book, we have a number of things to help you to get the most from your purchase.

Downloading the example code for the book

Visit http://www.packtpub.com/files/code/6286_Code.zip to directly download the example code.

The downloadable files contain instructions on how to use them.

Errata

Although we have taken every care to ensure the accuracy of our contents, mistakes do happen. If you find a mistake in one of our books—maybe a mistake in text or code—we would be grateful if you would report this to us. By doing so, you can save other readers from frustration, and help us to improve subsequent versions of this book. If you find any errata, please report them by visiting http://www.packtpub.com/support, selecting your book, clicking on the let us know link, and entering the details of your errata. Once your errata are verified, your submission will be accepted and the errata added to any list of existing errata. Any existing errata can be viewed by selecting your title from http://www.packtpub.com/support.

Piracy

Piracy of copyright material on the Internet is an ongoing problem across all media. At Packt, we take the protection of our copyright and licenses very seriously. If you come across any illegal copies of our works in any form on the Internet, please provide us with the location address or website name immediately so that we can pursue a remedy.

Please contact us at <copyright@packtpub.com> with a link to the suspected pirated material.

We appreciate your help in protecting our authors, and our ability to bring you valuable content.

Questions

You can contact us at <questions@packtpub.com> if you are having a problem with any aspect of the book, and we will do our best to address it.

Chapter 1. Data Pump

Storage technology is improving day by day, and the more storage that becomes available at a low cost, the more data appears to fill it up. Managing high volumes of data becomes impractical if we take the traditional export/import approach, as this tool is very limited.