|
Oracle Interview Questions and Answers
It is possible to use raw devices as data files and
what is the advantages over file. system files ?
Yes. The advantages over file system files. I/O will be
improved because Oracle is bye-passing the kernnel which
writing into disk. Disk Corruption will be very less.
What are disadvantages of having raw devices ?
We should depend on export/import utility for
backup/recovery (fully reliable) The tar command cannot
be used for physical file backup, instead we can use dd
command which is less flexible and has limited
recoveries.
What is the significance of having storage clause ?
We can plan the storage for a table as how much initial
extents are required, how much can be extended next, how
much % should leave free for managing row updations
etc.,
What is the use of INCTYPE option in EXP command ?
Type export should be performed
COMPLETE,CUMULATIVE,INCREMENTAL. List the sequence of
events when a large transaction that exceeds beyond its
optimal value when an entry wraps and causes the
rollback segment toexpand into anotion Completes. e.
will be written.
What is the use of FILE option in IMP command ?
The name of the file from which import should be
performed.
What is a Shared SQL pool?
The data dictionary cache is stored in an area in SGA
called the Shared SQL Pool. This will allow sharing of
parsed SQL statements among concurrent users.
What is hot backup and how it can be taken?
Taking backup of archive log files when database is
open. For this the ARCHIVELOG mode should be enabled.
The following files need to be backed up. All data
files. All Archive log, redo log files. All control
files.
List the Optional Flexible Architecture (OFA) of Oracle
database? or How can we organize the tablespaces in
Oracle database to have maximum performance ?
SYSTEM - Data dictionary tables.
DATA - Standard operational tables.
DATA2- Static tables used for standard operations
INDEXES - Indexes for Standard operational tables.
INDEXES1 - Indexes of static tables used for standard
operations.
TOOLS - Tools table.
TOOLS1 - Indexes for tools table.
RBS - Standard Operations Rollback Segments,
RBS1,RBS2 - Additional/Special Rollback segments.
TEMP - Temporary purpose tablespace
TEMP_USER - Temporary tablespace for users.
USERS - User tablespace.
How to implement the multiple control files for an
existing database ?
Shutdown the database Copy one of the existing control
file to new location Edit Config ora file by adding new
control file. name Restart the database.
What is advantage of having disk shadowing/ Mirroring ?
Shadow set of disks save as a backup in the event of
disk failure. In most Operating System if any disk
failure occurs it automatically switchover to place of
failed disk. Improved performance because most OS
support volume shadowing can direct file I/O request to
use the shadow set of files instead of the main set of
files. This reduces I/O load on the main set of disks.
How will you force database to use particular rollback
segment ?
SET TRANSACTION USE ROLLBACK SEGMENT rbs_name.
Why query fails sometimes ?
Rollback segment dynamically extent to handle larger
transactions entry loads. A single transaction may
wipeout all available free space in the Rollback Segment
Tablespace. This prevents other user using Rollback
segments.
What is the use of RECORD LENGTH option in EXP command ?
Record length in bytes.
How will you monitor rollback segment status ?
Querying the DBA_ROLLBACK_SEGS view
IN USE - Rollback Segment is on-line.
AVAILABLE - Rollback Segment available but not on-line.
OFF-LINE - Rollback Segment off-line
INVALID - Rollback Segment Dropped.
NEEDS RECOVERY - Contains data but need recovery or
corupted.
PARTLY AVAILABLE - Contains data from an unresolved
transaction involving a distributed database.
What is meant by Redo Log file mirroring ? How it can be
achieved?
Process of having a copy of redo log files is called
mirroring. This can be achieved by creating group of log
files together, so that LGWR will automatically writes
them to all the members of the current on-line redo log
group. If any one group fails then database
automatically switch over to next group. It degrades
performance.
Which parameter in Storage clause will reduce no. of
rows per block?
PCTFREE parameter
Row size also reduces no of rows per block.
What is meant by recursive hints ?
Number of times processes repeatedly query the
dictionary table is called recursive hints. It is due to
the data dictionary cache is too small. By increasing
the SHARED_POOL_SIZE parameter we can optimize the size
of Data Dictionary Cache.
What is the use of PARFILE option in EXP command ?
Name of the parameter file to be passed for export.
What is the difference between locks, latches, enqueues
and semaphores? (for DBA)
A latch is an internal Oracle mechanism used to protect
data structures in the SGA from simultaneous access.
Atomic hardware instructions like TEST-AND-SET is used
to implement latches. Latches are more restrictive than
locks in that they are always exclusive. Latches are
never queued, but will spin or sleep until they obtain a
resource, or time out.
Enqueues and locks are different names for the same
thing. Both support queuing and concurrency. They are
queued and serviced in a first-in-first-out (FIFO)
order.
Semaphores are an operating system facility used to
control waiting. Semaphores are controlled by the
following Unix parameters: semmni, semmns and semmsl.
Typical settings are:
semmns = sum of the "processes" parameter for each
instance
(see init<instance>.ora for each instance)
semmni = number of instances running simultaneously;
semmsl = semmns
What is a logical backup?
Logical backup involves reading a set of database
records and writing them into a file. Export utility is
used for taking backup and Import utility is used to
recover from backup.
Where can one get a list of all hidden Oracle
parameters? (for DBA)
Oracle initialization or INIT.ORA parameters with an
underscore in front are hidden or unsupported
parameters. One can get a list of all hidden parameters
by executing this query:
select *
from SYS.X$KSPPI
where substr(KSPPINM,1,1) = '_';
The following query displays parameter names with their
current value:
select a.ksppinm "Parameter", b.ksppstvl "Session
Value", c.ksppstvl "Instance Value"
from x$ksppi a, x$ksppcv b, x$ksppsv c
where a.indx = b.indx and a.indx = c.indx
and substr(ksppinm,1,1)='_'
order by a.ksppinm;
Remember: Thou shall not play with undocumented
parameters!
What is a database EVENT and how does one set it? (for
DBA)
Oracle trace events are useful for debugging the Oracle
database server. The following two examples are simply
to demonstrate syntax. Refer to later notes on this page
for an explanation of what these particular events do.
Either adding them to the INIT.ORA parameter file can
activate events. E.g.
event='1401 trace name errorstack, level 12'
... or, by issuing an ALTER SESSION SET EVENTS command:
E.g.
alter session set events '10046 trace name context
forever, level 4';
The alter session method only affects the user's current
session, whereas changes to the INIT.ORA file will
affect all sessions once the database has been
restarted.
What is a Rollback segment entry ?
It is the set of before image data blocks that contain
rows that are modified by a transaction. Each Rollback
Segment entry must be completed within one rollback
segment. A single rollback segment can have multiple
rollback segment entries.
What database events can be set? (for DBA)
The following events are frequently used by DBAs and
Oracle Support to diagnose problems:
" 10046 trace name context forever, level 4 Trace SQL
statements and show bind variables in trace output.
" 10046 trace name context forever, level 8 This shows
wait events in the SQL trace files
" 10046 trace name context forever, level 12 This shows
both bind variable names and wait events in the SQL
trace files
" 1401 trace name errorstack, level 12 1401 trace name
errorstack, level 4 1401 trace name processstate Dumps
out trace information if an ORA-1401 "inserted value too
large for column" error occurs. The 1401 can be replaced
by any other Oracle Server error code that you want to
trace.
" 60 trace name errorstack level 10 Show where in the
code Oracle gets a deadlock (ORA-60), and may help to
diagnose the problem.
The following lists of events are examples only. They
might be version specific, so please call Oracle before
using them:
" 10210 trace name context forever, level 10 10211 trace
name context forever, level 10 10231 trace name context
forever, level 10 These events prevent database block
corruptions
" 10049 trace name context forever, level 2 Memory
protect cursor
" 10210 trace name context forever, level 2 Data block
check
" 10211 trace name context forever, level 2 Index block
check
" 10235 trace name context forever, level 1 Memory heap
check
" 10262 trace name context forever, level 300 Allow 300
bytes memory leak for connections
Note: You can use the Unix oerr command to get the
description of an event. On Unix, you can type "oerr ora
10053" from the command prompt to get event details.
How can one dump internal database structures? (for DBA)
The following (mostly undocumented) commands can be used
to obtain information about internal database
structures.
o Dump control file contents
alter session set events 'immediate trace name CONTROLF
level 10'
/
o Dump file headers
alter session set events 'immediate trace name FILE_HDRS
level 10'
/
o Dump redo log headers
alter session set events 'immediate trace name REDOHDR
level 10'
/
o Dump the system state
NOTE: Take 3 successive SYSTEMSTATE dumps, with
10-minute intervals alter session set events 'immediate
trace name SYSTEMSTATE level 10'
/
o Dump the process state
alter session set events 'immediate trace name
PROCESSSTATE level 10'
/
o Dump Library Cache details
alter session set events 'immediate trace name library
cache level 10'
/
o Dump optimizer statistics whenever a SQL statement is
parsed (hint: change statement or flush pool) alter
session set events '10053 trace name context forever,
level 1'
/
o Dump a database block (File/ Block must be converted
to DBA address) Convert file and block number to a DBA
(database block address).
Eg: variable x varchar2;
exec :x := dbms_utility.make_data_block_address(1,12);
print x
alter session set events 'immediate trace name blockdump
level 50360894'
/
What are the different kind of export backups?
Full back - Complete database
Incremental - Only affected tables from last incremental
date/full backup date.
Cumulative backup - Only affected table from the last
cumulative date/full backup date.
How free extents are managed in Ver 6.0 and Ver 7.0 ?
Free extents cannot be merged together in Ver 6.0.
Free extents are periodically coalesces with the
neighboring free extent in Ver 7.0
What is the use of RECORD option in EXP command?
For Incremental exports, the flag indirects whether
a record will be stores data dictionary tables recording
the export.
Page Numbers :
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34 35
Have a Question ?
post your questions here. It
will be answered as soon as possible.
Check
Job Interview Questions
for more Interview Questions with Answers
|
