Transcript Lecture 4 - bhecker.com

4
Creating a Database
Objectives
• Preparing the operating system
• Preparing the parameter file
• Creating the database
4-2
Overview
Instance
SGA
User
process
Shared Pool
Server
process
PGA
Control
files
Parameter
file
Redo log
files
Datafiles
Password
file
4-3
Database
Creation Prerequisites
• A privileged account authenticated in
one of the following ways:
- By the operating system
- Using a password file
•
•
4-4
Memory to start the instance
Sufficient disk space for the planned
database
Planning Database File Locations
• Keep at least two active copies of a database
control file on at least two different devices.
• Multiplex the redo log files and put group
members on different disks.
• Separate data files whose data:
– Will participate in disk resource contention
across different physical disk resources
– Have different life-spans
– Have different administrative characteristics
4-5
Oracle Software Locations
/u01/app/oracle
/product
/u02/app/applmgr
/product
/8.0.3
/bin
/dbs
/orainst
/sqlplus
...
4-6
/7.3.3
/admin
/admin
/local
/local
Oracle Database Files
/u03/
/u02/
oradata/
db01/
system01.dbf
control01.ctl
redo0101.rdo
...
db02/
system01.dbf
control01.ctl
redo0101.rdo
...
4-7
oradata/
db01/
tools01.dbf
control02.ctl
redo0102.rdo
...
db02/
users01.dbf
control02.ctl
redo0102.rdo
...
Creating a Database: Considerations
• On UNIX:
– Created automatically during an
installation
– Created manually after installation
• On NT:
– Created using the Oracle Database
Assistant
– Created manually
4-8
File Management Methods
Files controlled by the file management method
are control files, data files, and redo log files
Determines how files are located to help with:
• Multiplexing of control and redo log files
• I/O load balancing
Determines how files are added, extended, or
deleted
4-9
User-Managed File Management
Type of File Management (first of two methods)
Pros:
• DBA has complete control of names, locations,
and sizes of all files
• Compatible with older versions
Cons:
• DBA must manually delete files after their
associated tablespace is dropped
• DBA must monitor and adjust file sizes over
time
4-10
User-Managed File Management
How to implement:
• For user-managed control files, set
CONTROL_FILES to a list of files. For example:
• For user-managed redo log files, use the LOGFILE
clause in the CREATE DATABASE command.
• For user-managed data files, use the DATAFILE
clause in the CREATE DATABSE command or the
CREATE TABLESPACE command
4-11
User-Managed File Management
Example
Initialization parameter:
CONTROL_FILES = (/d1/oracle/control01.ctl, /d2/oracle/control02.ctl)
CREATE DATABASE command:
CREATE DATABASE TECHNO92
MAXDATAFILES 100
DATAFILE ‘C:\ora\oradata\system01.dbf' SIZE 325M
AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
LOGFILE GROUP 1 (‘C:\ora\oralogs\redo01.log') SIZE 50M,
GROUP 2 (‘D:\ora\oralogs\redo02.log') SIZE 50M;
4-12
Oracle Managed File Management
Type of File Management (second of two methods)
Pros:
• Automated control of control of names and sizes of
all files
• DBA only has to determine the locations
• Less monitoring required due to automated size
adjustment and deleting of appropriate files
Cons:
• File names can be somewhat cryptic
• No control over exact sizes and names of files
4-13
Oracle Managed File Management
How to implement:
• For user-managed data files, set the
DB_CREATE_FILE_DEST to a valid directory
• For user-managed control files and redo log
files, set DB_CREATE_ONLINE_LOG_DEST_n
to a valid directory
• When the database is created, insert the
control_files parameter back into the init.ora
file so that db can be started and stopped later
4-14
Oracle Managed File
Management
Example
Initialization parameters:
DB_CREATE_FILE_DEST = ‘C:\ora\oradata'
DB_CREATE_ONLINE_LOG_DEST_1= ‘C:\ora\oralogs’
DB_CREATE_ONLINE_LOG_DEST_2= ‘D:\ora\oralogs’
CREATE DATABASE command:
CREATE DATABASE TECHNO92
MAXDATAFILES 100;
4-15
Creating a Database
Two distinct methods:
• Automated: Database Configuration Assistant
– Better for novice DBA
– Will create a parameter file for you
– May not be able to use OFM methods
– Easier to use due to the many pre-defined settings
• Manual: CREATE DATABASE command
– More flexible
– Useful when using script for creating multiple
identical (or similar) databases on several sites
– Can work off of a parameter file
–4-16Must run other scripts later (catalog.sql,etc.)
Creating a Database Manually
1. Decide on a unique instance and
database name and database character
set.
2. Set the operating system variables.
3. Prepare the parameter file.
4. Create a password file (recommended).
5. Start the instance.
6. Create the database.
7. Run scripts to generate the data dictionary
and accomplish postcreation steps.
4-17
Operating System Environment
On UNIX set the following environment
variables:
• ORACLE_HOME
• ORACLE_SID
• ORACLE_BASE
• ORA_NLS 33
• PATH
4-18
Operating System Environment
On NT
• Set the variable ORACLE_SID to use
SVRMGR30.
• Create the service and the password
file with ORADIM80.
C:\> ORADIM80 -NEW -SID u16
-INTPWD password -STARTMODE auto
-PFILE ORACLE_HOME\DATABASE\initU16.ora
4-19
Preparing the Parameter File
1. Create the new init<SID>.ora.
$cp init.ora $ORACLE_HOME/dbs/initU16.ora
2. Modify the initU16.ora by editing
the parameters.
4-20
Editing the Parameter File
db_name = U16
db_files = 100
# db_files = 400 # MEDIUM
# db_files = 1000 # LARGE
db_file_multiblock_read_count = 8
# db_file_multiblock_read_count = 16 # MEDIUM
# db_file_multiblock_read_count = 32 # LARGE
control_files = (/disk1/control01.con,/disk2/control02.con)
db_block_size = 8192
db_block_buffers = 2000
# SMALL
# db_block_buffers = 550 # MEDIUM
# db_block_buffers = 3200 # LARGE
shared_pool_size = 30000000
# shared_pool_size = 5000000 # MEDIUM
# shared_pool_size = 9000000 # LARGE
log_buffer = 65536
# log_buffer = 32768 # MEDIUM
# log_buffer = 163840 # LARGE
...
4-21
Starting the Instance
1. Connect as SYSDBA.
2. Start the instance in NOMOUNT stage.
SVRMGR> STARTUP NOMOUNT \
2> PFILE=initU16.ora
ORACLE instance started.
4-22
Creating the Database
SPOOL creU16.log
STARTUP NOMOUNT PFILE=initU16.ora
CREATE DATABASE U16
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXLOGHISTORY 100
LOGFILE
GROUP 1 ('/DISK3/log1a.rdo',/DISK4/log1b.rdo’) SIZE 1 M,
GROUP 2 ('/DISK3/log2a.rdo',/DISK4/log2b.rdo’) SIZE 1 M
DATAFILE
'/DISK1/system01.dbf' size 50M autoextend on
CHARACTER SET WE8ISO8859P1;
4-23
Oracle Database Assistant
4-24
Troubleshooting
Creation of the database fails if:
• There are syntax errors in the
SQL script
• Files that should be created
already exist
• Operating system errors such as file or
directory permission or insufficient
space errors occur
4-25
After Creation of the Database
The database contains:
• Data files which make up the SYSTEM
tablespace
• Control files and redo log files
• User SYS/change_on_install
• User SYSTEM/manager
• Rollback segment SYSTEM
• Internal tables (but no data
dictionary views)
4-26
OMF
• Oracle-Managed Files
• feature introduced in Oracle9i
• Allows Oracle RDBMSto manage datafiles for
you. Oracle has been making significant strides
in making the database easier to manage and
OMF falls into this category of features.
•For example, in Oracle databases prior to 9i,
when you dropped a tablespace, you would also
have to remove the physical datafile associated
with that tablespace. With Oracle9i, you can leave
physical file management to the database itself
by using OMFs
4-27
OMF (Cont.)
• Very useful in low-use / smaller databases in order to
reduce the administrative overhead.
• OMF reduces the overall administrative overhead
required for such smaller databases.
•OMF feature can be particularly useful for development
and test databases.
•OMF simplifies management of a standby database. In
pre-Oracle9i databases, when you added a tablespace or
datafile to the primary database, human intervention was
required on the standby database to perform the same
operation.
•With OMF, iff the standby database is configured to use
OMF, then the creation of a tablespace or addition of a
datafile to the primary database will result in the
automated creation of that tablespace or datafile on the
standby server. No other administrative activity is
required!
4-28
OMF (Cont.)
• OMF is also useful in a large database environment
that is using large disk arrays. (i.e. RAID-0).
•OMF is not an appropriate choice for use with a
high-volume or mission-critical database that is not
using high-end striped disk arrays.
•For example, OMF is not recommended on systems
with many smaller file systems, or systems running
RAID-5. This is because the nature of managed
datafiles is such that the DBA is not able to
distribute I/O as required.
•Also, the managed datafile feature does not
support the use of raw disk devices.
4-29
OMF (Cont.)
• Example Create Database Command:
CREATE DATABASE mydb
DATAFILE SIZE 500M
LOGFILE
GROUP 1 SIZE 10M , GROUP 2 SIZE 10M
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE SIZE 100M
UNDO TABLESPACE undotbs1
DATAFILE SIZE 50M
MAXLOGFILES = 5 MAXLOGMEMBERS = 5
MAXDATAFILES = 600
NOARCHIVELOG;
4-30
OMF (Cont.)
• Oracle9i Release 2 (9.2) changes:
File Type
Naming Convention
Datafile
Tempfile
Redo logfile
Control file
o1_mf_%t_%u_.dbf
o1_mf_%t_%u_.tmp
o1_mf_%g_%u_.log
o1_mf_%u_.ctl
Example
o1_mf_tbs1_2ixfh90q_.dbf
o1_mf_temp1_6dygh80r_.tmp
o1_mf_1_wo94n2xi_.log
o1_mf_cmr7t90p_.ctl
Where:
%t is the tablespace name (possibly truncated)
%u is an eight character string that guarantees
uniqueness
%g is the online redo log file group number
A file is now considered OMF if its base file name has: - a
"o1_mf_" prefix - and a ".dbf", ".tmp", ".log", or ".ctl"
extension - and an "_" character immediately preceding
4-31
the extension
Summary
• Planning the database structure
• Preparing the operating system
environment
• Creating the database
4-32