Steps To Create Container Database (CDB) Manually in Oracle 12c

Oracle 12c database is MULTITENANTE CONTAINER DATABASE space for creating pluggable databases.

(Eg Pluggable databases are applications like any OLTP databases etc..)

Oracle 12c database is Multi-tenant  container database in which a seed database called pdb$seed comes with it, Using which we can create pluggable database. in one container 253 pdbs can be created including pdbseed.

Follow below steps to create Multitenante Container Database manually.

Step 1 : Specify an instance identifier (SID) & Environment variables.

The maximum number of characters for ORACLE_SID is 12, only letters and digits are permitted.

[oracle@server1 ~]$ vi cdb1.sh  or bash_profile

export ORACLE_SID=cdb1
export ORACLE_HOME=/soft/app/oracle/product/12.1.1
export ORACLE_BASE=/soft/app/oracle
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/perl/bin:$PATH:.
export LD_LIBRARY_PATH=$ORACLRE_HOME/lib:/lib:/usr/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

Step 2 : Ensure that the required environment variables are set.

echo $ORACLE_SID
echo $ORACLE_HOME
echo $PATH

Step 3 : Create the initialization parameter file under ORACLE_HOME dbs location

[oracle@server1 ~]$ cd $ORACLE_HOME/dbs

[oracle@server1 dbs]$ cp  init.ora  initcdb1.ora   ( cdb1 is SID )

After copied open new initcdb1 parameter file and edit as specified below

[oracle@server1 dbs]$ vi initcdb1.ora
db_name='CDB1'
enable_pluggable_database=true
memory_target=1G
processes = 150
audit_file_dest='$ORACLE_BASE/admin/orcl/adump'
audit_trail ='db'
db_block_size=8192
db_domain=''
#db_recovery_file_dest='<ORACLE_BASE>/fast_recovery_area'
#db_recovery_file_dest_size=2G
diagnostic_dest='/u01/oradata/cdb1'
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/oradata/cdb1/control1.ctl
compatible ='12.0.0'


Step 4 : Create directory structure to store database physical file.

[oracle@server1 ~]$ mkdir  -p  /u01/oradata/cdb1
[oracle@server1 ~]$ mkdir  -p  /u01/oradata/cdb1/pdbseed


Step 5 : Create database creation script.

[oracle@server1 ~]$ vi createcdb1.sql
create database cdb1
datafile '/u01/oradata/cdb1/system.dbf' size 1024M extent management local
sysaux datafile '/u01/oradata/cdb1/sysaux.dbf' size 1024M
undo tablespace undotbs1 datafile '/u01/oradata/cdb1/untotbs.dbf' size 500M
default temporary tablespace temp tempfile '/u01/oradata/cdb1/temp.dbf' size 500M
default tablespace users datafile '/u01/oradata/cdb1/users.dbf' size 500M
logfile
group 1('/u01/oradata/cdb1/redo1a.log') size 10M,
group 2('/u01/oradata/cdb1/redo21.log') size 10M
ENABLE PLUGGABLE DATABASE
SEED FILE_NAME_CONVERT=('/u01/oradata/cdb1','/u01/oradata/cdb1/pdbseed');

Step 5 : Startup the database using below steps

[oracle@server1 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 12.1.0.1.0 Production on Wed Jan 24 20:02:22 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> startup nomount

SQL> @createcdb1.sql

Database Created

Step 6 : Verifying CDB was created

SQL> select NAME,OPEN_MODE,CDB from v$database;

NAME      OPEN_MODE            CDB
--------- -------------------- ---
CDB1      READ WRITE           YES

SQL> select DBID, CON_ID, NAME, OPEN_MODE from v$database;

      DBID     CON_ID NAME      OPEN_MODE

---------- ---------- --------- --------------------

 939984158          0 CDB1      READ WRITE

Step 7 :  Build data dictionary views

Run the script necessary to build data dictionary views, synonyms and PL/SQL packages, and to support proper functioning of SQL*Plus

[oracle@server1 ~]$ vi postcdb.sql

@$ORACLE_HOME/rdbms/admin/catalog.sql

@$ORACLE_HOME/rdbms/admin/catproc.sql

conn system/manager

@$ORACLE_HOME/sqlplus/admin/pupbld.sql

SQL> @postcdb.sql

--

--

--

--

Notice that the con id is 0 for the  whole container cdb1

At this point you should have two containers in your CDB database. The Root container and Seed Pluggable database. Check with below query.

SQL> select CON_ID, NAME from v$containers;

    CON_ID NAME

---------- ------------------------------

         1 CDB$ROOT

         2 PDB$SEED

Step 7 :  Build data dictionary views on PDB$SEED

Run the script necessary to build data dictionary views, synonyms, PL/SQL packages and to support proper functioning of SQL*Plus in pdb$seed.

SQL> alter session set "_oracle_script"=true;

Session altered.

SQL> SELECT con_id,dbid,NAME,OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE
---------- ---------- ------------------------------ ----------
         2 4208911838 PDB$SEED                       READ ONLY

SQL> alter pluggable database pdb$seed close;

Pluggable database altered.

SQL> alter pluggable database pdb$seed open;

Pluggable database altered.

SQL>

[oracle@server1 ~]$ vi postpdb.sql

@$ORACLE_HOME/rdbms/admin/catalog.sql

@$ORACLE_HOME/rdbms/admin/catblock.sql

@$ORACLE_HOME/rdbms/admin/catproc.sql

@$ORACLE_HOME/rdbms/admin/catoctk.sql

@$ORACLE_HOME/rdbms/admin/owminst.plb

conn system/manager

@$ORACLE_HOME/sqlplus/admin/pupbld.sql

SQL>@postpdb.sql

SQL> alter session set "_oracle_script"=false;

Session altered.

SQL> select OPEN_MODE from v$database;

OPEN_MODE
--------------------
READ WRITE

SQL> select con_id, dbid, NAME, OPEN_MODE from v$pdbs;

    CON_ID       DBID NAME                           OPEN_MODE

---------- ---------- ------------------------------ ----------

         2 4208911838 PDB$SEED                       READ WRITE

Ora 12154 tns error in oracle 12c 11g and 10g

Error ( Ora 12154 ) Message is : TNS:could not resolve the connect identifier specified.

One of my client has faced ora 12154 error while he configuring the database connectivity network using tnsnames.ora file with the provided details ( Service Name, Hostname, Port Number ) but he is facing issue, when we verified all the details which provided seems to be fine but customer still facing the issue, we did lot of digging to resolve the issue while i was searching in google i found most of the people are facing the same issue what i am looking, so that's why i decided to share the my tns error resolving log with you.

we have faced many ora 12154 tns error issues, i have copied all the logs and sharing one by one, i believe your situation suits any one of them, also i have mentioned in detailed description how to give tnsnames.ora configuration. In case if i miss anything please comment below i will paste on same page that will help to others.  

Below is one scenario where customer had done the mistake, instead of giving oradb1 he has mentioned wrong service name oradb11 due to connectivity failed  

---------------------------------------------------------------------------------------------------------

[oracle@rsrvt134 admin]$ tnsping oradb1

TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 20-AUG-2017 00:01:49

Copyright (c) 1997, 2014, Oracle.  All rights reserved.

Used parameter files:


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = rsrvt134.oraclenet.com)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = oradb11)))
OK (10 msec)

