We truly live in the Information age. Think for a moment about all the data that exists about you in computers around the world:
Your name
Your birth date and information
Your hobbies
Purchases you've made
The identities of your friends
Your place of employment
Whether it's a hospital's record system, your employer's payroll system, the invoicing records of an online store, or your financial accounts, the examples are endless. Next, multiply that amount of data by the number of people in the world. The result is a truly staggering amount of information. How is it possible that all this data can be organized and retrieved? How is it possible that you can search an online bookstore and find exactly the book you're looking for within seconds? How is all of this sensitive data protected and kept secure? In today's data-centric world, by and large, it is the Relational Database Management System (RDBMS) that makes this possible. The RDBMS is the cornerstone of the information technology world. While user interfaces, web servers, and application servers are all important parts of the way we save and obtain information, it is the RDBMS that serves as the massive storehouse for the data itself. Additionally, this massive amount of data is growing exponentially. New systems and strategies are constantly being devised in order to store and retrieve this information in a way that is useful to companies. In this book, we examine every aspect of the Oracle RDBMS, the most widely-used commercial database in the world. We will look at its architecture, the way it stores data, its security configuration, and its high degree of configurability.
This book takes a from-the-ground-up approach to Oracle database administration. The first chapter will cover topics that are not specifically covered on exam 1Z0-052, Oracle Database 11g: Administration I. Rather, it serves as a foundational knowledge for readers who are new to Oracle and a refresher for those already familiar with it. However, even experienced DBAs will likely discover new information in these chapters. Because of the amount of information we cover, we place a strong emphasis on learning by example. In addition, we will focus on comprehending the information needed for the Oracle Certification with special attention on how the information pertains to the life of a real-world Oracle DBA. At the end of each chapter, we'll highlight the exact certification topics that come directly from the Oracle Certification syllabus.
In this chapter we shall:
Understand the purpose of relational database management systems
Outline the Oracle family of products
Interpret Oracle naming and versioning
Examine the role of the Database Administrator (DBA)
Identify the common tools for DBAs
To better prepare ourselves to understand the Oracle database, we should first familiarize ourselves with the history of databases and how the RDBMS became such an important part of today's IT infrastructure. We'll follow this with a look at the Oracle product family.
Imagine, for a moment, that you have telephone books for the twenty largest cities in the United States, and I ask you to find all the phone numbers for all individuals named "Rick Clark" in the greater Chicago area. In order to satisfy the request, you simply do the following:
Open the Chicago phone book.
Scan to the "C" section of names.
Find all individuals that match with "Clark" and"Rick".
Report back their phone numbers.
Now imagine that I take each phone book, tear out all of the pages and throw them into the air. I then proceed to shuffle the thousands of pages on the ground into a completely disorganized mess. Now I repeat the same request to find all the phone numbers for individuals named "Rick Clark" in the greater Chicago area. How do you think you would do it? It would be nearly impossible. The data is all there, but is completely disorganized. Finding the individuals named "Rick Clark" of Chicago would involve individually examining each page to see if it satisfied the request—a very frustrating task, to say the least.
This example underscores the importance of an RDBMS. Today's RDBMS is the system that enables the storage, modification, and retrieval of massive amounts of data.
When the devices that we know as computers first came into existence, they were primarily used for one thing—computation. Computers became useful entities because they were able to do numeric computation on an unprecedented scale. For example, one of the first computers, ENIAC, was designed (although not used) for the US Army to calculate artillery trajectories, a task made simpler through the use of complex sequences of mathematical calculations. As such, originally, computers were primarily a tool for mathematical and scientific research. Eventually, the use of computers began to penetrate the business market, where the company's data itself became as important as computational speed. As the importance of this data grew, the need for data storage and management grew as well, and the concept of a database was born.
The earliest databases were simple to envision. Most of them were simply large files that were similar in concept to a spreadsheet or a comma-separated values (CSV) file. Data were stored as fields. A portion of these databases might look something like the following:
Susan, Bates, 123 State St, Somewhere, VA Fred, Hartman, 234 Banner Rd, Anywhere, CA Bill, Franklin, 345 Downtown Rd, Somewhere, MO Emily, Thompson, 456 Uptown Rd, Somewhere, NY
In this example, the first field is determined by reading from left to right until a delimiter, in this case a comma, is reached. This first field refers to the first name of the individual. The next field is determined by reading from the first delimiter to the next. That second field refers to the last name of the individual. It continues in this manner until we have five fields: first name, last name, street address, city, and state. Each individual line or record in the file refers to the information for a distinct individual. Because this data is stored in a file, it is often referred to as a flat file database. To retrieve a certain piece of information, programs could be written that would scan through the records for the requested information. In this way, large amounts of data could be stored and retrieved in an ordered, structured way.
The flat file database system served well for many years. However, as time passed and the demands of businesses to retain more data increased, the flat file paradigm began to show some flaws.
In our previous example, the amount of information in the flat file is quite limited. It contains only five fields representing five distinct pieces of information. If this flat file database contained the data for a real company, five distinct pieces of information would not even begin to suffice. A complete set of customer data might include addresses, phone numbers, order information, the date of the order, the delivery date of the order, and so on. In short, as the need to retain more data increases, the number of fields grows. As the number of fields grows, our flat file database gets wider and wider. We should also consider the amount of data being stored. Our first example had four distinct records, not a very realistic amount for storing customer data. The number of records could actually be in the thousands or even millions. Eventually, it is completely plausible that we could have a single flat file that is hundreds of fields wide and millions of records long. We could easily find that the speed with which our original data retrieval programs can retrieve the required data is decreasing at a rapid rate and is insufficient for our needs. It is clear that this flat file paradigm needs to be revised in order to meet the growing demands for our database.
The world of databases changed in the early 1970s due in large part to the work done by Dr. Edgar "Ted" Codd. In his paper A Relational Model of Data for Large Shared Data Banks, Dr. Codd presented a new paradigm— the relational paradigm. The relational paradigm seeks to resolve the limitations of the flat file architecture by organizing our data in such a way that the data and its inter-relationships can be clearly identified. When we design a database, we begin by asking two questions: "What data do I have ?" and "How do the pieces of data relate to each other?" During this process, the data is identified and organized into entities. An entity is any person, place, or thing. An entity also has attributes , or characteristics that pertain to it.
These entities represent distinct pieces of information. We could have an Employee entity that represents information about employees, an Email entity that represents information about e-mail addresses, and so on. These entities, and any others we choose to add, make up our data model. We can also look a little closer at the attributes of a particular entity, as shown in the following diagram:
In our example, data such as First name, Last name, Address, and Branch name are the attributes of the Employee entity—they provide information about the employee. We can extend this idea to any other entities that represent a group of related information.
The true strength of the relational paradigm, however, is its ability to structure these entities in a way that forms relationships between them based on data that is common to both. Following is a simple diagram of this:
Here, we have two entities, Employee and Email, that form a relationship. The Employee entity contains information about the various employees in Companylink. The Email entity contains the e-mail addresses of these employees. Since any given employee can have one or more e-mail addresses, we say that there is a one-to-many relationship between Employee and Email, designated by the crowsfoot symbol between them. This relationship is the heart of the relational model that allows us to separate data into different entities. In the RDBMS world, we call these entities tables, while their attributes are known as columns
. We will look much more closely at tables and their characteristics in future chapters. An entire data model can be visually displayed using an Entity Relationship Diagram
(ERD). An ERD makes it easier to view the inter-relationships between entities.
When someone says, "I work with Oracle" what does he or she mean? The name Oracle (not to be confused with ORACLE, an early computer built by the Oak Ridge National Laboratory) can be used to refer to several different aspects of Oracle Corporation and its products. Oracle is a company, a database, and a family of products.
Oracle Corporation as we know it, began in 1977 as a company called Software Development Laboratories (SDL), founded by Larry Ellison, Bob Miner, and Ed Oates. These men, inspired by the relational theory set forth by Dr. Ted Codd and the work done on an IBM project known as System R, set out to develop the relational idea into a commercial product. In 1979, the company, renamed Relational Software Inc. (RSI), sold the first version of their relational database software, called Oracle, to Wright-Patterson Air Force Base. Although it was the first version of the Oracle database, it was designated as Version 2 because Larry Ellison didn't believe that customers would buy a Version 1 product. In 1982, RSI was renamed Oracle Corporation to more closely align the company with its flagship database.
For many years, the Oracle database software was primarily available on mainframe and minicomputer systems, the primary computing platforms of that time. In 1983, in a move that would bode well for Oracle's future, Version 3 was rewritten in the C programming language, making it portable to many other platforms. It was ported to the PC architecture with Version 4. The modern history of Oracle Corporation can be traced to the release of Oracle Version 7 in 1992. Version 7 released many new enhancements, including new security and performance features. It is at this point that the Oracle database software began its rise to become the most widely used commercial RDBMS in the world.
Since Oracle's inception, the Oracle Database Server has been and remains Oracle's flagship product. It has maintained its position as the world's most widely used database for many years and stands essentially unchallenged in the market today. The Oracle database is designed to be high performing, highly available, and highly secure. It can run on a variety of hardware and operating system platforms. One of the Oracle Database Server's strengths is its ability to run on a range of system types, from small departmental servers to massive symmetric multiprocessing servers to enormous highly available clustered systems. Many of the largest companies in the world run their database systems on Oracle software. Oracle currently holds a larger revenue share of the worldwide RDBMS market than its five closest competitors combined.
The primary language used to access Oracle databases is Structured Query Language , (SQL). SQL (pronounced either "S-Q-L" or "sequel") is the language most commonly used to address relational databases today, and likely will be for some time to come. The structure and syntax of SQL are governed by the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO). It is these organizations that decide, albeit with input from other companies such as Oracle, what comprises the accepted standard for SQL. The current revision is SQL 2008. Although most commercial RDBMS products generally conform to the ANSI standard, they can differ in their implementations. This means that not every RDBMS uses the exact same SQL syntax. Oracle database also supports the use of two other programming languages within the database: PL/SQL and Java. PL/SQL is Oracle's proprietary third-generation programming language. It allows programmers to write structured code that integrates easily with SQL and stores that code as objects within the database. Java is an industry standard object-oriented language used in many types of applications today. Oracle also supports the storage of Java code within the database.
Apart from the different versions of Oracle available, Oracle also offers a number of different editions of their database software. These editions primarily differ by cost and availability of features. They are as follows:
Enterprise Edition (EE):The EE has no maximum as to the number of server central processing unit (CPU) sockets allowed, as well as no limits on memory accessibility or database size. It contains a number of database options not found in any of the other editions. It is available for Linux, Unix, and Windows, and supports both 32-bit and 64-bit versions.
Standard Edition (SE): The SE is only available for servers with four or fewer CPU sockets, although, like the EE, it has no limits on memory or database size. It includes many database options, though generally fewer than the EE. It is also available for Linux, Unix, and Windows, and supports both 32-bit and 64-bit versions. The SE and the SE1 can also be licensed on a per user basis, rather than the standard per CPU core basis.
Standard Edition (SE1): The SE1, although similar in many respects to the SE, only supports servers with two or fewer CPU sockets. SE1 is available for Linux, Unix, and Windows. For smaller IT organizations that don't require large database servers, SE1 can provide a low-cost, high-performance database solution.
Express Edition (XE): The XE is a somewhat different product, an entry-level, free version of the Oracle database that lacks many of the capabilities of higher editions. It can only support one CPU and has a 1 GB limit for usable random access memory (RAM) and a maximum database size of 4 GB. It is only available for Linux and Windows, and lacks the 64-bit support. The XE is primarily targeted for developers working on applications that will eventually be deployed in larger environments.
Oracle also offers Oracle Personal Edition (PE), a low-cost, full-featured version of the Oracle database. PE is restricted to only one user per database, but provides access to all the features of the EE. Throughout the course of the book, we will use and focus on the Enterprise Edition so as to have access to the widest range of features.
Note
The real-world DBA
Although the Enterprise Edition of Oracle is the most commonly found one in the IT world, a DBA shouldn't completely discount other editions. Often, significant cost savings can be achieved for companies that don't require "all the bells and whistles." Also, although uncommon, Oracle may separately negotiate the licensing of the EE at a lower cost, provided that the license being purchased is large enough. In these situations, companies may negotiate the purchase of the EE without a license for some of the included options. This can result in a DBA being required to administer an EE database that lacks the expected features. Always check your licensing agreement.
In addition to the various editions of the Oracle RDBMS, Oracle offers a number of options, depending on your chosen edition. These options function primarily as "add-ons," and are available at an additional cost. Some of the more commonly used options are listed here:
Advanced Security : This is an option, sometimes known as ASO for Advanced Security Option, that provides encryption for both data at rest and data in transit over a network. It can also integrate with services such as Kerberos and RADIUS to provide strong authentication.
Active Data Guard : This is an enhancement to the standard Data Guard feature that provides the ability to create a standby copy of the database that can also be used for read-only business reporting.
Advanced Compression : This allows the use of internal compression routines within the database that facilitate reduction of disk use. It is especially useful in databases that store large amounts of binary data.
Total Recall : This is an add-on that simplifies the long-term storage of large amounts of historical data. This can be important for environments that are subject to compliance and accounting audits, such as the health insurance and legal industries.
Partitioning : This is a feature that allows a DBA to take a very large table, with perhaps many millions of rows, and convert it into a partitioned table. A partitioned table is one that actually exists as a number of smaller tables that can be seen as one. Under certain circumstances, this can dramatically improve the performance of queries, particularly in data warehousing environments.
Database Vault : This is an option that extends Oracle's auditing capabilities and allows for extremely fine-grained access control over the database environment.
Real Application Clusters : This is a popular enhancement, usually referred to as RAC, which allows database servers to be clustered together and appear as one large database. RAC can be an essential component for a high-availability database environment.
Although the Oracle Database Server is the cornerstone of their product offerings, Oracle has offered additional types of software products for many years. Recently, however, Oracle has acquired an enormous number of software companies to add to their product family. In 2009, Oracle acquired Sun Microsystems, signaling an important shift in the direction of the company. With the acquisition, Oracle acquired Sun's well-respected line of hardware, as well as the Solaris operating system, the MySQL open source RDBMS, and the Java programming language. Although many in the industry believed that this move would signal the end of Sun's software product line, Oracle has continued to offer, promote, and support many of these products. As a result, Oracle now offers a staggering amount of hardware and software solutions that span every layer of enterprise IT, including hardware, operating systems, databases, application servers, and enterprise administration. An exhaustive discussion of the complete line of Oracle products is beyond the scope of this book; however, we mention some of the most important ones here.
With the purchase of Sun Microsystems, Oracle achieved its long standing desire to offer complete hardware solutions bundled with Oracle software. These hardware products come pre-installed with Oracle software and are ready to operate after a quick installation and configuration. They represent an attractive offering for customers that require new solutions that can be up and running in a short period of time. The flagship of Oracle's product line is the Exadata Database Machine . Exadata is a complete hardware/storage/database solution that provides a highly available, high performance database platform out of the box. Exadata can provide superior performance for most types of database operations, since the hardware itself is tailored to the requirements of the database. Oracle also offers the Database Appliance , a smaller, less expensive platform tailored for the Oracle Database that lacks some of the high-performance features of Exadata. For the application server side of IT, Oracle offers Exalogic and Exalytics , two hardware platforms that can be used to support application operations and data analysis, respectively.
Server virtualization has become extremely important to enterprise IT in recent years. The concept of allowing one server or a group of servers to appear as many virtual servers provides IT organizations with a way to make more effective use of expensive hardware resources. Say for instance that a company's database servers, on average, use less than 50 percent of their available memory, and run at 20 percent CPU usage. With virtualization, these servers could serve as hosts for virtualized servers and make more effective use of these resources. When users connect to these virtual servers, they see what appears to be a physical machine, when in reality they are connected to a virtual machine. For enterprises, Oracle offers Oracle VM , a complete virtualization solution that allows servers to host virtual machines. Oracle VM is based on the Xen Hypervisor technology and actually runs from a Linux kernel. For desktop-level virtualization, Oracle offers VirtualBox , which it acquired from Sun. With VirtualBox, users can create their own virtual machines on a desktop system, such as a personal computer. To do this, we simply install the VirtualBox software on a PC and create a new virtual machine. Then we install the operating system, as well as any other software we desire, on this empty virtual machine.
Note
The real-world DBA
It is interesting to note that the author runs VirtualBox on a Linux desktop and can use it to run many different operating systems, including DOS, Windows 95, and several distributions of Linux. In fact, all of the screenshots in this book were taken using Oracle databases and tools running in virtual machines from a desktop. VirtualBox can be a great way to experience and learn about different operating systems without trying to maintain multiple computers at home!
The choice of an operating system for any platform running the Oracle database is a crucial one. The operating system stands between the database and the hardware that services the needs of the database. An operating system that makes efficient use of hardware resources to serve the database can have a positive impact on the performance of the database. Although many Oracle products are supported on various operating systems, Oracle offers two different operating systems of their own.
In 2006, Oracle began offering Oracle Enterprise Linux (now simply known as Oracle Linux ), a repackaged version of the Red Hat Linux operating system. Oracle Linux, like all distributions of Linux under the GNU General Public License, can be downloaded and used for free, even for enterprise production-level systems. Oracle, like many other companies that offer Linux, also offers paid support contracts for the use of Oracle Linux. Oracle Linux is distributed with two separate kernels. The Red Hat Compatible Kernel is identical to the one shipped with Red Hat Linux. In 2010, Oracle announced the second kernel, the Unbreakable Enterprise Kernel , which includes enhancements designed to increase performance for the Oracle database.
In addition to its Linux product, Oracle offers the Solaris operating system, now known as Oracle Solaris . While Solaris is commonly associated with the SPARC chip architecture found in Sun hardware, it is also available for x86 and x86-64 chip architectures, such as the ones found in personal computers. Solaris is a true POSIX-compliant version of the Unix operating system.
Note
The real-world DBA
Since both Oracle Linux and Oracle Solaris can run on the x86 platform, you can download either of them and install them at home if you have a spare computer with sufficient resources. Additionally, both operating systems can run the Oracle Database. This can be a great way to learn about administering Oracle on a Unix-like environment.
Application servers provide an environment that facilitates the development, deployment, and execution of the various software applications that a company utilizes. This can include anything from simple web servers to more advanced middleware architectures that use Java and clustering for high availability. In this domain, Oracle has historically offered the Oracle Application Server, which was later incorporated into Oracle Fusion Middleware, a package of software products that facilitate business intelligence, SOA, and content management operations. In 2008, Oracle acquired BEA Systems and their WebLogic application server product. It has, since then, essentially replaced Oracle Application Server as Oracle's de facto standard.
Oracle also has a significant presence in the Customer Relationship Management (CRM) market. In 2005, Oracle purchased PeopleSoft, which had previously acquired JD Edwards. Adding these two products to their own CRM product, Oracle Applications, Oracle became a major player in the CRM market.
People who are new to the world of Oracle are often curious about the different versions available and what their letter suffixes (the "g" in 11g, for instance) actually mean. As we mentioned previously, the first release of Oracle Database was Oracle 2. Oracle continued the standard approach of numbering subsequent major releases of the Oracle database with increasing number values until Version 8. With Version 8i, Oracle began suffixing their version numbers with abbreviations to align their products with their marketing focus. The "i" in 8i stands for "internet" as that version contains many features that facilitate the use of the Oracle database as the backend for Internet applications. The ninth major version of Oracle was similarly named Oracle 9i. With the release of the tenth version of Oracle in 2004, Oracle suffixed the letter "g" (short for "grid") to the version, naming the release "10g", to align their product with the grid computing market. Version 11g followed this pattern as well. With the current focus on cloud computing, it is speculated that Oracle Database Version 12 will be named, "12c", for "cloud."
Apart from the common release name, since Version 8i, Oracle Database products have been versioned in an extended form as well. While the database may be "11g," its proper version name may be 11.2.0.2.0. The following table shows what this long version number of Version 11.2.0.2.0 describes:
|
Version number |
Description |
|---|---|
|
11 |
Major version (release) number |
|
.2 |
Database maintenance release number |
|
.0 |
Application server release number |
|
.2 |
Component-specific release number (akin to "patch level") |
|
.0 |
Platform-specific release number |
The Application server release number (the third decimal digit) is used only for application server releases and doesn't apply to the database version. Rather, it's a way for Oracle to standardize release naming across its product family. The platform-specific release number (the last decimal digit) applies only when a certain platform requires a release that only applies to that platform and not others. Additionally, Oracle database versions are often referred to by the information in the first two decimal digits of their version. So, if our database is Version 11.2.0.2.0, we might say that we're running Oracle 11g Release 2. If our version is 10.1.0.3.0, we could refer to it as Oracle 10g Release 1. Moving to a later major release of the database is usually referred to as an upgrade while a later release of any other components (the other digits) is referred to as a patch.
Despite all of the industry talk about "zero administration systems," DBAs are as necessary today as they have ever been, and likely more so. The basic function of an Oracle DBA is to safeguard the integrity and availability of the data in the database. However, defining the precise role of a DBA can be difficult in today's world. In the past, the DBA would be responsible for every aspect of the database, often including the operation of the hardware and operating system. In such a role, the morning of the DBA's day might be spent writing SQL statements to create new tables within the database, while the afternoon would find him or her troubleshooting issues with the disks in a storage unit. Relatively speaking, the systems of the past were less complicated and less demanding in terms of time and resources, allowing (or perhaps burdening) the DBA to be involved in all operations even tangentially related to the database. At times, the DBA was also the system administrator, the network administrator, or the programmer. While these types of DBA positions still exist, today, because of the demands of modern IT, the role of the DBA is often highly specialized. Rather than a single DBA, IT organizations today often have teams that are responsible for database administration. The responsibilities of these teams are sometimes divided based on the lines of business. For instance, one part of the DBA team might be responsible for all databases that support the finance department of a company, while another group would administer any databases related to the HR department. Each member of the team is a DBA, but different parts of the team focus on certain lines of business to provide better support.
Because of the complex nature of technology, it is not uncommon to differentiate DBAs based on their skillsets or functional roles. In the spectrum of technologists, the role of the DBA stands between system administrators on one side and developers on the other. Because of the vast difference between those two roles, it can be difficult to find DBAs who have all of the skillsets required to span that gap. In some IT organizations, DBAs are roughly divided into Physical DBAs and Logical DBAs. A Physical DBA (sometimes called an Environmental DBA) has a role that generally tracks closer to the hardware and system side of database operations, while the Logical DBA (sometimes referred to as an Application DBA) tends to work more with developers in the creation and support of the database objects required for the application to function. This delineation can be somewhat subjective, but we list a general division of responsibilities as follows:
Oracle database installation and configuration
Backup and recovery
Database tuning, as it relates to the overall system performance
Administration of the overall database physical architecture, such as server configuration, memory usage, and disk layout
Configuration of database parameters
Works more closely with system administrators
The role of the Logical DBA is as follows:
Creation and maintenance of database objects, such as tables and indexes
Closely involved in software deployments
Database tuning as it relates to the performance of SQL statements
Responsible for internal database security
Data modeling and supervision of the application data model
Works more closely with developers
Naturally, the role of any individual DBA may overlap these categories, but these are some of the divisions that have developed over time in many IT organizations.
Note
The real-world DBA
In today's IT world, it is very easy for a DBA to allow oneself to become "stuck" in a certain job role. It is always safer to stick with what you already know than to branch out into new areas. However, having a skillset that spans both categories of a DBA can be very satisfying and rewarding, since such DBAs are highly sought after. Always press yourself to learn and expand your knowledge.
What makes a good DBA? What types of characteristics are needed for the job? Being an Oracle DBA is one of the most demanding and rewarding jobs in all technologies. A DBA is afforded the opportunity to work with many diverse types of technologies, but that opportunity can require an extremely broad knowledge base. It's been said that, in IT, "everyone wants to be a DBA." However, not everyone is capable of being one. Here are a few of the traits commonly found in successful DBAs:
A logical thinker with exceptional problem solving skills: The core of database administration is the ability to solve problems, both big and small. A good DBA operates based on factual information and observation and is highly detail-oriented.
Self-confident in the face of challenges: A DBA meets challenges on a daily basis. A good one is confident in his or her knowledge, accepts his or her limitations, and moves to solve the problem, rather than folding under pressure.
Professionalism: Good DBAs are professional in every aspect of their work, from their dealings with users, to their daily interaction with other IT specialists. They value the quality of their work and their reputation as capable and trustworthy.
Effective communicator: You can't be a DBA by simply sitting in your cube with the phone off the hook. DBAs must be able to explain and communicate complex ideas, both verbally and in writing, to users, customers, and the management in a way that they can understand.
Lifelong learner: Technology is complex and is always moving forward at a rapid pace. Good DBAs are always looking forward and educating themselves for what's to come.
Although the greatest asset of any DBA is his or her own mind, it doesn't hurt to have the right tools. As a DBA in today's world, there is an abundance of administration tools from which to choose. There are benefits and drawbacks to each, but the choice of a tool is generally about one's comfort level with the tool as well as its feature set. Some tools are free, some are open source, and some require paid licenses; however, each tool uses the same syntax for SQL when it connects to an Oracle database. The following are some commonly used DBA tools:
Note
The real-world DBA
While your choice of SQL tool is an important one, in the industry it is one that is sometimes dictated by the toolset standards of your employer. It's important that you don't completely dedicate yourself to one tool. If you become an expert at one and then transfer to a different employer whose standards don't allow for the use of your tool, you may find yourself with an initial learning curve.
For years, SQL*Plus has been the de facto standard tool for connecting to Oracle databases. Since the early versions of Oracle, it has been included as a part of any Oracle RDBMS installation. SQL*Plus is a command-line tool and is launched on all Oracle platforms using the command, sqlplus. This command-line tool has been a staple of Oracle DBAs for many years and has a powerful, interactive command interface that can be used to issue SQL statements, create database objects, launch scripts, and start up databases. For some beginners, that power comes at a price, in the form of SQL*Plus's somewhat steep learning curve. However, although SQL*Plus can be challenging to use as a SQL and PL/SQL development tool, it is an extremely useful tool for DBAs, since most of the commands we will execute are fairly straightforward. Until Version 11g, the Windows installation of Oracle included a version of SQL*Plus that could be executed from the desktop. That tool, invoked using the SQLPLUSW.exe program, was deprecated starting with Version 11g. Throughout the course of this book, we will learn to use SQL*Plus to execute basic administration commands such as startup and shutdown. The following is a screenshot of the command-line SQL*Plus tool from a DOS command line in Windows:
Oracle Enterprise Manager (OEM ) is an interesting and useful tool that has been available since Oracle Database Server Version 8.0. With Version 8i, Oracle moved to a Java-capable browser console that could be run standalone or with a management server. The management server served as a central repository for database information, and would communicate with various agents on the database servers. These agents acted to gather information from all the databases in the enterprise and relayed it to the management server, giving the DBA a centralized single point of administration. Enterprise Manager followed this architecture through Version 9i.
Beginning with Version 10g, Enterprise Manager experienced a graphical and architectural overhaul. OEM 10g featured a completely integrated browser interface that offered complete control of nearly every aspect of database administration, including storage allocation, schema object manipulation, backup and recovery, database parameter configuration, and performance monitoring. OEM 10g and 11g can be used in either of the two configurations, Database Control or Grid Control . Database Control runs on a database server and gives the DBA full administrative control of all aspects of a single database running on that server. Grid Control can be used at the enterprise level in the same way as Database Control can be used at the database level. Grid Control is installed as a centralized server with agents deployed on individual database servers. Like the earlier versions of OEM, these agents communicate with the Grid Control server, allowing the DBA direct control over every administrative aspect of these databases.
Another makeover for OEM comes with the latest version, Oracle Enterprise Manager 12c, Cloud Control. OEM 12c features a cleaner graphical interface and a number of new features, a few of which are listed here:
Integration with Oracle Support for easier creation and resolution of service requests
A compliance framework for easier monitoring and reporting in environments where compliance auditing and oversight is crucial
Improved web interface security
Enhancements providing better control over backup and recovery operations
Automatic discovery of databases
The following screenshot shows the UI of OEM 11g:
The Tool for Oracle Application Developers (TOAD ) is a full-featured development and administration tool for Oracle as well as other relational database systems, including Microsoft SQL Server, Sybase, and IBM's DB2. It was originally created by Jim McDaniel for his own use; he later released it as freeware for the Oracle community at large. Eventually, Quest Software acquired the rights to TOAD and began distributing a licensed version, while greatly expanding on the original functionality. TOAD is immensely popular among both DBAs and developers, due to its large feature set. For DBAs, it is a complete administration tool, allowing the user to control every major aspect of the database, including storage manipulation, object creation, and security control. For developers, TOAD offers a robust coding interface, including advanced debugging facilities. TOAD is available for download in both freeware and trial licensed versions. A screenshot of the TOAD UI is shown as follows:
DBArtisan (now called DBArtisanXE), by Embarcadero Technologies, is another complete suite of database management tools that operate across multiple platforms. DBArtisan is only available as a licensed product, but has extensive administration capabilities, including the ability to do advanced capacity and performance management, all packaged in an attractive and user-friendly GUI front end. A trial version is available for download from Embarcadero's website.
Oracle SQL Developer , originally called Raptor, is a GUI database interface that takes a somewhat different approach from its competitors. While many of the major licensable GUI administration products have continued to expand their product offerings through more and more add-on components, SQL Developer is a much more dedicated tool. Although it lacks a full set of administration features, it serves well as a streamlined SQL interface to the Oracle database. You can create and manipulate database objects in the GUI interface, as well as write and execute SQL statements from a command line. Administration-oriented activities such as storage control are left to other tools. SQL Developer aims to be a strong SQL and PL/SQL editor with some GUI functionalities. SQL Developer has gained popularity in recent years, in large part owing to several benefits, which are listed as follows:
It is completely free with no mandatory licensable components, although third-party add-ons are available for purchase.
It is a true cross-platform client-side tool written primarily in Java. While a majority of the commonly used SQL tools are available only on the Windows platform, SQL Developer runs on Windows, Linux, and even the Mac.
SQL Developer supports read-only connections to many popular databases, including SQL Server, Sybase, MySQL, Microsoft Access, DB2, and Teradata.
Because it is written in Java, it allows for the creation and addition of third-party extensions. If you want a capability that SQL Developer does not have, you can write your own!
It is provided by Oracle and is now included with any installation of Oracle database. It has essentially replaced SQL*Plus as Oracle's default SQL interface, although SQL*Plus is still available from the command line.
The UI of SQL Developer is as shown in the following screenshot:
For these reasons, the tools we use in this book for the purposes of demonstration will primarily be SQL Developer and SQL*Plus. From time to time, we'll also look at managing database operations with Enterprise Manager. But before we get ahead of ourselves, let's find out a little about the data we'll be using, and look at the Companylink database.
This book focuses on two objectives: first, to prepare you for the Oracle Database 11g: Administration I exam, number 1Z0-052, and second, to present the knowledge needed for the exam in such a way that you can use it in a real-world setting. To that end, rather than using the default tables included in Oracle, we will be working with simulated real-world data. The database we will use throughout this book is for the fictional company Companylink. Although most people are aware of the impact of social networking in our private lives, companies are realizing the importance of using it in their industries as well. Our fictional Companylink is a business that focuses on social networking in the corporate setting. The data model that we will use is a small but realistic set of working data that could support a social networking website. The following tables are included in the Companylink database, which can be downloaded from the support site at http://www.packtpub.com/support.
Employee: Information about employees that use the Companylink site.Address: Information about the street address, city, and state of each employee.Branch: The corporate branch to which each employee belongs. Each employee belongs to one branch.Division: The corporate division to which each branch belongs. Each division is associated with multiple branches.E-mail: An employee can store multiple e-mail addresses.Message: The fictional Companylink social networking site allows you to send messages to fellow employees. That information is stored here.Website: Companylink allows users to create their own personal web pages. The URL of these pages are contained in this table.Blog: In addition to a website, users can optionally create their own blogs. This information is stored in theBlogtable.Project: Each employee is assigned to a single primary project, which is contained here.Award: Employees can win corporate awards. The list of possible awards is stored here. Employees can win more than one award.Employee_award: This table is used to relate employees with their awards. Since multiple employees can win the same award and multiple awards can be won by the same employee, this creates a many-to-many table relationship, which, in the relational paradigm, must be avoided. TheEmployee_awardtable divides this many-to-many relationship into two distinct one-to-many relationships.
Once we've installed the Oracle software and created our database, these tables will form the backbone of the data that we'll use as examples throughout this book.
Tip
Downloading the example code
You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.
In this chapter, we've introduced the Oracle RDBMS. We've learned about the importance of the RDBMS in today's world and described the way Oracle's product line supports RDBMS operations. We've looked at the standards for naming and versioning in Oracle. We've outlined the job role of today's Oracle DBA and described some of the characteristics required to become a DBA. Lastly, we've presented several of the common tools used by DBAs and introduced the table structures of the Companylink database. In our next chapter, we'll perform a step-by-step installation of the Oracle software that will provide the software framework for our database.
Q 1. Which of the following database paradigms can organize our data in such a way that the data and its inter-relationships can be clearly identified?
a. Hierarchical
b. Flat-file
c. Relational
d. Network
Q 2. Which of the following languages is not supported directly within an Oracle database?
a. SQL
b. Perl
c. Java
d. PL/SQL
Q 3. Which of the following types of products are offered in the current Oracle product line?
a. Application servers
b. Server hardware
c. Virtualization solutions
d. All of the above
Q 4. Given that your database is running Oracle database Version 11.2.0.1.0, which of the following statements is false?
a. The major software release level is 11.
b. The database could be referred to a Oracle 11g Release 2.
c. The patch level is 0.
d. None of these statements is false
Q 5. Which of the following Oracle database tools only runs as a command-line interface?
a. SQL*Plus
b. Oracle Enterprise Manager 12c
c. SQL Developer
d. TOAD
