>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 are the steps to install oracle on
Linux system? List two kernel parameter that effect oracle installation ?
Initially set up disks and kernel
parameters, then create oracle user and DBA group, and finally run installer to
start the installation process. The SHMMAX & SHMMNI two kernel parameter
required to set before installation process.
> 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 is the difference between data
block/extent/segment ?
A data block is the smallest unit of logical
storage for a database object. As objects grow they take chunks of additional
storage that are composed of 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? Define structure of shared
pool component of SGA ?
The system global area is a group of shared
memory area that is dedicated to 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. You can control the SGA
memory by setting the parameter db_cache_size, shared_pool_size and log_buffer.
Shared pool portion contain three 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,
data buffer cache for parallel execution message and control structure.
Shared pool portion contain three 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,
data buffer cache for parallel execution message and control structure.
> 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.
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
a dirty read occurs.
SCN is incremented whenever a deadlock occurs.
SCN is a value that keeps track of explicit locks.
SCN is a value that is incremented whenever database changes are made.
SCN is incremented whenever a deadlock occurs.
SCN is a value that keeps track of explicit locks.
SCN is a value that is incremented whenever database changes are made.
> 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
LOG_CHECKPOINT_INTERVAL = 1000; # number of OS blocks.
LOG_CHECKPOINT_TIMEOUT = 3600; # Every one hour
LOG_CHECKPOINT_INTERVAL = 1000; # number of OS blocks.
> What happens when we fire SQL statement
in Oracle ?
First it will check the syntax and semantics
in library cache, after that it will create execution plan. If already data is
in buffer cache it will directly return to the client. If not it will fetch the
data from datafiles and write to the database buffer cache after that it will
send server and finally server send to the client.
> What is the use of large pool, which
case you need to set the large pool ?
You need to set large pool if you are using:
MTS (Multi thread server) and RMAN Backups. Large pool prevents RMAN & MTS
from competing with other sub system 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.
> 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. Background processes are started
before mounting the database only.
> What are logfile states ?
"CURRENT" state means that redo
records are currently being written to that group. It will be until a log
switch occurs. At a time there can be only one redo group current.
If a redo group containing redo's of a dirty buffer that redo group is said to be 'ACTIVE' state. As we know log file keep changes made to the data blocks then data blocks are modified in buffer cache (dirty blocks). These dirty blocks must be written to the disk (RAM to permanent media).
And when a redolog group contains no redo records belonging to a dirty buffer it is in an "INACTIVE" state. These inactive redolog can be overwritten.
One more state 'UNUSED' initially when you create new redo log group its log file is empty on that time it is unused. Later it can be any of the above mentioned state.
If a redo group containing redo's of a dirty buffer that redo group is said to be 'ACTIVE' state. As we know log file keep changes made to the data blocks then data blocks are modified in buffer cache (dirty blocks). These dirty blocks must be written to the disk (RAM to permanent media).
And when a redolog group contains no redo records belonging to a dirty buffer it is in an "INACTIVE" state. These inactive redolog can be overwritten.
One more state 'UNUSED' initially when you create new redo log group its log file is empty on that time it is unused. Later it can be any of the above mentioned state.
> 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.
Sometimes you can force the log switch.
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
> How to check Oracle database version ?
SQL> Select * from v$version;
Do you like this post? Please share this article.
HTML Link Code:
Post a Comment