Interview Questions.

Q1. What is the meaning of Oracle DBA?

Ans: The Oracle Certified Professional (OCA / OCP) Database Administrator (DBA) certification is designed to reflect proficiency in Oracle database architecture, set up, management, overall performance tuning and troubleshooting.

Q2. What is Oracle and what is it used for?

Ans: An Oracle database is a group of information treated as a unit. The reason of a database is to shop and retrieve associated statistics. ... Oracle Database is the primary database designed for company grid computing, the maximum bendy and fee effective way to control data and packages.

Q3. Explain the tools  which are used to begin up an Oracle database?

Ans: You can start up a database with three tools.

SQL*Plus: This is the most widely used choice. You first hook up with an idle instance with SQL*Plus after which startup the example with “startup” command.

Oracle Enterprise Manager: This is any other manner of commencing a database. You can logon to Oracle Enterprise Manager although the database is stopped. OEM will hit upon the repute of the down database and could gift you “Startup” button. You can startup the database through clicking this button.

RMAN: This is as a substitute a less used tool for starting up a database but it is feasible to startup a database from Recovery Manager command line.

Q4. What is a Tablespace?

Ans: Oracle use Tablespace for logical facts Storage. Physically, information gets stored in Data documents. Data documents can be linked to tablespace. A desk space can have a couple of statistics documents. A tablespace may have gadgets from distinct schema’s and a schema can have a couple of tablespace’s. Database creates “SYSTEM tablespace” by way of default for the duration of database advent. It carries read best statistics dictionary tables which contains the data about the database.

Q5. What is the usage of checkpoint in Oracle?

Ans:  A checkpoint performs the following three operations: Every grimy block within the buffer cache is written to the facts documents. That is, it synchronizes the datablocks inside the buffer cache with the datafiles on disk. It's the DBWR that writes all modified databaseblocks returned to the datafiles.

Q6. What makes up an Oracle Instance?

Ans:

An instance is made from a shared memory area on RAM known as System Global Area (SGA) and history processes.

The gadget international region is a shared memory, because of this it could be accessed with the aid of a couple of methods. This are holds information which is required with the aid of the example to perform.

The background methods are working device approaches and each manner has a selected responsibility in the example.

The System Global Area and history methods are created whilst the example is “began”.

When the instance is “close down”, the methods are killed and the shared reminiscence vicinity is “released” returned to running machine.

Q7. What is Oracle Home Inventory?

Ans: Oracle home inventory or nearby stock is present internal every Oracle domestic. It best incorporates information relevant to a particular Oracle home. This record is placed within the following area:

$ORACLE_HOME/stock

It incorporates the subsequent documents and folders:

Components File

Home Properties File

Other Folders

Q8. What are the gadgets in a database?

Ans: A database object is any defined item in a database that is used to store or reference data. Some examples of database gadgets encompass tables, perspectives, clusters, sequences, indexes, and synonyms. The desk is this hour's attention due to the fact it's far the primary and most effective form of records garage in a relational database.

HubSpot Video
 

Q9.  List the not unusual obligations of Oracle DBA ?

Ans: The obligations of Oracle DBA as follows:

Installing Oracle software program

Creating Oracle databases

Performing improvements of the database and software to new release degrees

Starting up and closing down the database

Managing the database’s garage systems

Managing customers and protection

 Managing schema objects, such as tables, indexes, and perspectives

 Making database backups and performing recovery while vital

 Proactively monitoring the database’s health and taking preventive or corrective movement as required

Monitoring and tuning performanceIn a small to midsize database environment, you is probably the sole character appearing these duties. In massive employer environments, the job is often divided among numerous DBAs, each with their own place of uniqueness, consisting of the database safety administrator or database tuning expert.

Q10. What are the benefits of ORDBMS?

Ans: The items as such may be saved within the database. The language of the DBMS may be included with an object- orientated programming language. The language may additionally even be precisely the same as that used in the utility, which does no longer force the programmer to have  representations of his gadgets.

Q11. What are the disk additives in Oracle?

Ans: These are the physical components which gets stored within the disk.

Data files

Redo Log documents

Control documents

Password documents

Parameter documents

Q12. What is the difference among information block/quantity/phase?

Ans: A data block is the smallest unit of logical storage for a database item. As gadgets grow they take chunks of extra garage which can be composed of contiguous data blocks. These groupings of contiguous statistics blocks are known as extents. All the extents that an item takes while grouped collectively are taken into consideration the phase of the database object.

Q13. What is the distinction among PGA and UGA?

Ans: When you're running committed server then process information saved in the process global vicinity (PGA) and whilst you are the usage of shared server then the system facts saved inside consumer global vicinity (UGA).

