Interview Q and A for Oracle12c Part - 1

1. 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.

2. What are the common concepts of multitenant database?

Multitenant database consists of

1. CDB is a container database which is similar like standalone database. Called CDB$ROOT
2. PDB$SEED is a template database to create a databases within the CDB databases
3. PDB<n> are individual or application databases
4. Data dictionary between this databases are shared via internal links called object link and data link
5. Users between CDB and PDB are different, there will be common users (starts with C##) and local users
6. When the CDB starts up, the PDB will be in mount state, you must open them exclusively

3. Methods to create Multitenant Database

1. Via DBCA when creating database there is an check box "enable container database" and then provide PDB details, this will automatically creates the CDB,PDB$SEED, PDB databases
2. Via create database command and keep a clause enable container database

4. How to convert a normal database to Container enabled database?

1) Upgrade and set comptabile = 12.0.0
2) Open the database in readonly mode
3) Execute the dbms_pdb package describe procedure
BEGIN DBMS_PDB.DESCRIBE( pdb_descr_file => ‘/disk1/oracle/ncdb.xml’); END; /
4) Shutdown the non-cdb database
5) If in same server your CDB database contains
CREATE PLUGGABLE DATABASE ncdb USING ‘/disk1/oracle/ncdb.xml’ COPY FILE_NAME_CONVERT = (‘/disk1/oracle/dbs/’, ‘/disk2/oracle/ncdb/’);
6) Execute the script
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql
7) Open the read write mode new PDB

5. How to create a new PDB database?

1. Copying

SQL> CREATE PLUGGABLE DATABASE salespdb ADMIN USER salesadm IDENTIFIED BY password 
STORAGE (MAXSIZE 2G MAX_SHARED_TEMP_SIZE 100M) DEFAULT TABLESPACE sales        DATAFILE ‘/disk1/oracle/dbs/salespdb/sales01.dbf’ SIZE 250M AUTOEXTEND ON 
PATH_PREFIX = ‘/disk1/oracle/dbs/salespdb/’ 
FILE_NAME_CONVERT = (‘/disk1/oracle/dbs/pdbseed/’, ‘/disk1/oracle/dbs/salespdb/’);

2. Cloning
1. CREATE PLUGGABLE DATABASE pdb2 FROM pdb1;
3. Plugging in
1. Create an xml file
2. create pluggable database pdb_plug_nocopy using ‘/u01/app/oracle/oradata/pdb1.xml’
   NOCOPY
   TEMPFILE REUSE;
3. alter pluggable database pdb_plug_nocopy open;

6. 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

7. How to connect to PDB from a CDB?

In a container enabled databases , once the pdb is created , a service will be automatically created on same name for example pdb

1. Either using TNS Entries SQLPLUS sys/****@pdb as sysdba

2. Log into cdb and then alter session set container=pdb

3. EZconnect method sqlplus

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

1. Datafiles are individual to each database for cdb and each pdb
2. Undofiles and redofiles are only one across container
3. Tempfiles can be created in each database or share one across all databases
4. SGA is shared across all databases
5. Background process are shared across all databases , no additional back ground process defined

9. 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.

10. How does the data dictionary works to manage multiple database, for example if I used PDB1 to select a query from emp.

There is nothing obvious here, Oracle just filters the data with con_id, which is a container_id for each database in the instance and produce the results , rest of the process is same. To understand this check any v$ view or dba_view you will find the con_id column which states that what database that row belongs to.  This is something like VPD where the rows will be masked and present to the database users where they have privileges, similarly the rows will be shown only what database you have logged in.