Oracle 12c – Multitenant Architecture & Creating database manually

-
Containers in Multitenancy
Oracle Database 12c introduced a new feature called “multitenant.” The multitenant feature provides the ability for a single instance to manage multiple databases. The multitenant architecture enables an Oracle database to function as a multitenant container database (CDB).
The multitenant architecture comprises the following :

“Multitenant” Database Instance:
This is a term used to distinguish between a database instance that supports multitenant database and one that does not.

Container Database (CDB):
This is the database that is created when that database supports Oracle’s multitenant option. It’s also called the ROOT container and is the CDB$ROOT within the data dictionary views of the CDB.

Root Container Database: This is created automatically when you create a multitenant database. The root container contains the data dictionary for the CDB.

Pluggable Database (PDB): These are the databases that are stored within the CDB. A PDB is a portable collection of schemas, schema objects, and non schema objects that appears to an Oracle Net client as a non-CDB.
Note :
A Container Database (CDB) comprises zero, one, or many customer-created pluggable databases. All Oracle databases before Oracle Database 12c were non-CDBs.
Oracle Multitenant offers the ability to have up to 252 PDBs per multitenant container database. The multitenant architecture with one user-created pluggable database (single tenant) is available in all editions without the multitenant option.
Database Environment Before Database Consolidation
 
Single Container Database

Container DB with Seed & PDB’s
The ROOT
The root container, also called the root, is a collection of schemas, schema objects, and nonschema objects to which all PDBs belong. Every CDB has one and only one root container, which stores the system metadata required to manage PDBs. All PDBs belong to the root.
The name of the root is CDB$ROOT.
The root does not store user data. Thus, you must not add user data to the root or modify system supplied schemas in the root.
However, you can create common users and roles for database administratio.
A common user with the necessary privileges can switch between PDBs.

The SEED
The seed PDB is a system-supplied template that the CDB can use to create new PDBs.
The seed PDB is named PDB$SEED.
You can add or modify objects in PDB$SEED.

Pluggable Database
A Pluggable database (PDB) is a user-created entity that contains the data and code required for a specific set of features.
For example, a PDB can support a specific application, such as a human resources or sales application.
No PDBs exist at creation of the CDB. You add PDBs based on your requirement.
Currently Oracle 12c supports up to 252 Pluggable Databases in a single container.

Data Objects in a CDB
A container data object is a table or view containing data pertaining to multiple containers and possibly the CDB as a whole, along with mechanisms to restrict data visible to specific common users through such objects to one or more containers.
Examples of container data objects are Oracle-supplied views whose names begin with V$ and CDB_.
All container data objects have a CON_ID column shows the meaning of the values for this column.

How to create/set up container database
There are three ways to create container database, as follows:
  1. Database Configuration Assistant (DBCA)
  2. Oracle Universal Installer (OUI)
  3. Manual Creation

Manual Creation:
Steps for creating container database :
  • Set environmental variable by using .bash_profile
  • Create parameter file (pfile).
  • Create directory structure.
  • Keep the database in nomount state.
  • Create database creation script and run at SQL prompt.
  • Post database creation steps: -Run the required scripts at SQL prompt.
Prerequisites for creating database:
  • Sufficient primary memory should be available to start the Oracle instance.
  • Sufficient disk space must be available on the computer.
Step 1: setting environment variable with  .bash_profile
Step 2: create parameter file (pfile)
Step 3: create directory structure
Step 4:  Connect to SQL* PLUS  and  keep the database in nomount state.
Step 5: Create database creation script .
Run at SQL prompt.
Step 6 : Run the database scripts to create the required views and tables in the database.
Inorder to view the data dictionary views from the seed db, we need to execute the catalog, catproc and pupbld scripts using catcon.pl (os level) in the seed db i.e. PDB$SEED database.









Comments

Post a Comment

Popular posts from this blog

Step by Step: How to troubleshoot a slow running query in Oracle

-
This is a day to day task of a DBA. Someone from application team comes to our desk and simply says a query is running slow. We may question him on many things but ultimately a DBA has to tune the query at the end. This is also very popular and known question for the DBA's who are attending interviews. This question is simply asked again and again. I have also been asked this question multiple times. After a pause, interviewer simply asks...so suppose a user comes and reports that his query is running slow, what will be your approach to tune this. There is no absolute or concrete answer to this question because there might be multiple way to tune a slow running query. Everyone can have a different approach. So here is my approach: Very first thing we have to find which type of query is running slow. Whether it is SELECT query of DML statements(insert, update, delete and merge). So first we will see how to tune a SELECT query Step 1 – Find the SQL_ID of the ...
Read more

Register Archive log file manually in standby Database

-
  Some time, the archive logs are not shipping to standby location due to network issue.  There may be  several  archive logs are waiting in standby location for one archive log file. For instance, Archive log sequence # 100 is not shipped to standby location. But other archive logs(sequence#101 to sequence#200) were shipped to standby and it is waiting to ship the sequence# 100.  In this scenario, DBA can copy the archive log file and register manually. Once manually registered, the sequence#101 to sequence#200 will apply automatically on standby database. Step 1 Let us assume, the archive log file devdb12_1_283_801988635.arc is not shipped to standby due to some reason. Copy the archive log file devdb12_1_283_801988635.arc manually from primary to standby archive destination location. Step  2 Run the below command in standby database. ALTER DATABASE REGISTER LOGFILE '/dbArch/oradata/devdb12/ devdb12_1_283_801988635.arc';   Step  3 Verify th...
Read more

How to check UNDO tablespace usage and who is using more undo

-
Query To check UNDO tablespace usage: ================================ set linesize 152 col tablespace_name for a20 col status for a10 select tablespace_name,status,count(extent_id) "Extent Count", sum(blocks) "Total Blocks",sum(bytes)/(1024*1024*1024) spaceInGB from   dba_undo_extents group by  tablespace_name, status having tablespace_name=upper('&TSNAME') order by tablespace_name; Query To check who is using more UNDO space: ===================================== SET termout ON SET heading ON SET PAGESIZE   6000 SET LINESIZE   200 COLUMN pgm_notes    FORMAT a80        HEADING 'Notes' COLUMN rbs          FORMAT a16         HEADING 'RBS'             JUST center COLUMN oracle_user  FORMAT a12        HEADING 'Oracle|Username' COLUMN sid_serial   FORMAT a12      ...
Read more