Oracle DBA Interview Questions and Answers

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, installation, administration, performance tuning and troubleshooting.

Ans: An Oracle database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. ... Oracle Database is the first database designed for enterprise grid computing, the most flexible and cost effective way to manage information and applications.

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

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

1. SQL*Plus: This is the most widely used option. You first connect to an idle instance with SQL*Plus and then startup the instance with “startup” command.
2. Oracle Enterprise Manager: This is another way of starting up a database. You can logon to Oracle Enterprise Manager even if the database is stopped. OEM will detect the status of the down database and will present you “Startup” button. You can startup the database by clicking this button.
3. RMAN: This is rather a less used tool for starting up a database but it is possible to startup a database from Recovery Manager command line.

Q4. What is a Tablespace?

Ans: Oracle use Tablespace for logical data Storage. Physically, data will get stored in Data files. Data files will be connected to tablespace. A table space can have multiple data files. A tablespace can have objects from different schema’s and a schema can have multiple tablespace’s. Database creates “SYSTEM tablespace” by default during database creation. It contains read only data dictionary tables which contains the information about the database.

Q5. What is the use of checkpoint in Oracle?

Ans:  A checkpoint performs the following three operations: Every dirty block in the buffer cache is written to the data files. That is, it synchronizes the datablocks in the buffer cache with the datafiles on disk. It's the DBWR that writes all modified databaseblocks back to the datafiles.

Q6. What makes up an Oracle Instance?

Ans:

• An instance is made up of a shared memory region on RAM called System Global Area (SGA) and background processes.
• The system global area is a shared memory, which means it can be accessed by multiple processes. This are holds data which is required by the instance to operate.
• The background processes are operating system processes and each process has a specific responsibility in the instance.
• The System Global Area and background processes are created when the instance is “started”.
• When the instance is “shut down”, the processes are killed and the shared memory region is “released” back to operating system.

Q7. What is Oracle Home Inventory?

Ans: Oracle home inventory or local inventory is present inside each Oracle home. It only contains information relevant to a particular Oracle home. This file is located in the following location:
$ORACLE_HOME/inventory
It contains the following files and folders:

• Components File
• Home Properties File
• Other Folders

Q8. What are the objects in a database?

Ans: A database object is any defined object in a database that is used to store or reference data. Some examples of database objects include tables, views, clusters, sequences, indexes, and synonyms. The table is this hour's focus because it is the primary and simplest form of data storage in a relational database.

Q9.  List the common tasks of Oracle DBA ?

Ans: The tasks of Oracle DBA as follows:

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

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

Q11. What are the disk components in Oracle?

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

• Data files
• Redo Log files
• Control files
• Password files
• Parameter files

Q12. What is the difference between data block/extent/segment?

Ans: A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

Q13. What is the difference between PGA and UGA?

Ans: When you are running dedicated server then process information stored inside the process global area (PGA) and when you are using shared server then the process information stored inside user global area (UGA).

Q14. What are the different types of storage systems available and which one is used by Oracle?

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

• Most databases use RDBMS model, Oracle also 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 major installation steps of oracle software on UNIX in brief?

Ans:

1. Set up disk and make sure you have Installation file (run Installer) in your dump.
2. Check the swap and TEMP space .
3.  Export the following environment variables
   1. ORACLE_BASE
   2. ORACLE_HOME
   3. PATH
   4. LD_LIBRARY_PATH
   5. TNS_ADMIN
4. Set up the kernel parameters and file maximum descriptors.
5. Source the Environment file 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 sessions disconnect themselves.
Immediate: No new connections are allowed and the existing active transactions are rolled back. Changes made by an active transaction are lost in this option.
Transactional: No new connections are allowed and Oracle waits until all active transactions are completed.
Abort: This happens immediately however the database is not shutdown cleanly. Database will have to perform instance recovery next time it is started. This option should not be used in regular activities.

Q17. What happens when we fire SQL statement in Oracle?

Ans: First it will check the syntax and semantics in library cache, after that it will create execution plan.
If already data is in buffer cache it will directly return to the client.
If not it will fetch the data from datafiles and write to the database buffer cache after that it will send server and finally server send to the client.

Q18. Explain Two Easy Sql Optimizations?

Ans: EXISTS can be better than IN under various conditions.
UNION ALL is faster 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 sure to set NLS_LANG for 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 those may require extra attention. Also watch export/import for messages about any "character set conversions" which may 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 architecture pools large numbers of servers, storage, and networks into a flexible, on-demand computing resource for enterprise computing needs. The grid computing infrastructure continually analyzes demand for resources and adjusts supply accordingly.
For example, you could run different applications on a grid of several linked database servers. When reports are due at the end of the month, the database administrator could automatically provision more servers to that application to handle the increased demand.
Grid computing uses sophisticated workload management that makes it possible for applications to share resources across many servers. Data processing capacity can be added or removed on demand, and resources within a location can be dynamically provisioned. Web services can quickly integrate applications to create new business processes.

