In our last post of this series, we talked about the essential skills every beginner DBA should have. We discussed the following functions they would need to complete in their first few years:
- Installing Oracle and applying patches
- Optimizing database performance
- Ensuring availability and integrity
- Addressing security concerns
- Executing troubleshooting tasks as-needed
Chances are, in that first year, a DBA has had more than their fair share of grunt work, has made mistakes, and has had quite a few anxious moments. The DBA would have also have had the chance to interact with users across the board with end users, system and security administrators, IT personnel, Oracle support, and the software development team.
Let’s get into the next phase of the career of a DBA. What essential skills should a Junior Oracle DBA have? The following is a checklist of the top 9 so you can see if you’re on track – and maybe find some areas in which you can focus to get up to speed:
- Backing up and recovering databases
First and foremost, a DBA should understand the need for reliable backups — to ensure database recoverability and continued availability in case of disasters.
A database backup is reliable only if it can restore a database effectively. Knowing how to use backup tools is important. Even more important is the ability to test and verify the veracity of a backup. Backup tools usually have options with which backups can be tested — for instance, RMAN (recovery manager) has the preview restore option.
- Securing databases
A junior-level DBA should be aware of the overall security risks that a database instance can be exposed to over a period of time, ranging from network and OS-level vulnerabilities, purposeful hacking, misuse of privileges, malicious code, or just insufficiently complex passwords.
It is not just awareness that is important. A DBA at this level should have the skills and the know-how to proactively identify and address such security issues.
- Knowledge of Oracle performance metrics
There are several KPIs that are built into the Oracle system. These KPIs, or performance metrics, are not just indicative of the reliability, scalability, performance, and overall health of the database, but also reflect upon the DBA’s performance and may formally be part of reviews. A junior DBA should know not only about the performance metrics, but also how to retrieve the values for these metrics and create new metrics, if required.
- Monitoring and tuning databases
Delivering and constantly improving database performance is a primary responsibility of an Oracle DBA, so it makes sense that the DBA should be very familiar with monitoring and tuning database performance.
The reasons for poor response time or throughput can originate at various levels — the server or network, improper database configuration, or poorly designed SQL queries, to name a few. A DBA should be able to monitor performance statistics, drill down to the reasons for poor response time, and take steps to tune performance.
- Reducing downtime for scheduled maintenance activities
Applying scheduled patches and updates to a database instance invariably results in downtime. A good DBA should know the best practices for reducing downtime when a planned update is to take place.
Another best practice is to use multiple Oracle homes while applying patches. This helps reduce downtime in case problems arise after patches are applied and a rollback is required.
- Reading an Oracle query execution plan
Queries that are not optimized for performance can be a singular factor responsible for poor database performance and throughput. Such queries don’t result in bringing down the overall database performance level but can create bottlenecks when they’re run.
The first place the DBA should investigate performance bottlenecks is in the underlying SQL code. In order to do that, the DBA should be able to understand Oracle query execution plans at a simple level and build up skills to understand and interpret such plans at an advanced level. The DBA should be comfortable with commands such as EXPLAIN_PLAN.
- Running health check monitor
The Oracle health monitor is an important tool for checking integrity issues in the database structure, transactions, redo logs, core dictionary objects, and media. Judicious, timely, and proactive use of this tool can help prevent database integrity issues before a crash takes place.
Since one of the primary responsibilities of a DBA is ensuring the continued availability and reliability of a database, it is imperative that the DBA should be familiar with the use of this tool and be able to understand the results generated by it.
- Solid grounding in data migration and ETL
Migration is one of the most challenging and complex tasks in the management of databases. A migration can take many different forms:
- Building a data warehouse by extracting data from multiple sources
- Merging data from multiple sources to another database
- Transferring of data from one database to another on a different platform
ETL (Extract-Transform-Load) utilities are an indispensable tool for carrying out data migrations.
A junior DBA may or may not be responsible for using ETL utilities — the job could be done by Business Intelligence professionals. But, at the very least, a junior-level DBA should be aware of the issues involved in migration and have a minimal level of competency in the usage of ETL tools and utilities.
- Interacting with vendors and external parties
DBAs need to be able to interact with external vendors, the IT vendors pushing a product or contacting Oracle support for resolution of issues. In the first case, the DBA should be knowledgeable about technology trends and have the ability to determine the product required by the organization. In the second case, Oracle support is a lifeline for an Oracle DBA. The DBA should do his or her homework and collect all the facts related to the issue in order to resolve the technical issue.
And there you have it. If you find yourself lacking in any of these areas as a Junior DBA, there are numerous online free and paid resources you can use to advance or hone your skillset. Stay tuned for the final post in this series detailing the top 10 skillsets every Senior DBA should know.
The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.
Which RAID should you use with SQL Server? Learn the differences between RAID 0, RAID 1, RAID 5, and RAID 10, along with best practices.