Interview on 07-Aug-2015

Interview question for 4-8 years experience held on 07-Aug-2015
1.Tell me about your self
2.Day to day activity in office as oracle dba
3. What is the flow of select statement?
4.What are the steps for oracle RAC installation?
5.What is the purpose of standby redolog files? why it is used?
6.Difference between 10g and 11g?
7.One table is accidentally dropped by user. You don't have flash back feature enabled. Database is production database and in terms of TB. How to restore using RMAN?
8.What is the steps of physical standby creation?
9. What are difference between exp and expdp?
10.What is voting disk and ocr?
11. Why odd numbers of voting disks are used?
12.How to trouble shoot when one node is down in RAC? What are the possible reasons?
13. In expdp what method used to take backup byte or block?
14. What are the advantages of datapump over tradition export ?
15.Suppose a user is connected to the database. and mean the time listner get down. What would happen to that user? When the user will give a statement will that process?
16.User complained one query is not performing well ? what action will be taken?
17.one index is already present in a column. Now we create primary key on that column. will new index will be created?
18. One big transaction was going on and client machine got rebooted. At that time what would happen to that transaction. If it roll back then which process will do this?
19. How to recover undo tablespace?
20 How to recover if redolog files got deleted?
21.What are the steps to create asm instance?
22. What is SCAN listner?
23.If one archive log file has not been applied and it got deleted. How to make standby database sync with primary database?
24.Explain different background process of RAC?
25. Explain background process for ASM?

Interview questions

1)What is the directory where you need  to check if Grid failure happen?
2)Crosscheck command?
3)What is active duplicate in oracle?difference between normal and active duplicate?
4)How to create standby database?
5)AWK function?
6)What is the difference between sqlid and Hash value?
7)How to take the procedure backup in expdp?
8)How to upgrade 10g standby databse to 11g standby database?
9)What is your apporach for AWR report?
10)What is active data guard in oracle?
11)How to switch all the connection to other node?

Interview question

-cold migration from solaris to unix.Steps
-restoring from backup by skiping few tablespaces
-difference between expiration and absolte backup in rman
-standby few archive logs are missing but later archives are there how to syn the standby
-you find user stating query is slow what would be your action
-how to provide explain plan
-you want to check what is the current explain plan for a user
-how to perform agent installation
-In RAC one node is continuesly booting--how to trouble shoot
-CRS is not comming up what needs to be done

Oracle DBA Interview Questions

Oracle DBA interview 09-Feb-2012 :-

1)What are the basic steps in upgrading 10g to 11g?
2)What is the difference between opatch napply and opatch apply?What is cpu patch?
3)How to determine parallel option in datapump?
4)you have rman full backup at 9p.m. and one table got droped
5)What are the portions you would look in AWR report?and how you are going to troubleshoot?
6)What is present in the tkprof report?
7)How to enable the trace at a session and what you will get from it?
8)One query is giving more time while executing for last one month?But you can not rewrite the logic and index and all fine?
9)Oracle 10g database.One table is droped one hour ago by mistake and you donot have the backup and flash back feature is not enabled?How you will able to recover?
10)How to delete materialize view logs?
11) We cannot rewrite the query due to business logic and statistics gather and index creation is correct for this query.This query is slow in giving result? How to troubleshoot it?

Oracle DBA interview 10-Feb-2012 :-

1)What is instance?
2)What is the advantage of RMAN?
3)What is block corruption and how to recover it?
4)What happens during begin backup mode?
5)What are the different mode of dataguard?
6)How many standby database can be configured?
7)What is SQL profile?
8)what is the db file sequential read and db file scatter read events?
9)How to find one query is running in which database ?
10)What is the difference between level 0 and level 1 backup?
11)CPU utilization is more in a server, What would be your action to reduce the utilization?
12)How to check which patch is applied in the server?
13)What is the advantage of datapump over traditional export,import?
14)what is the parameter to import from one schema to another schema both in data pump and tradition import?
15)What is the difference between oracle 10g and 11g?

Oracle DBA 4-5 years of experience

