Oracle 12c Interview question & answers

Here you go, The complete list of Oracle 12c New Features is here

Architectural & Administration Questions

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.

You can read more here,

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

Read more here, here, here,

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. CopyingSQL> 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;

Read more here

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

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

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

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

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

9. How about creating a user?

Well, this is something you have to be careful

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, backupadmin, 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

Read more here

10. How does the data visibility in container databases works? Suppose when I logs as common user and want to verify the PDB information,

For example, dba_users, just filter with con_id,

select username from dba_users where con_id=3

Note: CDB is 0, PDB$SEED is 1 etc.

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

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

12. So then, how to take a backup in Multitenant database? Shall i Take backup in rman does it take backup of all databases?

Yes, if you use backup database this will take all databases backup,

To take the backup of only one particular db, use backup pluggable database

Note: Archivelog backups will not be taken using pluggable database backup

13. Tell about rman enhancements?

RMAN Enhancements,

1. Now you can select with rman,
2. Table recovery made easy with RMAN,
3. Image copies can now split into sections,
4. Recover database using network from standby directly
5. Duplicate database now made easy
6. Rollforward standby using network service only no need of incremental backups

14. Datapump Enhancements?

1. EXPDP - Here
2. IMPDP – Here

15. Describe about Major Performance Enhancements?

1. Optimizer will not use dynamic plan changes while executing called Adaptive Query Optimization
2. If any object missing the statistics or found stale during execution of plan, dynamic statistics (aka sampling) will be collected with new level 11
3. Optimizer now stores the execution information in sysaux tablespace about the individual object execution statistics and create directives called sql plan directives which can be used later execution to further optimize the statement
4. Clustering factor can be set according to the index skewness, not leaving to oracle
5. CTAS now collect the statistics
6. Global Temporary tables have private session statistics no more it uses the one statistics for all, very useful for different batches run at same time
7. DBMS_STATS now can show in report mode to tell how much does it take

16. List some Partition enhancements?

1. Truncate with cascade
2. You can move partitions online now
3. New package to maintain the partitions
4. Manage multiple partitions at one time
5. You can partially index

17. Indexing Enhancements

1. Multiple indexes on same column, infact invisible the other, bitmap and btree is possible on same column
2. Key Limit alleviation

18. General Enhancements

A whole lot bunch , please read here directly

19. What are 12c ASM Enhancements?

1. FlexASM,- simple you do not need 4 asm instances for 4 nodes, 3 is enough as like scan
2. ASM metadata is now copied to multiple allocation units in the disk header
3. Replace disk command
4. Now you can create 511 diskgroups no more 64 limit
5. Now you can estimate the rebalance/resync operations
6. 12c stores password files in ASM
7. Check logical corruptions using disk scrubbing
8. Failuregroup_repair_time for whole group in contrast to diskgroup_repair_time
9. OCR backup now goes to asm disk group

20. Grid Infrastructure enhancements

1. FlexCluster – HUB and Leaf spoke technology, just to ensure to couple the application layer and database layer closely
2. IPV6 Support – You can have ipv6 format and ipv4 format network attachments to cluster
3. Convert normal cluster to flexcluster
4. crsctl commands in eval mode

21. RAC enhancements

1. Application continuity – Transaction Guard – New API to protect the transactions state and reapply them in case service failure
2. New failover_type = transaction when using application continuity
3. Global data services
4. srvctl evaluation mode

22. Security Features

1. A wide range of auditing is enabled and all auditing details will be stored initially in memory tables and then flushed to unified_audit_trail
2. Unified auditing is a consolidated audit for database where all operations like rman backup/restores, expdp/impdp, general audits, fga will be collected and stored in unified_audit trail table instead of sys.aud$ table and this new table is placed sysaux tablespace
3. dbms_privilege_capture procedure gives you the ability to monitor the privilege usage
4. Resource role does not contain the unlimited tablespace now
5. select any dictionary privilege does not access the some sensitive tables like default_pwd$ tables

Still more to write, will be keep on adding as and when I get time.