For BE/B.Tech/BCA/MCA/ME/M.Tech Major/Minor Project for CS/IT branch at minimum price Text Message @ 9424820157

Oracle DBA Interview Questions

 Oracle DBA Interview Questions



1) How to check opatch applied or not in our Oracle home?

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


2) What is sqlnet.ora?

It is used to security of our database, Privileged IP address only able to access the database.


3) what are the different components of physical and logical database structure?

physical: Datafile, Controlfiles, redologfiles.

Logical: Database buffer cache, Redo log buffer cache, Shared pool, large pool


4) What is Database Writer (DBWR)?

It is responsible for writing data from database buffer cache into datafiles.


5) What is a listener process?

It is network connectivity, Application and sql developer tool to access database. Lietener.ora and Tnsnames.ora files are used.


6) What is the difference between SPFILE and PFILE?

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.


7) What is the purpose of collecting statistics?

It will help optimizer to generate best execution plan


8) What is an Oracle index?

It is an database object used to increase the performance for retrieval data.


9) If the table is fragmented, how would you rebuild it?

Alter table tablename move, We can analyze the table.


10) What are the different tools that are provided by Oracle to assist performance monitoring?

AWR,ADDM,Trace,TKPROF,OEM


11) What is an explain plan and how does it help in optimizing the SQL Query?

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.


12) How to analyze AWR report and ADDM report.

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 hour


13) If a performance issue will occur, What are the actions need to take?

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 occurred


14) What are the different types of backups that are available in Oracle?

RMAN

Datapump,Exp/IMP

Cold backup


15) RMAN full backup was failed due to archivelogs error, What we will do ?

We need to crosscheck the archivelogs.

crosscheck archivelog all;

delete expired archivelog all;


16) What makes you mean by Database Normalization?

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 Form


17) How can we identified the resources for which the sessions are waiting?

We can find out using v$session_waits and v$ system _waits.


18) Defined by table partitioning?

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.


19) How do understanding for row Chaining?

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.


20) Why do we need to rebuild indexes?

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.


21) Different tools that are provided by Oracle to assist performance monitoring?

TKPROF – (Transient kernel profiler)

STATSPACK – (Stands for Statistics Package)

ADDM – (Automated Database Diagnostics Monitor)

AWR – (Automatic Workload Repository)

OEM – (Oracle Enterprise Manager)


22) Different areas where we can perform tuning?

Database Design.

Memory Allocation.

Disk I/Os.

Database Contention.

OS level (CPU).


23) How can we monitor these space allocations in a database?

DBA_FREE_SPACE

DBA_SEGMENTS

DBA_DATA_FILES


24) Defined by Recovery Catalog?

Recovery Catalog is a means by database schema that holds by the metadata managed by RMAN for restoration data and recovery data processes.


25) What is means by Database Writer?

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.


26) What are the specific disk components?

Data files

Redo Logs

Control files

Password files and

Parameter files


27) What is mean by Oracle DBA PGA?

Answer: PGA is a memory distance used by Oracle database. It is expected to save session-specific information.


28) When effects DBWR write to the data file?

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.


29) What is defined by System Change Number?

SCN is an ID that Oracle makes to every transaction. It is recorded including those corresponding varieties into a redo approach.


30)  What is the significance of Oracle DBA?

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.


31) What is Oracle and what is it utilized for?

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.


32) Clarify the devices which are utilized to fire up an Oracle database?

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.


33)  What is Tablespace?

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 outlines and construction can have numerous tablespaces. 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.


34) What is the utilization of checkpoint in Oracle?

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 the DBWR that composes all changed database hinders back to the datafiles.


35) What makes up an Oracle Instance?

1.An occurrence is comprised of a mutual memory locale on RAM called System Global Area (SGA) and foundation forms.

2.The framework worldwide region is a common memory, which implies it very well may be gotten to by various procedures. This holds information that is required by the case to work.

3.The foundation forms are working framework forms and each procedure has a particular obligation in the case.

4.The System Global Area and foundation forms are made when the case begins.

5.When the occasion is shut down the procedures are murdered and the common memory.


36) What is the Oracle Home Inventory?

Prophet home stock or neighborhood stock is available inside every Oracle home. It just contains data significant to a specific Oracle home.

This document is situated in the accompanying area: $ORACLE_HOME/stock

It contains the accompanying records and organizers:

Components File

Home Properties File

Other Folders


37) What are the articles in a database?

