Home > COREDBA > Oracle DBA Interview Questions and Answers

Oracle DBA Interview Questions and Answers

January 28, 2014

Dear friends, i have collected these from net different sources and  verifying each every question and separating them based on the topic. Mostly in another 3 days of time, you will all the questions in proper format. ALL THE BEST.

Resume based.

         Tell me about yourself? Your role as a DBA? Your Day to Day activities?

General:-

How to check Oracle database version?
SQL> Select * from v$version;

Installation:

What are the steps to install oracle on Linux system? List two kernel parameter that effect oracle installation?

1) configure the storage. create the folders as per the requirements.
2) configure the required users(oracle) and groupds(oinstall, dba).
3) configure the kernal parameters(SHMMAX, SHMMNI, SHMALL)
4) Give the necessary permissions on the folders to Oracle user.
5) Create the graphical environment.(vnc etc..)
6) Start the installation and provide the inputs.
7) Run the root.sh

Why do you run orainstRoot and ROOT.SH once you finalize the Installation?
orainstRoot.sh needs to be run to change the Permissions and groupname to 770 and to dba.
Root.sh (ORACLE_HOME location) needs to be run to create a ORATAB in /etc/oratab or /opt/var/oratab in Solaris and to copy dbhome, oraenv and coraenv to /usr/local/bin.
orainstRoot.sh
[root@oracle11g ~]# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory to 770.
Changing groupname of /u01/app/oraInventory to dba.
The execution of the script is complete
root.sh
[root@oracle11g ~]# /u01/app/oracle/product/11.1.0/db_1/root.sh
Running Oracle 11g root.sh script…
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/11.1.0/db_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin …
Copying oraenv to /usr/local/bin …
Copying coraenv to /usr/local/bin …
Creating /etc/oratab file…
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished product-specific root actions.
For Oracle installation on unix/linux, we will be prompted to run a script ‘root.sh’ from the oracle inventory directory.this script needs to run the first time only when any oracle product is installed on the server.
It creates the additional directories and sets appropriate ownership and permissions on files for root user.
File types and its extension when OMF is enabled
File type Extension Default  location (when created with OMF)
Pfile :
ORA
C:\oracle\product\10.2.0\admin\orcl\pfile
Spfile:
ORA
C:\oracle\product\10.2.0\db_1\database
Control file:
CTL
C:\oracle\product\10.2.0\oradata\orcl
Redo log file:
LOG
C:\oracle\product\10.2.0\oradata\orcl
Archive log file:
LOG
C:\oracle\product\10.2.0\flash_recovery_area\ORCL\ARCHIVELOG
Data file:
DBF
C:\oracle\product\10.2.0\oradata\orcl
Alert log files:
LOG
C:\oracle\product\10.2.0\admin\orcl\adump
Trace log files:
TRC
C:\oracle\product\10.2.0\admin\orcl\udump|bdump|cdump
Password file:
ORA
C:\oracle\product\10.2.0\db_1\database

Tuning:-

What are bind variables?
With bind variable in SQL, oracle can cache queries in a single time in the SQL cache area. This avoids a hard parse each time, which saves on various locking and latching resource we use to check object existence and so on.

What happens when we fire SQL statement in Oracle?

It goes three stages.

a) parsing.
b) optimization.
c) execution.
d)fetch(in case of select).

<PENDING> Still i have to write further on this. will provide shortly.

Architecture

What is difference between oracle SID and Oracle service name?

Oracle SID is the unique name that uniquely identifies your instance/database where as the service name is the TNS alias can be same or different as SID.

What is the difference between data block/extent/segment?
A data block is the smallest unit of logical storage available in oracle database. Extent is nothing but collection of one more contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

What is the difference between PGA and UGA?

When you are running dedicated server then process information stored inside the process global area (PGA) and when you are using shared server then the process information stored inside user global area (UGA).

What is SGA? 

The SGA is memory structure and a part of instance. this will be created while starting the instance. SGA is dedicated to only one oracle instance. All oracle process uses the SGA to hold information. The SGA is used to store incoming data and internal control information that is needed by the database. Following are the key components of  SGA

a) DB cache(db_cache_size),

b) Shared pool (shared_pool_size)

c) log buffer (log_buffer).

