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.
2) 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.
3) 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.
4) 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.
5) 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).
6) 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.
7) 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.
8) 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.
9) 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.
10) 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.
11) 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.
12) What does database do during the mounting process?
While mounting the database oracle reads the data from control file which is used for verifying physical database files during sanity check. Background processes are started before mounting the database only.
13) What are log file 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 redos 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 redo log group contains no redo records belonging to a dirty buffer it is in an "INACTIVE" state. These inactive redo log 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.
14) 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;
15) How to check Oracle database version?
SQL> Select * from v$version;I think this is a great advancement in the way we use exp. I found this very helpful.