Q23. What would be the main responsibilities of an Oracle DBA in an organization?

Ans:

• The main duty of an Oracle DBA isto keep the Oracle Databases of the organization up and running. This may involve installing and configuring a database from scratch.
• On a running system, the DBA will be the only privileged person who can shut down and start up the database.
• The DBA will create new users and manage the privileges of each user.
• He will take regular backups to ensure that data is safe. In case of a disaster, he will be responsible of restoring the database from backups. He will have to do monitor the space usage and do capacity planning for the database.
• He will be responsible for enforcing security policies. He will have to monitor database activities.
• He will have to tune the database so that it works at an acceptable speed.
• He is expected to follow the latest patches and apply them when applicable.

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

Ans: SQL*Loader with direct path option can load data ABOVE the high water mark of a table, and DIRECTLY into the datafiles, without going through the SQL engine at all. This avoids all the locking, latching, and so on, and doesn't impact the db (except possibly the I/O subsystem) at all.

Q25. Explain some examples of join methods.

Ans: Join methods are of mainly 3 types.

Merge Join :

• Sorting both the tables using join key and then merge the rows which are sorted.

Nested loop join -:

• It gets a result set after applying filter conditions based on the outer table.
• Then it joins the inner table with the respective result set.

Hash join :

•  It uses hash algorithm first on smaller table and then on the other table to produce joined columns. After that matching rows are returned.

Q26. What are the components of logical data model and list some differences between logical and physical data model?

Ans: Components of logical data model are

Entity - Entity refers to an object that we use to store information. It has its own table.
Attribute - It represents the information of the entity that we are interested in. It is stored as a column of the table and has specific datatype associated with it.
Record - It refers to a collection of all the properties associated with an entity for one specific condition, represented as row in a table.
Domain - It is the set of all the possible values for a particular attribute.
Relation - Represents a relation between two entities.

Q27. What is normalization? What are the different forms of normalization?

Ans:

• Normalization is a process of organizing the fields and tables of a relational database to minimize redundancy and dependency.
• It saves storage space and ensures consistency of our data.

There are six different normal forms

1. First Normal Form - If all underlying domains contain atomic values only.
2. Second Normal Form - If it is in first normal form and every non key attribute is fully functionally dependent on primary key.
3. Third Normal Form - If it is in 2nd normal form and every non key attribute is non transitively dependent on the primary key.
4. Boyce Codd Normal Form - A relation R is in BCNF if and only every determinant is a candidate key.
5. Fourth Normal Form 
6. Fifth Normal Form

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

Ans:

• Init<SID>.ora parameter file or SPFILE is accessed first .( SID is instance name)
• Settings required for starting a database are stored as parameters in this file.
•  SPFILE is by default created during database creation whereas PFILE should be created from SPFILE.
• PFILE is client side text file whereas SPFILE is server side binary file.
•  SPFILE is a binary file (it can’t be opened) whereas PFILE is a text file we can edit it and set parameter values.
• Changes made in SPFILE are dynamically effected with running database whereas PFILE changes are effected after bouncing the database.
• We can backup SPFILE using RMAN.

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

Ans: RMAN is faster, can do incremental (changes only) backups, and does not place tablespaces into hotbackup mode.

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

Ans: With bind variables in SQL, Oracle can cache related queries a single time in the SQL cache (area). This avoids a hard parse each time, which saves on various locking and latching resources we use to check objects existence and so on. BONUS: For rarely run queries, especially BATCH queries, we explicitely DO NOT want to use bind variables, as they hide information from the Cost Based Opitmizer.

Q31. In Pl/sql, 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 between unix processes. This is slow, and uses up resources. If we loop on an SQL statement, we are implicitely flipping between these two engines. We can minimize this by loading our data into an array, and using PL/SQL bulk binding operation to do it all in one go.

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 mean faster inserts/updates. More indexes mean faster, more specific WHERE clauses possibly without index merges.

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

