Lesson 1

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

The command to create a database in Oracle 19c has evolved from earlier versions, with some syntax changes and new features.
Below is a rewritten version of the Oracle 8 command, adapted for Oracle 19c:

CREATE DATABASE PROD01
USER SYS IDENTIFIED BY "your_sys_password"
USER SYSTEM IDENTIFIED BY "your_system_password"
LOGFILE GROUP 1 (
  '/oradata02/PROD01/redo0101.log',
  '/oradata03/PROD01/redo0102.log'
) SIZE 50M,
GROUP 2 (
  '/oradata02/PROD01/redo0201.log',
  '/oradata03/PROD01/redo0202.log'
) SIZE 50M
MAXLOGFILES 4
MAXLOGMEMBERS 2
MAXLOGHISTORY 1
MAXDATAFILES 254
MAXINSTANCES 1
NOARCHIVELOG
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
DATAFILE '/oradata01/PROD01/system01.dbf' SIZE 800M
AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;

-- Additional files typically created with a new database:
DATAFILE '/oradata01/PROD01/sysaux01.dbf' SIZE 500M
AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;
DATAFILE '/oradata01/PROD01/undotbs01.dbf' SIZE 200M
AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;
TEMPFILE '/oradata01/PROD01/temp01.dbf' SIZE 100M
AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;

Key Updates:

1. Character Set: The default character set for Oracle 19c is typically `AL32UTF8`, which is a more modern and widely compatible encoding. The national character set is often `AL16UTF16`.
2. Password Requirement: Oracle 19c requires you to specify passwords for the SYS and SYSTEM users during database creation.
3. System, SYSAUX, Undo, and Temp Tablespaces: Oracle 19c databases include additional tablespaces such as `SYSAUX`, `UNDO`, and `TEMP` by default. These are crucial for database operation.
4. Control File Management: In Oracle 19c, control file reuse is usually handled automatically. The syntax `CONTROLFILE REUSE` is not necessary unless you're specifically reusing an existing control file, which is a rare case for new databases.
5. Sizes: Sizes of log files and data files have been increased to match modern requirements. Adjust these according to your specific needs.

This script is a basic example and may need adjustments based on your environment, such as ASM usage, file locations, and specific database options.

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.