Creating a CDB Without Using Oracle Managed Files

The following procedure below aims to create an Oracle 12c database by command line with only the CDB by enabling the ENABLE_PLUGGABLE_DATABASE = TRUE parameter for a possible creation of PDBs in the future.
Therefore, it follows the procedures:

Creating database directories and granting permissions to the oracle user:

mkdir /u01/app/oracle/oradata
mkdir /u01/app/oracle/oradata/newcdb
mkdir /u01/app/oracle/oradata/pdbseed
mkdir /u01/logs/
mkdir /u01/logs/my
mkdir /u02/
mkdir /u02/logs/
mkdir /u02/logs/my
mkdir /u01/app/oracle/flash_recovery_area
mkdir /u01/app/oracle/control01
mkdir /u02/control02
chown oracle:oinstall  /u01/app/oracle/oradata
chown  oracle:oinstall /u01/app/oracle/oradata/newcdb
chown  oracle:oinstall /u01/app/oracle/oradata/pdbseed
chown oracle:oinstall  /u01/logs/
chown oracle:oinstall  /u01/logs/my
chown oracle:oinstall  /u02/
chown oracle:oinstall  /u02/logs/
chown oracle:oinstall  /u02/logs/my
chown oracle:oinstall  /u01/app/oracle/flash_recovery_area
chown oracle:oinstall /u01/app/oracle/control01
chown oracle:oinstall /u02/control02

Creating the database pfile with the ENABLE_PLUGGABLE_DATABASE parameter set to TRUE, this way it will be possible to create PDBs in the future:


[oracle@dbdg dbs]$ cat initnewcbd.ora
##############################################################################
# Example INIT.ORA file
#
# This file is provided by Oracle Corporation to help you start by providing
# a starting point to customize your RDBMS installation for your site.
#
# NOTE: The values that are used in this file are only intended to be used
# as a starting point. You may want to adjust/tune those values to your
# specific hardware and needs. You may also consider using Database
# Configuration Assistant tool (DBCA) to create INIT file and to size your
# initial set of tablespaces based on the user input.
###############################################################################

# Change '<ORACLE_BASE>' to point to the oracle base (the one you specify at
# install time)

db_name='newcdb'
processes = 150
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (/u01/app/oracle/control01/ora_control1, /u02/control02/ora_control2)
compatible ='12.0.0'
ENABLE_PLUGGABLE_DATABASE=TRUE

Setting the environment variable and initializing the database in NOMOUNT status:


[oracle@dbdg dbs]$ . oraenv
ORACLE_SID = [oracle] ? newcdb
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.1.0/dbhome_1/
The Oracle base has been set to /u01/app/oracle

