The Oracle DBA: Performing Day-to-Day Operations

In order to properly perform the role of database administrator, you will need to develop and implement solutions that cover all areas of this discipline. The amazing part of this job is that you may be asked to do many, or perhaps all, of the different aspects of your job on any given day. Your daily tasks will vary from doing high-level architecture and design to performing low-level tasks. Let’s take a look at the things that you will be getting involved in.

Architecture and Design

DBAs should be involved with the architecture and design of new applications, databases, and even technical infrastructure changes. Decisions made here will have a large impact on database performance and scalability, while database knowledge will help you choose a better technical implementation. Data modeling tools such as SQL Developer Data Modeler can assist the DBA.

Capacity Planning

Short- and long-range planning needs to be performed on your databases and applications. You should focus on performance and sizing characteristics of your systems that will help to determine upcoming storage, CPU, memory, and network needs. This is an area that is often neglected and can lead to big problems if it is not done properly. There is a shift in planning environments to be able to add resources easily as systems grow, either with virtualization or cloud environments. With virtualized database environments, these resource concerns and planning for capacity might be less on the DBA side, but those who are now planning out the virtualized environment capacity will be concerned with overall usage. These environments tend to scale better because there are ways to add resources as needed. Still, being able to communicate database growth and how it will be using the different resources will assist in managing the overall environment.

Backup and Recovery

A backup and recovery plan is, of course, critical for protecting your corporate data. You need to ensure that the data can be recovered quickly to the nearest point in time as possible. There is also a performance aspect to this because backups must be performed using minimal resources while the database is up and running, and recoveries need to be performed within a time limit predefined by Service Level Agreements (SLAs) developed to meet customers’ requirements. A complete backup and recovery implementation should include local recovery and remote recovery that is also referred to as disaster recovery planning (DRP). Oracle 12c offers backup and recovery at a pluggable database level, and this will need to be factored into recovery plans.

Security

Security is an area that has become extremely important because of the number of users that can access your databases and the amount of external, web-based access. Database users need to be authenticated so that you know with certainty who is accessing your database. Users must then be given authorization to use the objects in Oracle that they need to do their job. However, despite this need for permissions and access in order to do their jobs, a best practice is to grant only the minimum amount of permissions and access for the role or user. This can be managed with Oracle Enterprise Manager, as you’ll see in some examples of this later in this chapter. External users require extra web-based security that is beyond the scope of this book.

Managing user permissions is just part of the security in the database environment. Encryption of data, auditing of access and permissions, and looking at the data access of system users in development environments are a few other areas that need attention in order to provide a secured database environment.

Performance and Tuning

Performance and tuning is arguably the most exciting area of database management. Changes here are noticed almost immediately, and every experienced DBA has stories about small changes they’ve made that resulted in large performance gains. On the other hand, every performance glitch in the environment will be blamed on the database and you will need to learn how to deal with this. Automatic Workload Repository (AWR) Reports, Statspack, OEM Performance Management, and third-party tools will assist you in this area. There is a lot to learn here, but the proper tools will simplify this considerably.

Managing Database Objects

You need to manage all schema objects, such as tables, indexes, views, synonyms, sequences, and clusters, as well as source types, such as packages, procedures, functions, and triggers, to ensure they are valid and organized in a fashion that will deliver adequate performance and have adequate space. The space requirements of schema objects are directly related to the tablespaces and data files that are growing at incredible rates. SQL Developer or OEM can simplify this, something you’ll see examples of later in this chapter.

Storage Management

Databases are growing at incredible rates. You need to carefully manage space and pay particular attention to the space used by data files and archive logs. Also, with Fast Recovery Area (FRA) there are backup areas that need to be managed for their space usage. With Automatic Segment Space Management (ASSM), the need for reorganization of database objects has decreased. Reorgs also use considerable resources so there should be some evaluation of whether an object needs to be reorganized or not. There are online utilities that are supposed to help with reorganization of indexes and tables while they remain online, but do not perform these operations unless it is necessary. See the section “Manage Space,” later in the chapter, for more on this.

Change Management

Being able to upgrade or change the database is a skill that requires knowledge of many areas. Upgrades to the database schema, the procedural logic in the database, and the database software must all be performed in a controlled manner. Change control procedures and tools such as Oracle’s Change Management Pack and third-party offerings will assist you.

Schedule Jobs

Since Oracle Database 10g, DBMS_SCHEDULER was introduced with the existing DBMS_JOBS. It allows for jobs to be scheduled for a specific date and time, and to categorize jobs into job classes that can then be prioritized. This means that resources can be controlled by job class. Of course, other native scheduling systems such as crontab in Linux and Unix can be used, as well as other third-party offerings.

Jobs can include any of the database maintenance tasks such as backups and monitoring scripts. Grouping the monitoring and maintenance jobs into a job class can give them a lower priority than an application batch job that needs to finish in a short batch window.

Network Management

Oracle Networking is a fundamental component of the database that you will need to become comfortable with. Troubleshooting connections to the database is similar to troubleshooting performance issues, because even though the database is up and available, if your applications can’t reach it, it’s the same as if it’s down and unavailable. Database connectivity options such as tnsnames, the Oracle Internet Directory (OID), and the Oracle Listener require planning to ensure that performance and security requirements are met in a way that is simple to manage. Details of this are discussed in the “Understand Database Connections” section of this chapter.

High Availability

With information and data being available 24/7, architecting highly available systems has fallen into the hands of the database administrator. Options on the database side include Real Application Clusters, Data Guard, replication, and fast recovery options. There are also hardware and virtualization options to provide redundant and available systems.

Troubleshooting

Although troubleshooting may not be what you’d consider a classic area of database management, it is one area that you will encounter daily. You will need tools to help you with this. My Oracle Support provides technical support and is an invaluable resource. Oracle alert logs and dump files will also help you greatly. Experience will be your biggest ally here and the sooner you dive into database support, the faster you will progress.