Explain the difference between a hot backup and a cold backup.
A cold backup is done when there is no
user activity going on with the system. Also called as offline backup, is taken
when the database is not running and no users are logged in. all files of the
database are copied and no changes during the copy are made.
A hot backup is taken when the database needs to run all the time. It is
an online backup. All files of the database are copied and there may be changes
to the database during the copy.
How
many memory layers are in the oracle shared pool? Explain them
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.
What
is the cache hit ratio, what impact does it have on performance of an Oracle
database and what is involved in tuning it?
When the cache client like a CPU, web
browser etc finds a successful entry in the cache memory, it is called as a cache
hit. The percentage of these successful cache hits is called as cache hit
ratio. Higher the cache hit ratio better will be the performance because data
read from the cache is faster than the memory.
Explain
the concept of the DUAL table.
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
What
are the ways tablespaces can be managed and how do
they differ?
Objects can be assigned to a table
space. The related objects can be then grouped together. Table space can also
be managed using extents. Extents consist of a specific
number of contiguous data blocks. For the required extent, the free extent
closest in size is determined.
Explain
what are Oracle Catalog and Oracle Archive log?
Oracle catalog contains tables and
views to get information about the database. It helps user to understand the
available tables, attributes, constraints etc.
Oracle Archive log mode of a database
in Oracle, ensures, the online redo logs are not overwritten before they are
archived. This ensures that recovery is possible.
What
are PCT Free and PCT Used? What is PCT increase
parameter in segment?
PCTFREE is a parameter used to find how
much space should be left in a database block for future updates. This means
that if the PCTFREE = 20, new rows will be added in the block until it is 80%
full.
PCTUSED is a parameter helps Oracle to
find when it should consider a database block to be empty enough to be added to
the freelist. This means that if the PCTFREE = 50, new rows will be not be
added in the block until sufficient rows are deleted from the block so that it
falls below 40% empty.
PCTINCREASE parameter is used to find
how much will the each subsequent segment will grow. This value is in %.
What
is dump destination? What are bdump, cdump and udump?
Trace files for Oracle processes are stored
in dump destination.
Bdump- Oracle writes to the trace log
and creates trace files for background processes in background dump
destination. If this directory becomes full and more files cannot be written,
debugging becomes difficult.
Cdump- Oracle writes core files and
background processes in Core dump destination directory. If this directory
becomes full and more files cannot be written, debugging becomes difficult.
Udump – Oracle creates trace files for
the user processes in the User Dump directory if this directory becomes full
and more files cannot be written, debugging becomes difficult.
How
do you increase the performance of %LIKE operator?
LIKE% works the fastest because it uses
the index to search on the column provided an index is specified on the column.
Using % after LIKE, results in faster results.
Why
use materialized view instead of a table
Materialized views are basically used
to increase query performance since it contains results of a query. They should
be used for reporting instead of a table for a faster execution.
Why
and how the deadlock situation arises
A deadlock situation arises when two or
more users wait for the same resource locked by one anther or two or more
processes wait to update rows which are locked by other processes. Oracle if
detects a deadlock, rolls back the session chosen by the deadlock victim.
What
are standby databases? Difference between Physical and logical standby
databases
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.
What
is Cache Fusion Technology?
In Cache fusion, multiple buffers join
to act as one. It eliminates disk i/o operaions by making use of a scalable
shared cache. It treats multiple buffer caches as one thereby resolving data
consistency issues. Cash fusion technology can provide more resources and
increases concurrency of users.
What
is the difference between Cloning and Standby databases?
The clone database is a copy of the
database which can be opened in read write mode. It is treated as a separate
copy of the database that is functionally completely separate. The standby
database is a copy of the production database used for disaster protection. In
order to update the standby database; archived redo logs from the production
database can be used. If the primary database is destroyed or its data becomes
corrupted, one can perform a failover to the standby database, in which case
the standby database becomes the new primary database.
