Using DBCA to Create Oracle 11g Databases

481
When the creation process is complete, connect to the database with one of the tools
such as SQL*Plus or Enterprise Manager to ensure that all the database options and
components were installed properly. Logging into Enterprise Manager will give you an
overview of the new database. By using the URL specified in Figure 9.31, you can invoke
the Database Control home page. Log in using the
SYSMAN
account with the password you
supplied in Figure 9.7. Figure 9.33 shows the home screen of Enterprise Manager Database
Control.
FIGURE 9.33 Enterprise Manager Database Control home page
Configuring an Oracle Database Using the DBCA
The DBCA lets you change various aspects of an existing database. To change the database
configuration, select Configure Database Options on the DBCA Operations screen (shown
earlier in this chapter in Figure 9.2). If the database is not started, the DBCA starts it for
you automatically. You must connect to the database as a user who has DBA authority.
95127c09.indd 481 2/18/09 6:43:54 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
482

Chapter 9
N
Creating an Oracle 11g Database
Once you have selected and started the database, you can add options that may not
have been previously included in the database. Using DBCA you can perform the following
changes to database configuration:
Add database components (refer to Figure 9.13).
ÛN
Change database security settings from pre-11

Startup mount;
Next, issue the following command:
Drop database;
This command deletes all the files associated with the database. If you are using raw disk
devices, the special files created for these devices are not deleted. Also, you may have to
remove any archived logs from the database archive area using the appropriate operating-
system command.
95127c09.indd 482 2/18/09 6:43:54 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Using DBCA to Create Oracle 11g Databases

483
Managing Database Templates Using the DBCA
As I explained earlier in this chapter, the DBCA can store and use XML-based templates to
create your Oracle Database. As the DBA, you can manage these database-definition tem-
plates. Saving a definition of your database in a template format makes it easier to perform
various tasks. For example, you can copy a preexisting template to modify new database
definitions. The template definition is normally stored in the
$ORACLE_HOME/assistants/
dbca/templates
directory on Unix and in the
%ORACLE_HOME%\assistants\dbca\templates

directory on Windows systems.
The DBCA can use two types of templates: seed and nonseed. Seed templates are tem-
plate definitions that contain database-definition information and the actual data files and
redo log files. The advantage of a seed template is that the DBCA makes a copy of the data
files and redo logs included in the definition file. These prebuilt data files include all schema
information, which makes for a faster database-creation process. The seed templates carry
a

From an Existing Database
(Structure Only)
Creates a new template based on the structural characteris-
tics of an existing database. The data files are created from
scratch and will not include data from the original database.
Choose this option when you want a database that is struc-
turally like another database but does not contain any data.
The database you are copying from can reside anywhere in
your network.
From an Existing Database
(Structure As Well As Data)
Creates a new template based on the structural character-
istics of an existing database. The data files and all corre-
sponding user data are included in the new database. Choose
this option when you want an exact copy of an existing data-
base. The database you are copying must reside on the same
physical server as the new database you are creating.
Depending on the option selected, you are presented with a set of forms to save your
template definition. If you elect to create a template from an existing database, you will have
to connect to the database so that the DBCA can obtain information about the database.
You must connect to the database as a user who has DBA credentials to perform this task.
If you are copying a definition from an existing template, you can configure the template
by following a series of screens that are similar to those used to create a database. These
screens allow you to configure the various aspects of the template, including initialization
parameters and data file and redo log locations.
Deleting Template Definitions Using the DBCA
You can also delete an existing template definition. On the Operations screen (see Figure 9.2,
shown earlier in this chapter), click Manage Templates. You will be presented with the
Template Management screen (see Figure 9.34). Select the option Delete a Database Tem-
plate. You can then choose the template to delete. When you remove the template, the

status of the database components. The dictionary is also immediately updated when a
DDL statement is executed.
Data dictionary views and dynamic performance views are described in the next section.
Data Dictionary Views
Data dictionary views provide information about the database and its objects. Depending
on which features are installed and configured, an Oracle 11g database can contain more
than 1,600 data dictionary views. Data dictionary views have names that begin with
DBA_
,
ALL_
, and
USER_
. Oracle creates public synonyms on many data dictionary views so users
can access the views conveniently.
The difference between the
DBA_
,
ALL_
, and
USER_
views can be illustrated using the
DBA_TABLES
data dictionary view as an example. The
DBA_TABLES
view shows information
on all the tables in the database. The corresponding
ALL_TABLES
view, despite its name,
shows only the tables that a particular database user owns or can access. For example,
if you were logged into the database as a user named

