Relational Database Management Systems

Relational Database Management Systems

A First Look at Oracle Database 12c

I want to spend some time talking the theory of relational database management systems. What Leonardo da Vinci said is so important that I’ll quote it again: “Those who are in love with practice without knowledge are like the sailor who gets into a ship without rudder or compass and who never can be certain [where] he is going. Practice must always be founded on sound theory.” How can you competently administer a relational database management system like Oracle if you don’t really know what makes a “relational” database relational or what a database “management” system manages for you?

However, if you’re like most of my students, you won’t be satisfied until you’ve seen an Oracle database management system. I’ll grant that seeing a real system will make it easier for you to understand a few things. But it would take quite a while to coach you through the process of creating an Oracle database management system. Fortunately, there is a solution. Oracle provides a convenient virtual machine (VM) containing a complete and ready-to-use installation of Oracle Database 12c on Linux. All you need to do is to download and install the Oracle VirtualBox virtualization software and then import a ready-to-use VM. The instructions for doing so are at www.oracle.com/technetwork/community/developer-vm. Pick the Database App Development

VM option, and follow the download and installation instructions. (I hope you don’t want me to regurgitate the instructions here.) The instructions are short and couldn’t be simpler, because you don’t need to install and configure Oracle Database; rather, you import a prebuilt VM into Oracle VirtualBox. The only difficulty you may experience is that the prebuilt VM is almost 5GB in size, so you need a reliable and fast Internet connection.

If you follow the instructions and fire up the VM, you see the screen in Figure 1-1. It looks like a

Windows or Mac screen, doesn’t it? This is the Gnome Desktop, which makes it possible to use Linux without getting completely lost in a world of cryptic Linux commands.

Next, minimize the terminal window that’s taking up so much real estate, and click the SQL Developer icon in the top row of icons. SQL Developer is a GUI tool provided by Oracle for database administration. Because this is the first time, it will take a few minutes to start; that’s normal. Figure 1-2 shows what you see at startup.

As shown in Figure 1-3, click Connections, and create a new connection. Give the connection the name “hr” (Human Resources) or any other name you like. Use the following settings:

• Username “hr”

• Password “oracle”

• Connection Type “Basic”

• Role “Default”

• Hostname “localhost”

• Service Name “PDB1”

Expand the Tables item in the navigation pane on the left. Six tables are shown; click the EMPLOYEES table. The data in the EMPLOYEES table is listed in a full-screen editor, as shown in Figure 1-5. If you like, you can make changes to the data and then either save your changes (commit) or discard them (roll back) using the Commit Changes and Rollback Changes buttons or the F11 and F12 keys.

When I was a junior programmer, early in my career, my friends and I were assigned to work on a

big software development project for which we would have to use unfamiliar technologies, although we were promised that training would be provided before the project started. All we knew in advance was that the operating system was something called VAX/VMS; we didn’t know which programming language or database would be used. The very first thing the instructor said was (paraphrasing), “First you have to insert your definitions into the CDD,” and then he walked to the chalkboard and wrote the commands that we needed for the purpose. Needless to say, we were quite flustered, because we had no idea what those “definitions” might be or what a “CDD” was and how it fit into the big picture.

I’m sure you’re eager to learn how to create an Oracle database. Anybody can issue a command such

as CREATE DATABASE or push a button in a GUI tool such as the Database Creation Assistant. But the mere knowledge of a few commands (or even a lot of commands) doesn’t make anyone an Oracle Database administrator in my opinion.

What Is a Database?

Chris Date was the keynote speaker at one of the educational conferences organized by the Northern

California Oracle Users Group (NoCOUG), of whose journal I am the editor. The local television news station sent out a crew to cover the event because Chris Date is a well-known database theoretician and one of the associates of Dr. Edgar Codd, an IBM researcher and the inventor of relational database theory. The news reporter cornered me and asked if I was willing to answer a few questions for the camera. I was flattered, but when the reporter pointed the camera at me and asked, “Why are databases important to society?” all I could think of to say was (paraphrasing), “Well, they’re important because they’re, like, really important, you know.” All those years of database administration under my belt, and I still flunked the final exam!

I’d therefore like to spend a few minutes at the outset considering what the word database signifies. An understanding of the implications of the word and the responsibilities that go along with them will serve you well as a good database administrator.

I’ll begin by saying that databases can contain data that is confidential and must be protected from

prying eyes. Only authorized users should be able to access the data, their privileges must be suitably

restricted, and their actions must be logged. Even if the data in the databases is for public consumption, you still may need to restrict who can update the data, who can delete from it, and who can add to it. Competent security management is therefore part of your job.

Databases can be critical to an organization’s ability to function properly. Organizations such as

banks and e-commerce web sites require their databases to be available around the clock. Competent

availability management is thus an important part of your job. In the event of a disaster such as a flood or fire, the databases may have to be relocated to an alternative location using backups. Competent continuitymanagement is therefore another important element of your job. You also need competent changemanagement to protect a database from unauthorized or badly tested changes, incident management to detect problems and restore service quickly, problem management to provide permanent fixes for known issues, configuration management to document infrastructure components and their dependencies, and release management to bring discipline to the never-ending task of applying patches and upgrades to software and hardware.

I’ll also observe that databases can be very big. The first database I worked with, for the semiconductor manufacturing giant Intel, was less than 100MB in size and had only a few dozen data tables. Today, databases used by enterprise application suites like PeopleSoft, Siebel, and Oracle Applications are tens or hundreds of gigabytes in size and might have 10,000 tables or more. One reason databases are now so large is that advancements in magnetic disk storage technology have made it feasible to efficiently store and retrieve large quantities of nontextual data such as pictures and sound. Databases can grow rapidly, and you need to plan for growth. In addition, database applications may consume huge amounts of computing resources. Capacity management is thus another important element of your job, and you need a capacity plan that accommodates both continuous data growth and increasing needs for computing resources.

When you stop thinking in terms of command-line syntax such as create database and GUI tools

such as the Database Creation Assistant (dbca) and start thinking in terms such as security management, availability management, continuity management, change management, incident management, problem management, configuration management, release management, and capacity management, the business of database administration begins to make coherent sense and you become a more effective database administrator. These terms are part of the standard jargon of the IT Infrastructure Library (ITIL), a suite of best practices used by IT organizations throughout the world.