Q14. What are the distinctive kinds of garage structures to be had and which one is utilized by Oracle?

Ans: Two varieties of garage systems are available :Relational Database Management System (RDBMS) and Hierarchical Storage Management System (HSM)

Most databases use RDBMS model, Oracle additionally uses RDBMS model.

Hierarchical Storage Management System (HSM)

Information Management System (IMS) from IBM.

Integrated Database Management System (IDMS) from CA.

Q15. List out the essential set up steps of oracle software program on UNIX in short?

Ans:

Set up disk and make sure you've got Installation record (run Installer) for your sell off.

Check the swap and TEMP area .

 Export the subsequent surroundings variables

1. ORACLE_BASE

2. ORACLE_HOME

3. PATH

four. LD_LIBRARY_PATH

5. TNS_ADMIN

Set up the kernel parameters and file maximum descriptors.

Source the Environment document to the respective bash profile and now run Oracle Universal Installer.

Q16. What are the types of shutdown modes of an Oracle database?

Ans:

Normal: In this mode, no new connections are allowed and the database is closed after all the periods disconnect themselves.

Immediate: No new connections are allowed and the present energetic transactions are rolled lower back. Changes made with the aid of an active transaction are misplaced in this option.

Transactional: No new connections are allowed and Oracle waits until all active transactions are finished.

Abort: This takes place right now however the database is not shutdown cleanly. Database will need to perform example recovery next time it's miles began. This choice should no longer be utilized in normal sports.

Q17. What takes place when we hearth SQL statement in Oracle?

Ans: First it will test the syntax and semantics in library cache, after that it will create execution plan.

If already data is in buffer cache it'll at once go back to the customer.

If now not it will fetch the information from datafiles and write to the database buffer cache after that it's going to send server and eventually server send to the consumer.

Q18. Explain Two Easy Sql Optimizations?

Ans: EXISTS may be higher than IN under various situations.

UNION ALL is quicker than UNION (not sorting).

Q19. Name Three Sql Operations That Perform A Sort?

Ans:

CREATE INDEX.

DISTINCT.

GROUP BY.

ORDER BY.

INTERSECT.

MINUS.

UNION.

UNINDEXED TABLE JOIN.

Q20. When Using Oracle Export/import What Character Set Concerns Might Come Up? How Do You Handle Them?

Ans: Be certain to set NLS_LANG as an example to "AMERCIAN_AMERICA.WE8ISO8859P1". If your source database is US7ASCII, beware of 8-bit characters. Also be wary of multi-byte characters sets as the ones may require more attention. Also watch export/import for messages about any "character set conversions" which might also occur.

Q21. How Do You Use Automatic Pga Memory Management With Oracle 9i And Above?

Ans: Set the WORKAREA_SIZE_POLICY parameter to AUTO and set PGA_AGGREGATE_TARGET.

Q22. Explain Oracle Grid Architecture?

Ans: The Oracle grid structure swimming pools massive numbers of servers, garage, and networks into a flexible, on-call for computing useful resource for business enterprise computing wishes. The grid computing infrastructure usually analyzes call for for resources and adjusts supply hence.

For example, you could run exclusive programs on a grid of several connected database servers. When reviews are due at the give up of the month, the database administrator may want to routinely provision greater servers to that software to deal with the multiplied call for.

Grid computing uses state-of-the-art workload management that makes it viable for packages to proportion sources across many servers. Data processing potential may be introduced or eliminated on demand, and resources inside a vicinity can be dynamically provisioned. Web services can speedy combine packages to create new commercial enterprise techniques.

Q23. What could be the primary duties of an Oracle DBA in an company?

Ans:

The essential responsibility of an Oracle DBA isto keep the Oracle Databases of the enterprise up and going for walks. This might also involve putting in and configuring a database from scratch.

On a jogging system, the DBA might be the handiest privileged person who can close down and begin up the database.

The DBA will create new users and manage the privileges of each person.

He will take normal backups to make sure that information is safe. In case of a disaster, he could be responsible of restoring the database from backups. He will ought to do display the distance usage and do capacity planning for the database.

He might be chargeable for implementing protection rules. He will must monitor database activities.

He will need to track the database so that it works at an appropriate pace.

He is anticipated to follow the brand new patches and apply them when relevant.

Q24. Why Is Sql*loader Direct Path So Fast?

Ans: SQL*Loader with direct path option can load facts ABOVE the excessive water mark of a table, and DIRECTLY into the datafiles, with out going via the SQL engine at all. This avoids all the locking, latching, and so on, and would not impact the db (except probably the I/O subsystem) at all.

Q25. Explain some examples of be a part of techniques.

Ans: Join strategies are of especially 3 sorts.

 

