Creating a CDB database by command line

brunors

 

 

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>

Related posts

Leave a Comment