Interview Qustions for 2+ Year experienced Candidates

 Hii , Today I will Give you some Questions that you may face in DBA Interview :

How do you find the number of rows in a Table ?

A bad answer is : count them (SELECT COUNT(*) FROM table_name)

 A good answer is : 'By generating SQL to ANALYZE TABLE table_name COUNT STATISTICS by querying Oracle System Catalogues (e.g. USER_TABLES or ALL_TABLES).

The best answer is : to refer to the utility which Oracle released which makes it unnecessary to do ANALYZE TABLE for each Table individually.

Why do you set kernel parameters?

Ans: Oracle recommends that you set shared memory segment attributes as well as semaphores to the following values.

If not set, database instance creation will fail. I added the following lines to /etc/sysctl.conf file.

Every OS process needs  semaphore where It waits on for the resources.

For more on semaphore, please read the UNIX os documents.

How you Identify the Corruption (rman)

An alternative method to identify block corruption in an Oracle data file is to use the RMAN validate function. The following example is taken from Windows:

RMAN> connect target sys/****

connected to target database: TAXPROD (DBID=3492187718)

RMAN> BACKUP VALIDATE CHECK LOGICAL DATABASE FILESPERSET=10;

Once the validate completes, the V$DATABASE_BLOCK_CORRUPTION can be used to check for corruption:

Q. Common Listener Issues You Faced ?

It is not very common to find listener-related problems in Oracle, and most connectivity-related problems are generally associated with database server problems, such as shortage of memory.

Here are some common errors:

    ORA-12500 TNS:listener failed to start a dedicated server process  - This is generally associated with memory shortages or permission problems in UNIX.

    ORA-3113 end-of-file on communication channel – This is generally a network failure

    TNS-12547 TNS:lost contact – This can be corrected by increasing the connect_timeout_listener parameter.

    TNS-12224 TNS:no listener – Clients will get this message if the listener process is not running.

    ORA-12570 TNS:packet reader failure – This is a network problem.

    ORA-12571 TNS:packet writer failure – This is a network problem

Note that in many cases, a failure of the listener will not be logged on the database server, but will instead be presented as a message on the client workstation.  Hence, the vast majority of listener errors are reported by end-users.

Q. How we see that our listener log is located in ?? 

 

 cat `lsnrctl stat|grep Log|awk '{print $4 }'`|grep ORA-

/ora7/home/dba/product/7.3.4/network/log/listener.log.