Oracle Databases and DBA Fundamentals
DBAs wear many hats and play many roles within an organization. A DBA is the administrator who designs, implements, tests, operates, and maintains databases for an organization. Figure 1-8 illustrates the DBA’s general tasks, and the list that follows provides further explanation.
- Database design, storage, and capacity planning: DBAs play a major role in designing the database along with determining disk storage requirements and future database growth. Monitoring database growth trends is important so that the DBA can advise management on long-term capacity plans.
Install, configure, upgrade, migrate, and provisioning: Although system administrators are generally responsible for the hardware and OS on a given server, installation of the database software is typically done by the DBA. This role requires knowledge of the hardware prerequisites and requirements so that the database server runs efficiently, and then communicating those requirements to the system administrator. The DBA installs the database software and selects from various options to configure it for the purpose for which it is being deployed. As new releases and patches are made available, it is the DBA’s role to determine which are appropriate and to complete the installation. If the server is a replacement server, it is the DBA’s role to transfer the data from the old server to the new one. The more seasoned DBA will take a highly cautious approach to any data transfer or migration operation because data loss is a potential consequence of sloppy transitions. DBAs are tasked to provision database servers on demand for development, testing, QA, and reporting.
Figure 1-8 General tasks for DBAs
- Database security: Databases centralize the storage of data and are attractive targets for hackers. DBAs must understand the particular security model that the database product uses as well as the security requirements of the application and how to effectively control data access. The three basic security tasks are authentication (setting up user accounts to control logins to the database), authorization (setting permissions on various schemas and database objects), and auditing (tracking user movements and actions within the database). The auditing task is particularly important as regulatory laws, such as Sarbanes-Oxley (SOX), the Healthcare Insurance Portability and Accountability Act (HIPAA), and Payment Card Industry (PCI), have security and reporting requirements that require adherence.
- Backup and recovery, high availability: DBAs are responsible for developing, implementing, and periodically testing a backup and recovery plan for the databases they manage. Even in large organizations where a separate system administrator performs server backups, the DBA has final responsibility for ensuring that the database backups are done as scheduled and that they include all the files necessary to make database recovery possible after a failure. When failures do occur, the DBA needs to know how to use the backups to restore the database to operational status as quickly as possible, without losing any transactions that were committed. There are several ways a database can fail, and the DBA must have a strategy to recover from each type of failure. From a business standpoint, there is a cost to doing backups, and the DBA makes management aware of the cost/risk trade-offs of various backup methods. DBAs use techniques such as online backups, clustering, replication, and standby databases to provide higher availability.
- Performance tuning and monitoring: DBAs are responsible for monitoring the database server on a regular basis to identify bottlenecks and remedy them. Database server tuning is performed at multiple levels. The capacity of the server hardware and the way the OS 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 quickly 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 involves 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 that good performance will result.
- Troubleshooting and support: When things go wrong with the database server, the DBA needs to know how to quickly ascertain the problem and to correct the issue without losing data or making the situation worse. DBAs provide 24x7 support, 365 days a year.