Define structure of shared pool component of SGA?
Shared pool contain two major area:
Library cache (parse SQL statement, cursor information and execution plan),
data dictionary cache (contain cache, user account information, privilege user information, segments and extent information,

What is the difference between SMON and PMON processes?
SMON (System Monitor) performs recovery after instance failure, monitor temporary segments and extents; clean temp segment, coalesce free space. It is mandatory process of DB and starts by default.
PMON (Process Monitor) failed process resources. In shared server architecture monitor and restarts any failed dispatcher or server process. It is mandatory process of DB and starts by default.

What is a system change number (SCN)?
SCN is a value that is incremented whenever database changes are made. For further updates on SCN, please refer to my article in this blog.

What is the main purpose of ‘CHECKPOINT’ in oracle database? How do you automatically force the oracle to perform a checkpoint?
A checkpoint is a database event, which synchronize the database blocks in memory with the datafiles on disk. It has two main purposes: To establish a data consistency and 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

Specifies (in seconds) the amount of time that has passed since the incremental checkpoint at the position where the last write to the redo log (sometimes called the tail of the log) occurred. This parameter also signifies that no buffer will remain dirty (in the cache) for more than integer seconds. Basically it ensures that check point occurs for every LOG_CHECKPOINT_TIMEOUT interval. this is in seconds.

LOG_CHECKPOINT_INTERVAL = 1000; # number of OS blocks.

Specifies the frequency of checkpoints in terms of the number of redo log file blocks that can exist between an incremental checkpoint and the last block written to the redo log. This number refers to physical operating system blocks, not database blocks.

What is the use of large pool, which case you need to set the large pool?

large pool is an optional memory structure. You need to set large pool if you are using: MTS (Multi thread server) or RMAN Backups. Large pool prevents RMAN & MTS from competing with other sub system(shared pool memory) for the same memory. RMAN uses the large pool for backup & restore when you set the DBWR_IO_SLAVES or BACKUP_TAPE_IO_SLAVES parameters to simulate asynchronous I/O. If neither of these parameters is enabled, then Oracle allocates backup buffers from local process memory rather than shared memory. Then there is no use of large pool.  i have one more url for this article in blog. Please refer to the same.

What does database do during the mounting process?
While mounting the database oracle reads the data from controlfile which is used for verifying physical database files during sanity check during the open state.

What are logfile states?
To see the status of the redo log file there are two dynamic views are there.
A)V$LOG
B)V$LOGFILE

A)V$LOG:
V$LOG displays redo log file information from the control file.

Status:
1) UNUSED – Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.

2) CURRENT – Current redo log. This implies that the redo log is active. The redo log could be open or closed.

3) ACTIVE – Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.

4) CLEARING – Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.

5) CLEARING_CURRENT – Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.

6) INACTIVE – Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived

What is log switch?
The point at which oracle ends writing to one online redo log file and begins writing to another is called a log switch. You can force the log switch.
ALTER SYSTEM SWITCH LOGFILE;

Explain Oracle Architecture?

Oracle server:
a DBMS that provides an open, comprehensive, integrated approach to information management,Consists of an Instance and a database.
Oracle database:
a collection of data that is treated as a unit,Consists of Datafiles, Control files, Redo log files. (optional param file, passwd file, archived log)
Oracle Instance:
a means to access an Oracle database,always opens one and only one database accesses it and consists of memory structures and background process.
Instance memory Structures:
System Global Area (SGA):
Allocated at instance startup, and is a fundamental component of an Oracle Instance.
 
