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.

Here's a concise and structured explanation of the clauses used in the `CREATE DATABASE` command in Oracle, aligned with Oracle 23c standards:
Clauses in the `CREATE DATABASE` Command
When issuing the `CREATE DATABASE` command in Oracle, several optional and mandatory clauses can be used to define the characteristics of the new database. Among these:

1. CREATE DATABASE
   • Mandatory clause.
   • Syntax:

     CREATE DATABASE database_name;
             

   • If the `database_name` is omitted, Oracle uses the value of the `DB_NAME` parameter from the initialization parameter file (`init.ora` or `spfile.ora`).
   • Important: The value provided in the command should match the `DB_NAME` parameter exactly.
2. CONTROLFILE REUSE
   • Optional clause.
   • Used when control files specified by the `CONTROL_FILES` parameter already exist.
   • It prevents Oracle from throwing an error if the files already exist by reusing (i.e., overwriting) them.
   • This is typically used during database recreation, such as after restoring from a backup or when reinitializing a database instance.

   • CREATE DATABASE mydb
     CONTROLFILE REUSE;
             

   • Without this clause, if the control files already exist, Oracle will halt with an error to prevent accidental data loss.

Here is an expanded and structured overview of commonly used clauses in the `CREATE DATABASE` command, including `MAXLOGFILES`, `MAXDATAFILES`, `CHARACTER SET`, and `UNDO TABLESPACE`, in the context of Oracle 23c:
Clauses in the `CREATE DATABASE` Command

1. `CREATE DATABASE`
   • Required clause.
   • Specifies the name of the database.
   • If omitted, Oracle uses the `DB_NAME` parameter from the initialization parameter file.
   • Syntax Example:

     CREATE DATABASE mydb;
                     

2. CONTROLFILE REUSE
   • Optional clause.
   • Reuses existing control files if specified in the `CONTROL_FILES` parameter.
   • Prevents Oracle from throwing an error if control files already exist.
   • Use case: Typically included when re-creating a database using pre-existing control files.
3. MAXLOGFILES
   • Sets the maximum number of redo log groups that can ever be created for the database.
   • Default: platform-specific (often 16 or 32).
   • Cannot be changed after database creation without recreating the control file.
   • Syntax:

     MAXLOGFILES 32
                     

4. MAXLOGMEMBERS
   • Defines the maximum number of members (copies) per redo log group.
   • Helps with redundancy of redo logs for fault tolerance.
   • Syntax:

     MAXLOGMEMBERS 3
                     

MAXDATAFILES
• Sets the upper limit on the number of datafiles that can be associated with the database.
• Affects the size of the control file.
• Must be large enough to accommodate future growth.
• Syntax:

  MAXDATAFILES 100
                  

CHARACTER SET

• Specifies the database character set used to store text data.
• Should be chosen carefully, as changing it after creation is complex and disruptive.
• Examples:
  • `AL32UTF8` (recommended for full Unicode support)
  • `WE8MSWIN1252` (Western European Windows character set)
• Syntax:

  CHARACTER SET AL32UTF8
                  

NATIONAL CHARACTER SET

• Defines the character set used for `NCHAR`, `NVARCHAR2`, and `NCLOB` datatypes.
• Typically set to `AL16UTF16`.
• Syntax:

  NATIONAL CHARACTER SET AL16UTF16
                  

UNDO TABLESPACE

• Specifies the name of the undo tablespace to be used for managing undo data (for consistent reads and transaction rollback).
• The undo tablespace must be created separately (or automatically if `CREATE DATABASE` includes a `DEFAULT UNDO TABLESPACE` clause).
• Syntax:

  UNDO TABLESPACE undotbs1
                  

Full Example:

CREATE DATABASE mydb
   USER SYS IDENTIFIED BY MySysPass
   USER SYSTEM IDENTIFIED BY MySystemPass
   CONTROLFILE REUSE
   MAXLOGFILES 32
   MAXLOGMEMBERS 3
   MAXDATAFILES 100
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   UNDO TABLESPACE undotbs1;

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.