[oracle@rsrvt134 admin]$ sqlplus kkasari/password@oradb1

SQL*Plus: Release 12.1.0.2.0 Production on Sun Aug 20 00:01:52 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name:

( Answer is corrected the service name to oradb11 to oradb1 )

SQL>  show parameter service_name

Name                        Type                                 Value

-----------                   ------------                         -----------------

service_name            string                                ORADB1

------------------------------------------------------------------------------------------------------------------


To configure oracle database remote connectivity we need four details...

• Tns Service Name
• Host Name
• Port Number
• Database Service Name

Tns Service name -  you can choose any name, it would be good if the name is related and meaning full to related with database name.

Hostname -  Specify server ( IP Address / Hostname ) name where the database exists.

Port Number - Port number must match with the server side listener port number, be careful when you have multiple listener entries in a single file.

Service Name - Maximum database name is the service name in case if you have any doubt please check with using below parameter.

SQL>  show parameter service_name

Name                        Type                                 Value

-----------                   ------------                         -----------------

service_name            string                                ORADB1

TNS File ( tnsnames.ora )
---------------------------------

oradb1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = rsrvt134.oraclenet.com)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED ) - Optional 
      (SERVICE_NAME = oradb1)
    )
  )

Manual Database Creation in Oracle 10g On Linux, AIX, Sun Solarise, UNIX

Simple manual database creation in 10g

In Oracle we can create database through manually and GUI(DBCA) as well but in normal case most of the DBA's prefer to create database with manual only. I had seen some DBA's they are generating the scripts through DBCA and executing manually that is also good at least they come to know what oracle doing internally before creating the database and what type scripts it is executing.  This is very important to know who are working as a DBA's.

 Step : 1

Create the environment file or .bash_profile

]$ vi test.env   or   .bash_profile

export  ORACLE_SID = test

export ORACLE_HOME = /oraeng/app/oracle/product/10.2.0

export ORACLE_BASE  = /oraeng/app/oracle/product

export  PATH = $ORACLE_HOME/bin:PATH:.

:wq!

  Step :2

Change or create the parameter file in $ORACLE_HOME/dbs

$ cd  $ORACLE_HOME/dbs

dbs $   cp init.ora  inittest.ora

dbs $  vi  inittest.ora

db_name = test

instance_name = test

shared_pool_size = 64m

control_files =  /disk1/oradata/test/control.ctl

core_dump_dest = /disk1/oradata/test/cdump

user_dump_dest = /disk1/oradata/test/bdump

background_dump_dest = /disk1/oradata/test/bdump

undo_tablespace = undotbs

undo_retention = 900(15m default) 7200

undo_management = auto

compatibility 10.2.0

:wq!

  Step :3

Create necessary directories

$ mkdir –p /disk1/oradata/test

$ cd   /disk1/oradata/test

Test $ mkdir bdump cdump udump

  Step :4

Create Database script

]$ vi create.sql
 

create database test

datafile ‘/disk1/oradata/test/system.dbf’ size 170m autoextend on

sysaux

datafile ‘/disk1/oradata/test/sysaux.dbf’ size 100m

default tablespace userdata

datafile ‘/disk1/oradata/test/userdata.dbf’ size 50m

default temporary tablespace temp

tempfile ‘/disk1/oradata/test/temp.dbf’ size 50m

undo tablespace undotbs

datafile ‘/disk1/oradata/test/undo.dbf’ size 50m

logfile

group 1(‘/disk1/oradata/test/redo1a.log’) size 4m,

group 2(‘/disk1/oradata/test/redo1b.log’) size 4m;

:wq!

Step :5

create run.sql script for all data dictionary views  procedures. packages and default users profiles

]$ vi run.sql

@$ORACLE_HOME/rdbms/admin/catalog.sql

@$ORACLE_HOME/rdbms/admin/catproc.sql

Connect system/manager

@$ORACLE_HOME/sqlplus/admin/pupbld.sql

:wq!

$ conn / as sysdba

>  startup nomount

>  @create.sql

>  @run.sql

VIEWS :   V$DATABASE    V$INSTANCE