Wednesday, 26 April 2017

Creating Multi-tenant Database in Oracle 12c Using SQL (Manual)

Let’s us create a container database (CDB) by name cdb1 and one pluggable database pdb1.

We will place all datafiles, logfiles, controlfile of CDB in /u02/oracle/cdb1 directory and place seed database pdbseed database datafiles in '/u02/oracle/cdb1/pdbseed' directory and pluggable database PDB1 files /u02/oracle/cdb1/pdb1 directory.

Step 1: Make directories

$mkdir /u02/oracle/cdb1

$cd /u02/oracle/cdb1
$mkdir pdb1
$mkdir fast_recovery_area
$mkdir pdbseed
Create directory for storing audit files

$mkdir $ORACLE_BASE/admin/cdb1
$cd $ORACLE_BASE/admin/cdb1
$mkdir adump

Create directory for diagnostic destination

$cd /u02/oracle/cdb1
$mkdir diag

 
Step 2:- Create Parameter File by copying Template
$cd $ORACLE_HOME/dbs
$cp init.ora initcdb1.ora

Now open the PFile in vi editor and edit the require parameters. After editing it should look like as shown below
db_name='cdb1'
memory_target=500M
processes = 150
audit_file_dest=/opt/oracle/product/admin/cdb1/adump
audit_trail ='db'
db_block_size=8192
db_domain=''
db_recovery_file_dest='/u02/oracle/cdb1/fast_recovery_area'
db_recovery_file_dest_size=2G
diagnostic_dest=/u02/oracle/cdb1/diag
dispatchers='(PROTOCOL=TCP) (SERVICE=cdb1XDB)'
open_cursors=300
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'
control_files = '/u02/oracle/cdb1/control01.ora'
compatible ='12.1.0'
enable_pluggable_database=TRUE

Step 3- Set the ORACLE_SID environment variable and start the instance

$ export ORACLE_SID=cdb1
$ sqlplus
Enter User: / as sysdba

SQL>

Step 4:- Give Create Database statement as follows

CREATE DATABASE cdb1
LOGFILE 
GROUP 1 '/u02/oracle/cdb1/log1.ora' size 10M,
GROUP 2 '/u02/oracle/cdb1/log2.ora' size 10M
EXTENT MANAGEMENT LOCAL
DATAFILE '/u02/oracle/cdb1/system01.dbf'
    SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE '/u02/oracle/cdb1/sysaux01.dbf'
    SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
DEFAULT TABLESPACE deftbs
    DATAFILE '/u02/oracle/cdb1/deftbs01.dbf' SIZE 500M 
        REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/u02/oracle/cdb1/temp01.dbf'
    SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
UNDO TABLESPACE undotbs1
    DATAFILE '/u02/oracle/cdb1/undotbs01.dbf'
        SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
ENABLE PLUGGABLE DATABASE
SEED
FILE_NAME_CONVERT = 
    ('/u02/oracle/cdb1/', 
    '/u02/oracle/cdb1/pdbseed/')
SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
SYSAUX DATAFILES SIZE 100M
USER_DATA TABLESPACE usertbs
    DATAFILE '/u02/oracle/cdb1/pdbseed/usertbs01.dbf'
        SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

Step 5:- 

Run the scripts. In Oracle 12c it is recommended to run catalog and catproc scripts through Perl program catcon.pl 

Goto ORACLE_HOME/rdbms/admin directory. This is where all the scripts are available.
$cd $ORACLE_HOME/rdbms/admin

Give the following command at O/s prompt

$perl catcon.pl -d $ORACLE_HOME/rdbms/admin -b catalog_output catalog.sql 
$perl catcon.pl -d $ORACLE_HOME/rdbms/admin -b catproc_output catproc.sql 
$perl catcon.pl -d $ORACLE_HOME/rdbms/admin -b catblock_output catblock.sql 
$perl catcon.pl -d $ORACLE_HOME/rdbms/admin -b catoctk_output catoctk.sql
$perl catcon.pl -d $ORACLE_HOME/rdbms/admin -b owminst_output owminst.plb
$perl catcon.pl -d $ORACLE_HOME/sqlplus/admin -b pupbld_output pupbld.sql 

Step 6:- See the status of Pluggable databases

SQL> show pdbs

CON_ID  CON_NAME OPEN MODE RESTRICTED
------- -------- --------- ----------  
2       PDB$SEED READ ONLY NO
Step 7:- Let’s us create the pluggable database pdb1

       To create a pluggable database give the following command

SQL>CREATE PLUGGABLE DATABASE pdb1 ADMIN USER pdb1adm IDENTIFIED BY tiger 
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M)
DEFAULT TABLESPACE users 
DATAFILE '/u02/oracle/cdb1/pdb1/users01.dbf' 
SIZE 250M AUTOEXTEND ON
FILE_NAME_CONVERT = ('/u02/oracle/cdb1/pdbseed/', 
'/u02/oracle/cdb1/pdb1/');

To view list of PDB’s

SQL> show pdbs

CON_ID     CON_NAME OPEN MODE RESTRICTED
------     -------- --------- -------- 
2          PDB$SEED READ ONLY NO
3          pdb1  MOUNTED

Now open the pluggable database 

SQL>alter pluggable database pdb1 open;

See the status
SQL> show pdbs

CON_ID     CON_NAME OPEN MODE  RESTRICTED
------     -------- ---------  -------- 
2          PDB$SEED READ ONLY  NO
3          pdb1  READ WRITE

To switch to Pluggable database give the following command

SQL>alter session set container=pdb1

To view current container, give the following command

SQL> show con_name

 

No comments:

Post a Comment