Sometimes the Oracle DBA needs to create a CDB database without DBCA. This procedure is possible. However, it is needed some additional steps to be done. For instance, to run some scripts to create components who DBCA utilitty create automatically. Therefore, in this post I am going to show how to create a CDB database by command line:
- Create directories for the Oracle Database. As the database will be named “db2”, some file system structures will contain this word:
mkdir -p /u01/app/oracle/oradata/db2 mkdir -p /u01/logs/db2 mkdir -p /u02/logs/db2 mkdir -p /u01/app/oracle/flash_recovery_area mkdir -p /u01/app/oracle/control01/db2 mkdir -p /u02/app/oracle/control02/db2 mkdir -p /u01/app/oracle/oradata/pdbseed/db2/ chown oracle:oinstall /u01/app/oracle/oradata/db2 chown oracle:oinstall /u01/logs/db2 chown oracle:oinstall /u02/logs/db2 chown oracle:oinstall /u01/app/oracle/flash_recovery_area chown oracle:oinstall /u01/app/oracle/control01/db2 chown oracle:oinstall /u02/app/oracle/control02/db2 chown oracle:oinstall /u01/app/oracle/oradata/pdbseed/db2/
- Create the init<SID>.ora file at $ORACLE_HOME/dbs:
[oracle@vm2 templates]$ echo $ORACLE_HOME /u01/app/oracle/product/12.1.0/dbhome_1 [oracle@vm2 templates]$ vi /u01/app/oracle/product/12.1.0/dbhome_1/initdb2.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='db2' processes = 150 db_block_size=8192 db_domain='' db_recovery_file_dest='/u01/app/oracle/flash_recovery_area' DB_RECOVERY_FILE_DEST_SIZE=500m 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/db2/ora_control1.ctl, /u02/app/oracle/control02/db2/ora_control2.ctl) compatible ='12.0.0' ENABLE_PLUGGABLE_DATABASE=YES
- Start the instance in NOMOUNT stage using the created pfile.
[oracle@vm2 dbs]$ export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1 [oracle@vm2 dbs]$ export ORACLE_SID=db2 [oracle@vm2 dbs]$ sqlplus "/as sysdba" SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 6 12:38:42 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/initdb2.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> SQL> select instance_name, status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ db2 STARTED
- Create the database using the “CREATE DATABASE” statement:
SQL> CREATE DATABASE db2
USER SYS IDENTIFIED BY 112233
USER SYSTEM IDENTIFIED BY 112233
LOGFILE GROUP 1 ('/u01/logs/db2/redo01a.log','/u02/logs/db2/redo01b.log') SIZE 100M BLOCKSIZE 512,
GROUP 2 ('/u01/logs/db2/redo02a.log','/u02/logs/db2/redo02b.log') SIZE 100M BLOCKSIZE 512,
GROUP 3 ('/u01/logs/db2/redo03a.log','/u02/logs/db2/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/db2/system01.dbf'
SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE '/u01/app/oracle/oradata/db2/sysaux01.dbf'
SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TABLESPACE deftbs
DATAFILE '/u01/app/oracle/oradata/db2/deftbs01.dbf'
SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/u01/app/oracle/oradata/db2/temp01.dbf'
SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs1
DATAFILE '/u01/app/oracle/oradata/db2/undotbs01.dbf'
SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
ENABLE PLUGGABLE DATABASE
SEED
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/db2/',
'/u01/app/oracle/oradata/pdbseed/db2')
SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
SYSAUX DATAFILES SIZE 100M
USER_DATA TABLESPACE usertbs
DATAFILE '/u01/app/oracle/oradata/pdbseed/db2/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
Database created.
- Run the catcdb.sql, catalog.sql, catproc.sql and utlrp.sql as SYS user to create some components.
SQL> @?/rdbms/admin/catcdb.sql Session altered. Enter new password for SYS: Enter new password for SYSTEM: Enter temporary tablespace name: tempts1 SQL> @?/rdbms/admin/catalog.sql SQL> @?/rdbms/admin/catproc.sql SQL> @?/rdbms/admin/utlrp.sql
- Run the pupbld.sql as SYSTEM user to create some components.
SQL> conn system Enter password: Connected. SQL> @?/sqlplus/admin/pupbld.sql
- In the end, check the status of the new CDB database:
SQL> select instance_name, status from v$instance; INSTANCE_NAME STATUS ---------------- ------------ db2 OPEN 1 row selected. SQL> select cdb from v$database; CDB --- YES 1 row selected. SQL> select name, open_mode from v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY 1 row selected. SQL>
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)