SGA Memory structures:
Includes Shared Pool,  Database Buffer Cache, Redo Log Buffer among others.
Shared Pool :
Consists of two key performance-related memory structures Library Cache and  Data Dictionary Cache.
Library Cache:
Stores information about the most recently used SQL and PL/SQL statements and enables the sharing of commonly used statements.
Data Dictionary Cache :
Stores collection of the most recently used definitions in the database Includes db files, tables, indexes, columns etc. Improves perf. During the parse phase, the server process looks at the data dictionary for information to resolve object names and validate access.
Database Buffer Cache:
Stores copies of data blocks that have been retrieved from the datafiles.
Redo Log Buffer :
Records all changes made to the database data blocks, Primary purpose is recovery. Redo entries contain information to reconstruct or redo changes.
User process:
Started at the time a database User requests connection to the Oracle server. Run at client location where db user initiates the connection. Interaction with the Oracle server, does not interact directly with the Oracle server. It happens through Oracle listener initially.
Server process:
Connects to the Oracle Instance and is Started when a user establishes a session based on the architecture. Fulfills calls generated and returns results.
Each server process has its own non-shared PGA when the process is started.
Server Process Parses and run SQL statements issued through the application, Reads necessary data blocks from datafiles on disk into the buffer cache of the SGA, if the blocks are not already present in buffer cache.  Uses LRU algorithm to manage the cache.
Program Global Area (PGA):  
Memory area used by a single Oracle server process. Allocated when the server process is started, deallocated when the process is terminated and used by only one process.
Used to process SQL statements and to hold logon and other session information.
Background processes: 
Started when an Oracle Instance is started.
Background Processes Maintains and enforces relationships between physical and memory structures
There are two types of database processes:
      1.      Mandatory background processes
      2.      Optional background processes
Mandatory background processes:
– DBWn, PMON, CKPT,  LGWR,  SMON
Optional background processes:
– ARCn, LMDn, RECO, CJQ0, LMON, Snnn, Dnnn, Pnnn, LCKn, QMNn
DBWn writes when:
• Checkpoint occurs
• Dirty buffers reach threshold
• There are no free buffers
• Timeout occurs
• RAC ping request is made
• Tablespace OFFLINE
• Tablespace READ ONLY
• Table DROP or TRUNCATE
• Tablespace BEGIN BACKUP
Log Writer (LGWR) writes:
• At commit
• When 1/3rd full
• When there is 1 MB of redo
• Every 3 seconds
• Before DBWn writes
 
System Monitor (SMON) Responsibilities:
• Instance recovery
– Rolls forward changes in redo logs
– Opens database for user access
– Rolls back uncommitted transactions
• Coalesces free space
• Deallocates temporary segments.
Process Monitor (PMON) Cleans up after failed processes by:
• Rolling back the transaction
• Releasing locks
• Releasing other resources
• Restarting dead dispatchers
Checkpoint (CKPT) Responsible for:
• Signaling DBWn at checkpoints
• Updating datafile headers with checkpoint information
• Updating control files with checkpoint information
Archiver (ARCn)
• Optional background process
• Automatically archives online redo logs when ARCHIVELOG mode is set
• Preserves the record of all changes made to the database
What is oraInventory ?
oraInventory is repository (directory) which store/records oracle software products & their oracle_homes location on a machine. This Inventory now a days in XML format and called as XML Inventory where as in past it used to be in binary format & called as binary Inventory.
There are basically two kind of inventories,
One is Local Inventory (also called as Oracle Home Inventory) and other is
Global Inventory (also called as Central Inventory).

What is Local Inventory ?

Inventory inside each Oracle Home is called as local Inventory or oracle_home Inventory. This Inventory holds information to that oracle_home only.

What is Global Inventory ?

Global Inventory holds information about Oracle Products on a Machine. These products can be various oracle components like database, oracle application server, collaboration suite, soa suite, forms & reports or discoverer server . This global Inventory location will be determined by file oraInst.loc in /etc (on Linux) or /var/opt/oracle (solaris). If you want to see list of oracle products on machine check for file inventory.xml under ContentsXML in oraInventory Please note if you have multiple global Inventory on machine check all oraInventory directories)
You will see entry like
HOME NAME=”ORA10g_HOME” LOC=”/u01/oracle/10.2.0/db” TYPE=”O” IDX=”1?/

What is Oracle Home Inventory?

Oracle home inventory or local inventory is present inside each Oracle home. It only contains information relevant to a particular Oracle home. This file is located in the following location:
$ORACLE_HOME/inventory
It contains the following files and folders:
·         Components File
·         Home Properties File
·         Other Folders
Can I have multiple Global Inventory on a machine ?
Quite common questions is that can you have multiple global Inventory and answer is YES you can have multiple global Inventory but if your upgrading or applying patch then change Inventory Pointer oraInst.loc to respective location. If you are following single global Inventory and if you wish to uninstall any software then remove it from Global Inventory as well.
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=”{}”
Specific to 11g:
Oracle Database 11g New Feature for DBAs?
1) Automatic Diagnostic Repository [ADR]
2) Database Replay
3) Automatic Memory Tuning
4) Case sensitive password
5) Virtual columns and indexes
6) Interval Partition and System Partition
7) The Result Cache
8) ADDM RAC Enhancements
9) SQL Plan Management and SQL Plan Baselines
10) SQL Access Advisor & Partition Advisor
11) SQL Query Repair Advisor
12) SQL Performance Analyzer (SPA) New
13) DBMS_STATS Enhancements
14) The Result Cache
15) Total Recall (Flashback Data Archive)
Note: The above are only top new features, there are other features as well introduced in 11g which will be included subsequently