ary views.
95127c09.indd 485 2/18/09 6:43:55 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
486

Chapter 9
N
Creating an Oracle 11g Database
FIGURE 9.35 A comparison of data dictionary views
USER_TABLES
All tables owned by a
particular database user.
DBA_TABLES
All tables in the entire database.
Reserved for user accounts that
have DBA privileges.
ALL_TABLES
All tables owned by a
particular database user plus
all tables to which the user
has been granted access.
TABLE 9.5 Examples of Data Dictionary Views
Dictionary View Description
DBA_TABLES
Shows the names and physical storage information about all the
tables in the database
DBA_USERS
Shows information about all the users in the database
DBA_VIEWS
Shows information about all the views in the database

V_$
. Public synonyms are created on these views, and they begin with
V$
.
For example, the dynamic performance view with data file information is
v_$datafile
,
whereas the public synonym is
v$datafile
.
Depending on which features are installed and configured, an Oracle 11g database can
contain approximately 480 dynamic performance views. Most of these views have names
that begin with
V$
. Table 9.6 describes a few of these dynamic performance views.
TABLE 9.6 Examples of Dynamic Performance Views
Dynamic
Performance View
Description
V$DATABASE
Contains information about the database, such as the database name
and when the database was created
V$VERSION
Shows which software version the database is using
V$OPTION
Displays which optional components are installed in the database
V$SQL
Displays information about the SQL statements that database users
have been issuing
You can find a complete list of the Oracle 11g data dictionary views in Part

).
The
DBA_
views are available only when the
database is open and running.
Some
V$
views are available even when the
database is not fully open and running.
The data contained in the
DBA_
views is
static and is not cleared when the database
is shut down.
The
V$
views contain dynamic statistical
data that is lost each time the database is
shut down.
The data dictionary view
DICTIONARY
shows information about the data
dictionary and dynamic performance views in the database.
DICT
is a syn-
onym for the
DICTIONARY
view. The
COMMENTS
column shows the purpose

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Managing Initialization-Parameter Files

489
The parameter file can be a plain text file, commonly referred to as a pfile, or it can be
a binary parameter file, commonly referred to as an spfile. You can use either type of file
to configure instance and database options; however, there are some important differences
between the two types of configuration files, as shown in Table 9.8.
TABLE 9.8 Pfiles vs. Spfiles
Pfile Spfile
Text file that can be edited using a text editor. Binary file that cannot be edited directly.
When changes are made to the pfile, the
instance must be shut down and restarted
before it takes effect.
Parameter changes made to the database
using
ALTER​SYSTEM
are updated in the spfile.
Is called
initinstance_ name.ora
. Is called
spfileinstance_ name.ora
.
Oracle instance reads only from pfile. Oracle instance reads and writes to the spfile.
Can be created from an spfile using the
create​pfile​from​spfile
command.
Can be created from a pfile using the
create​spfile​from​pfile
command.

DB_CREATE_FILE_DEST
No Specifies the directory location where database data files
will be created if the Oracle-Managed Files feature is used.
DB_CREATE_ONLINE_LOG_
DEST_n
No Specifies the location(s) where the database redo log files
will be created if the Oracle-Managed Files feature is used.
DB_DOMAIN
Yes Specifies the logical location of the database on the net-
work.
DB_NAME
Yes Specifies the name of the database that is mounted by
the instance.
DB_RECOVERY_FILE_DEST
No Specifies the location where recovery files will be writ-
ten if the flash recovery feature is used.
DB_RECOVERY_FILE_
DEST_SIZE
No Specifies the amount of disk space available for storing
flash recovery files.
DB_UNIQUE_NAME
Yes Specifies a globally unique name for the database within
the enterprise.
INSTANCE_NUMBER
Yes Identifies the instance in a Real Application Clusters
(RAC) environment.
LDAP_DIRECTORY_​
SYSAUTH
Yes Enables or disables Oracle Internet directory–based
authentication for

Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Managing Initialization-Parameter Files

491
Parameter Name Static Description
REMOTE_LOGIN_​
PASSWORDFILE
Yes Determines whether the instance uses a password file
and what type.
ROLLBACK_SEGMENTS
Yes Specifies the rollback-segment names, only if Automatic
Undo Management is not being used.
SESSIONS
Yes Determines the maximum number of sessions that can
connect to the database.
SGA_TARGET
No Establishes the maximum size of the SGA, within which
space is automatically allocated to each SGA component
when Automatic Memory Management is used.
SHARED_SERVERS
No Specifies the number of shared server processes to start
when the instance is started. See Chapter 11 for details.
STAR_TRANSFORMATION_
ENABLED
No Determines whether the optimizer will consider star trans-
formations when queries are executed. See Chapter 14 for
details on the optimizer.
UNDO_MANAGEMENT
Yes Establishes whether system undo is automatically or
manually managed. See Chapter 8, “Introducing Oracle

