1. What
are the components of Physical database structure of Oracle Database?.
ORACLE database is comprised of three types of
files. One or more Data files, two are more Redo Log files, and one or more
Control files.
2. What
are the components of Logical database structure of ORACLE database?
Tablespaces
and the Database's Schema Objects.
3. What
is a Tablespace?
A
database is divided into Logical Storage Unit called tablespaces. A tablespace
is used to grouped related logical structures together.
4. What
is SYSTEM tablespace and When is it Created?
Every
ORACLE database contains a tablespace named SYSTEM, which is automatically
created when the database is created. The SYSTEM tablespace always contains the
data dictionary tables for the entire database.
5.
Explain the relationship among Database, Tablespace and Data file.
Each
databases logically divided into one or more tablespaces One or more data files
are explicitly created for each tablespace.
6. What
is schema?
A
schema is collection of database objects of a User.
7. What
are Schema Objects ?
Schema
objects are the logical structures that directly refer to the database's data. Schema
objects include tables, views, sequences, synonyms, indexes, clusters, database
triggers, procedures, functions packages and database links.
8. Can
objects of the same Schema reside in different tablespaces.?
Yes.
9. Can a
Tablespace hold objects from different Schemes ?
Yes.
10. What
is Database Link ?
A
database link is a named object that describes a "path" from one
database to another.
11. What are the types
of Database Links ?
Private
Database Link, Public Database Link & Network Database Link.
12. What is Private
Database Link ?
Private
database link is created on behalf of a specific user. A private database link can
be used only when the owner of the link specifies a global object name in a SQL
statement or in the definition of the owner's views or procedures.
13. What is Public
Database Link ?
Public
database link is created for the special user group PUBLIC. A public database link
can be used when any user in the associated database specifies a global object name
in a SQL statement or object definition.
14. What is Network
Database link ?
Network
database link is created and managed by a network domain service. A network
database link can be used when any user of any database in the network specifies
a global object name in a SQL statement or object definition.
15. What is Data Block ?
ORACLE
database's data is stored in data blocks. One data block corresponds to a specific
number of bytes of physical database space on disk.
16. How to define Data
Block size ?
A
data block size is specified for each ORACLE database when the database is created.
A database users and allocated free database space in ORACLE datablocks. Block
size is specified in INIT.ORA file and cann't be changed latter.
17. What is Row Chaining
?
In
Circumstances, all of the data for a row in a table may not be able to fit in
the same data block. When this occurs , the data for the row is stored in a
chain of data block (one or more) reserved for that segment.
18. What is an Extent ?
An
Extent is a specific number of contiguous data blocks, obtained in a single
allocation, used to store a specific type of information.
19. What is a Segment ?
A
segment is a set of extents allocated for a certain logical structure.
20. What are the
different type of Segments ?
Data Segment, Index Segment,
Rollback/undo Segment and Temporary Segment.
21. What is a Data
Segment ?
Data segment are the physical
areas within a database block in which the data associated with tables and
clusters are stored.
Each Non-clustered table has
a data segment. All of the table's data is stored in the extents of its data
segment. Each cluster has a data segment. The data of every table in the
cluster is stored in the cluster's data segment.
22. What is an Index
Segment ?
Each Index has an Index
segment that stores all of its data.
23. What is Rollback
Segment ?
A Database contains one or
more Rollback Segments to temporarily store "undo" information.
24. What are the uses of
Rollback Segment ?
Rollback Segments are used :
To generate read-consistent database information during database recovery to
rollback uncommitted transactions for users.
25. What is a Temporary
Segment?
Temporary segments are
created by ORACLE when a SQL statement needs a temporary work area to complete
execution. When the statement finishes execution, the temporary segment extents
are released to the system for future use.
26. What is a Data File
?
Every ORACLE database has one
or more physical data files. A database's data files contain all the database
data. The data of logical database structures such as tables and indexes is physically
stored in the data files allocated for a database.
27. What are the
Characteristics of Data Files ?
A data file can be associated
with only one database.Once created a data file can't change size. One or more
data files form a logical unit of database storage called a tablespace.
28. What is a Redo Log ?
Redo logs are logical and
physical structures that are designed to hold all the changes made to a
database and are intended to aid in the recovery of a database.
29. What is the function
of Redo Log ?
The Primary function of the
redo log is to record all changes made to data.
30. What is the use of
Redo Log Information ?
The Information in a redo log
file is used only to recover the database from a system or media failure
prevents database data from being written to a database's data files.
31. What does a Control
file Contain ?
A Control file records the
physical structure of the database. It contains the following information. - Database
Name, Names and locations of a database's files and redolog files. ,Time stamp
of database creation.
32. What is the use of
Control File ?
When an instance of an ORACLE
database is started, its control file is used to identify the database and redo
log files that must be opened for database operation to proceed. It is also used
in database recovery.
33. What is a Data
Dictionary ?
The data dictionary of an
ORACLE database is a set of tables and views that are used as a read-only
reference about the database. It stores information about both the logical and physical
structure of the database, the valid users of an ORACLE database, integrity
constraints defined for
tables in the database and space allocated for a schema object and how much of
it is being used.
34. What constitute an
ORACLE Instance ?
SGA and ORACLE background
processes constitute an ORACLE instance. (or) Combination of memory structure
and background process.
35. What is SGA ?
The System Global Area in a
Oracle database is the area in memory to facilitates the transfer of
information between users. It holds the most recently requested structural
information between users. The System Global Area (SGA) is a
shared memory region allocated by ORACLE that contains data and control
information for one ORACLE instance.
36. What are the
components of SGA ?
Database buffers, Dictionary
cache, Redo Log Buffer the Shared Pool and Cursors.
37. What do Database
Buffers contain ?
Database buffers store the
most recently used blocks of database data. It can also contain modified data
that has not yet been permanently written to disk.
38. What do Redo Log
Buffers contain ?
Redo Log Buffer stores redo
entries a log of changes made to the database.
39. What is Shared Pool
?
Shared Pool is a portion of
the SGA that contains shared memory constructs such as shared SQL areas. This
will allow sharing of parsed SQL statements among concurrent users.
40. What is Shared SQL
Area ?
A Shared SQL area is required
to process every unique SQL statement submitted to a database and contains information
such as the parse tree and execution plan for the corresponding statement.
41. What is Cursor ?
A Cursor is a handle ( a name
or pointer) for the memory associated with a specific statement.
42. What is PGA ?
Program Global Area (PGA) is
a memory buffer that contains data and control information for a server
process.
That is used by a Single
Oracle User Process.
43. What is User Process
?
A user process is created and
maintained to execute the software code of an application program. It is a
shadow process created automatically to facilitate communication between the
user and the server process.
44. What is Server
Process ?
Server Process handle
requests from connected user process. A server process is in charge of
communicating with the user process and interacting with ORACLE carry out requests
of the associated user process.
45. What are the two
types of Server Configurations ?
Dedicated Server
Configuration and Multi-threaded Server Configuration.
46. What is Dedicated
Server Configuration ?
In a Dedicated Server
Configuration a Server Process handles requests for a Single User Process.
47. What is a
Multi-threaded Server Configuration ?
In a Multi-threaded Server
Configuration many user processes share a group of server process.
48. What is a Parallel
Server option in ORACLE ?
A configuration for loosely
coupled systems where multiple instance share a single physical database is
called Parallel Server. (Only In Multi-CPU environments)
49. Name the ORACLE
Background Process ?
DBWR - Database Writer. LGWR
- Log Writer, CKPT - Check Point, SMON - System Monitor, PMON - Process
Monitor, ARCH – Archiver, RECO – Recover, Dnnn - Dispatcher and LCKn – Lock,
Snnn - Server.
50. What Does DBWR do ?
Database writer writes
modified blocks from the database buffer cache to the data files.
51.When Does DBWR write
to the database ?
DBWR writes when more data
needs to be read into the SGA and too few database buffers are free. The least
recently used data is written to the data files first. DBWR also writes when
CheckPoint occurs.
52. What does LGWR do ?
Log Writer (LGWR) writes redo
log entries generated in the redo log buffer of the SGA to on-line Redo Log
File.
53. When does LGWR write
to the database ?
LGWR writes redo log entries
into an on-line redo log file when transactions commit and the log buffer files
are full.
54. What is the function
of checkpoint(CKPT)?
The Checkpoint (CKPT) process
is responsible for signaling DBWR at checkpoints and updating all the data
files and control files of the database.
55. What are the
functions of SMON ?
System Monitor (SMON)
performs instance recovery at instance start-up. In a multiple instance system
(one that uses the Parallel Server), SMON of one instance can also perform
instance recovery for other instance that have failed SMON also cleans up
temporary segments that are
no longer in use and recovers dead transactions skipped during crash and
instance recovery because of file-read or off-line errors. These transactions
are eventually recovered by SMON when the tablespace or file is brought back
on-line SMON also coalesces free extents within the database to make free space
contiguous and easier to allocate.
56. What are functions
of PMON ?
Process Monitor (PMON)
performs process recovery when a user process fails PMON is responsible for
cleaning up the cache and Freeing resources that the process was using PMON
also checks on dispatcher and server processes and restarts them if they
have failed.
57. What is the function
of ARCH ?
Archiver (ARCH) copies the
on-line redo log files to archival storage when they are full. ARCH is active
only when a database's redo log is used in ARCHIVELOG mode.
58. What is function of
RECO ?
RECOver (RECO) is used to
resolve distributed transactions that are pending due to a network or system
failure in a distributed database. At timed intervals,the local RECO attempts
to connect to remote databases and automatically complete the commit or
rollback of the local portion
of any pending distributed transactions.
59. What is the function
of Dispatcher (Dnnn) ?
Dispatcher (Dnnn) process is
responsible for routing requests from connected user processes to available
shared server processes and returning the responses back to the appropriate
user processes.
60. How many Dispatcher
Processes are created ?
Atleast one Dispatcher
process is created for every communication protocol in use.
61. What is the function
of Lock (LCKn) Process ?
Lock (LCKn) are used for
inter-instance locking when the ORACLE Parallel Server option is used.
62. What is the maximum
number of Lock Processes used ?
Though a single LCK process
is sufficient for most Parallel Server systems upto Ten Locks (LCK0,....LCK9)
are used for inter-instance locking.
63. What is the function
of Optimizer ?
The goal of the optimizer is
to choose the most efficient way to execute a SQL statement.
64. What is Execution
Plan ?
The combinations of the steps
the optimizer chooses to execute a statement is called an execution plan.
65. What are the
different approaches used by Optimizer in choosing an execution plan ?
Rule-based and Cost-based.
66. What are the factors
that affect OPTIMIZER in choosing an Optimization approach ?
The OPTIMIZER_MODE initialization
parameter Statistics in the Data Dictionary the OPTIMIZER_GOAL parameter of the
ALTER SESSION command hints in the statement.
67. What are the values
that can be specified for OPTIMIZER MODE Parameter ?
COST and RULE.
68. Will the Optimizer
always use COST-based approach if OPTIMIZER_MODE is set to "Cost'?
Presence of statistics in the
data dictionary for atleast one of the tables accessed by the SQL statements is
necessary for the OPTIMIZER to use COST-based approach. Otherwise OPTIMIZER chooses
RULE-based approach.
69. What is the effect
of setting the value of OPTIMIZER_MODE to 'RULE' ?
This value causes the
optimizer to choose the rule_based approach for all SQL statements issued to
the instance regardless of the presence of statistics.
70. What are the values
that can be specified for OPTIMIZER_GOAL parameter of the ALTER SESSION Command
?
CHOOSE,ALL_ROWS,FIRST_ROWS
and RULE.
71. What is the effect
of setting the value "CHOOSE" for OPTIMIZER_GOAL,parameter of the
ALTER SESSION Command ?
The Optimizer chooses
Cost_based approach and optimizes with the goal of best throughput if statistics
for atleast one of the tables accessed by the SQL statement exist in the data dictionary.
Otherwise the OPTIMIZER chooses RULE_based approach.
72. What is the effect
of setting the value "ALL_ROWS" for OPTIMIZER_GOAL parameter of the
ALTER SESSION command ?
This value causes the
optimizer to the cost-based approach for all SQL statements in the session
regardless of the presence of statistics and to optimize with a goal of best
throughput.
73. What is the effect
of setting the value 'FIRST_ROWS' for OPTIMIZER_GOAL parameter of the ALTER
SESSION command ?
This value causes the
optimizer to use the cost-based approach for all SQL statements in the session
regardless of the presence of statistics and to optimize with a goal of best
response time.
74. What is the effect
of setting the 'RULE' for OPTIMIER_GOAL parameter of the ALTER SESSION Command
?
This value causes the
optimizer to choose the rule-based approach for all SQL statements in a session
regardless of the presence of statistics.
75. What is RULE-based
approach to optimization ?
Choosing an executing
planbased on the access paths available and the ranks of these access paths.
76. What is COST-based
approach to optimization ?
Considering available access
paths and determining the most efficient execution plan based on statistics in
the data dictionary for the tables accessed by the statement and their
associated clusters and indexes.
77. What are Roles ?
Roles are named groups of
related privileges that are granted to users or other roles.
78. What are the use of
Roles ?
REDUCED GRANTING OF PRIVILEGES - Rather
than explicitly granting the same set of privileges to many users a database administrator
can grant the privileges for a group of related users granted to a role and
then grant only the role to each member of the group.
DYNAMIC PRIVILEGE MANAGEMENT - When the
privileges of a group must change, only the privileges of the role need to be
modified. The security domains of all users granted the group's role automatically
reflect the changes made to the role.
SELECTIVE AVAILABILITY OF PRIVILEGES - The roles
granted to a user can be selectively enable (available for use) or disabled (not
available for use). This
allows specific control of a user's privileges in any given situation.
APPLICATION AWARENESS - A
database application can be designed to automatically enable and disable
selective roles when a user attempts to use the application.
79. How to prevent
unauthorized use of privileges granted to a Role ?
By creating a Role with a
password.
80. What is default
tablespace ?
The Tablespace to contain
schema objects created without specifying a tablespace name.
81. What is Tablespace
Quota ?
The collective amount of disk
space available to the objects in a schema on a particular tablespace.
82. What is a profile ?
Each database user is
assigned a Profile that specifies limitations on various system resources
available to the user.
83. What are the system
resources that can be controlled through Profile ?
The number of concurrent
sessions the user can establish the CPU processing time available to the user's
session the CPU processing time available to a single call to ORACLE made by a
SQL statement the amount of logical I/O available to the user's
session the amount of logical
I/O available to a single call to ORACLE made by a SQL statement the allowed
amount of idle time for the user's session the allowed amount of connect time
for the user's session.
84. What is Auditing ?
Monitoring of user access to
aid in the investigation of database use.
The database has the ability to audit all actions that take place
within it.
a) Login attempts, b) Object Accesss, c) Database Action Result of
Greatest(1,NULL) or sLeast(1,NULL) NULL
85. What are the
different Levels of Auditing ?
Statement Auditing, Privilege
Auditing and Object Auditing.
86. What is Statement
Auditing ?
Statement auditing is the
auditing of the powerful system privileges without regard to specifically named
objects.
87. What is Privilege
Auditing ?
Privilege auditing is the
auditing of the use of powerful system privileges without regard to
specifically named objects.
88. What is Object
Auditing ?
Object auditing is the auditing
of accesses to specific schema objects without regard to user.
89. What is Distributed
database ?
A distributed database is a
network of databases managed by multiple database servers that appears to a
user as single logical database. The data of all databases in the distributed
database can be simultaneously accessed and modified.
90. What is Two-Phase
Commit ?
Two-phase commit is mechanism
that guarantees a distributed transaction either commits on all involved nodes
or rolls back on all involved nodes to maintain data consistency across the
global distributed database. It has two phase, a Prepare Phase and a Commit
Phase.
91. Describe two phases
of Two-phase commit ?
Prepare phase - The
global coordinator (initiating node) ask a participants to prepare (to promise
to commit or rollback the transaction, even if there is a failure)
Commit - Phase - If all
participants respond to the coordinator that they are prepared, the coordinator
asks all nodes to commit the transaction, if all participants cannot prepare,
the coordinator asks all nodes to roll back the transaction.
92. What is a SQL * NET?
SQL *NET is ORACLE's
mechanism for interfacing with the communication protocols used by the networks
that facilitate distributed processing and distributed databases. It is used in
Clint-Server and Server-Server communications.
93. What are the steps
involved in Database Startup ?
Start an instance, Mount the
Database and Open the Database.
94. What are the steps
involved in Database Shutdown ?
Close the Database, Dismount
the Database and Shutdown the Instance.
95. What is Restricted
Mode of Instance Startup ?
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.
96. What are the
different modes of mounting a Database with the Parallel Server ?
Exclusive Mode If the first
instance that mounts a database does so in exclusive mode, only that Instance
can mount the database. Parallel Mode If the first instance that mounts a
database is started in parallel mode, other instances that are started in parallel
mode can also mount the database.
97.WhatisOn-lineRedoLog?
The On-line Redo Log is a set
of tow or more on-line redo files that record all committed changes made to the
database. Whenever a transaction is committed, the corresponding redo entries
temporarily stores in redo log buffers of the SGA are written to an on-line
redo log file by the background process LGWR. The on-line redo log files are
used in cyclical fashion.
98. What is Mirrored
on-line Redo Log ?
A mirrored on-line redo log
consists of copies of on-line redo log files physically located on separate
disks, changes made to one member of the group are made to all members.
99. What is Archived
Redo Log ?
Archived Redo Log consists of
Redo Log files that have archived before being reused.
100. What are the advantages
of operating a database in ARCHIVELOG mode over operating it in NO ARCHIVELOG
mode ?
Complete database recovery
from disk failure is possible only in ARCHIVELOG mode. Online database backup
is possible only in ARCHIVELOG mode.
No comments:
Post a Comment