1. Start with the end in mind.
—The 7 Habits of Highly Effective People, by Stephen Covey
Your goal as Oracle administrator is not simply to create a database but to be on time, be on budget, and meet the availability and performance targets of the business. As with any goal, careful planning is the key to success. You have little control over a number of factors that affect the success of your database: for example, application design and testing. This chapter discusses three important issues that are definitely within your circle of influence and that you cannot afford to ignore: licensing, architecture, and sizing.
Oracle provides a choice of licenses, ranging from free licenses to licenses for basic functionality or more advanced functionality, and a wide range of individually priced extra-cost options. You do not need licensing keys to unlock Oracle software; anyone may freely download software from the Oracle web site and begin using it. This is an unusual practice that separates Oracle from other software vendors and has probably contributed to Oracle’s success in the marketplace. It is, therefore, very easy to make the mistake of using software for which you do not have the required licenses. Make sure the licensing question is answered as early in the software development project as possible.
Suppose you are considering using a high-end configuration of four Sun UltraSPARC T2 servers—each with four four-core CPUs—for an e-commerce web site. For extra horsepower and reliability, the production database will be handled by two servers clustered together using Oracle Real Application Clusters (RAC) technology. For protection against a catastrophe such as fire or flood, the third server will be located in a separate data center and will handle a standby database, which will be kept synchronized with the production database using Oracle’s Data Guard technology; you can also use the standby database for reporting purposes and backups if the Active Data Guard option is purchased. The fourth server is needed to house multiple development and testing databases.
Table 4-1 shows the license and support fees I calculated for a high-end configuration using the pricing document on the Oracle web site when I wrote this chapter; the prices are subject to change, so you should always download the latest version of the pricing document. I’ve included typical options that you might consider licensing, such as table partitioning, Diagnostics Pack, and Tuning Pack. Note that because each CPU has multiple cores, you have to multiply the number of cores by a factor of 0.75 to compute the number of equivalent CPUs that must be licensed. You can find more information on the treatment of multicore CPUs in the pricing document listed at the end of this chapter; AMD and Intel CPUs are treated differently than Sun CPUs.
Table 4-2 shows the prices I calculated for a low-end configuration consisting of four Windows servers, each with two Intel Xeon four-core CPUs.
The calculations may lead you to select a different hardware configuration or to license only the options that you really need. However, licensing costs, support fees, and feature sets are not the only considerations when making technology choices; development costs, personnel costs, hardware compatibility, and performance benchmarks are some of the other considerations.
You can freely download Oracle database software from the Oracle web site and use it for self-education, evaluate its suitability for a project, or develop a prototype of an application. The following language is found at www.oracle.com/technology/software/index.html:
All software downloads are free, and each comes with a Development License that allows you to use full versions of the products at no charge while developing and prototyping your applications (or for strictly self-educational purposes).
No license keys are required to unlock Oracle database software, there is no limit to the length of the evaluation period, there are no restrictions on the use of the product, and you don’t need to provide information about yourself or your company if you don’t want to do so. Oracle does not appear to be worried about the potential for illegal use of its software and stands alone in the software industry in allowing its product to be downloaded and used in this way; some of its commercial success can undoubtedly be traced to its liberal download and usage policies.
Just as Microsoft does with Windows Vista, Oracle packages its software into different versions. Those who need only basic database functionality can buy a less expensive edition than a large international corporation that needs high-end features. Here are some of the editions that you can choose from:
Oracle licenses can be purchased on the Web at http://store.oracle.com. However, it is conventional to contact an Oracle sales representative so that discounts can be negotiated on large purchases. If your organization already uses Oracle, you have an assigned sales representative. If your organization is using Oracle for the first time, you can get in touch with a sales representative by calling the toll-free number listed on the Oracle web site.
Note Further licensing topics that this book must omit for lack of space include the named user metric, Personal Edition, Standard Edition One, and term licenses. I also do not have space to list the features that are bundled in the various editions and the restrictions on each edition. The documents containing all this information are listed at the end of this chapter.
As already indicated, Oracle software can be freely downloaded from the Oracle web site. Media packs (CD sets) can also be ordered from the online store for a nominal price. Reference manuals can be downloaded in PDF format from the Oracle web site at no charge, regardless of whether you buy licenses for Oracle software. Of particular interest to you at this stage are the installation guides for various operating system platforms. Printed copies of the reference manuals can be purchased if necessary, but they are quite expensive.
Oracle provides a variety of architectural choices to suit every need. The choice of architecture is determined by factors such as performance, availability, and scalability. For example, if you require very high availability, you could consider Oracle’s Data Guard architecture. If you need to start small and scale to very high volumes, you could consider Oracle’s RAC architecture. The following sections explore the most common architectural choices for Oracle databases.
This is the simplest Oracle configuration as well as the most common. It requires that each connection to the database be handled by a dedicated Oracle process; if 100 users were connected to the database, then 100 Oracle processes are required to handle them. You use dedicated server architecture when creating a database on your laptop in Chapter 6.
The dedicated server architecture does not work well for large numbers of connections because the Oracle Database processes contend for the limited amount of RAM. Large numbers of database connections are typically observed in Online Transaction Processing (OLTP) situations, but most of the connections are typically idle most of the time.
For example, suppose that all the employees of a company use the same computer program on their individual workstations to do their daily work. They remain connected to the database throughout the day, but each of them usually makes a very reasonable number of requests for information. The total time required to process each user’s requests is generally a very small fraction of the time the user is connected.
In such a situation, shared Oracle Database processes can be used to conserve RAM. The program running on the user’s workstation communicates with an Oracle Database dispatcher process instead of a dedicated Oracle Database process. The dispatcher process places the request on a request queue. Any available shared process handles the request and places the results on a response queue. The dispatcher then communicates the results to the user. Any information that has to be preserved until the next request—that is, any state information—is preserved in the SGA.
A computer can efficiently handle only a certain number of requests at a time; this number is greatly dependent on the number of CPUs and the amount of RAM. As the number of requests increases beyond a certain threshold, processes begin competing for scarce resources, and all of them suffer. The easiest solution is to increase the number of CPUs and the amount of RAM, but large and powerful computers are also very expensive.
RAC is a technology that combines the resources of more than one computer. Two or more Oracle Database instances share access to the same set of disks and coordinate with each other over a fast network. Additional instances can be added to the cluster as the workload increases. This allows you to start with cheap commodity hardware and scale out as the workload increases instead of scaling up to a more powerful (and more expensive) computer.
RAC also has implications for database availability. For example, any single instance can be shut down for hardware maintenance or OS patching without affecting the availability of the database. In some cases, it is even possible to apply Oracle patches in rolling upgrade fashion.
A standby database can improve application availability. In this scenario, redo information from the main database is shipped and applied to another database, called the standby (which started life as a clone of the main database). In the event of a primary-database outage, applications can use the standby database. Applications can also be switched to the standby database when hardware maintenance or operating system maintenance needs to be performed on the primary database; even the outages associated with Oracle patch sets and upgrades can be avoided. Note that maintenance of the standby database can be automated and simplified using an Oracle product called Data Guard, which is part of Enterprise Edition.
To save money, it is typical for the standby database to have less hardware resources (CPU and memory) than the primary database; it is also typical to use a non-RAC standby database for an RAC primary database. Another interesting option is to use an active-active configuration of two or more computers. A typical active-active configuration has two databases, each on a separate server; the standby database of the first database is placed on the second server, and the standby database of the second database is placed on the first server. Each server thus hosts a primary database as well as a standby database. If one server suffers an outage, the standby database on the other node can be activated; this is a very cost-effective way of using hardware resources.
Maximum Availability Architecture (MAA) can be used when both availability and performance are important and budgets are generous. The typical standby configuration uses less hardware resources for the standby database because the probability of a switchover is low; that is, there will be some amount of degradation in performance if the applications are pointed to the standby database. The MAA configuration combines RAC technology with standby technology and uses identically configured primary and secondary sites. The primary and standby databases both have the same number of RAC nodes.
MAA can be extended to the application tier, too; the details are in the white paper listed at the end of this chapter. Of course, identically configured primary and secondary sites (both database and application tiers) can be extremely expensive.
The hardware resources your database needs depend on a number of factors such as the characteristics of the application (for example, OLTP vs. OLAP), the expected amount of activity, and the performance targets (such as the response time of business-critical transactions). However, it is rare to get good information on the expected amount of activity; ask how many simultaneously active sessions can be expected on average, and you may get a blank stare in return. Even if you have good information, sizing can still be tricky. Here are some strategies you can use:
It is easy to underestimate the amount of space you need for the database. Another common mistake is not to leave sufficient room for growth. The following sections describe the different types of files that you have to worry about in forecasting disk-space needs for a database.
Data files store your tables and indexes, and therefore you need to have some idea of how much data will be stored in tables in the foreseeable future. To estimate the amount of space required for a table, you have to estimate the average size of each row of data and the number of rows in the table. A reasonable rule of thumb is to allow as much room for indexes as you allow for tables. There is a fair amount of overhead space and white space in tables and indexes, and it would not be unreasonable to estimate that only 50% of the allocated space is usable. To summarize: estimate the amount of space required for tables, then double the number to allow for indexes, and then double the number again to allow for overhead space and white space.
You also need to allow room for the SYSTEM and SYSAUX tablespaces used by Oracle. Oracle uses the SYSTEM tablespace to store the data dictionary and the SYSAUX tablespace for other kinds of management information: for example, Active Workload Repository (AWR).
If you do not have a license for the Diagnostics Pack (which is required in order to use AWR), you can and should use a free Oracle-provided alternative called Statspack to collect performance data. In this case, you should reserve a sufficient amount of space for Statspack data.
You must also allow a sufficient amount of undo and temporary space. Undo refers to a copy of a data block that is made before the block is changed; it is used to restore the block in case the transaction does not commit its work. Temporary space is used for sorting operations during SQL queries. Don’t forget to leave a margin of error, especially if your estimates are not very reliable.
The space required for control files and online redo log files is usually modest; 2GB should suffice for most databases. In a highly demanding OLTP environment, you should pay careful attention to the placement of these files because they can quickly become a performance bottleneck.
Redo logs contain the information necessary to redo the database; they hold a record of all changes to the database. When redo logs fill up, they have to be copied to the archive destination before they can be reused. The amount of space required for the storage of archived redo logs depends on the amount of activity in the database; it also depends on retention preferences. An amount of space equal to the size of the database is not unreasonable for a busy OLTP database.
Allocating an area twice the size of the database for backups is not an unreasonable thing to do, unless you plan to put backups directly onto tape.
You must allocate some space for the Oracle software and for various categories of error logs such as the alert log, listener log, and trace files. A reasonable rule of thumb for most databases is 4GB of space.
There’s more to worry about when it comes to files than just the amount of space they take. Usable space, file placement, and disk speed are some of the issues to consider.
When specifying the amount of space required for data, be clear that you are talking about usable space. For data protection, most disks use some form of redundant array of inexpensive disks (RAID) layout, which reduces the amount of usable space. RAID 10 (mirroring plus striping) reduces the amount of usable space by 50%. RAID 5 has the most usable space but reduces write performance; the details are outside the scope of this book. RAID 10 is the best choice for databases.
Some attention must be paid to the placement of different categories of files; for example, data files should not be placed on the same file system where software, archived redo log files, or backups are stored. Optimal Flexible Architecture (OFA) is a set of recommendations provided by Oracle for the placement of different categories of files. OFA is automatically used by Database Configuration Assistant (DBCA) when creating databases.
Pay attention to your disk ratings, because disk I/O can be a big performance bottleneck. If it takes 10 milliseconds to retrieve 1 data block (typically 8KB in size), then an SQL query may not be able to read more than 100 data rows per second, because the required rows will probably be spread out over many data blocks.
Memory sizing is heavily dependent on the characteristics of your application. The amount of space you need to meet performance targets is very difficult to estimate unless you have good information, such as the results of volume testing. The cost of memory is a consideration, but memory becomes cheaper every year, and it is no longer unusual to find databases using many gigabytes of memory. Here are the main considerations when planning memory requirements:
The number of CPUs you need depends not only on the speed of the CPUs—the faster the CPUs, the fewer you need—but also on the application load. A good rule of thumb is as many CPUs as the average number of simultaneously active sessions during the critical period of the day; the database for a big e-commerce application may require 16 CPUs to meet performance targets.
Note that if you use more than four CPUs, you will need to purchase Oracle Enterprise Edition, which is far costlier than Oracle Standard Edition.
Finally, you must give some consideration to network requirements. A database that services hundreds of connections may require multiple network cards to meet performance targets. Fast connections are required to the storage area network (SAN) and between the nodes in an RAC cluster.
Recent years have seen several innovations that reduce the complexity and cost of provisioning Oracle Database: engineered systems and various flavors of cloud computing.
Instead of designing and implementing your own hardware and software configuration, you can simply buy a complete hardware and software package—a.k.a. an engineered system—directly from Oracle Corporation. Two examples are Oracle Database Appliance (ODA) and Exadata.
Deploying RAC is a complex endeavor. ODA is a two-node plug-and-play RAC cluster running Enterprise Edition; it reduces typical deployment times from weeks to less than a day. ODA eliminates or automates the following classes of complex and error-prone work involved in a typical RAC deployment:
ODA offers more advantages than simplified provisioning. Oracle licenses can be purchased using the “pay-as-you-grow” model; that is, you can license only the number of CPU cores you need and disable the rest. Also, software patching is automated for all tiers (BIOS, firmware, operating system, and database).
If you are planning to use RAC and Enterprise Edition, you should check whether ODA has enough capacity for you .in terms of CPU cores, storage, and IOPS. If so, there is simply no reason for you not to use ODA. Each new generation of ODA has more capacity than the previous generation; at the time of writing, each of the two nodes in ODA has two 12-core Intel Xeon processors and 256GB of memory, and the configuration includes 9TB of usable storage (doubly mirrored) and four 200GB solid-state disks for the online redo logs.
Exadata is the big brother of ODA and should be considered if your organization has outgrown the capacity of ODA. It comes in four progressively more powerful configurations: eighth-rack (two RAC nodes), quarter-rack (two RAC nodes), half-rack (four RAC nodes), and full-rack (eight RAC nodes). Like ODA, each new generation of Exadata has more capacity at the previous generation. At the time of writing, each Exadata node has two 12-core Intel Xeon processors (in an eighth-rack configuration, only half the number of cores are enabled on each server) and up to 512GB of memory. A full-rack configuration also uses 14 storage servers each with 12 CPU cores, 12 × 1.2 TB 10,000 RPM disks (or 12 × 4 TB 7,200 RPM disks), and 3.2TB of flash cache.
Expansion capabilities are available if you have even higher storage needs. This makes Exadata suitable for today’s most demanding workloads and database consolidation on a massive scale.
Cloud computing is “the delivery of computing as a service instead of a product” (Wikipedia). Gone are the days when the only option was to purchase dedicated hardware and software when your computing needs changed or increased. In a large sense, cloud computing represents a return to the earliest days of the computing era when mainframe computers and time sharing were the norm. Cloud computing not only is more economical than owning hardware and software outright but also offers the key advantage of elasticity; that it, you can acquire more computing resources as your needs grow.
In the PaaS model, you have the ability to create tables, indexes, views, and other kinds of database objects in a dedicated database schema. This schema is a tenant in a multi-tenanted Oracle database containing many tenants. Of course, each tenant is completely isolated from the others. Key aspects of database administration such as backups and monitoring are automatically handled for you. The schema is accessed using web interfaces. Oracle Corporation offers PaaS with a choice of 5GB, 20GB, or 50GB of database storage.
In the IaaS model, customers receive access not just to a database schema but to an entire server. This server is accessible over the Internet and is typically a virtual machine carved out of a very powerful server. In mere minutes, you can have complete access to a fully configured and operational Linux or Windows server containing a fully configured and operational Oracle Database instance. As your needs grow, you can add CPU cores, memory, or disk storage. You can either bring your own Oracle Database licenses (BYOL) or pay a per-minute charge for each minute the servers are running. At the time of writing, Microsoft Azure was charging $1.60 per hour for access to a four-core server with 7GB of RAM. Storage and network bandwidth are charged for separately. Such a pay-per-use model with no long-term contractual commitments is an irresistible attraction to businesses that wish to avoid heavy capital expenditures (CAPEX). In some service plans, key aspects of database administration such as backups and monitoring are automatically handled for you (IT as a service).
At the time of writing, the two major providers of Oracle Database servers are Amazon EC2 and Microsoft Azure, with Oracle making its first steps into the field. If your company is an Oracle shop, you will definitely want to check Oracle’s offering.
Here is a short summary of the concepts touched on in this chapter:
18.220.202.209