dispatchers=’(PROTOCOL=TCP)​(SERVICE=OCA11GXDB)’
memory_target=1G
open_cursors=300
processes=150
remote_login_passwordfile=’EXCLUSIVE’
undo_tablespace=’UNDOTBS1’
In this sample pfile, the sizes of the shared pool, database buffer cache, large pool, and
Java pool are not individually specified. Instead, Oracle 11g’s Automatic Memory Manage-
ment features allow you to simply set one configuration parameter—
MEMORY_TARGET
—to
establish the total amount of memory allocated to the SGA and PGA. I will discuss this
parameter in Chapter 14.
On production databases, if your Oracle license is based on the number of named users,
you can enforce the license compliance by setting the
LICENSE_MAX_USERS
parameter. The
default for this parameter is 0, which means you can create any number of users in the
database and the license compliance is not enforced.
Handle with Care: Undocumented Configuration Parameters
You’ve just read a performance-tuning tip posted to the Oracle newsgroup at
comp​
.databases.oracle.server
. The person posting the tip suggests setting the undocu-
mented pfile parameter
_dyn_sel_est_num_blocks
to a value of 200 in order to boost
your database’s performance. Should you implement this suggestion?
More than 1,000 undocumented configuration parameters are available in Oracle 11g.
Undocumented configuration parameters are distinguished from their documented counter-

either a pfile or an spfile. Oracle looks for files with the following names in the default
directory to start the instance:
spfile$ORACLE_SID.ora
ÛN
spfile.ora
ÛN
init$ORACLE_SID.ora
ÛN
Oracle first looks for a parameter file called
spfile$ORACLE_SID.ora
. If it doesn’t find
that, it searches for
spfile.ora
. Finally, it searches for a traditional text pfile with the
default name of
init$ORACLE_SID.ora
.
If the parameter files do not exist in the default location or you want to use a different
parameter file to start your database, you can specify a parameter file to use when you issue
a
startup
command to start the Oracle Database.
You will see examples of how database startup is performed later in
this chapter in the section “Starting Up and Shutting Down an Oracle
Instance.”
Modifying Initialization-Parameter Values
In some instances, you may need to change the initialization parameters. For example, you
might need to increase the number of sessions allowed to connect to the database because
you are adding users. Whatever the case, you need to know how to make these changes.
There are a few options to change the initialization-parameter value, based on the type of

modification immediately affects the parameter that affects the currently running instance
without stopping the database. The changes you make on the Current tab are not perma-
nent, so the next time the database is stopped and restarted, the settings revert to their
original values.
SPFile tab If you are using a server parameter file, you will see the SPFile tab. This tab
also lets you change existing database parameters. The difference between changing param-
eters on this tab and changing parameters on the Current tab is that changes to the spfile
are persistent across database startups and shutdowns because the changes are saved to the
spfile definition. You can also apply your changes to the spfile only or to the spfile and the
currently running instance.
FIGURE 9.36 The EM Database Control Initialization Parameters screen
95127c09.indd 494 2/18/09 6:43:56 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Managing Initialization-Parameter Files

495
Using SQL*Plus
Though EM Database Control is a handy tool to modify the initialization parameters,
sometimes it is convenient to use SQL*Plus and make changes to the parameters. You
should know about two dynamic performance views:
V$PARAMETER
and
V$SPPARAMETER
.
V$PARAMETER
The
V$PARAMETER
view shows information about the initialization parameters that are cur-
rently in effect. This view has several useful columns. Table 9.10 lists some of the columns
in

statement. Such parameters can be changed only
using the
SCOPE=SPFILE
clause.
V$SPPARAMETER
The
V$SPPARAMETER
view shows the contents of the spfile used to start the instance. A
TRUE

value for the
ISSPECIFIED
column shows whether the parameter was specified in the spfile.
If a pfile was used to start the instance, all the rows will have
FALSE
for the
ISSPECIFIED

column. Sometimes, querying the
V$SPPARAMETER
can produce readable output for param-
eters that take multiple values.
95127c09.indd 495 2/18/09 6:43:56 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
496

Chapter 9
N
Creating an Oracle 11g Database
V$PARAMETER vs. V$SPPARAMETER