[oracle@dbdg dbs]$ ls -ltr
total 8
-rw-r--r-- 1 oracle oinstall 2992 Feb  3  2012 init.ora
-rw-r--r-- 1 oracle oinstall 1291 Feb 10 12:30 initnewcbd.ora
[oracle@dbdg dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Sat Feb 10 12:32:24 2018

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

Connected to an idle instance.

SQL>; startup nomount pfile=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initnewcbd.ora
ORACLE instance started.

Total System Global Area  222298112 bytes
Fixed Size            2922760 bytes
Variable Size          163579640 bytes
Database Buffers       50331648 bytes
Redo Buffers            5464064 bytes
SQL&gt; select instance_name,status from v$instance; 

INSTANCE_NAME     STATUS
---------------- ------------
newcdb         STARTED

Creation of the database:


[oracle@dbdg dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Sat Feb 10 12:42:54 2018

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> CREATE DATABASE newcdb
  USER SYS IDENTIFIED BY sys_password
  USER SYSTEM IDENTIFIED BY system_password
  LOGFILE GROUP 1 ('/u01/logs/my/redo01a.log','/u02/logs/my/redo01b.log')
             SIZE 100M BLOCKSIZE 512,
          GROUP 2 ('/u01/logs/my/redo02a.log','/u02/logs/my/redo02b.log')
             SIZE 100M BLOCKSIZE 512,
          GROUP 3 ('/u01/logs/my/redo03a.log','/u02/logs/my/redo03b.log')
             SIZE 100M BLOCKSIZE 512
  MAXLOGHISTORY 1
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXDATAFILES 1024
  CHARACTER SET AL32UTF8
  NATIONAL CHARACTER SET AL16UTF16
  EXTENT MANAGEMENT LOCAL
  DATAFILE '/u01/app/oracle/oradata/newcdb/system01.dbf'
    SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
  SYSAUX DATAFILE '/u01/app/oracle/oradata/newcdb/sysaux01.dbf'
    SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
  DEFAULT TABLESPACE deftbs
     DATAFILE '/u01/app/oracle/oradata/newcdb/deftbs01.dbf'
     SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
  DEFAULT TEMPORARY TABLESPACE tempts1
     TEMPFILE '/u01/app/oracle/oradata/newcdb/temp01.dbf'
     SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
  UNDO TABLESPACE undotbs1
     DATAFILE '/u01/app/oracle/oradata/newcdb/undotbs01.dbf'
     SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
  ENABLE PLUGGABLE DATABASE
    SEED
    FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/newcdb/',
                         '/u01/app/oracle/oradata/pdbseed/')
    SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
    SYSAUX DATAFILES SIZE 100M
  USER_DATA TABLESPACE usertbs
    DATAFILE '/u01/app/oracle/oradata/pdbseed/usertbs01.dbf'
    SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38 

Database created.

Run the catcdb.sql SQL script. This script installs all of the components required by a CDB:

With SYSDBA administrative privilege:

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catproc.sql
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/catcdb.sql

With SYSTEM:

@?/sqlplus/admin/pupbld.sql

O seguinte procedimento abaixo visa a criação de um banco de dados Oracle 12c através de comandos SQL somente com o CDB habilitando o parâmetro ENABLE_PLUGGABLE_DATABASE=TRUE para uma possível criação de PDBs no futuro.
Sendo assim, segue os procedimentos:

Criação dos diretórios do banco de dados e conceder as permissões para o usuário oracle:

mkdir /u01/app/oracle/oradata
mkdir /u01/app/oracle/oradata/newcdb
mkdir /u01/app/oracle/oradata/pdbseed
mkdir /u01/logs/
mkdir /u01/logs/my
mkdir /u02/
mkdir /u02/logs/
mkdir /u02/logs/my
mkdir /u01/app/oracle/flash_recovery_area
mkdir /u01/app/oracle/control01
mkdir /u02/control02
chown oracle:oinstall  /u01/app/oracle/oradata
chown  oracle:oinstall /u01/app/oracle/oradata/newcdb
chown  oracle:oinstall /u01/app/oracle/oradata/pdbseed
chown oracle:oinstall  /u01/logs/
chown oracle:oinstall  /u01/logs/my
chown oracle:oinstall  /u02/
chown oracle:oinstall  /u02/logs/
chown oracle:oinstall  /u02/logs/my
chown oracle:oinstall  /u01/app/oracle/flash_recovery_area
chown oracle:oinstall /u01/app/oracle/control01
chown oracle:oinstall /u02/control02

Criação do pfile do banco de dados com o parâmetro ENABLE_PLUGGABLE_DATABASE configurado para TRUE, assim será possível a criação de PDBs no futuro:


[oracle@dbdg dbs]$ cat initnewcbd.ora
##############################################################################
# Example INIT.ORA file
#
# This file is provided by Oracle Corporation to help you start by providing
# a starting point to customize your RDBMS installation for your site.
#
# NOTE: The values that are used in this file are only intended to be used
# as a starting point. You may want to adjust/tune those values to your
# specific hardware and needs. You may also consider using Database
# Configuration Assistant tool (DBCA) to create INIT file and to size your
# initial set of tablespaces based on the user input.
###############################################################################
 
# Change '<ORACLE_BASE>' to point to the oracle base (the one you specify at
# install time)
 
db_name='newcdb'
processes = 150
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest='/u01/app/oracle'
dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
# You may want to ensure that control files are created on separate physical
# devices
control_files = (/u01/app/oracle/control01/ora_control1, /u02/control02/ora_control2)
compatible ='12.0.0'
ENABLE_PLUGGABLE_DATABASE=TRUE

Configuração da variável de ambiente e inicializando o banco de dados em NOMOUNT:


[oracle@dbdg dbs]$ . oraenv
ORACLE_SID = [oracle] ? newcdb
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.1.0/dbhome_1/
The Oracle base has been set to /u01/app/oracle

[oracle@dbdg dbs]$ ls -ltr
total 8
-rw-r--r-- 1 oracle oinstall 2992 Feb  3  2012 init.ora
-rw-r--r-- 1 oracle oinstall 1291 Feb 10 12:30 initnewcbd.ora
[oracle@dbdg dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Sat Feb 10 12:32:24 2018

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

Connected to an idle instance.

SQL> startup nomount pfile=/u01/app/oracle/product/12.1.0/dbhome_1/dbs/initnewcbd.ora
ORACLE instance started.

Total System Global Area  222298112 bytes
Fixed Size            2922760 bytes
Variable Size          163579640 bytes
Database Buffers       50331648 bytes
Redo Buffers            5464064 bytes
SQL> select instance_name,status from v$instance; 

INSTANCE_NAME     STATUS
---------------- ------------
newcdb         STARTED

Criação do banco de dados:


[oracle@dbdg dbs]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Sat Feb 10 12:42:54 2018

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

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> CREATE DATABASE newcdb
  USER SYS IDENTIFIED BY sys_password
  USER SYSTEM IDENTIFIED BY system_password
  LOGFILE GROUP 1 ('/u01/logs/my/redo01a.log','/u02/logs/my/redo01b.log')
             SIZE 100M BLOCKSIZE 512,
          GROUP 2 ('/u01/logs/my/redo02a.log','/u02/logs/my/redo02b.log')
             SIZE 100M BLOCKSIZE 512,
          GROUP 3 ('/u01/logs/my/redo03a.log','/u02/logs/my/redo03b.log')
             SIZE 100M BLOCKSIZE 512
  MAXLOGHISTORY 1
  MAXLOGFILES 16
  MAXLOGMEMBERS 3
  MAXDATAFILES 1024
  CHARACTER SET AL32UTF8
  NATIONAL CHARACTER SET AL16UTF16
  EXTENT MANAGEMENT LOCAL
  DATAFILE '/u01/app/oracle/oradata/newcdb/system01.dbf'
    SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
  SYSAUX DATAFILE '/u01/app/oracle/oradata/newcdb/sysaux01.dbf'
    SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
  DEFAULT TABLESPACE deftbs
     DATAFILE '/u01/app/oracle/oradata/newcdb/deftbs01.dbf'
     SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
  DEFAULT TEMPORARY TABLESPACE tempts1
     TEMPFILE '/u01/app/oracle/oradata/newcdb/temp01.dbf'
     SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
  UNDO TABLESPACE undotbs1
     DATAFILE '/u01/app/oracle/oradata/newcdb/undotbs01.dbf'
     SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
  ENABLE PLUGGABLE DATABASE
    SEED
    FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/newcdb/',
                         '/u01/app/oracle/oradata/pdbseed/')
    SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
    SYSAUX DATAFILES SIZE 100M
  USER_DATA TABLESPACE usertbs
    DATAFILE '/u01/app/oracle/oradata/pdbseed/usertbs01.dbf'
    SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31   32   33   34   35   36   37   38 

Database created.

Execução do script catcdb.sql para instalar todos os componentes requiridos pelo CDB:

Com  privilégio de SYSDBA:

@?/rdbms/admin/catalog.sql 
@?/rdbms/admin/catproc.sql 
@?/rdbms/admin/utlrp.sql
@?/rdbms/admin/catcdb.sql

Com usuário SYSTEM:

@?/sqlplus/admin/pupbld.sql

Session altered.

brunors

Hi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)

Creating a CDB Without Using Oracle Managed Files

Related posts

Leave a Comment Cancel reply

Related posts

Oracle Database Growth Monitoring Script

From Stockholm Marathon to Multi-cloud (OCI, AWS, Azure, GCP) Strategy

PL/SQL package SYS.DBMS_BACKUP_RESTORE version is not currentPL/SQL package SYS.DBMS_RCVMAN version 19.10.00.00 is too old

Leave a Comment Cancel reply