Oracle 12c Interview Questions for freshers experienced :-
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.
1) Oracle SGA is shared between multiple databases called Pluggable databases (PDB)
2) A root database called container database which holds all the Oracle dictionary globally (CDB) shareable across PDB’s
3) Listener registers both CDB instance and PDB as services
4) There will be
- global roles and local roles
- local user and common user
- common privilege and local privilege
at CDB level and PDB level, depends on the role setting, the activities (DBA) will be performed accordingly on the local/global database.
5) CDB$ROOT holds oracle data dictionary (assumption)
6) PDB$SEED is the template kind of database for a pluggable database which can be used to create the PDB databases
7) There will be local and global datafiles, as you can see in pink boxes all datafiles that are related to PDB1/2 databases, where there are other files at last in diagram which holds all the global dictionary i.e container database
8) Background processes will be shared across, where in the foreground processes are unique to their databases, this helps to reduce the footprint of processes , Ex: If you have 10 databases in a 11g environment by default 30 BG processes will start, so 30*10 = 300 bg processes, where in 12c cases only 30 BG processes will be used for all 10 databases. User processes i.e foreground anyways depends on the application usability.
2. 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
3. Methods to create Multitenant Database
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
Via create database command and keep a clause enable container database
4. How to convert a normal database to Container enabled database?
- Upgrade and set comptabile = 12.0.0
- OPen the database in readonly mode
- Execute the dbms_pdb package describe procedure
BEGIN DBMS_PDB.DESCRIBE( pdb_descr_file => ‘/disk1/oracle/ncdb.xml’); END; / - Shutdown the non-cdb database
- 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/’); - Execute the script
@$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql - Open the read write mode new PDB
5. How to create a new PDB database?
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/’);
Cloning
CREATE PLUGGABLE DATABASE pdb2 FROM pdb1;
Plugging in
Create an xml file
create pluggable database pdb_plug_nocopy using ‘/u01/app/oracle/oradata/pdb1.xml’
NOCOPY
TEMPFILE REUSE;
alter pluggable database pdb_plug_nocopy open;
6. 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
- 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
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.
- When you want to create a common user across all databases for example, backupadmin, you must use C## as prefix
- Create user c##username identified by password; will create common user across all databases
- Create user c##username identified by password container=current; will create common user only at current container
- create user username identified by password container=all; does not work since the username does not contain c## prefix
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,
- Now you can select with rman,
- Table recovery made easy with RMAN,
- Image copies can now split into sections,
- Recover database using network from standby directly
- Duplicate database now made easy
- Rollforward standby using network service only no need of incremental backups
14. Datapump Enhancements?
- EXPDP
- IMPDP
15. Describe about Major Performance Enhancements?
- Optimizer will not use dynamic plan changes while executing called Adaptive Query Optimization
- If any object missing the statistics or found stale during execution of plan, dynamic statistics (aka sampling) will be collected with new level 11
- 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
- Clustering factor can be set according to the index skewness, not leaving to oracle
- CTAS now collect the statistics
- 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
- DBMS_STATS now can show in report mode to tell how much does it take
16. List some Partition enhancements?
- Truncate with cascade
- You can move partitions online now
- New package to maintain the partitions
- Manage multiple partitions at one time
- You can partially index
17. Indexing Enhancements
Multiple indexes on same column, infact invisible the other, bitmap and btree is possible on same column
Key Limit alleviation
18. General Enhancements
A whole lot bunch
19. What are 12c ASM Enhancements?
- FlexASM,- simple you do not need 4 asm instances for 4 nodes, 3 is enough as like scan
- ASM metadata is now copied to multiple allocation units in the disk header
- Replace disk command
- Now you can create 511 diskgroups no more 64 limit
- Now you can estimate the rebalance/resync operations
- 12c stores password files in ASM
- Check logical corruptions using disk scrubbing
- Failuregroup_repair_time for whole group in contrast to diskgroup_repair_time
- OCR backup now goes to asm disk group
20. Grid Infrastructure enhancements
- FlexCluster – HUB and Leaf spoke technology, just to ensure to couple the application layer and database layer closely
- IPV6 Support – You can have ipv6 format and ipv4 format network attachments to cluster
- Convert normal cluster to flexcluster
- crsctl commands in eval mode
21. RAC enhancements
- Application continuity – Transaction Guard – New API to protect the transactions state and reapply them in case service failure
- New failover_type = transaction when using application continuity
- Global data services
- srvctl evaluation mode
22. Security Features
- 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
- 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
- dbms_privilege_capture procedure gives you the ability to monitor the privilege usage
- Resource role does not contain the unlimited tablespace now
- select any dictionary privilege does not access the some sensitive tables like default_pwd$ tables
23. What is the basic architectural enhancement in Oracle 12c database?
Until 11g Oracle supplied data, metadata, user/schema data and metadata are all stored as single database. Starting with 12c Oracle defines an interesting architecture called the pluggable database architecture
24. Explain the basic concept behind Oracle 12c pluggable database architecture
- Pluggable database architecture separates Oracle supplied data and metadata from user created data and metadata
- Oracle supplied data and metadata is stored in container database
- User supplied data and metadata is stored in pluggable database
25. What is the major advantage of Oracle 12c pluggable database?
One container can contain many number of pluggable databases. This allows public synonyms and database links to be defined at pluggable database level
26. What is difference between traditional exp/imp vs datapump utilities
Oracle database utilities expdp/impdp is a replacement of old exp/imp utilities and there are few basic differences
- exp/impdp are faster than exp/imp
- No need to create directory in case of exp/imp
- Oracle Object reorganization
27. 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
28. 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
- Either using TNS Entries SQLPLUS sys/****@pdb as sysdba
- Log into cdb and then alter session set container=pdb
- EZconnect method sqlplus