Tag: FILE_NAME_CONVERT

Creating Oracle 12c Multitenant Container Database

Published on by AdvaitLeave a comment

Oracle has come up with new feature called multitenant database. Using this feature we can have many pluggable databases plugged into single container database.
This article explains pluggable databases architecture and how to create them.
I am not covering administration part. Viewers can check the documentation mentioned in reference section to check administrative part for managing pluggable databases.

Multitenant Architecture

One of the high-profile new features of Oracle 12c Enterprise Edition(EE) is the multitenant option – multitenant container database (CDB). This option allows the creation of many pluggable databases (PDBs) inside a CDB. The PDBs share resources provided by the CDB, such as memory, background processes, UNDO, REDO, and control files. This enables more databases to run on a single platform than was possible with the former Oracle 11gR2 architecture.
You can consider all databases till 11g as non-container databases or non-CDB. In oracle 12c we can still create non-CDB databases using normal “create database” command and administration wise there is no difference in managing those 12c databases compared to 11g database.

Oracle Multitenant architecture contains following:

One root container database
This looks like a normal database, but the purpose is different. This stores Oracle-supplied metadata and common users. It has all component that a normal database has – controlfile, online redo logs, SYSTEM and SYSAUX tablespace, Pfile and SPfile, archive logs and flasback logs etc. You can also create additional users, tablespace in this database.

One seed pluggable database
This is a seed databases which gets created when we create container database. This is nothing but set of datafiles in different location. This seed database can be used to create new pluggable databases.

Zero or more user-created PDBs
We can create multiple pluggable databases in single container databases. There are different methods of creating pluggable databases. Using above seed database to create pluggable database is just 1 method. We can also clone existing pluggable database from same container database or different container database. We can also make other non-CDB oracle 12c databases as pluggable database in our container database. You can refer to doc mentioned in reference section for more details on this.

Following figure show multitenant architecture:

cdbContainer database is same as normal database having all required component same as normal database.
Pluggable database has its own SYSTEM, SYSAUX and data tablespace. All other components like UNDO tablespace, online redo logs, controlfile, archive logs etc are common for container database and pluggable database.

When compared to RAC, I feel this architecture is exactly opposite. In RAC we have multiple instances and 1 database. In multitenant architecture we have single instance and multiple databases.

Creating Multitenant Container database

Creating container database is similar to creating non-container database but with few changes. Following changes should be made if we want to create container database

1) Define enable_pluggable_database parameter to true.
By default this parameter is false. Meaning that by default database will be created as non-multitenant database. If you want to create multitenant database, you have to make this parameter true.
Failing to make this parameter true will give you following error if you try to create multitenant database

ORA-65093: multitenant container database not set up properly

2) Change in “Create database” command
So if you are going to create multitenant database using “create database” command, you need to add couple of clause to this command.
Following clause needs to be added

  • ENABLE PLUGGABLE DATABASE
  • SEED

Following steps can be used to create multitenant database

Step 1) Create pfile and spfile

You can use following parameters as generic to create test database. You can alter the memory parameters and include/remove many parameters as required. But these are good enough to create test instance.

cat initdeocdb.ora

db_name='deocdb'
sga_max_size=4G
shared_pool_size=1200M
shared_pool_reserved_size=300M
java_pool_size = 200M
pga_aggregate_target=1G
processes = 300
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u01/oradata/databases/flashback'
db_recovery_file_dest_size=50G
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDO_T1'
control_files = (/ctl-01/databases/deocdb/control.ctl, /ctl-02/databases/deocdb/control.ctl)
compatible ='12.0.0'
audit_file_dest = /dumps-01/databases/deocdb/adump
core_dump_dest = /dumps-02/databases/deocdb/cdump
diagnostic_dest = /dumps-01
sec_case_sensitive_logon = FALSE
utl_file_dir = /dumps-01/databases/deocdb/output
enable_pluggable_database=true

create spfile from pfile;

***Its important to set java_pool_size in pfile/spfile. If you do not set java_pool_size, we will see following errors in alert log while creating container database