1)What is file system?
2)What are the steps involved during the sql statement exectuion?
3)What is symantics check?
4)What is tablespace?
5)Difference between hot backup and rman bacukup?
6)What are the advantages of rman backup?
7))what is the easiest way to create pfile if it is lost?
8)How to arrange 4 files in asending order in linux?
9)How oracle block depends upon operating system blocks?
10)How to determine the size of oracle block before setting?
11)what is the internal activity happen during the select statement?
12)what are the difference between block,extent,segment,tablespace?
13)Can extent spread across multiple datafile?
14)Can we insert,update,delete multiple rows in a single sql statement?
15)What are the parameter used in tkprof tool?
16)If a query is behaving very slowly what would be your approach?
17)What happends during statics gather?
18)How Oracle generate the  exectution plan?
19)What is weight of a query?
20)How to determine the cost of a query?
21)What are the portion you would look into an AWR report?
22)What are the monitoing tool you used?
23)Brifely describe about your project?
24)Difference between locally managed and dictionary managed tablespace?
25)Is tablespace header contain information about the tablespace?
26)What is the difference between materlize view and dynamic view?
27)How to determine what should be the size of SGA and PGA ?
28)How to determin what are the current user connect to the database?
29)What are the difference between active,inactive and idle session?
30)What are the activity ideally one DBA should know?
31)what is your role in the relase managemnt activity?
32)How are you giving privilages to user account?
33)how to create 100 user account in a database?
34)Can you write shell script program?
35)What is the difference between undo tablespace and temporary tablespace?
36)What are the contents of SGA?
37)What is the functionality of share pool?
38)what is PCT free and PCT used block?
39)What happen during index rebuilt?
40)What is partion?
41)What the differenece between index oraganize and bit map index?
42)Why you want to rebuild the index?
43)

Oracle DBA interview--4-5 years of experience

1)Tell me about your self?
2)What do you know about Roberbosh?
3)How to configure shared server in oracle database?
4)What is the difference between shared and dedicated server?
5)How to determine what should be the PGA size?
6)What do you mean by cursor?
7)How to determine what should be the cursor sharing?
8)How to determine the load in the database?
9)How to recove when redolog file is corrupted? many senarios
10)How to recover the datafile?
11)What is consistency =y in export?
12)What happen when staticts=n?
13)what is compress=y in export?
14)What are the differece between export and expdp?
15)What is the advantage of using directory in datapump?
16)difference between shutdown immediate and transactional?
17)What are the wait events?Give any 5 wait events functionality?
18)Write on shell script to perform one simple activity?
19)What are the contents of tkprof? give one description?
20)How to fill the archive log gap?

Oracle DBA  4-5 years of experience:-

In Motorola I had 2 telephonic technical round and one face to face director round and rest of the HR round are through telephonic only.

1)Tell me about your self?
2)Tell me about your project?
3)Can you describe about the data guard configuation?
4)Wha is the contents of SGA and describe about them?
5)How to recover a datafile?
6)Waht is block coruption and what are the steps involved?
7)How to remove the archive log gap in dataguard?
8)What are all the steps you will do when a query is too slow?
9)What are the major challenges you face in your project?
10)What are the tools used to monitor the performance?
11)Waht are the advantage of rman?
12)What is your backup stragy in your project?
13)Asked on senario for recovery?

Oracle DBA --4-5 years exp:-

Oracle having 4 round.One written,one technical,one managerial and one Director round

1)Tell me about your self?
2)What happen during one sql exectution?
3)How block transtion happen during roll back between undo tablespace and database buffer cache?
4)What are the network files available and what are the functionality of those?
5)What is static registration in listner?
6)Can we change the port number of 1251?If yes how?
7)how oracle will know which port to listen when multiple listener present?
8)does dictionary cache contain dynamic view?
9)Describe few errors which you might have faced?
10)What are the contents of alert log file?
11)How oracle database maintains the consistency?
12)What is the difference between connection and sessoin?
13)What are the difference between idle and active and inactive session?
14)What is the content of sqlnet.ora,tnsnames.ora and listner.ora file?
15)What is SGA and what are the functionality of those?

What happen during begin backup mode in oracle ?

          Below activity happen during begin backup mode.

1.   DBWn checkpoints the tablespace (writes out all dirty blocks as of a given SCN)
2. CKPT stops updating the Checkpoint SCN field in the datafile headers and begins updating the Hot Backup Checkpoint SCN field instead
3. LGWR begins logging full images of changed blocks the first time a block is changed after being written by DBWn

Those three actions are all that is required to guarantee consistency once the file is restored and recovery is applied. By freezing the Checkpoint SCN, any subsequent recovery on that backup copy of the file will know that it must commence at that SCN. Having an old SCN in the file header tells recovery that the file is an old one, and that it should look for the archivelog containing that SCN, and apply recovery starting there.
Note that during hot backup mode, checkpoints to datafiles are not suppressed. Only the main Checkpoint SCN flag is frozen, but CKPT continues to update a Hot Backup Checkpoint SCN in the file header.
There is a confusing side effect of having the Checkpoint SCN frozen at an SCN earlier than the true checkpointed SCN of the database. In the event of a system crash or a shutdown abort during hot backup of a tablespace, the automatic crash recovery routine during startup will look at the file headers, think that the files for that tablespace are out of date, and will suggest that you need to apply old archived redologs in order to bring them back into sync with the rest of the database. Fortunately, no media recovery is necessary. With the database started up in mount mode:

SQL> alter database end backup;

