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.