ORA-04031: unable to allocate 4096 bytes of shared memory (“java pool”,”/51835a0f_ReflectionFactoryGet”,”JOXLE^5cb1fb41″,”:SGAClass”)

Step 2) Create database command

I used following create database command to create container database. Highlighted lines are additional clauses for creating container database.


startup nomount;

CREATE DATABASE deocdb
USER SYS IDENTIFIED BY welcome
USER SYSTEM IDENTIFIED BY welcome
LOGFILE GROUP 1 ('/redo-01-a/databases/deocdb/redo-t01-g01-m1.log',
                 '/redo-03-a/databases/deocdb/redo-t01-g01-m2.log') SIZE 100M BLOCKSIZE 512,
        GROUP 2 ('/redo-02-a/databases/deocdb/redo-t01-g02-m1.log',
                 '/redo-04-a/databases/deocdb/redo-t01-g02-m2.log') SIZE 100M BLOCKSIZE 512,
        GROUP 3 ('/redo-01-a/databases/deocdb/redo-t01-g03-m1.log',
                 '/redo-03-a/databases/deocdb/redo-t01-g03-m2.log') SIZE 100M BLOCKSIZE 512,
        GROUP 4 ('/redo-02-a/databases/deocdb/redo-t01-g04-m1.log',
                 '/redo-04-a/databases/deocdb/redo-t01-g04-m2.log') SIZE 100M BLOCKSIZE 512
MAXLOGHISTORY 1
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 1024
CHARACTER SET UTF8
NATIONAL CHARACTER SET UTF8
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/oradata/databases/deocdb/system01.dbf'   
    SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE '/u01/oradata/databases/deocdb/sysaux01.dbf'   
    SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TABLESPACE admin
DATAFILE '/u01/oradata/databases/deocdb/admin-01.dbf' 
    SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED  
DEFAULT TEMPORARY TABLESPACE TEMP 
TEMPFILE '/u01/oradata/databases/deocdb/temp01.dbf' 
    SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE undo_t1
DATAFILE '/u01/oradata/databases/deocdb/undo_t1-01.dbf'
    SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
ENABLE PLUGGABLE DATABASE
SEED
    FILE_NAME_CONVERT = ('/u01/oradata/databases/deocdb/', 
                         '/u01/oradata/databases/pdbseed/')
    SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
    SYSAUX DATAFILES SIZE 100M
USER_DATA TABLESPACE users
DATAFILE '/u01/oradata/databases/pdbseed/users-01.dbf'
    SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Step 3) run catcdb.sql

DO NOT RUN catlog.sql and catproc.sql
If you are creating container database, you need to run only catcdb.sql.
This script takes care of running all other dependent scripts internally.

If you run catlog.sql and catproc.sql, this script will fail later at many stages with following errors

catcon: ALL catcon-related output will be written to dbmsxdbt_catcon_2527.lst
catcon: See dbmsxdbt*.log files for output generated by scripts
catcon: See dbmsxdbt_*.lst files for spool files, if any
catconInit: database is not open on the default instance
Unexpected error encountered in catconInit; exiting

In case of normal database creation, we usually run catlog.sql and catproc.sql as mentioned in http://docs.oracle.com/database/121/ADMIN/create.htm#ADMIN11082
But that’s for normal database creation and process is same in 12c. But for creating container database, we should be running only catcdb.sql

catcdb.sql will ask for 3 inputs – new sys password, new system password and temp tablespace

SQL> @?/rdbms/admin/catcdb.sql

Session altered.

Enter new password for SYS:
Enter new password for SYSTEM:
Enter temporary tablespace name: TEMP

Once you provide this, it will create root container database with whatever database name you give. Container name for root container DB will be CDB$ROOT.
It will also create seed pluggable container database. Container name will be seed$pdb

Step 4) Run utlrp.sql to compile invalid objects.

There will be few invalid objects in database. You can run utlrp.sql to compile the same

Validation

You can run few commands to validate if you database is container databases and what all containers it has.

We have a new column in v$database called CDB. If your database is container database it will show as YES

SYS.DEOCDB.PRIMARY>select name, cdb from v$database;

