Oracle DBA is the database administrator who is an expert in managing databases using Oracle DBMS. Oracle DBA would have all the rights to databases that a general developer won’t have. So it’s clear that it is one of the most prestigious jobs in the software and IT field. Not only this, there are so many opportunities and perks associated with this job which attracts more and more people to this job.
This is why several people apply for this job and they want to get an Oracle DBA job. The thing is there are many job candidates and only a few jobs. So you would be competing for this job against several eligible candidates. It’s pretty much clear that to get the job you have to take the interview and prove yourself as a worthy candidate for the job.
To do that, you have to be fully prepared and deepen your knowledge on the topics that would be asked in your interview. So that you can answer the questions and impress your interviewers. For that, you would need to prepare the questions that are mostly asked in the Oracle DBA interview.
By getting to know the questions that they mostly asked in the interview, you can easily prepare for the interview and know how to answer the questions as well. Apart from this, when you know you are prepared for the interview, you would feel more confident and this would help you to pass the interview and get your dream job.
Now the bigger question is where would you find the most asked Oracle DBA interview questions? Well right here! In this article, we are going to list out several Oracle DBA interview questions that you need to know about. So go ahead and take a look at the following list of Oracle DBA Interview Questions.
Contents
Also check- ETL testing interview questions / Second interview questions
Oracle Dba Interview Questions
Q1.What is Oracle?
Ans-
Oracle is a company. Oracle is also a database server, which manages data in a very structured way. It allows users to store and retrieve related data in a multi user environment so that many users can concurrently access the same data. All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery. A standby database is a database replica created by taking a backup of a primary database.Q2.What is a Oracle Database?
Ans-
Oracle provides software to create and manage the Oracle database. The database consists of physical and logical structures in which system, user, and control information is stored. The software that manages the database is called the Oracle database server. Collectively, the software that runs oracle and the physical database are called the Oracle database system. A database buffer cache stores the data in memory for quicker access. The redo logs track and store all the changes made to the database. A Data Guard ensures data protection and high availability of data and control file records the physical structure of the database. Click on the above link to read more and to become a Oracle DBA.
Learn more about Oracle DBA in Oracle DBA Tutorial.Q3.What are the benefits of ORDBMS?
Ans-
The objects as such can be stored in the database. The language of the DBMS can be integrated with an object- oriented programming language. The language may even be exactly the same as that used in the application, which does not force the programmer to have two representations of his objects.Q4.What is an Oracle index?
Ans-
An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.Q5.What are the Common Oracle DBA Tasks?
Ans-
As an Oracle DBA, you can expect to be involved in the following tasks:
Installing Oracle software
Creating Oracle databases
Performing upgrades of the database and software to new release levels
Starting up and shutting down the database
Managing the database’s storage structures
Managing users and security
Managing schema objects, such as tables, indexes, and views
Making database backups and performing recovery when necessary
Proactively monitoring the database’s health and taking preventive or corrective action as required
Monitoring and tuning performanceIn a small to midsize database environment, you might be the sole person performing these tasks. In large enterprise environments, the job is often divided among several DBAs, each with their own area of specialty, such as the database security administrator or database tuning expert.Q6.What are the roles of DBA?
Ans-
DBA has the authority to create new users, remove existing users or modify any of the environment variables or privileges assigned to other users.
Manage database storage
Administer users and security
Manage schema objects
Monitor and manage database performance
Perform backup and recovery
Schedule and automate jobsQ7.How to check opatch applied or not in our Oracle home?
Ans-
Answer: ./opatch lsinventory 100 GB of database size while applying opatch it will take 30 mins, so 500 GB of database size how much time it will take to apply opatch. Same 30 Mins, We will apply the opatch for oracle binary only not for data.Q8.What is sqlnet.ora ?
Ans-
Answer: It is used to security of our database, Privileged IP address only able to access the database.Q9.what are the different components of physical and logical database structure?
Ans-
physical: Datafile,Controlfiles, redologfiles.
Logical: Database buffer cache, Redo log buffer cache,Shared pool,large poolQ10.What is Database Writer (DBWR)?
Ans-
It is responsible to writing data from database buffer cache into datafiles.Q11.What is a listener process?
Ans-
It is network connectivity, Application and sql developer tool to access database. Lietener.ora and Tnsnames.ora files are used.Q12.What is the difference between SPFILE and PFILE?
Ans-
Spfile: Server parameter file of binary file, It contains all the information for setting the database initialization parameters. SPFILE permits dynamic changes without requiring you to restart that instance.
Pfile: It is text parameter file, information for setting the database initialization parameters.Q13.What is the purpose of collecting statistics?
Ans-It will help optimizer to generate best execution planQ14.What is an Oracle index?
Ans-It is an database object used to increase the performance for retrieval data.Q15.If the table is fragmented, how would you rebuild it?
Ans-Alter table tablename move, We can analyze the table.
Real Time Oracle Dba Interview Questions And Answers
Q16.How to analyze AWR report and ADDM report.
Ans-
AWR: AWR report mainly we have to analyze query elapsed time and executions.
ADDM: It is a utility helps in providing some recommendations based on the statistics collected every hourQ17.If performance issue will occur, What are the actions need to take ?
Ans-
We need to take AWR report and check query elapsed time and how many executions, Query using proper index or not, or elase we have to analyze table and indexes.Check alertlog any issue occurredQ18.What are the different types of backups that are available in Oracle?
Ans-
RMAN
Datapump,Exp/IMP
Cold backupQ19.RMAN full backup was failed due to archivelogs error, What we will do ?
Ans-
We need to crosscheck the archivelogs.
crosscheck archivelog all;
delete expired archivelog all;Q20.How do you identify which sessions are locking the objects? What is the way to clear them?
Ans-
By combining V$locked_object and v$sessionQ21.Till yesterday a query is giving output in 1 min. today it is taking 5 min. how you will troubleshoot this problem?
Ans-
We will start looking at the network issues
Check if the query is changed from yesterday to today
Check table statistics are upto date
Generate explain plan and see where is the problemQ22.how to import table with different schema and different table and different tablespace ?
Ans-
remap_schema,remap_table,remap_tablespaceQ23.how to check production database and standby database sync ?
Ans-
select max(sequence#) from V$archived_log;
select max(sequence#) from V$log_history;Q24.Our standby database not in sync with production 10000 archivelogs gap will be there how to sync with production ?
Ans-
We will take SCN based incremental backup from production and applied into standby database and sync with production.Q25.How to open standby database read write mode and again how to rollback, What are options we have to use it.
Ans-
Using flashback and restore point option we can open and rollback the standby database.Q26.Database Components
Ans-
set line 200;
set pagesize 9999;
col COMP_ID format a15;
col COMP_NAME format a50;
select COMP_ID,COMP_NAME,STATUS from dba_registry;Q27.What makes you mean by Database Normalization?
Ans-
Normalization technique is a set of rules that exist used to build this relational database to prevent data redundancy also dependency. Once original database objects remain classified,
–
normalization benefits in identifying specific relationships between schema objects.
–
First Normal Form
Second Normal Form
Third Normal Form
Boyce-Codd Normal Form
Fourth Normal Form
Fifth Normal FormQ28.What are the different tools that are provided by Oracle to assist performance monitoring?
Ans-
AWR,ADDM,Trace,TKPROF,OEMQ29.What is an explain plan and how does it help in optimizing the SQL Query?
Ans-
We will check if query is using proper indexes are not. If not we will either create new indexes or can use hints to specify which indexes to use.Q30.How can we identified the resources for which the sessions are waiting?
Ans-
We can find out using v$session_waits and v$ system _waits.
–
Slove : Hope the above set would have provided you a good knowledge of any of the advanced ideas of Oracle. Just go through those questions before attending the interview also success will be yours.
Junior Oracle Dba Interview Questions
Q31.Defined by table partitioning?
Ans-
It is a process of sharing a table within smaller chunks so as to obtain the data retrieval simple and quick. Each piece order is known as a partition also can occur accessed separately.Q32.How do understanding for row Chaining?
Ans-
When a row is too long that it cannot and then it will end up accepting consequent blocks which can be lead to this concept of Row Chaining. It can signify avoided by refreshing this storage parameter over an appropriate value.Q33.Why do we need to rebuild indexes?
Ans-
Due to various insert & delete actions, that file gets fragmented & unstructured data, through creating that relationship slowly. To get data inside these indexes, rebuilding is completed.Q34.Different tools that are provided by Oracle to assist performance monitoring?
Ans-
TKPROF – (Transient kernel profiler)
STATSPACK – (Stands for Statistics Package)
ADDM – (Automated Database Diagnostics Monitor)
AWR – (Automatic Workload Repository)
OEM – (Oracle Enterprise Manager)Q35.Different areas where we can perform tuning?
Ans-
Database Design.
Memory Allocation.
Disk I/Os.
Database Contention.
OS level (CPU).Q36.How can we monitor these space allocations in a database?
Ans-
DBA_FREE_SPACE
DBA_SEGMENTS
DBA_DATA_FILESQ37.Defined by Recovery Catalog?
Ans-
Recovery Catalog is a means by database schema that holds by the metadata managed by RMAN for restoration data and recovery data processes.Q38.What is means by Database Writer?
Ans-
DBWR is a background process that writes changed data blocks of defense amounts over this data file. This changed information blocks move called dirty blocks.Q39.What are the specific disk components?
Ans-
Data files
Redo Logs
Control files
Password files and
Parameter filesQ40.What is mean by Oracle DBA PGA?
Ans-
PGA is a memory distance used by Oracle database. It is expected to save session specific information.Q41.When effects DBWR write to the data file?
Ans-
When checkpoint occurs
When number from infected blocks provides a threshold
Every three moments due to timeout
When the server method demands free space in buffer cache to read new blocks.Q42.What is defined by System Change Number?
Ans-
SCN is an ID that Oracle makes to every transaction. It is recorded including those corresponding variety into a redo approach.Q43.What is the significance of Oracle DBA?
Ans-
The Oracle Certified Professional (OCA/OCP) Database Administrator (DBA) accreditation is intended to reflect capability in Oracle database engineering, establishment, organization, execution tuning, and investigating.Q44.What is Oracle and what is it utilized for?
Ans-
An Oracle database is an accumulation of information treated as a unit. The motivation behind a database is to store and recover related data. … Oracle Database is the primary database intended for big business framework figuring, the most adaptable and practical approach to oversee data and applications.Q45.Clarify the devices which are utilized to fire up an Oracle database?
Ans-
You can start up a database with three devices.
–
SQL*Plus: This is the most generally utilized choice. You initially associate with an inactive case with SQL*Plus and after that startup the case with "startup" direction.
–
Prophet Enterprise Manager: This is another method for beginning up a database. You can sign in to Oracle Enterprise Manager regardless of whether the database is halted. OEM will recognize the status of the down database and will introduce you " Startup" catch. You can start up the database by clicking this catch.
–
RMAN: This is somewhat a less utilized instrument for beginning up a database yet it is conceivable to fire up a database from the Recovery Manager direction line.
Oracle Dba Interview Questions For Experienced
Q46.What is Tablespace?
Ans-
Prophet utilizes Tablespace for consistent information Storage. Physically, the information will get put away in Data documents. Information records will be associated with the tablespace. Table space can have numerous information documents. A tablespace can have objects from various outline's and construction can have numerous tablespace's. The database makes " Framework tablespace" as a matter of course amid database creation. It contains read-just information word reference tables which contain data about the database.Q47.What is the utilization of checkpoint in Oracle?
Ans-
A checkpoint plays out the accompanying three tasks: Every filthy square in the cushion reserve is kept in touch with the information records. That is, it synchronizes the information hinders in the support reserve with the datafiles on circle. It's the DBWR that composes all changed database hinders back to the datafiles.Q48.What is means by LoG-WRiter(LGWR)?
Ans-
LGWR is the background method that writes redo data from redo log buffers before the log files.Q49.What is Oracle database?
Ans-
Oracle database is used to store and retrieve information. It contains physical and logical structures. Physical structures are controlled file, datafile and redo log file. The logical structure is a database, tablespace, segment, extent, and blocks.Q50.What are datafiles?
Ans-
All the tables are stored in data files. The data file has all the data stored.Q51.How will you recover the database from a loss of a data file?
Ans-
The database can be recovered using the backup which is taken already. Using rman we can recover from the failure.Q52.How to check the current database name?
Ans-
Select name from v$database;Q53What is ora-1555 error?
Ans-
Snapshot too old error happens when it does not have enough space in the undo. So increasing the space will solve the problem.Q54.What are the two involved in network connection to a database?
Ans-
TNSNAMES.ORA and SQLNET.ORAQ55.How will you backup all redo log file from Linux?
Ans-
$cp *.log/u01/app/backup/db_name (This command will copy all redo log file.Q56.How will you backup all datafiles from Linux?
Ans-
$cp *.dbf/u01/app/backup/db_name (This command will copy all datafile.Q57.How will you find the location of pfile?
Ans-
> Show parameter pfile;Q58.What is the use of a control file?
Ans-
The control file contains the physical structure of the database, such as the number of log files and their location. Oracle database server uses a control file to find its physical component.
Conclusion:
So these are some of the most important Oracle DBA interview questions that you need to know about. You need to know that these questions have been picked up by some experts and professionals who have a good knowledge of this subject! You should also know that these won’t be the only questions asked in the Oracle DBA interview, but these are listed just to give you a brief idea about what you can expect in the interview.
That’s all we have to offer you for now and we hope you got everything you have been looking for. So thank you for being here and we wish you all the best for your upcoming Oracle DBA interview.
