OCA/OCP Oracle Database 11g All-in-One Exam Guide
96
18. What tools can be used to manage templates? (Choose one or more correct
answers.)
A. The Database Configuration Assistant
B. The Database Upgrade Assistant
C. SQL*Plus
D. Database Control
E. The Oracle Universal Installer
19. At what point can you choose or change the database character set? (Choose
two correct answers.)
A. At database creation time, if you are not using any template
B. At database creation time, if you are using a template that does not include
datafiles
C. At database creation time, whether or not you are using a template
D. After database creation, with the DBCA
E. After database creation, with SQL*Plus
20. If there are several databases created off the same Oracle Home, how will
Database Control be configured? (Choose the best answer.)
A. Database Control will give access to all the databases created from the one
Oracle Home through one URL.
B. Database Control will give access to each database through different ports.
C. Database Control need only be configured in one database and can then
be used to connect to all of them.
D. Database Control can only manage one database per Oracle Home.
Self Test Answers
1. þ C. SQL Developer is not installed with the OUI; it is delivered as a ZIP file
that just needs to be unzipped.
ý A, B, and D. All other products (even the OUI) are installed with the OUI.
2. þ A, C, and D. DBCA is meant for creating databases, but they can also be
created from SQL*Plus or by instructing the OUI to create a database after

installing the Oracle Home.
ý B and E. B is wrong because DBUA can only upgrade an existing database. E
is wrong because Database Control is available only after the database is created.
3. þ B. Oracle Secure Backup is the enterprise backup facility.
ý A and C. These are both wrong because they are limited to backing up
database files only.
Chapter 2: Installing and Creating a Database
97
PART I
4. þ B. The Oracle Base directory contains all the Oracle Homes, which can be
any versions of any products.
ý A, C, and D. A is wrong because it inverts the relationship. C is wrong
because there is no requirement for a separate base for each product. D is
wrong because it confuses the oraInst.loc file and the OUI with the OFA.
5. þ A. The rather grandly named Optimal Flexible Architecture is nothing
more than a naming convention for directory structures.
ý B, C, and D. These are wrong because they go way beyond OFA.
6. þ D. Without a DISPLAY set, the OUI will not be able to open any windows.
ý A, B, and C. These are wrong because while they can be set before
launching the OUI, the OUI will prompt for values for them.
7. þ C. Perhaps not advisable, but you can certainly do this.
ý A, B, and D. A is wrong because while it might be a good idea, it is not
something you have to do. B is wrong because the interactive installation will
halt. D is wrong because all prerequisites are checked at the same time.
8. þ A and B. The Oracle Home must exist on a file system, but it can be local
or clustered.
ý C and D. Raw devices and ASM devices can be used for databases, but not
for an Oracle Home.
9. þ D. The -ignoresysprereqs switch stops OUI from running the tests.
ý A, B, and C. A is wrong because this will suppress generation of windows,

not running tests. B is wrong because this is the switch to generate a response
file. C is wrong because this is the switch to read a response file.
10. þ D. If the OUI cannot find an inventory, it will create one.
ý A, B, and C. A and B are wrong because one inventory stores details of
all Oracle Base and Oracle Home directories. C is wrong because it is not
possible to create an inventory before running the OUI.
11. þ B. The CREATE DATABASE command can only be issued in NOMOUNT
mode.
ý A, C, and D. A is wrong, because if the instance is not started, the only
possible command is STARTUP. C and D are wrong because it is impossible to
mount a database if there is no controlfile, and it cannot be opened if there is
no redo log and SYSTEM tablespace.
12. þ C. There are defaults for everything, including the SYSAUX tablespace and
datafile definitions.
ý A, B, and D. A is wrong because the command will succeed. B and D are
wrong because these are not the way the defaults work.
OCA/OCP Oracle Database 11g All-in-One Exam Guide
98
13. þ D. The only time a listener is required is if the DBCA is used, and Database
Control is selected. The DBCA will not continue if it cannot detect a listener.
ý A, B, and C. A is wrong because there is a circumstance where a listener
is required; B is wrong because in all other circumstances a listener is not
required. C is wrong because it does not go far enough: The DBCA will not
require a listener, if Database Control is not selected.
14. þ A. This is the correct sequence (though 2 and 3 could be done the other
way round).
ý B, C, and D. None of these are possible.
15. þ D. This is the one parameter that can never be changed after creation.
ý A, B, and C. A and B are wrong because DB_BLOCK_SIZE cannot be
changed no matter when you try to do it. C is wrong because the CONTROL_

