System DBA vs. Application DBA
Overview
In small environments and development shops a DBA tends to wear both hats. In large mature organizations the roles are separated. Though both can share common tasks where appropriate for an organization, what follows are the common roles for a System DBA and an Application DBA in an enterprise environment.
System DBA
· Database backup and restoration.
· Installation of Oracle Database product.
· Keeping Oracle database product patches up-to-date.
· Creation of databases.
· Daily checks and routine monitoring of local and remote databases.
· Installation and maintenance of data synchronization technologies.
Examples: Data Pump, Data Guard, Streams, custom scripts etc.
· Creation, maintenance and troubleshooting of Oracle Network (listeners etc.).
· Standard system maintenance of database:
- Tablespace creation, sizing and segment optimization. Datafile management.
- Redo log file sizing, file management and change intervals.
- Archive log sizing and file management.
- Memory sizing and management.
- Partition creation and management.
- Scheduling and implementation of database level:
-- Segment space optimization.
-- Routine index rebuilds.
-- Routine statistics gathering.
-- Oracle Text rebuilds and optimization.
· Overall architectural changes and scaling of database both at system and schema level.
· System level data loading, i.e. full database imports etc. .
· Writing maintenance scripts to perform above system DBA tasks.
· AWR, ADDM and ASH Reports as needed to troubleshoot issues.
Application DBA
· Writing and maintaining applications. · Creation and maintenance of schema objects: tables, indexes, views, sequences etc. · Standard data management\steward tasks: - Copying data between tables. - Loading small to medium sets of data into the database (table and schema level). - QA and QC of data. - Customer DML (Data Manipulation Language) · SQL code optimization (using tools such as Explain Plan, TKProf etc.). · Helping users write and optimize SQL. · AWR, ADDM and ASH Reports as needed to troubleshoot issues.
Production Environment vs. DBA Resources
To ensure business\mission continuity your DBA resources need to attend to these core tasks before all others. Not allocating adequate resources to these is a mistake found in those environments where downtime and loss of revenue are acceptable.
- Database backups.
- Daily checks and routine monitoring of local and remote databases.
- Data synchronization technologies (Data Guard, Streams etc.).
- Oracle database product patching.
- Disaster recovery testing.
It is a good idea to review your database shop's workload annually to make sure this foundation remains in place.