Have you found yourself thrust into new responsibilities within your IT department as a manager overseeing database operations? You may be what’s called an “Accidental DBA.” As Microsoft’s TechNet explains:
Even for professionals in this new job, it’s tough to get up and running when you’re unsure where to start and what to do. Let’s start with some basics.
You are usually the gatekeeper to the organization’s information infrastructure. Very often you will be responsible for providing data users with access, user IDs, passwords, and setting levels of permission. Some users will be “read only” while others will have “read/write” permissions and some will have “admin” access.
In your first 30 days as a DBA manager, you should conduct an audit of system users and permissions. You may have to do catch-up duty if the manager’s spot was vacant for long, such as suspending access for former contractors or employees. Reviewing database access and security rules and procedures will help you establish a reputation and a defender of the data.
Start with an inventory of existing backups and a review of backup procedures. Work to verify, test, and correct any glaring deficiencies in backup procedures within your first 30 days.
You’ll want to be certain that you are not storing backups on the same physical or virtual system as the database. You need to isolate backup files from anything that happens to the database – such as hardware failure or data corruption. When you need them most, the backups won’t be there. Put the backup data on another server to avoid such issues.
You want to review your procedures in the event a physical database is damaged, for instance by fire, flood or natural disaster. This may involve co-located servers, keeping “hard copies” of data backups offsite. A review of the disaster preparedness plan should be part of your first 30 days. Implementing better procedures may take a little longer.
If you need to restore the system, how will you do this? If you have only one full backup, you could be in trouble if some or all of that backup is corrupted. This is where having differential/log backups is important. You want to adopt an approach that gives you lots of possibilities for completely restoring the database in the event that one or more older full backups is damaged or invalid.
You will want to determine how long you need to keep backup files. For many companies, legal counsel directs how long and in what format backup files are maintained. Sarbanes-Oxley and other securities laws include specific instructions for records retention – and severe penalties for poor compliance. You should determine, in your first 30 days, if there are legal regulations concerning your backup policies.
Another important consideration is maintenance. How often are you running backup? Do you have a schedule for running integrity checks? What about for maintaining the database index and statistics?
Once you’ve made any changes to the configuration of the system, regular maintenance helps keep it running properly and available to clients. Within your first 30 days, you should establish a maintenance checklist and schedule for the systems you’re responsible for.
Database performance depends on proper database indexing. There are several different approaches to indexing for performance. You’ll want to know how indexes work and basic good strategies for indexing. There may be other issues involved in the process. Is there, for example, someone else in the organization responsible for database indexing?
Existing indexing strategies may be working well enough. Although you may be tempted to enact changes immediately – especially indexes that are too big and don’t seem to be doing anything but dragging down performance – you need to make certain those changes don’t cause performance problems.
Analysis is key. Check index health first. Among the issues you may want to check for are fully duplicate indexes, and similar or unused indexes. Another impedance may be the result of bloat.
After analysis, testing helps determine whether changes will be positive for performance. Until you are comfortable with the databases, testing any changes on a backup database before implementing them may take a little time, but can protect you from creating a big problem.
Database Health Check
Another good task is establishing baseline database health through a health check. A Health check provides a quick overview of the system status. It looks for issues such as misconfigured memory settings or missing backups. Other issues may include index fragmentation, duplicate or unused indexes — many of the issues we’ve discussed previously.
Health checks also help the Accidental DBA set maintenance schedules. Some issues need daily attention to keep the database’s availability high. This also encompasses establishing best practices and checking for performance-slowing database growth patterns.
Consult the Datavail presentation “Health Checks Using Apex” for more detailed information. Datavail can help make your first 30 days as a DBA manager incredibly productive, so you can proceed with your databases locked down, backed up, and free from any obvious defects or problems. Contact Datavail for more information on how we might best support you in your new position.
The “ORA-12154: TNS:could not resolve the connect identifier specified” Oracle error is a commonly seen message for database administrators.