FILES parameter can certainly be changed, though this will require a shutdown
and restart.
16. þ A, C, F, and G. All of these will always be created, by default if they are
not specified.
ý B, D, and E. B and D are wrong because these should exist before the
instance is started. E is wrong because the conversion of the static parameter file
to a dynamic parameter file only occurs, optionally, after the database is created.
17. þ D. The database will function, but without the data dictionary views and
PL/SQL packages created by these scripts it will be unusable.
ý A, B, C, and E. A is wrong because the database will open; in fact, it must be
open to run the scripts. B is wrong because tables and other objects can certainly
be created. C is wrong because PL/SQL will be available; it is the supplied
packages that will be missing. E is completely irrelevant to these scripts.
18. þ A. The DBCA is the only tool that can manage templates.
ý B, C, D, and E. These are all wrong because only the DBCA offers
template management.
19. þ C and E. C is right because the character set can be set at creation time,
no matter how the creation is done. E is right because it is possible to change
character sets after creation (though you don’t want to do this unless it is
really necessary).
ý A, B, and D. A and B are wrong because templates are not relevant. If the
template includes datafiles, the DBCA will change the character set behind the
scenes. D is wrong because the DBCA does not offer an option to do this.
20. þ B. Database Control can be used for each database and will be configured
with a different port for each one.
ý A, C, and D. A is wrong because this is what Grid Control can do. C is
wrong because Database Control must be installed in every database that will
use it. D is wrong because while a Database Control is only for one database,
every database can have its own.
CHAPTER 3

Instance Management
Exam Objectives
In this chapter you will learn to
• 052.4.1 Set Database Initialization Parameters
• 052.4.2 Describe the Stages of Database Startup and Shutdown
• 052.4.3 Use Alert Log and Trace Files
• 052.4.4 Use Data Dictionary and Dynamic Performance Views
99
OCA/OCP Oracle Database 11g All-in-One Exam Guide
100
You should now have a database installed on your learning environment and be ready
to investigate and demystify your Oracle instance. There are many benefits to learning
in a playpen environment, the most important of which is that as you experiment and
explore you will inevitably make a mistake, and the authors find that resolving such
mistakes provides the best opportunity for learning. You could always deinstall and
reinstall the software if you believe you have damaged it irreparably, but even such a
nonheroic solution still provides valuable OUI experience.
The database and instance are governed by a set of initialization parameters. There
are a vast number of them, of which only about 33 are really important to know. These
parameters determine settings like the amount of memory your instance will request
the operating system to allocate at instance startup time, the location of the controlfiles
and redo logfiles, and the database name. The default parameter values won’t suit most
production environments, but they are general enough to acceptably run your learning
environment. Many DBAs are slightly afraid of modifying these parameters, but there
is nothing scary here, just a bunch of settings that once configured hardly ever change.
If you change them during the course of a performance tuning exercise, or while trying
to multiplex your controlfiles, and the database behaves worse, it is a simple matter to
revert your changes. These initialization settings are stored in a parameter file without
which your instance will not start.
The stages of database startup and shutdown will be examined, and although

they are quite simple, these fundamental stages have important implications for
understanding how the mechanism for instance crash recovery operates and how
some of the instance background processes interact with the database.
The value provided by alert log and trace files cannot be overemphasized when
problems arise, and Oracle has contrived a convenient set of initialization parameters
used to quickly locate the relevant files. This is especially useful when high-powered
company executives are intently watching you resolve problems after your company’s
production database has just decided to go for a loop. The alert log file is probably
the most important file to a DBA, as it contains a living record of the critical events
that occur on your instance, recording events like startups, shutdowns, and serious
error conditions. The trace files are usually generated by background and server
processes and, just like the alert log file, provide a mixture of informational and
error messaging. Familiarity with these files is vital and will be discussed.
The chapter closes with a discussion of the database dictionary and the dynamic
performance views. These objects are interrogated by SQL queries and provide vital
information on the current state of your system. One of the authors once had a manager
who insisted that all DBA support staff memorize the data dictionary objects. And they
did. Thankfully, the manager left when Oracle 7 was the current version. The Oracle 11g
dictionary is significantly larger and can be intimidating, but fortunately, you do not
have to memorize the plethora of information available. Knowing the nature of the
information available is, however, important and very useful. The data available in the
dynamic performance views will not persist across instance shutdown and startup
cycles. These views report on the current database activity and help both the instance
and the DBA keep abreast of the happenings in the system. Using and befriending these
objects will greatly simplify your task of understanding what the database is really about.
Chapter 3: Instance Management
101
PART I
Set Database Initialization Parameters
An instance is defined by the parameters used to build it in memory. Many, though

