"Need to Know" for Oracle DBA Begineers

There is more to being a DBA than knowing SQL.  The DBA works at the intersection of the database, server, operations group, and developers.  A DBA must understand concepts from all these facets of IT as well as be able to draw upon knowledge of their production environment to troubleshoot performance, hardware, and software issues. Below is a list of the ten topics I feel every entry level DBA should understand.  The list I created stems from my experiences working with databases as both a DBA, Developer, and Manager.   When looking to hire entry level DBA’s, the interview questions I ask are drawn from these areas. If you’re looking to start a job as DBA, then you’ll want to be familiar with these topics.

Backup and Restore

Any DBA worth their salt should know the DBMS’ (Database Management System’s) built-in methods to backup and restore data, such as using Oracle Recovery Manager, but in addition to these built-in utilities, it also makes sense to understand what third party offerings exist. Enterprise backup solutions are used in many larger IT shops.  Be familiar with products such as NetBackup or NetApp SnapManager. As a junior DBA it would be impressive if you knew these tools existed and that not all backups are created equal.  That is to say, just because you back up the database files, doesn’t mean get a good backup… in fact, you didn’t.

Basic optimizations

It is important to know when to recommend when an index should be created.  You should know some basic indexing strategies.  When are clustered indexes appropriate?  When should you use a covered index? Also know how your database optimizer works.  Does it rely on special table statistics?  How do you update those? Know what it means to reorganize tables and indexes.  When should they be reorganized, and what can you do to automate the process?

Developers

Software developers can make or break a database.  It is important that you can work with others to help them write efficient queries.  You need to help them understand that one call to a database is much more efficient than one thousand!  In most cases it is faster run one query that returns a 1,000 rows, than it is to call 1,000 queries that return one row each. As a DBA you should help them understand when it is better to perform processing on the DBMS rather than in code.  Pulling volumes of data across your network to perform a sum is most likely slower than writing a query with an aggregate function.

Storage Systems

In most databases the main bottle neck is disk access.  Understanding where your database is stored and how the DBMS accesses the physical data is important.  Is your database on local disk or a SAN (Storage Area Network)? If you company has a storage team, get to know them, and understand what tools they use to monitor IOPS (Input/output Operations per Second).

How to read a query plan

As a junior DBA you should know how to generate and read a basic query plan.  I wouldn’t expect you know fully understand all verbiage, but several key phrases, such as “Full Table Scan,” and “Nested Loops” should jump out as red flags. Also, you should understand, when the optimizer recommends a change be made, why that change would work, and what the trade-offs are in making it.  For instance, the optimizer may recommend that an index be created.  It this to compensate for poor programming?  Also, if you add it, could something else suffer, such as a process to insert or update data?

Knowledge of normalization

Normalized tables are the foundation of a well-designed relational database, yet they can also be its bane. A junior DBA should understand and know how to put data into 1^st, 2^nd, and 3^rd normal form.  Why is normalization important and when can it become a liability? Knowing the difference between a Primary, Foreign, and Unique key is important.  Also is understanding and knowing how to enforce one-to-one, one-to-many relationships.

Knowledge of SQL

It might seem obvious, but a DBA should have a really good grasp of both SQL DML (Data Manipulation Language) and DDL (Data Definition Language). DML covers items such as Select, Update, Insert and Delete.  You should understand all the major clauses such as WHERE, GROUP BY, HAVING, and ORDER BY.  In addition you should be comfortable with sub queries and joins. DDL covers items such as CREATE TABLE and ALTER TABLE.  A junior DBA should understand how to create and modify tables and indexes and know the difference between deleting records, truncating a table, or dropping it! And… don’t forget views!

Operating System

As a DBA you need to be familiar with the OS (Operating System) your DBMS lives within.  You should understand nuances of your operating system, such as security settings, integration with Active Directory, LDAP (Lightweight Directory Access Protocol), and naming conventions.  Also, how is your DBMS started?  What scripts are used to start, shutdown, or lock users out of your database?

