Explore topic-wise InterviewSolutions in .

Logical BACKUP is a process of extracting DATA in the FORM of SQL statements, where it is useful to recover in case the objects are lost. The MAIN drawback of using this backup is that MEAN TIME TO RECOVER is high.

Logical backup is a process of extracting data in the form of SQL statements, where it is useful to recover in case the objects are lost. The main drawback of using this backup is that MEAN TIME TO RECOVER is high.

Yes, by USING the following statement:
ALTER database backup CONTROLFILE to '<location>' or TRACE

Yes, by using the following statement:
alter database backup controlfile to '<location>' or trace

Archive log FILES should not be SAVED in the same disk as datafiles to ENSURE recovery in CASE of media failure. They should be kept on SEPARATE mount points or disk.

Archive log files should not be saved in the same disk as datafiles to ensure recovery in case of media failure. They should be kept on separate mount points or disk.

No. The DATABASE NEEDS to be in the ARCHIVELOG MODE to SUPPORT online backups.

No. The database needs to be in the ARCHIVELOG mode to support online backups.

In this case, partial BACKUP is POSSIBLE; HOWEVER, they are not useful.

In this case, partial backup is possible; however, they are not useful.

In this CASE, you can TAKE only COLD BACKUPS.

In this case, you can take only cold backups.

Backup set is a LOGICAL grouping of backup files that are created when you issue an RMAN backup command. It is RMAN's NAME for a COLLECTION of files associated with a backup. A backup set is composed of ONE or more backup PIECES.

Backup set is a logical grouping of backup files that are created when you issue an RMAN backup command. It is RMAN's name for a collection of files associated with a backup. A backup set is composed of one or more backup pieces.

RMAN backup CONSISTS of a backup of all or part of a database. This results from ISSUING an RMAN backup command. A backup consists of one or more backup SETS.

RMAN backup consists of a backup of all or part of a database. This results from issuing an RMAN backup command. A backup consists of one or more backup sets.

Catalog is optional because RMAN manages BACKUP and recovery operations and it REQUIRES a place to store necessary INFORMATION about the database. RMAN always stores this information in the target database control file. You can also store the RMAN metadata in a recovery catalog schema contained in a separate database. The recovery catalog schema must be STORED in a database other than the target database.

Catalog is optional because RMAN manages backup and recovery operations and it requires a place to store necessary information about the database. RMAN always stores this information in the target database control file. You can also store the RMAN metadata in a recovery catalog schema contained in a separate database. The recovery catalog schema must be stored in a database other than the target database.

YES, RMAN can be USED without RECOVERY CATALOG.

Yes, RMAN can be used without recovery catalog.

Recovery CATALOG is an inventory of the backup TAKEN by RMAN for the DATABASE. It is used to restore a physical backup, reconstruct it, and make it AVAILABLE to the Oracle server.

Recovery catalog is an inventory of the backup taken by RMAN for the database. It is used to restore a physical backup, reconstruct it, and make it available to the Oracle server.

YES, you can TAKE OFFLINE BACKUP with RMAN.

Yes, you can take offline backup with RMAN.

RMAN process uses channel to communicate with I/O DEVICES. You can control the type of I/O DEVICE, PARALLELISM, number of files, and size of files by allocating CHANNELS.

RMAN process uses channel to communicate with I/O devices. You can control the type of I/O device, parallelism, number of files, and size of files by allocating channels.

RMAN uses multiple CHANNELS and does not TAKE backup of free BLOCKS. This is the reason why performance of RMAN backup is BETTER.

RMAN uses multiple channels and does not take backup of free blocks. This is the reason why performance of RMAN backup is better.

Following are the architectural components of RMAN:

1. RMAN EXECUTABLE
2. Server processes
3. Channels
4. Target DATABASE
5. RECOVERY catalog database (optional)
6. Media management layer (optional)
7. BACKUPS, backup sets, and backup pieces

Following are the architectural components of RMAN:

ORACLE database 11G offers bock change tracking FEATURE. It records the information in block change tracking FILE. RMAN uses this information to determine SPECIFIC blocks to be backed up again without re-scanning the entire datafile.

Oracle database 11g offers bock change tracking feature. It records the information in block change tracking file. RMAN uses this information to determine specific blocks to be backed up again without re-scanning the entire datafile.

