Introducing RDBMS

Learning objective

After completing this topic, you should be able to recognize the characteristics of a relational database system.

1. Introducing relational databases and RDBMSs

A relational database management system (RDBMS) is a system in which data is managed in related tables. Relational databases have become the standard database type in business today.

The standard language used to manipulate relational databases is Structured Query Language (SQL). The Oracle RDBMS was the first commercial implementation of SQL.

Unlike a flat-file database, a relational database system is composed of multiple two-dimensional tables. Arranging data in tables enables you to search for specific data more effectively, analyze data comparatively, and sort data in a particular data category.

A database table is composed of three elements.

• column
• field or cell
• row

column

A column contains data of a specific data type, and has its own name. For example, a row of data in an Employees table could be divided into columns such as name, ID, and salary.

field or cell

A field or cell can be defined as a single column of a single row, or the intersection point of a column and row. For example, a field or cell in an Employees table could contain a given employee's ID number.

row

A table consists of rows of data, or records, which can be arranged into one or more columns. Each row contains data related to a specific subject. For example, a row of data in an Employees table would represent a single employee.

Question

Match the components of a database table to their descriptions.

Options:

1. Field or cell
2. Row
3. Column

Targets:

1. Has a name and a data type associated with it
2. A single column of a single row
3. A collection of related data items

Answer

A column has a name and a data type associated with it. A field or cell is a single column of a single row. And a row is a collection of related data items.

For example, a cell in a customer table could contain a given customer's telephone number.

For example, a row of data in a customer table might represent a single customer.

For example, a column of data called Total Purchases in a customer table might hold a numeric data type.

2. Relationships between tables

Relational databases work by creating relationships between tables using columns that are common to more than one table. Relational databases are typically composed of many tables linked in this way.

In a relational database, tables that are related by a given column may share the same column values.

Instead of each table duplicating these values, each field in the column can contain a value, known as an identifier, that refers to a separate table containing the relevant data.

Suppose you have two tables, Customer and City. Each customer in the table must be associated with a city, but instead of listing each customer's city in the Customer table, the fields of the City column are identifiers that point to values in the City table.

Each entry in the Customer table points to only one row in the City table.

In relational database terms, an identifier is a key field. In the Customer table, the fields in the City column are known as the foreign key, as they point to values in a different, or foreign, table.

And in the City table, the primary keys are the fields to which the foreign key in the Customer table is pointing. The primary key field must always contain a value, and it must be unique.

Question

Suppose you have two tables, Employees and Department. Because every employee must be assigned to a department, each value in the Department column of the Employees table points to a value in the Department table.

Which statements accurately reflect the relationship between the tables?

Options:

1. Each entry in the Employees table points to only one row in the Department table
2. The Department table contains the primary key
3. The Employees table contains the foreign key
4. The key field in the Department table doesn't need to have a value

Answer

Each entry in the Employees table points to only one row in the Department table. The Department table contains the primary key, and the Employees table contains the foreign key.

Option 1 is correct. The primary key, in this case in the Department table, must contain a unique value so that each identifier in the Employees table points to a value.

Option 2 is correct. The primary key is the field in the Department table that is referenced by the identifier in the Employees table.

Option 3 is correct. This is because the Department fields of the Employees table point to values in another table.

Option 4 is incorrect. The primary key field must always contain a value, and it must be unique.

3. Integrity constraints

You can ensure that a relational database contains only valid data by implementing integrity constraints.

These are rules or constraints, specified at database creation and enforced by RDBMS, that must remain true in order to preserve database integrity.

An example of integrity constraints at work would be to implement a constraint in an automobile component parts database that demanded that every vehicle entered into the database had four wheels as standard.

This would ensure that data for motorcycles would not be entered into the database in error.

Oracle implements integrity constraints by including them as part of the table's definition in the data dictionary. All applications accessing the table must abide by the rules defined in the constraints.

Thus, if you need to change a constraint for any reason, you need only change it once in the database and not at application level.

There are five types of constraints supported by Oracle Database 10g.

• CHECK
• FOREIGN KEY
• NOT NULL
• PRIMARY KEY
• UNIQUE

CHECK

The CHECK constraint ensures that a value cannot be entered if it does not satisfy the logical expression of the constraint. This can be used to ensure data integrity and validity.

FOREIGN KEY

The FOREIGN KEY constraint requires each value in a column or columns to match a value in a related table's UNIQUE or PRIMARY KEY. It is also used to define what should happen to dependent data if the data referenced by it is altered.

NOT NULL

The NOT NULL constraint does not permit nulls, or empty entries, in the columns of a table. However, it doesn't prevent duplicate values from being entered.

PRIMARY KEY

The PRIMARY KEY constraint does not allow duplicate values or nulls (empty entries) in columns.

UNIQUE

The UNIQUE constraint does not allow duplicate values to be entered in columns. However, it doesn't prevent nulls being entered.

Question

Match the five constraints used by Oracle Database 10g to their uses.

Options:

1. CHECK
2. FOREIGN KEY
3. NOT NULL
4. PRIMARY KEY
5. UNIQUE

Targets:

1. Allows only values that satisfy the logical expression of the constraint
2. Requires column values to match the values in a related table's UNIQUE or PRIMARY KEY
3. Does not allow nulls in the columns of a table
4. Does not permit duplicate values or nulls in columns
5. Does not allow duplicate values to be entered in columns

Answer

The constraints have been correctly matched to their uses.

The CHECK constraint can be used to ensure data integrity and validity.

The FOREIGN KEY constraint is also used to define what should happen to dependent data if the data referenced by it is altered.