not all parameters, can be changed after startup. Some are fixed at startup time and
can only be changed by shutting down the instance and starting again.
The parameters used to build the instance initially come either from the parameter
file (which may be a static pfile or a dynamic spfile) or from defaults. Every parameter
has a default value, except for the DB_NAME parameter; this must always be specified.
In total there are close to three hundred parameters (the exact number will vary
between releases and platforms) that it is acceptable for the DBA to set. There are in
fact about another fifteen hundred parameters, known as “hidden” parameters, that
the DBA is not supposed to set; these are not usually visible and should only be set
on the advice of Oracle Support.
The (approximately) three hundred parameters are divided into “basic” and
“advanced.” The idea is that most database instances will run well with default values
for the advanced parameters. Only about thirty-three (the exact number may vary
between versions) are “basic.” So setting parameters is not an enormous task. But it
is enormously important.
Static and Dynamic Parameters and
the Initialization Parameter File
To view the parameters and their current values, you may query the V$PARAMETER view:
select name,value from v$parameter order by name;
A query that may give slightly different results is
select name,value from v$spparameter order by name;
The difference is the view from which the parameter names and values are taken.
V$PARAMETER shows the parameter values currently in effect in the running instance.
V$SPPARAMETER shows the values stored in the spfile on disk. Usually, these will be
the same. But not always. Some parameters can be changed while the instance is
running; others, known as static parameters, are fixed at instance startup time. A change
made to the changeable parameters will have an immediate effect on your running
instance and can optionally be written out to the spfile. If this is done, then the
change will be permanent: the next time the instance is stopped and started, the new
value will be read from the spfile. If the change is not saved to the spfile, then the

change will only persist until the instance is stopped. To change a static parameter,
the change must be written to the spfile, but it will only come into effect at the next
startup. If the output of the two preceding queries differs, this will typically be
because the DBA has done some tuning work but not yet made it permanent, or has
found it necessary to adjust a static parameter and hasn’t yet restarted the instance.
The other columns in V$PARAMETER and V$SPPARAMETER are self-explanatory.
They show information such as whether the parameter can be changed (for a session
or for the whole instance), whether it has been changed, and whether it has been
specified at all or is on default.
OCA/OCP Oracle Database 11g All-in-One Exam Guide
102
The views can also be seen through Database Control. From the database home
page, take the Server tab and the Initialization Parameters link. On the window
following, shown in Figure 3-1, there are two subtabs: Current shows the values
currently in effect in the running instance and may be obtained by querying the
V$PARAMETER view, while the SPFile tab shows those values recorded in the spfile
and may be obtained by querying the V$SPPARAMETER view.
The changeable parameters can be adjusted through the same window. The values
for the first four parameters shown (CLUSTER_DATABASE, COMPATIBLE, CONTROL_
FILES, and DB_BLOCK_SIZE) cannot be dynamically changed; they are static. But the
next parameter, DB_CREATE_FILE_DEST, can be dynamically changed. In the figure,
it has not been set—but it can be, by entering a value in the box in the column headed
“Value.” To change the static parameters, it is necessary to navigate to the SPFile tab,
and make the changes there.
To change a parameter from SQL*Plus, use the ALTER SYSTEM command. Figure
3-2 shows several examples.
The first query in Figure 3-2 shows that the values for the parameter DB_CREATE_
FILE_DEST are the same in the running instance in memory, and in the spfile on disk.
The next two commands adjust the parameter in both places to different values, by
using the SCOPE keyword. The results are seen in the second query. The final command

