Monday, 19 June 2017

Creating a Container Database in 12c

Creating a CDB:


To use the pluggable database feature, we have to specifically create a pluggable enabled CDB.
There are a several different techniques for creating a CDB:

• Manually issuing the SQL CREATE DATABASE command

• Using DBCA

• Generating the required scripts with the DBCA, and then manually running the scripts to create a CDB.

• Using RMAN to duplicate an existing CDB

Creating Manually with SQL:

Note: As of now I am listing the steps to Create Container Database Manually.  I will also try to post the other methods of creating CDB.

First, ensure that your ORACLE_SID, ORACLE_HOME, and PATH variables are set for your CDB environment.

Next, create a parameter initialization file in the ORACLE_HOME/dbs directory. Make certain you set the ENABLE_PLUGGABLE_DATABASE parameter to TRUE. Here, I’ve used an OS text editor to create a file named initCDB.ora and placed within it the following parameter specifications:

db_name='CDB'
 enable_pluggable_database=true
 audit_trail='db'
 control_files='/u01/dbfile/CDB/control01.ctl','/u01/dbfile/CDB/control02.ctl'
 db_block_size=8192
 db_domain=''
 memory_target=629145600
 memory_max_target=629145600
 open_cursors=300
 processes=300
 remote_login_passwordfile='EXCLUSIVE'
 undo_tablespace='UNDOTBS1'
initCDB.ora

Container Database in 12c, Oracle Database

mkdir -p /u01/dbfile/CDB/pdbseed

 mkdir -p /u01/dbfile/CDB
 mkdir -p /u01/oraredo/CDB
Now, start up your database in nomount mode, and run the credb.sql script:

$ sqlplus / as sysdba
 SQL> startup nomount;
 SQL> @credb.sql

Container Database in 12c, Oracle Database

Contents of credb.sql

CREATE DATABASE CDB
 MAXLOGFILES 16
 MAXLOGMEMBERS 4
 MAXDATAFILES 1024
 MAXINSTANCES 1
 MAXLOGHISTORY 680
 CHARACTER SET US7ASCII
 NATIONAL CHARACTER SET AL16UTF16
 DATAFILE
 '/u01/dbfile/CDB/system01.dbf' SIZE 500M
 EXTENT MANAGEMENT LOCAL
 UNDO TABLESPACE undotbs1 DATAFILE
 '/u01/dbfile/CDB/undotbs01.dbf' SIZE 800M
 SYSAUX DATAFILE
 '/u01/dbfile/CDB/sysaux01.dbf' SIZE 500M
 DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE
 '/u01/dbfile/CDB/temp01.dbf' SIZE 500M
 DEFAULT TABLESPACE USERS DATAFILE
 '/u01/dbfile/CDB/users01.dbf' SIZE 50M
 LOGFILE GROUP 1
 ('/u01/oraredo/CDB/redo01a.rdo') SIZE 50M,
 GROUP 2
 ('/u01/oraredo/CDB/redo02a.rdo') SIZE 50M
 USER sys IDENTIFIED BY oracle
 USER system IDENTIFIED BY oracle
 USER_DATA TABLESPACE userstbs DATAFILE
 '/u01/dbfile/CDB/userstbsp01.dbf' SIZE 500M
 ENABLE PLUGGABLE DATABASE
 SEED FILE_NAME_CONVERT = ('/u01/dbfile/CDB','/u01/dbfile/CDB/pdbseed');

USER_DATA TABLESPACE clause specifies that an additional tablespace be created within the seed database; this tablespace will also be replicated to any pluggable databases that are cloned from the seed database.

Also, the SEED FILE_NAME_CONVERT specifies how the seed database files will be named and in what directories they will be located. Next, ensure you have created any directories referenced in the parameter file and the CREATE DATABASE statement:

Container Database in 12c, Oracle Database

Container Database in 12c, Oracle Database

If successful, you should see this:

Database created.

Oracle recommends that you use the catcon.pl Perl script to run any Oracle supplied SQL scripts for a CDB. Therefore to create the data dictionary for a CDB use the catcon.pl Perl script. First change directories to the ORACLE_HOME/rdbms/admin directory:

$ cd $ORACLE_HOME/rdbms/admin

Now use catcon.pl to run the catalog.sql script as SYS:

$ perl catcon.pl -u sys/oracle -s -e -d $ORACLE_HOME/rdbms/admin -b catalog1 catalog.sql > catcon-catalog.log
Next, use catcon.pl to run the catproc.sql script as SYS:

$ perl catcon.pl -u sys/oracle -s -e -d $ORACLE_HOME/rdbms/admin -b catproc1 catproc.sql > catcon-catproc.log
After you have created the data dictionary, as the SYSTEM schema, create the product user profile tables:

$ perl catcon.pl -u system/oracle -s -e -d $ORACLE_HOME/sqlplus/admin -b pupbld1 pupbld.sql > catcon-pupbld.log

Container Database in 12c, Oracle Database

At this point, we have a fully functional CDB database and can start creating pluggable databases within it.

$ sqlplus / as sysdba
You can now confirm that the CDB was successfully created via this query. If a database was created as a CDB,the CDB column of V$DATABASE will contain a YES value:

SQL> select name, cdb from v$database;

Here is some sample output:

NAME   CDB
———    —
CDB     YES

At this point, you should have two containers in your CDB database: the root container and the seed pluggable database. You can check with this query:

SQL> select con_id, name from v$containers;

Here is some sample output:

CON_ID   NAME
——     ————
1           CDB$ROOT
2           PDB$SEED

Container Database in 12c, Oracle Database

Note that if you had selected from DBA_DATA_FILES instead of CDB_DATA_FILES, you would only see the four data files associated with the root container (the container you’re currently connected to); for example,

Container Database in 12c, Oracle Database

This Ends the Creation of Container Database.