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