AWS Administration - 100% Lab - Online Training | AKSWAVE
14
Jul

Oracle 12c Container database creation manually ( step-by-step)

  • 4.9
  • Reviews : 642

Oracle 12c database is a MULTITENANT CONTAINER DATABASE provides space for creating pluggable databases (Eg Pluggable databases are applications like any OLTP databases etc.,) Oracle 12c database is a Multi-tenant container database in which a seed  database called pdb$seed comes with it, Using which we can create pluggable databases with in the container database.  In One Container 253 pdbs can be created including pdbseed.

Course : ORACLE 12C DBA


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

Using the CREATE DATBASE SQL statement is a more manual approach to creating a database. If you use the CREATE DATABASE statement, you must complete additional actions before you have an operational database. These actions include building views on the data dictionary tables and installing standard PL/SQL packages. You perform these actions by running prepared scripts and are called as perl scripts in this version.

1.       Specify an Instance Identifier (SID) & Environment Variables

ORACLE_SID, unique Oracle system identifier (SID) is used to distinguish this instance from other Oracle Database instances that you may create later and run concurrently on the same host computer. The maximum number of characters for ORACLE_SID is 12, and only letters and numeric digits are permitted.

In our case ORACLE_HOME is on /u01/app/oracle/product/12.1.0.2/dbhome_1 path where we have Oracle software Installed.

$ cd

$ vi .bash_profile

export ORACLE_SID=cdb1

export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/dbhome_1

export PATH=$ORACLE_HOME/bin:$PATH:$ORACLE_HOME/perl/bin

export PERL5LIB=$ORACLE_HOME/rdbms/admin

$ . .bash_profile

2.       Ensure That the Required Environment Variables Are Set

$ echo $ORACLE_SID

$ echo $ORACLE_HOME

$ echo $PATH

3.       Create the Initialization Parameter File

$ cd $ORACLE_HOME/dbs

$ cp init.ora initcdb1.ora (cdb1 is SID here )

Open your pfile and edit with the following parameters

$ vi initcdb1.ora

db_name='cdb1'

enable_pluggable_database=true

audit_trail='db'

control_files='/disk1/oradata/cdb1/control01.ctl'

diagnostic_dest=/disk3/oradata/cdb1

db_block_size=8192

memory_target=429145600

memory_max_target=429145600

open_cursors=300

processes=300

remote_login_passwordfile='EXCLUSIVE'

undo_tablespace='UNDOTBS1'

compatible=12.1.0.2

4.       Create directory structure

$ mkdir -p /disk1/oradata/cdb1/pdbseed

$ mkdir -p /disk2/oradata/cdb1

$ mkdir -p /disk3/oradata/cdb1

5.       Create script for controlfile

$ vi $HOME/cr8cdb.sql

 

CREATE DATABASE cdb1

LOGFILE

GROUP 1 ('/disk1/oradata/cdb1/redo01a.log') SIZE 10M,

GROUP 2 ('/disk1/oradata/cdb1/redo02a.log') SIZE 10M

EXTENT MANAGEMENT LOCAL

DATAFILE '/disk1/oradata/cdb1/system01.dbf' SIZE 400M

UNDO TABLESPACE undotbs1 DATAFILE '/disk1/oradata/cdb1/undotbs01.dbf' SIZE 150M

SYSAUX DATAFILE '/disk1/oradata/cdb1/sysaux01.dbf' SIZE 400M

DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/disk1/oradata/cdb1/temp01.dbf' SIZE 20M

DEFAULT TABLESPACE USERS DATAFILE '/disk1/oradata/cdb1/users01.dbf' SIZE 20M

ENABLE PLUGGABLE DATABASE

SEED FILE_NAME_CONVERT =

('/disk1/oradata/cdb1','/disk1/oradata/cdb1/pdbseed');

 

Here in the above script seed database template called “pdbseed” is created along with Container database “cdb1” which is must for a container database.Pdbseed (template) is used to create Pluggable databases within the container database cdb1.

Note: If you want to create a database without seed it is called as non container database, which is similar to 11g database.

There are a few clauses of the CREATE DATABASE statement relevant only to pluggable databases. For instance,the ENABLE PLUGGABLE DATABASE clause is required if you want to create a pluggable database within the CDB.

The 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 SEEDFILE_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

Now, start up your database in nomount mode, and run the cr8cdb.sql script

6.       Connect and start the Instance

$ sqlplus / as sysdba

SQL> startup NOMOUNT

SQL> @$HOME/cr8cdb.sql

SQL> EXIT

7.       Build Data Dictionary Views

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

Create the script

$ ls

$ mkdir output

$ cd output

$ vi postcdb.sql

!$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys/oracle -d $ORACLE_HOME/rdbms/admin -b catalog_output -e -l $HOME/output catalog.sql

