Oracle Applications DBA Field Guide

264 Pages·2006·2.122 MB·English

by  Elke Phelps, Paul Jackson

6447FM.qxd 3/6/06 5:04 PM Page i
Oracle Applications DBA
Field Guide
Elke Phelps
Paul Jackson
6447FM.qxd 3/6/06 5:04 PM Page ii
Oracle Applications DBA Field Guide
Copyright © 2006 by Elke Phelps and Paul Jackson
All rights reserved. No part of this work may be reproduced or transmitted in any form
or by any means, electronic or mechanical, including photocopying, recording, or by
any information storage or retrieval system, without the prior written permission of
the copyright owner and the publisher.
ISBN-13 (pbk): 978-1-59059-644-9
ISBN-10 (pbk): 1-59059-644-7
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names may appear in this book. Rather than use a trademark symbol
with every occurrence of a trademarked name, we use the names only in an editorial
fashion and to the benefit of the trademark owner, with no intention of infringement
of the trademark.
Lead Editor: Jonathan Hassell
Technical Reviewers: Srini Chavali, Sandra Vucinic
Editorial Board: Steve Anglin, Dan Appleman, Ewan Buckingham, Gary Cornell,
Jason Gilmore, Jonathan Hassell, James Huddleston, Chris Mills, Matthew Moodie,
Dominic Shakeshaft, Jim Sumser, Matt Wade
Project Manager: Elizabeth Seymour
Copy Edit Manager: Nicole LeClerc
Copy Editor: Andy Carroll
Assistant Production Director: Kari Brooks-Copony
Production Editor: Laura Cheu
Compositor: Dina Quan
Proofreader: Linda Seifert
Indexer: Carol Burbo
Artist: Kinetic Publishing Services, LLC
Photographer: Chris Fieldhouse
Cover Designer: Kurt Krames
Manufacturing Director: Tom Debolski
Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring
Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax [phone],
e-mail [email protected], or visit http://www.springeronline.com.
For information on translations, please contact Apress directly at 2560 Ninth Street,
Suite 219, Berkeley, CA 94710. Phone [phone], fax [phone], e-mail
[email protected], or visit http://www.apress.com.
The information in this book is distributed on an “as is” basis, without warranty.
Although every precaution has been taken in the preparation of this work, neither the
author(s) nor Apress shall have any liability to any person or entity with respect to any
loss or damage caused or alleged to be caused directly or indirectly by the information
contained in this work.
6447FM.qxd 3/6/06 5:04 PM Page iii
Contents at a Glance
About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
About the Technical Reviewers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
■ CHAPTER 1 Components and Architecture of Oracle Applications . . . . . 1
■ CHAPTER 2 Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
■ CHAPTER 3 Monitoring and Troubleshooting . . . . . . . . . . . . . . . . . 51
■ CHAPTER 4 Performance Tuning . . . . . . . . . . . . . . . . . . . . . . . . 95
■ CHAPTER 5 Patching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
■ CHAPTER 6 Toolkit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
■ CHAPTER 7 Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201
■ INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
iii
6447FM.qxd 3/6/06 5:04 PM Page iv
6447FM.qxd 3/6/06 5:04 PM Page v
Contents
About the Authors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xi
About the Technical Reviewers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xiii
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
■ CHAPTER 1 Components and Architecture of Oracle Applications . . . . . 1
Servicing User Requests—Oracle Applications Components . . . . . . . . 2
Client . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Web Node . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Forms Node . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
Concurrent Processing Node . . . . . . . . . . . . . . . . . . . . . 3
Admin Node . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Database Node . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
Oracle Applications Architecture . . . . . . . . . . . . . . . . . . . . . . 4
Fundamental Architecture . . . . . . . . . . . . . . . . . . . . . . 4
Advanced Architecture . . . . . . . . . . . . . . . . . . . . . . . . 5
Architecture Best Practices . . . . . . . . . . . . . . . . . . . . . . . . 9
■ CHAPTER 2 Configuration . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
The Application Context File . . . . . . . . . . . . . . . . . . . . . . . 13
Locating and Creating the Application Context File . . . . . . . . . 13
Modifying the Application Context File . . . . . . . . . . . . . . . 13
Creating a Port Numbering Convention . . . . . . . . . . . . . . . 15
Identifying Nodes with Context Parameters . . . . . . . . . . . . . 17
Using AD Configuration . . . . . . . . . . . . . . . . . . . . . . . . . 18
Executing AD Configuration . . . . . . . . . . . . . . . . . . . . . 18
Reviewing adconfig.sh Log Files . . . . . . . . . . . . . . . . . . 19
Reviewing adconfig.sh Execution Changes . . . . . . . . . . . . . 19
Location of adconfig.sh Backup Files . . . . . . . . . . . . . . . . 20
Adding Customizations to the Application Configuration Files . . . . 20
Web Node Configuration . . . . . . . . . . . . . . . . . . . . . . . . . 22
Apache Configuration Files . . . . . . . . . . . . . . . . . . . . . 22
JServ Configuration Files . . . . . . . . . . . . . . . . . . . . . . 23
Forms Node Configuration . . . . . . . . . . . . . . . . . . . . . . . . 25
Basic Configuration . . . . . . . . . . . . . . . . . . . . . . . . . 25
Forms Metric Server and Forms Metric Client . . . . . . . . . . . . 26
v
6447FM.qxd 3/6/06 5:04 PM Page vi
vi ■ CONTENTS
Concurrent Processing Node Configuration . . . . . . . . . . . . . . . . 27
Basic Configuration . . . . . . . . . . . . . . . . . . . . . . . . . 27
Configuring the Report Review Agent (FNDFS) . . . . . . . . . . . . 28
Admin Node Configuration . . . . . . . . . . . . . . . . . . . . . . . . 29
Application Environment Files . . . . . . . . . . . . . . . . . . . . 29
Administering the identity.obj File . . . . . . . . . . . . . . . . . . 30
Administering the Database Connection File . . . . . . . . . . . . 30
Configuring Generic Service Management . . . . . . . . . . . . . 31
Additional Service Components . . . . . . . . . . . . . . . . . . . . . 32
TCF Socket . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
Discoverer Server . . . . . . . . . . . . . . . . . . . . . . . . . 33
Fulfillment Server . . . . . . . . . . . . . . . . . . . . . . . . . 33
Database Node Configuration . . . . . . . . . . . . . . . . . . . . . . 34
Database Initialization File . . . . . . . . . . . . . . . . . . . . . 34
Reviewing Database Initialization Parameters . . . . . . . . . . . . 39
Database Network Configuration . . . . . . . . . . . . . . . . . . 40
Oracle Applications Tablespace Model . . . . . . . . . . . . . . . 42
Creating Custom Database Objects . . . . . . . . . . . . . . . . . 43
Additional Configuration Topics . . . . . . . . . . . . . . . . . . . . . . 44
Configuration Wizards . . . . . . . . . . . . . . . . . . . . . . . 44
License Manager . . . . . . . . . . . . . . . . . . . . . . . . . . 45
Configuring Password Security . . . . . . . . . . . . . . . . . . . 46
Miscellaneous Configuration . . . . . . . . . . . . . . . . . . . . 48
Configuration Best Practices . . . . . . . . . . . . . . . . . . . . . . . 50
■ CHAPTER 3 Monitoring and Troubleshooting . . . . . . . . . . . . . . . . . 51
Methods for Monitoring . . . . . . . . . . . . . . . . . . . . . . . . . 52
Database Monitoring and Troubleshooting . . . . . . . . . . . . . . . . 57
Database Log Files . . . . . . . . . . . . . . . . . . . . . . . . 57
Database Availability . . . . . . . . . . . . . . . . . . . . . . . . 59
Session Monitoring . . . . . . . . . . . . . . . . . . . . . . . . . 60
Storage Monitoring . . . . . . . . . . . . . . . . . . . . . . . . . 68
Apache Server Monitoring and Troubleshooting . . . . . . . . . . . . . . 74
Apache Log Files . . . . . . . . . . . . . . . . . . . . . . . . . . 74
Apache Availability . . . . . . . . . . . . . . . . . . . . . . . . . 75
Troubleshooting iAS Configuration . . . . . . . . . . . . . . . . . 76
Testing Java Servlet Configuration . . . . . . . . . . . . . . . . . 78
Monitoring the JVM Pool . . . . . . . . . . . . . . . . . . . . . . 78
Forms Monitoring and Troubleshooting . . . . . . . . . . . . . . . . . . 78
Using OAM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
Monitoring Forms Dump Files . . . . . . . . . . . . . . . . . . . . 80
Concurrent Manager Monitoring . . . . . . . . . . . . . . . . . . . . . 80
Monitoring Concurrent Manager Log Files . . . . . . . . . . . . . . 81
Reviewing Active Concurrent Requests . . . . . . . . . . . . . . . 81
Monitoring Pending Concurrent Requests . . . . . . . . . . . . . . 81
Canceling Active Concurrent Requests . . . . . . . . . . . . . . . 82
Monitoring Concurrent Request Run Times . . . . . . . . . . . . . 83
6447FM.qxd 3/6/06 5:04 PM Page vii
■ CONTENTS vii
Server Monitoring and Troubleshooting . . . . . . . . . . . . . . . . . . 84
Server Availability . . . . . . . . . . . . . . . . . . . . . . . . . 84
Server CPU Utilization . . . . . . . . . . . . . . . . . . . . . . . 85
Server Memory Utilization . . . . . . . . . . . . . . . . . . . . . 86
Server Filesystem Usage . . . . . . . . . . . . . . . . . . . . . . 86
Network Monitoring . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
Using ping and tracert for Network Monitoring . . . . . . . . . . . 88
Performing a Network Test from the Application . . . . . . . . . . . 90
Additional Monitoring and Troubleshooting Topics . . . . . . . . . . . . . 90
Monitoring Profile Changes . . . . . . . . . . . . . . . . . . . . . 91
Monitoring System Backups . . . . . . . . . . . . . . . . . . . . 92
Resolving JInitiator Issues . . . . . . . . . . . . . . . . . . . . . 92
Monitoring and Troubleshooting Best Practices . . . . . . . . . . . . . . 94
■ CHAPTER 4 Performance Tuning . . . . . . . . . . . . . . . . . . . . . . . . 95
Performance Tuning Process . . . . . . . . . . . . . . . . . . . . . . . 96
Identifying Performance Issues . . . . . . . . . . . . . . . . . . . 96
Developing an Action Plan . . . . . . . . . . . . . . . . . . . . . 98
Tools for Resolving Performance Issues . . . . . . . . . . . . . . . . . 98
Tuning the Database . . . . . . . . . . . . . . . . . . . . . . . . 99
Tuning the Server . . . . . . . . . . . . . . . . . . . . . . . . . 108
Tuning the Application Tier . . . . . . . . . . . . . . . . . . . . 113
User Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Trace Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
Additional Tuning Considerations . . . . . . . . . . . . . . . . . 127
Common Performance Issues . . . . . . . . . . . . . . . . . . . 128
Performance Tuning Best Practices . . . . . . . . . . . . . . . . . . . 128
■ CHAPTER 5 Patching . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 131
Applications Patching . . . . . . . . . . . . . . . . . . . . . . . . . . 132
Types of Application Patches . . . . . . . . . . . . . . . . . . . 132
Preparing to Patch . . . . . . . . . . . . . . . . . . . . . . . . 133
Applying Patches . . . . . . . . . . . . . . . . . . . . . . . . . 141
Monitoring and Resolving Patching Problems . . . . . . . . . . . 149
Post-Patching Steps . . . . . . . . . . . . . . . . . . . . . . . . 152
Patching Cleanup . . . . . . . . . . . . . . . . . . . . . . . . . 154
Database Patching . . . . . . . . . . . . . . . . . . . . . . . . . . . 154
Patching Best Practices . . . . . . . . . . . . . . . . . . . . . . . . . 156
■ CHAPTER 6 Toolkit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 157
Oracle Applications Utilities and Commands . . . . . . . . . . . . . . . 158
Application Component Startup and Shutdown Scripts . . . . . . . 158
Changing Application,Oracle,and the APPLSYS/APPS
Passwords . . . . . . . . . . . . . . . . . . . . . . . . . . . 160
Relinking Application Executables . . . . . . . . . . . . . . . . . 163
Regenerating Forms,Libraries,and Menus . . . . . . . . . . . . 165
Recompiling JSP Pages . . . . . . . . . . . . . . . . . . . . . . 167
6447FM.qxd 3/6/06 5:04 PM Page viii
viii ■ CONTENTS
Determining Component Versions . . . . . . . . . . . . . . . . . . . . 168
Identifying Application File Versions . . . . . . . . . . . . . . . . 169
JInitiator Version . . . . . . . . . . . . . . . . . . . . . . . . . 170
Apache Version and Rollup Patch . . . . . . . . . . . . . . . . . 171
Forms and PL/SQL Version . . . . . . . . . . . . . . . . . . . . 171
Oracle Applications Framework Version . . . . . . . . . . . . . . 173
OJSP Version . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
JDK Version . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174
Java Class File Versions . . . . . . . . . . . . . . . . . . . . . . 175
Database Version Query . . . . . . . . . . . . . . . . . . . . . . 176
Additional Commands and Usage Tips . . . . . . . . . . . . . . . . . 177
General Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
Using chown . . . . . . . . . . . . . . . . . . . . . . . . . . . 178
Using chmod . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
Using kill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181
Using find . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
Using df . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182
Using du . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
Using tar . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
Finding and Removing Memory Segments and Semaphores . . . . 184
Finding and Removing Print Jobs . . . . . . . . . . . . . . . . . 186
Removing Database Sessions . . . . . . . . . . . . . . . . . . . 187
Scheduling Scripts . . . . . . . . . . . . . . . . . . . . . . . . . . . 188
Using crontab . . . . . . . . . . . . . . . . . . . . . . . . . . . 188
Scheduling and Monitoring Tools . . . . . . . . . . . . . . . . . 189
Preventative Maintenance . . . . . . . . . . . . . . . . . . . . . . . 189
Gathering Statistics . . . . . . . . . . . . . . . . . . . . . . . . 190
Recompiling Invalid Objects . . . . . . . . . . . . . . . . . . . . 192
Rotating Log Files . . . . . . . . . . . . . . . . . . . . . . . . . 193
Rebuilding Indexes . . . . . . . . . . . . . . . . . . . . . . . . 195
Stopping and Restarting the Apache Server . . . . . . . . . . . . 196
Purging Concurrent Requests . . . . . . . . . . . . . . . . . . . 196
Purging Workflow History . . . . . . . . . . . . . . . . . . . . . 197
Purging and Archiving Data . . . . . . . . . . . . . . . . . . . . 197
Application Instance Clones . . . . . . . . . . . . . . . . . . . . . . . 198
Using Rapid Clone . . . . . . . . . . . . . . . . . . . . . . . . 198
Cloning Cleanup . . . . . . . . . . . . . . . . . . . . . . . . . 199
■ CHAPTER 7 Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 201
Working with Oracle Support . . . . . . . . . . . . . . . . . . . . . . 201
MetaLink Search Techniques . . . . . . . . . . . . . . . . . . . 202
Oracle Support Service Requests . . . . . . . . . . . . . . . . . 206
User Communities and Conferences . . . . . . . . . . . . . . . . . . 212
User Groups . . . . . . . . . . . . . . . . . . . . . . . . . . . 212
Conferences . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
6447FM.qxd 3/6/06 5:04 PM Page ix
■ CONTENTS ix
Online Resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214
Oracle Web Site . . . . . . . . . . . . . . . . . . . . . . . . . . 214
Additional Community Resources . . . . . . . . . . . . . . . . . 214
Books and Periodicals . . . . . . . . . . . . . . . . . . . . . . . . . 215
Books . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
Periodicals . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
Final Thoughts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
■ INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219