Oracle interview questions
When you enter the interview panel, the rule of thumb question asked by every interviewer is to introduce yourself.I have just mentioned a sample introduction.There is again no thumb rule to answer the question in one way.There are multiple ways of answering this question based on each perspective.
1)Tell me about yourself,your activities in your day to day life as a dba
I am basically a database admin with x years of experience.
I handled over 100 oracle servers with 200 databases and 300 instances in my previous projects.
I have good experience on installation,configuration,upgradation,migration,user management,tablespace management,patching,cloning,backup,high availability solutions like RAC,dataguard,performance tuning and so on …
2)Can you explain about the architecture of oracle database 12c?
The instance is called memory + background processes like SGA and PGA.Also UGA depending on shared servers.
SGA consist of shared pool,database buffer cache,redo buffer,large pool,java pool,streams pool.
Database consists of physical files like datafiles,online redolog files,controlfiles,archivelog files etc.
Mandatory background processes: SMON,PMON,DBWR,LGWR and CKPT
3)What is SMON?
smon manages instance crash recovery and clean up the temporary segments.
4)What are the modes of database buffer cache?
Free(unused),pinned(held) and dirty buffer(modified)
5)How to calculate the size of database?Mention the views?
dba_segments, dba_extents, v$datafile, dba_data_files, dba_temp_files, v$tempfile, dba_free_space
6)How to calculate the memory for a database?Mention the views?
select sum(value)/1024/1024/1024 “SGA_SIZE (GB)” from v$sga;
select sum(pga_max_mem)/1024/1024/1024 “PGA_SIZE (GB)” from v$process;
7)How to check the resource consumption (CPU,memory) of a process in a Linux server?
TOP command,free -mg, mpstat,sar,vmstat
8)What is the state of redolog when new member is added to redolog?
Unused
9)If one of the controlfile is corrupted,what happens to the database?
we get error “ORA-00205: error in identifying control file, check alert log". Recent controlfile needs to be restored from backup.
10)For a database ,how many recovery catalog can be present?
we can have multiple catalogs for a database.
But multiple databases can register to a single catalog.
11)explain about the sql processing in oracle?
Whenever sql statement is executed ,it has to enter the library cache into shared pool and the statement is parsed.
The parse has to undergo syntax check and semantic check.
The optimization has to generate execution plan for the statement with hash value.
Then the result is retrieved from the object.
12)What will you do if a user complains performance issues on the particular sql query?
fetch the sqlid using v$sql and v$session.use sql tuning advisor to get the advise.You can add index to the predicate column for example as per the advise.
13) how to show all the nodes in a RAC cluster?
olsnodes -nip
13)How to check if dataguard is running in a database?
v$dataguard_status, show parameter log_archive_Dest_2
14) How to check if mrp is running in physical standby?
v$managed_standby and ps -ef | grep mrp on standby
15)Things to check on awr report for performance issues?
dbtime ,elapsed time,top five foreground events by CPU time ,elapsed time and wait events- db_file_sequential_read,db_file_scattered_read etc.Please refer online resources to get more details on these jargons