Create Database   «Prev  Next»

Lesson 1

Oracle Database Creation

During the course of this module, those of you who are running the Oracle12c database software will create the COIN database that we have been planning. Those who are not running Oracle11g will be able to follow along through the steps, in order to learn the process. Two key steps of the actual creation process are writing and executing the CREATE DATABASE command. After you have created the database, there are several housekeeping tasks that you must take care of before the database is ready for use. These include:
  1. Creating the data dictionary views
  2. Creating the built-in PL/SQL packages
  3. Creating the product user profile
  4. Creating initial tablespaces
  5. Creating rollback segments

When you are done with this module, you will have a completely empty database ready for you to use. If you are not running Oracle, you should still go through this module in order to learn about the process of creating a database. There will be plenty of opportunities for you to participate.

The CREATE DATABASE Command

The database is created using the CREATE DATABASE command. You must start up the instance (with STARTUP NOMOUNT PFILE=) before issuing the command. A sample database creation command is shown below.
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 “US7ASCII”
NATIONAL CHARACTER SET “US7ASCII”
DATAFILE ‘/oradata01/PROD1/system01.dbf’ SIZE 80M
AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED;

Let us discuss the clauses used in the CREATE DATABASE command. 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. The value specified in the parameter file and the database name in this command should be the same. The CONTROLFILE REUSE clause is used to overwrite an existing control file. Normally this clause is used 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.

LOGFILE Clause

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 to have 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 is used only for the Parallel Server configuration. It 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.