What is RESULT Cache?

SQL Result Cache is another cache in the SGA, just like buffer cache or program global area. When you execute a query with the hint result_cache, Oracle performs the operation just like any other operation but the results are stored in the SQL Result Cache. Subsequent invocations of the same query do not actually go to the table(s) but get the results from the cache. The size of the cache is determined by several initialization parameters:

Parameter Description
result_cache_max_size Maximum size of the result cache (5M for 5 MB, for example). If you set this to 0, result caching will be completely turned off.
result_cache_max_result Specifies the percentage of result_cache_max_size that any single result can use
result_cache_mode If set to FORCE, all the queries are cached if they fit in the cache. The default is MANUAL, which indicates that only queries with the hint will be cached.
result_cache_remote_expiration Specifies the number of minutes that a cached result that accesses a remote object will remain valid. The default is 0.

I am still working on above result cache but further details you refer to below URL.

http://www.oracle.com/technetwork/articles/sql/11g-caching-pooling-088320.html

11G Backgroung Processes?

The following process are added in 11g as new background processes.
1: dbrm DB resource manager
2: dia0 Diagnosability process
3: fbda Flashback data archiver process
4:  vktm Virtual Timekeeper
5: w000 Space Management Co-ordination process
6: smc0 Space Manager process
NOTE : The above six are mandatory processes.
But 11g has 56 new processes added which can be queried using
If any one of these 6 mandatory background processes is killed/not running, the instance will be aborted ?
Background processes are started automatically when the instance is started.
Mandatory background processes are DBWn, LGWR, CKPT, SMON, PMON, and RECO. All other processes are optional, will be invoked if that particular feature is activated.
If any one of these 6 mandatory background processes is killed/not running, the instance will be aborted.

Any issues related to background processes should be monitored and analyzed from the trace files generated and the alert log.

Literal Vs. Bind Variables?

Select * from emp where dept=10; what level should i configure for better performance Where Clause …. Literal or Bind Variables? Expalin?

What is a Baseline?

Incarnation? Explain in detail? Where the incarnation information will be stored? 

Hard Parse Vs. Soft Parse?

What is semaphores, semaphores?

What is latch?

What is Enqueue?

What is SGA_TARGET and SGA_MAX_SIZE ?
SGA_MAX_SIZE is the largest amount of memory that will be available for the SGA in the instance and it will be allocated from memory. You do not have to use it all, but it will be potentially wasted if you set it too high and don’t use it. It is not a dynamic parameter. Basically it gives you room for the Oracle instance to grow.
SGA_TARGET is actual memory in use by the current SGA. This parameter is dynamic and can be increased up to the value of SGA_MAX_SIZE.
SGA_MAX_SIZE and SGA_TARGET both are the parameter are used to change the SGA SIZE.
SGA_MAX_SIZE sets the maximum value for sga_target.
SGA_TAGET is 10G feature used to change the sga size dynamically .it specifies the total amount of SGA memory available to an instance.
this feature is called Automatic Shared Memory Management. With ASMM, the parameters java_pool_size, shared_pool_size, large_pool_size and db_cache_size are affected.
 
