Create an Oracle Database 19c using SQL

As an other method to create an Oracle database like the tool DBCA in graphical method or in silence method, we can use the SQL commandes to create an Oracle database,

Steps:

1. Create PFILE
2. Create a database with SQL
3. Run the SQL Catalog sctipt

1. Create PFILE

In my case just a simple pfile with thre parameters;

Note: Make sure you create all needed directories,

$ cat initcdbtest.ora
*.control_files='/u01/app/oracle/oradata/cdbtest/controlfile01.ctl','/u01/app/oracle/orafra/cdbtest/controlfile02.ctl'
*.db_name=cdbtest
*.enable_pluggable_database=true

2. Create a database with SQL:

We need to start the instance in NOMOUNT state, and specify the:

• Database Name
• Password username sys & system
• LogFile locations
• Datafiles locations
• CharacterSet:

With SQL*Plus

[oracle@admin19c ~]$ . oraenv
ORACLE_SID = [oracle] ? cdbtest
The Oracle base has been set to /u01/app/oracle
[oracle@admin19c ~]$
[oracle@admin19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sat October 28  09:47:44 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  318767000 bytes
Fixed Size                  9133976 bytes
Variable Size             251658240 bytes
Database Buffers           50331648 bytes
Redo Buffers                7643136 bytes
SQL> CREATE DATABASE cdbtest USER SYS IDENTIFIED BY PASSWORD USER SYSTEM IDENTIFIED BY PASSWORD
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/cdbtest/redo1a.log','/u01/app/oracle/orafra/cdbtest/redo1b.log') SIZE 200M,
        GROUP 2 ('/u01/app/oracle/oradata/cdbtest/redo2a.log','/u01/app/oracle/orafra/cdbtest/redo2b.log') SIZE 200M
CHARACTER SET AL32UTF8 NATIONAL CHARACTER SET AL16UTF16 EXTENT MANAGEMENT LOCAL DATAFILE
'/u01/app/oracle/oradata/cdbtest/system01.dbf' SIZE 800M
SYSAUX DATAFILE '/u01/app/oracle/oradata/cdbtest/sysaux01.dbf' SIZE 400M
DEFAULT TEMPORARY TABLESPACE tempts TEMPFILE '/u01/app/oracle/oradata/cdbtest/temp01.dbf' SIZE 20M
UNDO TABLESPACE undotbs DATAFILE '/u01/app/oracle/oradata/cdbtest/undotbs01.dbf' SIZE 400M
ENABLE PLUGGABLE DATABASE SEED FILE_NAME_CONVERT =('/u01/app/oracle/oradata/cdbtest','/u01/app/oracle/oradata/cdbtest/seed');
Database created.

3. Run the SQL Catalog sctipt with SQL*Plus :

During the execution of the script, we need

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL> @$ORACLE_HOME/rdbms/admin/catcdb.sql
....
....
SQL> host perl -I &&rdbms_admin &&rdbms_admin_catcdb --logDirectory &&1 --logFilename &&2
Enter value for 1: /tmp
Enter value for 2: create_cdbtest.log

Note: If you have this error: Can’t locate Term/ReadKey.pm

Can’t locate Term/ReadKey.pm

Enjoy!