Oracle® Database 2 Day DBA 11g Release 1 (11.1) Part Number B28301-03 |
|
|
View PDF |
During Oracle Database installation, if you choose to create a starter database (see Step 4), Oracle Universal Installer automatically runs Database Configuration Assistant (DBCA) after software installation is complete. DBCA then guides you through the steps to create the database. If you do not create a starter database and later want to create one, or if you want to create additional databases, then you use DBCA.
Note:
With Oracle Database, you typically have a single database that hosts more than one application. You do not need multiple databases to run multiple applications. Instead, you separate the objects that support each different application into different schemas in the same database. However, there may be situations in which you want to create more than one Oracle database on the same host computer. When you do this with DBCA, the new databases typically use the same Oracle home directory as the first database, but store database data files separately from those of the first database.DBCA also enables you to modify a database configuration, delete a database, and more. This section describes the following DBCA tasks:
Online Help is available by clicking Help. It provides information that guides you in selecting configuration options.
Follow the steps in this section to start DBCA.
Note:
If you choose to create a starter database while installing the Oracle Database software, OUI automatically starts DBCA.To start DBCA:
Log on to your computer as a member of the administrative group that is authorized to install Oracle Database software and to create and run the database.
Do one of the following:
To start DBCA on a Microsoft Windows operating system, click Start, select Programs (or All Programs), then Oracle - HOME_NAME, then Configuration and Migration Tools, and then Database Configuration Assistant.
To start DBCA on UNIX or Linux, or at the command-line prompt on the Windows operating system, enter the following command:
dbca
The dbca
utility is typically located in the Oracle_home
/bin
directory.
Click Next to bypass with Welcome window.
DBCA enables you to create an Oracle database by following a step-by-step guided workflow.
To create a database using DBCA:
Start DBCA as described in "Starting DBCA".
In the Operations window, select Create a Database and click Next to start the guided workflow for creating a database. The workflow requests your input in the following windows:
The following sections provide details on each window. Most windows provide default settings.
This window enables you to select the type of database that you want to create. You can use DBCA to create a database from templates supplied by Oracle or from templates that you create. The templates contain settings optimized for a particular type of workload.
Oracle ships templates for the following two workload types:
General purpose or transaction processing
Data warehouse
Click Show Details to see the configuration for each type of database. Select 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 or Transaction Processing template.
For more complex environments, you can select the Custom Database option. This option does not use templates and results in a more extensive interview, which means that it will take longer to create your database.
For more information about using database templates, see "Managing Templates with DBCA".
In the Global Database Name field, enter the database name in the form database_name.domain_name.
In the SID field, enter the 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, and central management tools for managing your entire Oracle environment.
Select Configure Enterprise Manager to use Enterprise Manager. Then, select one of the following options:
If the Oracle Management Agent is installed on your host computer, then you can choose central management by selecting Register with Grid Control for centralized management and then selecting the Management Service.
To manage your database locally, select Configure Database Control for local management. You can then optionally configure email notification of database alerts or a daily backup of the database.
In this window, specify the passwords for the administrative accounts such as SYS
and SYSTEM
.
In this window, specify the type of storage mechanism you would like your database to use. For more information, see "Advanced Installation".
In this window, specify the Oracle software home and the directory path in which to create the database files. Select one of the following options:
Use Database File Locations from Template—This option instructs DBCA to use the directory information as specified in the template. Later, you can make modifications to database file names 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 file names and locations.
Use Oracle-Managed Files—This option instructs Oracle Database to directly manage operating system files comprising an Oracle database. You specify the default location, called a database area, for all your files. Oracle Database 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, see 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 file names, location, or their sizes.
When you create a new database, it is important to configure the database so you can recover your data in the event of a system failure. Redo logs contain a record of changes that were made to datafiles. Redo logs are stored in redo log groups, and you must have at least two redo log groups for your database. After the redo log files in a group have filled up, the log writer process (LGWR) switches the writing of redo records to a new redo log group. Oracle Database can automatically save the inactive group of redo log files to one or more offline destinations, known collectively as the archived redo log (also called the archive log). The process of turning redo log files into archived redo log files is called archiving.
Archiving can be performed only if the database is running in ARCHIVELOG
mode. This means a group of redo log files cannot be reused by the log writer (LGWR) process until the group is archived. If the database is running in NOARCHIVELOG
mode, then when a group becomes inactive after the LGWR process switches to a new group, the inactive group is available for immediate reuse by the LGWR process.
The NOARCHIVELOG
mode protects a database from instance failure but not from media failure. Only the most recent changes made to the database, which are stored in the online redo log groups, are available for instance recovery. To restore a database operating in NOARCHIVELOG
mode, you can use only entire database backups taken while the database is closed. Therefore, if you decide to operate a database in NOARCHIVELOG
mode, back up the entire database at regular, frequent intervals.
The archiving of redo log files has these advantages:
A database backup, together with online and archived redo log files, guarantees that you can recover all committed transactions in the event of an operating system or disk failure.
You can recover the database using a backup that was taken while the database was open and being used, as long as you have a copy of the archived log files that were written while the database was being backed up.
You can perform online tablespace backups, and use these backups to restore a tablespace following media failure.
You can keep a standby database current with its original database by continuously applying the original archived redo logs to the standby database.
Before you can archive redo logs, you must determine the destination to which you will archive. Oracle recommends that the archive log be stored in a flash recovery area because it can simplify backup and recovery operations for your database. A flash recovery area is a location in which Oracle Database can store and manage files related to backup and recovery. It is distinct from the database area, which is a location for the current database files (datafiles, control files, and online redo logs).
When creating your database, you can select the following options:
Specify Flash Recovery Area—Select this option to specify a backup and recovery area and its directory location and size. You can use variables to identify standard locations.
Enable Archiving—Select this option to enable the 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 Database Control or running the database in ARCHIVELOG
mode.
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 ARCHIVELOG mode later. See "Configuring Your Database for Basic Backup and Recovery".
When you create your database, you can load it with data using either of the following methods:
Sample Schemas—Select Sample Schemas to include the Sample Schemas (EXAMPLE
) tablespace in your database. Oracle guides and educational materials contain examples based upon the Sample Schemas. Oracle recommends that you include them in your database.
Custom Scripts—In the Custom Scripts subpage, optionally specify one or more SQL scripts to run after your database is created. Scripts are useful for performing postinstallation tasks, such as loading custom schemas. Note that if you choose to run scripts after installation, your scripts must include a connection string that identifies the database. Click Help for more information.
The links in this window provide access to additional windows that enable you to change the default initialization parameter settings. These parameters fall into the following categories:
You can also click the All Initialization Parameters button at the bottom of the window to display a list of all the database initialization parameters and their current settings.
Use this window to set the initialization parameters that control how the database manages its memory. You can choose from one of the following methods for memory management:
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 DBCA assigns to the System Global Area (SGA) and the aggregate Program Global Area (PGA). With this setting, the system automatically tunes many of the memory components of the SGA, and allocates memory to individual PGAs as needed. The system can also dynamically decrease or increase the total amount of memory allocated to the SGA or aggregate PGA, depending on processing demands. The total memory used for the database instance never exceeds the amount you specify. This automatic memory tuning for the instance is known as automatic memory management. To learn more about PGA and SGA, see "About Instance Memory Structure".
Custom—This method requires more configuration than the Typical option, 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 aggregate PGA and their substructures, such as the shared pool and buffer cache.
Select one of the following options for customized memory management:
Automatic Shared Memory Management to allocate specific amounts of memory to the SGA and aggregate PGA. With this setting, automatic shared memory management is enabled for the SGA, and memory is allocated to the individual PGAs as needed.
Manual Shared Memory Management to enter specific values for each SGA component and the aggregate PGA. This disables automatic shared memory management and enables you to determine how the SGA memory is distributed among the SGA memory components.
See Also:
Chapter 5, "Managing the Oracle Instance" for more information about memory management options
In this tab, you specify the smallest block size and the maximum number of operating system user processes that can simultaneously connect to the database.
Block Size—Use this list to select the block size, or accept the default. Oracle Database data is stored in data blocks of the size specified. One data block corresponds to a specific number of bytes of physical space on disk. Selecting a block size other than the default 8 kilobytes (KB) value requires advanced knowledge and should be done only when absolutely required.
While using predefined templates, this list is not enabled because the database will be created with the default block size of 8 KB. While using the custom option, you can change the block size.
Processes—In this field, specify the maximum number of processes that can simultaneously connect to the database. Enter a number or accept the default value of 150. The default value for this parameter is appropriate for many environments. The value you select should allow for all background processes, user processes, and parallel execution processes.
Use this tab to define the character sets used by your database. Character sets are the encoding schemes used to display characters on your computer screen. The character set determines what languages can be represented in the database.
Oracle recommends Unicode AL32UTF8 as the database character set. Unicode is the universal character set that supports most of the currently spoken languages of the world. The default character set suggested or used by Oracle Universal Installer and Database Configuration Assistant is based on the language configuration of the local operating system. If you choose to use a different character set, choose the character set most frequently used by the clients connecting to this database, for example WE8MSWIN1252. You might choose this character set if most clients connecting to the database run under the Microsoft Windows operating system.
Database Character Set—In this section, select from one of the following options:
Use the Default—Select this option to support only the language currently used by the operating system for all your database users and database applications.
Use Unicode (AL32UTF8)—Select this option to support multiple languages for your database users and database applications.
Choose from the list of character sets—Select this option if you want Oracle Database to use a character set other than the default character set used by the operating system.
National Character Set—In this 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 set can make programming for multiple languages easier.
Default Language—In this 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 reading direction (left to right or right to left).
Default Territory—In this list, select the name of the territory whose conventions are to be followed for day and week numbering or accept the default. The default territory also establishes the default date format, the default decimal character and group separator, and the default International Standardization Organization (ISO) and local currency symbols. For example, in the United Kingdom, the default date format is DD-MON-YYYY, where DD is the day of the month (1-31), MON is the abbreviated name of the month, and YYYY is the 4-digit year.
Use this window to select the database mode. You can run the database in either of the following modes:
Dedicated Server Mode—This mode allows a dedicated server process for each user process. Select this option when the number of total clients is expected to be small, for example, 50 or fewer. You might also choose this option when database clients will typically make persistent, long-running requests to the database. By default, the database is configured for dedicated server processes.
Shared Server Mode—This mode allows several client connections to share a database-allocated pool of resources. Use this mode in configurations in which client load is expected to cause a strain on memory and other system resources. If you choose shared server mode, then you must indicate the number of server processes you want to create when a database instance is started. For more information about setting this parameter, click Help.
In this window, choose to use either the enhanced default security settings or the settings of a previous release, optionally disabling certain security features. The enhanced security settings include case-sensitive passwords.
On this page you decide whether or not you want to enable the automatic maintenance tasks feature of Oracle Database 11g. Automated maintenance tasks are tasks that are started automatically at regular intervals to perform maintenance operations on the database. They are:
Automatic Optimizer Statistics Collection—Collects optimizer statistics for all schema objects in the database for which there are no statistics or only stale statistics.
Automatic Segment Advisor—Identifies segments that have space available for reclamation, and makes recommendations on how to defragment those segments.
Automatic SQL Tuning Advisor—Examines the performance of high-load SQL statements, and makes recommendations on how to tune those statements.
See Oracle Database Administrator's Guide for more information.
In this window, 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.
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.
In this window, select any of the following options for creating the database:
Create Database—Select this option to create your database at this time.
Save as a Database Template—Select this option to save the database definition as a template to use at a later time.
Generate Database Creation Scripts—Select this option to generate a SQL database creation script that you can run at a later time.
After you make your choice, click Finish. A confirmation window appears after a short period of time. Review the information, and click OK to proceed.
You can use DBCA to change the configuration of an existing database. For example, you can:
Change default security settings
Change server mode from dedicated to shared, or the reverse
Add database options that were not previously configured (for example, Oracle Label Security or Oracle OLAP)
To change the configuration of a database using DBCA:
Start DBCA as described in "Starting DBCA".
In the Operations window, select Configure Database Options and click Next.
Follow the instructions in the DBCA guided workflow.
You can also use DBCA to delete a database. When DBCA deletes a database, it shuts down the database instance and then deletes all database files. On the Windows platform, it also deletes associated Windows services.
To delete a database using DBCA:
Start DBCA as described in "Starting DBCA".
In the Operations window, select Delete a Database and click Next.
Select the database to delete and click Finish.
DBCA templates are XML files that contain information required to create a database. Templates are used in DBCA to create new databases and duplicate 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 duplicating a database. Duplication saves time because you copy the files of an existing database, referred to as a seed database, to the correct locations.
Templates are stored in the following directory:
Oracle_home/assistants/dbca/templates
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 computer to another.
Templates are divided into the following types:
Seed templates
Nonseed templates
The characteristics of each are shown in Table 2-1.
Table 2-1 DBCA Template Types
Type | File Extension | Includes Datafiles | Database Structure |
---|---|---|---|
Seed |
|
Yes |
This type of template contains both the structure and the physical datafiles of an existing database, referred to as the seed database. Your new 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 Oracle Enterprise Manager Database Control. The datafiles for the seed database are stored in compressed RMAN backup format in a file with a .dfb extension. The seed database control file is stored in a file with .ctl extension. (This file is needed only when storing datafiles in Automatic Storage Management (ASM) disk groups or as Oracle managed files.) The .dbc file contains the location of the seed database datafiles and contains the source database name used to mount the control file. |
Nonseed |
|
No |
This type of template is used to create a new database. It contains the characteristics of the database to be created. Nonseed 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 the templates shown in Table 2-2.
Table 2-2 Oracle-Provided DBCA Templates and Their Corresponding Workloads
Template | Workload |
---|---|
Data warehouse |
Users perform numerous, complex queries that process large volumes of data. Response time, accuracy, and availability are key issues. These queries ( |
General Purpose or Transaction processing |
Many concurrent users perform numerous transactions that require rapid access to data. Availability, speed, concurrency, and recoverability are key issues. Transactions consist of reading ( |
Custom database |
This template allows you maximum flexibility in defining a database because you can change any of the settings for the database being created. |
Follow the instructions in this section to create your own templates.
To create templates:
Start DBCA as described in "Starting DBCA".
In the Operations window, select Manage Templates and click Next.
In the Template Management window, select one of the following options, and click Next.
From an existing template
Using an existing template, you can create a new template based on the predefined template settings. You can add or change any template settings such as initialization parameters, storage parameters, or whether or not 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 schemas and their data will not be part of the created template. The source database can be either local or remote. Select 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 as well as 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 schemas and their data will be part of the created template. The source database must be local. Select this option when you want a template from which you can create an exact replica of the source database.
When creating templates from existing databases, you can translate file paths into Optimal Flexible Architecture (OFA) or maintain existing file paths. OFA is a set of file naming and placement guidelines for Oracle software and databases. Using OFA is recommended if the target computer on which you plan to create a database using the template has a different directory structure than computer on which the template was defined. Standard file paths can be used if the target computer has a directory structure that is similar to the directory structure on the source computer.
Follow the instructions in the DBCA guided workflow to create your template.
When you delete a template, it is no longer available to create a new database or a new template.
To delete a template:
Start DBCA as described in "Starting DBCA".
In the Operations window, select Manage Templates and click Next.
In the Template Management window, select Delete a database template and click Next.
Select the template to delete and click Finish.