SGA_MAX_SIZE & SGA_TARGET
SGA_MAX_SIZE sets the overall amount of memory the SGA can consume but is not dynamic.
The SGA_MAX_SIZE parameter is the max allowable size to resize the SGA Memory area parameters. If the SGA_TARGET is set to some value then the Automatic Shared Memory Management (ASMM) is enabled, the SGA_TARGET value can be adjusted up to the SGA_MAX_SIZE parameter, not more than SGA_MAX_SIZE parameter value.
I.e. If SGA_MAX_SIZE=4GB and SGA_TARGET=2GB, later period of time, if you want you can resize your SGA_TARGET parameter to the value of SGA_MAX_SIZE i.e. 4GB, you can’t resize the SGA_TARGET value to more than 4GB.
It is significant that SGA_TARGET includes the entire memory for the SGA, in contrast to earlier releases in which memory for the internal and fixed SGA was added to the sum of the configured SGA memory parameters. Thus, SGA_TARGET gives you precise control over the size of the shared memory region allocated by the database. If SGA_TARGET is set to a value greater than SGA_MAX_SIZE at startup, then the latter is bumped up to accommodate SGA_TARGET
Do not dynamically set or unset the SGA_TARGET parameter. This should be set only at startup.
SGA_TARGET is a database initialization parameter (introduced in Oracle 10g) that can be used for automatic SGA memory sizing.
SGA_TARGET provides the following:
§  Single parameter for total SGA size
§  Automatically sizes SGA components
§  Memory is transferred to where most needed
§  Uses workload information
§  Uses internal advisory predictions
§  STATISTICS_LEVEL must be set to TYPICAL
§  SGA_TARGET is dynamic
§  Can be increased till SGA_MAX_SIZE
§  Can be reduced till some component reaches minimum size
§  Change in value of SGA_TARGET affects only automatically sized components
If I keep SGA_TARGET =0 then what will happen ?
Disable automatic SGA tuning by setting sga_target=0
Disable ASMM by setting SGA_TARGET=0
SGA_TARGET is a database initialization parameter (introduced in Oracle 10g) that can be used for automatic SGA memory sizing.
Default value     0 (SGA auto tuning is disabled)
What happens when you run ALTER DATABASE OPEN RESETLOGS ?
The current online redo logs are archived, the log sequence number is reset to 1, new database incarnation is created, and the online redo logs are given a new time stamp and SCN.
The reason to do the open the database with the resetlogs is that after doing an incomplete recovery , the data files and control files still don’t come to the same point of the redo log files. And as long as the database is not consistent within all the three file-data, redo and control, you can’t open the database. The resetlogs clause would reset the log sequence numbers within the log files and would start them from 0 thus enabling you to open the database but on the cost of losing all what was there in the redo log files.
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.
Whenever you perform incomplete recovery or recovery with a backup control file, you must reset the online logs when you open the database. The new version of the reset database is called a new incarnation..
 
Difference between RESETLOGS and NORESETLOGS ?
After recover database operation, open the database with: ALTER DATABASE OPEN [NO]RESETLOGS
NORESETLOGS:
The NORESETLOGS option does not clear the redo log files during startup and the online redo logs to be used for recovery. Only used in scenario where MANUAL RECOVERY is started, CANCEL is used, and then RECOVER DATABASE is started.
RESETLOGS:
CAUTION: Never use RESETLOGS unless necessary.
Once RESETLOGS is used then the redo log files cannot be used and any completed transactions in those redo logs are lost!!
Before using the RESETLOGS option take an offline backup of the database.
What is SCN (System Change Number) ?
The system change number (SCN) is an ever-increasing value that uniquely identifies a committed version of the database at a point in time. Every time a user commits a transaction Oracle records a new SCN in redo logs.
Oracle uses SCNs in control files datafile headers and redo records. Every redo log file has both a log sequence number and low and high SCN. The low SCN records the lowest SCN recorded in the log file while the high SCN records the highest SCN in the log file.
What is Database Incarnation ?
Database incarnation is effectively a new “version” of the database that happens when you reset the online redo logs using “alter database open resetlogs;”.
Database incarnation falls into following category Current, Parent, Ancestor and Sibling
i) Current Incarnation : The database incarnation in which the database is currently generating redo.
ii) Parent Incarnation : The database incarnation from which the current incarnation branched following an OPEN RESETLOGS operation.
iii) Ancestor Incarnation : The parent of the parent incarnation is an ancestor incarnation. Any parent of an ancestor incarnation is also an ancestor incarnation.
iv) Sibling Incarnation : Two incarnations that share a common ancestor are sibling incarnations if neither one is an ancestor of the other.
How to view Database Incarnation(History of Database)?
     SQL> select * from v$database_incarnation;
RMAN>LIST INCARNATION;
What are new background processes are there in 10g and 11g
Advertisements
Categories: COREDBA
%d bloggers like this: