1)Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode? benefits and disadvantages to each.
Ans : ARCHIVELOG mode is a mode that you can put the database in for creating abackup of all transactions that have occurred in the database so that you canrecover to any ball in time.
NOARCHIVELOG mode is basically the absence ofARCHIVELOG mode and has the disadvantage of not being able to recover to any ball in time. NOARCHIVELOG mode does have the advantage of not having towrite transactions to an archive log and thus increases the performance of the database slightly.
2) What command would you use to create a backup control file?
Ans : Alter database backup control file to trace.
3) How many redo logs should you have and how should they be configured for maximum recoverability?
Ans : You should have at least three groups of two redo logs with the two logs each on a separate disk spindle (mirrored by Oracle). The redo logs should not be on raw devices on UNIX if it can be avoided
4)What are the steps involved in Instance Recovery?
5)What is Restricted Mode of Instance Startup?
Ans : An instance can be started in (or later altered to be in) restricted mode so that when the database is open connections are limited only to those whose user accounts have been granted the RESTRICTED SESSION system privilege
6)What are standby databases? Difference between Physical and logical standby databases
Ans : A standby database is a replica of the original database. In order to keep both the database synchronized, archived redo logs can be used. It is mainly used in disaster protection. It can also be opened in read only mode which allows it to be used independently for reporting.
A logical standby database allows new database objects like tables, indexes to be added to the database. On the other hand, the physical standby database is a physical or structural copy of primary database. They can be opened in read only for disaster recovery.
7)How many memory layers are in the oracle shared pool? Explain them
Ans : Oracles shared pool consists of two layers namely, Library cache and Data dictionary cache.
Library cache: This layer has information about SQL statements that were parsed, information about cursors and any plan data.
Data Dictionary cache: This Layer has information about the accounts of the users, their privileges and segments information
8)Explain the concept of the DUAL table.
Ans : On installing Oracle database, DUAL table Is present by default. It is a special table with just one row. It has a single column called DUMMY. The data type of this column in VARCHAR2(1). It has a value “X”. It is most commonly used to select pseudo columns in Oracle like sysdate.
Select sysdate from dual
9)How can we find how many users are currently logged into the database and their respective operating system id?
10)Can we reduce the space of TEMP datafile? How?
11)Explain the difference between a hot backup and a cold backup
12)Can we perform hot backup without being in archive log mode, can you recover in the event of a failure? Why or why not?
13)Describe in brief about oracle database tuning.
Oracle includes many performance tuning enhancements like:
Automatic Performance Diagnostic and Tuning Features
14)Explain how oracle optimizer works.
Ans : The optimization is determining the most efficient way to execute a SQL statement after considering the factors related to the objects referenced and the conditions specified in the query.This determination is an important step in the processing of any SQL statement and can greatly affect execution time.
The optimizer first evaluates expressions and conditions containing constants.Then Statement transformation follows which is followed by Choice of optimizer approaches. Then is the Choice of access paths followed by Choice of join orders and finally the Choice of join methods.
15)How to set pga size, can you change it while the database is running?
Ans : show parameter pga_aggregate_target;
alter system set pga_aggregate_target=100m;
Yes the pga can be changed while the database is up and running.
16) What is an index, how many types of indexes you know? Why you need an index
Ans : Index is an oracle object which is used to retrieve the data much faster rather than scanning entire table.Typically this is like an index page in a book which contains the links to the pages,where we can go through easily through out the book.
If index page is not there,we have to search each and every page for our need,so we use indexes in oracle also to retrive the data quickly.
Types of indexes:
17) Define different types of tablespaces you know?
Ans :Permanent: system table space,sys aux,user
undo: to store undo segments
temp: to store the sort segments
18) How to find the datafiles that associated with particular tablespace? Ex: System
Ans : SQL> desc dba_data_files
SQL> select * from dba_data_files where tablespace_name=’SYSTEM';
19) What is the oracle block, can you explain?
Ans : Oracle block is the lowest level of storage structure where it contains the data(business/oracle data)
The block has divided into many sections starting from block header,row header,row directory,ITL list(Intrested Transaction List),free space.
20) What is the difference between v$ views and dba views?
21) What is row migration? When does it occur? Where can you find this information?
Ans : 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.
select table_name,chain_cnt from dba_tables where table_name=’tablename';
set pct_free storage parameter for table to adequate.;
22)What is profile? what is the benefit of profile? Where do you see the information of profiles? Provide an example of profile?
Ans : Profile is a set of properties assign to an user
For an example password complexity,password reuse,password expiry,idle time etc
SQL> select username,profile from dba_users;
23)What is fragmentation of table?
Ans : Fragmentation of a table is something when ever there is a purge or deletion of a table.Oracle will not use those unused blocks and always try to allocate the extents above high watermark.this leads the table to grow larger than its size.
24) What are latches?
Ans : latches are typically a kind of locks but held for very short time to protect the memory structures of the instance
25)What is checkpoint? why database need it?
Ans : checkponit which occurs when ever the redolog switch happens,during this ckpt process writes the check point information to control file and the data file header and tells to dbwr to flush the dirty buffer from buffer cache to disk until that check point.
26)What is the difference between Oracle home and oracle base
Ans : ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.
Oracle DBA Interview questions & material
|Selection||File type icon||File name||Description||Size||Revision||Time||User|