You can use the FOLLOWING database views to get the information on database size:

•  dba_segments —Gives the information about the used space. You can take a total of all the BYTES in the dba_segments VIEW to get the used space.
•  dba_data__files —Gives the Information on space allocated to datafiles for permanent tablespaces.
•  V$log— Gives the information on redo log files.

You can use the following database views to get the information on database size:

RMAN is an ORACLE SUPPLIED TOOL or utility that can be USED to manage backup and recovery activities.

RMAN is an Oracle supplied tool or utility that can be used to manage backup and recovery activities.

No, FULL BACKUP should be TAKEN before the INCREMENTAL backup.

No, full backup should be taken before the incremental backup.

During the HOT backup, when a TABLESPACE is put up in the backup mode, all the datafiles that belongs to the tablespace get their checkpoint frozen.
When DBWR process writes the changed data BLOCKS to the datafile, the same data blocks are written to redo log files to support roll forward process to MAINTAIN the consistency of a hot backup. This is the reason for large redo generation and requirement of archive logs for hot backup.

During the hot backup, when a tablespace is put up in the backup mode, all the datafiles that belongs to the tablespace get their checkpoint frozen.
When DBWR process writes the changed data blocks to the datafile, the same data blocks are written to redo log files to support roll forward process to maintain the consistency of a hot backup. This is the reason for large redo generation and requirement of archive logs for hot backup.

Hot BACKUP is taken when database is STILL online while cold backup is taken when database is OFFLINE.
Database needs to be in the archive LOG MODE for the hot backup but there is no such requirement for the cold backup.

Hot backup is taken when database is still online while cold backup is taken when database is offline.
Database needs to be in the archive log mode for the hot backup but there is no such requirement for the cold backup.

We cannot BACKUP the online REDO logs; HOWEVER, online redo logs are PROTECTED by multiplexing and optionally by archiving.

We cannot backup the online redo logs; however, online redo logs are protected by multiplexing and optionally by archiving.

You can use either the OPERATING system UTILITIES or the RMAN UTILITY for full backup.
However, Oracle recommends the use of RMAN utility.

You can use either the operating system utilities or the RMAN utility for full backup.
However, Oracle recommends the use of RMAN utility.

The FOLLOWING FILES MUST be backed up:

•  Database files
•  Control files
•  Archived LOG files
•  INIT.ORA
•  PASSWORD files

The following files must be backed up:

The listener or Transparent Network Substrate (TNS) listener is a SERVER process that provides network connectivity to the Oracle database. The listener is configured to LISTEN for CONNECTION requests on a specified port on the database server. When an incoming request is received on the port, the listener attempts to resolve the request and forward the connection information to the appropriate database instance.

The listener or Transparent Network Substrate (TNS) listener is a server process that provides network connectivity to the Oracle database. The listener is configured to listen for connection requests on a specified port on the database server. When an incoming request is received on the port, the listener attempts to resolve the request and forward the connection information to the appropriate database instance.

• In case of a DEDICATED server, a server PROCESS is associated with a single user process and serves it dedicatedly.
• In case of a SHARED server, a single server process can serve multiple user processes. This is achieved with the help of a DISPATCHER process, which places each user process in a single request queue. Server process picks up the user process whenever it is free. After that, the server process puts the result in the INDIVIDUAL response queue associated with different dispatcher processes.

In case of startup mount, the parameter and control files can be READ but the DATAFILES and the log files cannot be ACCESSED because database is not yet open. Generally, this MODE is USED when a datafile recovery is required.

In case of startup mount, the parameter and control files can be read but the datafiles and the log files cannot be accessed because database is not yet open. Generally, this mode is used when a datafile recovery is required.

PMON cleans up the memory after USER PROCESS FAILS.

PMON cleans up the memory after user process fails.

You can use the SHOW PARAMETER command from SQL*PLUS or query V$PARAMETER VIEW to see the value of the PARAMETERS.

You can use the SHOW PARAMETER command from SQL*Plus or query v$PARAMETER view to see the value of the parameters.

SPFILE supports dynamic changes in parameter VALUES. The changes in SPFILE can only be made by using the Oracle statements; therefore, there is no chance of ACCEPTING impossible changes as it will be checked by the system. As a result, chances of HUMAN errors are REDUCED.
Moreover, back up of SPFILE is possible through RMAN.

