Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Tuesday, 2 April 2013

DBA Interview Questions with Answer Part 15

Can you differentiate Redo vs. Rollback vs. Undo?
I find there is always some confusion when talking about Redo, Rollback and Undo. They all sound like pretty much the same thing or at least pretty close.
Redo: Every Oracle database has a set of (two or more) redo log files. The redo log records all changes made to data, including both uncommitted and committed changes. In addition to the online redo logs Oracle also stores archive redo logs. All redo logs are used in recovery situations.
Rollback: More specifically rollback segments. Rollback segments store the data as it was before changes were made. This is in contrast to the redo log which is a record of the insert/update/deletes.
Undo: Rollback segments. They both are really one in the same. Undo data is stored in the undo tablespace. Undo is helpful in building a read consistent view of data.
Alert. log showing this error “ORA-1109 signalled during: alter database close”. What is the reason behind it?
The ORA-1109 error just indicates that the database is not open for business. You'll have to open it up before you can proceed.
It may be while you are shutting down the database, somebody trying to open the database respectively. It is a failure attempt to open the database while shutdown is progress.Wait for the time to successfully shutdown the database and open it again for use. Alternatively you have to restart your oracle services on windows environment.
Which factors are to be considered for creating index on Table? How to select column for index?
Creation of index on table depends on size of table, volume of data. If size of table is large and we need only few data for selecting or in report then we need to create index. There are some basic reason of selecting column for indexing like cardinality and frequent usage in where condition of select query. Business rule is also forcing to create index like primary key, because configuring primary key or unique key automatically create unique index. 
It is important to note that creation of so many indexes would affect the performance of DML on table because in single transaction should need to perform on various index segments and table simultaneously.
What is Secure External password Store (SEPS)?
Through the use of SEPS you can store password credentials for connecting to database by using a client side oracle wallet, this wallet stores signing credentials. This feature introduced since oracle 10g. Thus the application code, scheduled job, scripts no longer needed embedded username and passwords. This reduces risk because the passwords are no longer exposed and password management policies are more easily enforced without changing application code whenever username and password change.
Differentiate DB file sequential read wait/DB File Scattered Read?
Sequential read associated with index read where as scattered read has to do with full table scan. The sequential read, reads block into contiguous memory and DB scattered read gets from multiple block and scattered them into buffer cache. 
I install oracle 10g on windows 7 successfully. I found every thing working fine except the toad is giving “cannot load oci.dll” error. Is this compatibility issue?
Read the toad user guide. You will get important information related to compatibility issue. In fact toad works with both 32 bit and 64 bit oracle server where as toad only work with 32 bit client. If you need 64 bit client for other applications, you can install both 32 bit and 64 bit client on a single machine and just tell the toad to use the 32 bit client.
What are the differences between Physical/Logical standby databases? How would you decide which one is best suited for your environment?
Physical standby DB:
        As the name, it is physically (datafiles, schema, other physical identity) same copy of the primary database.
        It synchronized with the primary database with Apply Redo to the standby DB.
Logical Standby DB:
        As the name logical information is the same as the production database, it may be physical structure can be different.
        It synchronized with primary database though SQL Apply, Redo received from the primary database into SQL statements and then executing these SQL statements on the standby DB.
        We can open “physical stand by DB to “read only” and make it available to the applications users (Only select is allowed during this period). we can not apply redo logs received from primary database at this time.
        We do not see such issues with logical standby database. We can open the database in normal mode and make it available to the users. At the same time, we can apply archived logs received from primary database.
        For OLTP large transaction database it is better to choose logical standby database.