Ans: RAID 5 is striping with an extra disk for parity. If we lose a disk we can reconstruct from that parity disk.
RAID 10 is mirroring pairs of disks, and then striping across those sets.
RAID 5 was created when disks were expensive. Its purpose was to provide RAID on the cheap. If a disk fails, the IO subsystem will perform VERY slowly during the rebuild process. What's more your liklihood of failure increases dramatically during this period, with all the added weight of the rebuild.
Even when it is operating normally RAID 5 is slow for everything but reading. Given that and knowing databases (especially Oracle's redo logs) continue to experience write activity all the time, we should avoid RAID5 in all but the rare database that is MOSTLY read activity. Don't put redologs on RAID5.
RAID10 is just all around goodness. If you lose one disk in a set of 10 for example, you could lose any one of eight other disks and have no troubles. What's more rebuilding does not impact performance at all since you're simply making a mirror copy. Lastly RAID10 perform exceedingly well in all types of databases.

Q34. How does an Oracle DBA role differ from an Oracle Developer role in an organization? Are there any similarities between these too?

Ans: An Oracle developer is mainly responsible for developing backend applications. They do data modelling according to business rules. They design tables, create indexes and other type of constraints. They are expected to know SQL and PL/SQL. The develop procedures using this languages. However, the Oracle developers are not expected to administer the database software itself.
On the other side, an Oracle DBA’s main duty is to administer the database which involves tasks like doing maintenance to keep the databases up and running, taking backups, enforcing security policies etc. DBAs are not primarily assigned to develop code. DBAs are supposed to have a good knowledge of SQL and PL/SQL like a developer as these are also required for administering the database.
According to the structure of the organization, DBAs might also be assigned development tasks or at least assist the developers where necessary.

Q35. What is a password file and why is it needed?

Ans: Passwords for database users are stored in the data dictionary of the database. When a user wants to loginto the database, the username and password provided by the user is checked against the values stored in the database. If the username and password match, the user is granted access to database. The data dictionary is part of the database and it will be accessible as long as the database is open. The passwords for administrators are stored in the dictionary as well.
When the database is closed, the data dictionary will be inaccessible. There needs to be a mechanism for administrators to logon to database even when it is closed, because it is one of the administrator’s tasks to start up a down database. A password file is a separate operating system file that is stored on disk outside of the database. The username and password for the users who have SYSDBA or SYSOPER privileges are stored in it. Administrators who have those privileges are authenticated using this password file even when 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, cannot be rolled back, and is fast because Oracle simply resets the HWM. When a full table scan is performed on a table, such as for a sort operation, Oracle reads to the HWM. So if you delete every single solitary row in 10 million row table so it is now empty, sorting on that table of 0 rows would still be extremely slow

Q37. Differentiate between a cluster and a grid?

Ans: Clustering is one technology used to create a grid infrastructure. Simple clusters have static resources for specific applications by specific owners. Grids, which can consist of multiple clusters, are dynamic resource pools shareable among many different applications and users. A grid does not assume that all servers in the grid are running the same set of applications. Applications can be scheduled and migrated across servers in the grid. Grids share resources from and among independent system owners.
At the highest level, the idea of grid computing is computing as a utility. In other words, you should not care where your data resides, or what computer processes your request. You should be able to request information or computation and have it delivered – as much as you want, and whenever you want. This is analogous to the way electric utilities work, in that you don’t know where the generator is, or how the electric grid is wired, you just ask for electricity, and you get it. The goal is to make computing a utility, a commodity, and ubiquitous. Hence the name ‘The Grid’. This view of utility computing is, of course, a “client side” view.
From the “server side”, or behind the scenes, the grid is about resource allocation, information sharing, and high availability. Resource allocation ensures that all those that need or request resources are getting what they need, that resources are not standing idle while requests are going unserviced. Information sharing makes sure that the information users and applications need is available where and when it is needed. High availability features guarantee all the data and computation is always there, just like a utility company always provides electric power.

Q38. List out the Tools for Administering the Database?

Ans: The following are some of the products, tools, and utilities you can use in achieving your goals as a database administrator.

• Oracle Universal Installer (OUI):
  The Oracle Universal Installer installs your Oracle software and options. It can automatically launch the Database Configuration Assistant to install a database.
• Database Configuration Assistant (DBCA):
  The Database Configuration Assistant creates a database from templates that are Oracle supplied, or you can create your own. It enables you to copy a preconfigured seed database, thus saving the time and effort of customizing and generating a database from scratch.
• Database Upgrade Assistant:
  This tool guides you through the upgrade of your existing database to a new Oracle release.
• Oracle Net Manager:
  This tool guides you through your Oracle Net network configuration.
• Oracle Enterprise Manager:

The primary tool for managing your database is Oracle Enterprise Manager, a web-based interface. After you have installed the Oracle software, created or upgraded a database, and configured the network, you can use Oracle Enterprise Manager as the single interface for managing your database. In addition, Oracle Enterprise Manager also provides an interface for performance advisors and an interface for Oracle utilities such as SQL*Loader and Recovery Manager.

Related Interview Questions...

• Oracle HRMS Interview Questions and Answers
• Oracle Finance Interview Questions and Answers
• Oracle OBIEE Interview Questions and Answers
• Oracle PL SQL Interview Questions and Answers
• Oracle RAC Interview Questions and Answers
• Oracle Identity Manager Interview Questions and Answers
• Basic Oracle Interview Questions with Answers
• Oracle Application Interview Questions and Answers
• Oracle SCM Interview Questions