The NOT NULL constraint does not prevent duplicate values being entered.

The primary key field must contain a value, and it must be unique.

However, the UNIQUE constraint does not prevent nulls being entered in columns.

Summary

A relational database management system (RDBMS) is a system whereby data is managed in multiple, two-dimensional tables and manipulated using Structured Query Language (SQL). A database table is composed of rows, columns, and cells or fields.

Relationships between tables are created using columns that are common to more than one table. These contain values, or identifiers, that refer to values in separate tables. Fields containing identifiers are called the foreign key, and the fields referenced by them are called the primary key. The primary key field must always contain a unique value.

You can ensure that a relational database contains only valid data by implementing integrity constraints, which are rules that must remain true in order to preserve database integrity. Constraints supported by Oracle Database 10g include PRIMARY KEY, FOREIGN KEY, and NOT NULL.

Creating an Oracle 10g database

Learning objective

After completing this topic, you should be able to create an Oracle database, and delete one, using the Database Configuration Assistant.

Exercise overview

In this exercise, you're required to create a database and delete a database using the Database Configuration Assistant (DBCA).

This involves the following tasks:

• starting the DBCA
• creating a database
• configuring a database
• deleting a database

Let's say you're a database administrator and you've installed Oracle Database 10g software on your system. You now need to create a new database.

Task 1: Starting the DBCA

Start the DBCA from the terminal window.

Steps list

Instructions
1. Type dbca in the terminal window and press Enter

Task 2: Creating a database

Using the DBCA, create a new database using the General Purpose template. Name the database "orcl2", accept the default management options, and set the password for all accounts to "oracle". Use the file system for database storage.

Steps list

Instructions
1. Click Next
2. Select Create a Database and click Next
3. Select General Purpose and click Next
4. Enter orcl2 in the Global Database Name field and click Next
5. Click Next
6. Enter oracle in the Password and Confirm Password fields and click Next
7. Select File System and click Next

Task 3: Configuring a database

You have already configured a number of options for the new orcl2 database and now you want to complete the steps of the DBCA, starting by specifying locations for the database files.

Specify "/home/oracle/oradata" as the common area for storing all your database files. Use the default location for storing your backup and recovery files, and set "2048" as the size for this location. Finally, instruct Oracle to archive your database redo logs and finish the database on step 8 of the DBCA .

Steps list

Instructions
1. Select Use Common Location for All Database Files
2. Enter /home/oracle/oradata in the Database Files Location field and click Next
3. Select Specify Flash Recovery Area and enter 2048 in the Flash Recovery Area Size field
4. Select Enable Archiving
5. Click Finish
6. Click OK

Task 4: Deleting a database

After you create the orcl2 database, you realize that another database on your system - orcl8 - is now redundant. You start the DBCA and navigate to the Operations page.

You have restarted the DBCA and navigated to the Operations page. Drop the orcl8 database and then exit the DBCA.

Steps list

Instructions
1. Select Delete a Database and click Next
2. Select the orcl8 database and click Finish
3. Click Yes
4. Click No

The orcl2 database has now been created and configured, and the orcl8 database has been deleted.

Finalizing the Oracle 10g instance

Learning objective

After completing this topic, you should be able to recognize how to set database storage settings and creation options, and delete a database using the Database Configuration Assistant

1. Database storage parameters

The Database Storage page of the Database Configuration Assistant (DBCA) enables you to view and configure the storage settings for control files, data files, and online redo log files.

Say, for instance, you want to view the control files for the database. You first need to access the Controlfile page.

You click Controlfile to do this.

Control files contain information about the physical structure of the database. The DBCA automatically creates three identical copies of a control file.

Ideally, each copy of a control file should be kept on a separate disk to protect against failure and maintain data integrity.

You can enter details for a new control file in the blank File Name field.

You can also change the location of an existing file by overtyping the entry in the File Directory field.

In this example, you want to create a new control file, so you enter the name of the file in the File Name field and the desired location in the File Directory field.

You next click Datafiles to view the data files associated with the database.

These files contain the information in the database. When creating a database from a template, you cannot add or alter any data files at this point.

You want to view the redo logs associated with the database, so you next select the Redo Log Groups folder.

Redo logs are managed in groups. Each redo log in a group is identical to the other members.

Ideally, each member of a redo log group should be kept on a separate disk to protect against failure and maintain data integrity.

Suppose you want to add a member to Redo Log Group 1.

You select the Redo Log Group 1 folder, enter redo04.rdo in the File Name field, and click Next.

This brings you to the Creation Options page of the DBCA.

Question

What database storage settings can you configure for a database using the DBCA?

Options:

1. Add a member to a redo log group
2. Change the location of a control file
3. Change the password for a database
4. Create a new control file

Answer

You can use the DBCA to add a member to a redo log group, create a new control file or change the location of an existing control file.

Option 1 is correct. The redo logs in a group are identical.

Option 2 is correct. Ideally, each copy of a control file should be kept on a separate disk to protect against failure and maintain data integrity.

Option 3 is incorrect. Changing the password for a database is not a storage setting. You use the Password Management window to change passwords and unlock database accounts.

Option 4 is correct. The DBCA automatically creates three identical copies of a control file, and you can create further copies if you wish.

Question

Suppose you are using the DBCA to configure your database storage settings. You need to create a new control file named "control04.ctl". You also need to add a member named "redo04.rdo" to Redo Log Group 1.

Which sequence of steps would you use to complete this task?

Options:

