Oracle Database 12c FAQs. Part 1. Basics Concept of Oracle Database 12c.

Oracle Database 12c FAQs. Part 1. Basics of Oracle Database 12c.

I have prepared this Article to clear basic concepts of Oracle Database 12c and its Multitenant feature. This article is in the form of Question and Answer. Every DBA who is learning Oracle Database 12c is having following question. I have just tried to answer it as my best.

One of my friends told me that your article is looks like Interview Question and Answer; I laughed  ;) , and said Yea, its look like that.

Let’s start exploring Oracle Database 12c.
Common FAQs of Oracle Database 12c, to understand very basic concepts.

Q. What is Oracle multitenant database?
Ans. In very simple meaning, => Multiple Databases, Single Instance <=

Let me describe and compare this with previous versions.

Before Oracle Database 12c, each database is having their own instance and each database requires its own processor, memory and other server resources. Most of the efforts were going to manage those individual instances, in terms of patching, migration, upgrades and performance tuning.

In addition to management issues, these databases often waste server resources because they use only a very small portion of dedicated server. Hence consolidation of these databases into a single database Oracle introduces Multitenant option, which run one or more Pluggable Databases (PDBs) within a single Oracle instance, called a multitenant Container Database (CDB).

Q. What is pluggable database?
Ans. Pluggable Database (PDB) is a database which resides in Oracle’s Container Database (CDB). PDB uses the resources of CDB i.e background processes and memory.

A PDB is a user-created set of schemas, objects, and related structures that appears logically to an application as a separate database.

Refer following architecture diagram.

Q. How SGA is define in Multitenant Database?
Ans. Container Database (CDB) is having instance, which is shared to all Pluggable databases (PDBs).
The central CDB instance, with its background processes, SGA allocation, and data dictionary, is part of the root container. This instance is common to all the PDBs within that CDB.

Q. What is root container, Seed and PDB as shown in above diagram of Multitenant?
Ans.
When you create a CDB, it’s the root container that gets created first. The root container is mandatory and contains Oracle-created metadata, common users, and common roles. Here it refers as CDB$ROOT. CDB$ROOT which is holding all PDBs.

Seed also called PDB$SEED, which is a template to create Pluggable Database (PDB). There is only one seed PDB in a CDB.

Pluggable Database (PDB) is a database which holds all user schemas and user metadata information. PDB is actual database created by user.

Q. Is root container stores user data?
Ans. No, the root does not store user data.

Q. How many PDB’s one can create in a single CDB?
Ans. One can create 252 PDBs in a single CDB.

Q. How can one segregate user of CDB & PDB?
Ans.
In multitenant database architecture users can be segregate like the local user and the common user.
Local Users:-  A local user is defined in a specific PDB and can only connect to that PDB and not to any other PDBs or the CDB.
Common user:- A common user belongs in the root container (ROOT$CDB) directory’s data dictionary and is known in all the PDBs that belong to the CDB. A common user has the same identity in the CDB as well as in all the PDBs, and can log in to any PDB in which that user has privileges. The common user performs administrative tasks for the entire CDB or for the individual PDBs.
These tasks include the plugging/ unplugging of databases and the starting/ stopping of the CDB, for example. Other tasks that the common user can perform depend on the privileges you grant.
Oracle’s SYS and SYSTEM users serve as Oracle-supplied common users.

Q. Is it mandatory to create CDB & PDB? Can we create normal database just like 9i, 10g & 11g?
Ans. No it is not mandatory to create CDB & PDB. You can still create normal database like previous version of Oracle Database.
At the time of creation of DB, installer will ask you if you want to create Database as Container Database. If you select as Container Database it will Create CDB & PDB.
Refer below screenshot for the same. If you uncheck “Create as Container Database” it will create normal orcl database.