SPFILE supports dynamic changes in parameter values. The changes in SPFILE can only be made by using the Oracle statements; therefore, there is no chance of accepting impossible changes as it will be checked by the system. As a result, chances of human errors are reduced.
Moreover, back up of SPFILE is possible through RMAN.

The maximum NUMBER of REDO LOG FILES can be controlled by the parameter MAXLOGFILES.

The maximum number of redo log files can be controlled by the parameter MAXLOGFILES.

The number of datafiles in an ORACLE DATABASE is CONTROLLED by the INITIALIZATION parameter DB_FILES.

The number of datafiles in an Oracle database is controlled by the initialization parameter DB_FILES.

Initialization PARAMETER FILE or SPFILE is always accessed first when an Oracle DATABASE is started.
This file is used to determine database LEVEL setting because those VALUES are stored as parameters in this file.

Initialization parameter file or SPFILE is always accessed first when an Oracle database is started.
This file is used to determine database level setting because those values are stored as parameters in this file.

The SYSTEM tablespace is created automatically during database creation. It contains data DICTIONARY OBJECTS. All data stored on BEHALF of stored PL/SQL program UNITS (procedures, functions, packages, and triggers) resides in the SYSTEM tablespace, which is always online when the database is open.

The SYSTEM tablespace is created automatically during database creation. It contains data dictionary objects. All data stored on behalf of stored PL/SQL program units (procedures, functions, packages, and triggers) resides in the SYSTEM tablespace, which is always online when the database is open.

Following is the logical structure of the disk resources:

•  DATA block :Refers to the SMALLEST logical storage unit. Size of a data block is a MULTIPLE of operating system block size.
•  Extent :Refers to the contiguous set of data blocks, which is allocated as a unit to a SEGMENT.
•  TABLESPACE :Refers to the final logical storage unit. It is mapped to physical datafile.
•  Segment:Allocates a logical structure, such as table. It is a set of extents, which are stored in the same tablespace.

Following is the logical structure of the disk resources:

Following are the situations when LGWR writes to the log FILE:

1. When a user process commits a transaction
2. When redo log buffer is one-third FULL
3. When more than a MEGABYTE of CHANGES are recorded into the redo log buffer
4. Before DBWR writes modified blocks to the DATAFILES

Following are the situations when LGWR writes to the log file:

LGWR is the background process that WRITES redo information from redo log BUFFERS to the log files.

LGWR is the background process that writes redo information from redo log buffers to the log files.

SCN is an ID that Oracle GENERATES for every transaction. It is recorded with the corresponding CHANGE in a redo entry.

SCN is an ID that Oracle generates for every transaction. It is recorded with the corresponding change in a redo entry.

Following is the process to update or insert data in the DATABASE:

•  RDBMS searches for parsed statement in library cache or parses the statement to generate execution plan.
•  Server process retrieves relevant data from disk to the BUFFER cache. In case of inserting data in the database, the data block with sufficient free space will be retrieved.
•  Lock is acquired. The data block is updated in the buffer cache.
•  A lock is acquired on ROLLBACK segment as well to store old VERSION of data in case rollback is required.
•  User process creates a redo entry in the redo log buffer to SUPPORT recovery.

Following is the process to update or insert data in the database:

ROLLBACK segment holds the previous VERSION of data block until either it gets committed or the space is not available for reuse. If a session is reading specific block from rollback segment while the other session has committed the INFORMATION, then more rollback space is required to maintain more rollback information. In that case, this specific block ages out of the rollback segment and the reading or selecting session gets an ERROR.

Rollback segment holds the previous version of data block until either it gets committed or the space is not available for reuse. If a session is reading specific block from rollback segment while the other session has committed the information, then more rollback space is required to maintain more rollback information. In that case, this specific block ages out of the rollback segment and the reading or selecting session gets an error.

ORACLE uses locking mechanism to ENSURE that only one session or user can update a single set of data at given point in TIME.

Oracle uses locking mechanism to ensure that only one session or user can update a single set of data at given point in time.

In a multi-user environment, there can be SITUATIONS when one or more users are reading certain set of data while other is modifying the same set of data. Data consistency provides a stable or CONSISTENT data to the users throughout the session.
Data consistency is maintained through rollback segments. A rollback SEGMENT holds the data image before CHANGE; therefore, one or more sessions will get the same image of the data throughout the session that were reading the data while another session is updating.

