Database 版 (精华区)
发信人: rhine (风雨无阻), 信区: Database
标 题: Oracle DBA Guide V8, part 2
发信站: 哈工大紫丁香 (2000年07月08日20:26:00 星期六), 站内信件
发信人: chendu (good), 信区: Database
标 题: Oracle DBA Guide V8, part 2
发信站: BBS 水木清华站 (Sat Nov 6 18:50:10 1999)
Hi, required by several friends, I am posting the
Oracle Database Administrator's Guide version 8
here. This is part 2.
Web Master, if my post it too long and not good for
the bbs, please delete it and I am sorry for it.
Hoping everyone enjoy it!
Good luck!
PS: please do not ask me question by email me
directly, this will cause some delay. (PPMM can ^_^)
----------------------------------------------------------------------------
----
2
Creating an Oracle Database
This chapter lists the steps necessary to create an Oracle database, and inc
ludees the following topics:
Considerations Before Creating a Database
Creating an Oracle Database
Parameters
Considerations After Creating a Database
Initial Tuning Guidelines
Considerations Before Creating a Database
This section includes the following topics:
Creation Prerequisites
Using an Initial Database
Migrating an Older Version of the Database
Database creation prepares several operating system files so they can work t
ogetther as an Oracle database. You need only create a database once, regard
less of how many datafiles it has or how many instances access it. Creating
a database can also erase
information in an existing database and create a new database with the same
namee and physical structure.
Creating a database includes the following operations:
creating new datafiles or erasing data that existed in previous datafiles
information creating structures that Oracle requires to access and use the d
atabbase (the data dictionary)
creating and initializing the control files and redo log files for the datab
ase
Consider the following issues before you create a database:
Plan your database tables and indexes, and estimate how much space they will
reqquire.
Plan how to protect your new database, including the configuration of its on
linee and archived redo log (and how much space it will require), and a back
up strategy.
Select the database character set. You must specify the database character s
et wwhen you create the database. After the database is created, you cannot
change the character set choices without re-creating the database. Hence, it
is important that you
carefully consider which character set(s) to use. All character data, includ
ing data in the data dictionary, is stored in the database character set. If
users access the database using a different character set, the database cha
racter set should be the
same as, or a superset of, all character sets they use.
Also become familiar with the principles and options of starting up and shut
tingg down an instance, mounting and opening a database, and using parameter
files.
See Also: "National Language Support" in the Oracle8 Reference.
For information about tables, indexes, and space management, see Chapters 9
throough 17.
For information about the online and archive redo logs, see Chapters 5 and 2
3 reespectively.
For information about database backup and recovery, see the Oracle8 Backup a
nd RRecovery Guide.
Creation Prerequisites
To create a new database, you must have the following:
the operating system privileges associated with a fully operational database
admministrator
sufficient memory to start the Oracle instance
sufficient disk storage space for the planned database on the computer that
execcutes Oracle
Using an Initial Database
Depending on your operating system, a database might have been created autom
aticcally as part of the installation procedure for Oracle. You can use this
initial database and customize it to meet your information management requi
rements, or discard it
and create one or more new databases to replace it.
Migrating an Older Version of the Database
If you are using a previous release of Oracle, database creation is required
onlly if you want an entirely new database. Otherwise, you can migrate your
existing Oracle databases managed by a previous version of Oracle and use t
hem with the new version
of the Oracle software.
See Also: Oracle8 Migration manual for information about migrating an existi
ng ddatabase.
For more information about migrating an existing database, see your operatin
g syystem-specific Oracle documentation.
Creating an Oracle Database
This section includes the following topics:
Steps for Creating an Oracle Database
Creating a Database: Example
Troubleshooting Database Creation
Dropping a Database
Steps for Creating an Oracle Database
These steps, which describe how to create an Oracle database, should be foll
owedd in the order presented.
To Create a New Database and Make It Available for System Use
Available for System Use
Back up any existing databases.
Create parameter files.
Edit new parameter files.
Check the instance identifier for your system.
Start Enterprise Manager and connect to Oracle as an administrator.
Start an instance.
Create the database.
Back up the database.
See Also: These steps provide general information about database creation on
alll operating systems. See your operating system-specific Oracle documenta
tion for information about creating databases on your platform.
Step 1: Back up any existing databases.
Oracle Corporation strongly recommends that you make complete backups of all
exiisting databases before creating a new database, in case database creati
on accidentally affects some existing files. Backup should include parameter
files, datafiles, redo
log files, and control files.
Step 2: Create parameter files.
The instance (System Global Area and background processes) for any Oracle da
tabaase is started using a parameter file.
Each database on your system should have at least one customized parameter f
ile that corresponds only to that database. Do not use the same file for sev
eral databases.
To create a parameter file for the database you are about to make, use your
operrating system to make a copy of the parameter file that Oracle provided
on the distribution media. Give this copy a new filename. You can then edit
and customize this new
file for the new database.
file for the new database.
See Also: For more information about copying the parameter file, see your op
eratting system-specific Oracle documentation.
----------------------------------------------------------------------------
----
Note:
In distributed processing environments, Enterprise Manager is often executed
froom a client machine of the network. If a client machine is being used to
execute Enterprise Manager and create a new database, you need to copy the
new parameter file
(currently located on the computer executing Oracle) to your client workstat
ion.. This procedure is operating system dependent. For more information abo
ut copying files among the computers of your network, see your operating sys
tem-specific Oracle
documentation.
----------------------------------------------------------------------------
----
Step 3: Edit new parameter files.
To create a new database, inspect and edit the following parameters of the n
ew pparameter file:
DB_NAME
DB_NAME
DB_DOMAIN
CONTROL_FILES
DB_BLOCK_SIZE
DB_BLOCK_BUFFERS
PROCESSES
ROLLBACK_SEGMENTS
You should also edit the appropriate license parameter(s):
LICENSE_MAX_SESSIONS
LICENSE_SESSION_WARNING
LICENSE_MAX_USERS
Step 4: Check the instance identifier for your system.
If you have other databases, check the Oracle instances identifier. The Orac
le iinstance identifier should match the name of the database (the value of
DB_NAME) to avoid confusion with other Oracle instances that are running con
currently on your system.
See your operating system-specific Oracle documentation for more information
.
Step 5: Start Enterprise Manager and connect to Oracle as an administrator.
Once Enterprise Manager is running, connect to the database as an administra
tor.
See Also: Starting Enterprise Manager is operating system specific; see your
opeerating system-specific Oracle documentation for details.
Step 6: Start an instance.
To start an instance (System Global Area and background processes) to be use
d wiith the new database, use the Startup Database dialog box of Enterprise
Manager. In the Startup Database dialog box, make sure that you have selecte
d the Startup Nomount
radio button.
After selecting the Startup Nomount, the instance starts. At this point, the
re iis no database. Only an SGA and background processes are started in prep
aration for the creation of a new database.
Step 7: Create the database.
To create the new database, use the SQL command CREATE DATABASE, optionally
settting parameters within the statement to name the database, establish max
imum numbers of files, name the files and set their sizes, and so on.
When you execute a CREATE DATABASE statement, Oracle performs the following
operrations:
creates the datafiles for the database
creates the control files for the database
creates the redo log files for the database
creates the SYSTEM tablespace and the SYSTEM rollback segment
creates the data dictionary
creates the users SYS and SYSTEM
specifies the character set that stores data in the database
mounts and opens the database for use
----------------------------------------------------------------------------
----
warning:
Make sure that the datafiles and redo log files that you specify do not conf
lictt with files of another database.
----------------------------------------------------------------------------
----
Step 8: Back up the database.
You should make a full backup of the database to ensure that you have a comp
letee set of files from which to recover if a media failure occurs.
See Also: Oracle8 Backup and Recovery Guide.
For more information about parameter files see "Using Parameter Files".
For information about the CREATE DATABASE command, character sets, and datab
ase creation see Oracle8 SQL Reference.
Creating a Database: Example
The following statement is an example of a CREATE DATABASE statement:
CREATE DATABASE test
DATAFILE 'test_system' SIZE 10M
LOGFILE GROUP 1 ('test_log1a', 'test_log1b') SIZE 500K,
GROUP 2 ('test_log2a', 'test_log2b') SIZE 500K;
The values of the MAXLOGFILES, MAXLOGMEMBERS, MAXDATAFILES, MAXLOGHISTORY, a
nd MMAXINSTANCES options in this example assume the default values, which ar
e operating system-dependent. The database is mounted in the default modes N
OARCHIVELOG and EXCLUSIVE
and then opened.
The items and information in the example statement above result in creating
a daatabase with the following characteristics:
The new database is named TEST.
The SYSTEM tablespace of the new database is comprised of one 10 MB datafile
nammed TEST_SYSTEM.
The new database has two online redo log groups, each containing two 500 KB
membbers.
The new database does not overwrite any existing control files specified in
the parameter file.
----------------------------------------------------------------------------
----
Note:
You can set several limits during database creation. Some of these limits ar
e allso subject to superseding limits of the operating system and can affect
each other. For example, if you set MAXDATAFILES, Oracle allocates enough s
pace in the control file
to store MAXDATAFILES filenames, even if the database has only one datafile
inittially; because the maximum control file size is limited and operating s
ystem-dependent, you might not be able to set all CREATE DATABASE parameters
at their theoretical
maximums.
----------------------------------------------------------------------------
----
See Also: For more information about setting limits during database creation
, seee the Oracle8 SQL Reference.
See your operating system-specific Oracle documentation for information abou
t opperating system limits.
Troubleshooting Database Creation
If for any reason database creation fails, shut down the instance and delete
anyy files created by the CREATE DATABASE statement before you attempt to c
reate it once again.
After correcting the error that caused the failure of the database creation,
retturn to Step 6 of "Creating an Oracle Database".
Dropping a Database
To drop a database, remove its datafiles, redo log files, and all other asso
ciatted files (control files, parameter files, archived log files).
To view the names of the database's datafiles and redo log files, query the
dataa dictionary views V$DATAFILE and V$LOGFILE.
See Also: For more information about these views, see the Oracle8 Reference.
Parameters
As described in Step 3 of the section "Creating an Oracle Database", Oracle
sugggests you alter a minimum set of parameters. These parameters are descri
bed in the following sections:
DB_NAME and DB_DOMAIN
CONTROL_FILES
DB_BLOCK_SIZE
PROCESSES
ROLLBACK_SEGMENTS
License Parameters
DB_BLOCK_BUFFERS
DB_BLOCK_BUFFERS
LICENSE_MAX_SESSIONS_and LICENSE_SESSIONS WARNING
LICENSE_MAX_USERS
DB_NAME and DB_DOMAIN
A database's global database name (name and location within a network struct
ure)) is created by setting both the DB_NAME and DB_DOMAIN parameters before
database creation. After creation, the database's name cannot be easily cha
nged. The DB_NAME
parameter determines the local name component of the database's name, while
the DB_DOMAIN parameter indicates the domain (logical location) within a net
work structure. The combination of the settings for these two parameters sho
uld form a database name
that is unique within a network. For example, to create a database with a gl
oball database name of TEST.US.ACME.COM, edit the parameters of the new para
meter file as follows:
DB_NAME = TEST
DB_DOMAIN = US.ACME.COM
DB_NAME must be set to a text string of no more than eight characters. Durin
g daatabase creation, the name provided for DB_NAME is recorded in the dataf
iles, redo log files, and control file of the database. If during database i
nstance startup the value
of the DB_NAME parameter (of the parameter file) and the database name in th
e coontrol file are not the same, the database does not start.
DB_DOMAIN is a text string that specifies the network domain where the datab
ase is created; 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 di
stributed database
system, pay special attention to this initialization parameter before databa
se ccreation.
See Also: For more information about distributed databases, see Oracle8 Dist
ribuuted Database Systems.
CONTROL_FILES
Include the CONTROL_FILES parameter in your new parameter file and set its v
aluee to a list of control filenames to use for the new database. If you wan
t Oracle to create new operating system files when creating your database's
control files, make sure
that the filenames listed in the CONTROL_FILES parameter do not match any fi
lenaames that currently exist on your system. If you want Oracle to reuse or
overwrite existing files when creating your database's control files, make
sure that the filenames
listed in the CONTROL_FILES parameter match the filenames that currently exi
st.
listed in the CONTROL_FILES parameter match the filenames that currently exi
st.
----------------------------------------------------------------------------
----
warning:
Use extreme caution when setting this option. If you inadvertently specify a
fille that you did not intend and execute the CREATE DATABASE statement, th
e previous contents of that file will be overwritten.
----------------------------------------------------------------------------
----
If no filenames are listed for the CONTROL_FILES parameter, Oracle uses a de
faullt filename.
Oracle Corporation strongly recommends you use at least two control files st
oredd on separate physical disk drives for each database. Therefore, when sp
ecifying the CONTROL_FILES parameter of the new parameter file, follow these
guidelines:
List at least two filenames for the CONTROL_FILES parameter.
Place each control file on a separate physical disk drives by fully specifyi
ng ddrives for each filename.
----------------------------------------------------------------------------
----
Note:
The file specification for control files is operating system-dependent. Rega
rdleess of your operating system, always fully specify filenames for your co
ntrol files.
----------------------------------------------------------------------------
----
When you execute the CREATE DATABASE statement (in Step 7), the control file
s liisted in the CONTROL_FILES parameter of the parameter file will be creat
ed.
See Also: The default filename for the CONTROL_FILES parameter is operating
systtem-dependent. See your operating system-specific Oracle documentation f
or details.
DB_BLOCK_SIZE
The default data block size for every Oracle server is operating system-spec
ificc. The Oracle data block size is typically either 2K or 4K. Generally, t
he default data block size is adequate. In some cases, however, a larger dat
a block size provides
greater efficiency in disk and memory I/O (access and storage of data). Such
casses include:
Oracle is on a large computer system with a large amount of memory and fast
diskk drives. For example, databases controlled by mainframe computers with
vast hardware resources typically use a data block size of 4K or greater.
The operating system that runs Oracle uses a small operating system block si
ze. For example, if the operating system block size is 1K and the data block
size matches this, Oracle may be performing an excessive amount of disk I/O
during normal
operation. For best performance in this case, a database block should consis
t off multiple operating system blocks.
Each database's block size is set during database creation by the initializa
tionn parameter DB_BLOCK_SIZE. The block size cannot be changed after databa
se creation except by re-creating the database. If a database's block size i
s different from the
pt by re-creating the database. If a database's block size is different from
the
have 50 concurrent users, set this parameter to at least 55.
ROLLBACK_SEGMENTS
This parameter is a list of the rollback segments an Oracle instance acquire
s att database startup. List your rollback segments as the value of this par
ameter.
----------------------------------------------------------------------------
----
Attention:
After installation, you must create at least one rollback segment in the SYS
TEM tablespace in addition to the SYSTEM rollback segment before you can cre
ate any schema objects.
----------------------------------------------------------------------------
----
See Also: For more information about how many rollback segments you need, se
e Orracle8 Tuning.
License Parameters
Oracle helps you ensure that your site complies with its Oracle license agre
emennt. If your site is licensed by concurrent usage, you can track and limi
t the number of sessions concurrently connected to an instance. If your site
is licensed by named
users, you can limit the number of named users created in a database. To use
thiis facility, you need to know which type of licensing agreement your sit
e has and what the maximum number of sessions or named users is. Your site m
ight use either type of
licensing (session licensing or named user licensing), but not both.
See Also: For more information about managing licensing, see "Session and Us
er LLicensing".
LICENSE_MAX_SESSIONS_and LICENSE_SESSIONS WARNING
You can set a limit on the number of concurrent sessions that can connect to
a ddatabase on the specified computer. To set the maximum number of concurr
ent sessions for an instance, set the parameter LICENSE_MAX_SESSIONS in the
parameter file that
starts the instance, as shown in the following example:
LICENSE_MAX_SESSIONS = 80
In addition to setting a maximum number of sessions, you can set a warning l
imitt on the number of concurrent sessions. Once this limit is reached, addi
tional users can continue to connect (up to the maximum limit), but Oracle s
ends a warning for each
connecting user. To set the warning limit for an instance, set the parameter
LICCENSE_SESSIONS_WARNING. Set the warning limit to a value lower than LICE
NSE_MAX_SESSIONS.
For instances running with the Parallel Server, each instance can have its o
wn cconcurrent usage limit and warning limit. However, the sum of the instan
ces' limits must not exceed the site's session license.
See Also: For more information about setting these limits when using the Par
alleel Server, see Oracle8 Parallel Server Concepts and Administration.
LICENSE_MAX_USERS
You can set a limit on the number of users created in the database. Once thi
s liimit is reached, you cannot create more users.
----------------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
Note:
This mechanism assumes that each person accessing the database has a unique
userr name and that no people share a user name. Therefore, so that named us
er licensing can help you ensure compliance with your Oracle license agreeme
nt, 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_USER
S paarameter in the database's parameter file, as shown in the following exa
mple:
LICENSE_MAX_USERS = 200
For instances running with the Parallel Server, all instances connected to t
he ssame database should have the same named user limit.
See Also: For more information about setting this limit when using the Paral
lel Server see Oracle8 Parallel Server Concepts and Administration.
Considerations After Creating a Database
After you create a database, the instance is left running, and the database
is oopen and available for normal database use. Use Enterprise Manager to su
bsequently start and stop the database. If more than one database exists in
your database system,
specify the parameter file to use with any subsequent database startup.
If you plan to install other Oracle products to work with this database, see
thee installation instructions for those products; some products require yo
u to create additional data dictionary tables. See your operating system-spe
cific Oracle
documentation for the additional products. Usually, command files are provid
ed tto create and load these tables into the database's data dictionary.
The Oracle Server distribution media can include various SQL files that let
you experiment with the system, learn SQL, or create additional tables, view
s, or synonyms.
A newly created database has only two users, SYS and SYSTEM. The passwords f
or tthese two usernames should be changed soon after the database is created
.
See Also: For more information about the users SYS and SYSTEM see "Database
Admiinistrator Usernames".
For information about changing a user's password see "Altering Users".
Initial Tuning Guidelines
You can make a few significant tuning alterations to Oracle immediately foll
owinng installation. By following these instructions, you can reduce the nee
d to tune Oracle when it is running. This section gives recommendations for
the following
installation issues:
Allocating Rollback Segments
Choosing the Number of DB_BLOCK_LRU_LATCHES
Distributing I/O
Allocating Rollback Segments
Proper allocation of rollback segments makes for optimal database performanc
e. TThe size and number of rollback segments required for optimal performanc
e depends on your application. The Oracle8 Tuning manual contains some gener
al guidelines for
Proper allocation of rollback segments makes for optimal database performanc
e. TThe size and number of rollback segments required for optimal performanc
e depends on your application. The Oracle8 Tuning manual contains some gener
al guidelines for
choosing how many rollback segments to allocate based on the number of concu
rrennt transactions on your Oracle Server. These guidelines are appropriate
for most application mixes.
To create rollback segments, use the CREATE ROLLBACK SEGMENT command.
See Also: For information about the CREATE ROLLBACK SEGMENT command, see the
Oraacle8 SQL Reference.
Choosing Sizes for Rollback Segments
The size of your rollback segment can also affect performance. Rollback segm
ent size is determined by the storage parameters in the CREATE ROLLBACK SEGM
ENT statement. Your rollback segments must be large enough to hold the rollb
ack entries for your
transactions.
See Also: For information about choosing sizes for your rollback segments, s
ee OOracle8 Tuning.
Choosing the Number of DB_BLOCK_LRU_LATCHES
Contention for the LRU latch can impede performance on symmetric multiproces
sor (SMP) machines with a large number of CPUs. The LRU latch controls the r
eplacement of buffers in the buffer cache. For SMP systems, Oracle automatic
ally sets the number of
LRU latches to be one half the number of CPUs on the system. For non-SMP sys
temss, one LRU latch is sufficient.
You can specify the number of LRU latches on your system with the initializa
tiotter DB_BLOCK_LRU_LATCHES. This parameter sets the maximum value for the
desired number of LRU latches. Each LRU latch will control a set of buffers
and Oracle
balances allocation of replacement buffers among the sets.
See Also: For more information on LRU latches, see Oracle8 Tuning.
Distributing I/O
Proper distribution of I/O can improve database performance dramatically. I/
O caan be distributed during installation of Oracle. Distributing I/O during
installation can reduce the need to distribute I/O later when Oracle is run
ning.
There are several ways to distribute I/O when you install Oracle:
redo log file placement
datafile placement
separation of tables and indexes
density of data (rows per data block)
See Also: For information about ways to distribute I/O, see Oracle8 Tuning.
----------------------------------------------------------------------------
----
------------------
--
※ 来源:·BBS 水木清华站 bbs.net.tsinghua.edu.cn·[FROM: 162.105.41.141]
发信人: chendu (good), 信区: Database
标 题: Oracle DBA Guide V8, part 2
发信站: BBS 水木清华站 (Sat Nov 6 18:50:10 1999)
Hi, required by several friends, I am posting the
Oracle Database Administrator's Guide version 8
here. This is part 2.
Web Master, if my post it too long and not good for
the bbs, please delete it and I am sorry for it.
Hoping everyone enjoy it!
Good luck!
PS: please do not ask me question by email me
directly, this will cause some delay. (PPMM can ^_^)
----------------------------------------------------------------------------
----
2
Creating an Oracle Database
This chapter lists the steps necessary to create an Oracle database, and inc
ludees the following topics:
Considerations Before Creating a Database
Creating an Oracle Database
Parameters
Considerations After Creating a Database
Initial Tuning Guidelines
Considerations Before Creating a Database
This section includes the following topics:
Creation Prerequisites
Using an Initial Database
Migrating an Older Version of the Database
Database creation prepares several operating system files so they can work t
ogetther as an Oracle database. You need only create a database once, regard
less of how many datafiles it has or how many instances access it. Creating
a database can also erase
information in an existing database and create a new database with the same
namee and physical structure.
Creating a database includes the following operations:
creating new datafiles or erasing data that existed in previous datafiles
information creating structures that Oracle requires to access and use the d
atabbase (the data dictionary)
creating and initializing the control files and redo log files for the datab
ase
Consider the following issues before you create a database:
Plan your database tables and indexes, and estimate how much space they will
reqquire.
Plan how to protect your new database, including the configuration of its on
linee and archived redo log (and how much space it will require), and a back
up strategy.
Select the database character set. You must specify the database character s
et wwhen you create the database. After the database is created, you cannot
change the character set choices without re-creating the database. Hence, it
is important that you
carefully consider which character set(s) to use. All character data, includ
ing data in the data dictionary, is stored in the database character set. If
users access the database using a different character set, the database cha
racter set should be the
same as, or a superset of, all character sets they use.
Also become familiar with the principles and options of starting up and shut
tingg down an instance, mounting and opening a database, and using parameter
files.
See Also: "National Language Support" in the Oracle8 Reference.
For information about tables, indexes, and space management, see Chapters 9
throough 17.
For information about the online and archive redo logs, see Chapters 5 and 2
3 reespectively.
For information about database backup and recovery, see the Oracle8 Backup a
nd RRecovery Guide.
Creation Prerequisites
To create a new database, you must have the following:
the operating system privileges associated with a fully operational database
admministrator
sufficient memory to start the Oracle instance
sufficient disk storage space for the planned database on the computer that
execcutes Oracle
Using an Initial Database
Depending on your operating system, a database might have been created autom
aticcally as part of the installation procedure for Oracle. You can use this
initial database and customize it to meet your information management requi
rements, or discard it
and create one or more new databases to replace it.
Migrating an Older Version of the Database
If you are using a previous release of Oracle, database creation is required
onlly if you want an entirely new database. Otherwise, you can migrate your
existing Oracle databases managed by a previous version of Oracle and use t
hem with the new version
of the Oracle software.
See Also: Oracle8 Migration manual for information about migrating an existi
ng ddatabase.
For more information about migrating an existing database, see your operatin
g syystem-specific Oracle documentation.
Creating an Oracle Database
This section includes the following topics:
Steps for Creating an Oracle Database
Creating a Database: Example
Troubleshooting Database Creation
Dropping a Database
Steps for Creating an Oracle Database
These steps, which describe how to create an Oracle database, should be foll
owedd in the order presented.
To Create a New Database and Make It Available for System Use
Available for System Use
Back up any existing databases.
Create parameter files.
Edit new parameter files.
Check the instance identifier for your system.
Start Enterprise Manager and connect to Oracle as an administrator.
Start an instance.
Create the database.
Back up the database.
See Also: These steps provide general information about database creation on
alll operating systems. See your operating system-specific Oracle documenta
tion for information about creating databases on your platform.
Step 1: Back up any existing databases.
Oracle Corporation strongly recommends that you make complete backups of all
exiisting databases before creating a new database, in case database creati
on accidentally affects some existing files. Backup should include parameter
files, datafiles, redo
log files, and control files.
Step 2: Create parameter files.
The instance (System Global Area and background processes) for any Oracle da
tabaase is started using a parameter file.
Each database on your system should have at least one customized parameter f
ile that corresponds only to that database. Do not use the same file for sev
eral databases.
To create a parameter file for the database you are about to make, use your
operrating system to make a copy of the parameter file that Oracle provided
on the distribution media. Give this copy a new filename. You can then edit
and customize this new
file for the new database.
file for the new database.
See Also: For more information about copying the parameter file, see your op
eratting system-specific Oracle documentation.
----------------------------------------------------------------------------
----
Note:
In distributed processing environments, Enterprise Manager is often executed
froom a client machine of the network. If a client machine is being used to
execute Enterprise Manager and create a new database, you need to copy the
new parameter file
(currently located on the computer executing Oracle) to your client workstat
ion.. This procedure is operating system dependent. For more information abo
ut copying files among the computers of your network, see your operating sys
tem-specific Oracle
documentation.
----------------------------------------------------------------------------
----
Step 3: Edit new parameter files.
To create a new database, inspect and edit the following parameters of the n
ew pparameter file:
DB_NAME
DB_NAME
DB_DOMAIN
CONTROL_FILES
DB_BLOCK_SIZE
DB_BLOCK_BUFFERS
PROCESSES
ROLLBACK_SEGMENTS
You should also edit the appropriate license parameter(s):
LICENSE_MAX_SESSIONS
LICENSE_SESSION_WARNING
LICENSE_MAX_USERS
Step 4: Check the instance identifier for your system.
If you have other databases, check the Oracle instances identifier. The Orac
le iinstance identifier should match the name of the database (the value of
DB_NAME) to avoid confusion with other Oracle instances that are running con
currently on your system.
See your operating system-specific Oracle documentation for more information
.
Step 5: Start Enterprise Manager and connect to Oracle as an administrator.
Once Enterprise Manager is running, connect to the database as an administra
tor.
See Also: Starting Enterprise Manager is operating system specific; see your
opeerating system-specific Oracle documentation for details.
Step 6: Start an instance.
To start an instance (System Global Area and background processes) to be use
d wiith the new database, use the Startup Database dialog box of Enterprise
Manager. In the Startup Database dialog box, make sure that you have selecte
d the Startup Nomount
radio button.
After selecting the Startup Nomount, the instance starts. At this point, the
re iis no database. Only an SGA and background processes are started in prep
aration for the creation of a new database.
Step 7: Create the database.
To create the new database, use the SQL command CREATE DATABASE, optionally
settting parameters within the statement to name the database, establish max
imum numbers of files, name the files and set their sizes, and so on.
When you execute a CREATE DATABASE statement, Oracle performs the following
operrations:
creates the datafiles for the database
creates the control files for the database
creates the redo log files for the database
creates the SYSTEM tablespace and the SYSTEM rollback segment
creates the data dictionary
creates the users SYS and SYSTEM
specifies the character set that stores data in the database
mounts and opens the database for use
----------------------------------------------------------------------------
----
warning:
Make sure that the datafiles and redo log files that you specify do not conf
lictt with files of another database.
----------------------------------------------------------------------------
----
Step 8: Back up the database.
You should make a full backup of the database to ensure that you have a comp
letee set of files from which to recover if a media failure occurs.
See Also: Oracle8 Backup and Recovery Guide.
For more information about parameter files see "Using Parameter Files".
For information about the CREATE DATABASE command, character sets, and datab
ase creation see Oracle8 SQL Reference.
Creating a Database: Example
The following statement is an example of a CREATE DATABASE statement:
CREATE DATABASE test
DATAFILE 'test_system' SIZE 10M
LOGFILE GROUP 1 ('test_log1a', 'test_log1b') SIZE 500K,
GROUP 2 ('test_log2a', 'test_log2b') SIZE 500K;
The values of the MAXLOGFILES, MAXLOGMEMBERS, MAXDATAFILES, MAXLOGHISTORY, a
nd MMAXINSTANCES options in this example assume the default values, which ar
e operating system-dependent. The database is mounted in the default modes N
OARCHIVELOG and EXCLUSIVE
and then opened.
The items and information in the example statement above result in creating
a daatabase with the following characteristics:
The new database is named TEST.
The SYSTEM tablespace of the new database is comprised of one 10 MB datafile
nammed TEST_SYSTEM.
The new database has two online redo log groups, each containing two 500 KB
membbers.
The new database does not overwrite any existing control files specified in
the parameter file.
----------------------------------------------------------------------------
----
Note:
You can set several limits during database creation. Some of these limits ar
e allso subject to superseding limits of the operating system and can affect
each other. For example, if you set MAXDATAFILES, Oracle allocates enough s
pace in the control file
to store MAXDATAFILES filenames, even if the database has only one datafile
inittially; because the maximum control file size is limited and operating s
ystem-dependent, you might not be able to set all CREATE DATABASE parameters
at their theoretical
maximums.
----------------------------------------------------------------------------
----
See Also: For more information about setting limits during database creation
, seee the Oracle8 SQL Reference.
See your operating system-specific Oracle documentation for information abou
t opperating system limits.
Troubleshooting Database Creation
If for any reason database creation fails, shut down the instance and delete
anyy files created by the CREATE DATABASE statement before you attempt to c
reate it once again.
After correcting the error that caused the failure of the database creation,
retturn to Step 6 of "Creating an Oracle Database".
Dropping a Database
To drop a database, remove its datafiles, redo log files, and all other asso
ciatted files (control files, parameter files, archived log files).
To view the names of the database's datafiles and redo log files, query the
dataa dictionary views V$DATAFILE and V$LOGFILE.
See Also: For more information about these views, see the Oracle8 Reference.
Parameters
As described in Step 3 of the section "Creating an Oracle Database", Oracle
sugggests you alter a minimum set of parameters. These parameters are descri
bed in the following sections:
DB_NAME and DB_DOMAIN
CONTROL_FILES
DB_BLOCK_SIZE
PROCESSES
ROLLBACK_SEGMENTS
License Parameters
DB_BLOCK_BUFFERS
DB_BLOCK_BUFFERS
LICENSE_MAX_SESSIONS_and LICENSE_SESSIONS WARNING
LICENSE_MAX_USERS
DB_NAME and DB_DOMAIN
A database's global database name (name and location within a network struct
ure)) is created by setting both the DB_NAME and DB_DOMAIN parameters before
database creation. After creation, the database's name cannot be easily cha
nged. The DB_NAME
parameter determines the local name component of the database's name, while
the DB_DOMAIN parameter indicates the domain (logical location) within a net
work structure. The combination of the settings for these two parameters sho
uld form a database name
that is unique within a network. For example, to create a database with a gl
oball database name of TEST.US.ACME.COM, edit the parameters of the new para
meter file as follows:
DB_NAME = TEST
DB_DOMAIN = US.ACME.COM
DB_NAME must be set to a text string of no more than eight characters. Durin
g daatabase creation, the name provided for DB_NAME is recorded in the dataf
iles, redo log files, and control file of the database. If during database i
nstance startup the value
of the DB_NAME parameter (of the parameter file) and the database name in th
e coontrol file are not the same, the database does not start.
DB_DOMAIN is a text string that specifies the network domain where the datab
ase is created; 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 di
stributed database
system, pay special attention to this initialization parameter before databa
se ccreation.
See Also: For more information about distributed databases, see Oracle8 Dist
ribuuted Database Systems.
CONTROL_FILES
Include the CONTROL_FILES parameter in your new parameter file and set its v
aluee to a list of control filenames to use for the new database. If you wan
t Oracle to create new operating system files when creating your database's
control files, make sure
that the filenames listed in the CONTROL_FILES parameter do not match any fi
lenaames that currently exist on your system. If you want Oracle to reuse or
overwrite existing files when creating your database's control files, make
sure that the filenames
listed in the CONTROL_FILES parameter match the filenames that currently exi
st.
listed in the CONTROL_FILES parameter match the filenames that currently exi
st.
----------------------------------------------------------------------------
----
warning:
Use extreme caution when setting this option. If you inadvertently specify a
fille that you did not intend and execute the CREATE DATABASE statement, th
e previous contents of that file will be overwritten.
----------------------------------------------------------------------------
----
If no filenames are listed for the CONTROL_FILES parameter, Oracle uses a de
faullt filename.
Oracle Corporation strongly recommends you use at least two control files st
oredd on separate physical disk drives for each database. Therefore, when sp
ecifying the CONTROL_FILES parameter of the new parameter file, follow these
guidelines:
List at least two filenames for the CONTROL_FILES parameter.
Place each control file on a separate physical disk drives by fully specifyi
ng ddrives for each filename.
----------------------------------------------------------------------------
----
Note:
The file specification for control files is operating system-dependent. Rega
rdleess of your operating system, always fully specify filenames for your co
ntrol files.
----------------------------------------------------------------------------
----
When you execute the CREATE DATABASE statement (in Step 7), the control file
s liisted in the CONTROL_FILES parameter of the parameter file will be creat
ed.
See Also: The default filename for the CONTROL_FILES parameter is operating
systtem-dependent. See your operating system-specific Oracle documentation f
or details.
DB_BLOCK_SIZE
The default data block size for every Oracle server is operating system-spec
ificc. The Oracle data block size is typically either 2K or 4K. Generally, t
he default data block size is adequate. In some cases, however, a larger dat
a block size provides
greater efficiency in disk and memory I/O (access and storage of data). Such
casses include:
Oracle is on a large computer system with a large amount of memory and fast
diskk drives. For example, databases controlled by mainframe computers with
vast hardware resources typically use a data block size of 4K or greater.
The operating system that runs Oracle uses a small operating system block si
ze. For example, if the operating system block size is 1K and the data block
size matches this, Oracle may be performing an excessive amount of disk I/O
during normal
operation. For best performance in this case, a database block should consis
t off multiple operating system blocks.
Each database's block size is set during database creation by the initializa
tionn parameter DB_BLOCK_SIZE. The block size cannot be changed after databa
se creation except by re-creating the database. If a database's block size i
s different from the
pt by re-creating the database. If a database's block size is different from
the
have 50 concurrent users, set this parameter to at least 55.
ROLLBACK_SEGMENTS
This parameter is a list of the rollback segments an Oracle instance acquire
s att database startup. List your rollback segments as the value of this par
ameter.
----------------------------------------------------------------------------
----
Attention:
After installation, you must create at least one rollback segment in the SYS
TEM tablespace in addition to the SYSTEM rollback segment before you can cre
ate any schema objects.
----------------------------------------------------------------------------
----
See Also: For more information about how many rollback segments you need, se
e Orracle8 Tuning.
License Parameters
Oracle helps you ensure that your site complies with its Oracle license agre
emennt. If your site is licensed by concurrent usage, you can track and limi
t the number of sessions concurrently connected to an instance. If your site
is licensed by named
users, you can limit the number of named users created in a database. To use
thiis facility, you need to know which type of licensing agreement your sit
e has and what the maximum number of sessions or named users is. Your site m
ight use either type of
licensing (session licensing or named user licensing), but not both.
See Also: For more information about managing licensing, see "Session and Us
er LLicensing".
LICENSE_MAX_SESSIONS_and LICENSE_SESSIONS WARNING
You can set a limit on the number of concurrent sessions that can connect to
a ddatabase on the specified computer. To set the maximum number of concurr
ent sessions for an instance, set the parameter LICENSE_MAX_SESSIONS in the
parameter file that
starts the instance, as shown in the following example:
LICENSE_MAX_SESSIONS = 80
In addition to setting a maximum number of sessions, you can set a warning l
imitt on the number of concurrent sessions. Once this limit is reached, addi
tional users can continue to connect (up to the maximum limit), but Oracle s
ends a warning for each
connecting user. To set the warning limit for an instance, set the parameter
LICCENSE_SESSIONS_WARNING. Set the warning limit to a value lower than LICE
NSE_MAX_SESSIONS.
For instances running with the Parallel Server, each instance can have its o
wn cconcurrent usage limit and warning limit. However, the sum of the instan
ces' limits must not exceed the site's session license.
See Also: For more information about setting these limits when using the Par
alleel Server, see Oracle8 Parallel Server Concepts and Administration.
LICENSE_MAX_USERS
You can set a limit on the number of users created in the database. Once thi
s liimit is reached, you cannot create more users.
----------------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
Note:
This mechanism assumes that each person accessing the database has a unique
userr name and that no people share a user name. Therefore, so that named us
er licensing can help you ensure compliance with your Oracle license agreeme
nt, 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_USER
S paarameter in the database's parameter file, as shown in the following exa
mple:
LICENSE_MAX_USERS = 200
For instances running with the Parallel Server, all instances connected to t
he ssame database should have the same named user limit.
See Also: For more information about setting this limit when using the Paral
lel Server see Oracle8 Parallel Server Concepts and Administration.
Considerations After Creating a Database
After you create a database, the instance is left running, and the database
is oopen and available for normal database use. Use Enterprise Manager to su
bsequently start and stop the database. If more than one database exists in
your database system,
specify the parameter file to use with any subsequent database startup.
If you plan to install other Oracle products to work with this database, see
thee installation instructions for those products; some products require yo
u to create additional data dictionary tables. See your operating system-spe
cific Oracle
documentation for the additional products. Usually, command files are provid
ed tto create and load these tables into the database's data dictionary.
The Oracle Server distribution media can include various SQL files that let
you experiment with the system, learn SQL, or create additional tables, view
s, or synonyms.
A newly created database has only two users, SYS and SYSTEM. The passwords f
or tthese two usernames should be changed soon after the database is created
.
See Also: For more information about the users SYS and SYSTEM see "Database
Admiinistrator Usernames".
For information about changing a user's password see "Altering Users".
Initial Tuning Guidelines
You can make a few significant tuning alterations to Oracle immediately foll
owinng installation. By following these instructions, you can reduce the nee
d to tune Oracle when it is running. This section gives recommendations for
the following
installation issues:
Allocating Rollback Segments
Choosing the Number of DB_BLOCK_LRU_LATCHES
Distributing I/O
Allocating Rollback Segments
Proper allocation of rollback segments makes for optimal database performanc
e. TThe size and number of rollback segments required for optimal performanc
e depends on your application. The Oracle8 Tuning manual contains some gener
al guidelines for
Proper allocation of rollback segments makes for optimal database performanc
e. TThe size and number of rollback segments required for optimal performanc
e depends on your application. The Oracle8 Tuning manual contains some gener
al guidelines for
choosing how many rollback segments to allocate based on the number of concu
rrennt transactions on your Oracle Server. These guidelines are appropriate
for most application mixes.
To create rollback segments, use the CREATE ROLLBACK SEGMENT command.
See Also: For information about the CREATE ROLLBACK SEGMENT command, see the
Oraacle8 SQL Reference.
Choosing Sizes for Rollback Segments
The size of your rollback segment can also affect performance. Rollback segm
ent size is determined by the storage parameters in the CREATE ROLLBACK SEGM
ENT statement. Your rollback segments must be large enough to hold the rollb
ack entries for your
transactions.
See Also: For information about choosing sizes for your rollback segments, s
ee OOracle8 Tuning.
Choosing the Number of DB_BLOCK_LRU_LATCHES
Contention for the LRU latch can impede performance on symmetric multiproces
sor (SMP) machines with a large number of CPUs. The LRU latch controls the r
eplacement of buffers in the buffer cache. For SMP systems, Oracle automatic
ally sets the number of
LRU latches to be one half the number of CPUs on the system. For non-SMP sys
temss, one LRU latch is sufficient.
You can specify the number of LRU latches on your system with the initializa
tiotter DB_BLOCK_LRU_LATCHES. This parameter sets the maximum value for the
desired number of LRU latches. Each LRU latch will control a set of buffers
and Oracle
balances allocation of replacement buffers among the sets.
See Also: For more information on LRU latches, see Oracle8 Tuning.
Distributing I/O
Proper distribution of I/O can improve database performance dramatically. I/
O caan be distributed during installation of Oracle. Distributing I/O during
installation can reduce the need to distribute I/O later when Oracle is run
ning.
There are several ways to distribute I/O when you install Oracle:
redo log file placement
datafile placement
separation of tables and indexes
density of data (rows per data block)
See Also: For information about ways to distribute I/O, see Oracle8 Tuning.
----------------------------------------------------------------------------
----
------------------
--
※ 来源:·BBS 水木清华站 bbs.net.tsinghua.edu.cn·[FROM: 162.105.41.141]
发信人: chendu (good), 信区: Database
标 题: Oracle DBA Guide V8, part 2
发信站: BBS 水木清华站 (Sat Nov 6 18:50:10 1999)
Hi, required by several friends, I am posting the
Oracle Database Administrator's Guide version 8
here. This is part 2.
Web Master, if my post it too long and not good for
the bbs, please delete it and I am sorry for it.
Hoping everyone enjoy it!
Good luck!
PS: please do not ask me question by email me
directly, this will cause some delay. (PPMM can ^_^)
----------------------------------------------------------------------------
----
2
Creating an Oracle Database
This chapter lists the steps necessary to create an Oracle database, and inc
ludees the following topics:
Considerations Before Creating a Database
Creating an Oracle Database
Parameters
Considerations After Creating a Database
Initial Tuning Guidelines
Considerations Before Creating a Database
This section includes the following topics:
Creation Prerequisites
Using an Initial Database
Migrating an Older Version of the Database
Database creation prepares several operating system files so they can work t
ogetther as an Oracle database. You need only create a database once, regard
less of how many datafiles it has or how many instances access it. Creating
a database can also erase
information in an existing database and create a new database with the same
namee and physical structure.
Creating a database includes the following operations:
creating new datafiles or erasing data that existed in previous datafiles
information creating structures that Oracle requires to access and use the d
atabbase (the data dictionary)
creating and initializing the control files and redo log files for the datab
ase
Consider the following issues before you create a database:
Plan your database tables and indexes, and estimate how much space they will
reqquire.
Plan how to protect your new database, including the configuration of its on
linee and archived redo log (and how much space it will require), and a back
up strategy.
Select the database character set. You must specify the database character s
et wwhen you create the database. After the database is created, you cannot
change the character set choices without re-creating the database. Hence, it
is important that you
carefully consider which character set(s) to use. All character data, includ
ing data in the data dictionary, is stored in the database character set. If
users access the database using a different character set, the database cha
racter set should be the
same as, or a superset of, all character sets they use.
Also become familiar with the principles and options of starting up and shut
tingg down an instance, mounting and opening a database, and using parameter
files.
See Also: "National Language Support" in the Oracle8 Reference.
For information about tables, indexes, and space management, see Chapters 9
throough 17.
For information about the online and archive redo logs, see Chapters 5 and 2
3 reespectively.
For information about database backup and recovery, see the Oracle8 Backup a
nd RRecovery Guide.
Creation Prerequisites
To create a new database, you must have the following:
the operating system privileges associated with a fully operational database
admministrator
sufficient memory to start the Oracle instance
sufficient disk storage space for the planned database on the computer that
execcutes Oracle
Using an Initial Database
Depending on your operating system, a database might have been created autom
aticcally as part of the installation procedure for Oracle. You can use this
initial database and customize it to meet your information management requi
rements, or discard it
and create one or more new databases to replace it.
Migrating an Older Version of the Database
If you are using a previous release of Oracle, database creation is required
onlly if you want an entirely new database. Otherwise, you can migrate your
existing Oracle databases managed by a previous version of Oracle and use t
hem with the new version
of the Oracle software.
See Also: Oracle8 Migration manual for information about migrating an existi
ng ddatabase.
For more information about migrating an existing database, see your operatin
g syystem-specific Oracle documentation.
Creating an Oracle Database
This section includes the following topics:
Steps for Creating an Oracle Database
Creating a Database: Example
Troubleshooting Database Creation
Dropping a Database
Steps for Creating an Oracle Database
These steps, which describe how to create an Oracle database, should be foll
owedd in the order presented.
To Create a New Database and Make It Available for System Use
Available for System Use
Back up any existing databases.
Create parameter files.
Edit new parameter files.
Check the instance identifier for your system.
Start Enterprise Manager and connect to Oracle as an administrator.
Start an instance.
Create the database.
Back up the database.
See Also: These steps provide general information about database creation on
alll operating systems. See your operating system-specific Oracle documenta
tion for information about creating databases on your platform.
Step 1: Back up any existing databases.
Oracle Corporation strongly recommends that you make complete backups of all
exiisting databases before creating a new database, in case database creati
on accidentally affects some existing files. Backup should include parameter
files, datafiles, redo
log files, and control files.
Step 2: Create parameter files.
The instance (System Global Area and background processes) for any Oracle da
tabaase is started using a parameter file.
Each database on your system should have at least one customized parameter f
ile that corresponds only to that database. Do not use the same file for sev
eral databases.
To create a parameter file for the database you are about to make, use your
operrating system to make a copy of the parameter file that Oracle provided
on the distribution media. Give this copy a new filename. You can then edit
and customize this new
file for the new database.
file for the new database.
See Also: For more information about copying the parameter file, see your op
eratting system-specific Oracle documentation.
----------------------------------------------------------------------------
----
Note:
In distributed processing environments, Enterprise Manager is often executed
froom a client machine of the network. If a client machine is being used to
execute Enterprise Manager and create a new database, you need to copy the
new parameter file
(currently located on the computer executing Oracle) to your client workstat
ion.. This procedure is operating system dependent. For more information abo
ut copying files among the computers of your network, see your operating sys
tem-specific Oracle
documentation.
----------------------------------------------------------------------------
----
Step 3: Edit new parameter files.
To create a new database, inspect and edit the following parameters of the n
ew pparameter file:
DB_NAME
DB_NAME
DB_DOMAIN
CONTROL_FILES
DB_BLOCK_SIZE
DB_BLOCK_BUFFERS
PROCESSES
ROLLBACK_SEGMENTS
You should also edit the appropriate license parameter(s):
LICENSE_MAX_SESSIONS
LICENSE_SESSION_WARNING
LICENSE_MAX_USERS
Step 4: Check the instance identifier for your system.
If you have other databases, check the Oracle instances identifier. The Orac
le iinstance identifier should match the name of the database (the value of
DB_NAME) to avoid confusion with other Oracle instances that are running con
currently on your system.
See your operating system-specific Oracle documentation for more information
.
Step 5: Start Enterprise Manager and connect to Oracle as an administrator.
Once Enterprise Manager is running, connect to the database as an administra
tor.
See Also: Starting Enterprise Manager is operating system specific; see your
opeerating system-specific Oracle documentation for details.
Step 6: Start an instance.
To start an instance (System Global Area and background processes) to be use
d wiith the new database, use the Startup Database dialog box of Enterprise
Manager. In the Startup Database dialog box, make sure that you have selecte
d the Startup Nomount
radio button.
After selecting the Startup Nomount, the instance starts. At this point, the
re iis no database. Only an SGA and background processes are started in prep
aration for the creation of a new database.
Step 7: Create the database.
To create the new database, use the SQL command CREATE DATABASE, optionally
settting parameters within the statement to name the database, establish max
imum numbers of files, name the files and set their sizes, and so on.
When you execute a CREATE DATABASE statement, Oracle performs the following
operrations:
creates the datafiles for the database
creates the control files for the database
creates the redo log files for the database
creates the SYSTEM tablespace and the SYSTEM rollback segment
creates the data dictionary
creates the users SYS and SYSTEM
specifies the character set that stores data in the database
mounts and opens the database for use
----------------------------------------------------------------------------
----
warning:
Make sure that the datafiles and redo log files that you specify do not conf
lictt with files of another database.
----------------------------------------------------------------------------
----
Step 8: Back up the database.
You should make a full backup of the database to ensure that you have a comp
letee set of files from which to recover if a media failure occurs.
See Also: Oracle8 Backup and Recovery Guide.
For more information about parameter files see "Using Parameter Files".
For information about the CREATE DATABASE command, character sets, and datab
ase creation see Oracle8 SQL Reference.
Creating a Database: Example
The following statement is an example of a CREATE DATABASE statement:
CREATE DATABASE test
DATAFILE 'test_system' SIZE 10M
LOGFILE GROUP 1 ('test_log1a', 'test_log1b') SIZE 500K,
GROUP 2 ('test_log2a', 'test_log2b') SIZE 500K;
The values of the MAXLOGFILES, MAXLOGMEMBERS, MAXDATAFILES, MAXLOGHISTORY, a
nd MMAXINSTANCES options in this example assume the default values, which ar
e operating system-dependent. The database is mounted in the default modes N
OARCHIVELOG and EXCLUSIVE
and then opened.
The items and information in the example statement above result in creating
a daatabase with the following characteristics:
The new database is named TEST.
The SYSTEM tablespace of the new database is comprised of one 10 MB datafile
nammed TEST_SYSTEM.
The new database has two online redo log groups, each containing two 500 KB
membbers.
The new database does not overwrite any existing control files specified in
the parameter file.
----------------------------------------------------------------------------
----
Note:
You can set several limits during database creation. Some of these limits ar
e allso subject to superseding limits of the operating system and can affect
each other. For example, if you set MAXDATAFILES, Oracle allocates enough s
pace in the control file
to store MAXDATAFILES filenames, even if the database has only one datafile
inittially; because the maximum control file size is limited and operating s
ystem-dependent, you might not be able to set all CREATE DATABASE parameters
at their theoretical
maximums.
----------------------------------------------------------------------------
----
See Also: For more information about setting limits during database creation
, seee the Oracle8 SQL Reference.
See your operating system-specific Oracle documentation for information abou
t opperating system limits.
Troubleshooting Database Creation
If for any reason database creation fails, shut down the instance and delete
anyy files created by the CREATE DATABASE statement before you attempt to c
reate it once again.
After correcting the error that caused the failure of the database creation,
retturn to Step 6 of "Creating an Oracle Database".
Dropping a Database
To drop a database, remove its datafiles, redo log files, and all other asso
ciatted files (control files, parameter files, archived log files).
To view the names of the database's datafiles and redo log files, query the
dataa dictionary views V$DATAFILE and V$LOGFILE.
See Also: For more information about these views, see the Oracle8 Reference.
Parameters
As described in Step 3 of the section "Creating an Oracle Database", Oracle
sugggests you alter a minimum set of parameters. These parameters are descri
bed in the following sections:
DB_NAME and DB_DOMAIN
CONTROL_FILES
DB_BLOCK_SIZE
PROCESSES
ROLLBACK_SEGMENTS
License Parameters
DB_BLOCK_BUFFERS
DB_BLOCK_BUFFERS
LICENSE_MAX_SESSIONS_and LICENSE_SESSIONS WARNING
LICENSE_MAX_USERS
DB_NAME and DB_DOMAIN
A database's global database name (name and location within a network struct
ure)) is created by setting both the DB_NAME and DB_DOMAIN parameters before
database creation. After creation, the database's name cannot be easily cha
nged. The DB_NAME
parameter determines the local name component of the database's name, while
the DB_DOMAIN parameter indicates the domain (logical location) within a net
work structure. The combination of the settings for these two parameters sho
uld form a database name
that is unique within a network. For example, to create a database with a gl
oball database name of TEST.US.ACME.COM, edit the parameters of the new para
meter file as follows:
DB_NAME = TEST
DB_DOMAIN = US.ACME.COM
DB_NAME must be set to a text string of no more than eight characters. Durin
g daatabase creation, the name provided for DB_NAME is recorded in the dataf
iles, redo log files, and control file of the database. If during database i
nstance startup the value
of the DB_NAME parameter (of the parameter file) and the database name in th
e coontrol file are not the same, the database does not start.
DB_DOMAIN is a text string that specifies the network domain where the datab
ase is created; 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 di
stributed database
system, pay special attention to this initialization parameter before databa
se ccreation.
See Also: For more information about distributed databases, see Oracle8 Dist
ribuuted Database Systems.
CONTROL_FILES
Include the CONTROL_FILES parameter in your new parameter file and set its v
aluee to a list of control filenames to use for the new database. If you wan
t Oracle to create new operating system files when creating your database's
control files, make sure
that the filenames listed in the CONTROL_FILES parameter do not match any fi
lenaames that currently exist on your system. If you want Oracle to reuse or
overwrite existing files when creating your database's control files, make
sure that the filenames
listed in the CONTROL_FILES parameter match the filenames that currently exi
st.
listed in the CONTROL_FILES parameter match the filenames that currently exi
st.
----------------------------------------------------------------------------
----
warning:
Use extreme caution when setting this option. If you inadvertently specify a
fille that you did not intend and execute the CREATE DATABASE statement, th
e previous contents of that file will be overwritten.
----------------------------------------------------------------------------
----
If no filenames are listed for the CONTROL_FILES parameter, Oracle uses a de
faullt filename.
Oracle Corporation strongly recommends you use at least two control files st
oredd on separate physical disk drives for each database. Therefore, when sp
ecifying the CONTROL_FILES parameter of the new parameter file, follow these
guidelines:
List at least two filenames for the CONTROL_FILES parameter.
Place each control file on a separate physical disk drives by fully specifyi
ng ddrives for each filename.
----------------------------------------------------------------------------
----
Note:
The file specification for control files is operating system-dependent. Rega
rdleess of your operating system, always fully specify filenames for your co
ntrol files.
----------------------------------------------------------------------------
----
When you execute the CREATE DATABASE statement (in Step 7), the control file
s liisted in the CONTROL_FILES parameter of the parameter file will be creat
ed.
See Also: The default filename for the CONTROL_FILES parameter is operating
systtem-dependent. See your operating system-specific Oracle documentation f
or details.
DB_BLOCK_SIZE
The default data block size for every Oracle server is operating system-spec
ificc. The Oracle data block size is typically either 2K or 4K. Generally, t
he default data block size is adequate. In some cases, however, a larger dat
a block size provides
greater efficiency in disk and memory I/O (access and storage of data). Such
casses include:
Oracle is on a large computer system with a large amount of memory and fast
diskk drives. For example, databases controlled by mainframe computers with
vast hardware resources typically use a data block size of 4K or greater.
The operating system that runs Oracle uses a small operating system block si
ze. For example, if the operating system block size is 1K and the data block
size matches this, Oracle may be performing an excessive amount of disk I/O
during normal
operation. For best performance in this case, a database block should consis
t off multiple operating system blocks.
Each database's block size is set during database creation by the initializa
tionn parameter DB_BLOCK_SIZE. The block size cannot be changed after databa
se creation except by re-creating the database. If a database's block size i
s different from the
pt by re-creating the database. If a database's block size is different from
the
have 50 concurrent users, set this parameter to at least 55.
ROLLBACK_SEGMENTS
This parameter is a list of the rollback segments an Oracle instance acquire
s att database startup. List your rollback segments as the value of this par
ameter.
----------------------------------------------------------------------------
----
Attention:
After installation, you must create at least one rollback segment in the SYS
TEM tablespace in addition to the SYSTEM rollback segment before you can cre
ate any schema objects.
----------------------------------------------------------------------------
----
See Also: For more information about how many rollback segments you need, se
e Orracle8 Tuning.
License Parameters
Oracle helps you ensure that your site complies with its Oracle license agre
emennt. If your site is licensed by concurrent usage, you can track and limi
t the number of sessions concurrently connected to an instance. If your site
is licensed by named
users, you can limit the number of named users created in a database. To use
thiis facility, you need to know which type of licensing agreement your sit
e has and what the maximum number of sessions or named users is. Your site m
ight use either type of
licensing (session licensing or named user licensing), but not both.
See Also: For more information about managing licensing, see "Session and Us
er LLicensing".
LICENSE_MAX_SESSIONS_and LICENSE_SESSIONS WARNING
You can set a limit on the number of concurrent sessions that can connect to
a ddatabase on the specified computer. To set the maximum number of concurr
ent sessions for an instance, set the parameter LICENSE_MAX_SESSIONS in the
parameter file that
starts the instance, as shown in the following example:
LICENSE_MAX_SESSIONS = 80
In addition to setting a maximum number of sessions, you can set a warning l
imitt on the number of concurrent sessions. Once this limit is reached, addi
tional users can continue to connect (up to the maximum limit), but Oracle s
ends a warning for each
connecting user. To set the warning limit for an instance, set the parameter
LICCENSE_SESSIONS_WARNING. Set the warning limit to a value lower than LICE
NSE_MAX_SESSIONS.
For instances running with the Parallel Server, each instance can have its o
wn cconcurrent usage limit and warning limit. However, the sum of the instan
ces' limits must not exceed the site's session license.
See Also: For more information about setting these limits when using the Par
alleel Server, see Oracle8 Parallel Server Concepts and Administration.
LICENSE_MAX_USERS
You can set a limit on the number of users created in the database. Once thi
s liimit is reached, you cannot create more users.
----------------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
Note:
This mechanism assumes that each person accessing the database has a unique
userr name and that no people share a user name. Therefore, so that named us
er licensing can help you ensure compliance with your Oracle license agreeme
nt, 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_USER
S paarameter in the database's parameter file, as shown in the following exa
mple:
LICENSE_MAX_USERS = 200
For instances running with the Parallel Server, all instances connected to t
he ssame database should have the same named user limit.
See Also: For more information about setting this limit when using the Paral
lel Server see Oracle8 Parallel Server Concepts and Administration.
Considerations After Creating a Database
After you create a database, the instance is left running, and the database
is oopen and available for normal database use. Use Enterprise Manager to su
bsequently start and stop the database. If more than one database exists in
your database system,
specify the parameter file to use with any subsequent database startup.
If you plan to install other Oracle products to work with this database, see
thee installation instructions for those products; some products require yo
u to create additional data dictionary tables. See your operating system-spe
cific Oracle
documentation for the additional products. Usually, command files are provid
ed tto create and load these tables into the database's data dictionary.
The Oracle Server distribution media can include various SQL files that let
you experiment with the system, learn SQL, or create additional tables, view
s, or synonyms.
A newly created database has only two users, SYS and SYSTEM. The passwords f
or tthese two usernames should be changed soon after the database is created
.
See Also: For more information about the users SYS and SYSTEM see "Database
Admiinistrator Usernames".
For information about changing a user's password see "Altering Users".
Initial Tuning Guidelines
You can make a few significant tuning alterations to Oracle immediately foll
owinng installation. By following these instructions, you can reduce the nee
d to tune Oracle when it is running. This section gives recommendations for
the following
installation issues:
Allocating Rollback Segments
Choosing the Number of DB_BLOCK_LRU_LATCHES
Distributing I/O
Allocating Rollback Segments
Proper allocation of rollback segments makes for optimal database performanc
e. TThe size and number of rollback segments required for optimal performanc
e depends on your application. The Oracle8 Tuning manual contains some gener
al guidelines for
Proper allocation of rollback segments makes for optimal database performanc
e. TThe size and number of rollback segments required for optimal performanc
e depends on your application. The Oracle8 Tuning manual contains some gener
al guidelines for
choosing how many rollback segments to allocate based on the number of concu
rrennt transactions on your Oracle Server. These guidelines are appropriate
for most application mixes.
To create rollback segments, use the CREATE ROLLBACK SEGMENT command.
See Also: For information about the CREATE ROLLBACK SEGMENT command, see the
Oraacle8 SQL Reference.
Choosing Sizes for Rollback Segments
The size of your rollback segment can also affect performance. Rollback segm
ent size is determined by the storage parameters in the CREATE ROLLBACK SEGM
ENT statement. Your rollback segments must be large enough to hold the rollb
ack entries for your
transactions.
See Also: For information about choosing sizes for your rollback segments, s
ee OOracle8 Tuning.
Choosing the Number of DB_BLOCK_LRU_LATCHES
Contention for the LRU latch can impede performance on symmetric multiproces
sor (SMP) machines with a large number of CPUs. The LRU latch controls the r
eplacement of buffers in the buffer cache. For SMP systems, Oracle automatic
ally sets the number of
LRU latches to be one half the number of CPUs on the system. For non-SMP sys
temss, one LRU latch is sufficient.
You can specify the number of LRU latches on your system with the initializa
tiotter DB_BLOCK_LRU_LATCHES. This parameter sets the maximum value for the
desired number of LRU latches. Each LRU latch will control a set of buffers
and Oracle
balances allocation of replacement buffers among the sets.
See Also: For more information on LRU latches, see Oracle8 Tuning.
Distributing I/O
Proper distribution of I/O can improve database performance dramatically. I/
O caan be distributed during installation of Oracle. Distributing I/O during
installation can reduce the need to distribute I/O later when Oracle is run
ning.
There are several ways to distribute I/O when you install Oracle:
redo log file placement
datafile placement
separation of tables and indexes
density of data (rows per data block)
See Also: For information about ways to distribute I/O, see Oracle8 Tuning.
----------------------------------------------------------------------------
----
------------------
--
※ 来源:·BBS 水木清华站 bbs.net.tsinghua.edu.cn·[FROM: 162.105.41.141]
发信人: chendu (good), 信区: Database
标 题: Oracle DBA Guide V8, part 2
发信站: BBS 水木清华站 (Sat Nov 6 18:50:10 1999)
Hi, required by several friends, I am posting the
Oracle Database Administrator's Guide version 8
here. This is part 2.
Web Master, if my post it too long and not good for
the bbs, please delete it and I am sorry for it.
Hoping everyone enjoy it!
Good luck!
PS: please do not ask me question by email me
directly, this will cause some delay. (PPMM can ^_^)
----------------------------------------------------------------------------
----
2
Creating an Oracle Database
This chapter lists the steps necessary to create an Oracle database, and inc
ludees the following topics:
Considerations Before Creating a Database
Creating an Oracle Database
Parameters
Considerations After Creating a Database
Initial Tuning Guidelines
Considerations Before Creating a Database
This section includes the following topics:
Creation Prerequisites
Using an Initial Database
Migrating an Older Version of the Database
Database creation prepares several operating system files so they can work t
ogetther as an Oracle database. You need only create a database once, regard
less of how many datafiles it has or how many instances access it. Creating
a database can also erase
information in an existing database and create a new database with the same
namee and physical structure.
Creating a database includes the following operations:
creating new datafiles or erasing data that existed in previous datafiles
information creating structures that Oracle requires to access and use the d
atabbase (the data dictionary)
creating and initializing the control files and redo log files for the datab
ase
Consider the following issues before you create a database:
Plan your database tables and indexes, and estimate how much space they will
reqquire.
Plan how to protect your new database, including the configuration of its on
linee and archived redo log (and how much space it will require), and a back
up strategy.
Select the database character set. You must specify the database character s
et wwhen you create the database. After the database is created, you cannot
change the character set choices without re-creating the database. Hence, it
is important that you
carefully consider which character set(s) to use. All character data, includ
ing data in the data dictionary, is stored in the database character set. If
users access the database using a different character set, the database cha
racter set should be the
same as, or a superset of, all character sets they use.
Also become familiar with the principles and options of starting up and shut
tingg down an instance, mounting and opening a database, and using parameter
files.
See Also: "National Language Support" in the Oracle8 Reference.
For information about tables, indexes, and space management, see Chapters 9
throough 17.
For information about the online and archive redo logs, see Chapters 5 and 2
3 reespectively.
For information about database backup and recovery, see the Oracle8 Backup a
nd RRecovery Guide.
Creation Prerequisites
To create a new database, you must have the following:
the operating system privileges associated with a fully operational database
admministrator
sufficient memory to start the Oracle instance
sufficient disk storage space for the planned database on the computer that
execcutes Oracle
Using an Initial Database
Depending on your operating system, a database might have been created autom
aticcally as part of the installation procedure for Oracle. You can use this
initial database and customize it to meet your information management requi
rements, or discard it
and create one or more new databases to replace it.
Migrating an Older Version of the Database
If you are using a previous release of Oracle, database creation is required
onlly if you want an entirely new database. Otherwise, you can migrate your
existing Oracle databases managed by a previous version of Oracle and use t
hem with the new version
of the Oracle software.
See Also: Oracle8 Migration manual for information about migrating an existi
ng ddatabase.
For more information about migrating an existing database, see your operatin
g syystem-specific Oracle documentation.
Creating an Oracle Database
This section includes the following topics:
Steps for Creating an Oracle Database
Creating a Database: Example
Troubleshooting Database Creation
Dropping a Database
Steps for Creating an Oracle Database
These steps, which describe how to create an Oracle database, should be foll
owedd in the order presented.
To Create a New Database and Make It Available for System Use
Available for System Use
Back up any existing databases.
Create parameter files.
Edit new parameter files.
Check the instance identifier for your system.
Start Enterprise Manager and connect to Oracle as an administrator.
Start an instance.
Create the database.
Back up the database.
See Also: These steps provide general information about database creation on
alll operating systems. See your operating system-specific Oracle documenta
tion for information about creating databases on your platform.
Step 1: Back up any existing databases.
Oracle Corporation strongly recommends that you make complete backups of all
exiisting databases before creating a new database, in case database creati
on accidentally affects some existing files. Backup should include parameter
files, datafiles, redo
log files, and control files.
Step 2: Create parameter files.
The instance (System Global Area and background processes) for any Oracle da
tabaase is started using a parameter file.
Each database on your system should have at least one customized parameter f
ile that corresponds only to that database. Do not use the same file for sev
eral databases.
To create a parameter file for the database you are about to make, use your
operrating system to make a copy of the parameter file that Oracle provided
on the distribution media. Give this copy a new filename. You can then edit
and customize this new
file for the new database.
file for the new database.
See Also: For more information about copying the parameter file, see your op
eratting system-specific Oracle documentation.
----------------------------------------------------------------------------
----
Note:
In distributed processing environments, Enterprise Manager is often executed
froom a client machine of the network. If a client machine is being used to
execute Enterprise Manager and create a new database, you need to copy the
new parameter file
(currently located on the computer executing Oracle) to your client workstat
ion.. This procedure is operating system dependent. For more information abo
ut copying files among the computers of your network, see your operating sys
tem-specific Oracle
documentation.
----------------------------------------------------------------------------
----
Step 3: Edit new parameter files.
To create a new database, inspect and edit the following parameters of the n
ew pparameter file:
DB_NAME
DB_NAME
DB_DOMAIN
CONTROL_FILES
DB_BLOCK_SIZE
DB_BLOCK_BUFFERS
PROCESSES
ROLLBACK_SEGMENTS
You should also edit the appropriate license parameter(s):
LICENSE_MAX_SESSIONS
LICENSE_SESSION_WARNING
LICENSE_MAX_USERS
Step 4: Check the instance identifier for your system.
If you have other databases, check the Oracle instances identifier. The Orac
le iinstance identifier should match the name of the database (the value of
DB_NAME) to avoid confusion with other Oracle instances that are running con
currently on your system.
See your operating system-specific Oracle documentation for more information
.
Step 5: Start Enterprise Manager and connect to Oracle as an administrator.
Once Enterprise Manager is running, connect to the database as an administra
tor.
See Also: Starting Enterprise Manager is operating system specific; see your
opeerating system-specific Oracle documentation for details.
Step 6: Start an instance.
To start an instance (System Global Area and background processes) to be use
d wiith the new database, use the Startup Database dialog box of Enterprise
Manager. In the Startup Database dialog box, make sure that you have selecte
d the Startup Nomount
radio button.
After selecting the Startup Nomount, the instance starts. At this point, the
re iis no database. Only an SGA and background processes are started in prep
aration for the creation of a new database.
Step 7: Create the database.
To create the new database, use the SQL command CREATE DATABASE, optionally
settting parameters within the statement to name the database, establish max
imum numbers of files, name the files and set their sizes, and so on.
When you execute a CREATE DATABASE statement, Oracle performs the following
operrations:
creates the datafiles for the database
creates the control files for the database
creates the redo log files for the database
creates the SYSTEM tablespace and the SYSTEM rollback segment
creates the data dictionary
creates the users SYS and SYSTEM
specifies the character set that stores data in the database
mounts and opens the database for use
----------------------------------------------------------------------------
----
warning:
Make sure that the datafiles and redo log files that you specify do not conf
lictt with files of another database.
----------------------------------------------------------------------------
----
Step 8: Back up the database.
You should make a full backup of the database to ensure that you have a comp
letee set of files from which to recover if a media failure occurs.
See Also: Oracle8 Backup and Recovery Guide.
For more information about parameter files see "Using Parameter Files".
For information about the CREATE DATABASE command, character sets, and datab
ase creation see Oracle8 SQL Reference.
Creating a Database: Example
The following statement is an example of a CREATE DATABASE statement:
CREATE DATABASE test
DATAFILE 'test_system' SIZE 10M
LOGFILE GROUP 1 ('test_log1a', 'test_log1b') SIZE 500K,
GROUP 2 ('test_log2a', 'test_log2b') SIZE 500K;
The values of the MAXLOGFILES, MAXLOGMEMBERS, MAXDATAFILES, MAXLOGHISTORY, a
nd MMAXINSTANCES options in this example assume the default values, which ar
e operating system-dependent. The database is mounted in the default modes N
OARCHIVELOG and EXCLUSIVE
and then opened.
The items and information in the example statement above result in creating
a daatabase with the following characteristics:
The new database is named TEST.
The SYSTEM tablespace of the new database is comprised of one 10 MB datafile
nammed TEST_SYSTEM.
The new database has two online redo log groups, each containing two 500 KB
membbers.
The new database does not overwrite any existing control files specified in
the parameter file.
----------------------------------------------------------------------------
----
Note:
You can set several limits during database creation. Some of these limits ar
e allso subject to superseding limits of the operating system and can affect
each other. For example, if you set MAXDATAFILES, Oracle allocates enough s
pace in the control file
to store MAXDATAFILES filenames, even if the database has only one datafile
inittially; because the maximum control file size is limited and operating s
ystem-dependent, you might not be able to set all CREATE DATABASE parameters
at their theoretical
maximums.
----------------------------------------------------------------------------
----
See Also: For more information about setting limits during database creation
, seee the Oracle8 SQL Reference.
See your operating system-specific Oracle documentation for information abou
t opperating system limits.
Troubleshooting Database Creation
If for any reason database creation fails, shut down the instance and delete
anyy files created by the CREATE DATABASE statement before you attempt to c
reate it once again.
After correcting the error that caused the failure of the database creation,
retturn to Step 6 of "Creating an Oracle Database".
Dropping a Database
To drop a database, remove its datafiles, redo log files, and all other asso
ciatted files (control files, parameter files, archived log files).
To view the names of the database's datafiles and redo log files, query the
dataa dictionary views V$DATAFILE and V$LOGFILE.
See Also: For more information about these views, see the Oracle8 Reference.
Parameters
As described in Step 3 of the section "Creating an Oracle Database", Oracle
sugggests you alter a minimum set of parameters. These parameters are descri
bed in the following sections:
DB_NAME and DB_DOMAIN
CONTROL_FILES
DB_BLOCK_SIZE
PROCESSES
ROLLBACK_SEGMENTS
License Parameters
DB_BLOCK_BUFFERS
DB_BLOCK_BUFFERS
LICENSE_MAX_SESSIONS_and LICENSE_SESSIONS WARNING
LICENSE_MAX_USERS
DB_NAME and DB_DOMAIN
A database's global database name (name and location within a network struct
ure)) is created by setting both the DB_NAME and DB_DOMAIN parameters before
database creation. After creation, the database's name cannot be easily cha
nged. The DB_NAME
parameter determines the local name component of the database's name, while
the DB_DOMAIN parameter indicates the domain (logical location) within a net
work structure. The combination of the settings for these two parameters sho
uld form a database name
that is unique within a network. For example, to create a database with a gl
oball database name of TEST.US.ACME.COM, edit the parameters of the new para
meter file as follows:
DB_NAME = TEST
DB_DOMAIN = US.ACME.COM
DB_NAME must be set to a text string of no more than eight characters. Durin
g daatabase creation, the name provided for DB_NAME is recorded in the dataf
iles, redo log files, and control file of the database. If during database i
nstance startup the value
of the DB_NAME parameter (of the parameter file) and the database name in th
e coontrol file are not the same, the database does not start.
DB_DOMAIN is a text string that specifies the network domain where the datab
ase is created; 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 di
stributed database
system, pay special attention to this initialization parameter before databa
se ccreation.
See Also: For more information about distributed databases, see Oracle8 Dist
ribuuted Database Systems.
CONTROL_FILES
Include the CONTROL_FILES parameter in your new parameter file and set its v
aluee to a list of control filenames to use for the new database. If you wan
t Oracle to create new operating system files when creating your database's
control files, make sure
that the filenames listed in the CONTROL_FILES parameter do not match any fi
lenaames that currently exist on your system. If you want Oracle to reuse or
overwrite existing files when creating your database's control files, make
sure that the filenames
listed in the CONTROL_FILES parameter match the filenames that currently exi
st.
listed in the CONTROL_FILES parameter match the filenames that currently exi
st.
----------------------------------------------------------------------------
----
warning:
Use extreme caution when setting this option. If you inadvertently specify a
fille that you did not intend and execute the CREATE DATABASE statement, th
e previous contents of that file will be overwritten.
----------------------------------------------------------------------------
----
If no filenames are listed for the CONTROL_FILES parameter, Oracle uses a de
faullt filename.
Oracle Corporation strongly recommends you use at least two control files st
oredd on separate physical disk drives for each database. Therefore, when sp
ecifying the CONTROL_FILES parameter of the new parameter file, follow these
guidelines:
List at least two filenames for the CONTROL_FILES parameter.
Place each control file on a separate physical disk drives by fully specifyi
ng ddrives for each filename.
----------------------------------------------------------------------------
----
Note:
The file specification for control files is operating system-dependent. Rega
rdleess of your operating system, always fully specify filenames for your co
ntrol files.
----------------------------------------------------------------------------
----
When you execute the CREATE DATABASE statement (in Step 7), the control file
s liisted in the CONTROL_FILES parameter of the parameter file will be creat
ed.
See Also: The default filename for the CONTROL_FILES parameter is operating
systtem-dependent. See your operating system-specific Oracle documentation f
or details.
DB_BLOCK_SIZE
The default data block size for every Oracle server is operating system-spec
ificc. The Oracle data block size is typically either 2K or 4K. Generally, t
he default data block size is adequate. In some cases, however, a larger dat
a block size provides
greater efficiency in disk and memory I/O (access and storage of data). Such
casses include:
Oracle is on a large computer system with a large amount of memory and fast
diskk drives. For example, databases controlled by mainframe computers with
vast hardware resources typically use a data block size of 4K or greater.
The operating system that runs Oracle uses a small operating system block si
ze. For example, if the operating system block size is 1K and the data block
size matches this, Oracle may be performing an excessive amount of disk I/O
during normal
operation. For best performance in this case, a database block should consis
t off multiple operating system blocks.
Each database's block size is set during database creation by the initializa
tionn parameter DB_BLOCK_SIZE. The block size cannot be changed after databa
se creation except by re-creating the database. If a database's block size i
s different from the
pt by re-creating the database. If a database's block size is different from
the
have 50 concurrent users, set this parameter to at least 55.
ROLLBACK_SEGMENTS
This parameter is a list of the rollback segments an Oracle instance acquire
s att database startup. List your rollback segments as the value of this par
ameter.
----------------------------------------------------------------------------
----
Attention:
After installation, you must create at least one rollback segment in the SYS
TEM tablespace in addition to the SYSTEM rollback segment before you can cre
ate any schema objects.
----------------------------------------------------------------------------
----
See Also: For more information about how many rollback segments you need, se
e Orracle8 Tuning.
License Parameters
Oracle helps you ensure that your site complies with its Oracle license agre
emennt. If your site is licensed by concurrent usage, you can track and limi
t the number of sessions concurrently connected to an instance. If your site
is licensed by named
users, you can limit the number of named users created in a database. To use
thiis facility, you need to know which type of licensing agreement your sit
e has and what the maximum number of sessions or named users is. Your site m
ight use either type of
licensing (session licensing or named user licensing), but not both.
See Also: For more information about managing licensing, see "Session and Us
er LLicensing".
LICENSE_MAX_SESSIONS_and LICENSE_SESSIONS WARNING
You can set a limit on the number of concurrent sessions that can connect to
a ddatabase on the specified computer. To set the maximum number of concurr
ent sessions for an instance, set the parameter LICENSE_MAX_SESSIONS in the
parameter file that
starts the instance, as shown in the following example:
LICENSE_MAX_SESSIONS = 80
In addition to setting a maximum number of sessions, you can set a warning l
imitt on the number of concurrent sessions. Once this limit is reached, addi
tional users can continue to connect (up to the maximum limit), but Oracle s
ends a warning for each
connecting user. To set the warning limit for an instance, set the parameter
LICCENSE_SESSIONS_WARNING. Set the warning limit to a value lower than LICE
NSE_MAX_SESSIONS.
For instances running with the Parallel Server, each instance can have its o
wn cconcurrent usage limit and warning limit. However, the sum of the instan
ces' limits must not exceed the site's session license.
See Also: For more information about setting these limits when using the Par
alleel Server, see Oracle8 Parallel Server Concepts and Administration.
LICENSE_MAX_USERS
You can set a limit on the number of users created in the database. Once thi
s liimit is reached, you cannot create more users.
----------------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
Note:
This mechanism assumes that each person accessing the database has a unique
userr name and that no people share a user name. Therefore, so that named us
er licensing can help you ensure compliance with your Oracle license agreeme
nt, 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_USER
S paarameter in the database's parameter file, as shown in the following exa
mple:
LICENSE_MAX_USERS = 200
For instances running with the Parallel Server, all instances connected to t
he ssame database should have the same named user limit.
See Also: For more information about setting this limit when using the Paral
lel Server see Oracle8 Parallel Server Concepts and Administration.
Considerations After Creating a Database
After you create a database, the instance is left running, and the database
is oopen and available for normal database use. Use Enterprise Manager to su
bsequently start and stop the database. If more than one database exists in
your database system,
specify the parameter file to use with any subsequent database startup.
If you plan to install other Oracle products to work with this database, see
thee installation instructions for those products; some products require yo
u to create additional data dictionary tables. See your operating system-spe
cific Oracle
documentation for the additional products. Usually, command files are provid
ed tto create and load these tables into the database's data dictionary.
The Oracle Server distribution media can include various SQL files that let
you experiment with the system, learn SQL, or create additional tables, view
s, or synonyms.
A newly created database has only two users, SYS and SYSTEM. The passwords f
or tthese two usernames should be changed soon after the database is created
.
See Also: For more information about the users SYS and SYSTEM see "Database
Admiinistrator Usernames".
For information about changing a user's password see "Altering Users".
Initial Tuning Guidelines
You can make a few significant tuning alterations to Oracle immediately foll
owinng installation. By following these instructions, you can reduce the nee
d to tune Oracle when it is running. This section gives recommendations for
the following
installation issues:
Allocating Rollback Segments
Choosing the Number of DB_BLOCK_LRU_LATCHES
Distributing I/O
Allocating Rollback Segments
Proper allocation of rollback segments makes for optimal database performanc
e. TThe size and number of rollback segments required for optimal performanc
e depends on your application. The Oracle8 Tuning manual contains some gener
al guidelines for
Proper allocation of rollback segments makes for optimal database performanc
e. TThe size and number of rollback segments required for optimal performanc
e depends on your application. The Oracle8 Tuning manual contains some gener
al guidelines for
choosing how many rollback segments to allocate based on the number of concu
rrennt transactions on your Oracle Server. These guidelines are appropriate
for most application mixes.
To create rollback segments, use the CREATE ROLLBACK SEGMENT command.
See Also: For information about the CREATE ROLLBACK SEGMENT command, see the
Oraacle8 SQL Reference.
Choosing Sizes for Rollback Segments
The size of your rollback segment can also affect performance. Rollback segm
ent size is determined by the storage parameters in the CREATE ROLLBACK SEGM
ENT statement. Your rollback segments must be large enough to hold the rollb
ack entries for your
transactions.
See Also: For information about choosing sizes for your rollback segments, s
ee OOracle8 Tuning.
Choosing the Number of DB_BLOCK_LRU_LATCHES
Contention for the LRU latch can impede performance on symmetric multiproces
sor (SMP) machines with a large number of CPUs. The LRU latch controls the r
eplacement of buffers in the buffer cache. For SMP systems, Oracle automatic
ally sets the number of
LRU latches to be one half the number of CPUs on the system. For non-SMP sys
temss, one LRU latch is sufficient.
You can specify the number of LRU latches on your system with the initializa
tiotter DB_BLOCK_LRU_LATCHES. This parameter sets the maximum value for the
desired number of LRU latches. Each LRU latch will control a set of buffers
and Oracle
balances allocation of replacement buffers among the sets.
See Also: For more information on LRU latches, see Oracle8 Tuning.
Distributing I/O
Proper distribution of I/O can improve database performance dramatically. I/
O caan be distributed during installation of Oracle. Distributing I/O during
installation can reduce the need to distribute I/O later when Oracle is run
ning.
There are several ways to distribute I/O when you install Oracle:
redo log file placement
datafile placement
separation of tables and indexes
density of data (rows per data block)
See Also: For information about ways to distribute I/O, see Oracle8 Tuning.
----------------------------------------------------------------------------
----
------------------
--
※ 来源:·BBS 水木清华站 bbs.net.tsinghua.edu.cn·[FROM: 162.105.41.141]
发信人: chendu (good), 信区: Database
标 题: Oracle DBA Guide V8, part 2
发信站: BBS 水木清华站 (Sat Nov 6 18:50:10 1999)
Hi, required by several friends, I am posting the
Oracle Database Administrator's Guide version 8
here. This is part 2.
Web Master, if my post it too long and not good for
the bbs, please delete it and I am sorry for it.
Hoping everyone enjoy it!
Good luck!
PS: please do not ask me question by email me
directly, this will cause some delay. (PPMM can ^_^)
----------------------------------------------------------------------------
----
2
Creating an Oracle Database
This chapter lists the steps necessary to create an Oracle database, and inc
ludees the following topics:
Considerations Before Creating a Database
Creating an Oracle Database
Parameters
Considerations After Creating a Database
Initial Tuning Guidelines
Considerations Before Creating a Database
This section includes the following topics:
Creation Prerequisites
Using an Initial Database
Migrating an Older Version of the Database
Database creation prepares several operating system files so they can work t
ogetther as an Oracle database. You need only create a database once, regard
less of how many datafiles it has or how many instances access it. Creating
a database can also erase
information in an existing database and create a new database with the same
namee and physical structure.
Creating a database includes the following operations:
creating new datafiles or erasing data that existed in previous datafiles
information creating structures that Oracle requires to access and use the d
atabbase (the data dictionary)
creating and initializing the control files and redo log files for the datab
ase
Consider the following issues before you create a database:
Plan your database tables and indexes, and estimate how much space they will
reqquire.
Plan how to protect your new database, including the configuration of its on
linee and archived redo log (and how much space it will require), and a back
up strategy.
Select the database character set. You must specify the database character s
et wwhen you create the database. After the database is created, you cannot
change the character set choices without re-creating the database. Hence, it
is important that you
carefully consider which character set(s) to use. All character data, includ
ing data in the data dictionary, is stored in the database character set. If
users access the database using a different character set, the database cha
racter set should be the
same as, or a superset of, all character sets they use.
Also become familiar with the principles and options of starting up and shut
tingg down an instance, mounting and opening a database, and using parameter
files.
See Also: "National Language Support" in the Oracle8 Reference.
For information about tables, indexes, and space management, see Chapters 9
throough 17.
For information about the online and archive redo logs, see Chapters 5 and 2
3 reespectively.
For information about database backup and recovery, see the Oracle8 Backup a
nd RRecovery Guide.
Creation Prerequisites
To create a new database, you must have the following:
the operating system privileges associated with a fully operational database
admministrator
sufficient memory to start the Oracle instance
sufficient disk storage space for the planned database on the computer that
execcutes Oracle
Using an Initial Database
Depending on your operating system, a database might have been created autom
aticcally as part of the installation procedure for Oracle. You can use this
initial database and customize it to meet your information management requi
rements, or discard it
and create one or more new databases to replace it.
Migrating an Older Version of the Database
If you are using a previous release of Oracle, database creation is required
onlly if you want an entirely new database. Otherwise, you can migrate your
existing Oracle databases managed by a previous version of Oracle and use t
hem with the new version
of the Oracle software.
See Also: Oracle8 Migration manual for information about migrating an existi
ng ddatabase.
For more information about migrating an existing database, see your operatin
g syystem-specific Oracle documentation.
Creating an Oracle Database
This section includes the following topics:
Steps for Creating an Oracle Database
Creating a Database: Example
Troubleshooting Database Creation
Dropping a Database
Steps for Creating an Oracle Database
These steps, which describe how to create an Oracle database, should be foll
owedd in the order presented.
To Create a New Database and Make It Available for System Use
Available for System Use
Back up any existing databases.
Create parameter files.
Edit new parameter files.
Check the instance identifier for your system.
Start Enterprise Manager and connect to Oracle as an administrator.
Start an instance.
Create the database.
Back up the database.
See Also: These steps provide general information about database creation on
alll operating systems. See your operating system-specific Oracle documenta
tion for information about creating databases on your platform.
Step 1: Back up any existing databases.
Oracle Corporation strongly recommends that you make complete backups of all
exiisting databases before creating a new database, in case database creati
on accidentally affects some existing files. Backup should include parameter
files, datafiles, redo
log files, and control files.
Step 2: Create parameter files.
The instance (System Global Area and background processes) for any Oracle da
tabaase is started using a parameter file.
Each database on your system should have at least one customized parameter f
ile that corresponds only to that database. Do not use the same file for sev
eral databases.
To create a parameter file for the database you are about to make, use your
operrating system to make a copy of the parameter file that Oracle provided
on the distribution media. Give this copy a new filename. You can then edit
and customize this new
file for the new database.
file for the new database.
See Also: For more information about copying the parameter file, see your op
eratting system-specific Oracle documentation.
----------------------------------------------------------------------------
----
Note:
In distributed processing environments, Enterprise Manager is often executed
froom a client machine of the network. If a client machine is being used to
execute Enterprise Manager and create a new database, you need to copy the
new parameter file
(currently located on the computer executing Oracle) to your client workstat
ion.. This procedure is operating system dependent. For more information abo
ut copying files among the computers of your network, see your operating sys
tem-specific Oracle
documentation.
----------------------------------------------------------------------------
----
Step 3: Edit new parameter files.
To create a new database, inspect and edit the following parameters of the n
ew pparameter file:
DB_NAME
DB_NAME
DB_DOMAIN
CONTROL_FILES
DB_BLOCK_SIZE
DB_BLOCK_BUFFERS
PROCESSES
ROLLBACK_SEGMENTS
You should also edit the appropriate license parameter(s):
LICENSE_MAX_SESSIONS
LICENSE_SESSION_WARNING
LICENSE_MAX_USERS
Step 4: Check the instance identifier for your system.
If you have other databases, check the Oracle instances identifier. The Orac
le iinstance identifier should match the name of the database (the value of
DB_NAME) to avoid confusion with other Oracle instances that are running con
currently on your system.
See your operating system-specific Oracle documentation for more information
.
Step 5: Start Enterprise Manager and connect to Oracle as an administrator.
Once Enterprise Manager is running, connect to the database as an administra
tor.
See Also: Starting Enterprise Manager is operating system specific; see your
opeerating system-specific Oracle documentation for details.
Step 6: Start an instance.
To start an instance (System Global Area and background processes) to be use
d wiith the new database, use the Startup Database dialog box of Enterprise
Manager. In the Startup Database dialog box, make sure that you have selecte
d the Startup Nomount
radio button.
After selecting the Startup Nomount, the instance starts. At this point, the
re iis no database. Only an SGA and background processes are started in prep
aration for the creation of a new database.
Step 7: Create the database.
To create the new database, use the SQL command CREATE DATABASE, optionally
settting parameters within the statement to name the database, establish max
imum numbers of files, name the files and set their sizes, and so on.
When you execute a CREATE DATABASE statement, Oracle performs the following
operrations:
creates the datafiles for the database
creates the control files for the database
creates the redo log files for the database
creates the SYSTEM tablespace and the SYSTEM rollback segment
creates the data dictionary
creates the users SYS and SYSTEM
specifies the character set that stores data in the database
mounts and opens the database for use
----------------------------------------------------------------------------
----
warning:
Make sure that the datafiles and redo log files that you specify do not conf
lictt with files of another database.
----------------------------------------------------------------------------
----
Step 8: Back up the database.
You should make a full backup of the database to ensure that you have a comp
letee set of files from which to recover if a media failure occurs.
See Also: Oracle8 Backup and Recovery Guide.
For more information about parameter files see "Using Parameter Files".
For information about the CREATE DATABASE command, character sets, and datab
ase creation see Oracle8 SQL Reference.
Creating a Database: Example
The following statement is an example of a CREATE DATABASE statement:
CREATE DATABASE test
DATAFILE 'test_system' SIZE 10M
LOGFILE GROUP 1 ('test_log1a', 'test_log1b') SIZE 500K,
GROUP 2 ('test_log2a', 'test_log2b') SIZE 500K;
The values of the MAXLOGFILES, MAXLOGMEMBERS, MAXDATAFILES, MAXLOGHISTORY, a
nd MMAXINSTANCES options in this example assume the default values, which ar
e operating system-dependent. The database is mounted in the default modes N
OARCHIVELOG and EXCLUSIVE
and then opened.
The items and information in the example statement above result in creating
a daatabase with the following characteristics:
The new database is named TEST.
The SYSTEM tablespace of the new database is comprised of one 10 MB datafile
nammed TEST_SYSTEM.
The new database has two online redo log groups, each containing two 500 KB
membbers.
The new database does not overwrite any existing control files specified in
the parameter file.
----------------------------------------------------------------------------
----
Note:
You can set several limits during database creation. Some of these limits ar
e allso subject to superseding limits of the operating system and can affect
each other. For example, if you set MAXDATAFILES, Oracle allocates enough s
pace in the control file
to store MAXDATAFILES filenames, even if the database has only one datafile
inittially; because the maximum control file size is limited and operating s
ystem-dependent, you might not be able to set all CREATE DATABASE parameters
at their theoretical
maximums.
----------------------------------------------------------------------------
----
See Also: For more information about setting limits during database creation
, seee the Oracle8 SQL Reference.
See your operating system-specific Oracle documentation for information abou
t opperating system limits.
Troubleshooting Database Creation
If for any reason database creation fails, shut down the instance and delete
anyy files created by the CREATE DATABASE statement before you attempt to c
reate it once again.
After correcting the error that caused the failure of the database creation,
retturn to Step 6 of "Creating an Oracle Database".
Dropping a Database
To drop a database, remove its datafiles, redo log files, and all other asso
ciatted files (control files, parameter files, archived log files).
To view the names of the database's datafiles and redo log files, query the
dataa dictionary views V$DATAFILE and V$LOGFILE.
See Also: For more information about these views, see the Oracle8 Reference.
Parameters
As described in Step 3 of the section "Creating an Oracle Database", Oracle
sugggests you alter a minimum set of parameters. These parameters are descri
bed in the following sections:
DB_NAME and DB_DOMAIN
CONTROL_FILES
DB_BLOCK_SIZE
PROCESSES
ROLLBACK_SEGMENTS
License Parameters
DB_BLOCK_BUFFERS
DB_BLOCK_BUFFERS
LICENSE_MAX_SESSIONS_and LICENSE_SESSIONS WARNING
LICENSE_MAX_USERS
DB_NAME and DB_DOMAIN
A database's global database name (name and location within a network struct
ure)) is created by setting both the DB_NAME and DB_DOMAIN parameters before
database creation. After creation, the database's name cannot be easily cha
nged. The DB_NAME
parameter determines the local name component of the database's name, while
the DB_DOMAIN parameter indicates the domain (logical location) within a net
work structure. The combination of the settings for these two parameters sho
uld form a database name
that is unique within a network. For example, to create a database with a gl
oball database name of TEST.US.ACME.COM, edit the parameters of the new para
meter file as follows:
DB_NAME = TEST
DB_DOMAIN = US.ACME.COM
DB_NAME must be set to a text string of no more than eight characters. Durin
g daatabase creation, the name provided for DB_NAME is recorded in the dataf
iles, redo log files, and control file of the database. If during database i
nstance startup the value
of the DB_NAME parameter (of the parameter file) and the database name in th
e coontrol file are not the same, the database does not start.
DB_DOMAIN is a text string that specifies the network domain where the datab
ase is created; 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 di
stributed database
system, pay special attention to this initialization parameter before databa
se ccreation.
See Also: For more information about distributed databases, see Oracle8 Dist
ribuuted Database Systems.
CONTROL_FILES
Include the CONTROL_FILES parameter in your new parameter file and set its v
aluee to a list of control filenames to use for the new database. If you wan
t Oracle to create new operating system files when creating your database's
control files, make sure
that the filenames listed in the CONTROL_FILES parameter do not match any fi
lenaames that currently exist on your system. If you want Oracle to reuse or
overwrite existing files when creating your database's control files, make
sure that the filenames
listed in the CONTROL_FILES parameter match the filenames that currently exi
st.
listed in the CONTROL_FILES parameter match the filenames that currently exi
st.
----------------------------------------------------------------------------
----
warning:
Use extreme caution when setting this option. If you inadvertently specify a
fille that you did not intend and execute the CREATE DATABASE statement, th
e previous contents of that file will be overwritten.
----------------------------------------------------------------------------
----
If no filenames are listed for the CONTROL_FILES parameter, Oracle uses a de
faullt filename.
Oracle Corporation strongly recommends you use at least two control files st
oredd on separate physical disk drives for each database. Therefore, when sp
ecifying the CONTROL_FILES parameter of the new parameter file, follow these
guidelines:
List at least two filenames for the CONTROL_FILES parameter.
Place each control file on a separate physical disk drives by fully specifyi
ng ddrives for each filename.
----------------------------------------------------------------------------
----
Note:
The file specification for control files is operating system-dependent. Rega
rdleess of your operating system, always fully specify filenames for your co
ntrol files.
----------------------------------------------------------------------------
----
When you execute the CREATE DATABASE statement (in Step 7), the control file
s liisted in the CONTROL_FILES parameter of the parameter file will be creat
ed.
See Also: The default filename for the CONTROL_FILES parameter is operating
systtem-dependent. See your operating system-specific Oracle documentation f
or details.
DB_BLOCK_SIZE
The default data block size for every Oracle server is operating system-spec
ificc. The Oracle data block size is typically either 2K or 4K. Generally, t
he default data block size is adequate. In some cases, however, a larger dat
a block size provides
greater efficiency in disk and memory I/O (access and storage of data). Such
casses include:
Oracle is on a large computer system with a large amount of memory and fast
diskk drives. For example, databases controlled by mainframe computers with
vast hardware resources typically use a data block size of 4K or greater.
The operating system that runs Oracle uses a small operating system block si
ze. For example, if the operating system block size is 1K and the data block
size matches this, Oracle may be performing an excessive amount of disk I/O
during normal
operation. For best performance in this case, a database block should consis
t off multiple operating system blocks.
Each database's block size is set during database creation by the initializa
tionn parameter DB_BLOCK_SIZE. The block size cannot be changed after databa
se creation except by re-creating the database. If a database's block size i
s different from the
pt by re-creating the database. If a database's block size is different from
the
have 50 concurrent users, set this parameter to at least 55.
ROLLBACK_SEGMENTS
This parameter is a list of the rollback segments an Oracle instance acquire
s att database startup. List your rollback segments as the value of this par
ameter.
----------------------------------------------------------------------------
----
Attention:
After installation, you must create at least one rollback segment in the SYS
TEM tablespace in addition to the SYSTEM rollback segment before you can cre
ate any schema objects.
----------------------------------------------------------------------------
----
See Also: For more information about how many rollback segments you need, se
e Orracle8 Tuning.
License Parameters
Oracle helps you ensure that your site complies with its Oracle license agre
emennt. If your site is licensed by concurrent usage, you can track and limi
t the number of sessions concurrently connected to an instance. If your site
is licensed by named
users, you can limit the number of named users created in a database. To use
thiis facility, you need to know which type of licensing agreement your sit
e has and what the maximum number of sessions or named users is. Your site m
ight use either type of
licensing (session licensing or named user licensing), but not both.
See Also: For more information about managing licensing, see "Session and Us
er LLicensing".
LICENSE_MAX_SESSIONS_and LICENSE_SESSIONS WARNING
You can set a limit on the number of concurrent sessions that can connect to
a ddatabase on the specified computer. To set the maximum number of concurr
ent sessions for an instance, set the parameter LICENSE_MAX_SESSIONS in the
parameter file that
starts the instance, as shown in the following example:
LICENSE_MAX_SESSIONS = 80
In addition to setting a maximum number of sessions, you can set a warning l
imitt on the number of concurrent sessions. Once this limit is reached, addi
tional users can continue to connect (up to the maximum limit), but Oracle s
ends a warning for each
connecting user. To set the warning limit for an instance, set the parameter
LICCENSE_SESSIONS_WARNING. Set the warning limit to a value lower than LICE
NSE_MAX_SESSIONS.
For instances running with the Parallel Server, each instance can have its o
wn cconcurrent usage limit and warning limit. However, the sum of the instan
ces' limits must not exceed the site's session license.
See Also: For more information about setting these limits when using the Par
alleel Server, see Oracle8 Parallel Server Concepts and Administration.
LICENSE_MAX_USERS
You can set a limit on the number of users created in the database. Once thi
s liimit is reached, you cannot create more users.
----------------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
Note:
This mechanism assumes that each person accessing the database has a unique
userr name and that no people share a user name. Therefore, so that named us
er licensing can help you ensure compliance with your Oracle license agreeme
nt, 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_USER
S paarameter in the database's parameter file, as shown in the following exa
mple:
LICENSE_MAX_USERS = 200
For instances running with the Parallel Server, all instances connected to t
he ssame database should have the same named user limit.
See Also: For more information about setting this limit when using the Paral
lel Server see Oracle8 Parallel Server Concepts and Administration.
Considerations After Creating a Database
After you create a database, the instance is left running, and the database
is oopen and available for normal database use. Use Enterprise Manager to su
bsequently start and stop the database. If more than one database exists in
your database system,
specify the parameter file to use with any subsequent database startup.
If you plan to install other Oracle products to work with this database, see
thee installation instructions for those products; some products require yo
u to create additional data dictionary tables. See your operating system-spe
cific Oracle
documentation for the additional products. Usually, command files are provid
ed tto create and load these tables into the database's data dictionary.
The Oracle Server distribution media can include various SQL files that let
you experiment with the system, learn SQL, or create additional tables, view
s, or synonyms.
A newly created database has only two users, SYS and SYSTEM. The passwords f
or tthese two usernames should be changed soon after the database is created
.
See Also: For more information about the users SYS and SYSTEM see "Database
Admiinistrator Usernames".
For information about changing a user's password see "Altering Users".
Initial Tuning Guidelines
You can make a few significant tuning alterations to Oracle immediately foll
owinng installation. By following these instructions, you can reduce the nee
d to tune Oracle when it is running. This section gives recommendations for
the following
installation issues:
Allocating Rollback Segments
Choosing the Number of DB_BLOCK_LRU_LATCHES
Distributing I/O
Allocating Rollback Segments
Proper allocation of rollback segments makes for optimal database performanc
e. TThe size and number of rollback segments required for optimal performanc
e depends on your application. The Oracle8 Tuning manual contains some gener
al guidelines for
Proper allocation of rollback segments makes for optimal database performanc
e. TThe size and number of rollback segments required for optimal performanc
e depends on your application. The Oracle8 Tuning manual contains some gener
al guidelines for
choosing how many rollback segments to allocate based on the number of concu
rrennt transactions on your Oracle Server. These guidelines are appropriate
for most application mixes.
To create rollback segments, use the CREATE ROLLBACK SEGMENT command.
See Also: For information about the CREATE ROLLBACK SEGMENT command, see the
Oraacle8 SQL Reference.
Choosing Sizes for Rollback Segments
The size of your rollback segment can also affect performance. Rollback segm
ent size is determined by the storage parameters in the CREATE ROLLBACK SEGM
ENT statement. Your rollback segments must be large enough to hold the rollb
ack entries for your
transactions.
See Also: For information about choosing sizes for your rollback segments, s
ee OOracle8 Tuning.
Choosing the Number of DB_BLOCK_LRU_LATCHES
Contention for the LRU latch can impede performance on symmetric multiproces
sor (SMP) machines with a large number of CPUs. The LRU latch controls the r
eplacement of buffers in the buffer cache. For SMP systems, Oracle automatic
ally sets the number of
LRU latches to be one half the number of CPUs on the system. For non-SMP sys
temss, one LRU latch is sufficient.
You can specify the number of LRU latches on your system with the initializa
tiotter DB_BLOCK_LRU_LATCHES. This parameter sets the maximum value for the
desired number of LRU latches. Each LRU latch will control a set of buffers
and Oracle
balances allocation of replacement buffers among the sets.
See Also: For more information on LRU latches, see Oracle8 Tuning.
Distributing I/O
Proper distribution of I/O can improve database performance dramatically. I/
O caan be distributed during installation of Oracle. Distributing I/O during
installation can reduce the need to distribute I/O later when Oracle is run
ning.
There are several ways to distribute I/O when you install Oracle:
redo log file placement
datafile placement
separation of tables and indexes
density of data (rows per data block)
See Also: For information about ways to distribute I/O, see Oracle8 Tuning.
----------------------------------------------------------------------------
----
------------------
--
※ 来源:·BBS 水木清华站 bbs.net.tsinghua.edu.cn·[FROM: 162.105.41.141]
发信人: chendu (good), 信区: Database
标 题: Oracle DBA Guide V8, part 2
发信站: BBS 水木清华站 (Sat Nov 6 18:50:10 1999)
Hi, required by several friends, I am posting the
Oracle Database Administrator's Guide version 8
here. This is part 2.
Web Master, if my post it too long and not good for
the bbs, please delete it and I am sorry for it.
Hoping everyone enjoy it!
Good luck!
PS: please do not ask me question by email me
directly, this will cause some delay. (PPMM can ^_^)
----------------------------------------------------------------------------
----
2
Creating an Oracle Database
This chapter lists the steps necessary to create an Oracle database, and inc
ludees the following topics:
Considerations Before Creating a Database
Creating an Oracle Database
Parameters
Considerations After Creating a Database
Initial Tuning Guidelines
Considerations Before Creating a Database
This section includes the following topics:
Creation Prerequisites
Using an Initial Database
Migrating an Older Version of the Database
Database creation prepares several operating system files so they can work t
ogetther as an Oracle database. You need only create a database once, regard
less of how many datafiles it has or how many instances access it. Creating
a database can also erase
information in an existing database and create a new database with the same
namee and physical structure.
Creating a database includes the following operations:
creating new datafiles or erasing data that existed in previous datafiles
information creating structures that Oracle requires to access and use the d
atabbase (the data dictionary)
creating and initializing the control files and redo log files for the datab
ase
Consider the following issues before you create a database:
Plan your database tables and indexes, and estimate how much space they will
reqquire.
Plan how to protect your new database, including the configuration of its on
linee and archived redo log (and how much space it will require), and a back
up strategy.
Select the database character set. You must specify the database character s
et wwhen you create the database. After the database is created, you cannot
change the character set choices without re-creating the database. Hence, it
is important that you
carefully consider which character set(s) to use. All character data, includ
ing data in the data dictionary, is stored in the database character set. If
users access the database using a different character set, the database cha
racter set should be the
same as, or a superset of, all character sets they use.
Also become familiar with the principles and options of starting up and shut
tingg down an instance, mounting and opening a database, and using parameter
files.
See Also: "National Language Support" in the Oracle8 Reference.
For information about tables, indexes, and space management, see Chapters 9
throough 17.
For information about the online and archive redo logs, see Chapters 5 and 2
3 reespectively.
For information about database backup and recovery, see the Oracle8 Backup a
nd RRecovery Guide.
Creation Prerequisites
To create a new database, you must have the following:
the operating system privileges associated with a fully operational database
admministrator
sufficient memory to start the Oracle instance
sufficient disk storage space for the planned database on the computer that
execcutes Oracle
Using an Initial Database
Depending on your operating system, a database might have been created autom
aticcally as part of the installation procedure for Oracle. You can use this
initial database and customize it to meet your information management requi
rements, or discard it
and create one or more new databases to replace it.
Migrating an Older Version of the Database
If you are using a previous release of Oracle, database creation is required
onlly if you want an entirely new database. Otherwise, you can migrate your
existing Oracle databases managed by a previous version of Oracle and use t
hem with the new version
of the Oracle software.
See Also: Oracle8 Migration manual for information about migrating an existi
ng ddatabase.
For more information about migrating an existing database, see your operatin
g syystem-specific Oracle documentation.
Creating an Oracle Database
This section includes the following topics:
Steps for Creating an Oracle Database
Creating a Database: Example
Troubleshooting Database Creation
Dropping a Database
Steps for Creating an Oracle Database
These steps, which describe how to create an Oracle database, should be foll
owedd in the order presented.
To Create a New Database and Make It Available for System Use
Available for System Use
Back up any existing databases.
Create parameter files.
Edit new parameter files.
Check the instance identifier for your system.
Start Enterprise Manager and connect to Oracle as an administrator.
Start an instance.
Create the database.
Back up the database.
See Also: These steps provide general information about database creation on
alll operating systems. See your operating system-specific Oracle documenta
tion for information about creating databases on your platform.
Step 1: Back up any existing databases.
Oracle Corporation strongly recommends that you make complete backups of all
exiisting databases before creating a new database, in case database creati
on accidentally affects some existing files. Backup should include parameter
files, datafiles, redo
log files, and control files.
Step 2: Create parameter files.
The instance (System Global Area and background processes) for any Oracle da
tabaase is started using a parameter file.
Each database on your system should have at least one customized parameter f
ile that corresponds only to that database. Do not use the same file for sev
eral databases.
To create a parameter file for the database you are about to make, use your
operrating system to make a copy of the parameter file that Oracle provided
on the distribution media. Give this copy a new filename. You can then edit
and customize this new
file for the new database.
file for the new database.
See Also: For more information about copying the parameter file, see your op
eratting system-specific Oracle documentation.
----------------------------------------------------------------------------
----
Note:
In distributed processing environments, Enterprise Manager is often executed
froom a client machine of the network. If a client machine is being used to
execute Enterprise Manager and create a new database, you need to copy the
new parameter file
(currently located on the computer executing Oracle) to your client workstat
ion.. This procedure is operating system dependent. For more information abo
ut copying files among the computers of your network, see your operating sys
tem-specific Oracle
documentation.
----------------------------------------------------------------------------
----
Step 3: Edit new parameter files.
To create a new database, inspect and edit the following parameters of the n
ew pparameter file:
DB_NAME
DB_NAME
DB_DOMAIN
CONTROL_FILES
DB_BLOCK_SIZE
DB_BLOCK_BUFFERS
PROCESSES
ROLLBACK_SEGMENTS
You should also edit the appropriate license parameter(s):
LICENSE_MAX_SESSIONS
LICENSE_SESSION_WARNING
LICENSE_MAX_USERS
Step 4: Check the instance identifier for your system.
If you have other databases, check the Oracle instances identifier. The Orac
le iinstance identifier should match the name of the database (the value of
DB_NAME) to avoid confusion with other Oracle instances that are running con
currently on your system.
See your operating system-specific Oracle documentation for more information
.
Step 5: Start Enterprise Manager and connect to Oracle as an administrator.
Once Enterprise Manager is running, connect to the database as an administra
tor.
See Also: Starting Enterprise Manager is operating system specific; see your
opeerating system-specific Oracle documentation for details.
Step 6: Start an instance.
To start an instance (System Global Area and background processes) to be use
d wiith the new database, use the Startup Database dialog box of Enterprise
Manager. In the Startup Database dialog box, make sure that you have selecte
d the Startup Nomount
radio button.
After selecting the Startup Nomount, the instance starts. At this point, the
re iis no database. Only an SGA and background processes are started in prep
aration for the creation of a new database.
Step 7: Create the database.
To create the new database, use the SQL command CREATE DATABASE, optionally
settting parameters within the statement to name the database, establish max
imum numbers of files, name the files and set their sizes, and so on.
When you execute a CREATE DATABASE statement, Oracle performs the following
operrations:
creates the datafiles for the database
creates the control files for the database
creates the redo log files for the database
creates the SYSTEM tablespace and the SYSTEM rollback segment
creates the data dictionary
creates the users SYS and SYSTEM
specifies the character set that stores data in the database
mounts and opens the database for use
----------------------------------------------------------------------------
----
warning:
Make sure that the datafiles and redo log files that you specify do not conf
lictt with files of another database.
----------------------------------------------------------------------------
----
Step 8: Back up the database.
You should make a full backup of the database to ensure that you have a comp
letee set of files from which to recover if a media failure occurs.
See Also: Oracle8 Backup and Recovery Guide.
For more information about parameter files see "Using Parameter Files".
For information about the CREATE DATABASE command, character sets, and datab
ase creation see Oracle8 SQL Reference.
Creating a Database: Example
The following statement is an example of a CREATE DATABASE statement:
CREATE DATABASE test
DATAFILE 'test_system' SIZE 10M
LOGFILE GROUP 1 ('test_log1a', 'test_log1b') SIZE 500K,
GROUP 2 ('test_log2a', 'test_log2b') SIZE 500K;
The values of the MAXLOGFILES, MAXLOGMEMBERS, MAXDATAFILES, MAXLOGHISTORY, a
nd MMAXINSTANCES options in this example assume the default values, which ar
e operating system-dependent. The database is mounted in the default modes N
OARCHIVELOG and EXCLUSIVE
and then opened.
The items and information in the example statement above result in creating
a daatabase with the following characteristics:
The new database is named TEST.
The SYSTEM tablespace of the new database is comprised of one 10 MB datafile
nammed TEST_SYSTEM.
The new database has two online redo log groups, each containing two 500 KB
membbers.
The new database does not overwrite any existing control files specified in
the parameter file.
----------------------------------------------------------------------------
----
Note:
You can set several limits during database creation. Some of these limits ar
e allso subject to superseding limits of the operating system and can affect
each other. For example, if you set MAXDATAFILES, Oracle allocates enough s
pace in the control file
to store MAXDATAFILES filenames, even if the database has only one datafile
inittially; because the maximum control file size is limited and operating s
ystem-dependent, you might not be able to set all CREATE DATABASE parameters
at their theoretical
maximums.
----------------------------------------------------------------------------
----
See Also: For more information about setting limits during database creation
, seee the Oracle8 SQL Reference.
See your operating system-specific Oracle documentation for information abou
t opperating system limits.
Troubleshooting Database Creation
If for any reason database creation fails, shut down the instance and delete
anyy files created by the CREATE DATABASE statement before you attempt to c
reate it once again.
After correcting the error that caused the failure of the database creation,
retturn to Step 6 of "Creating an Oracle Database".
Dropping a Database
To drop a database, remove its datafiles, redo log files, and all other asso
ciatted files (control files, parameter files, archived log files).
To view the names of the database's datafiles and redo log files, query the
dataa dictionary views V$DATAFILE and V$LOGFILE.
See Also: For more information about these views, see the Oracle8 Reference.
Parameters
As described in Step 3 of the section "Creating an Oracle Database", Oracle
sugggests you alter a minimum set of parameters. These parameters are descri
bed in the following sections:
DB_NAME and DB_DOMAIN
CONTROL_FILES
DB_BLOCK_SIZE
PROCESSES
ROLLBACK_SEGMENTS
License Parameters
DB_BLOCK_BUFFERS
DB_BLOCK_BUFFERS
LICENSE_MAX_SESSIONS_and LICENSE_SESSIONS WARNING
LICENSE_MAX_USERS
DB_NAME and DB_DOMAIN
A database's global database name (name and location within a network struct
ure)) is created by setting both the DB_NAME and DB_DOMAIN parameters before
database creation. After creation, the database's name cannot be easily cha
nged. The DB_NAME
parameter determines the local name component of the database's name, while
the DB_DOMAIN parameter indicates the domain (logical location) within a net
work structure. The combination of the settings for these two parameters sho
uld form a database name
that is unique within a network. For example, to create a database with a gl
oball database name of TEST.US.ACME.COM, edit the parameters of the new para
meter file as follows:
DB_NAME = TEST
DB_DOMAIN = US.ACME.COM
DB_NAME must be set to a text string of no more than eight characters. Durin
g daatabase creation, the name provided for DB_NAME is recorded in the dataf
iles, redo log files, and control file of the database. If during database i
nstance startup the value
of the DB_NAME parameter (of the parameter file) and the database name in th
e coontrol file are not the same, the database does not start.
DB_DOMAIN is a text string that specifies the network domain where the datab
ase is created; 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 di
stributed database
system, pay special attention to this initialization parameter before databa
se ccreation.
See Also: For more information about distributed databases, see Oracle8 Dist
ribuuted Database Systems.
CONTROL_FILES
Include the CONTROL_FILES parameter in your new parameter file and set its v
aluee to a list of control filenames to use for the new database. If you wan
t Oracle to create new operating system files when creating your database's
control files, make sure
that the filenames listed in the CONTROL_FILES parameter do not match any fi
lenaames that currently exist on your system. If you want Oracle to reuse or
overwrite existing files when creating your database's control files, make
sure that the filenames
listed in the CONTROL_FILES parameter match the filenames that currently exi
st.
listed in the CONTROL_FILES parameter match the filenames that currently exi
st.
----------------------------------------------------------------------------
----
warning:
Use extreme caution when setting this option. If you inadvertently specify a
fille that you did not intend and execute the CREATE DATABASE statement, th
e previous contents of that file will be overwritten.
----------------------------------------------------------------------------
----
If no filenames are listed for the CONTROL_FILES parameter, Oracle uses a de
faullt filename.
Oracle Corporation strongly recommends you use at least two control files st
oredd on separate physical disk drives for each database. Therefore, when sp
ecifying the CONTROL_FILES parameter of the new parameter file, follow these
guidelines:
List at least two filenames for the CONTROL_FILES parameter.
Place each control file on a separate physical disk drives by fully specifyi
ng ddrives for each filename.
----------------------------------------------------------------------------
----
Note:
The file specification for control files is operating system-dependent. Rega
rdleess of your operating system, always fully specify filenames for your co
ntrol files.
----------------------------------------------------------------------------
----
When you execute the CREATE DATABASE statement (in Step 7), the control file
s liisted in the CONTROL_FILES parameter of the parameter file will be creat
ed.
See Also: The default filename for the CONTROL_FILES parameter is operating
systtem-dependent. See your operating system-specific Oracle documentation f
or details.
DB_BLOCK_SIZE
The default data block size for every Oracle server is operating system-spec
ificc. The Oracle data block size is typically either 2K or 4K. Generally, t
he default data block size is adequate. In some cases, however, a larger dat
a block size provides
greater efficiency in disk and memory I/O (access and storage of data). Such
casses include:
Oracle is on a large computer system with a large amount of memory and fast
diskk drives. For example, databases controlled by mainframe computers with
vast hardware resources typically use a data block size of 4K or greater.
The operating system that runs Oracle uses a small operating system block si
ze. For example, if the operating system block size is 1K and the data block
size matches this, Oracle may be performing an excessive amount of disk I/O
during normal
operation. For best performance in this case, a database block should consis
t off multiple operating system blocks.
Each database's block size is set during database creation by the initializa
tionn parameter DB_BLOCK_SIZE. The block size cannot be changed after databa
se creation except by re-creating the database. If a database's block size i
s different from the
pt by re-creating the database. If a database's block size is different from
the
have 50 concurrent users, set this parameter to at least 55.
ROLLBACK_SEGMENTS
This parameter is a list of the rollback segments an Oracle instance acquire
s att database startup. List your rollback segments as the value of this par
ameter.
----------------------------------------------------------------------------
----
Attention:
After installation, you must create at least one rollback segment in the SYS
TEM tablespace in addition to the SYSTEM rollback segment before you can cre
ate any schema objects.
----------------------------------------------------------------------------
----
See Also: For more information about how many rollback segments you need, se
e Orracle8 Tuning.
License Parameters
Oracle helps you ensure that your site complies with its Oracle license agre
emennt. If your site is licensed by concurrent usage, you can track and limi
t the number of sessions concurrently connected to an instance. If your site
is licensed by named
users, you can limit the number of named users created in a database. To use
thiis facility, you need to know which type of licensing agreement your sit
e has and what the maximum number of sessions or named users is. Your site m
ight use either type of
licensing (session licensing or named user licensing), but not both.
See Also: For more information about managing licensing, see "Session and Us
er LLicensing".
LICENSE_MAX_SESSIONS_and LICENSE_SESSIONS WARNING
You can set a limit on the number of concurrent sessions that can connect to
a ddatabase on the specified computer. To set the maximum number of concurr
ent sessions for an instance, set the parameter LICENSE_MAX_SESSIONS in the
parameter file that
starts the instance, as shown in the following example:
LICENSE_MAX_SESSIONS = 80
In addition to setting a maximum number of sessions, you can set a warning l
imitt on the number of concurrent sessions. Once this limit is reached, addi
tional users can continue to connect (up to the maximum limit), but Oracle s
ends a warning for each
connecting user. To set the warning limit for an instance, set the parameter
LICCENSE_SESSIONS_WARNING. Set the warning limit to a value lower than LICE
NSE_MAX_SESSIONS.
For instances running with the Parallel Server, each instance can have its o
wn cconcurrent usage limit and warning limit. However, the sum of the instan
ces' limits must not exceed the site's session license.
See Also: For more information about setting these limits when using the Par
alleel Server, see Oracle8 Parallel Server Concepts and Administration.
LICENSE_MAX_USERS
You can set a limit on the number of users created in the database. Once thi
s liimit is reached, you cannot create more users.
----------------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
Note:
This mechanism assumes that each person accessing the database has a unique
userr name and that no people share a user name. Therefore, so that named us
er licensing can help you ensure compliance with your Oracle license agreeme
nt, 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_USER
S paarameter in the database's parameter file, as shown in the following exa
mple:
LICENSE_MAX_USERS = 200
For instances running with the Parallel Server, all instances connected to t
he ssame database should have the same named user limit.
See Also: For more information about setting this limit when using the Paral
lel Server see Oracle8 Parallel Server Concepts and Administration.
Considerations After Creating a Database
After you create a database, the instance is left running, and the database
is oopen and available for normal database use. Use Enterprise Manager to su
bsequently start and stop the database. If more than one database exists in
your database system,
specify the parameter file to use with any subsequent database startup.
If you plan to install other Oracle products to work with this database, see
thee installation instructions for those products; some products require yo
u to create additional data dictionary tables. See your operating system-spe
cific Oracle
documentation for the additional products. Usually, command files are provid
ed tto create and load these tables into the database's data dictionary.
The Oracle Server distribution media can include various SQL files that let
you experiment with the system, learn SQL, or create additional tables, view
s, or synonyms.
A newly created database has only two users, SYS and SYSTEM. The passwords f
or tthese two usernames should be changed soon after the database is created
.
See Also: For more information about the users SYS and SYSTEM see "Database
Admiinistrator Usernames".
For information about changing a user's password see "Altering Users".
Initial Tuning Guidelines
You can make a few significant tuning alterations to Oracle immediately foll
owinng installation. By following these instructions, you can reduce the nee
d to tune Oracle when it is running. This section gives recommendations for
the following
installation issues:
Allocating Rollback Segments
Choosing the Number of DB_BLOCK_LRU_LATCHES
Distributing I/O
Allocating Rollback Segments
Proper allocation of rollback segments makes for optimal database performanc
e. TThe size and number of rollback segments required for optimal performanc
e depends on your application. The Oracle8 Tuning manual contains some gener
al guidelines for
Proper allocation of rollback segments makes for optimal database performanc
e. TThe size and number of rollback segments required for optimal performanc
e depends on your application. The Oracle8 Tuning manual contains some gener
al guidelines for
choosing how many rollback segments to allocate based on the number of concu
rrennt transactions on your Oracle Server. These guidelines are appropriate
for most application mixes.
To create rollback segments, use the CREATE ROLLBACK SEGMENT command.
See Also: For information about the CREATE ROLLBACK SEGMENT command, see the
Oraacle8 SQL Reference.
Choosing Sizes for Rollback Segments
The size of your rollback segment can also affect performance. Rollback segm
ent size is determined by the storage parameters in the CREATE ROLLBACK SEGM
ENT statement. Your rollback segments must be large enough to hold the rollb
ack entries for your
transactions.
See Also: For information about choosing sizes for your rollback segments, s
ee OOracle8 Tuning.
Choosing the Number of DB_BLOCK_LRU_LATCHES
Contention for the LRU latch can impede performance on symmetric multiproces
sor (SMP) machines with a large number of CPUs. The LRU latch controls the r
eplacement of buffers in the buffer cache. For SMP systems, Oracle automatic
ally sets the number of
LRU latches to be one half the number of CPUs on the system. For non-SMP sys
temss, one LRU latch is sufficient.
You can specify the number of LRU latches on your system with the initializa
tiotter DB_BLOCK_LRU_LATCHES. This parameter sets the maximum value for the
desired number of LRU latches. Each LRU latch will control a set of buffers
and Oracle
balances allocation of replacement buffers among the sets.
See Also: For more information on LRU latches, see Oracle8 Tuning.
Distributing I/O
Proper distribution of I/O can improve database performance dramatically. I/
O caan be distributed during installation of Oracle. Distributing I/O during
installation can reduce the need to distribute I/O later when Oracle is run
ning.
There are several ways to distribute I/O when you install Oracle:
redo log file placement
datafile placement
separation of tables and indexes
density of data (rows per data block)
See Also: For information about ways to distribute I/O, see Oracle8 Tuning.
----------------------------------------------------------------------------
----
------------------
--
※ 来源:·BBS 水木清华站 bbs.net.tsinghua.edu.cn·[FROM: 162.105.41.141]
发信人: chendu (good), 信区: Database
标 题: Oracle DBA Guide V8, part 2
发信站: BBS 水木清华站 (Sat Nov 6 18:50:10 1999)
Hi, required by several friends, I am posting the
Oracle Database Administrator's Guide version 8
here. This is part 2.
Web Master, if my post it too long and not good for
the bbs, please delete it and I am sorry for it.
Hoping everyone enjoy it!
Good luck!
PS: please do not ask me question by email me
directly, this will cause some delay. (PPMM can ^_^)
----------------------------------------------------------------------------
----
2
Creating an Oracle Database
This chapter lists the steps necessary to create an Oracle database, and inc
ludees the following topics:
Considerations Before Creating a Database
Creating an Oracle Database
Parameters
Considerations After Creating a Database
Initial Tuning Guidelines
Considerations Before Creating a Database
This section includes the following topics:
Creation Prerequisites
Using an Initial Database
Migrating an Older Version of the Database
Database creation prepares several operating system files so they can work t
ogetther as an Oracle database. You need only create a database once, regard
less of how many datafiles it has or how many instances access it. Creating
a database can also erase
information in an existing database and create a new database with the same
namee and physical structure.
Creating a database includes the following operations:
creating new datafiles or erasing data that existed in previous datafiles
information creating structures that Oracle requires to access and use the d
atabbase (the data dictionary)
creating and initializing the control files and redo log files for the datab
ase
Consider the following issues before you create a database:
Plan your database tables and indexes, and estimate how much space they will
reqquire.
Plan how to protect your new database, including the configuration of its on
linee and archived redo log (and how much space it will require), and a back
up strategy.
Select the database character set. You must specify the database character s
et wwhen you create the database. After the database is created, you cannot
change the character set choices without re-creating the database. Hence, it
is important that you
carefully consider which character set(s) to use. All character data, includ
ing data in the data dictionary, is stored in the database character set. If
users access the database using a different character set, the database cha
racter set should be the
same as, or a superset of, all character sets they use.
Also become familiar with the principles and options of starting up and shut
tingg down an instance, mounting and opening a database, and using parameter
files.
See Also: "National Language Support" in the Oracle8 Reference.
For information about tables, indexes, and space management, see Chapters 9
throough 17.
For information about the online and archive redo logs, see Chapters 5 and 2
3 reespectively.
For information about database backup and recovery, see the Oracle8 Backup a
nd RRecovery Guide.
Creation Prerequisites
To create a new database, you must have the following:
the operating system privileges associated with a fully operational database
admministrator
sufficient memory to start the Oracle instance
sufficient disk storage space for the planned database on the computer that
execcutes Oracle
Using an Initial Database
Depending on your operating system, a database might have been created autom
aticcally as part of the installation procedure for Oracle. You can use this
initial database and customize it to meet your information management requi
rements, or discard it
and create one or more new databases to replace it.
Migrating an Older Version of the Database
If you are using a previous release of Oracle, database creation is required
onlly if you want an entirely new database. Otherwise, you can migrate your
existing Oracle databases managed by a previous version of Oracle and use t
hem with the new version
of the Oracle software.
See Also: Oracle8 Migration manual for information about migrating an existi
ng ddatabase.
For more information about migrating an existing database, see your operatin
g syystem-specific Oracle documentation.
Creating an Oracle Database
This section includes the following topics:
Steps for Creating an Oracle Database
Creating a Database: Example
Troubleshooting Database Creation
Dropping a Database
Steps for Creating an Oracle Database
These steps, which describe how to create an Oracle database, should be foll
owedd in the order presented.
To Create a New Database and Make It Available for System Use
Available for System Use
Back up any existing databases.
Create parameter files.
Edit new parameter files.
Check the instance identifier for your system.
Start Enterprise Manager and connect to Oracle as an administrator.
Start an instance.
Create the database.
Back up the database.
See Also: These steps provide general information about database creation on
alll operating systems. See your operating system-specific Oracle documenta
tion for information about creating databases on your platform.
Step 1: Back up any existing databases.
Oracle Corporation strongly recommends that you make complete backups of all
exiisting databases before creating a new database, in case database creati
on accidentally affects some existing files. Backup should include parameter
files, datafiles, redo
log files, and control files.
Step 2: Create parameter files.
The instance (System Global Area and background processes) for any Oracle da
tabaase is started using a parameter file.
Each database on your system should have at least one customized parameter f
ile that corresponds only to that database. Do not use the same file for sev
eral databases.
To create a parameter file for the database you are about to make, use your
operrating system to make a copy of the parameter file that Oracle provided
on the distribution media. Give this copy a new filename. You can then edit
and customize this new
file for the new database.
file for the new database.
See Also: For more information about copying the parameter file, see your op
eratting system-specific Oracle documentation.
----------------------------------------------------------------------------
----
Note:
In distributed processing environments, Enterprise Manager is often executed
froom a client machine of the network. If a client machine is being used to
execute Enterprise Manager and create a new database, you need to copy the
new parameter file
(currently located on the computer executing Oracle) to your client workstat
ion.. This procedure is operating system dependent. For more information abo
ut copying files among the computers of your network, see your operating sys
tem-specific Oracle
documentation.
----------------------------------------------------------------------------
----
Step 3: Edit new parameter files.
To create a new database, inspect and edit the following parameters of the n
ew pparameter file:
DB_NAME
DB_NAME
DB_DOMAIN
CONTROL_FILES
DB_BLOCK_SIZE
DB_BLOCK_BUFFERS
PROCESSES
ROLLBACK_SEGMENTS
You should also edit the appropriate license parameter(s):
LICENSE_MAX_SESSIONS
LICENSE_SESSION_WARNING
LICENSE_MAX_USERS
Step 4: Check the instance identifier for your system.
If you have other databases, check the Oracle instances identifier. The Orac
le iinstance identifier should match the name of the database (the value of
DB_NAME) to avoid confusion with other Oracle instances that are running con
currently on your system.
See your operating system-specific Oracle documentation for more information
.
Step 5: Start Enterprise Manager and connect to Oracle as an administrator.
Once Enterprise Manager is running, connect to the database as an administra
tor.
See Also: Starting Enterprise Manager is operating system specific; see your
opeerating system-specific Oracle documentation for details.
Step 6: Start an instance.
To start an instance (System Global Area and background processes) to be use
d wiith the new database, use the Startup Database dialog box of Enterprise
Manager. In the Startup Database dialog box, make sure that you have selecte
d the Startup Nomount
radio button.
After selecting the Startup Nomount, the instance starts. At this point, the
re iis no database. Only an SGA and background processes are started in prep
aration for the creation of a new database.
Step 7: Create the database.
To create the new database, use the SQL command CREATE DATABASE, optionally
settting parameters within the statement to name the database, establish max
imum numbers of files, name the files and set their sizes, and so on.
When you execute a CREATE DATABASE statement, Oracle performs the following
operrations:
creates the datafiles for the database
creates the control files for the database
creates the redo log files for the database
creates the SYSTEM tablespace and the SYSTEM rollback segment
creates the data dictionary
creates the users SYS and SYSTEM
specifies the character set that stores data in the database
mounts and opens the database for use
----------------------------------------------------------------------------
----
warning:
Make sure that the datafiles and redo log files that you specify do not conf
lictt with files of another database.
----------------------------------------------------------------------------
----
Step 8: Back up the database.
You should make a full backup of the database to ensure that you have a comp
letee set of files from which to recover if a media failure occurs.
See Also: Oracle8 Backup and Recovery Guide.
For more information about parameter files see "Using Parameter Files".
For information about the CREATE DATABASE command, character sets, and datab
ase creation see Oracle8 SQL Reference.
Creating a Database: Example
The following statement is an example of a CREATE DATABASE statement:
CREATE DATABASE test
DATAFILE 'test_system' SIZE 10M
LOGFILE GROUP 1 ('test_log1a', 'test_log1b') SIZE 500K,
GROUP 2 ('test_log2a', 'test_log2b') SIZE 500K;
The values of the MAXLOGFILES, MAXLOGMEMBERS, MAXDATAFILES, MAXLOGHISTORY, a
nd MMAXINSTANCES options in this example assume the default values, which ar
e operating system-dependent. The database is mounted in the default modes N
OARCHIVELOG and EXCLUSIVE
and then opened.
The items and information in the example statement above result in creating
a daatabase with the following characteristics:
The new database is named TEST.
The SYSTEM tablespace of the new database is comprised of one 10 MB datafile
nammed TEST_SYSTEM.
The new database has two online redo log groups, each containing two 500 KB
membbers.
The new database does not overwrite any existing control files specified in
the parameter file.
----------------------------------------------------------------------------
----
Note:
You can set several limits during database creation. Some of these limits ar
e allso subject to superseding limits of the operating system and can affect
each other. For example, if you set MAXDATAFILES, Oracle allocates enough s
pace in the control file
to store MAXDATAFILES filenames, even if the database has only one datafile
inittially; because the maximum control file size is limited and operating s
ystem-dependent, you might not be able to set all CREATE DATABASE parameters
at their theoretical
maximums.
----------------------------------------------------------------------------
----
See Also: For more information about setting limits during database creation
, seee the Oracle8 SQL Reference.
See your operating system-specific Oracle documentation for information abou
t opperating system limits.
Troubleshooting Database Creation
If for any reason database creation fails, shut down the instance and delete
anyy files created by the CREATE DATABASE statement before you attempt to c
reate it once again.
After correcting the error that caused the failure of the database creation,
retturn to Step 6 of "Creating an Oracle Database".
Dropping a Database
To drop a database, remove its datafiles, redo log files, and all other asso
ciatted files (control files, parameter files, archived log files).
To view the names of the database's datafiles and redo log files, query the
dataa dictionary views V$DATAFILE and V$LOGFILE.
See Also: For more information about these views, see the Oracle8 Reference.
Parameters
As described in Step 3 of the section "Creating an Oracle Database", Oracle
sugggests you alter a minimum set of parameters. These parameters are descri
bed in the following sections:
DB_NAME and DB_DOMAIN
CONTROL_FILES
DB_BLOCK_SIZE
PROCESSES
ROLLBACK_SEGMENTS
License Parameters
DB_BLOCK_BUFFERS
DB_BLOCK_BUFFERS
LICENSE_MAX_SESSIONS_and LICENSE_SESSIONS WARNING
LICENSE_MAX_USERS
DB_NAME and DB_DOMAIN
A database's global database name (name and location within a network struct
ure)) is created by setting both the DB_NAME and DB_DOMAIN parameters before
database creation. After creation, the database's name cannot be easily cha
nged. The DB_NAME
parameter determines the local name component of the database's name, while
the DB_DOMAIN parameter indicates the domain (logical location) within a net
work structure. The combination of the settings for these two parameters sho
uld form a database name
that is unique within a network. For example, to create a database with a gl
oball database name of TEST.US.ACME.COM, edit the parameters of the new para
meter file as follows:
DB_NAME = TEST
DB_DOMAIN = US.ACME.COM
DB_NAME must be set to a text string of no more than eight characters. Durin
g daatabase creation, the name provided for DB_NAME is recorded in the dataf
iles, redo log files, and control file of the database. If during database i
nstance startup the value
of the DB_NAME parameter (of the parameter file) and the database name in th
e coontrol file are not the same, the database does not start.
DB_DOMAIN is a text string that specifies the network domain where the datab
ase is created; 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 di
stributed database
system, pay special attention to this initialization parameter before databa
se ccreation.
See Also: For more information about distributed databases, see Oracle8 Dist
ribuuted Database Systems.
CONTROL_FILES
Include the CONTROL_FILES parameter in your new parameter file and set its v
aluee to a list of control filenames to use for the new database. If you wan
t Oracle to create new operating system files when creating your database's
control files, make sure
that the filenames listed in the CONTROL_FILES parameter do not match any fi
lenaames that currently exist on your system. If you want Oracle to reuse or
overwrite existing files when creating your database's control files, make
sure that the filenames
listed in the CONTROL_FILES parameter match the filenames that currently exi
st.
listed in the CONTROL_FILES parameter match the filenames that currently exi
st.
----------------------------------------------------------------------------
----
warning:
Use extreme caution when setting this option. If you inadvertently specify a
fille that you did not intend and execute the CREATE DATABASE statement, th
e previous contents of that file will be overwritten.
----------------------------------------------------------------------------
----
If no filenames are listed for the CONTROL_FILES parameter, Oracle uses a de
faullt filename.
Oracle Corporation strongly recommends you use at least two control files st
oredd on separate physical disk drives for each database. Therefore, when sp
ecifying the CONTROL_FILES parameter of the new parameter file, follow these
guidelines:
List at least two filenames for the CONTROL_FILES parameter.
Place each control file on a separate physical disk drives by fully specifyi
ng ddrives for each filename.
----------------------------------------------------------------------------
----
Note:
The file specification for control files is operating system-dependent. Rega
rdleess of your operating system, always fully specify filenames for your co
ntrol files.
----------------------------------------------------------------------------
----
When you execute the CREATE DATABASE statement (in Step 7), the control file
s liisted in the CONTROL_FILES parameter of the parameter file will be creat
ed.
See Also: The default filename for the CONTROL_FILES parameter is operating
systtem-dependent. See your operating system-specific Oracle documentation f
or details.
DB_BLOCK_SIZE
The default data block size for every Oracle server is operating system-spec
ificc. The Oracle data block size is typically either 2K or 4K. Generally, t
he default data block size is adequate. In some cases, however, a larger dat
a block size provides
greater efficiency in disk and memory I/O (access and storage of data). Such
casses include:
Oracle is on a large computer system with a large amount of memory and fast
diskk drives. For example, databases controlled by mainframe computers with
vast hardware resources typically use a data block size of 4K or greater.
The operating system that runs Oracle uses a small operating system block si
ze. For example, if the operating system block size is 1K and the data block
size matches this, Oracle may be performing an excessive amount of disk I/O
during normal
operation. For best performance in this case, a database block should consis
t off multiple operating system blocks.
Each database's block size is set during database creation by the initializa
tionn parameter DB_BLOCK_SIZE. The block size cannot be changed after databa
se creation except by re-creating the database. If a database's block size i
s different from the
pt by re-creating the database. If a database's block size is different from
the
have 50 concurrent users, set this parameter to at least 55.
ROLLBACK_SEGMENTS
This parameter is a list of the rollback segments an Oracle instance acquire
s att database startup. List your rollback segments as the value of this par
ameter.
----------------------------------------------------------------------------
----
Attention:
After installation, you must create at least one rollback segment in the SYS
TEM tablespace in addition to the SYSTEM rollback segment before you can cre
ate any schema objects.
----------------------------------------------------------------------------
----
See Also: For more information about how many rollback segments you need, se
e Orracle8 Tuning.
License Parameters
Oracle helps you ensure that your site complies with its Oracle license agre
emennt. If your site is licensed by concurrent usage, you can track and limi
t the number of sessions concurrently connected to an instance. If your site
is licensed by named
users, you can limit the number of named users created in a database. To use
thiis facility, you need to know which type of licensing agreement your sit
e has and what the maximum number of sessions or named users is. Your site m
ight use either type of
licensing (session licensing or named user licensing), but not both.
See Also: For more information about managing licensing, see "Session and Us
er LLicensing".
LICENSE_MAX_SESSIONS_and LICENSE_SESSIONS WARNING
You can set a limit on the number of concurrent sessions that can connect to
a ddatabase on the specified computer. To set the maximum number of concurr
ent sessions for an instance, set the parameter LICENSE_MAX_SESSIONS in the
parameter file that
starts the instance, as shown in the following example:
LICENSE_MAX_SESSIONS = 80
In addition to setting a maximum number of sessions, you can set a warning l
imitt on the number of concurrent sessions. Once this limit is reached, addi
tional users can continue to connect (up to the maximum limit), but Oracle s
ends a warning for each
connecting user. To set the warning limit for an instance, set the parameter
LICCENSE_SESSIONS_WARNING. Set the warning limit to a value lower than LICE
NSE_MAX_SESSIONS.
For instances running with the Parallel Server, each instance can have its o
wn cconcurrent usage limit and warning limit. However, the sum of the instan
ces' limits must not exceed the site's session license.
See Also: For more information about setting these limits when using the Par
alleel Server, see Oracle8 Parallel Server Concepts and Administration.
LICENSE_MAX_USERS
You can set a limit on the number of users created in the database. Once thi
s liimit is reached, you cannot create more users.
----------------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
Note:
This mechanism assumes that each person accessing the database has a unique
userr name and that no people share a user name. Therefore, so that named us
er licensing can help you ensure compliance with your Oracle license agreeme
nt, 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_USER
S paarameter in the database's parameter file, as shown in the following exa
mple:
LICENSE_MAX_USERS = 200
For instances running with the Parallel Server, all instances connected to t
he ssame database should have the same named user limit.
See Also: For more information about setting this limit when using the Paral
lel Server see Oracle8 Parallel Server Concepts and Administration.
Considerations After Creating a Database
After you create a database, the instance is left running, and the database
is oopen and available for normal database use. Use Enterprise Manager to su
bsequently start and stop the database. If more than one database exists in
your database system,
specify the parameter file to use with any subsequent database startup.
If you plan to install other Oracle products to work with this database, see
thee installation instructions for those products; some products require yo
u to create additional data dictionary tables. See your operating system-spe
cific Oracle
documentation for the additional products. Usually, command files are provid
ed tto create and load these tables into the database's data dictionary.
The Oracle Server distribution media can include various SQL files that let
you experiment with the system, learn SQL, or create additional tables, view
s, or synonyms.
A newly created database has only two users, SYS and SYSTEM. The passwords f
or tthese two usernames should be changed soon after the database is created
.
See Also: For more information about the users SYS and SYSTEM see "Database
Admiinistrator Usernames".
For information about changing a user's password see "Altering Users".
Initial Tuning Guidelines
You can make a few significant tuning alterations to Oracle immediately foll
owinng installation. By following these instructions, you can reduce the nee
d to tune Oracle when it is running. This section gives recommendations for
the following
installation issues:
Allocating Rollback Segments
Choosing the Number of DB_BLOCK_LRU_LATCHES
Distributing I/O
Allocating Rollback Segments
Proper allocation of rollback segments makes for optimal database performanc
e. TThe size and number of rollback segments required for optimal performanc
e depends on your application. The Oracle8 Tuning manual contains some gener
al guidelines for
Proper allocation of rollback segments makes for optimal database performanc
e. TThe size and number of rollback segments required for optimal performanc
e depends on your application. The Oracle8 Tuning manual contains some gener
al guidelines for
choosing how many rollback segments to allocate based on the number of concu
rrennt transactions on your Oracle Server. These guidelines are appropriate
for most application mixes.
To create rollback segments, use the CREATE ROLLBACK SEGMENT command.
See Also: For information about the CREATE ROLLBACK SEGMENT command, see the
Oraacle8 SQL Reference.
Choosing Sizes for Rollback Segments
The size of your rollback segment can also affect performance. Rollback segm
ent size is determined by the storage parameters in the CREATE ROLLBACK SEGM
ENT statement. Your rollback segments must be large enough to hold the rollb
ack entries for your
transactions.
See Also: For information about choosing sizes for your rollback segments, s
ee OOracle8 Tuning.
Choosing the Number of DB_BLOCK_LRU_LATCHES
Contention for the LRU latch can impede performance on symmetric multiproces
sor (SMP) machines with a large number of CPUs. The LRU latch controls the r
eplacement of buffers in the buffer cache. For SMP systems, Oracle automatic
ally sets the number of
LRU latches to be one half the number of CPUs on the system. For non-SMP sys
temss, one LRU latch is sufficient.
You can specify the number of LRU latches on your system with the initializa
tiotter DB_BLOCK_LRU_LATCHES. This parameter sets the maximum value for the
desired number of LRU latches. Each LRU latch will control a set of buffers
and Oracle
balances allocation of replacement buffers among the sets.
See Also: For more information on LRU latches, see Oracle8 Tuning.
Distributing I/O
Proper distribution of I/O can improve database performance dramatically. I/
O caan be distributed during installation of Oracle. Distributing I/O during
installation can reduce the need to distribute I/O later when Oracle is run
ning.
There are several ways to distribute I/O when you install Oracle:
redo log file placement
datafile placement
separation of tables and indexes
density of data (rows per data block)
See Also: For information about ways to distribute I/O, see Oracle8 Tuning.
----------------------------------------------------------------------------
----
------------------
--
※ 来源:·BBS 水木清华站 bbs.net.tsinghua.edu.cn·[FROM: 162.105.41.141]
--
海纳百川，
有容乃大，
壁立千尺，
无欲则刚。
※ 来源:·哈工大紫丁香 bbs.hit.edu.cn·[FROM: dip.hit.edu.cn]
Powered by KBS BBS 2.0 (http://dev.kcn.cn)
页面执行时间：1,435.607毫秒