A database object is any characterized item in a database that is utilized to store or reference information. A few instances of database objects incorporate tables, sees, bunches, arrangements, files, and equivalent words. The table is this present hour's center since it is the essential and most straightforward type of information stockpiling in a social database.


38) What is mean by LoG-WRiter(LGWR)?

LGWR is the background method that writes redo data from redo log buffers before the log files.


39) What is Oracle database?

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.


40) What are datafiles?

All the tables are stored in data files. The data file has all the data stored.


41) How will you recover the database from a loss of a data file?

The database can be recovered using the backup which is taken already. Using rman we can recover from the failure.


42) How to check the current database name?

Select name from v$database;


43) What is ora-1555 error?

Snapshot too old error happens when it does not have enough space in the undo. So increasing the space will solve the problem.


44) What are the two involved in network connection to a database?

TNSNAMES.ORA and SQLNET.ORA


45) How will you backup all redo log file from Linux?

$cp *.log/u01/app/backup/db_name


46) How will you backup all datafiles from Linux?

$cp *.dbf/u01/app/backup/db_name


47) How will you find the location of pfile?

Show parameter pfile;


48) What is the use of a control file?

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.


No comments:

Post a Comment



Please go through below tutorials:


Mule 4 Tutorials

DEPLOY TO CLOUDHUB C4E CLIENT ID ENFORCEMENT CUSTOM POLICY RABBIT MQ INTEGRATION
XML TO JSON WEBSERVICE CONSUMER VM CONNECTOR VALIDATION UNTIL SUCCESSFUL
SUB FLOW SET & REMOVE VARIABLE TRANSACTION ID SCATTER GATHER ROUND ROBIN
CONSUME REST WEBSERVICE CRUD OPERATIONS PARSE TEMPLATE OBJECT TO JSON LOAD STATIC RESOURCE
JSON TO XML INVOKE IDEMPOTENT FILTER FOR EACH FLAT TO JSON
FIXWIDTH TO JSON FIRST SUCCESSFUL FILE OPERATIONS EXECUTE ERROR HANDLING
EMAIL FUNCTIONALITY DYNAMIC EVALUATE CUSTOM BUSINESS EVENT CSV TO JSON COPYBOOK TO JSON
CHOICE ASYNC

Widely used Connectors in Mule 3

CMIS JETTY VM CONNECTOR SALESFORCE POP3
JMS TCP/IP WEBSERVICE CONSUMER QUARTZ MONGO DB
FILE CONNECTOR DATABASE CONNECTOR


Widely used Scopes in Mule 3

SUB FLOW REQUEST REPLY PROCESSOR CHAIN FOR EACH CACHE
ASYNC TCP/IP COMPOSITE SOURCE POLL UNTIL SUCCESSFUL
TRANSACTIONAL FLOW

Widely used Components in Mule 3

EXPRESSION CXF SCRIPT RUBY PYTHON
JAVASCRIPT JAVA INVOKE CUSTOM BUSINESS EVENT GROOVY
ECHO LOGGER


Widely used Transformers in Mule 3

MONGO DB XSLT TRANSFORMER REFERENCE SCRIPT RUBY
PYTHON MESSAGE PROPERTIES JAVA TRANSFORMER GZIP COMPRESS/UNCOMPRESS GROOVY
EXPRESSION DOM TO XML STRING VALIDATION COMBINE COLLECTIONS BYTE ARRAY TO STRING
ATTACHMENT TRANSFORMER FILE TO STRING XML TO DOM APPEND STRING JAVASCRIPT
JSON TO JAVA COPYBOOK TO JSON MAP TO JSON JSON TO XML FLATFILE TO JSON
FIXWIDTH TO JSON CSV TO JSON


Widely used Filters in Mule 3

WILDCARD SCHEMA VALIDATION REGEX PAYLOAD OR
NOT MESSAGE PROPERTY MESSAGE IDEMPOTENT FILTER REFERNCE
EXPRESSION EXCEPTION CUSTOM AND


Exception Strategy in Mule 3

REFERENCE EXCEPTION STRATEGY CUSTOM EXCEPTION STRATEGY CHOICE EXCEPTION STRATEGY CATCH EXCEPTION STRATEGY GLOBAL EXCEPTION STRATEGY


Flow Control in Mule 3

CHOICE COLLECTION AGGREGATOR COLLECTION SPLITTER CUSTOM AGGREGATOR FIRST SUCCESSFUL
MESSAGE CHUNK AGGREGATOR MESSAGE CHUNK SPLITTER RESEQUENCER ROUND ROBIN SOAP ROUTER