Although a student becomes adept at handling any type of question after successfully completing the Oracle tutorial and certification from the institutes of GangBoard, this list of Oracle interview questions and answers will come in handy for a quick revision and when you are a bundle of nerves before the interview. However, this Oracle interview question and answer is for not only learned Oracle professionals but also beginners and those with no knowledge can use it to understand the terminologies and the fundamentals of Oracle. The Q & A includes topics from beginner, intermediate, to the most advanced level explained in Layman’s language.
Best Oracle Interview Questions and Answers
This field is growing at an astounding pace due to the high demand for certified professionals. Companies prefer individuals who are certified in the technology. We have observed that students who are adept in the use of Oracle and even score high marks in the certification fail to justify the same during the interview. This is one of the reasons why we have compiled the elaborate list of questions on Oracle and the answers are provided by the expert tutors who themselves are Oracle professionals and several are recruiters themselves for leading multinationals hiring Oracle certified professionals.
These Oracle interview questions and answers are based on mega trending topics. If you master most of these topics, there will be no Oracle interview question that you cannot answer and secure your dream job.
Q1) What is the use of Oracle ASM?
- ASM is a file system for oracle, and controlled by oracle volume manager.
- Oracle asm used for standalone databases as well as Cluster database (RAC)
Q2) Difference between normal file system and ASM?
Load balancing is high in ASM file system compare to the traditional file system. IN oracle asm we call it as ASM striping.
- To balance the loads across all the disks in a disk group
- The I/O latency is very low
- And also we can specify the redundancy level Normal or High like RAID.
Q3) What is ORACLE AFD?
Oralce asm Filter driver(ASMFD). It is a kernel module resides in the I/O paths of the oracle ASM disks. It is optional one. After completed Grid installation we need to configure the ASMFD to the system. In case if we used oracle asmlib we need to migrate ASMLIB to ASMFD. Then we need to cleanup the oracle asmlib. Usually when the server restarted the need rebind the disk devices with oracleasm, to simplifies this the Oracle ASMFD we need to configure. It will allow only oracle I/O requested and will rejects the invalid or non Oracle I/O requests.
Q4) How to create Disk Group?
The disk group will have one or more number of disks, And is the fundamental object for ASM manager.
To create the disk we need to use the command
oracleasm createdisk ORADATA /dev/sdb1
Oracle database files are allocated from disk groups. A disk groups might contain several databases. And single database can use multiple disk groups. What are all files we are storing inside the disk groups we call it as ASM files.
Q5) How to list the Disk Groups?
- To list the disk groups we are having more views below are the some example
- V$ASM_DISKGROUP – Group name, size related information, state
- V$ASM_FILE – list the ASM files and the groups
- V$ASM_ALIAS – Disk group mounted by oracle ASM
Q6) How to drop Disk groups?
- We need to mount the disk group which we are going to drop.
- Need to specify INCLUDING CONTENTS for dropping all the files in this disk group.
- DROP DISKGROUP dg_data_01 INCLUDING CONTENTS;
- Need to perform the above command in ASM instance not normal database instance.
Q7) Difference between the ORACLE asmlib and ORACLE afd?
- Oracle asm usually rebind the asm files once the server restarted
- Oracle asm might allow non oracle I/O to Oracle files
- Oracl ASMFD is a kernel module and it will avoid the non oracle I/O request.
- ORACLE ASMFD is available from the release 12.2 linux verion
Q8) How to start ASM instance?
ASM instance will work as oracle normal instance. It will mount the disk groups to make it ready ASM files for the database. It has minimal amount of SGA compare to normal instance.
Q9) What is the use of ASM instance?
ASM instance will work as oracle normal instance. It will mount the disk groups to make it ready ASM files for the database. It has minimal amount of SGA compare to normal instance. In cluster database we will have separate ASM instance for each nodes.
Q10) What is ACFS file system?
- Oralce automatic storage management cluster file system(ACFS).
- The ACFS files can be accessed from outside of the databases.
- It is a scalable and multi-platform file system
- ACFS does not support Grid infrastructure files
- ACFS does not support OCR and voting disk related files
We can use the ACFS file system for backup related information like RMAN, Data pump DUMPSET and archived log files
Q11) What is the use of OCR file?
OCR is a file that contain the information about the Oracle database configuration information to manages cluster. It will create and placed on shared storage while installation oracle cluster ware.
Q12) What is the use of Voting Disk?
The Voting disk is a file that contains information about node membership. It will create and placed on shared storage while installation oracle cluster ware. It will reassign the cluster ownership between the nodes in case of failure.
Q13) What is the use of CRS in oracle RAC?
CRS- cluster ready service provides the cluster software which is installed in each hosts via OUI cluster installation. Oracle clusterwate manages as called as CRS resources. Like database , instances VIP address , listener and services
Q14) What are all the cluster ware storages?
- Raw devices
- OCFS file system
- ASM file system
Q15) What is cache fusion in oracle RAC?
For instance recovery in RAC database it will first look into the cache, if it is not available it will look in to the remote cache instead of looking into the disk. This is call it as cache fusion. We are using cache coherency mechanism for cache fusion. IT depends on three services.
- Global Resource Directory (GRD)
- Global Cache Services (GCS)
- Global En-queue Services (GES)
- Where we need to use CVU?
- Cluster verification utility (CVU) to verify that all the nodes will meets all the criteria for oracle clusterware installation.
Q16) How to start database in Oracle Rac database?
We can use SRVCTL command to start the database in oracle RAC environment.
- To start all the instance
- Srvctl start instance –db db_name
- To start a particular instance using by using instance name
- srvctl start instance –db db_name –instance “instance_name1,instance_name2”
- To start the database using node name
- Srvctl start instance –db db_name –node node_name