Where do we start? One of the problems in comprehending a massive product such as the Oracle Database is getting a good sense of how the product works without getting lost in the details. This book aims to provide a thorough grounding in the concepts and technologies that form the foundation of Oracle’s Database Server, currently known as Oracle Database 12c. The book is intended for a wide range of Oracle Database administrators, developers, and users, from the novice to the experienced. It is our hope that once you have this basic understanding of the product, you’ll be able to connect the dots when using Oracle’s voluminous feature set, documentation, and the many other books and publications that describe the database.
Oracle also offers an Application Server and Fusion Middleware, business intelligence tools, and business applications (the E-Business Suite, PeopleSoft, JD Edwards, Siebel, Hyperion, and Fusion, among others). Since this book is focused on the database, we will only touch on these other software products as they relate to specific Oracle Database topics covered.
This first chapter lays the groundwork for the rest of the book. Of all the chapters, it covers the broadest range of topics. Most of these topics are discussed later in more depth, but some of the basics—for example, the brief history of Oracle and the contents of the different “flavors” of the Oracle Database products—are unique to this chapter.
Over the past 30-plus years, Oracle grew from being one of many vendors that developed and sold a database product to being widely recognized as the database market leader. Although early products were typical of a startup company, the Oracle Database grew such that its technical capabilities are now often viewed as the most advanced in the industry. With each database release, Oracle has improved the scalability, functionality, and manageability of the database.
This book is now in its fifth edition. This edition, like earlier editions, required many changes, since the database has changed a great deal over this time. Highlights of Oracle releases covered in the many editions of this book include:
Oracle8 (released in 1997) improved the performance and scalability of the database and added the ability to create and store objects in the database.
Oracle8i (released in 1999) added a new twist to the Oracle Database—a combination of enhancements that made the Oracle8i Database a focal point in the world of Internet computing.
Oracle9i (released in 2001) introduced Real Application Clusters as a replacement for Oracle Parallel Server and added many management and data warehousing features.
Oracle Database 10g (released in 2003) enabled deployment of “grid” computing. A grid is simply a pool of computers and software resources providing resources for applications on an as-needed basis. To support this style of computing, Oracle added the ability to provision CPUs and data. Oracle Database 10g also further reduced the time, cost, and complexity of database management through the introduction of self-managing features such as the Automated Database Diagnostic Monitor, Automated Shared Memory Tuning, Automated Storage Management, and Automated Disk Based Backup and Recovery.
Oracle Database 11g (released in 2007) highlighted improvement in self-tuning and managing capabilities, especially in the areas of Automatic Memory Management, partitioning, and security. The lifecycle of database change management was extended within Oracle’s Enterprise Manager with improved diagnosis capabilities and linkage to Oracle Support via a Support Workbench. This version also featured improved online patching capabilities. In 2008, Oracle announced that its first engineered system, the Oracle Exadata Database Machine, would support Oracle Database 11g Enterprise Edition.
Oracle Database 12c (released in 2013) introduces a number of deployment, manageability, and rapid provisioning features especially useful in private and public cloud computing environments where hardware infrastructure and the database are delivered as a service over a network, building upon capabilities introduced in previous releases. Typically, many databases are deployed and managed using this model, so Oracle introduced a capability in the database to share services by defining multitenant container and pluggable databases.
Before we dive into further details, let’s step back and look at how databases evolved, how we arrived at the relational model, and Oracle’s history. We’ll then take an initial look at Oracle Database packaging and key Oracle features today.
The relational database concept was described first by Dr. Edgar F. Codd in an IBM research publication entitled “System R4 Relational” that was published in 1970. Initially, it was unclear whether any system based on this concept could achieve commercial success. Nevertheless, a company named Software Development Laboratories Relational Software came into being in 1977 and released a product named Oracle V.2 as the world’s first commercial relational database within a couple of years (also changing its name to Relational Software, Incorporated). By 1985, Oracle could claim more than 1,000 relational database customer sites. Curiously, IBM would not embrace relational technology in a commercial product until the Query Management Facility in 1983.
Why did relational database technology grow to become the de facto database technology? A look back at previous database technology may help to explain this phenomenon.
Database management systems were first defined in the 1960s to provide a common organizational framework for data formerly stored in independent files. In 1964, Charles Bachman of General Electric proposed a network model with data records linked together, forming intersecting sets of data, as shown on the left in Figure 1-1. This work formed the basis of the CODASYL Data Base Task Group. Meanwhile, the North American Aviation’s Space Division and IBM developed a second approach based on a hierarchical model in 1965. In this model, data is represented as tree structures in a hierarchy of records, as shown on the right in Figure 1-1. IBM’s product based on this model was brought to market in 1969 as the Information Management System (IMS). As recently as 1980, almost all database implementations used either the network or hierarchical approach. Although several competitors sold similar technologies around 1980, only IMS could still be found in many large organizations just 20 years later.
The relational database uses the concept of linked two-dimensional tables consisting of rows and columns, as shown in Figure 1-2. Unlike the hierarchical approach, no predetermined relationship exists between distinct tables. This means that data needed to link together the different areas of the network or hierarchical model need not be defined. Because relational users don’t need to understand the representation of data in storage to retrieve it (many such business users create ad hoc queries), ease of access combined with improved flexibility to change data models to adapt to changing business needs and helped popularize the relational model.
Relational programming is nonprocedural and operates on a set of rows at a time. In a master-detail relationship between tables, there can be one or many detail rows for each individual master row, yet the statements used to access, insert, or modify the data simply describe the set of results. In many early relational databases, data access required the use of procedural languages that worked one record at a time. Because of this set orientation, programs access more than one record in a relational database more easily. Relational databases can be used more productively to extract value from large groups of data.
The contents of the rows in Figure 1-2 are sometimes referred to as records. A column within a row is referred to as a field. Tables are stored in a database schema, which is a logical organizational unit within the database. Other logical structures in the schema often include the following:
The relationships between columns in different tables are typically described through the use of keys, which are implemented through referential integrity constraints and their supporting indexes. For example, in Figure 1-2, you can establish a link between the DEPTNO column in the second table, which is called a foreign key, to the DEPTNO column in the first table, which is referred to as the primary key of that table.
Finally, even if you define many different indexes for a table, you don’t have to understand them or manage the data they contain. Relational databases include a query optimizer that chooses whether to use indexes, and the best way to use those indexes, to access the data for any particular query.
The relational approach lent itself to the Structured Query Language (SQL). SQL was initially defined over a period of years by IBM Research, but it was Oracle Corporation that first introduced it to the market in 1979. SQL was noteworthy at the time for being the only language needed for relational databases since you could use SQL:
Today, SQL contains many extensions and follows ANSI/ISO standards that define its basic syntax.
In 1983, Relational Software Incorporated was renamed Oracle Corporation to avoid confusion with a competitor named Relational Technologies Incorporated. At this time, the developers made a critical decision to create a portable version of Oracle written in C (version 3) that ran not only on Digital VAX/VMS systems, but also on Unix and other platforms. By 1985, Oracle claimed the ability to run on more than 30 platforms. Some of these platforms are historical curiosities today, but others remain in use. (In addition to VMS, early operating systems supported by Oracle included IBM MVS, HP/UX, IBM AIX, and Sun’s Solaris version of Unix.) Oracle was able to leverage and helped accelerate the growth in popularity of minicomputers and Unix servers in the 1980s. Today, this portability also includes releases for operating systems such as Microsoft Windows and Linux.
In addition to multiple platform support, other core Oracle messages from the mid-1980s still ring true today, including complementary software development and decision support (business intelligence) tools, ANSI standard SQL across platforms, and connectivity over standard networks. Since the mid-1980s, the database deployment model has evolved from single database and application servers to client/server, then to Internet computing implemented using browser-based clients accessing database applications, and now to private and public cloud deployment where the Oracle Database might be deployed as a service (DBaaS) or used as the foundation for a Cloud Platform as a Service (PaaS).
Oracle introduced many innovative technical features to the database as computing and deployment models changed (from offering the first distributed database to supporting the first Java Virtual Machine in the core database engine to enabling grid computing and providing needed services for public and private Cloud deployment). Oracle offered support for emerging standards such as XML, important in deploying a Service-Oriented Architecture (SOA). Table 1-1 presents a short list of Oracle’s major product introductions.
Table 1-1. History of Oracle introductions
Oracle Database 12c is the most recent version of the Oracle Relational Database Management System (RDBMS) family of products that share common source code. The family of database products includes:
- Oracle Enterprise Edition
Flagship database product and main topic of this book, aimed at large-scale implementations that require Oracle’s full suite of database features and options. For advanced security, only the Enterprise Edition features Virtual Private Database (VPD) support, Fine-Grained Auditing, and options including the Database Vault, Advanced Security, and Label Security. Data warehousing features only in Enterprise Edition include compression of repeating stored data values, cross-platform transportable tablespaces, Information Lifecycle Management (ILM), materialized views query rewrite, and the Partitioning, OLAP, and Advanced Analytics Options. High-availability features unique to the Enterprise Edition include Data Guard and Flashback Database, Flashback Table, and Flashback Transaction Query. The Enterprise Edition is the database version supported on Oracle’s engineered systems.
- Oracle Standard Edition
Oracle’s database intended for small- and medium-sized implementations. This database can be deployed onto server configurations containing up to 4 CPUs on a single system or on a cluster using Real Application Clusters (RAC).
- Oracle Standard Edition One
Designed for small implementations, this database can support up to 2 CPUs and does not support RAC. The feature list is otherwise similar to Oracle Standard Edition.
- Oracle Personal Edition
Database used by single developers to develop code for implementation on Oracle multiuser databases. It requires a license, unlike Express Edition, but gives you the full Enterprise Edition set of functionality.
- Oracle Express Edition
Entry-level database from Oracle available at no charge for Windows and Linux and unsupported as a product, this database is limited to 1 GB of memory and 4 GB of disk. It provides a subset of the functionality in Standard Edition One, lacking features such as a Java Virtual Machine, server-managed backup and recovery, and Automatic Storage Management. Although this database is not manageable by Oracle Enterprise Manager, you can deploy it for and manage multiple users through the Oracle Application Express administration interface.
Oracle releases new versions of the flagship database every three to five years. New releases typically follow themes and introduce a significant number of new features. In recent releases, these themes are indicated in the product version naming. In 1998, Oracle announced Oracle8i, with the “i” added to denote new functionality supporting Internet deployment. Oracle9i continued using this theme. In 2003, Oracle announced Oracle Database 10g, with the “g” denoting Oracle’s focus on emerging grid computing deployment models, then announced Database 11g with further improvements in manageability in 2007. In 2012, Oracle announced Oracle Database 12c, the “c” denoting new database functionality supporting Cloud deployment. In between major versions, Oracle issues point releases that also add features but are more typically focused on improvements to earlier capabilities.
The terms “Oracle,” “Oracle Database,” “database,” “Oracle8,” “Oracle8i,” “Oracle9i,” “Oracle Database 10g,” “Oracle Database 11g,” and “Oracle Database 12c” might appear to be used somewhat interchangeably in this book because Oracle Database 12c includes all the features of previous versions. When we describe a new feature that was first made available specifically in a certain release, we’ve tried to note that fact to avoid confusion, recognizing that many of you maintain older releases of Oracle. We typically use the simple terms “Oracle” and “database” when describing features that are common to all these releases.
Oracle Development has developed releases using a single source code model for the core family of database products since 1983. While each database implementation includes some operating-system-specific source code at very low levels in order to better leverage specific platforms, the interfaces that users, developers, and administrators deal with for each version are consistent. This development strategy enables Oracle to focus on implementing new features only once across its product set.
The introduction of Oracle’s engineered systems, Exadata storage, and the Exadata Storage Server software enabled Oracle to optimize the database for specific hardware server and storage configurations. Today, Oracle offers a family of engineered systems capable of running the Oracle Database. The Oracle Exadata Database Machine was the most popular engineered system as this edition of the book was published. The Oracle SuperCluster is a general purpose platform also featuring Exadata storage (and some general purpose storage) and is designed to run both the Oracle Database and Oracle Fusion Middleware. The Oracle Database Appliance is a two-node configuration designed to be a smaller departmental server. The Exalogic Elastic Cloud system is optimally designed to run Oracle’s Fusion Middleware and often is deployed as a middle-tier server in front of Exadata, but occasionally is deployed with the Oracle Database also running on it. Neither the Oracle Database Appliance nor Exalogic Elastic Cloud support Exadata storage.
The Oracle Database is a broad and deep product. To give some initial perspective, we begin describing Oracle with a high-level overview of the basic areas of functionality. By the end of this portion of the chapter, you will have orientation points to guide you in exploring the topics in the rest of this book.
To give some structure to the broad spectrum of the Oracle Database, we’ve organized our initial discussion of these features and complementary software components into the following sections:
Database application development features
Database connection features
The role of Oracle Fusion Middleware
Distributed database features
Data movement features
Database performance features
Managing the Oracle Database
Database security features
Database development tools
Note
In this chapter, we’ve included a lot of terminology and rather abbreviated descriptions of features. Oracle is a huge system. Our goal here is to quickly familiarize you with the full range of features in the system and introduce the concepts we are covering in this book. Subsequent chapters will provide additional details about these features and concepts. Obviously, though, whole books have been written about some of the feature areas summarized here, so this book is often used as a starting point in understanding where to go next.
The Oracle Database is typically used to store and retrieve data through applications. The features of the Oracle Database and related products described in this section are used to create applications. We’ve divided the discussion in the following subsections into database programming and database extensibility options. Later in this chapter, we will describe Oracle’s development tools and Oracle’s other embedded database products that meet unique applications deployment needs.
All flavors of the Oracle Database include languages and interfaces that enable programmers to access and manipulate the data in the database. Database programming features usually interest developers who are creating Oracle-based applications to be sold commercially, or IT organizations building applications unique to their businesses. Data in Oracle can be accessed using SQL, SQL/XML, XQuery, and WebDAV. Programs deployed within the database can be written in PL/SQL and Java.
The ANSI standard Structured Query Language (SQL) provides basic functions for data manipulation, transaction control, and record retrieval from the database. Most business users of the database interact with Oracle through applications or business intelligence tools that provide interfaces hiding the underlying SQL and its complexity.
Oracle’s PL/SQL, a procedural language extension to SQL, is commonly used to implement program logic modules for applications. PL/SQL can be used to build stored procedures and triggers, looping controls, conditional statements, and error handling. You can compile and store PL/SQL procedures in the database. You can also execute PL/SQL blocks via SQL*Plus, an interactive tool provided with all versions of Oracle. PL/SQL program units can be precompiled. Additionally, Oracle supplies a lot of additional functionality using PL/SQL programs included with the database, referred to as packages, which can be called from standard PL/SQL code.
Oracle8i introduced the use of Java as a procedural language and a Java Virtual Machine (JVM) in the database (originally called JServer). The JVM includes support for Java stored procedures, methods, triggers, Enterprise Java Beans (EJBs), CORBA, IIOP, and HTTP.
The inclusion of Java within the Oracle Database allows Java developers to leverage their skills as Oracle applications developers. Java applications can be deployed in the client, Application Server, or database, depending on what is most appropriate. Current Oracle Database versions include a just-in-time Java compiler that is enabled by default. The importance of Java to Oracle is illustrated by the acquisition of Sun by Oracle in 2010 and continued development efforts around Java since.
As of Oracle Database 11g, the Database can serve as a Web Services provider implemented through XML DB in the database. Web services enable SQL or XQuery to submit queries and receive results as XML, or invoke PL/SQL functions or package functions and to receive results. XQuery provides support for the JSR standard and is further optimized for performance in the Oracle Database. As of Oracle Database 12c, XQuery updates are supported. You can also define RESTful Web Services to access both SQL and PL/SQL in an Oracle Database through the APEX Listener, described in Chapter 15.
The Oracle Database has been able to store large objects since Oracle8 added the capability to store multiple LOB columns in each table. Oracle Database 10g essentially removed the space limitation on large objects. Oracle Database 11g greatly improved the performance of query and insert operations when used with LOBs through the introduction of SecureFiles. SecureFiles serve as a place to securely store LOBs in the Oracle Database instead of in filesystems while delivering performance similar to that experienced when LOBs are stored in filesystems. Transparent Data Encryption, a security feature described below and later in the book, is supported for SecureFiles LOB data.
Support of object structures has existed since Oracle8i to provide support for an object-oriented approach to programming. For example, programmers can create user-defined data types, complete with their own methods and attributes. Oracle’s object support includes a feature called Object Views through which object-oriented programs can make use of relational data already stored in the database. You can also store objects in the database as varying arrays (VARRAYs), nested tables, or index-organized tables (IOTs).
Programmers can interact with the Oracle Database from C, C++, Java, or COBOL by embedding SQL in those applications. Prior to compiling the applications using a platform’s native compilers, you must run the embedded SQL code through a precompiler. The precompiler replaces SQL statements with library calls the native compiler can accept. Oracle provides support for this capability through optional “programmer” precompilers for C and C++ using Pro*C and for COBOL using Pro*COBOL. In recent Oracle versions, Oracle features SQLJ, a precompiler for Java that replaces SQL statements embedded in Java with calls to a SQLJ runtime library, also written in Java.
All versions of Oracle include database drivers that allow applications to access Oracle via ODBC (the Open Database Connectivity standard) or JDBC (the Java Database Connectivity open standard). Also available are Oracle Data Access Connectors (ODAC) for .NET. ODAC provides a data provider for .NET, providers for ASP.NET and .NET stored procedures, and tools for developers using Visual Studio.
If you’re an experienced programmer seeking optimum performance or a finer level of control, you may choose to define SQL statements within host-language character strings and then explicitly parse the statements, bind variables for them, and execute them using the Oracle Call Interface (OCI). OCI is a much more detailed interface that requires more programmer time and effort to create and debug. Developing an application that uses OCI can be time-consuming, but the added functionality and incremental performance gains could make spending the extra time worthwhile.
In certain programming scenarios, OCI improves application performance or adds functionality. For instance, in high-availability implementations in which multiple systems share disks using Real Application Clusters, you could write programs using OCI that allow users to reattach to a second server transparently if the first fails. As of Oracle Database 12c, the Transaction Guard API to the database can be used in order to preserve guaranteed commits where data is accessed via OCI (or alternatively via JDBC thin drivers, OOCI, or ODP.NET).
National Language Support (NLS) provides character sets and associated functionality, such as date and numeric formats, for a variety of languages. The initial release of Oracle Database 12c features Unicode 6.1 support. All data may be stored as Unicode, or select columns may be incrementally stored as Unicode. UTF-8 encoding and UTF-16 encoding provide support for more than 57 languages and 200 character sets. Extensive localization is provided (for example, for data formats), and customized localization can be added through the Oracle Locale Builder. Oracle includes a Globalization Toolkit for creating applications that will be used in multiple languages.
The Internet and corporate intranets have created a growing demand for storage and manipulation of nontraditional data types within the database. There is a need for extensions to the standard functionality of a database for storing and manipulating image, audio, video, spatial, and time series information. These capabilities are enabled through extensions to standard SQL.
Oracle Multimedia (formerly interMedia) provides text manipulation and additional image, audio, video, and locator functions in the database. Oracle Multimedia offers the following major capabilities:
The text portion of Multimedia (Oracle Text) can identify the gist of a document by searching for themes and key phrases within the document.
The image portion of Multimedia can store and retrieve images of various formats; since Oracle Database 11g, DICOM medical images are supported in the database.
The audio and video portions of Multimedia can store and retrieve audio and video clips, respectively.
The locator portion of Multimedia can retrieve data that includes spatial coordinate information.
The Spatial and Graph Option is available for the Oracle Enterprise Edition. This option can be used to optimize the display and retrieval of data linked to coordinates, determine distance, and compute other geometric values such as area. It is often is used in the development of spatial information systems by vendors of Geographic Information Systems (GIS) products. Oracle Database 12c added support of named graphs in the database as defined by the Worldwide Web Consortium (W3C) in its Resource Description Framework (RDF).
Oracle added native XML data type support to the Oracle9i Database with XML and SQL interchangeability for searching. The structured XML object is held natively in object relational storage, meeting the W3C DOM specification. XML standards supported include XML Schema, XPath (syntax for searching in SQL), XQuery, XSLT, and DOM. XMLIndex can be used with all forms of XML data in the database. As of Oracle Database 12c, XML DB is a mandatory part of the database and cannot be uninstalled.
The connection between the client and the database server is a key component of the overall architecture. The database connection is responsible for supporting all communications between an application and the data it uses. Database users connect to the database by establishing a network connection. You can also link database servers via network connections. Oracle provides a number of features to establish connections between users and the database and/or between database servers, as described in the following subsections.
Oracle’s Net Services provide the interface between networks and distributed Oracle Databases establishing database sessions for purposes of distributed processing. You can use Oracle Net Services over a wide variety of network protocols including TCP/IP, HTTP, FTP, and WebDAV. The Services include Oracle Net, used to establish sessions, and the Oracle Database Server Listener. Client requests can be handled through dedicated or shared servers.
The Oracle Internet Directory (OID) is an LDAP (Lightweight Directory Access Protocol) directory and supports Oracle Net and other LDAP-enabled protocols. Database support first appeared in Oracle8i and replaced Oracle Names, which was used to enable user connections to an Oracle Database server without having a client-side configuration file. The directory services are provided by the Oracle Fusion Middleware Identity Management platform.
Each connection to the database takes up valuable network resources, which can impact the overall performance of a database application. Oracle’s Connection Manager (CMAN), illustrated in Figure 1-3, reduces the number of Oracle Net client network connections to the database through the use of concentrators, which provide connection multiplexing to implement multiple connections over a single network connection. Connection multiplexing provides the greatest benefit when there are a large number of active users.
You can also use the Connection Manager to provide multiprotocol connectivity if you still have some clients and servers not using TCP/IP. Oracle Database 10g first introduced the dynamic Connection Manager configuration, enabling the changing of CMAN parameters without shutting down the CMAN process.
The growing popularity of Internet and intranet applications in the late 1990s led to a change in deployment from client/server (with fat clients running a significant piece of the application) to a three-tier architecture (with a browser supplying everything needed for a thin client). Hence, middleware is an important part of any database connection strategy discussion. Oracle’s WebLogic Server enables deployment of the middle tier in a three-tier solution for web-based applications, component-based applications, and enterprise application integration. Oracle WebLogic Server is a key part of Oracle’s Fusion Middleware, replacing Oracle’s earlier generation Internet Application Server, and is a component of Oracle’s Cloud Application Foundation.
Other Fusion Middleware components address transaction management, data integration, business process management, business intelligence, identity management, service-oriented architectures (SOA), and portal, social, and content platforms (WebCenter). We’ll introduce those after first describing the WebLogic Server.
The Oracle WebLogic Server comes in two editions, a Standard Edition and an Enterprise Edition. Both editions include support for the latest Java Enterprise Edition (EE) specification. At the time this edition of the book was published, the WebLogic Server was fully Java EE 6 compatible with the following: JSF 2.0, Servlet 3.0, EJB 3.1, Java Persistence API (JPA) 2.0, CDI 1.0, JAX-RS 1.1, and Java Bean Validation 1.0. Reference implementations used include the EclipseLink JPA and Jersey 1.1. (Oracle also offers an open source application server named Oracle GlassFish that is based on the Java EE 6 specification but not based upon the WebLogic Server—it is often positioned by Oracle as more of a lightweight platform and is less likely to appear where Oracle Databases are deployed as enterprise class solutions.)
The WebLogic Server Standard Edition includes the Hotspot and JRockit Java Virtual Machines (JVMs). Supported development and testing environments include:
- TopLink
TopLink provides a Java persistence framework that includes support for Object-Relation Mapping with the JPA, Object-XML Binding with JAXB and SDO, and Database Web Services for data access using JAX-WS. It can be used to link Java objects to the Oracle Database via JDBC such that the Java developer need not build SQL calls or face broken Java applications resulting from database schema changes.
- Application Development Framework (ADF)
ADF is built upon the Enterprise Java platform and provides infrastructure and development framework for Model View Controller (MVC) layers of an application.
- JDeveloper
JDeveloper is Oracle’s Java Integrated Development Environment (IDE) for the Oracle Database and Fusion Middleware.
- Classloader Analysis Tool (CAT)
CAT in the WebLogic Server is used for finding Java class and library conflicts.
- Other development environments
Oracle provides Eclipse plug-ins and support for the NetBeans IDE, both of which are open source Java EE development offerings. WebLogic Server also provides a Maven plug-in for testing and development and can support and leverage Spring applications.
For deployment, the WebLogic Server Standard Edition features an administration console with a change center, a WebLogic Diagnostic Framework (WLDF), and support for command line and scripting control.
The Oracle WebLogic Server Enterprise Edition adds the following capabilities:
- Clustering and Failover
WebLogic Servers clusters are load balanced and self-monitored to avoid overloading. If problems occur, servers can be migrated in whole, service can be automatically migrated, and the transaction recovery service can be invoked.
- High performance and reliable Java Message Service (JMS)
JMS improvements include support for automatic migration of services from a failing server to a healthy one, store and forward messaging, and support for Oracle Database Streams Advanced Queuing (AQ).
- Oracle Virtual Assembly Builder (VAB)
VAB enables packaging of software components for movement among development, test, and production environments and can be used with Oracle VM to create, configure, and provision applications to virtualized resources.
- JRockit Mission Control
JRockit Mission Control provides JVM diagnostics using an Eclipse-based user interface and includes JRockit Flight Recorder (JFR) for further analysis of problems after they occur.
- Oracle Traffic Director
The Oracle Traffic Director optimizes performance of the WebLogic Server Enterprise Edition when deployed on the Exalogic Elastic Cloud engineered system from Oracle.
Oracle also offers a WebLogic Suite designed for highly available and scalable Cloud deployment and includes the WebLogic Server Enterprise Edition and Coherence Enterprise Edition in this packaging. Coherence enables the pooling and sharing of memory across multiple servers and is managed using the WebLogic administration and scripting tools. TopLink Grid is used when deploying JPA applications in such deployments.
Other suites that are options for the WebLogic Suite include:
- Business Process Management (BPM) Suite
The Business Process Management Suite includes BPM Studio client modeling, BPM (process) Composer, BPMN Service Engine and Workflow Extensions (for BPMN 2.0 and BPEL, the Business Process Engineering Language), BPM Process Spaces, and BPM Process Analytics (integrated with Oracle Business Intelligence and Oracle Business Intelligence Strategy Maps and Balanced Scorecards).
- SOA Suite for Oracle Middleware
This suite bundles Oracle Fusion Middleware SOA offerings, including BPEL Process Manager, Business Activity Monitoring (BAM) for real-time alerting and dashboards, business rules engine, Enterprise Service Bus (for messaging, routing, and transformations), the Aqualogic Service Bus, Web Services Management (including a policy manager and monitoring dashboard), Web Services Registry, applications and technology adapters, and Oracle Event Processing.
The Fusion Middleware SOA Suite serves as the basis for Oracle’s Application Integration Architecture (AIA). AIA also includes prepackaged business objects and business processes known as Process Integration Packs and provides key underpinnings used in integrating Oracle’s applications.
One could argue that the concept of a middle-tier platform began prior to the popularity of the Internet because of a need for transaction monitors. A transaction monitor is used to ensure data integrity across all resources (even if resource failures occur), enables distributed applications to work together seamlessly, and enables publish and subscribe event models. Oracle Tuxedo has a long history of providing these capabilities that evolved under the ownership of several vendors.
Oracle Tuxedo supports the ATMI programming model, the X/Open API for C, C++, COBOL, and Java. It also supports the SCA and CORBA programming models. A variety of LDAP servers are supported for authentication and authorization. An IDE is provided that supports metadata-driven development.
Tuxedo supervises two-phase commits for transactions via the XA protocol. It provides its own message queuing capability and has bi-directional adapters for IBM CICS/IMS applications and access to IBM WebSphere MQ queues. It is monitored through the Oracle Tuxedo System and Application Monitor (TSAM) and is integrated into Enterprise Manager.
Oracle’s data integration products that are considered to be part of Fusion Middleware include the following:
- Oracle Data Integrator (ODI)
ODI is an extraction, transformation, and loading declarative design tool sometimes referenced as an ELT (extraction, load, and transformation) tool by Oracle as transformations are pushed into the target database. Knowledge Modules define the technical implementation of the integration process. A variety of source and target databases are supported. ODI has replaced Oracle Warehouse Builder as Oracle’s primary offering for ELT.
- Enterprise Data Quality for Oracle Data Integrator
Enterprise Data Quality Options for ODI are available for data profiling, batch processing, and address verification.
- Oracle GoldenGate
GoldenGate enables lightweight capture, routing, transformation, and delivery of changed data among a variety of Oracle and non-Oracle databases in near real time. The Capture software component resides on the source database or monitors JMS messaging for changes. Trail Files containing changed data reside on the source or target server. The Delivery Module takes changed data from the Trail Files and applies it to the target database.
Oracle data warehouses are often accessed using business intelligence tools from a variety of vendors. Usage of Oracle’s own tools became more common for such deployment as Oracle grew its offerings through acquisitions, replacing Oracle’s earlier Discoverer and Reports products.
Oracle’s flagship product in this area is Oracle Business Intelligence Foundation Suite, consisting of former Siebel Analytics/Oracle Business Intelligence Enterprise Edition and Hyperion Essbase components. The product has evolved into an integrated middle-tier platform that features reporting, ad hoc query and analysis, dashboards, balanced scorecard and strategy management, Office plug-ins, and mobile support.
Oracle also offers business intelligence applications that include data models and reporting and analysis with pre-populated business metadata. Applications include Oracle’s Business Intelligence Applications (the former Siebel Business Analytics applications) and Hyperion Financial Performance Management applications.
Other business intelligence offerings include Endeca Information Discovery (EID) and Real-Time Decisions (RTD). EID features a multifaceted server for data discovery that provides multiple drill paths through structured and unstructured data without the need to pre-define a schema. RTD is a real-time recommendation engine that can mine data or use input from other data mining and statistics models in order to present best recommendations to web interfaces and through applications.
The WebCenter Suite of products features WebCenter Portal and WebCenter Content. WebCenter Portal is Oracle’s enterprise portal framework based on the Oracle ADF and used for creating dynamic portals, websites, composite applications, and mash-ups. Social collaboration is enabled through services such as discussion forums, wikis, blogs, RSS, tags, links, social networking, and activity streams.
WebCenter Content is a content management platform that provides version control, security, indexing and search, metadata, a workflow engine, and replication. Capabilities include content publishing, digital asset management, document imaging, records and retention management, and archiving and storage management through the WebCenter Portal.
Oracle’s Identity Management platform for Fusion Middleware includes Oracle’s Access Management Suite, Oracle Identity Governance, and Oracle Directory Services. This suite of products enables the securing of applications and associated data, Web Services, and Cloud-based services.
The Access Management Suite provides authentication, single sign-on, mobile and social sign-on, entitlement management, and fine-grained authentication. Key components of the Access Management Suite include Access Manager, Adaptive Access Manager, Identity Federation, Entitlements Server, OpenSSO Fedlet, and Security Token Service (STS). External authorization is available through support of multiple standards including XACML, NIST, and Enterprise RBAC.
The Identity Governance Suite provides a platform for access requests, role lifecycle management, access certification, closed loop remediation, and privileged account management. A lengthy list of components are included: Identity Analytics, Identity Manager, Privileged Account Manager, Identity Manager Connector for Database User Management, Identity Manager Connector for Microsoft Active Directory, Identity Manager Connector for Microsoft Windows, Identity Manager Connector for Novell eDirectory, Identity Manager Connector for Oracle Internet Directory, Identity Manager Connector for Sun Java System Directory, and Identity Manager Connector for Unix.
Oracle’s Directory Services include the Virtual Directory, Oracle Internet Directory including Delegated Administration Service (DAS) and Directory Integration Platform (DIP), Directory Server Enterprise Edition, and Oracle Unified Directory. The Unified Directory is a Java-based directory service adhering to LDAP and Directory Services Markup Language standards with advanced storage, proxy, synchronization, and virtualization capabilities.
The Oracle Database is well known for its ability to handle extremely large volumes of data and users. Oracle not only scales through deployment on increasingly powerful single platforms, but it also can be deployed in distributed configurations. Oracle deployed on multiple platforms can be combined to act as a single logical distributed database.
This section describes some of the basic ways that Oracle handles database interactions in a distributed database system.
Data within an organization is often spread among multiple databases for reasons of both capacity and organizational responsibility. Users may want to query this distributed data or update it as if it existed within a single database.
Oracle first introduced distributed databases in response to the requirements for accessing data on multiple platforms in the early 1980s. Distributed queries can retrieve data from multiple databases. Distributed transactions can insert, update, or delete data on distributed databases. Oracle’s two-phase commit mechanism guarantees that all the database servers that are part of a transaction will either commit or roll back the transaction. Background recovery processes can ensure database consistency in the event of system interruption during distributed transactions. Once the failed system comes back online, the same process will complete the distributed transactions.
Distributed transactions can also be implemented using popular transaction monitors (TPs) such as Tuxedo that interact with Oracle via XA, an industry standard (X/Open) interface.
Heterogeneous Services allow non-Oracle data and services to be accessed from an Oracle Database through generic connectivity via ODBC and OLE-DB, which are included with the database.
Optional Transparent Gateways use agents specifically tailored for a variety of target systems. Transparent Gateways allow users to submit Oracle SQL statements to a non-Oracle distributed database source and have them automatically translated into the SQL dialect of the non-Oracle source system, which remains transparent to the user. Gateways are available for Sybase, Microsoft SQL Server, DRDA, Informix, Teradata, APPC, and WebSphere MQ.
In addition to providing underlying SQL services, Heterogeneous Services provide transaction services utilizing Oracle’s two-phase commit with non-Oracle databases and procedural services that call third-generation language routines on non-Oracle systems. Users interact with the Oracle Database as if all objects are stored in the Oracle Database, and Heterogeneous Services handle the transparent interaction with the foreign database on the user’s behalf. Oracle 12c includes a new feature called SQL Translation, which allows the Oracle Database to dynamically translate SQL from a different database. This feature makes it easier to migrate data to an Oracle Database without the need for excessive rewrites of SQL in your applications. The feature is described in Chapter 4.
Moving data from one Oracle Database to another is often a requirement when using distributed databases, or when a user wants to implement multiple copies of the same database in multiple locations to reduce network traffic or increase data availability. You can export data and data dictionaries (metadata) from one database and import them into another. Oracle Database 10g introduced a high-speed data pump for the import and export.
Oracle also offers many other advanced features in this category, including transportable tablespaces and Advanced Queuing—/Oracle Streams. We introduce these in the next section. Pluggable databases are a key new feature in Oracle Database 12c that can be used to move data from one instance to another. We’ll describe the impact of pluggable databases in Oracle Database 12c in the manageability section of this chapter.
Transportable tablespaces first appeared in Oracle8i. Instead of using the export/import process, which dumps data and the structures that contain it into an intermediate file for loading, you can place a tablespace in read-only mode, move or copy it from one database to another, and then mount it. The same data dictionary information (metadata) describing the tablespace must exist on the source and the target. This feature can save a lot of time since it simplifies the movement of large amounts of data. Starting with Oracle Database 10g, you can move data with transportable tablespaces between heterogeneous platforms or operating systems.
Advanced Queuing (AQ) was introduced in Oracle8 to provide a means to asynchronously send messages from one Oracle Database to another. Messages are stored in a queue in the database and sent asynchronously when a connection is made, so the amount of overhead and network traffic is much lower than it would be using traditional guaranteed delivery through the two-phase commit protocol between source and target. This approach enabled a content-based publish and subscribe solution using a rules engine to determine relevant subscribing applications. In Oracle9i, AQ added XML support and Oracle Internet Directory (OID) integration.
AQ became part of Oracle Streams in the second release of Oracle9i and features log-based replication for data capture, queuing for data staging, and user-defined rules for data consumption. However, Oracle now recommends GoldenGate in its Fusion Middleware for enabling change data capture and replication as it provides more flexibility since it supports Oracle and a variety of non-Oracle databases.
Oracle includes many features specifically designed to boost performance in certain situations. We’ve divided the discussion in the following subsections into two categories: database parallelization and data warehousing.
By breaking up a single task into smaller tasks and assigning each subtask to an independent process, you can dramatically improve the performance of certain types of database operations. Database tasks that are implemented in parallel speed the querying, tuning, and maintenance of the database. Examples of query features implemented in parallel in Oracle Database 12c include:
Table scans
Nested loops
Sort merge joins
GROUP BYs
NOT IN subqueries (anti-joins)
User-defined functions
Index scans
Select distinct UNION and UNION ALL
Hash joins
ORDER BY and aggregation
Bitmap star joins
Partition-wise joins
Concurrent Union-All
Correlated filters and expressions
Stored procedures (PL/SQL, Java, external routines)
In addition to parallel query, many other Oracle features and capabilities can be parallelized.
While parallel features improve the overall performance of the Oracle Database, the Oracle Database also has particular performance enhancements for business intelligence and data warehousing applications that we briefly introduce in this section. Of course, as the Oracle BI Server most often accesses relational databases, these features can greatly speed the performance of that tool and other non-Oracle business intelligence tools.
Stored bitmap indexes have been available in the Oracle Database since Oracle 7.3 and provide a fast way of selecting and retrieving certain types of data. Bitmap indexes typically work best for columns that have few different values relative to the overall number of rows in a table.
Rather than storing the actual value, a bitmap index uses an individual bit for each potential value with the bit either “on” (set to 1) to indicate that the row contains the value or “off” (set to 0) to indicate that the row does not contain the value.
Typical data warehousing queries occur against a large fact table with foreign keys to much smaller dimension tables. Oracle added an optimization for queries against this type of star schema in Oracle 7.3. Performance gains are realized through the use of Cartesian product joins of dimension tables with a single join back to the large fact table. Oracle8 introduced a mechanism called a parallel bitmap star join, which uses bitmap indexes on the foreign keys to the dimension tables to speed star joins involving a large number of dimension tables.
Since Oracle8i, materialized views have provided another means of achieving a significant speedup of query performance. Summary-level information derived from a fact table and grouped along dimension values is stored as a materialized view. Queries that can use this view are directed to the view, transparently to the user and the SQL they submit. Oracle has continued to improve optimizer usage of materialized views with each new release of the database.
A growing trend in Oracle and other databases is inclusion of SQL-accessible analytic and statistical functions in the database. Oracle first introduced such capabilities in Oracle8i with the CUBE and ROLLUP functions. Today, the functionality provided also includes ranking functions, windowing aggregate functions, lag and lead functions, reporting aggregate functions, statistical aggregates, linear regression, descriptive statistics, correlations, crosstabs, hypothesis testing, distribution fitting, and Pareto analysis.
The OLAP Option physically stores dimensionally aware cubes in the Oracle relational database. These cubes are most frequently accessed using SQL, although a Java API is also supported. Since Oracle Database 11g, the Oracle Database optimizer recognizes the levels within these cubes. As a result, any business intelligence tool that submits SQL to an Oracle Database can transparently take advantage of the improved performance offered by deployment of this option. Refreshes of the values in these cubes are now maintained similar to refreshing materialized views.
Since Oracle9i, popular data mining algorithms have been embedded in the database through the Data Mining Option and are exposed through a PL/SQL or Java data mining API. Data mining applications that use these algorithms are typically built using Oracle’s DataMiner. Data mining algorithms available for Oracle Database 12c include Naïve Bayes, Associations, Adaptive Bayes Networks, Clustering, Support Vector Machines (SVM), Nonnegative Matrix Factorization (NMF), Decision Trees, and Generalized Linear Models.
Oracle R Enterprise support was added to the Oracle Data Mining Option that was then renamed the Oracle Advanced Analytics Option in later releases of Oracle Database 11g. This capability enables R statistical programmers using open source tools to generate R scripts and then deploy those scripts in the Oracle Database. This eliminates the need to move data out of the Oracle Database onto a separate analysis platform and scales consistently with the Oracle Database.
Oracle includes many features that make the database easier to manage. Oracle management fundamentally improved with the introduction of Oracle Database 10g, and has continued to evolve toward being more self-tuning and self-managing in subsequent database releases. If you are still managing Oracle Databases using older techniques (e.g., scripts), you might want to reevaluate your thinking on management.
Since Oracle Database 10g, statistics are automatically gathered to an Automatic Workload Repository (AWR) within the database. Oracle’s Automatic Database Diagnostic Monitor (ADDM) evaluates the statistics on a regular basis and sends alerts of potential problem conditions to Oracle Enterprise Manager, where you can evaluate the condition in more detail and potentially take corrective actions. Some of the newer fully automated features, such as Automatic Memory Management, also leverage data gathered in the AWR.
Oracle has a near real-time view of current database conditions as it makes automated recommendations. Such recommendations will often be more accurate than would be possible with the manual processes you might have used in the past. In the following subsections, we’ll introduce the impact this has on Oracle Enterprise Manager and add-on packs, Information Lifecycle Management, backup and recovery, and database availability. We’ll also describe the impact of pluggable databases in this section.
Many Oracle Database generations ago, Oracle Enterprise Manager (EM) was introduced as an Oracle Database manager framework. Today, Enterprise Manager 12c continues to be a framework used for managing the database, but it is also used for managing Fusion Middleware, Oracle’s Applications, Oracle’s engineered systems, Cloud-based infrastructure, and more. The framework consists of management services, monitoring, configuration management, task automation, security, and plug-ins for the managed platforms supported. Later in this book, we’ll focus our discussion on managing the database deployed to a traditional infrastructure and also to a Cloud-based infrastructure. We’ll also discuss the added capabilities provided by Enterprise Manager when managing the Oracle Exadata Database Machine including the monitoring of hardware alerts, configuration management, and proactive support.
Enterprise Manager is accessed via a browser or mobile device (e.g., iOS devices were supported when this edition of the book was published). Enterprise Manager provides a basic interface for monitoring and management of Oracle Database users and user privileges, database schema, and database configuration, status, and backup and recovery. The optional Enterprise Manager Packs that Oracle offers extend the management capabilities:
- Oracle Diagnostic Pack for Oracle Database
The Oracle Diagnostic Pack for the Database provides an automatic and real-time performance interface to the Automatic Database Diagnostic Monitor (ADDM), an automatic workload capture interface to the Database Automatic Workload Repository (AWR), performance comparisons versus ASR baselines, active session history, system monitoring and notification, and Exadata-specific lights-out monitoring and management of nodes, Exadata Storage Server cells, and InfiniBand switches.
- Oracle Tuning Pack for Oracle Database
The Oracle Tuning Pack for the Database provides real-time SQL monitoring used in identifying long-running SQL, a SQL Tuning Advisor that provides recommendations to administrators or that can be run in automatic mode, and a SQL Access Advisor that provides advice on schema design.
- Oracle Database Lifecycle Management Pack
The Oracle Database Lifecycle Management Pack automatically discovers database and application physical servers, enables deployment procedures for provisioning and patching of databases, provides capability to perform patching lifecycle change management, configuration management, and compliance management, and provides Site Guard to manage disaster recovery through integration with Data Guard and filesystem data storage.
- Oracle Cloud Management Pack for Oracle Database
The Cloud Management Pack enables identification of pooled resources, configuration of role-based access, definition of service catalogs and chargeback plans, and supports user requested database resource requests and provisioning where the database is deployed as a service (DBaaS).
- Oracle Data Masking Pack
The Oracle Data Masking Pack enables scanning of an Oracle Database for sensitive data based on patterns, uses referential relationships when determining data elements that should be masked, provides a library of typical masks needed, and supports advanced masking techniques such as condition-based masking, compound masking, deterministic masking, and key-based reversible masking.
- Oracle Test Data Management Pack
The Oracle Test Data Management Pack automatically discovers data relationships and table types, storing them in Application Data Models, and is used to create test databases from production databases.
Oracle Database 11g introduced the capability to rerun production workloads and test system changes through the Real Application Testing Option. This database option includes a Database Replay facility and the SQL Performance Analyzer (SPA). Database Replay captures production workload information, including concurrency, dependencies, and timing. It transforms the workload capture files into replay files, provides a Replay Client for processing the replay files, and provides the means to report on performance statistics and any errors that might occur. The SQL Performance Analyzer captures a SQL workload to be analyzed, measures the performance before database changes and afterward, and identifies performance changes among SQL statements.
As of Oracle Database 12c, the database can function as a multitenant container database (CDB) where the Multitenant Option is licensed and hold one or more pluggable databases (PDBs). A PDB is a portable collection of schemas and schema objects that could be unplugged from one CDB and into another. Since PDBs share resources provided by the CDB, this increases the number of databases that can be deployed on a given hardware platform due to more efficient utilization of the resources.
CDBs and PDBs are an important piece of Oracle’s DBaaS Cloud strategy as they enable more rapid provisioning of platforms. Key management tasks are simplified since they are managed through the CDBs and related to PDBs. Examples of management features in the CDBs include Active Session History (ASH), alerts, automated database maintenance tasks, ADDM, automatic statistics optimizer collection, Automatic Segment Advisor, AWR, SQL Management Base (SMB), SPA, SQL Tuning Advisor, and SQL Tuning Sets (STS).
Automatic Storage Management (ASM) is provided as part of the Oracle Database. First introduced with Oracle Database 10g, ASM is used to manage pools of storage in designated disk groups that store the database files. The database data is evenly distributed (striped) across disks in a disk group for optimal performance. Data is typically mirrored using ASM for availability. Because an ASM interface is provided through Enterprise Manager, the database administrator can perform this critical management task.
Data in large Oracle Databases is often partitioned to provide a higher degree of manageability and availability. For example, you can take individual partitions offline for maintenance while other partitions remain available for user access. This partitioning capability is provided by the Partitioning Option and was introduced for Oracle8. Since then, the types of partitioning supported have continued to grow in sophistication.
In data warehousing implementations, partitioning is sometimes used to implement rolling windows based on date ranges. Other partitioning types include hash partitioning (used to divide data into partitions using a hashing function and providing an even distribution of data), list partitioning (enables partitioning of data based on discrete values such as geography), interval partitioning (used to automatically create new fixed ranges as needed during data insertions), reference partitioning (where a child table inherits the partitioning strategy of the parent table), and virtual column partitioning (defined by an expression linked to one or more columns). Many of these partitioning types can be used in combination as “composite” partitions. Examples of composite partitions in Oracle Database 12c include range-range, range-hash, range-list, list-range, list-hash, and list-list, hash-hash, and interval-reference.
One of the goals of storage management is to minimize the amount of physical disk required. Compression techniques are often combined with partitioning strategies as it often makes sense to compress data on older nonchanging partitions to avoid performance hits during updates. Since Oracle9i Release 2, the Oracle Database has included basic compression that typically provides two to four times compression for read-only or inactive tables and partitions. The Advanced Compression Option provides two to four times compression for OLTP databases where updates occur and has been available since Oracle Database 11g. Hybrid Columnar Compression is available for Exadata Storage and provides about 10 times compression for data warehouses and 15 times compression for archiving data.
Oracle defines a Maximum Availability Architecture (MAA) that addresses recovery time objectives, recovery point objectives, and service level agreement (SLA) objectives in an organization. Organizations use these guidelines and software capabilities to deliver highly available databases by minimizing planned downtime for maintenance tasks and reducing or eliminating unplanned downtime from outages. Features in the Oracle Database that help manage planned downtime for systems changes, data changes, and applications changes include the ability to do online reconfiguration with rolling upgrades, online redefinition, and edition-based redefinition.
Eliminating unplanned downtime focuses on two areas: data availability and server availability. Solutions for eliminating unplanned data outages include Recovery Manager (RMAN) for backup and recovery, Oracle Secure Backup, the Data Recovery Advisor, Flashback, ASM, Data Guard, and GoldenGate. Real Application Clusters (RAC) is the critical component that helps eliminate unplanned server availability. We’ve introduced a few of these capabilities previously in this chapter, so we focus on introducing recoverability features and RAC in this section.
Oracle Database 12c introduces Application Continuity for masking lost database sessions from users during planned and unplanned downtime. A JDBC replay driver intercepts execution errors when sessions are lost and saves the context of each SQL and PL/SQL call it is instructed to hold. The JDBC replay driver replays the calls when the session is reestablished as directed by a continuity director in the database.
The Oracle Database features a variety of Flashback technologies that enable rapid recovery from human errors. A Flashback Query enables a query designating a point in time in the past to be submitted to see how data looked at that time and allows you to identify possible corruption that occurred since. The Flashback Version query enables looking at how a row of data changed over a time interval. A Flashback Transaction Query enables the administrator to see changes made by an individual transaction. Where dependencies exist, the Flashback Transaction capability can be used to back out all changes, relying on undo and archived redo logs.
When individual tables need to be recovered at a previous point in time, Flashback Table is used. Flashback Drop enables easy recovery of dropped tables. Flashback Database enables recovery of an entire Oracle Database to a previous point in time, relying on Flashback logs in the database to restore blocks that have changed.
As every database administrator knows, backing up a database is a rather mundane but necessary task. An improper backup makes recovery difficult, if not impossible. Unfortunately, people often realize the extreme importance of this everyday task only after losing business-critical data resulting from a failure of a related system.
Typical kinds of backups include complete database backups, tablespace backups, datafile backups, control file backups, and archivelog backups. Oracle’s Recovery Manager (RMAN) enables server-managed backup and recovery of the database and leverages a Recovery Catalog stored in the database. RMAN can automatically locate, back up, restore, and recover datafiles, control files, and archived redo logs. During backups, RMAN verifies all data blocks to ensure that corrupt blocks are not propagated to backup files. Efficient recovery can occur at the individual block level.
RMAN can restart backups and restore and implement recovery window policies when backups expire. A variety of compression levels are supported to assure reasonable performance where network bottlenecks or CPU limitations exist. A Fast Recovery Area (FRA) can be defined on a file system or ASM disk group enabling better space management. Oracle Enterprise Manager provides a GUI-based interface to RMAN and includes a job scheduler that can be used with RMAN for managing automatic backups to disk.
RMAN can perform incremental backups of Enterprise Edition Databases. Incremental backups will back up only the blocks modified since the last backup of a datafile, tablespace, or database; thus, they’re smaller and faster than complete backups. RMAN can also perform point-in-time recovery, which allows the recovery of data until just prior to an undesirable event (such as the mistaken dropping of a table).
Various media-management software vendors leverage Oracle’s RMAN as part of their backup solutions. Since Oracle Database 10g, Oracle has offered a tape backup solution integrated with RMAN for database and filesystem data named Oracle Secure Backup (OSB). OSB features policy-based and fine-grained control over backup media and to the Cloud through support of encryption and key management. OSB also supports tape duplication and managing the rotation of tapes among multiple sites.
Oracle first introduced a standby database feature in Oracle 7.3. A standby database provides a copy of the production database to be used if the primary database is lost—for example, in the event of primary site failure or during routine maintenance. Primary and standby databases may be geographically separated. The standby database is created from a copy of the production database and updated through the application of archived redo logs generated by the production database. Since Oracle9i, Data Guard fully automates this process, including the copying and applying of logs. Agents are deployed on both the production and standby database, and a Data Guard Broker coordinates commands. A single Data Guard command invokes the steps required for failover.
Oracle Database 10g introduced support for real-time application of redo data, integration with the Flashback Database feature, archivelog, and support of rolling upgrades. The Active Data Guard Option introduced with Oracle Database 11g enabled a standby database to be used for queries, sorting, and reporting even as changes from the production system are being applied. Oracle Database 12c introduces a lightweight Oracle instance Far Sync standby (with no datafiles) that is used to reliably forward redo synchronously to one or more remote locations and greatly widen distances where Data Guard might be deployed.
In addition to providing physical standby database support, Data Guard can be used to create a logical standby database where Oracle archive logs are transformed into SQL transactions and applied to the open standby database. Data Guard also supports snapshot standbys where redo is received but not applied when data is simply to be replicated to the standby for testing purposes (such standbys can be converted to physical standbys, and then redo is applied).
If an outage occurs, the Data Recovery Advisor leverages RMAN and Data Guard (including standbys) in determining the best recovery options that minimize any data loss. Administrators can choose among these options or the Advisor can be set to automatically run the best choice.
The Fail Safe feature provides a higher level of reliability for an Oracle Database on a Windows platform than simple high-availability approaches in leveraging Microsoft Cluster Services. Failover is implemented through a second system or node that provides access to data residing on a shared disk when the first system or node fails. Fail Safe is primarily a disaster recovery tool, so some downtime does occur as part of a failover operation. The recommended solution for high server availability on all platforms, including Windows, is RAC.
RAC first appeared as an option for Oracle9i, replacing the Oracle Parallel Server (OPS) option. RAC can provide failover support as well as increased scalability on Unix operating system variations, Linux, and Windows clusters. Key to RAC’s improved scalability was the introduction of Cache Fusion that greatly minimized the amount of writing to disk that was formerly used to control data locks. Oracle Database 10g introduced a new level of RAC portability and Oracle support by providing integrated “clusterware” for supported RAC platforms.
With Real Application Clusters, you can deploy multiple Oracle instances on multiple nodes of a clustered solution or in a grid configuration. RAC coordinates traffic among the systems or nodes, allowing the instances to function as a single database. As a result, the database has proven to scale across dozens of nodes. Since the cluster provides a means by which multiple instances can access the same data, the failure of a single instance will not cause extensive delays while the system recovers. You can simply redirect users to another instance that’s still operating. Applications can leverage the Oracle Call Interface (OCI) to provide failover to a second instance transparently to the user.
Data Guard can be used to provide automated failover with bounded recovery time in conjunction with Oracle Real Application Clusters. In addition, it provides client rerouting from the failed instance to the instance that is available with fast reconnect and automatically captures diagnostic data.
Oracle includes basic security for managing user access through roles and privileges. These can be managed through Enterprise Manager on a local basis or on a global basis by leveraging Oracle’s enterprise user security, a feature in the Advanced Security Option.
Database security features allow you to implement a Virtual Private Database (VPD) using Oracle by creating and attaching policies to database tables, views, or synonyms. These policies are then enforced by placing a predicate WHERE clause on SELECT, INSERT, UPDATE, DELETE, and/or INDEX statements.
New in Oracle Database 12c, you can redact or mask data queried by users or applications, taking into account assigned privileges. Full data redaction, partial data redaction, or random data redaction of specified columns in tables or views is supported.
Many organizations face the need to meet more stringent compliance requirements for improved data protection, although database usage now can extend beyond organizational boundaries. Oracle has added several options to the database to enable secure deployment in such challenging environments. These options include the Advanced Security Option, Label Security Option, Database Vault Option, and Audit Vault and Database Firewall Option.
The Advanced Security Option (ASO) enables data encryption of tablespaces and columns in the database via Transparent Data Encryption (TDE), which encrypts and decrypts data without requiring any code in the applications that access this data. Data encrypted in TDE remains encrypted when backed up using RMAN. ASO also provides strong authentication services to the database through two-tier key management consisting of a master encryption key and one or more data encryption keys. Oracle Database 12c further enhanced the range of TDE key management capabilities available.
Standards-based network encryption is provided with authentication to the database through Kerberos, KPI, or RADIUS. Industry standard network encryption, enabling more secure Oracle Net connections, includes support for the Advanced Encryption Standard (AES) and the U.S. Triple Data Encryption Standard (3DES).
Oracle Label Security controls access to data by comparing labels assigned to rows of data with label authorizations granted to users through their privileges. Multiple authorization levels are possible within a single database. Label security is a higher level interface to row-level security supported in Enterprise Edition.
Label security policies, data and user labels, and protected tables can all be managed through Oracle Enterprise Manager and can also be integrated with Oracle Identity Management. Since Policies are enforced in the database instead of through views, which greatly simplifies management of data accessibility and provides a more secure implementation.
The Oracle Database Vault Option allows for another dimension of database security. A standard problem with database security stems from the need for database administrators to have full access to the data they manage—a potential security hole. The Database Vault Option allows you to restrict access granted with system-wide privileges, such as preventing administrators read or write access to data, or restricting administrative access to a defined realm of data, allowing for finer grained separation of administrative duties frequently necessary as databases are consolidated. A security administrator can set factors to define access to the database including Oracle commands available to the different classes of users and administrators and audit-specific dimensions of security. At a more granular level, realms can be defined for limiting access to specific database schemas and roles.
The Audit Vault and Database Firewall Option includes the Audit Vault Server, Audit Vault Collection Agent, Database Firewall, and Database Firewall Management Server. The Oracle Audit Vault Server monitors Oracle Database audit tables and audit information from other database brands, redo logs, and operating system audit files for suspicious activities and is used to manage audit activities. It includes pre-built compliance reports and entitlement reports for Oracle Databases that show users, roles, and privileges and can send alerts showing where unusual or unauthorized activity is occurring.
The Database Firewall is used to monitor SQL statements transmitted to the Oracle Database and determine whether to allow, log, alert, substitute, or block the SQL. SQL statements from specific users or IP addresses of specific types can be blocked. Database Firewall events are logged to the Audit Vault Server.
Many Oracle tools are available to developers to help present data and build more sophisticated Oracle Database applications. As this book focuses on the Oracle Database, this section briefly describes the main Oracle tools used for database development today: Oracle SQL Developer and Oracle Application Express. Other legacy tools, such as Oracle Forms Developer, Oracle Designer, and Oracle Programmer, are used with much less frequency today.
Oracle SQL Developer is an IDE for any currently supported Oracle Database version that you can download from the Oracle Technology Network at no charge and run on your Windows, Linux, or Apple MAC OS X workstation. With SQL Developer, you can create connections to Oracle Databases, browse database objects, create and modify database objects, query and update data, export data and DDL, import data, process commands, and run and create reports. The product’s tools support the editing, debugging, and running of PL/SQL scripts and the DBA Console can be used to manage the database. In addition, SQL Developer can be pointed at non-Oracle databases to view their database objects and data, and it provides capabilities to migrate to an Oracle Database.
The SQL Developer Data Modeler provides a graphical user tool for creating, browsing, and editing database models. Data dictionaries can be imported from Oracle Databases, IBM DB2, and Microsoft SQL Server. Data models can be imported from the Computer Associates ERwin product and Oracle’s previous generation design tool, Oracle Designer.
Oracle Application Express (APEX) is an in-Oracle Database rapid development tool freely available for all current editions of the Oracle Database. The tool was designed to create HTML-based applications through a wizard-based approach and has proven to be fairly popular in the Oracle community. APEX has come with the Oracle Database since version 10g R2 and is available as a download from the Oracle Technology Network forum.
Developers using APEX access a browser-based declarative development framework and are presented with wizards and property sheets for declaratively building and maintaining applications. Key components include an application builder, SQL workshop, team development environment, and administration and analytics interface. Typical use cases include creation of data-driven applications, SQL-based reporting, spreadsheet conversion to web applications, Oracle Forms applications modernization to HTML and Web 2.0, and Microsoft Access replacement.
APEX provides flexibility for development and deployment in a variety of scenarios including local, on multitenant private Clouds (with workspaces for each department and self-service provisioning), or on public Clouds such as the Oracle Database Cloud Service. The framework is flexible in how applications might be accessed and in 2011 added support for mobile devices (leveraging jQuery Mobile) and HTML5.
Today, Oracle offers other databases including Oracle MySQL, Berkeley DB, Oracle NoSQL Database, and TimesTen. We’ll also briefly touch upon the Cloudera Hadoop distribution included with Oracle’s Big Data Appliance. These database engines have unique code lines with different intended roles. There are entire books written about these alternative databases. For this reason, we will describe these briefly in the following subsections but will not explore their capabilities in detail elsewhere in this book.
The MySQL database is an open source relational database, with source code made available under the GNU General Public License (GPL). Development of MySQL began in 1994 by Michael Widenius and David Axmark and it was first released in 1995. Over time, the MySQL database grew into the most popular open source database. MySQL was acquired by Sun in 2008, and Sun was then acquired by Oracle in 2010. In addition to the MySQL Database, there is a free graphical data modeling, SQL development, and administration tool called the MySQL Workbench that is often deployed as part of the infrastructure. Oracle bundles the Workbench with its editions of MySQL.
A MySQL Community Edition remains freely downloadable today and is supported by open source developers. Oracle offers support for several editions, including a Classic Edition, Standard Edition, Enterprise Edition, and Cluster Carrier Grade Edition. The editions share in common a reputation for rapid installation, low total cost of ownership, and exceptional scalability and performance for many applications.
The Classic Edition is intended to be used as an embedded database by OEMs, ISVs, and VARs developing read-intensive applications using MyISAM. For more demanding OLTP applications, the Standard Edition provides a basic database engine that also includes the InnoDB engine.
As scalability and performance requirements grow, the Enterprise Edition is designed to provide additional functionality. Key components include:
MySQL Enterprise Backup for hot compressed backups; full, incremental, and partial backups; full and partial restores; and point-in-time recovery
MySQL Enterprise High Availability, which includes replication, Oracle VM template support, and Solaris and Windows failover clustering
MySQL Enterprise Scalability for high-demand query and workload
MySQL Enterprise Security for supporting pluggable authentication, including Windows Active Directory support
MySQL Enterprise Audit for supporting policy-based auditing in applications
MySQL Enterprise Monitor for proactive best practices tips and security alerting through the Enterprise Dashboard, Advisors, Replication Monitor, and Query Analyzer (that identifies SQL code slowing query performance)
The MySQL Cluster Carrier Grade Edition automatically partitions database tables across nodes of a multinode commodity hardware platform providing horizontal scalability using the NDB engine. This Edition also enables highly available configurations and supports active-active replication for clustering across distributed geographies, including for purposes of disaster recovery. Nodes can be added and database schema can be updated while the database is online. The MySQL Cluster Manager automates common cluster management tasks and provides extended cluster monitoring support.
Oracle Berkeley DB is an extremely small-footprint embedded database engine. The engine supports transactional workloads and features multiversion concurrency control, indexing, encryption, and replication. It comes in variations that Oracle labels as Berkeley DB, Berkeley DB Java Edition, and Berkeley DB XML. Data interfaces supported include the SQLite API, Java Objects, key value, and XQuery/XPath for XML. For example, the Java Edition provides a direct persistence layer (DPL) API for EJB-style persistence and a Java collection API and the database is a single JAR file.
Berkeley DB is designed to be deployed with and run in the same process as your applications. Footprints for the database have static library sizes of less than 1 MB and runtime dynamic memory requirements of a few kilobytes.
When mobile applications deployed using Berkeley DB are to be attached to an Oracle Database, the Oracle Database Mobile Server provides a sync engine and a mobile manager. This server can be deployed on WebLogic or GlassFish. Mobile clients can include Java, Android, Blackberry, Windows, and Linux.
In 2011, Oracle introduced the Oracle NoSQL Database that leverages the Berkeley DB Java Edition engine. It is designed to support large volume and low latency applications and is implemented on a distributed key value engine with transparent load balancing across the nodes it is deployed to. The applications are written specifying data consistency policies depending on the amount of overhead that is acceptable. These policies can range from absolute consistency to time-based consistency to weak consistency (and lowest latency). The NoSQL Database can be deployed as highly available through configurable multiple replicas and for disaster recovery by locating the replicas in secondary locations.
Two editions are available. The Oracle NoSQL Database Community Edition is Oracle’s open source offering (AGPL version 3 license). It is included with the Oracle Big Data Appliance. Oracle also offers the Oracle NoSQL Database Enterprise Edition, which is Oracle-supported.
Oracle TimesTen is a relational database that is stored in physical memory and is typically used where very high-performance transaction-processing workloads are present. Access to the TimesTen database is available through SQL, JDBC, JMS, and ODBC. TimesTen databases can be deployed as exclusive or shared and can be created as permanent or temporary.
The database is refreshed by gathering data using TimesTen libraries deployed to applications or by using a Cache Connect Option to an Oracle Database. Because data is read and updated in memory, average update or read response times are typically measured in the millionths of seconds. The Cache Connect Option supports both read and write caching of Oracle Database data. Updates between TimesTen and Oracle can be bidirectional. When paired with the Oracle Database, this is referenced as the Oracle In-Memory Database Cache (IMDB Cache).
As is typical for embedded databases, TimesTen requires almost no ongoing administration. Replication is possible from one TimesTen database to another through an option and is, by default, asynchronous.
Oracle introduced a variation named TimesTen for Exalytics with the Oracle Exalytics In-Memory Machine in 2011. As might be expected for optimizing performance of the BI Foundation Suite, key analytics and query functionality was added, including OLAP grouping operators, analytic functions, time functions, and columnar compression.
Apache Hadoop is an open source framework for data-intensive applications where the data is considered to be semi-structured or unstructured. Such data typically comes from sensors, social media feeds, text, and web log data and contains descriptors, data of value tied to those descriptors, and other miscellaneous data. Thus, the data of value is relatively sparse. It was recognized by developers of search engines such as Google and Yahoo! in the early 2000s that there was a need to map such data and reduce it down to data of value to make sense of it. Hence, MapReduce was developed as a programming paradigm and is embedded in Java, Python, and other programming languages. The data itself is stored and analyzed in the Hadoop Distributed File System (HDFS) that is deployed across a cluster of a multinode hardware configuration.
The most popular distribution of Hadoop, at the time this book was published, is the Cloudera Distribution of Hadoop (CDH). CDH is included with Oracle’s Big Data Appliance (BDA) and supported by Oracle. In addition to HDFS and MapReduce, CDH includes other Hadoop components including Flume, Fuse-DFS, HBase, Hive, Mahout, Oozie, Pig, Sqoop, and Zookeeper. CDH also provides the Cloudera Manager for managing the Hadoop cluster, and Oracle provides further platform management integration via Enterprise Manager.
As some organizations prefer to make the Oracle Database the source for all data, Oracle Database 12c introduced pattern matching within the Oracle Database. This enables organizations with Oracle Database skills to begin MapReduce-like development in a more familiar environment. More commonly, organizations will perform MapReduce on their Hadoop cluster and then will load the data of value into a data warehouse. Oracle offers a Loader for Hadoop (OLH) that optimizes performance when loading this data into an Oracle Database.
Get Oracle Essentials, 5th Edition now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
