|Oracle® Database 2 Day DBA
10g Release 2 (10.2)
Part Number B14196-02
|PDF · Mobi · ePub|
During installation, you can direct the installer to create and configure a new database. If you have already done so, you can skip to the next chapter.
However, if you install Oracle software only, or if you want to create additional databases with the release software that you just installed, then you can use the Database Configuration Assistant (DBCA).
DBCA enables you to perform the following tasks:
Online Help is available by clicking Help. It provides information that guides you in selecting configuration options.
To launch DBCA:
Log on to your computer as a member of the administrative group that is authorized to install Oracle software and create and run the database.
To launch DBCA on a Windows operating system, click Start and then select Programs, Oracle - home_name, Configuration and Migration Tools, and then Database Configuration Assistant.
To launch the DBCA on UNIX, or as another method on a Windows operating system, enter the following command at a system prompt:
dbca utility is typically located in
The Welcome window appears.
Click Next to continue. The DBCA Operations window appears.
On the DBCA Operations window, select Create a Database to start a wizard that enables you to create and configure a database. The wizard requests your input on the following:
Note that most windows of the wizard provide a default setting. To accept all the default parameters, you can click Finish at any step. DBCA displays a final confirmation window. Click OK to initiate the database creation.
This window enables you to select the type of database you want to create. By default, Oracle ships pre-defined templates. There are templates for Data Warehouse, General Purpose, and Transaction Processing databases.
The templates contain settings optimized for workload. Click Show Details to see the configuration for each type of database. Choose the template suited to the type of workload your database will support. If you are not sure which to choose, select the default General Purpose template.
For more complex environments, you can select the Custom Database option. This option results in a more extensive interview, which means that it will take longer to create your database because a database creation script must be run.
For more information about using database templates, see "Managing Templates with DBCA".
In the Global Database Name box, enter the database name in the form database_name.domain_name.
In the SID box, enter the Oracle system identifier. The SID defaults to the database name and uniquely identifies the instance that runs the database.
Use this window to set up your database so it can be managed with Oracle Enterprise Manager. Oracle Enterprise Manager provides Web-based management tools for individual databases, as well as central management tools for managing your entire Oracle environment.
Check Configure the Database with Enterprise Manager to use Enterprise Manager. Select one of the following options:
If the Oracle Management Agent has been installed on your host computer, then you have the option of selecting central management by selecting Use Grid Control for Database Management. If you select this type of management, you must also indicate which management service to use in the drop-down menu.
Select Use Database Control for Database Management to manage your database locally. If you choose this option, you can additionally check Enable Email Notifications for Oracle to e-mail you alerts regarding potential problems, and check Enable Daily Backup. Click Help for more information about these options.
In this window, you specify the passwords for the administrative accounts such as
SYSTEM. Select one of the following options:
Select Use the Same Password for All Accounts and enter the password to use the same password for all accounts.
Select Use Different Passwords and specify passwords individually.
Specify the type of storage mechanism you would like your database to use. For more information, refer to "Installation Choices" .
In this window, you specify the Oracle home and directory path in which to install the Oracle software. Choose one of the following:
Use Database File Locations from Template—Selecting this option instructs the DBCA to use the directory information as specified in the template. Later, you can make modifications to database filenames and locations.
Use Common Location for All Database Files—This option requires you to specify a new directory for the Oracle home. All the database files will be created in this location. Later, you can make modifications to database filenames and locations.
Use Oracle-Managed Files—Select this option to have Oracle directly manage operating system files comprising an Oracle database. You specify default location called a database area for all your files. Oracle thereafter automatically creates and deletes files in this location as required. You can also create multiple copies of your redo and online log files by selecting Multiplex Redo Logs and Control Files. To learn more about redo logs and control files, refer to Chapter 9, "Performing Backup and Recovery".
Selecting this option enables you to delegate the complete management of database files to the database. You no longer need to specify the filenames, location, or their sizes.
Specify Flash Recovery Area—Select this option to specify a backup and recovery area and specify its directory location and size. You can use variables to identify standard locations. To review or add additional locations, click File Location Variables at the bottom of the window.
Enable Archiving—Select this option to enable archiving of database redo logs, which can be used to recover a database. Selecting this option is the same as enabling Archive Log Mode in Oracle Enterprise Manager or running the database in
ARCHIVELOG mode. You can accept the default archive mode settings or change them by selecting Edit Archive Mode Parameters.
Oracle recommends you select Enable Archiving. Selecting this option provides better protection for your database in the case of software or hardware failure. If you do not select this option now, you can enable archive log mode later. See "Configuring Your Database for Basic Backup and Recovery".
When you create your database, you can load it with data.
Check Sample Schemas to include the Sample Schemas (
EXAMPLE) tablespace in your database. The Sample Schemas provide a common platform for examples. Oracle books and educational materials contain examples based upon the Sample Schemas. Oracle recommends they be included in your database.
In the Custom Scripts tab, you can specify one or more SQL scripts to run after your database is created. Scripts are useful for performing post-installation tasks, such as loading custom schemas. To specify scripts, check Run the following scripts; otherwise accept the default No scripts to run. Note that if you choose to run scripts after installation, your scripts must include a connect string that identifies the database. Click Help for more information.
The links on this window provide access to windows that enable you change default initialization parameter settings. These parameters fall into the following categories:
Typical—This method requires little configuration, and allocates memory as a percentage of total overall physical system memory. Select Typical and enter a percentage value. Click Show Memory Distribution to see how much memory the DBCA assigns to the System Global Area (SGA) and the Program Global Area (PGA). To learn more about PGA and SGA, refer to "Managing Memory Parameters" in Chapter 5, "Managing the Oracle Instance".
Custom—This method requires more configuration, but gives you more control over how the database uses system memory. This option is meant for more experienced database administrators. You can directly specify memory sizes for the SGA and PGA and their sub-structures, such as the shared pool and buffer cache.
Select one of the following options:
Select Automatic to allocate specific amounts of memory to SGA and PGA.
Select Manual to enter specific values for each SGA component. This will customize how the SGA memory is distributed among the SGA memory substructures.
In the Block Size list, enter the size in bytes or accept the default. Oracle Database data is stored in these blocks. One data block corresponds to a specific number of bytes of physical space on disk. While using pre-defined templates, this field is not enabled since the database will be created with the default block size of 8 KB. But while using the custom option, you can change block size. Selecting a block size other than the default 8 KB value requires advanced knowledge and should only be done when absolutely required.
In the Processes field, specify the maximum number of processes that can simultaneously connect to the database. Enter a number or accept the default of 150. The default value for this parameter is good enough for many environments.The value should be 6 or greater. This value should allow for all background processes, such as locks and parallel execution processes.
Use this window to define the character sets used by your database. Character sets are the encoding schemes used to display characters on your computer screen. Choosing a character set determines what languages can be represented in the database.
For Database Character Set, select from one of the following options:
Use the Default—Select this option if you need to support only the language currently used by the operating system for all your database users and your database applications.
Use Unicode (AL32UTF8)—Select this option if you need to support multiple languages for your database users and your database applications.
Choose from the list of character sets—Select this option if you want the Oracle Database to use a character set other than the default character set used by the operating system.
In the National Character Set list, select a character set or accept the default. The national character set is an alternative character set that enables you to store Unicode characters in a database that does not have a Unicode database character set. Choosing a national character can make programming in the national character set easier.
In the Default Language list, select a default database language or accept the default. The default language determines how the database supports locale-sensitive information such as day and month abbreviations, default sorting sequence for character data, and writing direction (left or right).
In the Default Date Format list, select a date format or accept the default. The default date format determines the convention for displaying the hour, day, month, and year. For example, in the United Kingdom, the date format is DD-MM-YYYY.
Dedicated Server Mode allows a dedicated server process for each user process. Select this option when the number of total clients is expected to be small, or when database clients will make persistent, long-running requests to the database.
Shared Server Mode allows several client connections to share a database-allocated pool of resources. Use this mode when a large number of users need to connect to the database. It is also useful when database memory is limited or when better performance is needed, because you can have more client connections to the database than in dedicated server mode. If you choose shared server mode, then you must also indicate the number of server processes you want to create when an instance is started. For more information about setting this parameter, click Help.
A navigation tree displays the storage structure of your database (control files, datafiles, redo log groups, and so forth). If you are not satisfied with the storage structure or parameters, then you can make changes. You can create a new object with Create and delete existing objects with Delete.
Note that if you selected one of the preconfigured templates for a database, then you cannot add or remove control files, datafiles, or undo segments.
Check any of the following options for creating the database:
Create Database—Check to create your database at this time.
Save as a Database Template—Check to save the database definition as a template to use at another time.
Generate Database Creation Scripts—Check to generate a SQL database creation script that you can run at a later time.
In the Operations window, select Configure Database Options to change various aspects of your database configuration. For example, you can change the database from a dedicated server to a shared server. You can add database options that have not been previously configured for use with your database, for example, Oracle Label Security or Oracle OLAP.
In the Operations window, select Delete a Database to remove a database from the system. When you select this option, DBCA deletes all the files associated with this database. On Windows, any associated services are also deleted.
DBCA templates are XML files that contain information required to create a database. Templates are used in DBCA to create new databases and clone existing databases. The information in templates includes database options, initialization parameters, and storage attributes (for datafiles, tablespaces, control files, and online redo logs).
Templates can be used just like scripts, but they are more powerful than scripts because you have the option of cloning a database. Cloning saves time by copying a seed database's files to the correct locations.
Templates are stored in the following directory:
Using templates has the following advantages:
Time saving. If you use a template you do not have to define the database.
Easy Duplication. By creating a template containing your database settings, you can easily create a duplicate database without specifying parameters twice.
Easy editing. You can quickly change database options from the template settings.
Easy sharing. Templates can be copied from one machine to another.
Templates are divided into the following types:
The characteristics of each are shown in Table 2-1.
|Type||File Extension||Includes Datafiles||Database Structure|
This type of template contains both the structure and the physical datafiles of an existing (seed) database. Your database starts as a copy of the seed database, and requires only the following changes:
Other changes can be made after database creation using custom scripts that can be invoked by DBCA, command-line SQL statements, or the Oracle Enterprise Manager.
The datafiles and online redo logs for the seed database are stored in a compressed format in a file with a
This type of template is used to create a new database from scratch. It contains the characteristics of the database to be created. Non-seed templates are more flexible than their seed counterparts because all datafiles and online redo logs are created to your specification, and names, sizes, and other attributes can be changed as required.
Oracle provides templates for the environments shown in Table 2-2.
|Environment||Description of Environment|
Users perform numerous, complex queries that process large volumes of data. Response time, accuracy, and availability are key issues.
These queries (
Many concurrent users perform numerous transactions that require rapid access to data. Availability, speed, concurrence, and recoverability are key issues.
Transactions consist of reading (
This template creates a database designed for general use. It combines features of both the DSS and OLTP database templates.
This template allows you maximum flexibility in defining a database.
The Template Management window provides you with the option of creating or deleting a template. The DBCA saves templates as XML files.
To create a database template, select one of the following options:
From an existing template
Using an existing template, you can create a new template based on the pre-defined template settings. You can add or change any template settings such as initialization parameters, storage parameters, or whether to use custom scripts.
From an existing database (structure only)
You can create a new template that contains structural information from an existing database, including database options, tablespaces, datafiles, and initialization parameters. User-defined schema and their data will not be part of the created template. The source database can be either local or remote. Choose this option when you want the new database to be structurally similar to the source database, but not contain the same data.
From an existing database (structure and data)
You can create a new template that has both the structural information and physical datafiles of an existing database. Databases created using such a template are identical to the source database. User-defined schema and their data will be part of the created template. The source database must be local. Choose the option when you want to create an exact replica of the source database.
When creating templates from existing databases, you can choose to translate file paths into Optimal Flexible Architecture (OFA) or maintain existing file paths. Using OFA is recommended if the machine on which you plan to create the database has a different directory structure. Standard file paths can be used if the target machine has a similar directory structure.
In the Operations window, select Configure Automatic Storage Management to configure Automatic Storage Management (ASM). For a brief overview of ASM, see "Advanced Installation". For more detailed information, see Appendix A, "Automatic Storage Management" and Oracle Database Administrator's Guide.