So far we have learned about container database,Multi tenant architecture. Now in this post I will give you some steps on How to Create the container database in Oracle 12c
How to Create the container database in Oracle 12c
Container Database can be created by DBCA or you can create it manually
DBCA
It is same as pre 12c But we just have a additional flag to enable PLUGGABLE DATABASE
Manually:
1) We can set the Oracle home and Oracle SID
2) Create the init.ora and spfile
3) Connect to sqlplus
startup nomount
4) Run the command
CREATE DATABASE test_cdb1
USER SYS IDENTIFIED BY sys_test
USER SYSTEM IDENTIFIED BY system_test
LOGFILE GROUP 1 (‘/u01/oracle/CDB1/datafile/redo1a.log’,
‘/u02/oracle/CDB1/datafile/redo1b.log’) SIZE 1024M,
GROUP 2 (‘/u01/oracle/CDB1/datafile/redo2a.log’,
‘/u02/oracle/CDB1/datafile/redo2b.log’) SIZE 1024M
CHARACTER SET AL32UTF8 NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL DATAFILE
‘/u01/oracle/CDB1/datafile/system01.dbf’ SIZE 1024M
SYSAUX DATAFILE ‘/u01/oracle/CDB1/datafile/sysaux01.dbf’ SIZE 1024M
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE ‘/u01/oracle/CDB1/datafile/temp01.dbf’ SIZE 1024M
UNDO TABLESPACE undotbs
DATAFILE ‘/u01/oracle/CDB1/datafileundotbs01.dbf’ SIZE 1024M
ENABLE PLUGGABLE DATABASE
SEED FILE_NAME_CONVERT =
(‘/u01/oracle/CDB1/datafile’,
‘/u01/oracle/CDB1/datafile/seed’);
It create the Container database and Seed PDB
Two important things in this command
a) ENABLE PLUGGABLE DATABASE : It enabled the container database
b) SEED FILE_NAME_CONVERT: It tells where to create the datafile for seed database
c) If we have OMF present ,then we we dont need to give the SEED file_name_convert. Everything gets created into DB_CREATE_FILE_DEST
So with OMF
CREATE DATABASE test_cdb1
USER SYS IDENTIFIED BY sys_test
USER SYSTEM IDENTIFIED BY system_test
CHARACTER SET AL32UTF8 NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DEFAULT TEMPORARY TABLESPACE tempts1
UNDO TABLESPACE undotbs
ENABLE PLUGGABLE DATABASE
;
5) Run the catcdb.sql script which create the necessary views
Important things to check
- The above command create two container CDB$ROOT and PDB$SEED
- v$containers is the new view in CDB,it shows all the containers
select CON_ID, NAME, OPEN_MODE, TOTAL_SIZE from v$containers;
When this is run from root container, it shows all containers CDB as well as PDB. But if you run same command from any PDB, it will show that container.
3. We have a new column in v$database called CDB. If your database is container database it will show as YES
select name, cdb from v$database;
NAME CDB
—– ——
TEST_CDB1 YES
- SYS and SYSTEM are common user between root and seed container
-
SGA is shared across the all the container so,v$ views contains extra column con_id
-
USER_objects list all the object own by the user in PDB
- ALL_OBJECTS list all the object which he has privilege in the PDB
- DBA_OBJECTS list all the objects the PDB and root container
- There is new view CDB_OBJECTS. It list all the objects across all the PDB in the container