Chapter 3
Preparing to Implement Oracle
In This Chapter
Implementing Oracle
Verifying system requirements
Planning server and disk configuration
Taking care of post-software-installation setup
Before you create databases and store your data, you need to plan your steps, which will make your implementation much easier. First and foremost, you need to determine your overall database architecture. Databases don’t exist as standalone entities; they’re part of an information system, and you need to understand how that system is laid out. This chapter looks at two of the most common implementation methods and helps you determine which method is right for you.
After you determine the right overall implementation plan, you need to make sure that your target environment meets the necessary requirements to host Oracle. This chapter not only looks at obvious requirements, such as server hardware and software, but it also looks at less reviewed (yet critical) requirements, such as user, configuration, and storage considerations. This chapter gives you the knowledge to make good judgments of where and how you implement your Oracle database.
Understanding How an Oracle Database Fits into a System’s Architecture
Oracle databases don’t simply exist in isolation; they act as part of a computer system. Before installing the Oracle software and configuring your database, you need to know how your database fits into the overall system architecture. Some systems are more complex than others, but most fall into the following basic categories:
Client-server
Multi-tier
Component configurations
Client-server applications
Client-server applications (sometimes called two-tier applications) are those in which the user’s workstation has the application program installed and, during execution, the program accesses data stored on a remote database server. Although you have some wiggle room here, the workstation handles the presentation and application logic, and the database server acts as a data store. Figure 3-1 shows how a client-server configuration works.
Figure 3-1: A client-server application.
In Figure 3-1, the workstation (client-tier) handles the application logic and presentation to the user. Application logic may be implanted via many different languages, but common examples include PowerBuilder, MS Visual Basic, Java applications, and even some versions of Oracle Forms and Reports. When these client-side applications need data, they access the database via ODBC (Open Database Connectivity), JDBC (Java Database Connectivity), or Oracle Net by using client-side tnsnames.ora files. These database communication protocols allow connectivity from any client to any database, including Oracle.
On the database tier, the database stores the data and, via users, roles, and permissions, it provides that data to the application in response to SQL queries and data manipulation language (DML) statements (which are simply SQL statements that manipulate, or change, the data). Depending on whether you’re using a fat or thin client, some of the application logic and processing may be off-loaded to the database tier. Processing on the database server often makes sense because a database server can do much more intensive processing and number-crunching than even the largest workstation. Data processing is commonly executed via database procedures, functions, and packages, which process the data into a smaller result set to be returned to the client for presentation to the user.
Many people have claimed that client-server is dead. If it is, why are so many client-server applications still out there? Sure, the client-server architecture is older, and many newer applications exist in the multi-tier world. However, a simple client-server application still meets the immediate needs of a business in many situations. Also, the client-server application may be an existing legacy application that does its job — so, the business has no need to upgrade. Regardless, although we don’t recommend developing new, large-scale systems on this model, we can’t deny that client-server applications still exist in many organizations. Lastly, as a reader of this book, you may not be here to implement a new system. You may be here because you have to understand Oracle and how it works in an existing client-server application environment.
Multi-tier applications
Multi-tier applications are the current industry standard and compose multiple web, application, and database servers providing content to thin clients with presentation via a web browser. Ever wonder what’s behind the scenes when you log in to a web application for online purchases or banking? Well, it looks something like Figure 3-2.
Figure 3-2: Multi-tier architecture.
In Figure 3-2, the client-tier is merely a web browser accessing a web server. Displaying content to the user is the primary purpose of the client in this architecture; no actual processing occurs at this layer within the browser. Presentation occurs most commonly via HTML (HyperText Markup Language), but it can also be within a Java applet or an ActiveX component and use JavaScript for more dynamic formatting and content.
Communication from the browser to the web server occurs via HTTP (HyperText Transfer Protocol) or HTTPS for secure (encrypted) data. Web servers conceptually act as web listeners; they receive requests from browsers and return formatted result sets with little processing on their own. Once on the web server, the browser request is parsed and sent to the appropriate application server for processing. The application server component may be on the same physical server as the web server, or it may be on another physical server. By far, the most common web server is Apache, or one of its commercial derivatives, with over 50 percent of the market share according to http://news.netcraft.com/archives/web_server_survey.html
.
At the application server level, the user request is processed using the relevant application logic. One very common method is to use a Java application server, such as Tomcat, Orion, or Glassfish. In this case, the program logic is executed inside a Java Virtual Machine (JVM), which acts as the runtime environment for the program code.
Another popular tool is Oracle Fusion Middleware (OFM). Within OFM, the program may run as Oracle Forms, Reports, Discoverer, or even Java via Oracle Containers for J2EE (OC4J). Regardless of the product, it’s within the application server component that the application logic is executed.
During processing on the application server, it’s common to need database access to query, create, update, or delete data. The application server communicates with the database server via protocols, such as JDBC or Oracle Net, to access the data. During this time, the application server is accessing the database on behalf of the user making the application request. Rather than connecting as a named, distinct user such as JSMITH, the application server connects using a generic web account (such as WEB_USER). Multiple simultaneous connections from the application server to the database form a connection pool that allows any database connection to access data for a request. Connection pooling is a performance benefit because only a few database connections can service thousands of requests on behalf of many users.
When logged into the database instance, the generic web user queries or executes DML on behalf of the application server, which is processing an actual user request. The connection pooled web user doesn’t have schema ownership into the database; it has only those permissions needed to access or update data on behalf of the application server. During this time, normal database roles, permissions, and grants are used. Additionally, database program logic implemented in PL/SQL via procedures, functions, and packages is often executed.
After the data result set is generated on the database-tier, it’s passed back to the application server for more processing. Next, the results are relayed back through the web server and across the network for presentation to the user via their web browser.
Sounds complicated with all the various components? You may think so at first, but good reasons exist for breaking the system into web, application, and database components:
You can use components from different vendors in a “best of breed” configuration. For example, you can use a free Apache web server instance coupled with Tomcat or Glassfish for a cheap application server component. Then tie that to the power of the Oracle database, and you have a solid system at lower costs!
As more users come online, you can add more web, application, or database server instances to boost your processing power. Rather than buying bigger servers, just buy smaller servers.
After you have a series of multiple servers, you gain fault tolerance. This is called clustering. If a web server crashes or the application server needs maintenance, no problem — the redundant servers will pick up the workload.
Hopefully, these benefits show why multi-tier system architectures are the industry standard and have surpassed client-server systems.
Component configurations
In client-server and multi-tier systems, the Oracle database was the core of the system because it holds the data. Existing as the primary data store for the entire system is the most common use of an Oracle database, but it’s not the only time you’ll have to install Oracle. For example, often, these databases are in a supporting role, acting as secondary data stores for larger Commercial Off-The-Shelf (COTS) applications. In these cases, Oracle databases act as repositories storing specialized data for use within a larger system. During installation of the larger system, the Oracle database is installed as a supporting component.
One common example of an Oracle repository you may be familiar with is Oracle Designer. You can use this Oracle developer tool to design, create, and store application code (among other things), and it resides on the user’s desktop. When the user starts Oracle Designer, it prompts for an Oracle repository to connect to, and the user specifies that information. It is within that repository that all the objects to be used by the Designer desktop are stored. In this case, Designer is following the client-server model described in the section “Client-server applications,” earlier in this chapter.
Oracle Internet Directory (OID) is a more current example of Oracle acting as a subcomponent within a multi-tiered environment. OID is the Oracle implementation of an LDAP (Lightweight Directory Access Protocol). LDAPs are hierarchically defined (not relational) data-stores (not databases) that allow systems quick lookup access of data. A common example is an e-mail address book, which doesn’t contain a lot of updates or deeply layered data — it’s just a need for quick lookups of a piece of data, which is the core use of an LDAP.
Another common LDAP use is to store users and their credentials so that web application servers can simply look up a person to see whether she is authorized to access a system. After all, you don’t want to allow just anyone into your system! This credential verification creates a need for the Oracle Fusion Middleware products (OFM), and an LDAP is the solution. And, of course, with Oracle being a database company first and foremost, it opted to put its LDAP implementation inside an Oracle database, which is OID. (See Figure 3-3.)
Figure 3-3 shows how a specialized Oracle database can provide authentication via OID/LDAP for a larger system that also happens to use Oracle for the backend database where traditional customer data is stored. The OID is just a necessary component in a larger system.
Figure 3-3: A component architecture with Oracle Internet Directory.
The idea of this section isn’t to make you an authority on Oracle Designer or OID. Rather, it’s to show you that Oracle is more than just “the database” for large applications; Oracle also appears in critical support roles. Your Oracle installation may be for one of these support components, but don’t discount the importance of such a database. Without the supporting Oracle component database, the overall system would not be functional.
Verifying System Requirements
Oracle databases are very good at storing and accessing data, but a little prep work helps them to run even better. Before installing the Oracle software, you need to do some homework to ensure that your server can support the software. We cover a basic Linux installation in Appendix A, but read and understand this chapter before jumping to the software installation.
Each release of Oracle databases is better than the previous one, but each version also has minor updates to the installation requirements. Oracle does a good job of documenting these updates for the myriad versions it supports. This information can be found on the documentation website at
www.oracle.com/technology/documentation/database.html
Pay particular interest to the Installation Guide and Quick Installation Guide for your operating system (OS).
User and directory requirements
On UNIX- and Linux-based systems, the software is installed and configured as a specific user and group. In most cases, the user is called oracle, the primary group oinstall, and the secondary group dba. Here’s a sample of how this user is defined:
$ id
uid=501(oracle) gid=501(oinstall) groups=501(oinstall),502(dba))
It’s common to have multiple versions of Oracle running on the same machine simultaneously. To avoid chaos, you need to organize how and where each version is installed. The framework commonly used to organize and install Oracle software is called Optimal Flexible Architecture (OFA). As the DBA, you can use this organizational hierarchy to install the Oracle software based on software versions and common directories used by all versions.
Key to the OFA is the directory environment variables ORACLE_BASE and ORACLE_HOME:
ORACLE_BASE is where you can find common software used by all Oracle software versions; it’s the base of underlying Oracle code trees.
ORACLE_HOME is a subdirectory and denotes the location where a specific version of Oracle database software is installed, often associated with one or more database instances.
Here’s the hierarchy:
/directory name/app/oracle/product/version number/actual software_version_number
Table 3-1 describes each level of the hierarchy.
Table 3-1 OFA Hierarchy
Level |
Description |
directory name |
Base directory, file system, or drive name. |
app |
Directory name denoting application software will be located in this tree. |
oracle |
Owner of the software and is defined as ORACLE_BASE. |
product |
Holding directory for software trees. |
version number |
Directory with unique version number containing the actual software installation. Defined as ORACLE_HOME. |
Here’s an example of how this hierarchy may exist on UNIX or Linux:
/u01/app/oracle/product/12.1.0
And on Windows:
d:apporacleproduct12.1.0
When you install the database software with the Oracle installation tool (Oracle Universal Installer), it guides you through identifying these locations, but, you need to understand why each location is defined so you can better organize your software installations. We introduce the Oracle Universal Installer in Appendix A.
In the ORACLE_BASE directory, an Oracle Inventory directory is created as oraInventory. Within this directory, Oracle logs a record of all Oracle software that has been installed, patched, and removed from the server. This information is used so that the Oracle Universal Installer (OUI) and the OPatch utility can track software dependencies during installation and patching operations. The Oracle Inventory is managed automatically by the OUI and OPatch utilities.
Underneath ORACLE_BASE is an admin directory with named subdirectories for each Oracle database, as well as backup, config tool logs, the Fast Recovery Area, and product directories:
$ ls $ORACLE_BASE
admin backup cfgtoollogs diag fast_recovery_area product
Of particular importance, under each ORACLE_BASE/admin/database name subdirectory is directories for auditing, Data Pump configuration files, configuration, and Oracle Security Wallet files:
$ ls $ORACLE_BASE/admin/*
/u01/app/oracle/admin/db01:
adump dpdump pfile xdb_wallet
/u01/app/oracle/admin/dev12c:
adump dpdump pfile xbd_wallet
Table 3-2 shows you directories for auditing, Data Pump, configuration, and Oracle wallets.
Table 3-2 Database admin Directories
Directory |
Purpose |
adump |
Audit file location. Can generate many files, but are generally not very large. |
dpdump |
Location for Data Pump utility. |
pfile |
Location for database startup configuration files. |
xdb_wallet |
Oracle wallets storage area. These are security devices. |
In previous versions of Oracle, bdump, cdump, and udump directories appeared underneath each database admin directory storing alert, trace, and core dump files. However, starting in Oracle 11g and continuing so in 12c, these directories appear in trace, alert, and incident subdirectories under the $ORACLE_BASE/diag/rdbms/database name directory. In Figure 3-3, you see the location of key trace and alert files.
Table 3-3 Trace and Alert File Locations
Directory |
Purpose |
alert |
Location of the ever-important activity log file for your database (XML Format). |
cdump |
Location of core dump files. |
trace |
Location of database or user-generated trace files reflecting an error event. Replaces bdump and udump directories. |
incident |
Location of additional trace files (Plain Text Format). |
Oracle manages software installations based on their ORACLE_HOME directories. Multiple ORACLE_HOME directories can exist on a server, each corresponding to a different version of the database. Different versions can generally coexist without conflict as they only share the Oracle Inventory, oratab file, and database listener process. This separation of the software into different directories allows this separation and management to occur. Here’s an example of multiple ORACLE HOME directories:
$ ls -1 $ORACLE_BASE/product
12.1.0
11.1.0
10.2.0
In the preceding example, you see multiple ORACLE_HOME directories installed into different directories. Defining your environment variable settings to point to a specific ORACLE_HOME determines which one you’re using.
Database files (data, index, control, redo) are preferably stored in separate file systems allocated specifically for this purpose and separated by database names:
/u02/oradata/dev12c
/u03/oradata/dev12c
/u04/oradata/dev12c
The oracle user in group dba needs to be able to read, write, and execute to the ORACLE_BASE and ORACLE_HOME directories, subdirectories, and files, as well as the database files themselves. If other users on this server need to execute programs on the server side, such as SQL*Plus or export/import or SQL*Loader, they need execute permissions on corresponding executables and, in some cases, libraries.
Hardware requirements
Oracle software requires a minimum amount of memory, virtual memory, CPU speed, and disk space to install successfully. If you lack these requirements, at best, the software will run slowly; at worst, it may not even install at all.
Several vital server requirements to check include the following:
Memory: The working area for programs as they execute, memory is key to fast performance. The kind you care about here is Random Access Memory (RAM), and it’s measured in megabytes (MB) or, more commonly, gigabytes (GB). Oracle database SGAs are memory pools. Having large amounts of memory available allows you to have larger SGAs. The more memory you have available, the more options you have when managing the ever important SGA.
Virtual memory: When a program or data is being executed, it’s stored in memory. When that same program isn’t actively being executed but will be momentarily, it’s stored in virtual memory (for MS Windows) or swap (for UNIX/Linux operating systems). This system administrator-defined disk area operates as a slower extension of memory. Generally, virtual memory is sized to between 3⁄4 to twice the size of installed memory.
CPU speed: The clock speed of your CPU (central processing unit) is important. If the CPU is old (and slow) and is laboring just to keep the OS running, then adding an Oracle database isn’t a good idea. Additionally, if so many other programs are running and consuming the CPU, you can have problems trying to run Oracle. For as much hype as you hear about CPU speeds, a better solution than having one fast CPU is having multiple CPUs; even if they are a little slower, more CPUs are better than fewer.
Disk: The disk is where the Oracle database software is stored — essentially on your hard drive. The disk is only where your Oracle software itself is installed; it’s not where your actual database files will exist with all your data. Oracle software installations take only a few gigabytes, but actual databases can take terabytes.
Like most software, a minimum value is listed by the vendor but more is generally better. Table 3-4 lists the minimum hardware requirements for 12c databases.
Software requirements
Your OS version must meet the Oracle requirements. Being close isn’t good enough. Oracle 12c is currently supported to operate on the following requirements in these specific Windows and Linux operating environments:
Windows Server 2008 x64 — Standard, Enterprise, Datacenter, Web, and Foundation editions
Windows Server 2008 R2 x64 — Standard, Enterprise, Datacenter, Web, and Foundation editions
Windows 7 x64 — Professional, Enterprise, and Ultimate editions
Oracle Enterprise LINUX 5.0 and 6.0
Red Hat Enterprise LINUX 5.0 and 6.0
SUSE LINUX Enterprise Server 11.0 SP2
www.oracle.com/technetwork/indexes/documentation/index.html
Also consider that an OS has software bug fixes applied to it in the form of patches, which create a patch level. Patches aren’t a negative reflection of any particular operating system; they’re simply part of the software development lifecycle. Oracle requires a specific minimum patch level per OS for the database software to even install.
It’s common to have the system administrator apply software patches before the Oracle installer will execute. Hopefully, your system administrator routinely applies patches as they become available so that your OS is relatively current. Keep in mind that often a server needs to be restarted for the OS patches to take effect. The ramification is that if you need a patch applied, you may have to schedule time for a server to be restarted, which, depending on your organization’s policies, may take several days or weeks.
How do you know what patches need to be applied? One way is to check the Oracle Documentation Installation and Configuration Guide as it lists the minimum requirements. Sometimes, though, the requirements change faster than the documentation, and you need to check the Release Notes for detailed updates. These notes appear on the Oracle website under Installing and Upgrading for your specific OS version (www.oracle.com/pls/db121/homepage
) or on the software installation media.
$ runInstaller -executeSysPrereqs
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 500 MB. Actual 27861 MB Passed
Checking swap space: must be greater than 150 MB. Actual 3924 MB Passed
Checking monitor: must be configured to display at least 256 colors
Storage requirements
Your ORACLE_HOME directory hosts your software files and binaries. When installed, the ORACLE_HOME doesn’t grow excessively except for when patches are applied. The ORACLE_BASE grows some during logging operations and even more if trace and core dump files are generated. However, it’s the actual database files that can take lots of space and grow rapidly.
Many smaller databases are installed on whatever disk space is available on the server (called internal drives). Cramming multiple, smaller databases onto internal drives is often not optimal for several reasons:
You have negative performance impacts when database files are on nondedicated disks.
Internal disks are often not as fast or flexible as external disk solutions.
You need to consider special backup and recovery issues because these files have different backup requirements than other files. (See Chapter 8.)
Despite these issues, many people still cram their databases onto internal disks until their databases grow too large.
Storage arrays can be complex devices, but they offer many benefits. Using attached storage allows your database to grow because the storage administrator can allocate more space as needed. The reading and writing of data is often buffered in memory on the array to increase performance. Advanced configurations of disk mirroring and stripping are also available.
In addition to internal drives or attached storage, Oracle provides you three choices when determining what kind of disk to store your Oracle database files on:
Raw devices: These unformatted (uncooked) disk partitions don’t have an existing file system structure. While they’re necessary for some advanced Oracle configurations and offer a performance improvement, they’re difficult to manage and administer. Many people feel those negatives outweigh the benefits.
Automatic Storage Management (ASM): A step up from raw devices, with ASM, Oracle manages the disk for you. It uses partitioned disks, but Oracle sets up the disk groups and spreads the data across them to improve performance by balancing disk Input/Output (I/O) operations. The idea is to offload the work of managing the disks from the system administrator and place it in control of Oracle.
File system: The opposite of raw devices, these formatted (cooked) disk partitions have traditional mount points and directories like most people would expect. This disk is by far the most common type of disk configuration because it’s easy to use, intuitive, and standard for most servers. Although raw and ASM-based systems offer benefits, traditional file systems are still the de facto standard.
Other requirements
Oracle databases don’t operate in isolation merely for the edification on the DBA; they operate to support a computer system, which in turn meets a business need. Identifying the details of the computer system the database must support will likely identify some unique requirements.
The following sections describe common examples of additional requirements and questions to ask yourself, the application team, or other infrastructure personnel before installation.
Oracle version
What version of Oracle is needed for this system? It’s common to use the newest version of the database available, but is the application software certified for that version? Often times, a Commercial Off-The-Shelf (COTS) software package may not be tested and certified by the vendor to run with the latest version of a database. Although it may work fine, you don’t know until it’s tested. Plus, many organizations are mandated to operate only in vendor-supported configurations. Running a vendor supported configuration means you use a certain version of Oracle database to support the application software, even if it’s not the most recent release.
Oracle patches
Oracle software comes as a base release, such as 12.1.0.1.1, but then you’re expected to apply patches to get a more stable and secure version, such as 12.1.0.1.2. These patches typically come in the form of Oracle Security Patch Updates (SPUs) or Patch Set Updates (PSUs), which are released quarterly (January, April, July, and October).
These patches may fix both software bugs and security vulnerabilities. Oracle expects you to install the base version of the software first, and then apply whatever is the most recent SPU patch (such as July SPU 2013). You don’t have to apply previous SPU patches; the fixes are cumulative, so the most recent SPU will do.
Although SPUs are the most common patch, sometimes Oracle provides what are commonly referred to as one-off patches. These patches fix only a specific bug and are included in future Oracle versions or non-security related patches.
Network connectivity
Who is connecting to the database and how? Connecting to the database has more to it than just updating the local tnsnames.ora files with the connection information.
If you’re operating in the two-tier client/server model, people will be connecting to the database directly by using OracleNet protocol (sometimes still called SQL*Net) and connecting on port 1521 or 1526. If you’re operating in a multi-tiered web architecture, the application server is connecting to the database on behalf of the users, probably via JDBC.
The question is, are these communication ports open on the firewalls for the users or application servers to access the database? Getting firewall ports open for users requires coordination with the network staff and security, which can sometimes be an issue.
Security
You need to follow any company wide security procedures for before, during, and after installation, and verify these procedures have been followed. Know whether an audit trail is required and, if so, completed. For example, is your company bound by any regulatory compliance such as Sarbanes-Oxley or HIPPA?
Many organizations have additional security procedures that need to be applied. You should consider these procedures before installation, as well as any impact they may have on the end product. It’s not uncommon to have to uninstall some components, lock accounts, or change file and directory permissions after the installation.
Application
The database holds data, but it also contains PL/SQL packages, procedures, users, and grants/privileges to control access and processing of that data. Via SQL scripts and data loads, the DBA will load these objects and data into the finished database itself. You generally have either a client-based application or a web application server that accesses the database. The DBA and other application administrators will compile, install, and configure these components to access the database.
Automated batch jobs or programs may also be part of the build process. If the application is part of a commercial package, these steps are likely well documented along with any special requirements that need to be met. In cases of a home-grown application, the application developers and architects will develop the documentation and then provide the DBA with the proper steps to execute the application configuration. When you’re done, don’t forget to test and validate that the system works properly before turning it over to the users.
Backups
No planning session would be complete without consideration for database backups. The size and activity level of the database, sensitivity of the data, and availability and recovery requirements all drive the type and frequency of backups. In some cases, these backups take the form of traditional cold and hot backup scripts written in-house or downloaded off the web. In many other cases, you’re using Oracle’s preferred backup utility, Recovery Manager, to schedule and run various backups. And, of course, you need to store these backups somewhere or write them to tape or other media. (For more on backup methodology, see Chapter 8.)
18.219.4.174