1. What
is an Oracle Instance?
An oracle instance is a means to access an oracle database .
Consists of the SGA memory structures and the background processes that are
used to manage a database .
2. What
information is stored in Control File?
A
control file contains :
=>Database name and
identifier
=>Timestamp of database creation
=>Tablespace names
=>Names and locations of datafiles and online redolog files
=>Current online redolog file sequence number
=>Checkpoint information
=>Begin and end of undo segments
=>Redolog archive information
=>Backup information
3. When
you start an Oracle DB which file is accessed first?
Parameter
File
4. What
is the Job of SMON, PMON processes?
SMON
1.Instance Recovery(Rolls forward changes in online redolog files,
opens the database for user access
and rolls back uncommitted transactions)
2.Coalesce free space
3.Deallocates Temporary segments.
PMON(Cleans
up after failed processes by:)
1.Rolling back the transaction
2.Releasing locks
3.Releasing other resources
4.Restarting dead dispatchers.
5. What
is Instance Recovery?
When an Oracle instance fails,Oracle performs an instance recovery
when the associated database is re-started.
Instance recovery occurs in two steps:
Cache recovery: Changes being made to a database are recorded in
the database buffer cache. These changes are also recorded in
online redo log
files simultaneously. When there are enough data in the database
buffer cache,
they are written to data files. If an Oracle instance fails before
the data in
the database buffer cache are written to data files, Oracle uses
the data
recorded in the online redo log files to recover the lost data when
the
associated database is re-started. This process is called cache
recovery.
Transaction recovery: When a transaction modifies data in a
database, the before image of the modified data is stored in an
undo segment.
The data stored in the undo segment is used to restore the original
values in
case a transaction is rolled back. At the time of an instance
failure, the
database may have uncommitted transactions. It is possible that
changes made by
these uncommitted transactions have gotten saved in data files. To
maintain
read consistency, Oracle rolls back all uncommitted transactions
when the
associated database is re-started. Oracle uses the undo data stored
in undo
segments to accomplish this. This process is called transaction
recovery.
6. What
is written in Redo Log Files?
Online redolog files provides a means to redo transactions in the
event of a database failure . Every transaction is synchronously written to
the redolog buffer ,then gets flushed to the online redolog files in order to
provide a recovery mechanism in case of media failure. This includes
transactions that have not yet been committed ,undo segment information ,
schema and object management statements. Online redolog files are used in
situation such as instance failure to recover committed data that has not yet
been written to data files .The online redolog files are used only for
recovery .
7. How
do you control number of Datafiles one can have in an Oracle database?
8. How
many Maximum Datafiles can there be in an Oracle Database?
9. What
is a Tablespace?
A tablespace is a logical storage unit in Oracle Database which
collectively stores all of the database’s data. Each tablespace in an oracle
database consists of one or more files called datafiles .A tablespace can
belong to only one database at a time .A tablespace is further divided into
logical units of storage.
10. What
is the purpose of Redo Log files?
Online
redolog files provides a means to redo transactions in the event of a database
failure . Every transaction is synchronously written to the redolog buffer
,then gets flushed to the online redolog files in order to provide a recovery
mechanism in case of media failure. This includes transactions that have not
yet been committed ,undo segment information , schema and object management
statements. Online redolog files are used in situation such as instance
failure to recover committed data that has not yet been written to data files
.The online redolog files are used only for recovery .
11.
Which default Database roles are created when you create a Database?
Sysdba
and sysoper
12. What
is a Checkpoint?
A
checkpoint is an operation that Oracle performs to ensure data file
consistency. When a checkpoint occurs, Oracle ensures all modified buffers are
written from the data buffer to disk files. Frequent checkpoints decrease the
time necessary for recovery should the database crash, but may decrease
overall database performance.
A
checkpoint performs the following three operations:
1.)
Every dirty block in the buffer cache is written to the data files. That is,
it synchronizes the datablocks in the buffer cache with the datafiles on disk.
It's the DBWR that writes all modified databaseblocks back to the
datafiles.
2.) The
latest SCN is written (updated) into the datafile header.
3.) The
latest SCN is also written to the controlfiles.
The
checkpoint process (CKPT) is responsible for writing checkpoints to the data
file headers and control file. Checkpoints occur in a variety of situations.
For example, Oracle Database uses the following types of checkpoints:
1.)
Thread checkpoints : The database writes to disk all
buffers modified by redo in a specific thread before a certain target. The set
of thread checkpoints on all instances in a database is a database checkpoint.
Thread checkpoints occur in the following situations:
Consistent database shutdown .
ALTER SYSTEM CHECKPOINT statement .
Online redo log switch .
ALTER DATABASE BEGIN BACKUP statement
2.)
Tablespace and data file checkpoints : The database
writes to disk all buffers modified by redo before a specific target. A
tablespace checkpoint is a set of data file checkpoints, one for each data
file in the tablespace. These checkpoints occur in a variety of situations,
including making a tablespace read-only or taking it offline normal, shrinking
a data file, or executing ALTER TABLESPACE BEGIN BACKUP.
3.)
Incremental checkpoints : An incremental
checkpoint is a type of thread checkpoint partly intended to avoid writing
large numbers of blocks at online redo log switches. DBWn checks at least
every three seconds to determine whether it has work to do. When DBWn writes
dirty buffers, it advances the checkpoint position, causing CKPT to write the
checkpoint position to the control file, but not to the data file headers.
Other
types of checkpoints include instance and media recovery checkpoints and
checkpoints when schema objects are dropped or truncated.
Importance
of Checkpoints for Instance Recovery :
Instance
recovery uses checkpoints to determine which changes must be applied to the
data files. The checkpoint position guarantees that every committed change
with an SCN lower than the checkpoint SCN is saved to the data files.
Checkpoint
Position in Online Redo Log File
During
instance recovery, the database must apply the changes that occur between the
checkpoint position and the end of the redo thread. As shown in Figure, some
changes may already have been written to the data files. However, only changes
with SCNs lower than the checkpoint position are guaranteed to be on disk.
Time and
SCN of last checkpoint :
The date
and time of the last checkpoint can be retrieved through checkpoint_time in
v$datafile_header view
The SCN
of the last checkpoint can be found in v$database.
13.
Which Process reads data from Datafiles?
Server
process
14.
Which Process writes data in Datafiles?
Database
writer
15. Can
you make a Datafile auto extendible. If yes, how?
16. What
is a Shared Pool?
17. What
is kept in the Database Buffer Cache?
Database Buffer cache
is one of the most important components of System Global Area (SGA). Database
Buffer Cache is the place where data blocks are copied from datafiles to
perform SQL operations. Buffer Cache is shared memory structure and it is
concurrently accessed by all server processes.
18. How
many maximum Redo Logfiles one can have in a Database?
19. What
is difference between PFile and SPFile?
20. What is PGA_AGGREGRATE_TARGET parameter?
You can
automatically and globally manage the size of SQL work areas. The database
administrator simply needs to specify the total size dedicated to PGA memory
for the Oracle instance by setting the initialization parameter
PGA_AGGREGATE_TARGET. The specified number (for example, 2G) is a global
target for the Oracle instance, and Oracle tries to ensure that the total
amount of PGA memory allocated across all database server processes never
exceeds this target. With PGA_AGGREGATE_TARGET, sizing of work areas for all
dedicated sessions is automatic. At any given time, the total amount of PGA
memory available to active work areas on the instance is automatically derived
from the parameter PGA_AGGREGATE_TARGET. This amount is set to the value of
PGA_AGGREGATE_TARGET minus the PGA memory allocated by other components of the
system (for example, PGA memory allocated by sessions). The resulting PGA
memory is then allotted to individual active work areas based on their
specific memory requirement. There are fixed views and columns that provide
PGA memory use statistics. Most of these statistics are enabled when
PGA_AGGREGATE_TARGET is set.
•
Statistics on allocation and use of work area memory can be viewed in the
following dynamic performance views:
V$SYSSTAT
V$SESSTAT
V$PGASTAT
V$SQL_WORKAREA
V$SQL_WORKAREA_ACTIVE
• The
following three columns in the V$PROCESS view report the PGA memory allocated
and used by an Oracle process:
PGA_USED_MEM
PGA_ALLOCATED_MEM
PGA_MAX_MEM
21.
Large Pool is used for what?
The
database administrator can configure an optional memory area called the large
pool to provide large memory allocations for:
• Session memory for the shared server
and the Oracle XA interface (used where transactions interact with more than
one database)
• I/O server processes
• Oracle backup and restore operations
By
allocating session memory from the large pool for shared server, Oracle XA, or
parallel query buffers, Oracle can use the shared pool primarily for caching
shared SQL and avoid the performance overhead caused by shrinking the shared
SQL cache.
In
addition, the memory for Oracle backup and restore operations, for I/O server
processes, and for parallel buffers is allocated in buffers of a few hundred
kilobytes. The large pool is better able to satisfy such large memory requests
than the shared pool.
The
large pool does not have an LRU list. It is different from reserved space in
the shared pool, which uses the same LRU list as other memory allocated from
the shared pool.
22. What
is PCT Increase setting?
Specify
the percent by which the third and subsequent extents grow over the preceding
extent. The default value is 50, meaning that each subsequent extent is 50%
larger than the preceding extent. The minimum value is 0, meaning all extents
after the first are the same size. The maximum value depends on your operating
system. Oracle rounds the calculated size of each new extent to the nearest
multiple of the data block size. If you change the value of the PCTINCREASE
parameter (that is, if you specify it in an ALTER statement), then Oracle
calculates the size of the next extent using this new value and the size of
the most recently allocated extent. If you want to keep all extents the same
size, you can prevent the SMON background process from coalescing extents by
setting the value of PCTINCREASE to 0. In general, Oracle recommends a setting
of 0 as a way to minimize fragmentation and avoid the possibility of very
large temporary segments during processing. You cannot specify PCTINCREASE for
rollback segments. Rollback segments always have a PCTINCREASE value of 0.
23. What
is PCTFREE and PCTUSED Setting?
The
PCTFREE parameter sets the minimum percentage of a data block to be reserved
as free space for possible updates to rows that already exist in that block.
The
PCTUSED parameter sets the minimum percentage of a block that can be used for
row data plus overhead before new rows are added to the block. After a data
block is filled to the limit determined by PCTFREE, Oracle considers the block
unavailable for the insertion of new rows until the percentage of that block
falls beneath the parameter PCTUSED. Until this value is achieved, Oracle uses
the free space of the data block only for updates to rows already contained in
the data block.
24. What
is Row Migration and Row Chaining?
25. What
is 01555 - Snapshot Too Old error and how do you avoid it?
You get
this error when you get a snapshot too old within rollback. It can usually be
solved by increasing the undo retention or increasing the size of rollbacks.
You should also look at the logic involved in the application getting the
error message.
26. What
is a Locally Managed Tablespace?
27. Can
you audit SELECT statements?
28. What
does DBMS_FGA package do?
The
DBMS_FGA package provides fine-grained security functions. Execute privilege
on DBMS_FGA is needed for administering audit policies. Because the audit
function can potentially capture all user environment and application context
values, policy administration should be executable by privileged users only.
This package is available for only cost-based optimization. The rule-based
optimizer may generate unnecessary audit records since audit monitoring can
occur before row filtering. For both the rule-based optimizer and the
cost-based optimizer, you can refer to DBA_FGA_AUDIT_TRAIL to analyze the SQL
text and corresponding bind variables that are issued.
29. What
is Cost Based Optimization?
30. How
often you should collect statistics for a table?
31. How
do you collect statistics for a table, schema and Database?
32. Can
you make collection of Statistics for tables automatic?
33. On
which columns you should create Indexes?
34. What
type of Indexes are available in Oracle?
35. What
is B-Tree Index?
36. A
table is having few rows, should you create indexes on this table?
37. A
Column is having many repeated values which type of index you should create on
this column, if you have to?
38. When
should you rebuilt indexes?
39. Can
you built indexes online?
40. Can
you see Execution Plan of a statement.
41. A
table is created with the following setting
storage (initial 200k
next 200k
minextents 2
maxextents 100
pctincrease 40)
What will be size of 4th extent?
42. What
is DB Buffer Cache Advisor?
43. What
is STATSPACK tool?
44. Can
you change SHARED_POOL_SIZE online?
45. Can
you Redefine a table Online?
46. Can
you assign Priority to users?
47. You
want users to change their passwords every 2 months. How do you enforce this?
48. How
do you delete duplicate rows in a table?
49. What
is Automatic Management of Segment Space setting?
50. What
is the difference between DELETE and TRUNCATE statements?
51. What
is COMPRESS and CONSISTENT setting in EXPORT utility?
52. What
is the difference between Direct Path and Convention Path loading?
53. Can
you disable and enable Primary key?
54. What
is an Index Organized Table?
55. What
is a Global Index and Local Index?
56. What
is the difference between Range Partitioning and Hash Partitioning?
57. What
is difference between Multithreaded/Shared Server and Dedicated Server?
58. Can
you import objects from Oracle ver. 7.3 to 9i?
59. How
do you move tables from one tablespace to another tablespace?
60. How
do see how much space is used and free in a tablespace?