1. You enter control04.ctl in the File Name field. Next, you select the Redo Log Groups folder 1 and enter redo04.rdo in the File Name field. Finally, you click Next.
2. You click Create and enter control04.ctl in the File Name field. Next, you select the Redo Log Groups folder 1 and click Create. Then, you enter redo04.rdo in the File Name field.

Answer

You enter control04.ctl in the File Name field. Next, you select the Redo Log Groups\folder 1 and enter redo04.rdo in the File Name field. Finally, you click Next.

2. Database creation options

On the Creation Options page, you can choose to create the database immediately by selecting Create Database.

You also have the option of saving your database configuration as a template. This template can be used later to create databases using the same parameters.

You decide to create the database and save it as a template at the same time.

You select Save as a Database Template and click Finish.

The Confirmation window allows you to review the settings you have chosen for your database before it is created. These settings include common options, initialization parameters, data files, control files, and redo log groups.

You are satisfied with the options you have selected, so you click OK.

A Database Configuration Assistant dialog box appears, giving you the opportunity to unlock accounts and change passwords before completing the database configuration.

Say you want to unlock the HR database account and change its password. You first need to access the Password Management page.

You click Password Management to do this.

The Password Management dialog box now appears.

You need to unlock the HR account, so you click the blue checkmark beside it in the Lock Account? column.

To set the password, you enter pass in the New Password and Confirm Password fields and click OK.

Once you have succeeded in unlocking the HR account and changing its password, you click Exit to close the DBCA.

Question

Which of the following actions can you perform when configuring database creation options on the Creation Options page of the DBCA?

Options:

1. Change passwords
2. Create a database
3. Delete a database
4. Save your configuration as a database template

Answer

You can use the Creation Options page of the DBCA to create a database immediately or to save your configuration as a database template.

Option 1 is incorrect. You use the Password Management window to change passwords.

Option 2 is correct. Choosing the Create Database option creates the database immediately.

Option 3 is incorrect. To delete a database, you need to restart the DBCA and navigate to the Operations page.

Option 4 is correct. When you select Save as a Database Template, the template can be used later to create databases using the same parameters.

Question

Suppose you have created a database using the DBCA. You want to be able to log in under the OE username using the password "pass". However, this username is currently locked.

Which sequence of steps would you use to complete this task?

Options:

1. You click Password Management and select the OE database account. Next, you click the blue checkmark beside it in the Lock Account? column. Finally, you enter pass in the New Password and Confirm Password fields and click OK.
2. You click Password Management and select the OE database account. Next, you click the blue checkmark beside it in the Lock Account? column. Finally, you click OK.

Answer

You click Password Management and select the OE database account. Next, you click the blue checkmark beside it in the Lock Account? column. Finally, you enter pass in the New Password and Confirm Password fields and click OK.

3. Deleting a database

In addition to creating databases, the DBCA can be used to perform a number of other operations, such as deleting a database, configuring database options, and managing database templates.

You can specify which action you want to perform on the Operations page of the DBCA.

Suppose you no longer need the orcl8 database that you created, so you decide to delete it.

You select Delete a Database and click Next.

A list of available databases appears.

You want to delete the orcl8 database, so you select it and click Finish.

A confirmation window appears, asking if you are sure you want to delete the database.

You click Yes.

When the orcl8 database has been deleted, a window appears asking if you want to perform any other operations.

You click No and this closes the DBCA.

Question

Suppose you decide that you no longer need the orcl9 database. You want to remove this database from your system permanently.

Which sequence of steps would you use to complete this task?

Options:

1. Select Delete a Database and click Next. Select the orcl8 database and click Finish. Finally, you click Yes to complete the deletion.
2. Select Delete a Database and click Finish.

Answer

To delete a database, you select Delete a Database and click Next. Then you select the database and click Finish. Finally, you click Yes to complete the deletion.

Summary

The Database Storage page of the Database Configuration Assistant (DBCA) enables you to view and configure the storage settings for control files, data files, and online redo log files.

You can create a database, save your existing database configuration as a template or create and save as a template simultaneously. You can use the Password Management dialog box to unlock database accounts and change passwords.

As well as creating databases, the DBCA can be used for other operations, such as dropping a database, configuring database options, and managing database templates.

Setting file locations and parameters in Oracle 10g

Learning objective

After completing this topic, you should be able to recognize how to use the Database Configuration Assistant to set file locations and parameters.

1. File location, backup, and recovery

The Database Configuration Assistant (DBCA) provides a simple way of configuring the file location, backup, and recovery settings for your database.

The Database File Locations page offers three options when specifying a location to create database files.

• Use Database File Locations from Template
• Use Common Location for All Database Files
• Use Oracle-Managed Files

Use Database File Locations from Template

Selecting the Use Database File Locations from Template option instructs the DBCA to use the directory information as specified in the template.

Use Common Location for All Database Files

The Use Common Location for All Database Files option requires you to specify a new common area for all your database files.

Use Oracle-Managed Files

The Use Oracle-Managed Files option eliminates the need for you to
manage Oracle database operating-system files directly. However, you must specify a default location called a database area for all your files.

You decide to keep all of your database files in a common area.

You select Use Common Location for All Database Files.

You enter /home/oracle/oradata in the Database Files Location field.

You can use variables to specify some standard locations for files used by the database. To review or add additional locations, you click File Location Variables.

To modify these values you must exit the DBCA, change them in the Operating System environment, and then restart the DBCA.

Here, you close the File Location Variables window by clicking OK, and then click Next to continue.

Note

The File Location Variable page is available from several of the DBCA pages.

