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. Former Oracle ACE, Computer Scientist, MSc in Data Science, over ten years of experience in companies such as IBM, Epico Tech, and Playtech based in three different countries (Brazil, Hungary, and Sweden) and joined projects remotely in many others. I am super excited to show you my interest in Databases, Cloud, Data Science, Data Engineering, Bigdata, AI, Programming, Software Engineering, and data in general.
(Continue reading)