Oracle Application Express Administration: For DBAs and Developers

312 Pages·2016·24.52 MB·English

by  Francis Mignault

THE EXPERT’S VOICE® IN ORACLE
Oracle
Application
Express
Administration
For DBAs and Developers
—
Toward Making Oracle APEX Scalable
and Highly-Available
—
Francis Mignault
Luc Demanche
Oracle Application
Express Administration
For DBAs and Developers
Francis Mignault
Luc Demanche
Oracle Application Express Administration: For DBAs and Developers
Francis Mignault Luc Demanche
Insum Solutions, Montréal, Québec,
Montréal, Québec, Canada Canada
ISBN-13 (pbk): 978-1-4842-1957-7 ISBN-13 (electronic): 978-1-4842-1958-4
DOI 10.1007/978-1-4842-1958-4
Library of Congress Control Number: 2016955456
Copyright © 2016 by Francis Mignault and Luc Demanche
This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the
material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation,
broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage
and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or
hereafter developed.
Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with
every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an
editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark.
The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are
not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to
proprietary rights.
While the advice and information in this book are believed to be true and accurate at the date of publication,
neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or
omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material
contained herein.
Managing Director: Welmoed Spahr
Lead Editor: Jonathan Gennick
Development Editor: Laura Berendson
Technical Reviewer: Alex Fatkulin
Editorial Board: Steve Anglin, Pramila Balan, Laura Berendson, Aaron Black, Louise Corrigan,
Jonathan Gennick, Todd Green, Robert Hutchinson, Celestin Suresh John, Nikhil Karkal,
James Markham, Susan McDermott, Matthew Moodie, Natalie Pao, Gwenan Spearing
Coordinating Editor: Jill Balzano
Copy Editor: Brendan Frost
Compositor: SPi Global
Indexer: SPi Global
Artist: SPi Global
Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street,
6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax [phone], e-mail o [e-mail] ,
or visit w ww.springer.com . Apress Media, LLC is a California LLC and the sole member (owner) is Springer Science
+ Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation.
For information on translations, please e-mail [e-mail] , or visit w ww.apress.com .
Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use.
eBook versions and licenses are also available for most titles. For more information, reference our Special
Bulk Sales–eBook Licensing web page at w ww.apress.com/bulk-sales .
Any source code or other supplementary materials referenced by the author in this text are available to
readers at w ww.apress.com . For detailed information about how to locate your book’s source code, go to
w ww.apress.com/source-code/ . Readers can also access source code at SpringerLink in the Supplementary
Material section for each chapter.
Printed on acid-free paper
Contents at a Glance
About the Authors ....................................................................................................xv
About the Technical Reviewer ...............................................................................xvii
Acknowledgments. .................................................................................................xix
Introduction ............................................................................................................xxi
■ Part I: APEX Overview .........................................................................1
■ Chapter 1: About APEX ..........................................................................................3
■ Part II: Architecture ..........................................................................11
■ Chapter 2: APEX in the Database .........................................................................13
■ Chapter 3: Middle-Tier .........................................................................................27
■ Chapter 4: Database Advanced Security .............................................................37
■ Chapter 5: APEX and Oracle E-Business Suite .....................................................63
■ Chapter 6: High Availability .................................................................................71
■ Chapter 7: APEX in a Multitenant Database ........................................................93
■ Part III: Confi guration .....................................................................113
■ Chapter 8: Installing ..........................................................................................115
■ Chapter 9: Upgrading and Patching ...................................................................131
■ Chapter 10: Confi guring ....................................................................................143
■ Chapter 11: Administering APEX in a Runtime-Only Mode ................................175
■ Chapter 12: Database and APEX Backups .........................................................187
■ Chapter 13: Self-Provisioning Workspaces .......................................................209
iii
■ CONTENTS AT A GLANCE
■ Part IV: Tuning ................................................................................237
■ Chapter 14: Monitoring......................................................................................239
■ Chapter 15: Performance and Tuning ................................................................265
■ Chapter 16: Resource Manager and APEX .........................................................277
Index .....................................................................................................................303
iv
Contents
About the Authors ....................................................................................................xv
About the Technical Reviewer ...............................................................................xvii
Acknowledgments ..................................................................................................xix
Introduction ............................................................................................................xxi
■ Part I: APEX Overview .........................................................................1
■ Chapter 1: About APEX ..........................................................................................3
What Is APEX? ..................................................................................................................3
History ....................................................................................................................................................3
List of Key Features for Applications ......................................................................................................4
Builder Features .....................................................................................................................................5
Specifi c Characteristics ..........................................................................................................................7
Development / Deployment Options .......................................................................................................7
APEX Administration ...............................................................................................................................8
Why APEX? .......................................................................................................................8
What Is It For? ..................................................................................................................8
The APEX Community .......................................................................................................9
APEX.ORACLE.COM .................................................................................................................................9
OTN .........................................................................................................................................................9
APEX.WORLD ..........................................................................................................................................9
Oracle User Groups ...............................................................................................................................10
LinkedIn ................................................................................................................................................10
Meetups ................................................................................................................................................10
Twitter ...................................................................................................................................................10
Summary ........................................................................................................................10
v
■ CONTENTS
■ Part II: Architecture ..........................................................................11
■ Chapter 2: APEX in the Database .........................................................................13
Database Schemas .........................................................................................................13
APEX_PUBLIC_USER .............................................................................................................................13
FLOWS_FILES .......................................................................................................................................14
APEX_050000 .......................................................................................................................................15
APEX Views ...........................................................................................................................................14
Application Schemas ............................................................................................................................18
APEX Database Sessions ................................................................................................18
Application Execution ...........................................................................................................................18
Connection Pool ....................................................................................................................................19
v$session ..............................................................................................................................................19
APEX Workspaces ...........................................................................................................20
APEX Users ...........................................................................................................................................21
Application Users ..................................................................................................................................21
Scheduled Jobs ..............................................................................................................22
Oracle Support ...............................................................................................................22
How to Get Support ..............................................................................................................................22
APEX Oracle Lifetime Support ..............................................................................................................23
APEX in the Cloud ...........................................................................................................24
Microsoft Azure .....................................................................................................................................24
Amazon Web Services ..........................................................................................................................25
Oracle Cloud .........................................................................................................................................25
Summary ........................................................................................................................25
■ Chapter 3: Middle-Tier .........................................................................................27
Web Listeners .................................................................................................................27
EPG .......................................................................................................................................................28
OHS / mod_plsql (Deprecated) .............................................................................................................29
ORDS.....................................................................................................................................................29
vi
■ CONTENTS
Application Servers ........................................................................................................30
GlassFish ..............................................................................................................................................31
Tomcat ..................................................................................................................................................32
WebLogic ..............................................................................................................................................32
Reverse Proxy ................................................................................................................32
Architecture ..........................................................................................................................................32
Example of a Reverse Proxy Apache Confi guration ..............................................................................33
SSL / HTTPS ..........................................................................................................................................34
Summary ........................................................................................................................35
■ Chapter 4: Database Advanced Security .............................................................37
Real Application Security ...............................................................................................37
What Is Real Application Security .........................................................................................................37
Architecture of RAS ..............................................................................................................................38
Real Application Security Administration Application (RASADM) ..........................................................39
Example of a RAS Deployment .............................................................................................................39
Data Redaction ...............................................................................................................55
Example of a Data Redaction Deployment ............................................................................................55
Database Vault ...............................................................................................................57
Confi gure Database Vault for APEX .......................................................................................................58
Summary ........................................................................................................................61
■ Chapter 5: APEX and Oracle E-Business Suite .....................................................63
Architecture ....................................................................................................................63
APEX Schema vs. APPS Schema ....................................................................................64
EBS Patching vs. APEX Patching ....................................................................................67
Security ..........................................................................................................................67
Authentication ......................................................................................................................................68
Authorization ........................................................................................................................................68
Summary ........................................................................................................................69
vii
■ CONTENTS
■ Chapter 6: High Availability .................................................................................71
Overview of a High Availability Solution Using RAC .......................................................71
High Availability Solution for the Database ...........................................................................................71
High Availability Solution for the Application Server.............................................................................72
Usage of a Load Balancer .....................................................................................................................72
Deploying a High Availability Solution ............................................................................72
Deployment and Confi guration of RAC..................................................................................................72
Deployment and Confi guration of ORDS ...............................................................................................75
Deployment of the Load Balancer.........................................................................................................78
High Availability Scenarios .............................................................................................81
Loss of a Database Node ......................................................................................................................81
Loss of an Application Server Node ......................................................................................................84
Overview of a Solution Using Data Guard .......................................................................89
Data Guard as a Complement to RAC ...................................................................................................90
Summary ........................................................................................................................91
■ Chapter 7: APEX in a Multitenant Database ........................................................93
Default Installation of APEX in a Multitenant Database ..................................................93
Different Scenarios of APEX Deinstallation and Installation ...........................................94
Upgrade APEX in the CDB .....................................................................................................................94
Deinstallation of the Common APEX from the CDB ...............................................................................94
Installation of the Common APEX in the CDB ........................................................................................95
Installation of a Dedicated APEX in the PDB .........................................................................................96
Different Scenarios of Moving/Cloning a PDB ................................................................96
Source PDB Has APEX in the CDB .........................................................................................................96
Source PDB Has APEX in the PDB .......................................................................................................100
Source PDB Doesn’t Have APEX..........................................................................................................104
Move or Copy a PDB with Incompatible Versions of APEX ..................................................................105
Summary ......................................................................................................................111
viii
■ CONTENTS
■ Part III: Confi guration .....................................................................113
■ Chapter 8: Installing ..........................................................................................115
Preinstallation Steps ....................................................................................................115
Downloads ....................................................................................................................116
Download APEX ..................................................................................................................................116
Download ORDS ..................................................................................................................................118
Installing APEX in the Database ...................................................................................119
Tablespaces for APEX .........................................................................................................................119
Installing with DBCA ...........................................................................................................................120
Installing APEX Using the Downloaded Scripts ...................................................................................120
Postinstall ...........................................................................................................................................122
Installing ORDS .............................................................................................................125
Confi gure ORDS ..................................................................................................................................126
Deploying the War Files ......................................................................................................................128
Uninstall .......................................................................................................................129
Removing APEX ..................................................................................................................................129
1. Drop All APEX-Related Schemas .....................................................................................................130
2. Drop APEX Tablespaces ..................................................................................................................130
3. Undeploy ORDS ...............................................................................................................................130
Summary ......................................................................................................................130
■ Chapter 9: Upgrading and Patching ...................................................................131
Upgrading APEX ............................................................................................................131
Planning an Upgrade ..........................................................................................................................132
Preupgrade Tasks ...............................................................................................................................133
Upgrading APEX ..................................................................................................................................134
Postupgrade .......................................................................................................................................136
Patching .......................................................................................................................140
Planning a Patch .................................................................................................................................140
Downloading an APEX Patch ...............................................................................................................140
Installing an APEX Patch .....................................................................................................................141
Summary ......................................................................................................................141
ix