Q. Is SPFILE or PFILE common for CDB & PDB?
Ans. Yes SPFILE or PFILE is common for both CDB & PDB. Here one thing need to understand that in Multitenant Database there is only single Instance. This instance is shared to all PDBs. Hence there is only one SPFILE or PFILE.
SPFILE or PFILE belongs to CDB only, because CDB is the one who create instance at startup.

Q. How can I create PDB after fresh installation of Oracle Database and also how can I create another database (PDB)?
Ans. There are several methods you can create PDB.
1)    Using dbca : You can create new PDB using DBCA.
2)    Clone PDB : You can clone existing PDB to create similar database. Below link will guide you how to clone PDB within CDB.
http://tanwanichandan.blogspot.in/2016/05/step-by-step-clone-pdb-within-cdb-in.html
3)    Plug normal database (non-CDB) to existing CDB.
4)    Un-plug PDB from one CDB and plug it to another CDB.

Q. How GUID is defined for CDB & PDB? Is it same for both?
Ans. GUID is different for both CDB & PDB. Each PDB has it’s own GUID.

Q. Are SYSTEM, SYSAUX & TEMP tablespaces are common for CDB & PDB?
Ans.
No, there are separate SYSTEM and SYSAUX tablespaces for the root CDB and for each PDB. You can specify a separate default tablespace for the root CDB and for each PDB.
Although there is a default temporary tablespace for the entire CDB, you can optionally create additional temporary tablespaces for use by individual PDBs.
Path of data files also be different for both CDB & PDB.

Q. Can I see OS services of CDB & PDB?
Ans.
You can only see the CDB services i.e. root container services. Here in our example ORCL is our CDB/root container whereas PDBORCL is our PDB.
Database services are bound to Database Instance. As in Multitenant there is only single instance services are also single which is bound to an Instance.

Q. What does CON_ID means in v$database, v$containers and other views?
Ans.CON_ID means CONtainer ID.
CON_ID 1 represents the root container, and
CON_ID 2 represents the PDB SEED database also
CON_ID 3 and onward represents to PDBs.

Q. How administrative tasks are different for PDB DBAs & CDB DBAs in 12c?
Ans.
Similar Task for PDB & CDB Admins
•    Managing tablespaces
•    Managing data files and temp files
•    Managing schema objects

CDB Admin only task.
•    Starting and shutting down the CDB instance
•    Creating, plugging/ unplugging, and dropping PDBs
•    Modifying the CDB or the root
•    Executing DDL statements on a CDB or the root
•    Managing instance-level components such as processes, memory, alerts, control files, undo, and the online/ archived redo log files.

Q. Type of views in Oracle 12c
Ans.
•    CDB_ XXX    :-  All objects in the CDB across all the PDBs
•    DBA_ XXX     :-  All objects in a container or PDB
•    CDB_ p dbs    :-  All PDBs in a CDB
•    CDB_ tablespaces :-  All tablespaces in a CDB
•    CDB_ users    :- All users in a CDB (both common and local users)

Q. How to check if Database is CDB or not.
Ans. Run below Query,
select name,cdb,con_id from v$database;

If CDB column returns YES the database is CDB.

Q. Can we assign default tablespace to each PDB & CDB?
Ans. Yes, you can assign default tablesapce to each PDB.

Example:-
For PDB -> conn sys@pdb1
alter pluggable database pdb1 default tablespace users;
For CDB -> conn system@cdb1
alter database default tablesapce users1;

Q. What are common user & local user?

Ans.

Common User	Local User
Common User can create local user & common user.	Local user can create local user but cannot create common user.
Common user start with c##	Local user name cannot start with c##
“container” clause can be omit while creating common user. By default container=all for common user.	“container” clause require to create local user.
One must connected to root container to create common user.	One should provide container clause to create local user.
Common and Local user both can grant privilege locally.	Common and Local user both can grant privilege locally.

Please provide your comments & suggestions on this Article.

Oracle Database 12c FAQs. Part 2. Administration of Oracle Database 12c ... Coming Soon...

Thanks & Regards,

Chandan Tanwani

Oracle Performance Tuning Certified Expert