Merge Join :

Sorting both the tables the usage of be a part of key after which merge the rows which are taken care of.

Nested loop be part of -:

It gets a result set after applying filter out situations primarily based on the outer desk.

Then it joins the inner desk with the respective result set.

Hash be a part of :

 It makes use of hash set of rules first on smaller table after which on the opposite desk to provide joined columns. After that matching rows are back.

Q26. What are the additives of logical records model and listing a few variations between logical and physical data version?

Ans: Components of logical statistics version are

Entity - Entity refers to an item that we use to store information. It has its own table.

Attribute - It represents the information of the entity that we are inquisitive about. It is stored as a column of the desk and has specific datatype related to it.

Record - It refers to a collection of all the homes associated with an entity for one precise situation, represented as row in a table.

Domain - It is the set of all of the feasible values for a particular attribute.

Relation - Represents a relation among two entities.

Q27. What is normalization? What are the exceptional kinds of normalization?

Ans:

Normalization is a technique of organizing the fields and tables of a relational database to decrease redundancy and dependency.

It saves storage area and guarantees consistency of our information.

There are six special normal paperwork

First Normal Form - If all underlying domain names incorporate atomic values most effective.

Second Normal Form - If it's miles in first normal form and each non key characteristic is fully functionally depending on number one key.

Third Normal Form - If it's far in 2nd ordinary shape and each non key attribute is non transitively depending on the primary key.

Boyce Codd Normal Form - A relation R is in BCNF if and only each determinant is a candidate key.

Fourth Normal Form 

Fifth Normal Form

Q28. Which file is accessed first when Oracle database is began and What is the distinction between SPFILE and PFILE?

Ans:

Init<SID>.Ora parameter file or SPFILE is accessed first .( SID is instance call)

Settings required for starting a database are stored as parameters in this record.

 SPFILE is by default created at some point of database introduction while PFILE need to be created from SPFILE.

PFILE is purchaser facet textual content file whereas SPFILE is server side binary record.

 SPFILE is a binary report (it can’t be opened) while PFILE is a text report we will edit it and set parameter values.

Changes made in SPFILE are dynamically effected with walking database whereas PFILE adjustments are effected after bouncing the database.

We can backup SPFILE the usage of RMAN.

Q29. What Is The Difference Between Rman And A Traditional Hotbackup?

Ans: RMAN is faster, can do incremental (modifications handiest) backups, and does now not vicinity tablespaces into hotbackup mode.

Q30. What Are Bind Variables And Why Are They Important?

Ans: With bind variables in SQL, Oracle can cache associated queries a unmarried time inside the SQL cache (place). This avoids a tough parse on every occasion, which saves on diverse locking and latching sources we use to test gadgets life and so forth. BONUS: For rarely run queries, specially BATCH queries, we explicitely DO NOT want to use bind variables, as they conceal statistics from the Cost Based Opitmizer.

Q31. In Pl/sq., What Is Bulk Binding, And When/how Would It Help Performance?

Ans: Oracle's SQL and PL/SQL engines are separate parts of the kernel which require context switching, like among unix methods. This is gradual, and makes use of up sources. If we loop on an SQL announcement, we are implicitely flipping among these  engines. We can reduce this through loading our facts into an array, and the usage of PL/SQL bulk binding operation to do it multi functional cross.

Q32. What Are The Tradeoffs Between Many Vs Few Indexes? When Would You Want To Have Many, And When Would It Be Better To Have Fewer?

Ans: Fewer indexes on a table imply faster inserts/updates. More indexes imply faster, extra particular WHERE clauses in all likelihood with out index merges.

Q33. What Is The Difference Between Raid five And Raid 10? Which Is Better For Oracle?

Ans: RAID five is striping with a further disk for parity. If we lose a disk we will reconstruct from that parity disk.

RAID 10 is mirroring pairs of disks, and then striping across those units.

RAID five changed into created when disks had been highly-priced. Its reason turned into to offer RAID at the reasonably-priced. If a disk fails, the IO subsystem will perform VERY slowly at some stage in the rebuild method. What's extra your liklihood of failure will increase dramatically all through this era, with all the added weight of the rebuild.

