Oracle DBA Lesson 25A — How Manual Creation Builds a CDB

DBCA builds a database from the choices you make. Manual creation uses a CREATE DATABASE statement instead. An instance is memory and background processes. The database is the files. Starting the instance does not create those files.

Oracle DBA Lesson 25A — How Manual Creation Builds a CDB

Start in NOMOUNT

A new database has no control file yet, so there is nothing to mount. Start the instance with a prepared text parameter file. This is Oracle Database 19c, one instance, SQL*Plus, and a new SID named APPCDB.

STARTUP NOMOUNT PFILE='/u01/app/oracle/create/initAPPCDB.ora'

SELECT instance_name, status
FROM v$instance;

STATUS is STARTED. INSTANCE_NAME is APPCDB when that SID was selected. The instance is running. The database is not open. Do not query V$DATABASE here. There is no database yet. Starting up and shutting down. V$INSTANCE.

Settings the statement reads

These lines are excerpts from the PFILE, not a complete PFILE.

db_name='APPCDB'
enable_pluggable_database=true
db_create_file_dest='/u01/app/oracle/oradata'

• DB_NAME must match the name in CREATE DATABASE. APPCDB is the database name, within Oracle's eight-character limit. It is not a PDB name and not a service name. DB_NAME.
• ENABLE_PLUGGABLE_DATABASE=TRUE lets the instance create a CDB. The SQL statement still needs its own ENABLE PLUGGABLE DATABASE clause. The parameter and the clause are not substitutes for each other. Creating a CDB.
• DB_CREATE_FILE_DEST turns on Oracle Managed Files for this example. You choose the directory. Oracle names the files under it. The directory must already exist and be writable by the Oracle software owner. Oracle Managed Files.

This PFILE does not set CONTROL_FILES, DB_CREATE_ONLINE_LOG_DEST_n, or DB_RECOVERY_FILE_DEST. Control files and online redo logs go to the same managed-file directory. That is one directory, not a multiplexed layout. Put control files, redo, and the recovery area on separate storage when the database is not a throwaway practice.

The creation statement

Replace both password placeholders before you run this. The statement has no REUSE clauses. It follows Oracle's 19c managed-file CDB example.

CREATE DATABASE APPCDB
  USER SYS IDENTIFIED BY ""
  USER SYSTEM IDENTIFIED BY ""
  CHARACTER SET AL32UTF8
  NATIONAL CHARACTER SET AL16UTF16
  EXTENT MANAGEMENT LOCAL
  DEFAULT TABLESPACE users
  DEFAULT TEMPORARY TABLESPACE temp
  UNDO TABLESPACE undotbs1
  ENABLE PLUGGABLE DATABASE
    SEED
    SYSTEM DATAFILES SIZE 125M
      AUTOEXTEND ON NEXT 10M MAXSIZE 1G
    SYSAUX DATAFILES SIZE 100M
  LOCAL UNDO ON;

Clause	What it does
CREATE DATABASE APPCDB	Creates that database with the instance already running in NOMOUNT
Managed-file destination, no file names	Oracle creates and names the control file, online redo logs, and data files
CHARACTER SET AL32UTF8	Database character set
NATIONAL CHARACTER SET AL16UTF16	Character set for national character types
EXTENT MANAGEMENT LOCAL	Locally managed SYSTEM. This is not local undo
DEFAULT TABLESPACE users	Default permanent tablespace in the root for ordinary data
DEFAULT TEMPORARY TABLESPACE temp	Default temporary tablespace in the root
UNDO TABLESPACE undotbs1	Undo tablespace for the root
ENABLE PLUGGABLE DATABASE	Creates CDB$ROOT and PDB$SEED. It does not create an application PDB
SEED ... SYSTEM ... SYSAUX	Size of the seed's files, not the root's files
LOCAL UNDO ON	Each container gets its own undo. Omit this clause in 19c manual creation and Oracle uses shared undo

A tablespace is a logical storage area. Permanent tablespaces use data files. Temporary tablespaces use tempfiles. SYSTEM holds the data dictionary. SYSAUX holds data for other Oracle components. USERS, TEMP, and undo do not replace SYSTEM. Creating a database. CREATE DATABASE.

With Oracle Managed Files, seed file names are generated. You do not need FILE_NAME_CONVERT for this design. The seed's SYSTEM and SYSAUX sizes can differ from the root's.

The seed SYSTEM file starts at 125M, grows by 10M, and stops at 1G. That is one file. Seed SYSAUX starts at 100M with no autoextend clause in this statement. Root file sizes and redo sizes that the statement does not name use Oracle defaults. Set sizes, growth, and redundancy before you run a creation script on anything you intend to keep.

Root, seed, and what is missing

CDB$ROOT manages the CDB. PDB$SEED is the starting point for new PDBs. The seed has its own SYSTEM and SYSAUX files. LOCAL UNDO ON gives each container its own undo.

An application PDB is a separate CREATE PLUGGABLE DATABASE. This statement does not create one.

After the statement succeeds

A successful CREATE DATABASE creates the files, mounts the database, and opens it. You do not issue ALTER DATABASE OPEN just to finish that statement. The instance does not stay in NOMOUNT.

Open is not the end of manual creation. Data dictionary views, standard packages, and CDB components still have to be installed with the scripts that match this Oracle release. That is the next step. An open database does not mean those scripts have run. CDB creation.

Before you run it

Use a new disposable 19c instance and empty storage. Do not run this against a database that already exists. Confirm the Oracle home, the SID, OS permissions, memory settings, and that the OMF directory is empty and owned by the Oracle software owner. Connect as SYSDBA.

If creation fails, keep the error and read the alert log. Find any files that were created, and who owns them, before you delete anything. Do not add REUSE and run the statement again to get past an error you have not identified. For a NOMOUNT-only practice, shut down that instance or revert the VM.

Quiz

1. Why does new database creation start in NOMOUNT?

NOMOUNT creates the application PDB. The instance must run before Oracle creates its control file. NOMOUNT validates every installed component. The database is already open in NOMOUNT.

2. Which name must match APPCDB in the creation statement?

The temporary tablespace name. The application service name. The DB_NAME initialization setting. The seed's file name.

3. What does ENABLE PLUGGABLE DATABASE create here?

The root and seed. Every application PDB the application might need. Only the memory and background processes. Only the listener.

4. Which clause explicitly selects local undo?

DEFAULT TABLESPACE users. EXTENT MANAGEMENT LOCAL. CHARACTER SET AL32UTF8. LOCAL UNDO ON.

5. What do the SYSTEM DATAFILES clauses beneath SEED affect?

All application tables automatically. The root's SYSTEM file only. The seed's SYSTEM file properties. Every redo log group.

6. What is true immediately after a successful CREATE DATABASE?

The database is mounted and open, but dictionary completion remains. The instance stays in NOMOUNT. Every application PDB has been created and tested. All storage defaults have been proven suitable for the workload.

AIOps Pro

https://www.apps-dba.com/ https://x.com/AIOpsPro https://www.youtube.com/@AIOpsPro