Database Adminstrator's Responsibilities

Each database requires at least one database administrator (DBA) to administer it. Because an Oracle database system can be large and can have many users, often this is not a one person job. In such cases, there is a group of DBAs who share responsibility.

A database administrator's responsibilities can include the following tasks:

(1) Creates and maintains all databases required for development, testing,

education and production usage.

(2) Performs the capacity planning required to create and maintain the

databases. The DBA works closely with system administration staff because

computers often have applications or tools on them in addition to the Oracle

Databases.

(3) Performs ongoing tuning of the database instances.

(4) Install new versions of the Oracle RDBMS and its tools and any other tools

that access the Oracle database.

(5) Plans and implements backup and recovery of the Oracle database.

(6) Controls migrations of programs, database changes, reference data changes

and menu changes through the development life cycle.

(7) Implements and enforces security for all of the Oracle Databases.

(8) Performs database re-organisations as required to assist performance and

ensure maximum uptime of the database.

(9) Puts standards in place to ensure that all application design and code is

produced with proper integrity, security and performance. The DBA will perform

reviews on the design and code frequently to ensure the site standards are

being adhered to.

(10) Evaluates releases of Oracle and its tools, and third party products to

ensure that the site is running the products that are most appropriate.

Planning is also performed by the DBA, along with the application developers

and System administrators, to ensure that any new product usage or release

upgrade takes place with minimal impact.

(11) Provides technical support to application development teams. This is

usually in the form of a help desk. The DBA is usually the point of contact

for Oracle Corporation.

(12) Enforces and maintains database contraints to ensure integrity of the

database.

(13) Administers all database objects, including tables, clusters, indexes,

views, sequences, packages and procedures.

(14) Assists with impact analysis of any changes made to the database objects.

(15) Troubleshoots with  problems regarding the databases, applications and

development tools.

(16) Create new database users as required.

(17) Manage sharing of resources amongst applications.

(18) The DBA has ultimate responsibility for the physical database design.

 The DBA should posses the following skills

(1) A good knowledge of the operating system(s)

(2) A good knowledge of physical database design

(3) Ability to perform both Oracle and also operating system performance

monitoring and the necessary adjustments.

(4) Be able to provide a strategic database direction for the organisation.

(5) Excellent knowledge of Oracle backup and recovery scenarios.

(6) Good skills in all Oracle tools.

(7) A good knowledge of Oracle security management.

(8) A good knowledge of how Oracle acquires and manages resources.

(9) Sound knowledge of the applications at your site.

(10) Experience and knowledge in migrating code, database changes, data and

menus through the various stages of the development life cycle.

(11) A good knowledge of the way Oracle enforces data integrity.

(12) A sound knowledge of both database and program code performance tuning.

(13) A DBA should possess a sound understanding of the business.

(14) A DBA should have sound communication skills with management, development

teams, vendors, systems administrators and other related service providers.

Read More

Relational Database Management System (RDBMS)

RDBMS is a DBMS in which data is stored in tables and the relationships among the data are also stored in tables. The data can be accessed or reassembled in many different ways without having to change the table forms.

A relational database management system (RDBMS) is introduced by E.F Codd But Oracle provides a flexible RDBMS called Oracle9i. Using its features, you can store and manage data with all the advantages of a relational structure plus PL/SQL, an engine that provides you with the ability to store and execute program units. Oracle9i also supports Java and XML. The Oracle server offers the options of retrieving data based on optimization techniques. It includes security features that control how a database is accessed and used. Other features include consistency and protection of data through locking mechanisms.

The Oracle9i server provides an open, comprehensive, and integrated approach to information management. An Oracle server consists of an Oracle database and an Oracle server instance. Every time a database is started, a system global area (SGA) is allocated, and Oracle background processes are started. The system global area is an area of memory used for database information shared by the database users. The combination of the background processes and memory buffers is called an Oracle instance.

Read More

Initialization Parameter Files

Oracle provides two different types of parameter files that you can use, PFILE and SPFILE. When an Oracle Instance is started, the characteristics of the Instance are established by parameters specified within the initialization parameter file. These initialization parameters are either stored in a PFILE or SPFILE. Static parameter file Pfile commonly referred to as intiSID.ora and Persistent parameter file SPfile commonly referred to as spfileSID.ora. A PFILE is a traditional text based init.ora parameter file. Typically this resides on the server in the $ORACLE_BASE/admin/SID/pfile directory.Parameter File contents...

•        A list of instance parameters

•        The name of the database the instance is associated with

•        Allocations for memory structures of the System Global Area (SGA)

•        What to do with filled online redo log files

•        The names and locations of control files

•        Information on undo segments

 Pfile:- Until Oracle 8i, we were using a text file called the PFILE. The PFILE is a text file that can be maintained using a standard operating system editor. The parameter file is read only during instance startup. If the file is modified, the instance must be shut down and restarted in order to make the new parameter values effective. Some parameters are dynamic, which means that they can be modified while the instance is running. Changes to dynamic parameters are not reflected in the PFILE.

By default, the PFILE is located in the $ORACLE_HOME/dbs directory on a Unix machine and named initSID.ora. DBA may keep copies of this file on their local PC to allow remote startup.

SQL> CONNECT sys/password AS SYSDBA

SQL> STARTUP PFILE=C:LocalInit.ora

SPfile:- In Oracle9i, a new feature called SPFILE. SPFILE is a binary file that contains the same information as the old PFILE. If a parameter is changed using the ALTER SYSTEM SET command Oracle will apply this parameter change to the current SPFILE. The file is located in $ORACLE_HOME/dbs and has a default name in the format of spfileSID.ora. Once the file is created it is maintained by the Oracle server. The SPFILE provides the ability to make changes to the database persistent across shutdown and startup. The ALTER SYSTEM command is used to change the value of instance parameters. The SCOPE setting determines the scope of the change. The scope of the ALTER SYSTEM SET command can be defined using the following.

ALTER SYSTEM SET parameter = value SCOPE=[SPFILE/MEMORY/BOTH];

The actions of the scope parameters are listed below.

BOTH - (Default) The parameter takes affect in the current instance and is stored in the SPFILE.

SPFILE - The parameter is altered in the SPFILE only. It does not affect the current instance.

MEMORY - The parameter takes affect in the current instance, but is not stored in the SPFILE.

Creating an SPFILE

An SPFILE is created from an initSID.ora file using the CREATE SPFILE command. This can be executed before or after the database is open.

SQL> CREATE SPFILE FROM PFILE;

One can also specify a non-default location for either (or both) the PFILE and SPFILE parameters

SQL> CREATE SPFILE='/oradata/spfileORCL.ora' from PFILE='/oradata/initORCL.ora';

Related Post:-  

• Manually Database Creation

Read More