!$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u sys/oracle -d $ORACLE_HOME/rdbms/admin -b catproc_output -e -l $HOME/output catproc.sql

!$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u system/manager -d $ORACLE_HOME/sqlplus/admin -b pupbld_output –e –l $HOME/output pupbld.sql

Execute the script

Terminal 1:

$ sqlplus / as sysdba

SQL> @$HOME/output/postcdb.sql

Open another terminal

Terminal 2:

$ cd output

$ ls

$ tail -f catalog_output0.log

After completion of catalog give Ctl+c

Ctl+c

$ ls

$ tail -f catproc_output0.log

After completion of catproc give Ctl+c

Ctl+c

$ ls

$ tail -f pupbld_output0.log

After completion of pupbld close terminal 2 and continue with terminal 1.

8.       Verifying that a CDB was Created

To verify whether a database was created as a CDB, first connect to the root container as SYS.

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;

SQL> SELECT dbid,con_id,name,open_mode from v$database;

DBID CON_ID NAME OPEN_MODE

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

635405614 0 cdb1READ WRITE

 

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 the seed pluggabledatabase. You can check with this query:

SQL> SELECT con_id, name from v$containers;

CON_ID NAME

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

1 CDB$ROOT

2 PDB$SEED

SQL> EXIT

9.       Build Data Dictionary Views on pdb$seed

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

$ cd

$ vi postpdb.sql

spool postpdb.log

@$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

Connect system/manager

@$ORACLE_HOME/sqlplus/admin/pupbld.sql

Spool off

10.   Connect to the database and by default pdb$seed is in mount state so open the pdb$seed and execute the script

$ sqlplus / as sysdba

Set the parameter “_oracle_script” is true

SQL> alter session set "_oracle_script"=true;

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

SQL> alter pluggable database pdb$seed close;

SQL> alter pluggable database pdb$seed open;

Pluggable database altered.

 

SQL> @$HOME/postpdb.sql

SQL> alter session set "_oracle_script"=false;

11.   Check the pdb information

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 4062472313 PDB$SEED READ WRITE

 

SQL> EXIT


Akal Singh
Oracle Certified Master, 20+ yrs exp
Akal Singh stands at the forefront of the fastest moving technologies in IT Industry. He spent his past 20 years as Oracle DBA with skills into DBA Support, High Availability Design & Implementations, Technical Solutions, Automation using Scripting, Database Designing & as a Corporate Trainer too. With deep technical industry knowledge, Akal Singh has implemented many real time projects on advance Database areas.

His certification list includes many OCP, Oracle Certified Expert/Specialist (OCE) and Oracle Certified Master (OCM). He is an expertise in OS Administrations, Virtualizations/VMWare and Oracle Database 8i/9i/10g/11g & 12c, RAC, Data Guard, ASM, Oracle Exadata, Oracle Performance Tuning, Golden Gate, Streams, Oracle Security & many more advance technologies.

Akal Singh is also recognized as Senior Corporate Instructor and worked with Oracle University training division providing more than 300 corporate trainings into database advance concepts.

Certifications include :
  • Oracle Certified Professional (OCP) 9i
  • Oracle Certified Professional (OCP) 10g
  • Oracle Certified Professional (OCP) 11g
  • Oracle 10g Certified RAC Expert (OCE)
  • Oracle 11g Certified Expert (RAC) and Grid Infrastructure (OCE)
  • Oracle 10g Certified Master (OCM)
  • Oracle 11g Exadata Certified Implementation Specialist
  • Oracle Database 12c: RAC and Grid Infrastructure Administrator
  • Oracle Exadata X5 Administration
  • Oracle RAC 11g Release 2 and Grid Infrastructure Administration
  • Foundation Certificate in IT Service Management  ( ITIL Certificate ) 

Related Course

Oracle 12C DBA

( 5)
Reviews : 2,567

Classroom

 10,000

 14,000

29% Off

Online

 20,000

 22,000

9% Off

Related Documents

Oracle 12c Container database creation manually ( step-by-step)

Oracle 12c database is a MULTITENANT CONTAINER DATABASE provides space for creating pluggable databases (Eg Pluggable databases are applications like any OLTP databases etc.,) Oracle 12c database is a Multi-tenant container database in which a seed  database called pdb$seed comes with it, Using which we can create pluggable databases with in the container database.  In One Container 253 pdbs can be created including pdbseed.

Oracle 12c Physical standby database configuration (step-by-step)

Here will build a physical standby setup. Our assumption is primary database is already up and running fine And ORACLE_HOME is also installed on standby server.

12c Oracle Database Creation manually (step-by-step)

Using the CREATE DATBASE SQL statement is a more manual approach to creating a database. If you use the CREATE DATABASE statement, you must complete additional actions before you have an operational database. These actions include building views on the data dictionary tables and installing standard PL/SQL packages. You perform these actions by running prepared scripts.

0 Comments

Post a Comment