Oracle can store and manage files related to backup and recovery in a flash recovery area. If you select Specify Flash Recovery Area in the DBCA, you can choose a location to store these backup and recovery files.

The Flash Recovery Area Size field allows you to specify the amount of space Oracle can use for the flash recovery area.

In a production database, the database files and the flash recovery area are usually located on separate disks to avoid data corruption and loss of performance.

You decide to accept the default location, but want to change the size of the area.

You select Specify Flash Recovery Area.

You then enter 2048 in the Flash Recovery Area Size field.

The Recovery Configuration screen gives you the option to archive the database redo logs. These archived redo logs are used when recovering the database.

In this example, you decide to enable archiving so you select the Enable Archiving checkbox.

If you want to provide specific archive parameters for your database, you can use the Edit Archive Mode Parameters button.

This brings you to the Database Content page of the DBCA.

When you finish configuring recovery options for the database, you click Next.

This brings you to the Database Content page of the DBCA.

Question

Match each file location option to the situation in which you would use it.

Options:

1. Use Common Location for All Database Files
   
2. Use Database File Locations from Template
   
3. Use Oracle-Managed Files

Targets:

1. You don't want to manage your database files directly
2. You want to specify a new area where all of your database files will be created
3. You want to use the directory information contained in the template

Answer

The Use Database File Locations from Template option allows you to use the directory information contained in the template. The Use Common Location for All Database Files option specifies a new common area for all of your database files, and the Use Oracle-Managed Files option eliminates the need for you to manage your database files directly.

This option requires you to specify a common area for all of your database files to be created in.

This option uses the file location settings specified in the template.

This option allows Oracle 10g to manage your database files.

Question

Which of these are backup and recovery options provided by the DBCA?

Options:

1. Specify Flash Recovery Area
2. Enable Archiving
3. Use Oracle-Managed Files
4. Multiplex Redo Logs and Control Files

Answer

Specify Flash Recovery Area and Enable Archiving are backup and recovery options provided by the DBCA.

Option 1 is correct. If you select this option in the DBCA, you can specify a location to store backup and recovery files.

Option 2 is correct. Selecting Enable Archiving in the DBCA puts the database in archive log mode when it is created.

Option 3 is incorrect. The Use Oracle-Managed Files option is a file location option provided by the DBCA.

Option 4 is incorrect. The Multiplex Redo Logs and Control Files option is available if you have specified that you want Oracle 10g to manage your database files.

Question

Suppose you are using the DBCA to configure your database settings. You want to store your backup and recovery files using the default path, and set a size limit of 2048 MB. You also want to archive your database redo logs.

Which sequence of steps would you use to complete this task?

Options:

1. Select Specify Flash Recovery Area and enter 2048 in the Flash Recovery Area Size field. Then select Enable Archiving and click Next.
2. Select Specify Flash Recovery Area and enter /home/oracle/oradata in the Flash Recovery Area field. Then click Next.

Answer

To store your backup and recovery files in the default path using a limit of 2048 MB, you select Specify Flash Recovery Area and enter 2048 in the Flash Recovery Area Size field. Then to archive the database redo logs, you select Enable Archiving and click Next.

2. Content and intialization parameters

Database content consists of sample schemas and custom scripts. Content parameters are configured before the database is created.

The sample schemas are a set of database objects used for demonstrations and training.

If you want to add sample schemas to your database, you select the Sample Schemas checkbox.

The Custom Scripts tabbed page allows you to specify any SQL scripts you want to run after the database is created.

The database initialization parameters include memory, sizing, character set, and connection mode settings. These control actions occur when the database is created.

The Initialization Parameters page contains four tabbed pages which can be used to set the most common parameters.

• Memory
• Sizing
• Character Sets
• Connection Mode

Memory

The Memory tabbed page allows you to control how the database manages memory usage.

Sizing

On the Sizing tabbed page, you can set the block size and the maximum number of OS user processes that are allowed to connect to the database.

Character Sets

The Character Sets tabbed page enables you to set the default character set for the database and the national character set.

Connection Mode

On the Connection Mode tabbed page, you can select the default server mode for your database.

By clicking All Initialization Parameters, you can view and set all of the parameters in one window.

You can choose between two methods of managing memory usage:

• Typical
• Custom

Typical

If you select Typical, you can allow Oracle to manage a percentage of your overall system memory. You can alter the percentage allocated.

Custom

Selecting Custom grants you more control over how the database uses available system memory. You can alter the amount of memory allocated to the System Global Area (SGA) and the Program Global Area (PGA).

You want to allow Oracle 10g to manage memory usage.

You select Typical.

You have decided to allocate 30 percent of the total available system memory, so you enter 30 in the Percentage spin box.

Clicking Show Memory Distribution opens a window containing information on the amount of memory that will be assigned.

On the Sizing tabbed page, you use the Block Size spin box to set the size of the blocks in which the database is stored, and the Processes field to set the maximum number of processes that can connect to an instance.

You can choose the character sets used by the database on the Character Sets tabbed page.

A character set is an encoding scheme that displays characters on the computer screen.

The Connection Mode tabbed page offers two possible server modes:

• Dedicated Server Mode
• Shared Server Mode

Dedicated Server Mode

Using dedicated server mode, one server process is allocated to each user process. This option is useful if there are a small number of clients making long-running requests to the database.

Shared Server Mode

Shared server mode allocates one server process to multiple user processes. This option is used when a large number of users are connecting to the database simultaneously.

Your database will only be accessed by a small number of users.

You select Dedicated Server Mode and click Next.

