Database Reference
In-Depth Information
*.compatible='12.1.0.0.0'
*.control_files='/u1/d1/O12C/control01.ctl','/u1/d2/O12C/control02.ctl'
*.db_name='O12C'
...
*.processes=150
*.undo_management='AUTO'
O12C1.instance_number=1
O12C2.instance_number=2
O12C1.local_listener='LISTENER_O12C1'
O12C2.local_listener='LISTENER_O12C2'
O12C1.remote_listener='LISTENER_O12C2'
O12C2.remote_listener='LISTENER_O12C1'
O12C1.thread=1
O12C2.thread=2
O12C1.undo_tablespace='UNDOTBS1'
O12C2.undo_tablespace='UNDOTBS2'
That is, parameter settings that are common to all instances in the cluster would start with
*.
. Parameter settings
that are specific to a single instance, such as the
INSTANCE_NUMBER
and the
THREAD
of redo to be used, are prefixed with
the instance name (the Oracle SID). In the preceding example,
•
The
PFILE
would be for a two-node cluster with instances named
O12C1
and
O12C2
.
•
The
*.db_name = 'O12C'
assignment indicates that all instances using this
SPFILE
will be
mounting a database named
O12C
.
•
O12C1.undo_tablespace='UNDOTBS1'
indicates that the instance named
O12C1
will use that
specific undo tablespace, and so on.
Setting Values in SPFILEs
Once our database is up and running on the
SPFILE
, the next question relates to how we set and change values
contained therein. Remember,
SPFILEs
are binary files and we can't just edit them using a text editor. The answer is to
use the
ALTER SYSTEM
command, which has the following syntax (portions in <> are optional, and the presence of the
pipe symbol indicates “one of the list”):
Alter system set parameter=value <comment='text'> <deferred>
<scope=memory|spfile|both> <sid='sid|*'>
<container=
current
|all>
The
ALTER SYSTEM SET
command, by default, will update the currently running instance and make the change
to the
SPFILE
for you—or in the case of a pluggable database, in the data dictionary of that pluggable database (see the
following section on pluggable databases for more information). This greatly eases administration, and it eliminates
the problems that arose when you used
ALTER SYSTEM
to add or modify parameter settings, but you forgot to update
or missed an
init.ora
parameter file.
Let's take a look at each element of the command:
•
The
parameter=value
assignment supplies the parameter name and the new value for the
parameter. For example,
pga_aggregate_target = 1024m
would set the
pga_aggregate_
target
parameter to a value of 1,024MB (1GB).
