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

Thursday, 6 December 2012

DBA interview Question with Answer Part 13

Why it is not necessary to take UNDO backup
In fact when you do some transaction, redo entries will be generated and accepted just like that whenever some change happen to UNDO tablespace or UNDO segments oracle will generate redo entries.
So even though you does not backup UNDO, you have the redo entries through which you can recover or rollback the transactions.
What happens with the datafile during hot backup process?
The below three action will happen in case of hot backup process in database
  1. The Tablespace checkpointed.
  2. The checkpoint SCN in datafile header will freeze to increment with checkpoint.
  3. Full image of changed DB block are written to redologs.
Why more redologs are generated during hotbackup?
During the hotbackup in initial checkpointing, the datafile that comprise the tablespace generates full image of changed Db block in these tablespace to the redologs. Normally oracle logs an entry in the redologs for every change in database but it does not log the whole image of database blog. By logging full images of changed DB blocks to the redologs during hot backup mode, oracle eliminates the possibility of the backup containing fractured blocks and guarantees that in the event of a recovery, any fractured that might be in the backup copy of the datafile will be resolved by replacing them with the full image of the block from the redologs.
How do you increase the performance of % like operator?
The % placed after the search word (‘ss%’) can enable the use of index if one is specified in the index column. This performance is better than the other two ways using % such as before the search word (like ‘%ss’) and before and after the search word (‘%ss%’).
What is cache Fusion Technology?
Cache fusion treats multiple buffer caches as one joint global cache. This solves the issues like data consistency internally, without any impact on the application code or design. Cache fusion technology eases the process of a very high number of concurrent users and SQL operations without compromising data consistency.
Do you have idea about reports server?
Reports server is also a component of the middle tier and is hosted in the same node of the concurrent processing server. Reports server is used to produce business intelligence reports.
What is importance of replication and their use in oracle? 
Replication is the process of copying and maintaining database objects in multiple databases that make up a distributed database system. Changes applied at one site are captured and stored locally before being forwarded and applied each of the remote location. Replication provides user with fast, local access to shared data, and protects availability of applications because alternate data access options exist. Even if one site becomes unavailable, users can continue to query or even update the remaining locations.
In simple replication, you create a snapshot, a table corresponding to the query's column list. When the snapshot is refreshed, that underlying table is populated with the results of the query. As data changes in a table in the master database, the snapshot is refreshed as scheduled and moved to the replicated database.
Advanced replication allows the simultaneous transfer of data between two or more Master Sites. There are considerations to keep in mind when using multi-master replication. The important ones are sequences (which cannot be replicated), triggers (which can turn recursive if you're not careful) and conflict resolution.
What is the basic difference between Cloning and Standby databases?
The clone database is a copy of the database which can be opened in read write mode. It is treated as a separate copy of the database that is functionally completely separate. The standby database is a copy of the production database used for disaster protection. In order to update the standby database; archived redo logs from the production database can be used. If the primary database is destroyed or its data becomes corrupted, one can perform a failover to the standby database, in which case the standby database becomes the new primary database.
Why we are using materialized view instead of a table?
Materialized views are basically used to increase query performance since it contains results of a query. They should be used for reporting instead of a table for a faster execution.
Which BG process refreshes the materialized view?
Job Queue Process
What is the importance of transportable Tablespace in oracle?
The transportable tablespace enable us to transport data objects across different platform. Moving data using transportable can be much faster than performing either export/import or unload or load of the same because transporting a tablespace only requires the copying of datafiles & integrating the tablespace structure information.
Can we reduce the size of TEMP datafile?
Yes, we can reduce the space of the TEMP datafile. Prior to oracle 11g, you had to recreate the datafile but in oracle 11g you reduce space of TEMP datfile by shrinking the TEMP tablespace. It is a new feature to 11g. The dynamic performance view DBA_temp_files can be very useful in determining which table space to shrink.
New data dictionary to check free space
Select * from dba_temp_free_space;
How can we move table from one schema to another?
The simplest way is Login with the SCOTT schema and use the below command to move EMP table from HR Schema. You can also use Copy and Import/Export for that.
How we can prevent fragmentation in oracle Tablespace.
Tablespace fragmentation can be prevented by using PCTINCREASE command. PCTINCREASE is the percentage a new subsequent extent will grow. This value should be ideally set to 0 or 100 to avoid tablespace fragmentation. Alternate and strange values for PCTINCREASE results in strange sizes of extents. Same size of each extent of all segments must be used.
Do you know the use of  iostat, vmstat and netstat?
Iostat report on terminal, disk and terminal IO activities.
Vmstat reports on virtual memory statistics for processes, disk, tape and CPU activity.
Netstat reports on the contents of network data structures.
Name the different types of indexes available in Oracle?
Oracle provides several Indexing schemas
B-tree index – Retrieves a small amount of information from a large table.
Global and Local index – Relates to partitioned tables and indexes.
Reverse Key Index - It Is most useful for oracle real application clusters applications.
Domain Index – Refers to an application
Hash cluster Index – Refers to the index that is defined specifically for a hash cluster.
What is a user process trace file?
§         It is an optional file which is produced by user session.
It is generated only if the value of SQL_TRACE parameter is set to true for a session.
§         SQL_TRACE parameter can be set at database, instance, or session level.
§         If it set at instance level, trace file will be created for all connected sessions.
§         If it is set at session level, trace file will be generated only for specified session.
§         The location of user process trace file is specified in the USER_DUMP_DEST parameter.
How can you use automatic PGA memory management with oracle 9i or above?
When a user comes to you and asks that a particular SQL query is taking more time. How will you solve this?
If you find the particular query is taking time to execute, then take a SQLTRACE with explain plan, it will show how the SQL query will be executed by oracle, depending upon the report you will tune your database. 
Then determine the table size and check the user requirement is % of data from query table. If it is less then
For example: one table has 10000 records, but you want to fetch only 5 rows, but in that query oracle does the full table scan. Only for 5 rows full table scan is not a good, so create an index on that particular column.
If the user requirement is more than 80% of data from query table then in that case if we create index, again user will get poor performance because oracle will get contention on db buffer cache since first of all index block need to be picked up as well as almost all block from that table will be pull out. Hence it will increase the I/O, also other user request may get slow performance since existing data in cache will be flush out and reloaded.
Additionally we need to check system level performance, either any problem with dbwn either dbwn writing slow any modified data which is in buffer to datafile and either user server process is waiting for space in buffer cache?
Check alert log file too.
Check if user query needed join or sorting?
Check either there is not enough space in temporary tablespace?
If user again user again facing issue then we need drill down to check either any issue with table block level either table needs defragments if watermark reached high.
What is Difference between sqlnet.ora, listener.ora, tnsname.ora network file?
sqlnet.ora: The normal location for this file is D:\oracle\ora92\network\admin. The sqlnet.ora file is the profile configuration file, and it resides on the client machines and the database server. The sqnet.ora is text file (optional) that contain basic configuration details used by the SQL*Net. It contain network configuration details such domain name, as what path to take in resolving then name of an instance, order of naming method, authentication services etc.
listener.ora: The normal location for this file is D:\oracle\ora92\network\admin. This file is client side file (typically on remote PC). The client uses this tnsname.ora file to obtain connection details from the desired database.
tnsname.ora: The normal location for this file is D:\oracle\ora92\network\admin. This file is located on both client and server. If you make configuration changes on the server ensure you can connect to the database through the listener if you are logged on to the server. If you make configuration change on the client ensure you can connect from your client workstation to the database through the listener running on the server.
What is the address of official oracle support? or
Is the password in oracle case sensitive?
In oracle 10g and earlier version ‘NO’ and since 11g is ‘YES’
What is the difference between ISNULL and IS NOT NULL operators?
The IS NULL and IS NOT NULL operators are used to find the NULL and not NULL values respectively. The IS NULL operator returns TRUE, when the value is NULL; and FALSE, when the value is not NULL. The IS NOT NULL operator returns TRUE, when the value is not NULL; and FALSE, when the value is NULL.


  1. Good article, but there is at least one inaccuracy in it.

    1. If any things is inaccurate please write exactly or clearly.

  2. ASSALAM ALAIKUM .. thank you :) for sharing this info
    Afroz khan.

  3. Good One