The Database Storage page appears. This allows you to view and specify the storage configuration for data files, control files, and online redo log files.

Question

Match the database initialization parameters to their descriptions.

Options:

1. Dedicated Server Mode
2. Shared Server Mode
3. Default character set

Targets:

1. The parameter that allocates one server process to each user process.
2. The parameter that allocates one server process to multiple user processes.
3. The parameter that dictates the encoding scheme used to display characters on the computer screen.

Answer

You select Dedicated Server Mode to allocate one server process to each user process. You select Shared Server Mode to allocate one server process to multiple user processes. The default character set determines the encoding scheme used to display characters on the computer screen.

This parameter is used when there are a small number of clients and they make long-running requests to the database.

This parameter is used when a large number of users are connecting to the database simultaneously.

You can choose the character sets used by the database on the Character Sets tabbed page.

Question

Suppose your database is only accessed by a small number of users. You want Oracle to employ only 30 percent of your total system memory and use an appropriate server mode.

Which sequence of steps would you use to complete this task?

Options:

1. Select Typical and enter 30 in the Percentage spin box. Click the Connection Mode tab and select Dedicated Server Mode. Then click Next.
2. Select Custom and enter 30 in the SGA Size and PGA Size fields. Click the Connection Mode tab and select Dedicated Server Mode. Then click Next.

Answer

You select Typical and enter 30 in the Percentage spin box. Then you click the Connection Mode tab and select Dedicated Server Mode. Finally, you click Next.

Summary

The Database Configuration Assistant (DBCA) provides a simple way of configuring the file location, backup and recovery settings for your database. You can specify file locations yourself or allow Oracle to manage them. You can also select a flash recovery area or accept the area Oracle allocates by default. In addition, you can archive the database redo logs.

Database content consists of sample schemas and custom scripts. Initialization parameters include memory, sizing, character set, and connection mode settings. You can either instruct Oracle to manage these settings or configure them manually.

Using Oracle 10g's Database Configuration Assistant

Learning objective

After completing this topic, you should be able to recognize how to configure database identification, management, password, and storage options using the Database Configuration Assistant.

1. Using the Database Configuration Assistant (DBCA)

The Oracle Database Configuration Assistant (DBCA) is a graphical configuration tool that simplifies the creation of Oracle databases. It provides predefined database types and allows full custom database definition.

You start the DBCA from a terminal window. The command to launch the DBCA is dbca.

You type dbca in the terminal window.

The DBCA launches and displays a Welcome page. You click Next.

The Operations page allows you to specify which operation you wish to perform. You can create or delete a database. You can also change an existing database configuration or manage database templates.

You Select Create a Database, then click Next.

On the Database Templates page, you can create a custom database or select a predefined database template:

• Custom Database
• Data Warehouse
• General Purpose
• Transaction Processing

Custom Database

The Custom Database template allows you the freedom to specify your own settings and customize your database to the needs of your organization.

Data Warehouse

The Data Warehouse template enables you to bring together data from a number of transactional systems. Users can access and analyze the information it contains.

General Purpose

The General Purpose template creates a database that fulfills a broad range of functions.

Transaction Processing

The Transaction Processing template is suited to an environment in which the database system is required to respond immediately to user requests.

You decide to use a template designed to create a database that encompasses a range of functions.

You select General Purpose and click Next.

The Database Identification page enables you to identify your database. You enter the full name of the database - in this case orcl2 – in the Global Database Name field.

The Oracle System Identifier (SID) field contains the name of an instance that will connect to your database. When you type the Global Database Name, the SID field is filled automatically and you click Next.

Note

If you use Real Application Clusters, multiple instances open the same database, and each instance will have a different SID.

Question

Identify the operations you can perform using the DBCA.

Options:

1. Create a database
2. Delete a database
3. Manage templates
4. Install Oracle

Answer

You can create and delete databases using the DBCA. It also enables you to manage templates.

Option 1 is correct. This option creates a new database or template.

Option 2 is correct. This operation deletes all the database files.

Option 3 is correct. You can create a template from an existing template or database.

Option 4 is incorrect. The Oracle Universal Installer is provided to install Oracle.

Question

Suppose you want to create and configure a database to store customer data. You've accessed the Database Templates page of the DBCA and want to use the General Purpose template to create your database. You also want to name the database "orcl5".

Which sequence of steps would you use to perform this task?

Options:

1. You select General Purpose and click Next. Then you enter orcl5 in the Global Database Name field and click Next.
2. You select General Purpose and click Next.Then you enter orcl5 in the SID field and click Next.

Answer

To create a customer database using the General Purpose template, you select General Purpose and click Next. Then you name the database by entering orcl5 in the Global Database Name field. Finally, you click Next.

The Oracle Enterprise Manager enables you to manage the Oracle environment.

The methods for managing the database are

• Grid Control
• Database Control

Grid Control

You use Grid Control to manage your database centrally. To choose this option, Oracle Management Agent must be installed on your server. You click the down-pointing arrow to select the management service you wish to use.

Database Control

You select Database Control to manage your database locally. Enterprise Manager Database Control provides web-based features for monitoring and administering the single-instance or clustered database you are creating.

You need to manage your database locally, so you ensure that Configure the Database with Enterprise Manager and Use Database Control for Database Management are selected. You do not want to enable e-mail notifications or daily backup, so you click Next.

The Database Credentials page displays.

Question

Suppose you are creating a database with the DBCA and have decided to manage it using Enterprise Manager. You want to manage your database locally, but you do not require email notifications or a daily backup.

Which sequence of steps would you use to perform this task?