Session​altered.
SQL>
You can use the
ALTER​SYSTEM
statement to change the value of a parameter system-wide
or in the spfile, or both. You use the
SCOPE
clause to define where you want to change the
parameter value:
MEMORY
,
SPFILE
, and
BOTH
are the valid values for the
SCOPE
clause.
95127c09.indd 496 2/18/09 6:43:57 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Managing Initialization-Parameter Files

497
A value of
DEFERRED
or
IMMEDIATE
in the
ISSYS_MODIFIABLE
column shows that the
parameter can be dynamically changed using

SQL>​ALTER​SYSTEM​SET​UNDO_MANAGEMENT​=​MANUAL​SCOPE=SPFILE;
System​altered.
SQL>
Most of the times when you make a parameter change, you want it to take effect imme-
diately in memory as well as persist the change across database shutdowns. You can use the
SCOPE=BOTH
clause, which is the default, for this purpose. So if you omit the
SCOPE
clause,
Oracle will make changes to the memory and to the spfile. If a pfile is used to start the
instance, the change will be in memory only for the current running instance.
SQL>​ALTER​SYSTEM​SET​SGA_TARGET=500M​SCOPE=BOTH;
System​altered.
SQL>
95127c09.indd 497 2/18/09 6:43:57 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
498

Chapter 9
N
Creating an Oracle 11g Database
You can use the SQL*Plus command
SHOW​PARAMETER
to view the current value of an
initialization parameter. You can specify the full parameter name or part of the name. For
example, to view all parameters related to undo, you can do this:
SQL>​SHOW​PARAMETER​undo
NAME​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​​TYPE​​​​​​​​VALUE
------------------------------------​-----------​-----------
undo_management​​​​​​​​​​​​​​​​​​​​​​string​​​​​​AUTO

with the
SYSDBA
authorization. You can grant this authorization and the
SYSOPER
authoriza-
tion to give others the ability to perform these tasks without connecting as the
SYS
user.
Now I will discuss how to perform a database startup.
Starting Up an Oracle 11g Database
As described in Chapter 8, the Oracle instance is composed of a set of logical memory struc-
tures and background processes that users interact with to communicate with the Oracle
Database. When Oracle is started, these memory structures and background processes are
initialized and started so that users can communicate with the Oracle Database.
95127c09.indd 498 2/18/09 6:43:57 AM
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Starting Up and Shutting Down an Oracle Instance

499
Whenever an Oracle Database is started, it goes through a series of steps to ensure
database consistency. When it starts up, a database passes through three modes:
NOMOUNT
,
MOUNT
, and
OPEN
. I will review each of these startup modes and other special startup options
such as
FORCE
and

STARTUP​NOMOUNT
option but also attaches
and interacts with the database structures. At this point, Oracle obtains information from
the control files that it uses to locate and attach to the main database structures. The con-
trol file contains the name of the database, all the data file names, and the redo log files
associated with the database.
Certain administrative tasks can be performed while the database is in this mode, including
renaming data files, enabling or disabling archive logging, renaming and adding redo log
files, and recovering the database.
STARTUP OPEN    
This is the default startup mode if no mode is specified on the
STARTUP
com-
mand line.
STARTUP​OPEN
performs all the steps of the
STARTUP​NOMOUNT
and
STARTUP​MOUNT

options. This option makes the database available to all users.
When opening the database, you can use a couple of options.
STARTUP​OPEN​READ​ONLY
opens
the database in read-only mode.
STARTUP​OPEN​RECOVER
opens the database and performs a
database recovery.
Although you typically use the
STARTUP​NOMOUNT

STARTUP​FORCE
does
a shutdown abort and then restarts the database.
STARTUP RESTRICT    
The
STARTUP​RESTRICT
option starts up the database and places it in
OPEN
mode but gives access only to users who have the
RESTRICTED​SESSION
privilege. You
might want to open a database using the
RESTRICTED
option when you want to perform
maintenance on the database while it is open but ensure that users cannot connect and per-
form work on the database. You might also want to open the database using the
RESTRICTED

option to perform database exports or imports and guarantee that no users are accessing
the system during these activities. After you are done with your work, you can disable the
restricted session,
ALTER​SYSTEM​DISABLE​RESTRICTED​SESSION
, so everyone can connect to
the database.
Starting Up Oracle Using EM Database Control
Now that you understand the various startup options, let’s look at how to use the EM
Database Control to start up the Oracle instance.
When you invoke the Enterprise Manager console, you are notified that the database
instance is down (see Figure 9.37).
FIGURE 9.37 The EM Database Control database status screen