Chapter 4 Creating a Database and Data Dictionary

Creating a Database

Preparing Resources

  Depending on the operating system, you may have to adjust certain configuration parameters. For example, on Unix platforms, you must configure the shared memory parameters.

The following list itemizes the Unix kernel parameters and describes their purposes. The super-user administers these kernel parameters.
SHMMAX    The maximum size of a shared memory segment

SHMMNI The maximum number of shared memory identifiers in the system

SHMSEG The maximum number of shared memory segments to which a user process can attach

SEMMNI  The maximum number of semaphore identifiers in the system.

SHMMAX * SHMSEG The total maximum shared memory that can be allocated

 

Parameters

Oracle uses the parameter file to start up the instance before creating the database. You specify some database configuration values via the parameter file.The following parameters affect database configuration and creation:

CONTROL_FILES    Specifies the control file location(s) for the new database with the full pathname. Specify at least two control files on different disks. You can specify a maximum of eight control file names. Oracle creates these control files when the database is created. Be careful when specifying the control file; if you specify the control file name of an existing database, Oracle could overwrite the control file, which will damage the existing database. If you do not use this parameter, Oracle
uses the default filename, which is operating system dependent. For example:

control_files                      d:\Oracle\oradata\OEMREP\CONTROL01.CTL, d:\Oracle\oradata\OEMREP\CONTROL02.CTL, d:\Oracle\oradata\OEMREP\CONTROL03.CTL

 

 

DB_BLOCK_SIZE Specifies the database block size as a multiple of the operating system block size —this value cannot be changed after the database is created. The default block size is 4KB on most platforms. Oracle allows block sizes from 2KB to 32KB, depending on the operating system.

DB_NAME Specifies the database name—the name cannot be changed easily after the database is created (you must re-create the control file ).The DB_NAME value can be a maximum of eight characters. You can use alphabetic characters, numbers, the underscore (_), the pound symbol (#), and the dollar symbol ($) in the name. No other characters are valid. The first character should be an alphabetic character. Oracle removes double quotation marks before processing the database name. During database creation, the DB_NAME value is recorded in the data files, redo log files, and
control file of the database.
You must at least define the DB_CACHE_SIZE,LOG_BUFFER and SHARED_POOL_SIZE to calculate the SGA, which must fit into real, not virtual, memory.

 

The CREATE DATABASE Command

You create the database using the CREATE DATABASE command. You must start up the instance (with STARTUP NOMOUNT PFILE=) before issuing the command.

CREATE DATABASE “PROD01”
CONTROLFILE REUSE
LOGFILE GROUP 1
(‘/oradata02/PROD01/redo0101.log’,
‘/oradata03/PROD01/redo0102.log) SIZE 5M REUSE,
GROUP 2
(‘/oradata02/PROD01/redo0201.log’,
‘/oradata03/PROD01/redo0202.log) SIZE 5M REUSE
MAXLOGFILES 4
MAXLOGMEMBERS 2
MAXLOGHISTORY 0
MAXDATAFILES 254
MAXINSTANCES 1
NOARCHIVELOG
CHARACTER SET “WE8MSWIN1252”
NATIONAL CHARACTER SET “AL16UTF16”
DATAFILE ‘/oradata01/PROD01/system01.dbf’ SIZE 80M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
UNDO TABLESPACE UNDOTBS
DATAFILE ‘/oradata04/PROD01/undo01.dbf’ SIZE 35M
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE ‘/oradata05/PROD01/temp01.dbf’ SIZE 20M;

 The only mandatory portion in this command is the CREATE DATABASE clause.
 If you omit the database name, Oracle takes the default value from the parameter DB_NAME defined in the initialization parameter file. But the value specified in the parameter file and the database name in this command should be the same.

 

The CONTROLFILE REUSE clause overwrites an existing control file. Normally you use this clause only when re-creating a database. If you omit this clause, and any of the files specified by the CONTROL_FILES parameter exist, Oracle returns an error .

 

The LOGFILE clause specifies the location of the online redo log files . If you omit the GROUP clause, Oracle creates the files specified in separate groups with one member in each. A database must have at least two redo groups. In the example, Oracle creates two redo log groups with two members in each. It is recommended that all redo log groups be the same size. The REUSE clause overwrites an

existing file, if any, provided the sizes are the same.

 

The next five clauses specify limits for the database. The control file size depends on these limits, because Oracle pre-allocates space in the control file. MAXLOGFILES specifies the maximum number of redo log groups that can ever be created in the database . MAXLOGMEMBERS specifies the maximum
number or redo log members (copies of redo log files) for each redo log group. The MAXLOGHISTORY specifies the maximum number of archived redo log files for automatic media recovery.MAXDATAFILES specifies the maximum number of data files that can be created in this database . Data files are created when you create a tablespace or add more space to a tablespace by adding a data file . MAXINSTANCES specifies the maximum number of instances that can simultaneously mount and open this database.   If you want to change any of these limits after the database is created, you must
re-create the control file.

 

The initialization parameter DB_FILES specifies the maximum number of data files accessible to the instance. The MAXDATAFILES clause in the CREATE DATABASE command specifies the maximum number of data files allowed for the database. The DB_FILES parameter cannot specify a value larger than MAXDATAFILES.

 

You can specify NOARCHIVELOG or ARCHIVELOG to configure the redo log archiving. The default is NOARCHIVELOG; you can change the database to ARCHIVELOG mode by using the ALTER DATABASE command after the database is created.

 

The CHARACTER SET clause specifies the character set used to store data. The default is WE8MSWIN1252 on Windows platforms. The character set cannot be changed after database creation. The NATIONAL CHARACTER SET clause specifies the national character set used to store data in columns specifically defined as NCHAR, NCLOB, or NVARCHAR2 . If not specified, the national character set defaults to the database character set.

 

The DEFAULT TEMPORARY TABLESPACE clause defines the tablespace location for all temporary segments. If you create a user without specifying a temporary tablespace, this one is used.

 

Using OMF to Create a Database