Oracle DBA Roles and Responsibilities

Who is ORACLE DBA ?

Oracle Database administrator is the cumulative role of multiple responsibilities Oracle DBA can play as Application support in development or may play as system analysis with system administrator or network administrator. Oracle DBA has capability to plan capacity management with storage administrator etc.


Prerequisite 

• Basic SQL
• Basic Linux/Unix OS knowledge

Database Administrators (DBAs) are responsible for :-

• Installation, configuration and maintain Oracle database software 
• Upgrading ,migration and Patch(fix bug) oracle database software
• Troubleshooting problems regarding the databases alert, applications and development tools.
• Maintain sound backup and recovery policies and procedures to make database available 24*7
• Take care of the Database design and implementation
• Implement and maintain strong database security policy (create and maintain users and roles, assign privileges)
• Perform database tuning,Oracle instance components including SQL and PL/SQL, and performance monitoring and database maintenance 
• Perform schema refresh non-prod with production environments 
• Plan database growth and changes (capacity planning)
• Perform general technical trouble shooting and give consultation to development teams
• Work with Oracle Corporation for technical support
• Setup and maintain documentation and standards for future purpose
• Deployment of strong disaster recovery plans as per SLA (service level agreement)

DBA Skills Required

• Good understanding of the Oracle database, related utilities and tools
• Good understanding of the underlying operating system
• Good knowledge of the physical database design
• Ability to perform both Oracle and operating system performance tuning and monitoring
• Knowledge of Oracle backup and recovery scenarios
• Good knowledge of Oracle security management
• Sound knowledge of both database and system performance tuning
• DBA should have sound communication skills with management, development teams, vendors and systems administrators
• DBA should have the ability to handle multiple projects and deadlines
• DBA should possess a sound understanding of the business

Level of DBA's

• Junior DBA    :L1
• Mid-level DBA :L2
• Senior DBA    :L3

Popular database use in industry:

• Oracle
• MySql
• DB2
• Microsoft SQL Server
• PostgreSQL

Certification available 

• Oracle Certified Associate (OCA)
• Oracle Certified Professional (OCP)
• Oracle Certified Manager (OCM)

Daily Activity

1. Verify all instances are up & working
2. Look into new alert log entries and checking for any errors
3. Verify the success of the Database backup
4. Verify the success of the Database archiving to tape
5. Check table space usage
6. Checking if there is enough disk space for the data files to grow
7. Monitoring temporary segments usage
8. Checking for DB links that are inaccessible
9. Finding invalid objects and fixing them
10. Checking session locks
11. Monitoring overall Database health & performance
12. Identifying tables with chained rows & rectifying them
13. Monitoring users and transactions
14. Escalating issues and providing feedback and suggestions
15. Work on assigned

Weekly Activities

1. Listen users with objects in system table space
2. Listen users except Sys & System as a default table space
3. Listen disabled constraints, procedures, functions, packages & triggers
4. Listen foreign keys with non matching column definitions
5. Identify De-fragmentation in table space
6. Look for security policy violations
7. Look in SQL *Net logs for errors
8. Archive all alert logs, Trace files to history



Monthly Activities

1. Checking redo log optimal size based on transactional activities
2. Analyze tables and indexes for better performance
3. Identifying non-system tables with no primary key
4. Identifying non-system tables with excessive indexes causing performance issue
5. Listing unusable indexes and index usage
6. Preparing comprehensive monthly review and reports
7. Review tuning opportunities based on stats pack report

Quarterly Activities

1. Measuring Database growth summary
2. Backup verification on test database


Adhoc Activities

1. Creating project specific (objects, tables, indexes etc) in test and production schema
2. Applying user-creation and any other security policies as defined
3. Identifying and optimizing the queries causing bottlenecks
4. Scheduling jobs & monitoring
5. Participation in any database design discussion and providing feedback and reviews
6. Aligning the backup and recovery process according to the client requirements.