Creating a Database

1
4
Creating a Database
2
Objectives

• Preparing the operating system
• Preparing the parameter file
• Creating the database

3
Overview
Instance
SGA
Shared Pool
Database
4
Creation Prerequisites

• A privileged account authenticated in one of
  the following ways
• - By the operating system
• - Using a password file
• Memory to start the instance
• Sufficient disk space for the planned
  database

5
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

6
Oracle Software Locations
/u02/app/applmgr
/product
/admin
/local
7
Oracle Database Files
8
Creating a Database Considerations

• On UNIX
• Created automatically during aninstallation
• Created manually after installation
• On NT
• Created using the Oracle DatabaseAssistant
• Created manually

9
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

10
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

11
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

12
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.l
  og') SIZE 50M

13
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

14
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

15
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

16
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
• Must run other scripts later (catalog.sql,etc.)

17
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.

18
Operating System Environment

• On UNIX set the following environment variables
• ORACLE_HOME
• ORACLE_SID
• ORACLE_BASE
• ORA_NLS 33
• PATH

19
Operating System Environment

• On NT
• Set the variable ORACLE_SID to use SVRMGR30.
• Create the service and the password file with
  ORADIM80.

C\gt ORADIM80 -NEW -SID u16 -INTPWD password
-STARTMODE auto -PFILE ORACLE_HOME\DATABASE\initU
16.ora
20
Preparing the Parameter File

• . Create the new initltSIDgt.ora.

cp init.ora ORACLE_HOME/dbs/initU16.ora

• . Modify the initU16.ora by editing the
  parameters.

21
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 ...
22
Starting the Instance

• . Connect as SYSDBA.
• . Start the instance in NOMOUNT stage.

SVRMGRgt STARTUP NOMOUNT \ 2gt
PFILEinitU16.ora ORACLE instance started.
23
Creating the Database
SPOOL creU16.log STARTUP NOMOUNT
PFILEinitU16.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/log
2b.rdo) SIZE 1 M DATAFILE '/DISK1/system01.dbf'
size 50M autoextend on CHARACTER SET
WE8ISO8859P1
24
Oracle Database Assistant
25
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

26
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)

27
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

28
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!

29
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.

30
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

31
OMF (Cont.)

• Oracle9i Release 2 (9.2) changes
• File Type Naming Convention Example
• Datafile o1_mf_t_u_.dbf
  o1_mf_tbs1_2ixfh90q_.dbf
• Tempfile o1_mf_t_u_.tmp
  o1_mf_temp1_6dygh80r_.tmp
• Redo logfile o1_mf_g_u_.log
  o1_mf_1_wo94n2xi_.log
• Control file o1_mf_u_.ctl
  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 the extension

32
Summary

• Planning the database structure
• Preparing the operating system environment
• Creating the database