Scripting

To become efficient it is important to know when OS scripting, such as PowerScript, can help you manage your servers.  Consider having ten or more DB servers?  If you had to shut down all the DBMS on them, would you individually log in and manually shut them down or use a script? In my book, if you want to get an edge on other junior DBA’s you learn scripting.  This will only make you more efficient at your job.

Stored Procedures and Triggers

I made stored procedures and triggers a separate category since they’re more like programming than pumping out SQL statements.  Regardless, you should know when to use them and recommend to your developers their virtues versus writing SQL in code. Also, many third party applications ship with Stored Procedures and Triggers.  Being able to read them and understand their logic can go a long ways when trying to troubleshoot a performance issue. The sooner you can avoid treating them like black boxes, the quicker you’ll understand and become more comfortable with the databases you support.

Daily Work of a DBA

General tasks

1)Installation, configuration, upgrade, and migration Although system administrators are generally responsible for the hardware and operating system on a given server, installation of the database software is typically up to the DBA. This job role requires knowledge of the hardware prerequisites for an efficient database server, and communicating those requirements to the system administrator. The DBA then installs the database software and selects from various options in the product to configure it for the purpose it is being deployed. As new releases and patches are developed, it's the DBA's job to decide which are appropriate and to install them. If the server is a replacement for an existing one, it's the DBA's job to get the data from the old server to the new one.

2)Backup and recovery DBAs are responsible for developing, implementing, and periodically testing a backup and recovery plan for the databases they manage. Even in large shops where a separate system administrator performs server backups, the DBA has final responsibility for making sure that the backups are being done as scheduled and that they include all the files needed to make database recovery possible after a failure. When failures do occur, the DBA needs to know how to use the backups to return the database to operational status as quickly as possible, without losing any transactions that were committed. There are several ways the database can fail, and the DBA must have a strategy to recover from each. From a business standpoint, there is a cost to doing backups, and the DBA makes management aware of the cost/risk tradeoffs of various backup methods.

3)Database security Because databases centralize the storage of data, they are attractive targets for hackers and even curious employees. The DBA must understand the particular security model that the database product uses and how to use it effectively to control access to the data. The three basic security tasks are authentication (setting up user accounts to control logins to the database), authorization (setting permissions on various parts of the database), and auditing (tracking who did what with the database). The auditing task is particularly important currently, as regulatory laws like Sarbanes-Oxley and HIPAA have reporting requirements that must be met.

4)Storage and capacity planning The primary purpose of a database is to store and retrieve data, so planning how much disk storage will be required and monitoring available disk space are key DBA responsibilities. Watching growth trends are important so that the DBA can advise management on long-term capacity plans. 

5)Performance monitoring and tuning The DBA is responsible for monitoring the database server on a regular basis to identify bottlenecks (parts of the system that are slowing down processing) and remedy them. Tuning a database server is done on multiple levels. The capacity of the server hardware and the way the operating system is configured can become limiting factors, as can the database software configuration. The way the database is physically laid out on the disk drives and the types of indexing chosen also have an effect. The way queries against the database are coded can dramatically change how fast results are returned. A DBA needs to understand which monitoring tools are available at each of these levels and how to use them to tune the system. Proactive tuning is an attitude of designing performance into an application from the start, rather than waiting for problems to occur and fixing them. It requires working closely with developers of applications that run against the database to make sure that best practices are followed so good performance will result. 

6)Troubleshooting When things do go wrong with the database server, the DBA needs to know how to quickly ascertain the problem and to correct it without losing data or making the situation worse.

The Changed Role of the Oracle DBA

In the late 20th century, companies had dozens of Oracle DBA staff and important tasks were still overlooked because DBAs said “It’s not my job,” or “I don’t have time.” Changing technology mandated that the 21st century DBA would have more overall responsibility for the whole operation of their Oracle database.



Winner of the “It’s Not My Job” award