Oracle 12c Multi-tenant Interview Questions

Interview questions specific to Oracle 12c Multi-tenant architecture. Enjoy!

Q. What are the major changes in architecture for 12c?

From 12c Onwards, the instance is shared with multiple databases.

This multiple databases are self contained and pluggable from one database to another database. This is very useful methodology where database consolidation.

In short a single SGA and background process will be shared to multiple databases, the databases can be created on fly and drop or attach and detach from one server to another server.

Q. What is a pluggable database (PDB) in Multitenant Architecture?

Pluggable Databases (PDBs) is new in Oracle Database 12c Release 1 (12.1). You can have many pluggable databases inside a single Oracle Database occurrence. Pluggable Databases are always part of a Container Database (CDB) but a PDB looks like a normal standalone database to the outside world.

Q. Does In-memory require a separate license cost?

Yes, In-memory does require a separate license cost.

Q. Why would I consider using the Multitenant option?

You should consider following Database Consolidation Goals to achieve:

• Reduce Total Cost of Operation

  • Administration costs

  • Operations costs

  • Data center costs

  • Storage costs

  • Contingency costs

• Improve Functionalities

  • Resource utilization

  • Manageability

  • Integration

  • Service management

• Must not need to change applications

• Must not harm performance

• Must provide resource management and isolation between applications

• Must simplify patching and upgrade of Oracle Database

Q. What are the common concepts of multitenant database?

Multitenant database consists of

• CDB is a container database which is similar like standalone database. Called CDB$ROOT

• PDB$SEED is a template database to create a databases within the CDB databases

• PDB<n> are individual or application databases

• Data dictionary between this databases are shared via internal links called object link and data link

• Users between CDB and PDB are different, there will be common users (starts with C##) and local users

• When the CDB starts up, the PDB will be in mount state, you must open them exclusively

Q. Will CDB’s become the future?

Yes, Oracle has already depreciated standalone database development. No further improvements will be released in future for standalone databases.

Q. How many PDBs can you create?

12cR1 –> 252 PDBs

12cR2 –> 4096 PDBs

Q. Can multiple CDBs run on the same server?

Yes!

Q. Can multiple CDBs run out of the same ORACLE_HOME installation?

Yes, you can invoke DBCA and create new CDBs out of same Oracle_Home

Q. What are the methods to create Multitenant Database?

• DBCA method

• DBCA silent method

• Manual method using CREATE DATABASE statement

Q. What is the limit of container databases (CDBs) on a server?

As many as supported by server CPU, RAM and Hard disk space.

Q. How do I know if my database is Multitenant or not?

You can use below query to identify a CDB database:

SELECT NAME, OPEN_MODE, CDB FROM V$DATABASE;
SHOW CON_ID;
SHOW CON_NAME;

Q. How to distinguish you are in CDB or PDB?

Once you logged in you can check show con_name or con_id will show you which db you are in.

Q. What are the different ways you can create a PDB?

• Copying from PDB$SEED

• Copying from another PDB

• Copying from a remote PDB

• Converting a Non-CDB into PDB

• Unplugging and plugging in a PDB

Q. Can I have one PDB at release 1, and a second PDB at release 2?

No, one instance, one version for all PDBs.

Q. What Pluggable databases do we have in this container database ?

You can check this by querying v$containers:

SELECT NAME, OPEN_MODE FROM V$CONTAINERS;

SHOW pdbs;

Q. How do you switch from one container to another container inside SQL*PLUS?

ALTER SESSION SET CONTAINER=pdb1;

Q. How about the datafiles system, sysaux , undo, redo etc , does they create when you create PDB?

• Datafiles are individual to each database for cdb and each pdb

• Undofiles and redofiles are only one across container

• From 12cR2 onwards we can create local undo for each PDB

• Tempfiles can be created in each database or share one across all databases

• SGA is shared across all databases

• Background process are shared across all databases , no additional back ground process defined

Q. Is the alert log the same for all pdbs in a cdb, or are they different?

Yes, one CDB, one alert log.

Q. How can I connect to a PDB directly from SQL* PLUS?

You can use Oracle easy connect method to connect a PDB directly.

CONNECT username/password@host[:port][/service_name][:server][/instance_name]

OR

sqlplus user/password@//localhost/pdb2

Q. How do I switch to main container Database?

ALTER SESSION SET CONTAINER = CDB$ROOT;

Q. As you said, if SGA and background process are shared, is there any performance impact?

Ideally this architecture is used for database consolidation projects which and where small databases are shared in a single database host and not that high critical applications running. This leverages the reduction in licensing cost and also resource utilization effectively.

Q. How do I start up a Pluggable database?

From CDB$ROOT container

ALTER PLUGGABLE DATABASE PDB1 OPEN;

Q. How about creating a user?

Normally you will use create user username identified by password, however this is not work anymore.

1. When you want to create a common user across all databases for example, you must use C## as prefix

2. Create user c##username identified by password; will create common user across all databases

3. Create user c##username identified by password container=current; will create common user only at current container

4. create user username identified by password container=all; does not work since the username does not contain c## prefix

Q. How about AWR data, does it common across all databases or individual to database?

That is why you have individual sysaux tablespace for each database, whenever the AWR statistics collected the statistics will be pushed respective databases not the common sysaux. Since this will give you the ability to have self contained database where if you plug this database to another instance, the statistics will not lost.

Q. Which parameters are modifiable at PDB level?

select NAME, ISPDB_MODIFIABLE from V$PARAMETER;

Q. What is the difference between Container ID Zero and One?

CON_ID “0” means data does not pertain to any particular Container but to the CDB as a whole. For example, a row returned by fetching from V$DATABASE pertains to the CDB and not to any particular Container, so CON_ID is set to “0”. A CONTAINER_DATA object can conceivably return data pertaining to various Containers (including the Root which has CON_ID==1) as well as to the CDB as a whole, and CON_ID in the row for the CDB will be set to 0.

Following table describes various values of CON_ID Column in Container Data Objects.

0 = The data pertains to the entire CDB

1 = The data pertains to the root

2 = The data pertains to the seed

3 – 254 = The data pertains to a PDB, Each PDB has its own container ID.

Q. Are there any background processes ex, PMON, SMON etc associated with PDBs?

No. There is one set of background processes shared by the root and all PDBs.

Q. Are there separate control file required for each PDB?

No. There is a single redo log and a single control file for an entire CDB.

Q. Are there separate Redo log file required for each PDB?

No. There is a single redo log and a single control file for an entire CDB.

Q. Can I monitor SGA usage on a PDB by PDB basis?

SQL> alter session set container=CDB$ROOT;
SQL> select POOL, NAME, BYTES from V$SGASTAT where CON_ID = '&con_id';
SQL> select CON_ID, POOL, sum(bytes) from  v$sgastat group by CON_ID, POOL order by  CON_ID, POOL;

Q. Do I need separate SYSTEM and SYSAUX tablespaces for each of my PDB?

There is a separate SYSTEM and SYSAUX tablespace for the root and for each PDB.

Q. Where is user data stored in CDB?

In a CDB, most user data is in the PDBs. The root contains no user data or minimal user data.

Q. How can I create a pluggable database ?

sql> create pluggable database x admin user a identified by p;

Q. How to drop a PDB irrevocably?

sql> drop pluggable database x including datafiles;