This action will bring the Checkpoint SCN in the file headers in sync with the Hot Backup Checkpoint SCN (which is a true representation of the last SCN to which the datafile is checkpointed). Once you do this, normal crash recovery can proceed during ‘alter database open;’.
By initially checkpointing the datafiles that comprise the tablespace and logging full block images to redo, Oracle guarantees that any blocks changed in the datafile while in hot backup mode will also be present in the archivelogs in case they are ever used for a recovery. Most of the Oracle user community knows that  Oracle generates a greater volume of redo during hot backup mode. This is the result of Oracle logging of full images of changed blocks in these tablespaces. Normally, Oracle writes a change vector to the redologs for every change, but it does not write the whole image of the database block. Full block image logging during backup eliminates the possibility that the backup will contain unresolvable split blocks. To understand this reasoning, you must first understand what a split block is.
Typically, Oracle database blocks are a multiple of O/S blocks. For example, most Unix filesystems have a default block size of 512 bytes, while Oracle’s default block size is 8k. This means that the filesystem stores data in 512 byte chunks, while Oracle performs reads and writes in 8k chunks or multiples thereof. While backing up a datafile, your backup script makes a copy of the datafile from the filesystem, using O/S utilities such as copy, dd, cpio, or OCOPY. As it is making this copy, your process is reading in O/S-block-sized increments. If DBWn happens to be writing a DB block into the datafile at the same moment that your script is reading that block’s constituent O/S blocks, your copy of the DB block could contain some O/S blocks from before the database performed the write, and some from after. This would be a split block. By logging the full block image of the changed block to the redologs, Oracle guarantees that in the event of a recovery, any split blocks that might be in the backup copy of the datafile will be resolved by overlaying them with the full legitimate image of the block from the archivelogs. Upon completion of a recovery, any blocks that got copied in a split state into the backup will have been resolved by overlaying them with the block images from the archivelogs.
All of these mechanisms exist for the benefit of the backup copy of the files and any future recovery. They have very little effect on the current datafiles and the database being backed up. Throughout the backup, server processes read datafiles DBWn writes them, just as when a backup is not taking place. The only difference in the open database files is the frozen Checkpoint SCN, and the active Hot Backup Checkopint SCN. To demonstrate the principle, we can formulate a simple proof:
Create a table and insert a row:

SQL> create table fruit (kind varchar2(32)) tablespace users;
Table created.

SQL> insert into fruit values ('orange');
1 row created.

SQL> commit;
Commit complete.

Force a checkpoint, to flush dirty blocks to the datafiles.

SQL> alter system checkpoint;
System altered.

Get  the file name and block number where the row resides:

SQL> select dbms_rowid.rowid_relative_fno(rowid) file_num,
            dbms_rowid.rowid_block_number(rowid) block_num,
            kind
     from fruit;
FILE_NUM BLOCK_NUM KIND
-------- --------- ------
       4       183 orange

SQL> select name from v$datafile where file# = 4;
NAME
-----------------------------
/u01/oradata/uw01/users01.dbf

Use the dd utility to skip to block 183 and extract the DB block containing the row:

unixhost% dd bs=8k skip=183 count=1 if=/u01/oradata/uw01/users01.dbf | strings
1+0 records in
16+0 records out
orange

Now we put the tablespace into hot backup mode:

SQL> alter tablespace users begin backup;
Tablespace altered.

Update the row, commit, and force a checkpoint on the database.

SQL> update fruit set kind = 'plum';
1 row updated

SQL> commit;
Commit complete.

SQL> alter system checkpoint;
System altered.

Extract the same block. It shows that the DB block has been written to disk during backup mode:

unixhost% dd bs=8k skip=183 count=1 if=/u01/oradata/uw01/users01.dbf | strings
1+0 records in
16+0 records out
plum
orange

Don’t forget to take the tablespace out of backup mode!

SQL> alter tablespace administrator end backup;
Tablespace altered.

It is quite clear from this demonstration that datafiles receive writes even during hot backup mode!

Reference:-

http://www.bluegecko.net/oracle/oracle-tablespace-hot-backup-mode-revisited/

Password file authentication implementation

Password file authentication implementation:-

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

                                                                        =SHARED

                                                                       =NONE

EXCLUSIVE—

Password is associate with only one instance.

Password file can be modify mean we add more users to the password file by granting sysdba or sysopera priviliage to users.

SHARED---

This can be shared among multiple databases.

Basically in RAC where multiple instances shared the same password file. So in RAC environment the instances path should point to the same password file.

The password file cannot be modify.

This mode allow non sys users to use the password file to connect database.

NONE—

This mode indicates that password file is not exist.

No privilege users allowed for non-secure connection.

Orapwd file=orapworcl password=oracle force=y entries=30 ignorecase=n

File=The name of the file in which encrypted password is stored. You can specify full path along with the name of the file

Password=This the password which will be used for authentication.

Force=This parameter value Y means it will forcefully create and if any  file with same name already  present, then it would overwrite on it.

Entries=This parameter indicates maximum number of allowed users that can use the password file. Hence during the creation of the password file you should choose an appropriate value.

Ignorecase=The value is Y means the password will not be case sensitive and default value is N.