Options:

1. You select Configure the Database with Enterprise Manager and click Next.
2. You select Configure the Database with Enterprise Manager. Then you select Use Grid Control for Database Management and click Next.

Answer

To manage your database using Enterprise Manager, you select Configure the Database with Enterprise Manager. Then, to manage your database locally, you ensure Use Database Control for Database Management is selected and click Next.

2. Password and storage options

On the Database Credentials page, you select the passwords that allow users to access your database.

You can provide separate passwords for each of the administrative users – SYS, SYSTEM, DBSNMP, and SYSMAN. Alternatively, you can provide one password for all of these users.

You decide to use one password for all users, so you accept the default Use the Same Password for All Accounts and type a password in the Password and Confirm Password fields. Then you click Next.

There are three options for types of database storage mechanisms:

• File System
• Automatic Storage Management (ASM)
• Raw Devices

File System

This storage mechanism uses the file system of your operating system to manage files. You can choose to store your files in a specific directory.

Automatic Storage Management (ASM)

Automatic Storage Management simplifies database administration by allowing you to manage disk groups instead of a large number of database files. A disk group is a small set of disk devices that are managed as a single logical unit. You can also use mirroring or striping.

Raw Devices

A raw device is a disk partition that does not have a file system. Choosing this storage mechanism allows you to manage your storage devices outside of your operating system's file system.

Before selecting this option, you need to make sure that the raw devices are configured and identified and that your site has a raw partition for each Oracle data file.

Suppose you want to use your operating system's file system to manage files.

You select the File System storage mechanism and click Next.

The Database File Locations page allows you to choose a directory to store your database files.

Question

Match each file storage mechanism to the situation in which it would be best used.

Options:

1. Automatic Storage Management (ASM)
2. File System
3. Raw Devices

Targets:

1. You have a large number of database files and need to simplify database administration
2. You want to manage your storage devices outside of your operating system's file system
3. You want to use your operating system's file system to manage files

Answer

File System uses the file system of your operating system to manage files. You can manage storage devices outside of your current file system using Raw Devices. ASM uses disk groups to simplify database administration.

ASM allows you to manage disk groups instead of a large number of database files. It simplifies database administration.

The File System option uses the file system of your operating system to manage files. You can choose to store your files in a specific directory.

Raw Devices allows you to manage storage devices outside of your current file system. Your site should have a raw partition for each Oracle data file.

Question

You are using the DBCA to create a database and are currently on the Passwords page. You want to configure the database so that all users use the password pass to gain access. You also want to use your operating system's file system to manage files.

Which sequence of steps would you use to perform this task?

Options:

1. You select Use the Same Password for All Accounts. Next, you type pass in the Password Field. Then you type pass in the Confirm Password field and click Next. Finally, you select File System and click Next.
2. You select Use different Passwords. Next, you type pass in the Password field. Then you type pass in the Confirm Password field and click Next. Finally, you select Automatic Storage Management (ASM) and click Next.

Answer

To configure one password for all users, you select Use the Same Password for All Accounts. Next, you type pass in the Password field. Then you type pass in the Confirm Password field and click Next. Finally, to use your operating system's file system to manage files, you select File System and click Next.

Summary

The Oracle Database Configuration Assistant (DBCA) is a graphical tool that provides an easy way to create Oracle databases. It enables you to create or delete a database, change an existing database configuration, or create a template. You use Oracle Enterprise Manager to manage the database.

DBCA allows you to select the passwords that grant access to your database. You can specify either separate passwords for each user, or one password for all. There are three storage mechanisms you can use for the database – File System, Automatic Storage Management , and Raw Devices.

The architecture of an Oracle 10g database

Learning objective

After completing this topic, you should be able to distinguish between the basic components of Oracle database architecture.

Exercise overview

In this exercise, you're required to identify and distinguish between the files, memory structures, processes, and tools in Oracle Database 10g.

This involves the following tasks:

• identifying database core files
• distinguishing between storage units
• recognizing memory areas
• distinguishing between processes
• recognizing elements of the data dictionary

Task 1: Identifying database core files

You are a database administrator and your company is changing over to Oracle Database 10g. You want to refresh your knowledge of the architecture and framework of Oracle Database 10g.

First, you have to make sure you know what the constituent files of the database are.

Step 1 of 5

Which of these files make up the core files of an Oracle database?

Options:

1. Archive log files
2. Control files
3. Data files
4. Redo log files

Result

The Oracle database is made up of control files, data files, and redo log files.

Option 1 is incorrect. While the archive log files are part of the database, and contain an ongoing history of the redo logs, they are not database core files.

Option 2 is correct. Control files contain data about the status of the physical data files stored in the database.

Option 3 is correct. Data is stored in the database in data files. The database is made up of one or more tablespaces, each of which can contain one or more data files.

Option 4 is correct. Redo log files are used to help recover database instances if the original data is lost due to a system failure, such as a power outage or a computer fault.

Task 2: Distinguishing between storage units

Now, you want to refamiliarize yourself with how data is stored in Oracle Database 10g.

Step 2 of 5

Match the storage units to the appropriate descriptions.

Options:

1. Data blocks
2. Extents
3. Segments
4. Tablespaces

Targets:

1. Used to group related logical structures together
2. The smallest unit of data used by a database
3. Used to contain database objects, such as tables and indexes
4. Made up of contiguous data blocks

Result

Tablespaces are used to group related logical structures together. Data blocks are the smallest unit of data in a database. Segments are used to contain database objects, such as tables and indexes, and extents are made up of contiguous data blocks.