uses SCOPE=BOTH to change both the running and the stored value with one
command. The BOTH option is the default, if the SCOPE keyword is not specified.
Figure 3-1 Initialization parameters, as seen through Database Control
Chapter 3: Instance Management
103
PART I
EXAM TIP An attempt to change a static parameter will fail unless the
SCOPE is specified as SPFILE. The default SCOPE is BOTH the running
instance and the spfile. If the instance is started with a pfile, then
SCOPE=SPFILE will fail.
As was seen in Chapter 2, when a database instance is first created, it is built with
a pfile. This may be converted to an spfile using this command:
create spfile [='spfilename'] from pfile [='pfilename'];
If names are not given for spfilename or pfilename, then the default names based on
the ORACLE_HOME and the SID will be assumed. To reverse-engineer an spfile into a
pfile, the command is
create pfile [='pfilename'] from spfile [='spfilename'] ;
The CREATE PFILE and CREATE SPFILE commands can be run from SQL*Plus at
any time, even before the instance has been started.
The Basic Parameters
The instance parameters considered to be “basic” are those that should be considered
for every database. In some cases, the default values will be fine—but it is good
practice to always consider the values of the basic parameters in your database. The
basic parameters and their current values may be queried using
select name,value from v$parameter where isbasic='TRUE' order by name;
A query that may give slightly different results is
select s.name,s.value
from v$spparameter s join v$parameter p on s.name=p.name
where p.isbasic='TRUE' order by name;
Figure 3-2 Changing and querying parameters with SQL*Plus

OCA/OCP Oracle Database 11g All-in-One Exam Guide
104
Any differences are because some parameter changes may have been applied to
the instance but not the spfile (or vice versa). The necessity for the join is because
there is no column on V$SPPARAMETER to show whether a parameter is basic or
advanced. Table 3-1 summarizes the basic parameters.
Parameter Purpose
cluster_database Is the database a RAC or a single instance? That this is basic
indicates that RAC is considered a standard option
compatible The version that the instance will emulate. Normally this would
be the actual version, but it can look like older versions
control_files The name and location of the controlfile copies
db_block_size The default block size for formatting datafiles
db_create_file_dest The default location for datafiles
db_create_online_log_dest_1 The default location for online redo logfiles
db_create_online_log_dest_2 The default location for online redo logfiles multiplexed copies
db_domain The domain name that can be suffixed to the db_name to
generate a globally unique name
db_name The name of the database (the only parameter with no default)
db_recovery_file_dest The location of the flash recovery area
db_recovery_file_dest_size The amount of data that may be written to the flash recovery area
db_unique_name A unique identifier necessary if two databases with the same
db_name are on the same machine
instance_number Used to distinguish two or more RAC instances opening the same
database. Another indication that RAC is considered standard
job_queue_processes The number of processes available to run scheduled jobs
log_archive_dest_1 The destination for archiving redo logfiles
log_archive_dest_2 The destination for multiplexed copies of archived redo logfiles
log_archive_dest_state_1 An indicator for whether the destination is enabled or not
log_archive_dest_state_2 An indicator for whether the destination is enabled or not

nls_language The language of the instance (provides many default formats)
nls_territory The geographical location of the instance (which provides even
more default formats)
open_cursors The number of SQL work areas that a session can have open
at once
pga_aggregate_target The total amount of memory the instance can allocate to PGAs
processes The maximum number of processes (including session server
processes) allowed to connect to the instance
Table 3-1 The Basic Parameters
Chapter 3: Instance Management
105
PART I
All of these basic parameters, as well as some of the advanced parameters, are
discussed in the appropriate chapters.
Changing Parameters
The static parameters can only be changed using an ALTER SYSTEM command with a
SCOPE=SPFILE clause. Remember this command updates the spfile. Static parameters
cannot, by definition, take immediate effect. An example of a static parameter is
LOG_BUFFER. If you want to resize the log buffer to 6MB, you may issue the
command:
alter system set log_buffer=6m;
It will fail with the message “ORA-02095: specified initialization parameter cannot be
modified.” It must be changed with the SCOPE=SPFILE clause. The command will
succeed, but the instance must be restarted for the new value to take effect.
TIP The default log buffer size is probably correct. If you raise it, you may find
that commit processing takes longer. If you make it smaller than its default value,
it will in fact be internally adjusted up to the default size.
remote_listener The addresses of listeners on other machines with which the
instance should register; another parameter that is only relevant
for a RAC

remote_login_passwordfile Whether or not to use an external password file, to permit
password file authentication
rollback_segments Almost deprecated—superseded by the UNDO parameters
that follow
sessions The maximum number of sessions allowed to connect to the
instance
sga_target The size of the SGA, within which Oracle will manage the
various SGA memory structures
shared_servers The number of shared server processes to launch, for sessions
that are not established with dedicated server processes
star_transformation_enabled Whether to permit the optimizer to rewrite queries that join
the dimensions of a fact table
undo_management Whether undo data should be automatically managed in an
undo tablespace, or manually managed in rollback segments
undo_tablespace If using automatic undo management, where the undo data
should reside
Parameter Purpose
Table 3-1 The Basic Parameters (continued)