In a multi-user environment, there can be situations when one or more users are reading certain set of data while other is modifying the same set of data. Data consistency provides a stable or consistent data to the users throughout the session.
Data consistency is maintained through rollback segments. A rollback segment holds the data image before change; therefore, one or more sessions will get the same image of the data throughout the session that were reading the data while another session is updating.

The following steps are PERFORMED when a user requests for some information:
1. RDBMS checks if a copy of the parsed SQL statement exists in the library cache. If parsed copy exists, then steps 2 to 6 are skipped.
2. RDBMS VALIDATES the syntax of the statement.
3. RDBMS ENSURES that all the columns and tables referenced in the statement exist.
4. RDBMS acquires parse locks on objects referenced in the statement so that their definitions do not change while statement is parsed.
5. RDBMS ensures that the user has sufficient privileges.
6. Statement is parsed and execution plan is created.
7. Statement is executed.
8. Values are fetched.

The following steps are performed when a user requests for some information:
1. RDBMS checks if a copy of the parsed SQL statement exists in the library cache. If parsed copy exists, then steps 2 to 6 are skipped.
2. RDBMS validates the syntax of the statement.
3. RDBMS ensures that all the columns and tables referenced in the statement exist.
4. RDBMS acquires parse locks on objects referenced in the statement so that their definitions do not change while statement is parsed.
5. RDBMS ensures that the user has sufficient privileges.
6. Statement is parsed and execution plan is created.
7. Statement is executed.
8. Values are fetched.

A join DEPENDENCY is a SITUATION where a table can be CREATED by JOINING TWO or more tables.

A join dependency is a situation where a table can be created by joining two or more tables.

3NF is the STANDARD NORMAL FORM for most OLTP DATABASES.

3NF is the standard normal form for most OLTP databases.

A TRANSITIVE DEPENDENCY is the SITUATION when an ATTRIBUTE indirectly depends on ANOTHER attribute through a third attribute.

A transitive dependency is the situation when an attribute indirectly depends on another attribute through a third attribute.

A multi-valued dependency is the situation when an attribute (ASSUME B) is dependent on another attribute (assume A). However, there are MULTIPLE rows in a table representing the dependency. This situation can happen when there is at least one more attribute which is dependent on A and it requires to have multiple rows of A and B to represent all the POSSIBLE COMBINATIONS. A multi-valued dependency is represented by A-&GT;> B.

A multi-valued dependency is the situation when an attribute (assume B) is dependent on another attribute (assume A). However, there are multiple rows in a table representing the dependency. This situation can happen when there is at least one more attribute which is dependent on A and it requires to have multiple rows of A and B to represent all the possible combinations. A multi-valued dependency is represented by A->> B.

FULL functional dependency is the situation when an ATTRIBUTE depends on a GROUP of attributes completely but not on the subset of the attributes.

Full functional dependency is the situation when an attribute depends on a group of attributes completely but not on the subset of the attributes.

A trivial FUNCTIONAL DEPENDENCY is the situation, which shows a RELATION between an attribute with a superset of itself such that the attribute is dependent on the superset.
CONSIDER an attribute of the DRESS entity, say length. If the length attribute is dependent on the dress code attribute of the dress entity, then it is a non-trivial dependency; however, if the length attribute is dependent on the color and length attributes of the dress entity, then it is a trivial dependency.

A trivial functional dependency is the situation, which shows a relation between an attribute with a superset of itself such that the attribute is dependent on the superset.
Consider an attribute of the dress entity, say length. If the length attribute is dependent on the dress code attribute of the dress entity, then it is a non-trivial dependency; however, if the length attribute is dependent on the color and length attributes of the dress entity, then it is a trivial dependency.

PHYSICAL data model depicts DATABASE in terms of physical objects, such as TABLES and constraints; whereas, logical data model depicts database in terms of logical objects, such as entities and relationships, and it is represented by the entity- RELATIONSHIP model.

Physical data model depicts database in terms of physical objects, such as tables and constraints; whereas, logical data model depicts database in terms of logical objects, such as entities and relationships, and it is represented by the entity- relationship model.