Oracle Database Administration Responsibilities

Remember that your two main tasks are to protect and make the database available for use. Everything else tends to fall into either technical responsibilities or non-technical responsibilities and the responsibilities assigned to a particular DBA may vary on the shop.

Database Technical Responsibilities

System Activities

You may come into a project at anything phase of the software development. You may also work on a new project which involves the following steps:

1.  You will meet with the software developer to understand the nature of the application that the database will be supporting.
2. You will plan and design the database with the application team and data modelers will come up with entity relationship diagrams (ERD) of tables based on the user requirements which you will review and approve before implementing.
3. Once the system is designed, you will build and test it.
4. You will meet with the system administrators to provision for a server that meets your requirements and they will build and configure the server and turn it over to you.
5. You will then install the oracle software and then create the actual database which will be a blank database.
6. After the database is created, you will run the DDL (data definition language) script provided by the developers to create the objects in the database.
7. You will then load the database with data which will be provided to you by the application team.
8. After the database is loaded, you will ask the application developers to test to see if they are able to connect to the database using the application. Of course, you would have provided them with a username/password , database name, connect string which they will use to establish connectivity to the database.
9. You will manage the user accounts and schedule backups to run against  this database.
10. After a period of initial testing, the application will go live( meaning that it will be available for the business processes).
11. You will maintain the database after this stage meaning you will take proactive steps so the data and application is available for use to the end users.
    

    Creating and Managing Users

    Every database has users, you will be responsible for creating these users based on requests submitted by the project team leads. You will be expected to give the users guidelines on appropriate use of their accounts and also implement database security policies within the database based on the company’s security guidelines. You will also ensure that users adhere to this database security policy by enforcing strict rules and giving them only the necessary privileges needed based on their job functions.It’s also the responsibility of the DBA to monitor the resource usage by individual users and to flag the heavy resource users.

    User Accounts

    When users are locked out of the database because their password expired or the entered an incorrect password a certain number of times determined by the password enforcement policy, you will be responsible for unlocking their accounts when they submit the request.

    Note: In order to access a database, a user has to connect through an application such as Oracle Siebel with a username and password that you would have assigned.

    To create a user, you must have the create user privilege system privilege; and this privilege should only be granted to database administrator. A user needs the create session privilege in order to connect to the database. The username must be unique in the database. A user and a role cannot have the same name. A user has its associated schema- where its objects (such as tables, view, sequences, synonyms, indexes and so forth) reside. All these objects must have a unique name within the schema.

    It is a good practice to assign a default tablespace when a user is created. A tablespace is a logical storage structure for objects in a database.

    System Management

    You should have an understanding how the entire system works (database, Unix/Linux server, network, storage etc.) daily monitoring is not limited to the database itself. You will have to monitor the performance of the servers that host the database and of the network that enables connections to the database. In my company if a user tells us that the database is slow, we ask the UNIX system administrators, network team and storage team to check the server, network or storage to see if they find anything usual such as slow responses while we are checking the database that is reported with the issue.

    Troubleshooting
    
    You will be responsible for troubleshooting the database to fix problems and involves knowing how to get the right kind of help from Oracle support personnel, and how to use other Oracle resources to fix problems quickly.

    You may get a call from a user that the database is down and then find out that the user had been locked out of their account. You can also get a call that a tablespace is full meaning that no more data can be added to it and they cannot run their processes. So you will have to increase the size of the tablespace.You could also get a call that an important month-end batch job failed and that all the user needs to do to fix the problem is to add a larger rollback segment to the database or analyze the tables to gather the statistics.

    If the system is so slow that work cannot be completed successfully, it may as well not be running. It may sometimes not be a database issue for example, a network problem or bad piece of application code could be the slow down the system and you may have to work with the application, UNIX, network and storage teams to resolve the issue.

    Performance Tuning

    Performance tuning should start early in the planning phase when tables and data flows are being designed and on to the implementation stage, the testing stage, and the production stage of a database. In simply means making things run faster for the Once the application is built, you usually can only add or remove indexes and tune SQL and PL/SQL code. Sometimes simply adding a missing index has made dramatic improvements for the end users. It is imperative therefore that you work very closely with the developers so that you can catch some poor design errors early on in the project.

    Monitoring the System

    Once a database is actually in production, you job will be monitor the system to ensure uninterrupted service. Some will perform tasks such as monitoring space to ensure it is sufficient for the system (if the database runs out of space, it will crash), checking that jobs run by the users are successful, monitoring the log files on a daily basis for any unusual activities.

    Note: DBAs are expected to be proactive and prevent avoidable downtime such as downtime due to a process running out of space.

    Estimating Requirements
    You will be required to choose an appropriate operation system which the database will run on for example UNIX or Linux. You will also be required to estimate the operating disk space and memory requirements for any new projects. You will also come up with growth estimates for the databases and also for planning future growth and potential changes in the applications (this is called capacity planning).

    Developing Backup and Recovery Strategies

    Adequate backups can prevent the catastrophic loss of an organization’s vital business data. You will be tasked with developing an appropriate backup and recovery strategy and test the backups for corruption.
    You will also develop a recovery strategy in case of a disaster. The best way to do this is to simulate several types of data loss by implementing, testing, and documenting the various backup and recovery scenarios. Regular testing of the backups and practicing recoveries is very critical in the event of a disaster.

    Loss of business data not only leads to immediate monetary damage in the form of lost revenue, but also cost your customers/users to lose trust in you. Unplanned database downtime reflects poorly on a company’s reputation.

    Because the data that resides in our database today is so important to the agencies or to the companies that we work for, the loss of that data is absolutely devastating to the economic well-being of that particular organization. So one of the most critical skills that a DBA can have today is the ability to recover data that is lost due to floods, electrical outages, disk crashes, hard ware failure, operating system issues, human
    error, corruption, etc.

    Every company that I know of today has a backup plan and a backup schedule for their databases. They usually take full backup at least once a week. And incremental backups every evening. So they vary the full and the incremental based on the needs that they have. But it’s important to have a regular backup schedule for your particular database.

    And it’s also important that you as a DBA be able to recover from that backup. So you want to be sure that you can test your backup plan and be sure that you can recover the data.

    Overseeing Change Management

    Remember that the database is there to support the business applications and these applications are currently getting tested to fix bugs, so these changes have to be implemented at the database level as well. Every application goes through changes overtime to improve features and fix bugs in the software. There is a constant cycle of development, testing, and implementation, and you will play an important role in that cycle. Change management is the process of properly migrating new code, and you will need to understand the process that’s in place in his or her organization.

    It is your responsibility to ensure that all the latest changes to the database software are also evaluated and adopted. These so-called software patches are usually made available through Oracle support website.

    Database Design

    You may be involved in a new project from the beginning to the implementation(meaning when you create the objects and load the database) phase and this will require that you assist the data modelers with the design of the database. This may involve creating entity-relationship diagrams and suggesting dependencies and candidates for primary keys.

    Working with the application team during the design will eliminate a lotof issues in the long run as you would help the team make better design choices during the design phase and thereby improve the performance of the database down the road.

    Many database are slow due to poor design issues

    Installing and Upgrading Software

    You will be responsible for installing the Oracle software depending on your role; the UNIX system Administrator may also handle part of the installation process.

    In my shop, the system administrator is responsible for creating the oracle user and setting the memory parameters on the UNIX server and storage team will be responsible for providing the disks to house our database prior to actual installation.

    It is our responsibility to coordinate this with all the teams involved. Meaning that it is your responsibility to list all the memory and disk requirements so that the Oracle software and databases, as well as the system itself, can perform adequately.

    Creating Databases

    You will be responsible for the creation of databases based on the user requirements.For example a user may want a small database or a very large database of a certain size,and memory requirements that suits their needs. Based on your role, you may be required to plan the logical design of the database structures, such as creating the table spaces and creating the objects after the database is created.

    You will be required to work closely with the application team to come up with the proper sizing estimates for
    the database objects such as tables and indexes. Ensure that all the requirement is very clear, written down and approved by all team members before creating the database.

    Note: A tablespace is a logical structure and houses the data. The objects you create such as tables, indexes, views and so forth are stored on tablespaces.

    Creating Database Objects

    So you have created a database but there is no nothing in it. You will work with the developers to create the various objects. Such as tables, indexes, views, synonyms and so on. The developers will provide you with a script that contains the tables and indexes to be created which you will review with their team and make
    recommendations to improving the performance.

    Once the final script is approved by you. You will run it in the application schema which you would already have created within the database with the appropriate privileges.

    Note: Remember that the schema is a collection of the objects of a particular user. Schemas are created for each application that the database is supporting within the database and this is where all the objects will be created.

    Loading Data

    Now that you have created the database users, schemas, and objects (tables, indexes, view), you will now load the data. If the data loads need to be on a periodic basis, you will have to design, test and implement appropriate loading programs.

    Non-Technical Responsibilities
    
    Oracle Expert

    You would be considered the oracle expert, the go to person for solutions to user issues and recommendations. The majority of your users will have limited knowledge about the oracle database and its products, so it will be your job to educate them.

    Oracle Point of Contact

    You will be the oracle point of contact in your company. As the DBA you will recommend the level of Oracle support and make sure it is used by your organization.

    It is your job to make sure all the technical people in your organization (such as the developers and System Administrators) have access to online support services. When your user open a ticket with call Oracle Support to troubleshoot and issue with the database, it is your job to make sure they get the answers they need and to ensure that they get a solution.

    Process Expert

    You should understand the core business processes of the organization (the “how” and “why” of the business) because you have access to the data and application processes of so that you can weed out inefficient processes and optimize those processes that are efficient.