When a set of data blocks is requested by the database, the OS aligns them with real OS blocks on the storage device.

As extents are made up of contiguous data blocks, each extent can only exist in one data file.

A segment is made up of one or more extents.

Tablespaces contain one or more data files, which physically store the data of all the logical structures in the database.

Task 3: Recognizing memory areas

You need to understand how memory areas perform various functions in Oracle Database 10g.

Step 3 of 5

Match the memory areas with their characteristics.

Options:

1. Where the Oracle instance holds data in buffers and memory caches
2. Used to store data and control information for each server process
3. Contains a shared pool, a large pool, a Java pool, and a streams pool
4. Private to its server process, and is read and written by Oracle code acting on its behalf

Targets:

1. SGA
2. PGA

Result

The SGA is where the Oracle instance holds data in buffers and memory caches. It contains a shared pool, a large pool, a Java pool, and a streams pool. The PGA is used to store data and control information for each server process,is private to its server process, and is read and written by Oracle code acting on its behalf.

Task 4: Distinguishing between processes

It's also essential that you know the difference between a background process and a server process.

Step 4 of 5

Which of these are background processes?

Options:

1. Checkpoint
2. Database writer
3. Enterprise Manager
4. Process monitor

Result

Checkpoint, database writer, and process monitor are all background processes.

Option 1 is correct. The checkpoint tells the database writer about data changes. It also informs the database's data files and control files about the most recent checkpoint.

Option 2 is correct. The database writer is used to write the changed data from the database buffer cache to the long-term store on the hard disk.

Option 3 is incorrect. The Enterprise Manager is not a background process, it is an Oracle tool. When invoked, it creates a server request.

Option 4 is correct. When a user process fails, the process cleanup is performed by the process monitor.

Task 5: Recognizing elements of the data dictionary

Finally, you should understand how the data dictionary functions in Oracle Database 10g.

Step 5 of 5

Which of these are characteristic elements of the data dictionary?

Options:

1. It's created at the same time as the database
2. It's updated when the structure of the database is updated
3. It performs crash recovery
4. It is used by Enterprise Manager to show information about tables and views

Result

The data dictionary is created when the database is created, and it's updated whenever the database is modified. It's also used by the Enterprise Manager to show information about tables and views.

Option 1 is correct. The data dictionary is a read-only reference containing information about the sets of tables and views in a particular database.

Option 2 is correct. The data dictionary also contains the allocated space for a schema object and the amount currently in use.

Option 3 is incorrect. It is the system monitor, a background process in the PGA, that performs crash recovery.

Option 4 is correct. The DICTIONARY view in the Enterprise Manager provides useful descriptions of the data dictionary tables and views.

Managing the Oracle 10g instance

Learning objective

After completing this topic, you should be able to recognize the characteristics of the Oracle instance and its management interfaces.

1. Identifying basic memory structures

In each Oracle database server, there is an Oracle instance and an Oracle database.

The Oracle instance does not exist until it is started and the parameter file is read. The instructions about the configuration of the instance are set out in the parameter file.

When the instance is started, the database is opened and the user can access the data.

Two memory structures form an Oracle instance.

• System Global Area (SGA)
• Program Global Area (PGA)

System Global Area (SGA)

The System Global Area (SGA) is an area of memory shared by all server and background processes. This is where the Oracle instance holds data in buffers, memory caches and pools to be shared among the various processes.

If multiple users are connected to the same instance concurrently, the data in the instance's SGA is shared among the users.

This provides fast, repeat access to data without having to read the hard disk each time.

The SGA contains several data structures:

Before any data stored in the database can be queried or modified, it must be read from disk and stored in memory. The database buffer cache is used to store this data.

The redo log buffer provides a short-term store of data to be used in instance recovery. It maintains a record of data before it can be written to the physical redo log files stored on disk.

The shared pool stores constructs that can be accessed by multiple users.

The large pool is an optional area that you can create to provide a temporary store for large I/O processing requests.

The Java pool is used to store all Java code and data for a particular session of the Java Virtual Machine (JVM).

The streams pool is used by Oracle Streams to manage data, transactions, and events in a data stream.

The SGA has the ability to dynamically change the size of the shared pool, the Java pool, the database buffer cache, the streams pool, and the large pool - without needing to shut down the instance.

Although the memory parameters of the database are preconfigured, you may want to alter them as the needs of your database increase.

Oracle helps you determine the appropriate values for memory parameters by providing alerts and advisors that spot memory sizing problems when they occur.

The memory allocated for the SGA is displayed in Enterprise Manager or SQL*Plus when you start the instance.

Program Global Area (PGA)

The Program Global Area (PGA) is an area of memory, private to each server and background process, that contains data and control information for that one process. When a server process is started, its private PGA is created.

The PGA can only be accessed by its server process and is read and written by Oracle code acting on its behalf.

The PGA usually contains

The private SQL area stores the bind information and runtime memory structures. There is a private SQL area for every session that issues a SQL statement.

The session memory is memory made available for session variables and other session-related information.

How much PGA memory is used and what it contains depend on whether the instance is available in shared server mode.

Question

Match the memory structures associated with an Oracle instance to the appropriate characteristics.

Options:

1. It contains a private SQL area
2. It contains session information
3. It includes a database buffer cache and a redo log buffer.
4. It's where the Oracle instance holds data in buffers, memory caches, and pools.

Targets:

1. SGA
2. PGA

Answer

The SGA is where the Oracle instance holds data in buffers, memory caches, and pools and it includes a database buffer cache and a redo log buffer. The PGA contains a private SQL area and session information.

