Database Administrator Roles and Responsibilities

A Database Admininstrator, Database Analyst or Database Developer is the person responsible for managing the information within an organization. As most companies continue to experience inevitable growth of their databases, these positions are probably the most solid within the IT industry. In most cases, it is not an area that is targeted for layoffs or downsizing. On the downside, however, most database departments are often understaffed, requiring adminstrators to perform a multitude of tasks.

Depending on the company and the department, this role can either be highly specialized or incredibly diverse. The primary role of the Database Administrator is to adminster, develop, maintain and implement the policies and procedures necessary to ensure the security and integrity of the corporate database. Sub roles within the Database Administrator classification may include security, architecture, warehousing and/or business analysis. Other primary roles will include:

• Implementation of data models
• Database design
• Database accessibility
• Performance issues
• Capacity issues
• Data replication
• Table Maintainence

Database Administrators are often on-call and required to work as needed. This position carries an enormous amount of responsibility.

UNDO SPACE ACQUISITION AND MANAGEMENT

When you open the database , the instance e attempts to acquire one or more tablespaces or roll back, segments you determine whether to operate in automatic undo management mode or manual undo management mode at instance start up using the UNDO_MANAGEMENT initialization parameter. The supported values are AUTO or MANUAL, if AUTO , then the instance is started in automatic undo management mode. The default value is MANUAL.


1) If you use the undo tablespace method, you are automatic, undo management mode,. This is recommended.

2)If you use the rollback segment method of managing undo sapce, then you are using manual undo management mode.

Instance Recovery

If the database was last closed abnormally(say Shutdown Abort) either because the database administrator terminated its instance of because of a power failure, then Oracle automatically performs recovery when the database is re opend.

DATABASE ADMINISTRATOR

is a person responsible for the design, implementation, maintenance and repair of an organization's data base are also known by the titles Database Coordinator or Database Programmer, and is closely related to the Database Analyst, Database Modeler, Programmer Analyst, and Systems Manager. The role includes the development and design of database strategies, monitoring and improving database performance and capacity, and planning for future expansion requirements. They may also plan, co-ordinate and implement security measures to safeguard the database.^[Employing organizations may require that a database administrator have a certification or degree for database systems (for example, the Microsoft Certified Database Administrator).

How a Database is Mounted

The instance mounts a database to associate the database with that instance. To mount the database the instance control files and opens them.Control files are specified are in the CONTROL_FILES initialization parameter in the parameter file used to start the instance.Oracle then reads the control files to get the names of the database's and redo log files. At this point, the database is still closed and is accessible only to the database administrator. The administrator can keep the database closed while completing specific maintenance operations. However the database is not yet available for normal operations.

How a Clone Database is Mounted.

A clone database is a specified copy of a database that can be used for tablespace point-in-time recovery. When you perform tablespace point-in-time recovery, you mount the clone database and recover the tablespaces to desired time, then export metadata from the clone to the primary database and copy the data files from the recovered tablespaces.

How Database Is Mounted

The Instance mounts a database to associate the database with that instance.To mount the database.the instance finds the database control files and opens them.Control files are specified in the CONTROL_FILES initialization parameter in the parameter file used to start the instance.Oracle then reads the control files to get the names of the database's data files and redo log files.At this point, the database is still closed and is accessible only to database administrator. The database administrator can keep the database closed while completing specific maintenance operations. However , the database is not yet available for normal operations.

How a Clone Database Is Mounted

A clone database is a specialized copy of a database that can be used for tablespace point-in-time recovery . When you perform tablespace point-in-time recovery. you mount the clone database and recover the tablespace to the desired time. then export meta data from the clone to the primary database and copy the data files from the recovered tablespaces.

Restricted Mode of Instaance Startup

You can start an instance in restricted mode(or later alter an existing instance to be in restrcted mode). This restricts connections to only those users who have been granted the RESTRICTES SESSION system privilege.

Forced Startup in Abnormal Situations

In unusual circumstances , a previous instance might not have been shut down cleanly. For example, one of the instance's processes might not have terminated properly . In such situations, the database can return an error during normal instance startup. To reslove this problem, You must terminate all remnant Oracle processes of the previous instance before starting the new instance.

Instance and Database Startup

The three steps to starting an Oracle database and making it avilable for system wide use are.

1) Start an instance.

2) Mount the databse.

3) Open the database.

A database administrator can perform these steps using the SQL*PLUS STARTUP statement or Enterprise Manager.

How an Instance is Started

