If you are an expert in Oracle DBA Troubleshooting then this is for you. Let us know if you looking for a job change? Then do not worry, we’ve a right answer for your job interview preparation. If you are preparing for Oracle DBA Troubleshooting job interview then go through Wisdomjobs interview questions and answers page. Oracle DBA Troubleshooting is the activity of performing to find the root cause of the issues occurred related to database. During the database initialization, installation, migration etc, we might come across the problems. Good knowledge on the Oracle DBA Troubleshooting concepts are must to crack the job. Below are the Oracle DBA Troubleshooting interview questions and answers which makes you comfortable to face the interviews:
Answer :
A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.
Answer :
I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.
Question 3. How Do You Switch From An Init.ora File To A Spfile?
Answer :
Issue the create spfile from pfile command.
Question 4. Explain The Difference Between A Data Block, An Extent And A Segment.?
Answer :
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.
Question 5. Give Two Examples Of How You Might Determine The Structure Of The Table Dept?
Answer :
Use the describe command or use the dbms_metadata.get_ddl package.
Question 6. Where Would You Look For Errors From The Database Engine?
Answer :
In the alert log.
Question 7. Compare And Contrast Truncate And Delete For A Table?
Answer :
Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.
Question 8. Give The Reasoning Behind Using An Index?
Answer :
Faster access to data blocks in a table.
Answer :
Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.
Question 10. What Type Of Index Should You Use On A Fact Table?
Answer :
Bitmap index.
Question 11. Give Two Examples Of Referential Integrity Constraints?
Answer :
primary key and a foreign key.
Answer :
Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.
Answer :
ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time.
NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.
Question 14. What Command Would You Use To Create A Backup Control File?
Answer :
Alter database backup control file to trace.
Answer :
Question 16. What Column Differentiates The V$ Views To The Gv$ Views And How?
Answer :
The INST_ID column which indicates the instance in a RAC environment the information came from.
Question 17. How Would You Go About Generating An Explain Plan?
Answer :
Question 18. How Would You Go About Increasing The Buffer Cache Hit Ratio?
Answer :
Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size command.
Question 19. Explain An Ora-01555?
Answer :
You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.
Question 20. Explain The Difference Between $oracle_home And $oracle_base?
Answer :
ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.
Question 21. How Would You Determine The Time Zone Under Which A Database Was Operating?
Answer :
select DBTIMEZONE from dual;
Question 22. Explain The Use Of Setting Global_names Equal To True?
Answer :
Setting GLOBAL_NAMES dictates how you might connect to a database. This variable is either TRUE or FALSE and if it is set to TRUE it enforces database links to have the same name as the remote database to which they are linking.
Question 23. What Command Would You Use To Encrypt A Pl/sql Application?
Answer :
WRAP
Question 24. Explain The Difference Between A Function, Procedure And Package?
Answer :
A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.
Question 25. Explain The Use Of Table Functions?
Answer :
Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.
Question 26. Name Three Advisory Statistics You Can Collect?
Answer :
Question 27. Where In The Oracle Directory Tree Structure Are Audit Traces Placed?
Answer :
In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer
Question 28. Explain Materialized Views And How They Are Used?
Answer :
Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.
Question 29. When A User Process Fails, What Background Process Cleans Up After It?
Answer :
PMON
Question 30. What Background Process Refreshes Materialized Views?
Answer :
The Job Queue Processes.
Answer :
Use of V$SESSION and V$SESSION_WAIT
Question 32. Describe What Redo Logs Are?
Answer :
Redo logs are logical and physical structures that are designed to hold all the changes made to a database and are intended to aid in the recovery of a database.
Question 33. How Would You Force A Log Switch?
Answer :
ALTER SYSTEM SWITCH LOGFILE;
Question 34. Give Two Methods You Could Use To Determine What Ddl Changes Have Been Made?
Answer :
You could use Logminer or Streams
Question 35. What Does Coalescing A Tablespace Do?
Answer :
Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining neighboring free extents into large single extents.
Question 36. What Is The Difference Between A Temporary Tablespace And A Permanent Tablespace?
Answer :
A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database.
Question 37. Name A Tablespace Automatically Created When You Create A Database?
Answer :
The SYSTEM tablespace.
Answer :
Grant the CONNECT to the user.
Question 39. How Do You Add A Data File To A Tablespace?
Answer :
ALTER TABLESPACE ADD DATAFILE SIZE
Question 40. How Do You Resize A Data File?
Answer :
ALTER DATABASE DATAFILE RESIZE ;
Question 41. What View Would You Use To Look At The Size Of A Data File?
Answer :
DBA_DATA_FILES
Question 42. What View Would You Use To Determine Free Space In A Tablespace?
Answer :
DBA_FREE_SPACE
Question 43. How Would You Determine Who Has Added A Row To A Table?
Answer :
Turn on fine grain auditing for the table.
Question 44. How Can You Rebuild An Index?
Answer :
ALTER INDEX REBUILD;
Question 45. Explain What Partitioning Is And What Its Benefit Is?
Answer :
Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.
Question 46. You Have Just Compile A Pl/sql Package But Got Errors, How Would You View The Errors?
Answer :
SHOW ERRORS
Question 47. How Can You Gather Statistics On A Table?
Answer :
The ANALYZE command.
Question 48. How Can You Enable A Trace For A Session?
Answer :
Use the DBMS_SESSION.SET_SQL_TRACE or Use ALTER SESSION SET SQL_TRACE = TRUE;
Question 49. What Is The Difference Between The Sql*loader And Import Utilities?
Answer :
These two Oracle utilities are used for loading data into the database. The difference is that the import utility relies on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility allows data to be loaded that has been produced by other utilities from different data sources just so long as it conforms to ASCII formatted or delimited files.
Question 50. Name Two Files Used For Network Connection To A Database?
Answer :
TNSNAMES.ORA and SQLNET.ORA
Question 51. What Are Dynamic Performance Views?
Answer :
Dynamic performance views are also called V$ views. These views provided information about the sessions.
Any Oracle user can get information from dynamic performance views if the user has the select any table privilege. This privilege is generally granted through the SELECT_CATALOG_ROLE etc.
Question 52. What Is The Statspack Tool?
Answer :
Question 53. What Is Db File Sequential Read Wait Event?
Answer :
The db file sequential read wait event performs single block read operations against indexes, tables, control files, rollback segments and data file headers.
It has three parameters:
file#, firstblock# and block count.
Question 54. Explain Wait Events?
Answer :
Question 55. What Is Cost Based Optimizer?
Answer :
Question 56. Which Parameters Affect The Behaviour Of Merge Join?
Answer :
The behavior of merge join is influenced by the initialization parameters:
Question 57. Why Union All Faster Than Union?
Answer :
The UNION operation removes redundancy while UNION ALL does not; therefore, the UNION operation needs to perform sort. As a result, UNION ALL performs better as it does not need to perform any sort.
Question 58. How Can You Get More Details About The Blocking Session?
Answer :
You can use the v$session or gv$session view in Real Application Clusters (RAC) environment to get the session information.
Question 59. What Is The Ora-01555: Snapshot Too Old Error? How Can It Be Avoided?
Answer :
The ORA-01555: snapshot too old error indicates that the query cannot find the snapshot it is looking for in the rollback segment.
Rollback segment is designed to hold data blocks that are being changed. It is required to hold old snapshot until the transaction is committed. However, it holds the data until the space is required for other transactions.
Question 60. How Do You Handle Ora – 01403: No Data Found Error?
Answer :
You can handle ORA- 01403: no data found error by terminating the processing for the SELECT statement.
Question 61. What Are Different Types Of Locks?
Answer :
There are two different types of locks, which are given as follows:
Question 62. What Is The Significance Of Latches With Respect To Performance Tuning?
Answer :
Whenever there is a contention for latch, it indicates that there is a performance issue, which may be due to either of the two following reasons:
Question 63. What Is The Use Of Alert Log File?
Answer :
The ALERT log is a log file that records database wide events. The information in the ALERT log file is generally used for trouble shooting.
Following events are recorded in the ALERT log file:
Answer :
Lock is a mechanism provided by Oracle to reserve a database object so that different sessions do not interfere in each other's work.
Locking helps in ensuring data consistency and maintaining database objects in usable state in a multi user environment. However, it can cause one session to block another.
Question 65. What Is A Latch? How It Is Used In Oracle?
Answer :
Question 66. What Are The Background Trace Files?
Answer :
Background trace files are associated with background processes and are generated when certain background process experiences an error.
The information in background trace files is generally used for trouble shooting.
Question 67. What Is The Difference Between Latches And Enqueues?
Answer :
Question 68. What Is A User Process Trace File?
Answer :
A user process trace file is a trace file that is produced by user session. However, this is an optional file, which is generated if the user wants to generate the file. This file is generated when the value of SQL_TRACE parameter is set to TRUE for a session.
Question 69. Which Tools Are Available To Monitor Performance?
Answer :
Question 70. What Is An Event?
Answer :
An event is an Oracle is an occurrence that substantially alters the way your database executes or performs.
There are two types of events in Oracle:
Question 71. What Is The Main Reason For Block Corruption?
Answer :
Block corruption or physical corruption occurs when a block on a physical disk becomes unreadable or inconsistent to the state that the data is unusable. Block corruption can be caused by many different sources; and therefore, it is difficult to find the exact reason of block corruption.
However, it is mostly due to human error with the use of software (patches) firmware, or hardware bugs.
You can avoid this by testing all the hardware and software patches thoroughly in the test environment. In addition, you can use mirrored disk to protect your data.
Question 72. What Do You Understand By Db File Scattered Read?
Answer :
Question 73. What Do The Db_file_sequential_read And Db_file_scattered_read Events Indicate?
Answer :
Question 74. Which Trace File Is Used For Performance Tuning And Why?
Answer :
User process trace file is used for performance tuning because it contains information about execution plan and resource consumption. This information can be used for performance tuning.
These files are located in the directory specified in the BACKGROUND_DUMP_DIRECTORY parameter.
Question 75. How Can You Monitor Performance Of The Database Proactively?
Answer :
Question 76. Describe The Oracle Wait Interface?
Answer :
Question 77. Name A Few Places You Will Look To Get More Details On A Performance Issue?
Answer :
Oracle records the information about different kind of errors and the processes in the files, such as ALERT log, user process trace files and background process trace files.
Question 78. What Are The Different Types Of Locking Modes?
Answer :
Lock modes vary from 0 to 6 in order of increasing exclusively, given as follows:
You can get the information on modes of TM and TX locks by using the LMODE and REQUEST columns. Both the locks use the same numbering for lock modes.
Question 79. How Do You Handle Ora-01403: No Data Found Error?
Answer :
You can handle ORA- 01403: no data found error by terminating the processing for the select statement.
Question 80. How Can You Detect Block Corruption?
Answer :
There are four methods for detecting block corruption, which are given as follows:
Oracle DBA Troubleshooting Related Tutorials |
|
---|---|
Oracle 10g Tutorial | Oracle 9i Tutorial |
Oracle 8i Tutorial | Oracle 11g Tutorial |
Oracle DBA Troubleshooting Related Practice Tests |
|
---|---|
Oracle 10g Practice Tests | Oracle 9i Practice Tests |
Oracle 8i Practice Tests | Oracle 11g Practice Tests |
Oracle apps Practice Tests | Oracle Apps ERP Practice Tests |
Oracle 7.3 Practice Tests |
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.