2. Recognizing Oracle processes

Every time you use an application or an Oracle tool, such as Enterprise Manager, a server process is initiated by the Oracle server to service the request.

The instance also starts a number of background processes to manage the memory structure, interact with the operating system, write data to disk, and complete general housekeeping tasks.

Which background processes are invoked depends on the features used in the database.

The background processes most often used include

• system monitor (SMON)
• process monitor (PMON)
  
• database writer (DBWn)
• checkpoint (CKPT)
• log writer (LGWR)
• archiver (ARCn)

system monitor (SMON)

When the instance is started after a system failure, the system monitor will perform the crash recovery.

process monitor (PMON)

When a user process fails, the process cleanup is performed by the process monitor.

database writer (DBWn)

The database writer is used to write the changed data from the database buffer cache to the long-term store on the hard disk.

checkpoint (CKPT)

The checkpoint tells the database writer about data changes. It also updates the database's data files and control files to indicate the most recent checkpoint.

log writer (LGWR)

The log writer writes redo log entries to the hard disk.

archiver (ARCn)

When the log files are full or a log switch occurs, the archiver moves the redo log files to archival storage.

Question

Match the background process to the appropriate description.

Options:

1. Checkpoint
2. Database writer
3. Process monitor
4. System monitor

Targets:

1. Performs crash recovery
2. Performs the process cleanup
3. Writes the modified data to the files on hard disk
4. Tells the database writer about data changes

Answer

The system monitor performs crash recovery and the process monitor performs the process cleanup when a user process fails. The database writer writes the changed data from the database buffer cache, and the checkpoint tells the database writer about data changes.

The checkpoint alerts the database writer about database changes. It also updates data files and control files to indicate the most recent checkpoint.

The database writer writes the changed data from the database buffer cache to the disk files.

The process monitor performs the process cleanup when a user process fails.

The system monitor performs crash recovery after a system failure and when the instance has started.

The data dictionary is a read-only reference containing information about the sets of tables and views in a particular database.

The information in a data dictionary includes

• the allocated space for a schema object and the amount currently in use
• the database's physical and logical structure
• the integrity constraints imposed
• a list of valid users

When you create a database, you also create a data dictionary. Similarly, when the structure of the database is modified and updated, the data dictionary is automatically updated.

Enterprise Manager uses the data dictionary to get information about objects in the database.

You can also get information from the data dictionary tables using the DICTIONARY view in Enterprise Manager. This view provides useful descriptions of the data dictionary tables and views.

The names of data dictionary's tables and views are generally appended to one of three prefixes:

• USER
• ALL
• DBA

USER

The USER prefix indicates that the data relates to those objects owned by the current user.

ALL

The ALL prefix indicates that the data relates to those objects that are accessible to the current user.

DBA

The DBA prefix indicates that the data relates to every object in the database.

Question

Which of these are characteristics of the data dictionary?

Options:

1. It is created when a database is created
2. It is updated when the database is modified
3. It is used by Enterprise Manager for getting data on objects
4. It performs process cleanup when a user process fails

Answer

A data dictionary is created when a database is created and is updated when the database is. Enterprise Manager uses the data dictionary to get data on objects.

Option 1 is correct. When you create a database, a data dictionary is also created. Similarly, when the database is updated, the data dictionary is too.

Option 2 is correct. When modifications are made to the database and it's updated, the data dictionary is also updated.

Option 3 is correct. The DICTIONARY view in Enterprise Manager also provides useful descriptions of the data dictionary tables and views.

Option 4 is incorrect. The process monitor performs process cleanup when a user process fails. This is a background process.

3. Understanding database control and grid control

Oracle Enterprise Manager's Database Control is a web-enabled control console that comes packaged with every Oracle Database 10g.

As a database administrator, you typically use Database Control to monitor and manage your database.

However, if your needs are greater, you can use Enterprise Manager's Grid Control to expand and integrate Database Control's capabilities with the rest of your system.

You use Database control to

• manage alerts
• administer database objects and users
• manage storage
• monitor performance
• control advisors and maintenance wizards
• perform database backup and recovery

The Grid Control framework is very flexible and has a high level of functionality.

This allows you to customize Enterprise Manager to suit the management and monitoring needs of your environment.

The typical Enterprise Manager configuration consists of several functional areas:

• database control
• managed targets
• application server control
• management services
• web-based grid control

Question

Match the controls to the appropriate characteristic.

Options:

1. It includes database control and application server control
2. Its framework is very flexible and has high functionality
3. It's used to monitor the performance of a single database
4. It's used to administer a single database's database objects and users

Targets:

1. Database Control
2. Grid Control

Answer

Database Control is used to monitor the performance of a single database and to administer its database objects and users. Grid Control includes database control and application server control and its framework is very flexible and has high functionality.

Summary

Each Oracle Database 10g consists of a database and an instance. The instance is idle until it is started, when it reads the parameter file to get its configuration. The basic memory structures associated with the instance are the System Global Area (SGA) and the Program Global Area (PGA).

Invoking a tool such as Enterprise Manager creates a server process to complete the request. It also initiates several background processes. The most common background processes are System Monitor (SMON), Process Monitor (PMON), Database Writer (DBWn), Checkpoint (CKPT), Log Writer (LGWR), and Archiver(ARCn). Enterprise Manager uses the Data Dictionary to view objects such as tables and views.

Every Oracle Database 10g comes packaged with Enterprise Manager Database Control. This is used by the administrator to manage and monitor the database. The capabilities of Database Control can be extended using Grid Control.