Even whilst it is working normally RAID five is slow for the whole lot but analyzing. Given that and understanding databases (in particular Oracle's redo logs) continue to enjoy write pastime all of the time, we should avoid RAID5 in all but the rare database that is MOSTLY examine interest. Don't positioned redologs on RAID5.

RAID10 is simply all around goodness. If you lose one disk in a hard and fast of 10 as an example, you could lose any individual of 8 different disks and haven't any troubles. What's greater rebuilding does not impact performance at all since you're definitely creating a replicate copy. Lastly RAID10 carry out pretty well in all styles of databases.

Q34. How does an Oracle DBA role vary from an Oracle Developer function in an organization? Are there any similarities among these too?

Ans: An Oracle developer is especially responsible for developing backend applications. They do data modelling in line with commercial enterprise regulations. They layout tables, create indexes and different kind of constraints. They are expected to realize SQL and PL/SQL. The increase techniques the usage of this languages. However, the Oracle builders aren't expected to administer the database software itself.

On the other aspect, an Oracle DBA’s principal responsibility is to administer the database which includes responsibilities like doing renovation to keep the databases up and walking, taking backups, enforcing protection guidelines etc. DBAs are not broadly speaking assigned to develop code. DBAs are alleged to have an excellent information of SQL and PL/SQL like a developer as those also are required for administering the database.

According to the shape of the company, DBAs can also be assigned improvement duties or at least assist the developers wherein vital.

Q35. What is a password document and why is it wanted?

Ans: Passwords for database users are saved inside the facts dictionary of the database. When a person wants to loginto the database, the username and password provided by the user is checked in opposition to the values saved in the database. If the username and password suit, the user is granted get right of entry to to database. The records dictionary is part of the database and it is going to be available as long as the database is open. The passwords for directors are saved in the dictionary as well.

When the database is closed, the statistics dictionary may be inaccessible. There wishes to be a mechanism for directors to logon to database even if it's far closed, due to the fact it is one of the administrator’s responsibilities to start up a down database. A password document is a separate working machine report that is saved on disk out of doors of the database. The username and password for the users who've SYSDBA or SYSOPER privileges are saved in it. Administrators who've the ones privileges are authenticated the use of this password file even if the database is down.

Q36. What Is The Difference Between Truncate And Delete? Why Is One Faster? Can We Rollback Both? How Would A Full Table Scan Behave After?

Ans: Truncate is nearly instantaenous, can not be rolled again, and is fast because Oracle actually resets the HWM. When a complete desk scan is accomplished on a desk, which includes for a type operation, Oracle reads to the HWM. So if you delete each single solitary row in 10 million row desk so it is now empty, sorting on that desk of 0 rows could still be extraordinarily sluggish

Q37. Differentiate among a cluster and a grid?

Ans: Clustering is one era used to create a grid infrastructure. Simple clusters have static resources for precise packages by means of precise proprietors. Grids, which could include more than one clusters, are dynamic aid swimming pools shareable amongst many one-of-a-kind programs and users. A grid does not count on that every one servers within the grid are walking the identical set of applications. Applications may be scheduled and migrated across servers inside the grid. Grids proportion sources from and among independent device owners.

At the best stage, the concept of grid computing is computing as a utility. In different words, you should now not care in which your statistics resides, or what laptop approaches your request. You have to be able to request information or computation and have it brought – as tons as you want, and every time you want. This is analogous to the manner electric powered utilities work, in which you don’t recognize wherein the generator is, or how the electric grid is stressed out, you just ask for strength, and also you get it. The aim is to make computing a software, a commodity, and ubiquitous. Hence the name ‘The Grid’. This view of utility computing is, of path, a “patron facet” view.

From the “server aspect”, or behind the curtain, the grid is about useful resource allocation, information sharing, and high availability. Resource allocation guarantees that each one those who need or request assets have become what they need, that sources aren't standing idle even as requests are going unserviced. Information sharing makes certain that the information users and packages want is available where and when it's far wanted. High availability capabilities assure all of the information and computation is always there, much like a software corporation always gives electric energy.

Q38. List out the Tools for Administering the Database?

Ans: The following are a number of the products, gear, and utilities you may use in accomplishing your goals as a database administrator.

Oracle Universal Installer (OUI):

The Oracle Universal Installer installs your Oracle software program and options. It can robotically release the Database Configuration Assistant to install a database.

Database Configuration Assistant (DBCA):

The Database Configuration Assistant creates a database from templates which might be Oracle supplied, or you could create your own. It allows you to replicate a preconfigured seed database, hence saving the time and effort of customizing and producing a database from scratch.

Database Upgrade Assistant:

This device guides you thru the improve of your current database to a new Oracle release.

Oracle Net Manager:

This tool publications you thru your Oracle Net community configuration.

Oracle Enterprise Manager:

The primary tool for dealing with your database is Oracle Enterprise Manager, a web-based totally interface. After you have mounted the Oracle software program, created or upgraded a database, and configured the network, you may use Oracle Enterprise Manager as the unmarried interface for dealing with your database. In addition, Oracle Enterprise Manager also affords an interface for performance advisors and an interface for Oracle utilities along with SQL*Loader and Recovery Manager.