NAME    CDB
--------- ---
DEOCDB    YES

1 row selected.

You can also check different containers in your database using following

SYS.DEOCDB.PRIMARY>select CON_ID, NAME, OPEN_MODE, TOTAL_SIZE from v$containers;

    CON_ID NAME         OPEN_MODE  TOTAL_SIZE
---------- ------------------------------ ---------- ----------
   1 CDB$ROOT       READ WRITE        0
   2 PDB$SEED       READ ONLY  1599078400

V$CONTAINER is the new view available in 12c. 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.

SYS.DEOCDB.PRIMARY>alter session set container=PDB$SEED;

Session altered.

SYS.DEOCDB.PRIMARY>select CON_ID, NAME, OPEN_MODE, TOTAL_SIZE from v$containers;

    CON_ID NAME         OPEN_MODE  TOTAL_SIZE
---------- ------------------------------ ---------- ----------
   2 PDB$SEED       READ ONLY  1599078400

Creating Pluggable database

Following command can be used to create pluggable database from seed pluggable database PDB$SEED

SYS.DEOCDB.PRIMARY>create pluggable database deopdb1 admin user deodba identified by welcome FILE_NAME_CONVERT=('/u01/oradata/databases/pdbseed/','/u01/oradata/databases/deopdb1/');

Pluggable database created.

Above command is in its basic form and all parameter/clauses mentioned are mandatory. You need to provide FILE_NAME_CONVERT so that files from seed database will be copied to location you provided for your pluggable database. There are many parameters to this command and you can explore them in documentation link provided in reference.

Initially when you create pluggable database it will be in MOUNTED state. This is because pluggable database use same memory structure and control file as root container database. So memory structure and control file is already allocated.

SYS.DEOCDB.PRIMARY>select CON_ID, NAME, OPEN_MODE, TOTAL_SIZE from v$containers;

    CON_ID NAME         OPEN_MODE  TOTAL_SIZE
---------- ------------------------------ ---------- ----------
   1 CDB$ROOT       READ WRITE        0
   2 PDB$SEED       READ ONLY  1599078400
   3 DEOPDB1        MOUNTED       0

You need to just open the database.
You can open pluggable database from root container when you logged in using following command

SYS.DEOCDB.PRIMARY>show con_name;

NAME_COL_PLUS_PDB_CONTAINER
-------------------------------------
CDB$ROOT

SYS.DEOCDB.PRIMARY>alter pluggable database deopdb1 open;

Pluggable Database opened.

Or you can change container name and simply start

SYS.DEOCDB.PRIMARY>alter session set container=DEOPDB1;

Session altered.

SYS.DEOCDB.PRIMARY>show con_name;

NAME_COL_PLUS_PDB_CONTAINER
-------------------------------------
DEOPDB1

SYS.DEOCDB.PRIMARY>startup
Pluggable Database opened.

You can shutdown pluggable database using similar commands

if you are in root container, you can use following commands

SYS.DEOCDB.PRIMARY>show con_name;

NAME_COL_PLUS_PDB_CONTAINER
-------------------------------------
CDB$ROOT
SYS.DEOCDB.PRIMARY>alter pluggable database deopdb1 close;

Pluggable database altered.

Else you can change container and shutdown the database.

SYS.DEOCDB.PRIMARY>alter session set container=DEOPDB1;

Session altered.

SYS.DEOCDB.PRIMARY>show con_name;

NAME_COL_PLUS_PDB_CONTAINER
--------------------------------------
DEOPDB1
SYS.DEOCDB.PRIMARY>
SYS.DEOCDB.PRIMARY>
SYS.DEOCDB.PRIMARY>shut immediate;
Pluggable Database closed.

Reference

http://docs.oracle.com/database/121/ADMIN/cdb_intro.htm#ADMIN13508
http://docs.oracle.com/database/121/ADMIN/cdb_plug.htm#ADMIN13556
http://docs.oracle.com/database/121/ADMIN/cdb_create.htm#ADMIN13521
https://docs.oracle.com/database/121/CNCPT/cdbovrvw.htm#CNCPT89234

Hope this helps !!

Advertisement