Introducing Oracle database administration

Learning objective

After completing this topic, you should be able to identify the basic tasks involved in Oracle database administration.

1. Oracle database products

Oracle produces a range of database products, applicable to different business requirements.

• Oracle Applications
• Oracle Application Server
• Oracle Collaboration Suite
• Oracle Database
• Oracle Developer Suite
• Oracle Services

Oracle Applications

Oracle Application Server

Oracle Collaboration Suite

Oracle Database

Oracle Developer Suite

Oracle Services

Question

Match the Oracle database products to their descriptions.

Options:

1. Oracle Applications
2. Oracle Application Server
3. Oracle Collaboration Suite
4. Oracle Database
5. Oracle Development Suite

Targets:

1. Supports enterprise grid computing, clustering, and data warehousing
2. An e-business suite comprising different business applications
3. An application-development environment with business tools
4. Enables the development and deployment of web-based applications
5. Integrates communications data such as voice, e-mail, and fax?

Answer

The Oracle database products have been correctly matched to their descriptions.

Oracle Applications enables the management and automation of processes across an organization.

Oracle Application Server is a J2EE-certified application server.

Oracle Collaboration Suite also enables you to share files and to store and retrieve information from an Oracle database.

Oracle Database is available in three editions: Enterprise, Standard, and Personal.

Oracle Forms and Oracle Reports are examples of Oracle Development Suite tools.

2. Database administrator tasks

The main responsibility of an Oracle database administrator (DBA) is to ensure that the database is available to its users.

As an Oracle DBA, you need to perform a number of tasks when designing and implementing an Oracle database.

• plan the database
• evaluate the database server hardware
• install the Oracle software
• create and open the database

plan the database

You need to plan the logical storage structure, overall design, and backup strategy for the database.

evaluate the database server hardware

The evaluation should include information on disk-drive and tape-drive availability for the Oracle software, as well as available memory for the Oracle Database instances.

install the Oracle software

You install the Oracle Database server software and any front-end tools and database applications that will access the database. In a distributed installation, you also need to install the components that connect the remote machines to the database server.

create and open the database

Once you've completed the database design, you create the database and open it so it can be used. You can create a database using the Database Configuration Assistant, or by scripting.

Once you've created and opened the database, DBA tasks include:

• backing up the database
• enrolling system users
• implementing database design
• performance monitoring

backing up the database

Depending on your planned backup strategy, you need to create any additional redo log files, perform the first full database backup, and schedule future database backups.

enrolling system users

After backup, you can enroll database users and assign them the appropriate roles and privileges.

implementing database design

Once you've enrolled the database users, you implement the planned logical structure of the database by creating tablespaces for storage, and then the database objects. Following this, you should back up the database again.

performance monitoring

Monitoring and tuning database performance is one of the main ongoing responsibilities of a DBA. You can use Oracle's database resource management feature to control resource allocation among groups of users.

Question

Suppose you're a DBA and you've just installed Oracle Database software.

Rank the tasks in the order in which they should be performed next.

Options

Option	Description
A	Back up the database
B	Create and open the database
C	Enroll database users

Answer

Correct ranking

Option	Description
B	Create and open the database You can create the database using the Database Configuration Assistant, or by scripting.
A	Back up the database Following the initial backup, you should schedule regular backups of the database.
C	Enroll database users When you enroll users, you can assign them the appropriate roles and privileges.

3. SQL functionality

Structured Query Language (SQL) is the language used by administrators, users, and applications to interact with the database. SQL uses a set of standalone statements, or instructions, to the database, for retrieving and updating database information.

SQL provides an interface between the user and the database - that is, the user does not need to know how SQL retrieves information from the database.

Note

SQL now supports flow-control statements and persistent stored modules.

SQL adopts a logical approach to data. For example, when you want to retrieve a row of data, you specify a condition to filter the rows. All rows fulfilling that condition are retrieved and can be passed to the user, to another SQL statement, or to an application.

In this way, SQL provides automatic navigation to data and processes sets of data as groups, rather than as individual rows.

SQL statements also use the Oracle database optimizer to determine the most efficient way to access and retrieve data.

Some of the tasks you can perform using SQL include:

• controlling database access
• guaranteeing database integrity
• manipulating database objects
• manipulating table rows
• querying data

controlling database access

You can use SQL to control access to the database and its objects.

guaranteeing database integrity

You can ensure database consistency and integrity using SQL.

manipulating database objects

You can use SQL to create, replace, alter, and drop database objects.

manipulating table rows

You can use SQL to insert, update, and delete rows in a table.

querying data

SQL provides statements for querying data in all kinds of relational databases.

Question

Which of these tasks can you use SQL to perform?

Options:

1. Plan the database
2. Manipulating database objects
3. Manipulating table rows
4. Querying databases

Answer

You can use SQL to control and manipulate database objects and table rows, and to query databases.

Option 1 is incorrect. You plan the database before you install Oracle. This is usually completed on paper or within a word processing application.

Option 2 is correct. You can use SQL to create, replace, alter, and drop database objects.

Option 3 is correct. You can use SQL to insert, update, and delete rows in a table.

Option 4 is correct. SQL is a query language that is used for all relational database queries.

Summary

Oracle's range of database products, including Oracle Application Server, Oracle Applications, and Oracle Developer Suite, serves diverse. business needs.

The Oracle database administrator (DBA) is responsible for planning and evaluating the database and its resources, installing Oracle, and creating and opening the database. Once the database is available to users, the DBA is responsible for backing it up regularly, creating user roles and privileges, implementing database design, and monitoring database performance.

Structured Query Language (SQL) is the language used for retrieving and updating database information. You can use SQL statements to control database access, preserve database integrity, manipulate database objects and table rows, and to query data.