| Oracle® Database Administrator's Guide 10g Release 1 (10.1) Part Number B10739-01 |
|
| Oracle® Database Administrator's Guide 10g Release 1 (10.1) Part Number B10739-01 |
|
The SET DEFAULT ... TABLESPACE clause of the CREATE DATABASE statement to determines the defa
ult type of tablespace for this database in subsequent CREATE TABLESPACE statements. Specify either S
ET DEFAULT BIGFILE TABLESPACE or SET DEFAULT SMALLFILE TABLESPACE. If you omit this clause, the default is a
The use of bigfile tablespaces further enhances the Oracle-managed files feature, be
cause bigfile tablespaces make datafiles completely transparent for users. SQL syntax for the ALTER TABLESPACE statement has been extended to allow you to perform operations on tablespaces, rather than the underlying datafiles.
The <
code>CREATE DATABASE statement shown in "Specifying Oracle-Managed Files at Database Creation
" can be modified as follows to specify that the default type of tablespace is a bigfile tablespace:
CREATE DATABASE rbdb1
USER SYS IDENTIFIED BY pz6r58
USER SYSTEM IDENTIFIED BY y1tz5p
SET DEFAULT BIGFILE TABLESPACE
UNDO TABLESPACE undotbs
DEFAULT TEMPORARY TABLESPACE tempts1;
To dynamically change the default tablespace type
after database creation, use the SET DEFAULT TABLESPACE clause of the ALTER DATABASE statement:
ALTER DATABASE SET DEFAULT BIGFILE TABLESPACE;
You can determine the current default tablespace type
for the database by querying the DATABASE_PROPERTIES data dictionary view as follows:
SELE CT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TBS_TYPE';
The SYSTEM and SYSAUX tablespaces are always created with the default tablespace type.
However, you can explicitly override the default tablespace type for the UNDO and DEFAULT TEMPORARY tablespace during the CREATE DATABASE operation.
For example, you can create a bigfile UNDO tablespace in a database with the default ta
blespace type of smallfile as follows:
CREATE DATABASE rbdb1
...
BIGFILE UNDO TABLESPACE undotbs
DATAFILE '/u01/oracle/oradata/mynewdb/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
You can create a smallfile DEFAULT TEMPO
RARY tablespace in a database with the default tablespace type of bigfile as follows:
CREATE DATABASE rbdb1
SET DEFAULT BIGFILE TABLSPACE
...
S
MALLFILE DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/oracle/oradata/mynewdb/temp01.dbf'
SIZE 20M REUSE
...
<
/pre>
Oracle Database lets you specify the database default time zone and lets you choose the supporting time zone file.
You set the database default time zone by specifying the
SET TIME_ZONE clause of the CREATE DATABASE statement. If omitted, t
he default database time zone is the operating system time zone. The database time zone can be changed for a session with an AL
TER SESSION statement.
|
See Also: Oracle Database Globalizat ion Support Guide for more information about setting the database time zone |
This section provides information
on the time zone files used to support the database time zone, specifically on Solaris platforms. Names of directories, filenames, a
nd environment variables may differ for on other platforms but will probably be the same for all UNIX platforms.
Two time zone files are provided in the Oracle Database installation directory:
$ORACLE_HOME/oracore/zoneinfo/t
imezone.dat
This is the default file. It contains the most commonly used time zones and is smaller, thus enabling bette r database performance.
$ORACLE_HOME/oracore/zoneinfo/timezlrg.dat
This file contain
s a larger set of defined time zones and should be used if you require zones that are not defined in the default timezone.dat file. Use of this larger set of time zone information can affect performance.
To enable the use of the larger time zone datafile, do the following:
Shut down the database.
Set the environmen
t variable ORA_TZFILE to the full path name of the location for the timezlr
g.dat file.
Restart the database.
Once the larger timezlrg.dat is used, it must
continue to be used unless you are sure that none of the nondefault zones are used for data that is stored in the database. Also, al
l databases that share information must use the same time zone datafile.
The time zone files contain the valid time zone names. The following information is included for each zone (note that abbreviations are only used i n conjunction with the zone names):
Offset from UTC (formerly GMT)
Transiti on times for daylight savings
Abbreviation for standard time
Abbreviation f or daylight savings time
To view the time zone names in the file being used by your database, use the following quer y:
SELECT * FROM V$TIMEZONE_NAMES;
The time zone nam es contained in both the larger and smaller time zone files are listed in the Oracle Database Globalization Support Guide.
Some data definition language statements (such as CREATE TABLE) allow the NOLOGGING
clause, which causes some database operations not to generate redo records in the database redo log. The NOLOGGING sett
ing can speed up operations that can be easily recovered outside of the database recovery mechanisms, but it can negatively affect me
dia recovery and standby databases.
Oracle Database lets you force the writing of redo records even when NOLOGGING has been specified in DDL statements. The database never generates redo records for temporary tablespaces and temporary segments, s
o forced logging has no affect for objects.
NOLOGGING mode |
To put the database into FORC
E LOGGING mode, use the FORCE LOGGING clause in the CREATE DATABASE statement. If you do not specify
this clause, the database is not placed into FORCE LOGGING mode.
Use the ALTER DATABASE statement t
o place the database into FORCE LOGGING mode after database creation. This statement can take a considerable time for co
mpletion, because it waits for all unlogged direct writes to complete.
You can cancel FORCE LOGGING mode using th
e following SQL statement:
ALTER DATABASE NO FORCE LOGGING;
Independent of specifying FORCE LOGGING for the database, you can selectively specify FORCE LOGGING or NO FORCE LOGGING at th
e tablespace level. However, if FORCE LOGGING mode is in effect for the database, it takes precedence over the tablespac
e setting. If it is not in effect for the database, then the individual tablespace settings are enforced. Oracle recommends that eith
er the entire database is placed into FORCE LOGGING mode, or individual tablespaces be placed into FORCE LOGGING mode, but not both.
The FORCE LOGGING mode is a persistent attribute of the database. That is, if the datab
ase is shut down and restarted, it remains in the same logging mode. However, if you re-create the control file, the database is not
restarted in the FORCE LOGGING mode unless you specify the FORCE LOGGING clause in the CREATE CONTROL FILE statement.
|
See Also: "Controlling the Writing of Redo Records" fo r information about using theFORCE LOGGING clause for tablespace creation. |
How many media failures are likely to happen? How serious is the damage if
unlogged direct writes cannot be recovered? Is the performance degradation caused by forced logging tole
rable? If the database is running in FORCE LOGGING mode results in some performance degradation. If the primary reason for specifying
NOARCHIVELOG mode, then generally there is no benefit to placing t
he database in FORCE LOGGING mode. Media recovery is not possible in NOARCHIVELOG mode, so if you combine i
t with FORCE LOGGING, the result may be performance degradation with little benefit.
Oracle Database has provided generally appropriate values in the sample initialization parameter file provided with y our database software or created for you by the Database Configuration Assistant. You can edit these Oracle-supplied initialization p arameters and add others, depending upon your configuration and options and how you plan to tune the database. For any relevant initi alization parameters not specifically included in the initialization parameter file, the database supplies defaults.
If you ar
e creating an Oracle Database for the first time, Oracle suggests that you minimize the number of parameter values that you alter. As
you become more familiar with your database and environment, you can dynamically tune many initialization parameters using the ALTER SYSTEM statement. If you are using a traditional text initialization parameter file, your changes are effective only fo
r the current instance. To make them permanent, you must update them manually in the initialization parameter file, or they will be l
ost over the next shutdown and startup of the database. If you are using a server parameter file, initialization parameter file chang
es made by the ALTER SYSTEM statement can persist across shutdown and startup. This is discussed in
"Managing Initialization Parameters Using a Server Parameter File".
This section introduces you to some of the basic initialization parameters you can add or edit before you create your new database.
The follo wing topics are contained in this section:
The COMPATIBLE Initialization Parameter and Irreversible Compatibility
|
See Also: Oracle Database Reference for descriptions of all initialization parameters including their def ault settings |
The global database nam
e consists of the user-specified local database name and the location of the database within a network structure. The DB_NAME initialization parameter determines the local name component of the database name, and the DB_DOMAIN parameter ind
icates the domain (logical location) within a network structure. The combination of the settings for these two parameters must form a
database name that is unique within a network.
For example, to create a database with a global database name of test.us
.acme.com, edit the parameters of the new parameter file as follows:
DB_NAME = test DB_DOMAIN = us.acme.com
You can rename the GLOBAL_NAME of your database using the ALTER DATABASE RENAME GLOBAL_NAM
E statement. However, you must also shut down and restart the database after first changing the DB_NAME and DB_DOMAIN initialization parameters and re-creating the control file.
|
See Also: Oracle Database Utilities for information about using theDBNEWID utility, which is anothe
r means of changing a database name |
DB_NAME must be set to a text string of no more than eight characters. During da
tabase creation, the name provided for DB_NAME is recorded in the datafiles, redo log files, and control file of the dat
abase. If during database instance startup the value of the DB_NAME parameter (in the parameter file) and the database n
ame in the control file are not the same, the database does not start.
DB_DOMAIN is a text string that specifies the network domain where the database is crea
ted. This is typically the name of the organization that owns the database. If the database you are about to create will ever be part
of a distributed database system, give special attention to this initialization parameter before database creation.
|
See Also: Part VII, " Distributed Database Management " for more information about distributed databases |
A flash recovery area is a location in which O racle Database can store and manage files related to backup and recovery. It is distinct from the database area, which is a location for the Oracle-managed current database files (datafiles, control files, and online redo logs).
You specify a flash recovery a rea with the following initialization parameters:
DB_RECOVERY_FILE_DEST: Location of the fl
ash recovery area. This can be a directory, file system, or Automatic Storage Management (ASM) disk group. It cannot be a raw file sy
stem.
In a RAC environment, this location must be on a cluster file system, ASM disk group, or a shared directory configured t hrough NFS.
DB_RECOVERY_FILE_DEST_SIZE: Specifies the maximum total bytes to be used by the
flash recovery area. This initialization parameter must be specified before DB_RECOVERY_FILE_DEST is enabled.
In a RAC environment, the settings for these two parameters must be the same on all instances.
You cannot enable these
parameters if you have set values for the LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST parameters. You mus
t disable those parameters before setting up the flash recovery area. You can instead set values for the LOG_ARCHIVE_DEST_n parameters. If you do not set values for local LOG_ARCHIVE_DEST_n, then
setting up the flash recovery area will implicitly set LOG_ARCHIVE_DEST_10 to the flash recovery area.
Oracle rec ommends using a flash recovery area, because it can simplify backup and recovery operations for your database.
|
See Also: Oracle Database Backup and Recovery Basics to learn how to create and us e a flash recovery area |
The CONTROL_FIL
ES initialization parameter specifies one or more control filenames for the database. When you execute the CREATE DATABA
SE statement, the control files listed in the CONTROL_FILES parameter are created.
If do not include CONTROL_FILES in the initialization parameter file, then Oracle Database creates a control file using a default operating sys
tem dependent filename or, if you have enabled Oracle-managed files, creates Oracle-managed control files .
If you want the da
tabase to create new operating system files when creating database control files, the filenames listed in the CONTROL_FILES parameter must not match any filenames that currently exist on your system. If you want the database to reuse or overwrite existi
ng files when creating database control files, ensure that the filenames listed in the CONTROL_FILES parameter match the
filenames that are to be reused.
Oracle strongly recommends y ou use at least two control files stored on separate physical disk drives for each database.
The DB_BLOCK_SIZE initialization parameter specifies the standard block size for the database. This block size is used for the SYSTEM tablespace and by default in other tablespaces. Oracle Database can support up to four additional nonstandard block sizes.
The most commonly used block size should be picked as t
he standard block size. In many cases, this is the only block size that you need to specify. Typically, DB_BLOCK_SIZE is
set to either 4K or 8K. If you do not set a value for this parameter, the default data block size is operating system specific, whic
h is generally adequate.
You cannot change the block size after database creation except by re-creating the database. If the d
atabase block size is different from the operating system block size, ensure that the database block size is a multiple of the operat
ing system block size. For example, if your operating system block size is 2K (2048 bytes), the following setting for the DB_BL
OCK_SIZE initialization parameter is valid:
DB_BLOCK_SIZE=4096
A larger data block s ize provides greater efficiency in disk and memory I/O (access and storage of data). Therefore, consider specifying a block size larg er than your operating system block size if the following conditions exist:
Oracle Database is on a larg e computer system with a large amount of memory and fast disk drives. For example, databases controlled by mainframe computers with v ast hardware resources typically use a data block size of 4K or greater.
The operating system that runs Oracle Database uses a small operating system block size. For example, if the operating system block size is 1K and the default data block size matches this, the database may be performing an excessive amount of disk I/O during normal operation. For best performance in this case, a database block should consist of multiple operating system blocks.
|
See Also: Your operating system specific Oracle documentation for de tails about the default block size. |
Tablespa
ces of nonstandard block sizes can be created using the CREATE TABLESPACE statement and specifying the BLOCKSIZE clause. These nonstandard block sizes can have any of the following power-of-two values: 2K, 4K, 8K, 16K or 32K. Platform-speci
fic restrictions regarding the maximum block size apply, so some of these sizes may not be allowed on some platforms.
To use n onstandard block sizes, you must configure subcaches within the buffer cache area of the SGA memory for all of the nonstandard block sizes that you intend to use. The initialization parameters used for configuring these subcaches are described in the next section, < a href="#i1014121">"Managing the System Global Area (SGA) ".
The ability to specify multiple block sizes for your database is especially useful if you are transporting tablespaces between databases. You can, for example, transport a tablespace that uses a 4K block size from an OLTP environment to a data warehouse environment that uses a standard block size of 8K.
This section discusses the initialization parameters that a
ffect the amount of memory allocated to the System Global Area (SGA). Except for the SGA_MAX_SIZE initialization parameter, they are dynamic parameters whose values can be cha
nged by the ALTER SYSTEM statement. The size of the SGA is dynamic, and can grow or shrink by dynamically altering these
parameters.
This section contains the following topics:
Viewing Information About the SGA
|
See Also:
|
| SGA Component | Initialization Parameter |
|---|---|
| Fixed SGA and other internal allocations needed by the Oracle Database instance | NA |
| The shared pool | SHARED_POOL_SIZE |
| The large pool |
LARGE_POOL_SIZE |
| The Java pool | JAVA_POOL_SIZE |
| The buffer cache | DB_CACHE_SIZE |
The parameters listed in Table 2-4, if they
are set, take their memory from SGA_TARGET, leaving what is available for the four components listed in Table 2-3.
Table 2-4 Manually Sized SGA Components that Use SGA_TARGET Space
| SGA Component | Initialization Parameter |
|---|---|
| The log buffer | LOG_BUFFER |
| The keep and rec ycle buffer caches | DB_KEEP_CACHE_SIZE
|
| Nonstandard block size buffer c aches | DB_nK_CACHE_SIZE |
| The Streams pool | STREAMS_POOL_SIZ
E |
When you are migrating from a manual management sc
heme, execute the following query on the instance running in manual mode to get a value for SGA_TARGET:
SELECT ( (SELECT SUM(value) FROM V$SGA) - (SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY) ) "SGA_TARGET" FROM DUAL;
You can then remove the parameters that formerly controlled memory for the now automatically sized components.
For example, suppose you currently have the following configuration of parameters on a manual mode instance with SGA_MA
X_SIZE set to 1200M:
SHARED_POOL_SIZE = 200M
DB_C
ACHE_SIZE = 500M
LARGE_POOL_SIZE=200M
Also assume that the result o f the queries is as follows:
SELECT SUM(value) FROM V$SGA = 1200M SELECT CURRENT_VALUE FROM V$SGA_DYNAM IC_FREE_MEMORY = 208M
You can take advantage of automatic shared memory management by replacing the manually sized parame
ters (SHARED_POOL_SIZE, DB_CACHE_SIZE, LARGE_POOL_SIZE) with the following setting:
SGA_TARGET = 992M
where 992M = 1200M minus 208M.
By default, when you set a value for SG
A_TARGET, the value of the parameters corresponding to all the automatically sized SGA components is set to zero. However, you
can still exercise some control over the size of the automatically sized components by specifying minimum values for the parameters
corresponding to these components. Doing so can be useful if you know that an application cannot function properly without a minimum
amount of memory in specific components. You specify the minimum amount of SGA space for a component by setting a value for its corre
sponding initialization parameter. Here is an example configuration:
SGA_TARGET = 256M
SHARED_POOL_SIZE = 32M
DB_CACHE_SIZE = 100M
In this example, the shared pool and the default buffer pool will not be sized smaller than the specified values (32 M and 10 0M, respectively). The remaining 124M (256 minus 132) is available for use by all the manually and automatically sized components.
The actual distribution of values among the SGA components might look like this:
Actual shared pool size = 64M
Actual buffer cache size = 128M
Actual Java pool size = 60M
Actual large pool size = 4M
The parameter values determine the minimum amount of SGA space
allocated. The fixed view V$SGA_DYNAMIC_COMPONENTS displays the current actual size of each SGA component. You can also
see the current actual values of the SGA components in the Enterprise Manager memory configuration page.
Manually limiting th e minimum size of one or more automatically sized components reduces the total amount of memory available for dynamic adjustment. Thi s reduction in turn limits the ability of the system to adapt to workload changes. Therefore, this practice is not recommended except in exceptional cases. The default automatic management behavior maximizes both system performance and the use of available resources .
When the automatic shared memory management feature is enabled, the internal tuning a lgorithm tries to determine an optimal size for the shared pool based on the workload. It usually converges on this value by increasi ng in small increments over time. However, the internal tuning algorithm typically does not attempt to shrink the shared pool, becaus e the presence of open cursors, pinned PL/SQL packages, and other SQL execution state in the shared pool make it impossible to find g ranules that can be freed. Therefore, the tuning algorithmn only tries to increase the shared pool in conservative increments, starti ng from a conservative size and stabilizing the shared pool at a size that produces the optimal performance benefit.
You can modify the value of SGA_TARGET and the par
ameters controlling individual components dynamically using the ALTER SYSTEM statement, as described in the sections tha
t follow.
The SG
A_TARGET parameter can be increased up to the value specified for the SGA_MAX_SIZE parameter, and it can also be
reduced. If you reduce the value of SGA_TARGET, the system identifies one or more automatically tuned components to rele
ase memory. You can reduce SGA_TARGET until one or more automatically tuned components reach their minimum size. Oracle
Database determines the minimum allowable value for SGA_TARGET taking into account several factors, including values set
for the automatically sized components, manually sized components that use SGA_TARGET space, and number of CPUs.
The change in the amount of physical memory consumed when SGA_TARGET is modified depends on the operating system. On so
me UNIX platforms that do not support dynamic shared memory, the physical memory in use by the SGA is equal to the value of the SGA_MAX_SIZE parameter. On such platforms, there is no real benefit in setting SGA_TARGET to a value smaller tha
n SGA_MAX_SIZE. Therefore, setting SGA_MAX_SIZE on those platforms is not recommended.
On other plat
forms, such as Solaris and Windows, the physical memory consumed by the SGA is equal to the value of SGA_TARGET.
When SGA_TARGET is resized, the only components affected are the automatically tuned components for which you have not s
et a minimum value in their corresponding initialization parameter. Any manually configured components remain unaffected.
For example, suppose you have an environment with the following configuration:
SGA_MAX_SIZE = 1
024M
SGA_TARGET = 512M
DB_8K_CACHE_SIZE = 128M
In this example, the value of SGA_TARGET can be resized up to 1024M and can also be reduced until one or
more of the buffer cache, shared pool, large pool, or java pool reaches its minimum size. The exact value depends on environmental fa
ctors such as the number of CPUs on the system. However, the value of DB_8K_CACHE_SIZE remains fixed at all times at 128
M
When SGA_TARGET is reduced, if the values for any automatically tuned component sizes has been specified to lim
it their minimum sizes, then those components will not be reduced smaller than that minimum. Consider the following combination of pa
rameters:
SGA_MAX_SIZE = 1024M
SGA_TARGET = 512M<
/p>
DB_CACHE_SIZE = 96M
DB_8K_CACHE_SIZE = 128M
As in the last example, if SGA_TARGET is reduced, the DB_8K_CACHE_SIZE parameter is permanently
fixed at 128M. In addition, the primary buffer cache (determined by the DB_CACHE_SIZE parameter) will not be reduced sm
aller than 96M. Thus the amount that SGA_TARGET can be reduced is restricted.
When SGA_TARGET is not set,
the automatic shared memory management feature is not enabled. Therefore the rules governing resize for all component parameters are
the same as in earlier releases. However, when automatic shared memory management is enabled, the manually specified sizes of automat
ically sized components serve as a lower bound for the size of the components. You can modify this limit dynamically by changing the
values of the corresponding parameters.
If the specified lower limit for the size of a given SGA component is less than its cu rrent size, there is no immediate change in the size of that component. The new setting only limits the automatic tuning algorithm to that reduced minimum size in the future. For example, consider the following configuration:
SGA_T
ARGET = 512M
LARGE_POOL_SIZE = 256M
Current actual shar ed pool size = 284M
In this example, if you increase the value of LARGE_POOL_SIZE to a value greater th
an the actual current size of the component, the system expands the component to accommodate the increased minimum size. For example,
if you increase the value of LARGE_POOL_SIZE to 300M, then the system increases the large pool until it reaches 300M. T
his resizing occurs at the expense of one or more automatically tuned components.
If you decrease the value of LARGE_POO
L_SIZE to 200, there is no immediate change in the size of that component. The new setting only limits the reduction of the la
rge pool size to 200 M in the future.
Parameters for manually sized components can be dynamically altered as well. However, rather than setting a minimum size, the value of the parameter specifies the precise size of the corresponding component. When you increase the size of a manually sized component, extra memory is taken away from one or more automatically sized components. When you decrease the size of a manually sized component, the memory that is released is given to the automatically sized components.
For example, consider t his configuration:
SGA_TARGET = 512M
DB_8K_CACHE_SIZE = 128M
In this example, increasing DB_8K_CACHE_SIZE by 16 M to 144M means that the 16M will be tak
en away from the automatically sized components. Likewise, reducing DB_8K_CACHE_SIZE by 16M to 112M means that the 16M w
ill be given to the automatically sized components.
If you decide not to use automatic shared memory management by not set
ting the SGA_TARGET parameter, you must manually configure each component of the SGA. This section provides guidelines o
n setting the parameters that control the size of each SGA components.
The buffer cache initialization parameters determine the size of the buffer cache component of the SGA. You use t hem to specify the sizes of caches for the various block sizes used by the database. These initialization parameters are all dynamic.
If you intend to use multiple block sizes in your database, you must have the DB_CACHE_SIZE and at least one nK_CACHE_SIZE parameter set. Oracle Database assigns an appropriate default value to
the DB_CACHE_SIZE parameter, but the DB_nK_CACHE_SIZE parameters default
to 0, and no additional block size caches are configured.
The size of a buffer cache affects performance. Larger cache sizes generally reduce the number of disk reads and writes. However, a large cache may take up too much memory and induce memory paging or swapping.
DB_CACHE_SIZE initialization paramet
er has replaced the DB_BLOCK_BUFFERS initialization parameter, which was used in earlier releases. The DB_CACHE_SI
ZE parameter specifies the size in bytes of the cache of standard block size buffers. Thus, to specify a value for DB_CA
CHE_SIZE, you would determine the number of buffers that you need and multiple that value times the block size specified in For backward compatibility, the DB_BLOCK_BUFFERS parameter still functions, but it rema
ins a static parameter and cannot be combined with any of the dynamic sizing parameters.
The sizes and numbers of nonstandard block size buffers are specified by the following initializati on parameters:
DB_2K_CACHE_SIZE
DB_4K_CACHE_SIZE<
/p>
DB_8K_CACHE_SIZE
DB_16K_CACHE_SIZE
DB_32K_CACHE_SIZE
Each parameter specifies the size of the buffer cache for the correspo nding block size. For example:
DB_BLOCK_SIZE=4096 DB_CACHE_SIZE=12M DB_2K_CACHE_SIZE=8M DB_8K_CACHE_SI ZE=4M
In this example, the parameters specify that the standard block size of the database is 4K. The size of the cache o f standard block size buffers is 12M. Additionally, 2K and 8K caches will be configured with sizes of 8M and 4M respectively.
|
Note: You cannot use aDB_
nK_CACHE_SIZE parameter to size the cache for the standard block size. For example, if the value of DB_BLOCK_SIZE
is 2K, it is invalid to set DB_2K_CACHE_SIZE. The size of the cache for the standard block size is always determined fr
om the value of DB_CACHE_SIZE. |
The SHARED_POOL_SIZE initializat
ion parameter is a dynamic parameter that lets you specify or adjust the size of the shared pool component of the SGA. Oracle Databas
e selects an appropriate default value. Configuring the shared pool is discussed in Oracle Database Performance Tuning Guide.
The LARGE_POOL_SIZE initialization parameter is a dynamic pa
rameter that lets you specify or adjust the size of the large pool component of the SGA. The large pool is an optional component of t
he SGA. You must specifically set the LARGE_POOL_SIZE parameter if you want to create a large pool. Configuring the larg
e pool is discussed in Oracle Database Performanc
e Tuning Guide.
Th
e JAVA_POOL_SIZE initialization parameter is a dynamic parameter that lets you specify or adjust the size of the java po
ol component of the SGA. Oracle Database selects an appropriate default value. Configuration of the java pool is discussed in Oracle Database Java Developer's Guide.
The STREAMS_POOL_SIZE initializatio
n parameter is a dynamic parameter that lets you specify or adjust the size of the Streams pool component of the SGA. If STREAM
S_POOL_SIZE is set to 0, then the Oracle Streams product will use the shared pool to satisfy its SGA memory requirements. Conf
iguration of the Streams pool is discussed in O
racle Streams Concepts and Administration.
The following views provide information about the SGA components and their dynamic resizing:
| V iew | Description |
|---|---|
V$S
GA |
Displays summary information about the system global area (SGA). |
V$SGAINFO |
Displays size information about the SGA, including the sizes of different SGA components, the granule size, an d free memory. |
V$SGASTAT |
Displays detailed information about the SGA. |
| Displays information about the dynamic SGA components. This view summarizes information based on all completed SGA resize operati ons since instance startup. | |
V$SGA_DY
NAMIC_FREE_MEMORY |
Displays information about the amount of SGA memory availa ble for future dynamic SGA resize operations. |
V$SGA_RESIZE_OPS |
Displays information about the last 400 completed SGA resize operations. |
V$SGA_CURREN
T_RESIZE_OPS |
Displays information about SGA resize operations that are curre ntly in progress. A resize operation is an enlargement or reduction of a dynamic SGA component. |
The PROCESSES initialization parameter determines the maximum number of operating system processes that can be connected to O
racle Database concurrently. The value of this parameter must be a minimum of one for each background process plus one for each user
process. The number of background processes will vary according the database features that you are using. For example, if you are usi
ng Advanced Queuing or the file mapping feature, you will have additional background processes. If you are using Automatic Storage Ma
nagement, then add three additional processes.
If you plan on running 50 user processes, a good estimate would be to set the < code>PROCESSES initialization parameter to 60.
Every Oracle Database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they ar e committed. Collectively these records are called undo data.
The use of manual undo management mode, which s tores undo data in rollback segments, has been deprecated. This section provides instructions for setting up an environment for autom atic undo management using an undo tablespace. An undo tablespace is easier to administer than rollback segments, and automatic undo management lets you explicitly set a retention guarantee time.
The UNDO
_MANAGEMENT initialization parameter determines whether an instance will start up in automatic undo management mode, which sto
res undo in an undo tablespace, or manual undo management mode, which stores undo in rollback segments. By default, this parameter is
set to MANUAL. Set this parameter to AUTO to enable automatic undo management mode.
When an instance starts up in automatic undo management mode, it attempts to select an undo tablespace for storage of undo data. If
the database was created in undo management mode, then the default undo tablespace--either the system-created SYS_UNDOTS
tablespace or the user-specified undo tablespace--is the undo tablespace used at instance startup. You can override this default for
the instance by specifying a value for the UNDO_TABLESPACE initialization parameter. This parameter is especially usefu
l for assigning a particular undo tablespace to an instance in an Oracle Real Application Clusters environment.
If no undo tab
lespace has been specified during database creation or by the UNDO_TABLESPACE initialization parameter, then the instanc
e will start. However, it uses the SYSTEM rollback segment for storage of undo data. This use of the SYSTEM
rollback segment is not recommended in normal circumstances, and an alert message is written to the alert file to warn that the syst
em is running without an undo tablespace. ORA-01552 errors are issued for any attempts to write non-SYSTEM related undo
data to the SYSTEM rollback segment.
The COMPATIBLE initializat
ion parameter enables or disables the use of features in the database that affect file format on disk. For example, if you create an
Oracle Database 10g database, but specify COMPATIBLE = 9.2.0.2 in the initialization parameter file, then featu
res that requires 10.0 compatibility will generate an error if you try to use them. Such a database is said to be at the 9.2.0.2 comp
atibility level.
You can advance the compatibility level of your database. If you do advance the compatibility of your databas
e with the COMPATIBLE initialization parameter, then there is no way to start the database using a lower compatibility l
evel setting, except by doing a point-in-time recovery to a time before the compatibility was advanced.
The default value for
the COMPATIBLE parameter is the release number of the most recent major release.
|
< font face="arial, helvetica, sans-serif">Caution: For Oracle Database 10g Release 1 (10.1), the default value of theCOMPATIBLE parameter is 10.0.0. If you create an Oracle Database using this default value, you can
immediately use all the new features in this release, and you can never downgrade the database. |
|
See Als o:
|
|
Note: Oracle no longer offers licensing by the number of concurrent sessions. Therefore theLICENSE_MAX_SESSIONS and LICENSE_S
ESSIONS_WARNING initialization parameters are no longer needed and have been deprecated. |
If you use named user licensing, Oracle Database can help you enforce this form of licensing. You can set a limit on the number of users created in t he database. Once this limit is reached, you cannot create more users.
|
Note: This mechanism assumes that each person accessing the database has a unique user name and that no people share a user name. Therefore, so that named user licensing can help you ensure compliance with your Oracle li cense agreement, do not allow multiple users to log in using the same user name. |
To limit the number of users created in a database, set the LICENSE_MAX_USERS initialization par
ameter in the database initialization parameter file, as shown in the following example:
LICENSE_MAX_US ERS = 200
If database creation fails, you can look at the alert log to determine the reason for the failure and to determine corrective ac tion. The alert log is discussed in "Monitoring the Operation of Your Database".
You should
shut down the instance and delete any files created by the CREATE DATABASE statement before you attempt to create it ag
ain. After correcting the error that caused the failure of the database creation, try re-creating the database.
Dropping a database involves removing its datafiles, redo log files, control files, and initialization paramete
r files. The DROP DATABASE statement deletes all control files and all other data
base files listed in the control file. To use the DROP DATABASE statement successfully, all of the following conditions
must apply:
The database must be mounted and closed.
The database must be m ounted exclusively--not in shared mode.
The database must be mounted as RESTRICTED.
An example of this statement is:
DROP DATABASE;
The DROP DATABASE st
atement has no effect on archived log files, nor does it have any effect on copies or backups of the database. It is best to use RMAN
to delete such files. If the database is on raw disks, the actual raw disk special files are not deleted.
If you used the Dat abase Configuration Assistant to create your database, you can use that tool to delete (drop) your database and remove the files.
Initializ ation parameters for the Oracle Database have traditionally been stored in a text initialization parameter file. For better manageabi lity, you can choose to maintain initialization parameters in a binary server parameter file that is persistent across database start up and shutdown. This section introduces the server parameter file, and explains how to manage initialization parameters using either method of storing the parameters. The following topics are contained in this section.
A server para
meter file (SPFILE) can be thought of as a repository for initialization parameters that is maintained on the m
achine running the Oracle Database server. It is, by design, a server-side initialization parameter file. Initialization parameters s
tored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist
across instance shutdown and startup. This arrangement eliminates the need to manually update initialization parameters to make chang
es effected by ALTER SYSTEM statements persistent. It also provides a basis for self-tuning by the Oracle Database serve
r.
A server parameter file is initially built from a traditional text initialization parameter file using the CREATE SPF
ILE statement. It is a binary file that cannot be edited using a text editor. Oracle Database provides other interfaces for vi
ewing and modifying parameter settings.
|
Cauti on: Although you can open the binary server parameter file with a text editor and view its text, do not manually edit it. Doing so will corrupt the file. You will not be able to start your instance, and if the instance is running, it cou ld fail. |
At system startup, the default behavior of the <
code>STARTUP command is to read a server paramet
er file to obtain initialization parameter settings. The STARTUP command with no PFILE clause reads the ser
ver parameter file from an operating system specific location. If you use a traditional text initialization parameter file, you must
specify the PFILE clause when issuing the STARTUP command. Instructions for starting an instance using a se
rver parameter file are contained in "Starting Up a Database".
If you are current ly using a traditional initialization parameter file, use the following steps to migrate to a server parameter file.
If the initialization parameter file is located on a client machine, transfer the file (for example, FTP) from th e client machine to the server machine.
|
Note: If you are migrating to a server parameter file in an Oracle Real Application Clusters environment, you must com bine all of your instance-specific initialization parameter files into a single initialization parameter file. Instructions for doing this, and other actions unique to using a server parameter file for instances that are part of an Oracle Real Application Clusters i nstallation, are discussed in: |
Create a server parameter file using the CREA
TE SPFILE statement. This statement reads the initialization parameter file to create a server parameter file. The database do
es not have to be started to issue a CREATE SPFILE statement.
Start up the instance using the newly cre ated server parameter file.
The server parameter file is initially created from a traditional text initializ
ation parameter file. It must be created prior to its use in the STARTUP command. The CREATE SPFILE statement is used to create a server parameter file. You must have the SYSDBA or the
The following example creates a server parameter file from initi
alization parameter file /u01/oracle/dbs/init.ora. In this example no SPFILE name is specified, so the file
is created in a platform-specific default location and is named spfile$ORACLE_SID.ora.
CR EATE SPFILE FROM PFILE='/u01/oracle/dbs/init.ora';
Another example, which follows, illustrates creating a server paramete r file and supplying a name.
CREATE SPFILE='/u01/oracle/dbs/test_spfile.ora'
FROM PFILE='/u01/or
acle/dbs/test_init.ora';
When you create a server parameter file from an initialization parameter file, comments specifie d on the same lines as a parameter setting in the initialization parameter file are maintained in the server parameter file. All othe r comments are ignored.
The server parameter file is always created on the machine running the database server. If a server pa rameter file of the same name already exists on the server, it is overwritten with the new information.
Oracle recommends that
you allow the database server to default the name and location of the server parameter file. This will ease administration of your d
atabase. For example, the STARTUP command assumes this default location to read the parameter file. The table that follo
ws shows the default name and location of the server parameter file. The table assumes that the SPFILE is a file. If it
is a raw device, the default name could be a logical volume name or partition device name, and the default location could differ.
| Platform | Default Name | Default Location |
|---|---|---|
| UNIX | spfiledbname.ora |
$ORA
CLE_HOME/dbs or the same location as the datafiles |
| Windows | spfileSID |
ORACLE_BASE\ORACLE_HOME\database | <
/tr>
If you create a server parameter file in a location other tha
n the default location, then you must create a parameter file with the default parameter file name in the default location that point
s to the user-generated SPFILE. The parameter file would contain one line:
SPFILE = 'l ocation'
The CREATE SPFILE statement can be executed before or after instance startup. However, if the
instance has been started using a server parameter file, an error is raised if you attempt to re-create the same server parameter fil
e that is currently being used by the instance.
|
|
The SPFILE initialization parameter contains the name of th
e current server parameter file. When the default server parameter file is used by the server (that is, you issue a STARTUP command and do not specify a PFILE), the value of SPFILE is internally set by the server. The SQL*Plus
command SHOW PARAMETERS SPFILE (or any other method of querying the value of a parameter) displays the name of the serve
r parameter file that is currently in use.
The SPFILE parameter can also be set in a traditional parameter file t
o indicate the server parameter file to use. You use the SPFILE parameter to specify a server parameter file located in
a nondefault location. Do not use an IFILE initialization parameter within a traditional initialization paramet
er file to point to a server parameter file; instead, use the SPFILE parameter. See "Starti
ng Up a Database" for details about:
Starting up a database that uses a server parameter file
Using the SPFILE parameter to specify the name of a server parameter file to use at instance star
tup
The ALTER SYSTEM stateme
nt lets you set, change, or restore to default the values of initialization parameter. If you are using a traditional initialization
parameter file, the ALTER SYSTEM statement changes the value of a parameter only for the current instance, because there
is no mechanism for automatically updating initialization parameters on disk. You must update them manually to be passed to a future
instance. Using a server parameter file overcomes this limitation.
Use t
he SET clause of the ALTER SYSTEM statement to set or change initialization parameter values. The optional SCOPE clause specifies the scope of a chan
ge as described in the following table:
| SCOPE Clause | Description | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|
SCOPE = SPFILE |
The change is applied i
n the server parameter file only. The effect is as follows:
| |||||||||
SCOPE = MEMORY |
The change is applied in memory only. The effect is as follows:
| |||||||||
SCOPE = BOTH |
The change is applied in both the server
parameter file and memory. The effect is as follows:
|
| Method | Description | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|
SHOW PARAMETERS |
This SQL*Plus command displays the values of parameters currently in use. | |||||||||
CREATE PFILE |
This SQL s tatement creates a text initialization parameter file from the binary server parameter file. | |||||||||
V$PARAMETER |
This vi ew displays the values of parameters currently in effect. | |||||||||
V$PARAMETER2 |
This view displays the values of paramete
rs currently in effect. It is easier to distinguish list parameter values in this view because each list parameter value appears as a
row.
I think this means "For parameters with multiple attributes, this view displays each attribute on a separate row." DL | |||||||||
V$SPPARAMETER |
This view displays the current contents of the server parameter file. The view returns FALSE<
/code> values in the |
|
See Also: Oracle Real Application Clust ers Deployment and Performance Guide for more information about services in RAC |
Installations configure Oracle Database 10g services in the database giving each service a unique global name, associated performance goals, and associated importance. The servi ces are tightly integrated with the Oracle Database and are maintained in the data dictionary. You can find service information in th e following service-specific views:
DBA_SERVICES
ALL_SER
VICES or V$SERVICES
V$ACTIVE_SERVICES
V$SERVICE_EVENTS
V$SERVICE_WAIT
_CLASSES
V$SERV_MOD_ACT_STATS
V$SERVICE_METRICS
V$SERVICE_METRICS_HISTORY
The following additional views also contai n some information about services:
V$SESSION
V$ACTIVE_SE
SSION_HISTORY
DBA_RSRC_GROUP_MAPPINGS
DBA_SCHEDUL
ER_JOB_CLASSES
DBA_THRESHOLDS
Several Oracle Database features support services. The Automatic Workload Repository (AWR) m anages the performance of services. AWR records service performance, including execution times, wait classes, and resources consumed by service. AWR alerts warn when service response time thresholds are exceeded. The dynamic views report current service performance metrics with one hour of history. Each service has quality-of-service thresholds for response time and CPU consumption.
In add ition, the Database Resource Manager maps services to consumer groups. This enables you to automatically manage the priority of one s ervice relative to others. You can use consumer groups to define relative priority in terms of either ratios or resource consumption. This is described in more detail, for example, in Oracle Real Appl ication Clusters Deployment and Performance Guide.
Services describe applications, application functions, and data ranges as either functional services or data-dependent servi ces. Functional services are the most common mapping of workloads. Sessions using a particular function are grouped together. For Ora cle*Applications, ERP, CRM, and iSupport functions create a functional division of the work. For SAP, dialog and update func tions create a functional division of the work.
In contrast, data-dependent routing routes sessions to services based on data keys. The mapping of work requests to services occurs in the object relational mapping layer for application servers and TP monitors. For example, in RAC, these ranges can be completely dynamic and based on demand because the database is shared.
You can also define preconnect application services in RAC databases. Preconnect services span instances to support a service in the event of a fa ilure. The preconnect service supports TAF preconnect mode and is managed transparently when using RAC.
In addition to applica
tion services, Oracle Database also supports two internal services: SYS$BACKGROUND is used by the background processes o
nly and SYS$USERS is the default service for user sessions that are not associated with services.
Use the D
BMS_SERVICE package or set the SERVICE_NAMES parameter to create application services on a single-instance Oracle
Database. You can later define the response time goal or importance of each service through EM, either individually or by using the
Enterprise Manager feature "Copy Thresholds From a Baseline" on the Manage Metrics/Edit Threshold pages. You can also do this using P
L/SQL.
Using services requires no changes to your applica tion code. Client-side work connects to a service. Server-side work specifies the service when creating the job class for the Job Sch eduler and the database links for distributed databases. Work requests executing under a service inherit the performance thresholds f or the service and are measured as part of the service.
Middle-tier applications and client-server applications use a service by specifying the service as part of
the connection in TNS connect data. This connect data may be in the TNSnames file for thick Net drivers, in the URL specification for
thin drivers, or may be maintained in the Oracle Internet Directory. For example, data sources for the Oracle Application Server 10<
em>g are set to route to a service. Using Net Easy*Connection in Oracle Database 10g, this connection needs only the se
rvice and network address, for example, hr/hr@//myVIP/myservice. For Oracle E-Business Suite, the service is also mainta
ined in the application database identifier and in the cookie for the ICX parameters.
Server-side work, such as the Job Scheduler, par allel execution, and Oracle Streams Advanced Queuing, set the service name as part of the workload definition.
For the Job Sch eduler, the service that the job class uses is defined when the job class is created. During execution, jobs are assigned to job clas ses, and job classes run within services. Using services with job classes ensures that the work executed by the job scheduler is iden tified for workload management and performance tuning.
For parallel query and parallel DML, the query coordinator connects to a service just like any other client. The parallel query processes inherit the service for the duration of the execution. At the end of query execution, the parallel execution processes revert to the default service.
After you create a database, the instance is left running, and the database is open and available for normal database use. You may want to perform other actions, some of which are discussed in this section.
|
Note Regarding Security Enhancements: In this release of Oracle Database and in subsequent releases, several enh ancements are being made to ensure the security of default database user accounts. You can find a security checklist for this release in Oracle Database Security Guide. O racle recommends that you read this checklist and configure your database accordingly. |
Once the database is created, you can configure it to take advantage of Oracle Identity Management. For
information on how to do this, please refer to
A newly created database has
at least three user accounts that are important for administering your database: SYS, SYSTEM, and SY
SMAN.
|
Caution: To prevent unauthorized access and protect the integrity of your database, it is important that new passwords for user accountsSY
S and SYSTEM be specified when the database is created. This is accomplished by specifying the following CR
EATE DATABASE clauses when manually creating you database, or by using DBCA to create the database:
|
Additional administrative accounts are provided by Oracle Database that should be used only by authorized users. To protect these accounts from being used by unauthorized users familiar with their Oracle-s upplied passwords, these accounts are initially locked with their passwords expired. As the database administrator, you are responsib le for the unlocking and resetting of these accounts.
Table 2-5 lists the administrative accounts that are provided by Oracle Database. Not all accounts may be present on your system, depending upon the options that you selected for yo ur database.
| Username | Password | Description | See Also |
|---|---|---|---|
CTXSYS |
CTXSYS |
The Oracle Text a ccount | Oracle Te
xt Reference |
DBSNMP |
DBSNMP |
The acc ount used by the Management Agent component of Oracle Enterprise Manager to monitor and manage the database | Oracle Enterprise Manager Grid Control I
nstallation and Basic Configuration |
LBACSYS |
LBACSYS |
The Oracle Label Security administrator account | Oracle Label Security Administrator's Guide |
MDDATA |
MDDATA |
The schema used by Oracle Spatial for storing Geocoder a nd router data | Oracle Spatial User's Guide and Reference |
MDSYS |
MDSYS |
The Oracle Spatial and Oracle interMedia Locator administrator account | Oracle Spatial User's Guide and Reference
|
DMSYS |
DMSYS |
The Oracle Data Mining acc ount. | Oracle
Data Mining Administrator's Guide
|
OL
APSYS |
MANAGER |
The account used to create OLAP metadata structures. It owns the OLAP Catalog (CWMLite). | Oracle OLAP Application Developer's Guide |
ORDPLUGINS |
ORDPLUGINS |
The Oracle interMedi a user. Plug-ins supplied by Oracle and third party format plug-ins are installed in this schema. | Oracle interMedia User's Guide |
ORDSYS |
ORDSYS |
The Oracle interMedia admi nistrator account | Oracle interMedia User's Guide |
OUTLN |
OUTLN |
The account that supports plan stability. Plan stability enables you to maintain the same execution plans for the sa me SQL statements. OUTLN acts as a role to centrally manage metadata associated with stored outlines. | Oracle Database Performance Tuning Guide |
SI_INFORMTN_SCHEMA |
SI_INFORMTN_SCHEMA |
The account that stores the information views for the SQL/MM Still Image Standard | Oracle interMedia User's Guide |
SYS |
CHANGE_ON_INSTALL |
The account used to perform database a dministration tasks | Oracle Database Administrator's Guide |
SYSMAN |
CHANGE_ON_INSTALL |
The account used to perform Oracle Enterprise Manager database administration tasks. Note that SYS and SYSTEM can also perform these tasks. | Oracle Enterprise Manager Grid Control Installation and Basic Configuration |
SYSTEM |
MANAGER |
Another account used to perform database administration tasks. | Oracle Database Administrator's Guide |
The Oracle Database distribution media can includes various SQL files that let you experiment with the system, learn SQ L, or create additional tables, views, or synonyms.
Oracle Database includes sample schemas that help you to become familiar w ith Oracle Database functionality. All Oracle Database documentation and training materials are being converted to the Sample Schemas environment as those materials are updated.
The Sample Schemas can be installed automatically by the Database Configuration A ssistant, or you can install them manually. The schemas and installation instructions are described in detail in Oracle Database Sample Schemas.
In addition to the views listed previously in "Viewing Parameter Settings", you can view information about your database content and structu re using the following views:
| View | Description |
|---|---|
DATABASE_PROPERTIES |
Displays permanent database p roperties |
GLOBAL_NAME |
|
V$DATABASE |
Contains database inform ation from the control file |
|
 Copyright © 2001, 2003&nbs p;Oracle Corporation All Rights Reserved. |
 Home  <
br />
Book List Contents  Index  Master Index  Feedback |
