Explore topic-wise InterviewSolutions in .

• 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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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).

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).

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.

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.

The CONNECTION WOULD have been shared server connection in which CASE ORACLE will not allow to shutdown/startup the database.

The connection would have been shared server connection in which case oracle will not allow to shutdown/startup the database.

DBWR – 20, SMON – 16 .

DBWR – 20, SMON – 16 .

• DISPATCHERS
• MAX_DISPATCHERS
• SHARED_SERVER_PROCESSES
• MAX_SHARED_SERVER_PROCESSES.

We NEED to CHECK if SERVER=SHARED is MENTIONED in client TNS entry or not.

We need to check if SERVER=SHARED is mentioned in client TNS entry or not.

When we observed ORA-04030 or ORA-04031 ERRORS very FREQUENTLY in ALERT log file.

When we observed ORA-04030 or ORA-04031 errors very frequently in alert log file.

It is a SERVICE which SERVER MULTIPLE USER REQUESTS.

It is a service which server multiple user requests.

As one DISK will have only one I/O header, it will be BURDEN to the database if we PLACE all the FILES in single hard disk. So oracle recommends OFA.

As one disk will have only one I/O header, it will be burden to the database if we place all the files in single hard disk. So oracle recommends OFA.

It is a RULE which says DATABASE related files should split ACROSS multiple disks and YES i will recommend to IMPLEMENT it.

It is a rule which says database related files should split across multiple disks and yes i will recommend to implement it.

SMON (here SYSTEM MEANS DATABASE).

SMON (here system means database).

It is in a SINGLE LOCATION DEFINED by DIAG_DEST PARAMETER.

It is in a single location defined by DIAG_DEST parameter.

By INCREASING the SIZE SPECIFIED by SERVER_RESULT_CACHE or by increasing MEMORY_TARGET parameter if AMM is used.

By increasing the size specified by SERVER_RESULT_CACHE or by increasing MEMORY_TARGET parameter if AMM is used.

SERVER_RESULT_CACHE.

SERVER_RESULT_CACHE.

It is a NEW COMPONENT introduced in 11g. It will store the rows of a QUERY DIRECTLY this avoids the need of EXECUTING a statement.

It is a new component introduced in 11g. It will store the rows of a query directly this avoids the need of executing a statement.

MIGHT be there is a TRANSACTION which is LARGE and ROLLBACK is HAPPENING for the same.

Might be there is a transaction which is large and rollback is happening for the same.

We can open ANOTHER terminal and issue shut ABORT. Then once again startup and do shut IMMEDIATE.

We can open another terminal and issue shut abort. Then once again startup and do shut immediate.

Datafiles and redolog FILES are required to open the database and ORACLE will get that INFORMATION from controlffiles.

Datafiles and redolog files are required to open the database and oracle will get that information from controlffiles.

Control FILE is REQUIRED and it is for maintenance of some DATABASE ACTIONS.

Control file is required and it is for maintenance of some database actions.

PFILE or SPFILE is required to bring database to NOMOUNT state and instance will be STARTED (backgroung PROCESSES will be started and memory will be allocated to SGA from RAM) in this phase.

Pfile or spfile is required to bring database to NOMOUNT state and instance will be started (backgroung processes will be started and memory will be allocated to SGA from RAM) in this phase.

PSP0, PROCESS spawner. This background process will START other PROCESSES like PMON, SMON ETC. But in alert log first it will show PMON.

PSP0, process spawner. This background process will start other processes like PMON, SMON etc. But in alert log first it will show PMON.

All the BACKGROUND PROCESSES are RESPONSIBLE.

All the background processes are responsible.

Alert LOG itself cannot provide complete information about the error, in which CASE it will GENERATE trace file. Depends on the error, it will generate BACKGROUND, CORE or user trace files.

Alert log itself cannot provide complete information about the error, in which case it will generate trace file. Depends on the error, it will generate background, core or user trace files.

No EFFECT on the database functionality. ORACLE will create a NEW ALERT log FILE.

No effect on the database functionality. Oracle will create a new alert log file.

Database STARTUP and shutdown times, non-default parameters, any logical or physical structural changes and alloracle ERRORS etc.

Database startup and shutdown times, non-default parameters, any logical or physical structural changes and alloracle errors etc.

It is FILE which HELPS in diagnosing all the errors OCCURRED in the DATABASE.

It is file which helps in diagnosing all the errors occurred in the database.

SGA size is not DEPENDANT on database size, it depends on how many transactions HAPPENING in the database.

SGA size is not dependant on database size, it depends on how many transactions happening in the database.

Managing both SGA and PGA AUTOMATICALLY by oracle is called Automatic Memory MANAGEMENT (AMM).

Managing both SGA and PGA automatically by oracle is called Automatic Memory Management (AMM).

SGA_TARGET and VALUE is DEPENDENT on no of TRANSACTIONS in the DATABASE.

SGA_TARGET and value is dependent on no of transactions in the database.

Kernel PARAMETER SHMMAX MAY be reached. We should increase that FIRST.

Kernel parameter SHMMAX may be reached. We should increase that first.

Values of INDIVIDUAL PARAMETERS will ACT as minimum and ASMM as MAXIMUM.

Values of individual parameters will act as minimum and ASMM as maximum.

Setting SGA_TARGET parameter is called ASMM. This HELPS in managing all SGA components AUTOMATICALLY and MMAN is the BACKGROUND process helps in that.

Setting SGA_TARGET parameter is called ASMM. This helps in managing all SGA components automatically and MMAN is the background process helps in that.

To SPECIFY how many users with sysdba role can connect to the DATABASE REMOTELY.

To specify how many users with sysdba role can connect to the database remotely.

REMOTE_LOGIN_PASSWORD is not SET to EXCLUSIVE.

REMOTE_LOGIN_PASSWORD is not set to exclusive.

It is used to AUTHENTICATE any USER is CONNECTING as SYSDBA from a REMOTE machine.

It is used to authenticate any user is connecting as SYSDBA from a remote machine.

Hidden parameters are to be used only on recommendation of oracle support and SOMETIMES they help us in providing WORK around to any SERIOUS problem in the database.

Hidden parameters are to be used only on recommendation of oracle support and sometimes they help us in providing work around to any serious problem in the database.

Spfile.

Spfile.

We can RECOVER from ALERT LOG file which contains non-default PARAMETERS.

We can recover from alert log file which contains non-default parameters.

Ifile is a index FILE which can HELP database when pfile or SPFILE are not in DEFAULT location.

Ifile is a index file which can help database when pfile or spfile are not in default location.

Spfile is a BINARY file and it helps in changing PARAMETERS efficiently than a PFILE.

Spfile is a binary file and it helps in changing parameters efficiently than a pfile.

It contains LATEST SCN, all LOCATIONS and sizes of datafiles and redolog files, database creation date and timestamp and CONTROLFILE PARAMETERS.

It contains latest SCN, all locations and sizes of datafiles and redolog files, database creation date and timestamp and controlfile parameters.