How to re-organize schema?
We can use dbms_redefinition package for online re-organization of schema objects. Otherwise using import/export and data pump utility you can recreate or re-organize your schema.
To configure RMAN Backup for 100GB database? How we would estimate backup size and backup time?
Check the actual size of your database. For rman backup size almost depends on your actual size of database.
Backup time depends on your hardware configuration of your server such as CPU, Memory, and Storage.
Later you can also minimize the backup time by configuring multiple channels with the backup scripts.
How can you control number of datafiles in oracle database?
The db_files parameter is a "soft limit " parameter that controls the maximum number of physical OS files that can map to an Oracle instance. The maxdatafiles parameter is a different - "hard limit" parameter. When issuing a "create database" command, the value specified for maxdatafiles is stored in Oracle control files and default value is 32. The maximum number of database files can be set with the init parameter db_files.
Regardless of the setting of this parameter, maximum per database: 65533 (May be less on some operating systems), Maximum number of datafiles per tablespace: OS dependent = usually 1022
You can also by Limited size of database blocks and by the DB_FILES initialization parameter for a particular instance. Bigfile tablespaces can contain only one file, but that file can have up to 4G blocks.
What is Latches and why they are used in oracle?
A latch is a serialization mechanism. It is used to gain access to shared data structure in order to latches the structure that will prevent others from modifying it while you are modifying it.
Why it is not necessary to take UNDO backup?
In fact it is not necessary to take UNDO tablespace backup either with COLD or HOT backup scripts but many of DBA include UNDO tablespace in their backup script.
You know when you do some transactions; redo entries will be generated and accepted! Just like that other tablespace whenever any change happens to UNDO tablespace or UNDO segments oracle will generate redo entries. So even you not backed up the UNDO tablespace, you have the redo entries through which you can recover or rollback the transactions.
What should be effect on DB performance if virtual memory used to store SGA parameter?
For optimal performance in most systems, the entire SGA should fit in real memory. If it does not, and if virtual memory is used to store parts of it, then overall database system performance can decrease dramatically. The reason for this is that portions of the SGA are paged (written to and read from disk) by the operating system.
What is the role of lock_sga parameter?
The LOCK_SGA parameter, when set to TRUE, locks the entire SGA into physical memory. This parameter cannot be used with automatic memory management or automatic shared memory management.
What is CSSCAN?
CSSCAN (Database Character Set Scanner) is a SCAN tool that allows us to see the impact of a database character set change or assist us to correct an incorrect database nls_characterset setup. This helps us to determine the best approach for converting the database characterset.
Differentiate between co-related sub-query and nested query?
Co-related sub query is one in which inner query is evaluated only once and from that result your outer query is evaluated where as Nested query is one in which Inner query is evaluated for multiple times for getting one row of that outer query.
Example: Query used with IN() clause is Co-related query.
Example: Query used with = operator is Nested query
One after noon suddenly you get a call from your application user and complaining the database is slow then what will be your first step to solve this issue?
High performance is common expectation for end user, in fact the database is never slow or fast in most of the case session connected to the database slow down when they receives unexpected hit. Thus to solve this issue you need to find those unexpected hit. To know exactly what the second session is doing join your query with v$session_wait. 
SELECT NVL(s.username, '(oracle)') AS username, s.sid,  s.serial#,  sw.event, sw.wait_time, sw.seconds_in_wait, sw.state
FROM   v$session_wait sw, v$session s
WHERE  s.sid = sw.sid and s.username = 'HRMS'
ORDER BY sw.seconds_in_wait DESC;
Check the events that are waiting for something, try to find out the objects locks for that particular session. Follow the link: Find Locks : Blockers
Locking is not only the cause to effects the performance. Disk I/O contention is another case. When a session retrieves data from the database datafiles on disk to the buffer cache, it has to wait until the disk sends the data. The wait event shows up for the session as “db file sequential read” (for index scan) or “db file scattered read” (for full table scan). Query link: DB File Sequential Read Wait/ DB File Scattered Read , DB Locks
When you see the event, you know that the session is waiting for I/O from the disk to complete. To improve session performance, you have to reduce that waiting period. The exact step depends on specific situation, but the first technique “reducing the number of blocks retrieved by a SQL statement” almost always works. 
        Reduce the number of blocks retrieved by the SQL statement. Examine the SQL statement to see if it is doing a full-table scan when it should be using an index, if it is using a wrong index, or if it can be rewritten to reduce the amount of data it retrieves.
        Place the tables used in the SQL statement on a faster part of the disk.
        Consider increasing the buffer cache to see if the expanded size will accommodate the additional blocks, therefore reducing the I/O and the wait.


  1. Dear Shahid,

    Really appreciate for sharing your knowledge.

    I would be more than happy if you can put more light on performance issues with step by step actions you would have taken while trouble shooting ..

    Sameer Khan

    1. Dear Sameer,

      Thanks a lot for your appreciation.
      Regarding performance issue i already uploaded too many documents.
      Try to use search concept on the top of the blog, hope you will find the required one.

  2. Hello Sir,

    Very nice question and Answer, it is very helpful.
    As i am new DBA for oracle and preparing to exam, i found lot of real life question.
    it is awesome. keep posting sir.

    thanking you
    warm regards
    Mohammad Shahnawaz

  3. Hi Sir,

    Thank you for all your precious post which are very helpful, awesome job you have done,

    Sir Can you please give note on steps to do database upgradation,cloning, and migration from one OS to Other.

    Thank you a lot,

    1. Dear Naveen,

      I already uploaded related article on my blog. You can go through the search option.
      Hope You will find the required documents.

      Thanks with Best Regards
      Shahid Ahmed

  4. Thxs to share the valuable information. Plz keep it up. Will u guide me from where i should start to learn. As i have some practical knowledge of Oracle DBA. But i feel a little bit confusion...Plz help me Sir because i am dedicated user of Oracle.

    1. Dear Atif,
      Thanking your appreciation first. In fact in an industry you will start you work from Backup and Recovery concept so it is better to start and concentrate fist on all the Backup & Recovery related concept specially RMAN then gradually move for the Tablespace/Datafiles Space Management, Monitoring different aspect of the database (focus on different views) thenafter Move for Cloning and standby concent as on ....
      Here one thing is important do all the concept one by one priority basis. Please don't merge the concept. I mean first complete one concept then move for the others.
      One more suggestion in whatever the situation do not remove your database. While practice if you need recovery of database try to recover and fix it and activate your database. Generally users are facing complexity and they immediately remove or even even format the drive. Please do not do this. Make a habbit like you are working on Production database in any circumstances if you are not able to fix it then take a help from expert.
      I hope you understand what i need to say!

  5. I have learned oracel DBA. I am now in confusion whether to go with appsdba or RACdba. Can you please suggest which one is best and why?
    Thank you!!


    1. Dear,

      In Comparison with Core/Apps/RAC DBA, the Core DBA having basic knowledge of ‎database structure and its maintenance where as Apps DBA includes Core DBA ‎responsibilities plus Upgrade, Cloning, Services/Component and patching and difference ‎with Apps DBA RAC DBA includes Core DBA responsibilities plus Clustering database, ‎File system thus working on broader technologies.‎
      There are enough opportunities in both the streams with good salary if you have right ‎skills and good experience. It is quite difficult to compare salary and opportunities but in ‎my views you have better (Immediate) prospects as Apps DBA. Later you can increase ‎your potential as a RAC DBA.‎
      Thanks & Best Wishes
      Shahid Ahmed

  6. I see this blog and it's very rich site for DBA.

  7. Alampana thussi great ho...!!! your examples are related to real life examples and easier to understand.

    1. Thanks for your appreciated word.

    2. thats the correct reaction from the beginners

  8. Very good guidance and questions Shahid. You seem to be Afridi of Oracle. I'm a dba in Accenture bangalore, pls tell me how to find a dba job in middle east( Thanking you.

    Fahad Alam

    1. Dear Fahad,
      Thanks for your appreciated word. Already your are working in same profile so it is easy ‎for you to get a chance in any where in world. Try to upload your resume in different gulf ‎company site. For more about site knowledge you can access my blog “Gulf job link”.‎
      Thanks with Best Wishes
      Shahid Ahmed

  9. Hi,

    I am a new dba. This is my learning process. Such nice thing to learn for me. It,s so simple way you explain every thing. Thanks. ..


  10. this one also seems good for oracle dba and apps dba interview perpetration
    have look ,

  11. Really good information! thanks

  12. Great Post !!!! I have readied at least eight posts on your website and let me tell you, your website provides the most fascinating information. The Author has shared DBA interview Questions with Answers. But I had found one better and effective blog related to Oracle DBA Interview Q/A’s. For More Info…: Oracle-dba-interview-questions

  13. very very nice shahid this is the first link which i find max interview question and answer
    thanks once's again

  14. Very nice sir, great collection of information thank you for sharing. We provide Oracle DBA Online Training

  15. Your blog is very good, and very useful questions thank you for sharing. Oracle DBA Course Online

  16. Hi sir,
    Thanks for sharing your good knowledge, your scripts are really helping!!!
    I have a question how to send logs in email on completion of datapump backups in windows.

  17. My Youtube SQL channel growing fast getting good number of subscriber..

    Reference is as below.

    channel is