When oracle starts an instance , it reads the intilization parameter file to determine the values of intilization parameters. Then, it allocates an SGA, which is a shared area of memory used for database information, and creates background processes. At this point no database is associated with these memory structures and processes.

How Parameter Values Are Changed

The database administrator can adjust variable parameters to improve the performence of a database system Excatly which parameters most affect a system depends on numerous database characteristics and variables.

Some parameters can be changed dynamically by using the ALTER SESSION or ALTER SYSTEM statement while the instance is runnig. Unless you are using a server file, changes made using the ALTER SYSTEm statement are only in effect for the current instance. You must manually update the text intilization parameter file for the changes to be known the next time you start up an instance. When you use a server parameter file. you can update the parameters on disk. so that persist across database shutdown and startup.

Initialization Parameter Files.

a. Parameter that name things, such as files.

b. Parameter that set limits, such as maximums

c. Parameters that affect capacity, such as the size of the SGA, which are called.

VARIABLE PARAMETERS

1) Among other things the intialization parameters tell Oracle.

2)The name of the database for which to start up an instance.

3)How much memory to use for memory structure in the SGA

4)What to do with filled online redo log files.

5)The names and locations of the database control files.

6). The names of undo tablespaces or private rollback segments in the database.

Connection with Administrator Privileges.

Database startup and shutdown are powerful administrative options and are restricted to users who connect to Oracle with administrator privileges. Depending on the operating system. one of the following conditions establishes administrator privileges for a user.

1) The user's operating system privilleges allow him or her to using administrating privileges.

2)The user is granted the SYSDBA or SYSOPER privileges and the database uses password files to authenticate database administrators.

When you connect with SYSDBA privilleges, you are in the schema owned by SYS.

When you connect as SYSOPER , You are in the PUBLUC schema.SYSOPER privileges are a subset of SYSDBA privileges.

The Instance and the Database

After starting an instance, Oracle associstes the instance with the specified databases. This is called mounting the database. The database is then ready to be opened. which makes it accessible to authorized users.

Multiple instances can run concurrently on the same computer, each accessing its own physical databse. In clustered and massively parallel systems(MPS). Real Application clusters enables instances to mount a single database. Only the database administrator can start up an instance and open the database. If a database is open, then the database administrator can shut down the database so that it is closed .When a database is closed, users can not access the information that it contains.Security for database startup and shutdown is controlled through connections to Oracle with administrator prvilliges. normal users do not have control over the current status of an oracle database

ORACLE INSTANCE

Single Instance systems.

An oracle database server consists of an Oracle database and an orcale instance.Every time database is started a system global area (SGA) is allocated and oracle background processes are started. The combination of the background processes and memory buffers is called an oracle instance.

Every running Oracle database is associated with an oracle instance. When a database is started on a databse server (regardless of the type of computer).Oracle allocates a memory area called the system global area (SGA) and starts one or more oracle processes.This combination of the SGA and the Oracle processes is called an Oracle instance. The memory and process of an instance manage the associated databases data efficiently and serve the one or multiple users of the database.

Over View of Oracle Grid Architecture

Oracle Database is the first database for enterprise grid computing , the most flexible and cost effective to manage information and applications. Enterprice grid computing creates large pools of industry-standard, modular storage and servers. With this architecture. each new system can be rapidly provisioned from the pool of components. There is no need for peak workloads, because capacity can be easily added or reallocated from the resource pools as needed.with grid computing, groups of independent , modular hardware and software components can be connected and rejoined on demand to meet the changing needs of businesses.

The oracle grid architecture pools large numbers of servers, storage, and networks into a flexble on-demand computing resource for enterprise computing needs. The grid-computing infrastructure continually analyzes demand for resources and adjusts supply accordingly.

Grid computing uses sophisticated workload management that makes it posssible for applications to share resources across many servers.Data processing capacity can be added or removed on demand, and resorces with in a location can be dynamically provisioned. Web services can quickly integrate applications to create new business processes.

OVERVIEW OF DATABASE :

An oracle database is a collection of data treated as a unit.The purpose of a database is to store and retrieve related information.A data base server is the key to solving the problems of information of management.in general,A server reliably manages a large amount of data in a multi-user environment so that many users can concurrently access the same data.All this is accomplished while delivering high performance.A data base server also prevents unauthorized access and provides efficient solutions for failure recovery.

The database has logical structures and physical structures :


1.The physical database structures of an oracle database,including,

a. Data files

b. Redo log files

c. Control files


2.The logical storage structure includes

a. Data blocks

b. Segments

c. Table spaces