Tuesday 2 August 2016

Frequently Asked Interview Questions for 2+ Candidate


::     Frequently Asked Interview Questions for 2+ Candidate   ::

Experience Level  :  2 -3 years

What is main purpose of CHECKPOINT?
A Checkpoint is a database event, which synchronizes the data blocks in memory with the datafiles on disk.
A checkpoint has two purposes:
1. to establish data consistency
2. Enable faster database recovery

The following are the parameter that will be used by DBA to adjust time or interval of how frequently its checkpoint should occur in database.
LOG_CHECKPOINT_TIMEOUT = 3600;                                // Every one hour
LOG_CHECKPOINT_INTERVAL = 1000;               //number of OS blocks.


What does RESETLOGS option do?

1. Creates a new incarnation of the database, putting a new SCN in all data file headers.
2. Reset Log Sequence number to 1
3. Reformats ONLINE REDO LOGFILES if they exists


In what scenarios open resetlogs required ?
An ALTER DATABASE OPEN RESETLOGS statement is required,
1. after incomplete recovery (Point in Time Recovery) or
2. recovery with a backup control file.
3. recovery with a control file recreated with the reset logs option.


What to do if my Global Inventory is corrupted ?
No need to worry if your global Inventory is corrupted, you can recreate global Inventory on machine using Universal Installer and attach already Installed oracle home by option
-attachHome
./runInstaller -silent -attachHome -invPtrLoc $location_to_oraInst.loc
ORACLE_HOME=”Oracle_Home_Location” ORACLE_HOME_NAME=”Oracle_Home_Name”
CLUSTER_NODES=”{}”


Oracle version 11.2.0.4.0 what does each number refers to?
Oracle version number refers :
11 – for Major database release number
2 – for Database Maintenance release number
0 – for Application server release number
4 – for Component Specific release number
0 – for Platform specific release number


What is Row chaining and how can you find it? and how you will resolve the issue ?
When you are inserting record if a row is not fit into the single block… it will chain across multiple blocks leaving  a pointer between these  blocks.

Find using below query
SQL> analyze table <owner>.<table_name>;

SQL> select table_name,chain_cnt from dba_tables where table_name=’tablename';

To Overcome :
create a table with bigger the block size
1) Create tablespace tbs1 data file ‘/u01/oradata/test/data01.dbf’ size 100m blocksize 16k;
2) alter table tablename move to tbs1;
Here tbs1 is the tablespace name with larger block size and  before creating tablespace it is assumed that you have created a db buffer cache for it.

What is row migration? When does it occur? Where can you find this information?
Row migration happens when update occurs at one column and the row is not adequate to fit in the block then the entire row will be moved to the new block.

Find using below query
SQL> select table_name,chain_cnt from dba_tables where table_name=’tablename';

To Overcome :
-set pct_free storage parameter for table to adequate value


Do you know about statistics , what is the use of it? What kind of statistics exists in database?
Statistics is a collection information about data or database
There are different types of statistics that oracle maintains-
1)System-Statistics: statistics about the hardware like cpu speed,I/O speed,read time write time etc : select * from aux_stats$
2)Object statistics : For a table oracle collects the information about no.of rows,no.of blocks,avg row length etc.We can view
SQL>select table_name,num_rows,blocks,avg_row_len from dba_tables
for index oracle collect statistics on index column about no.of rows,no.of root blocks,no.of branch blocks,no.of leaf blocks,no.of distinct values etc.

See also

Interview Questions for 2+ Year experienced Candidates

Interview Questions on Architecture for 1+ Experienced

DBA 1+ year Interview 

General Tips to Prepare for an Oracle DBA Job Interview *Popular*

 

FREE Sign-Up with Gmail and Receive Pdf's and Updates via Mail -Click Here


 

 

 

No comments:

Post a Comment