Explore topic-wise InterviewSolutions in .

Recovery Manager (RMAN) is an Oracle utility that manages backup and recovery operations, creating BACKUPS of database files (DATAFILES, control files, and archived redo log files) and restoring or recovering a database from backups. Recovery Manager maintains a repository called the recovery catalog, which contains information about backup files and archived log files. Recovery Manager uses the recovery catalog to automate both restore operations and media recovery.

The recovery catalog contains:
•Information about backups of datafiles and archive logs
•Information about datafile copies
•Information about archived redo logs and copies of them
•Information about the physical schema of the TARGET database
•Named SEQUENCES of statements called stored SCRIPTS

Recovery Manager (RMAN) is an Oracle utility that manages backup and recovery operations, creating backups of database files (datafiles, control files, and archived redo log files) and restoring or recovering a database from backups. Recovery Manager maintains a repository called the recovery catalog, which contains information about backup files and archived log files. Recovery Manager uses the recovery catalog to automate both restore operations and media recovery.

The recovery catalog contains:
•Information about backups of datafiles and archive logs
•Information about datafile copies
•Information about archived redo logs and copies of them
•Information about the physical schema of the target database
•Named sequences of statements called stored scripts

In every database SYSTEM, the possibility of a system or hardware failure always exists. Should a failure OCCUR and affect the database, the database must be recovered. The goals after a failure are to ensure that the effects of all committed transactions are reflected in the recovered database and to RETURN to NORMAL operation as quickly as possible while INSULATING users from problems caused by the failure.

In every database system, the possibility of a system or hardware failure always exists. Should a failure occur and affect the database, the database must be recovered. The goals after a failure are to ensure that the effects of all committed transactions are reflected in the recovered database and to return to normal operation as quickly as possible while insulating users from problems caused by the failure.

Read consistency, as supported by ORACLE, does the FOLLOWING:

•Guarantees that the set of data seen by a statement is consistent with respect to a single point in time and does not change during statement execution (statement-level read consistency)
•Ensures that READERS of database data do not wait for writers or other readers of the same data
•Ensures that writers of database data do not wait for readers of the same data Ensures that writers only wait for other writers if they attempt to update identical rows in concurrent TRANSACTIONS

Read consistency, as supported by Oracle, does the following:

•Guarantees that the set of data seen by a statement is consistent with respect to a single point in time and does not change during statement execution (statement-level read consistency)
•Ensures that readers of database data do not wait for writers or other readers of the same data
•Ensures that writers of database data do not wait for readers of the same data Ensures that writers only wait for other writers if they attempt to update identical rows in concurrent transactions

A primary concern of a multiuser database management system is how to control concurrency, or the simultaneous access of the same data by many users. Without adequate concurrency controls, data could be updated or changed improperly, compromising data integrity.

If many PEOPLE are accessing the same data, one way of managing data concurrency is to make each user wait his or her turn. The goal of a database management system is to reduce that wait so it is either nonexistent or negligible to each user. All data manipulation language statements should proceed with as LITTLE interference as possible and destructive interactions between concurrent transactions must be prevented. Destructive interaction is any interaction that incorrectly updates data or incorrectly ALTERS underlying data structures. Neither performance nor data integrity can be sacrificed.

Oracle resolves such issues by using various types of locks and a multiversion consistency model. Both FEATURES are discussed later in this section. These features are based on the concept of a transaction. It is the application designer’s RESPONSIBILITY to ensure that transactions fully exploit these concurrency and consistency features.

A primary concern of a multiuser database management system is how to control concurrency, or the simultaneous access of the same data by many users. Without adequate concurrency controls, data could be updated or changed improperly, compromising data integrity.

If many people are accessing the same data, one way of managing data concurrency is to make each user wait his or her turn. The goal of a database management system is to reduce that wait so it is either nonexistent or negligible to each user. All data manipulation language statements should proceed with as little interference as possible and destructive interactions between concurrent transactions must be prevented. Destructive interaction is any interaction that incorrectly updates data or incorrectly alters underlying data structures. Neither performance nor data integrity can be sacrificed.

Oracle resolves such issues by using various types of locks and a multiversion consistency model. Both features are discussed later in this section. These features are based on the concept of a transaction. It is the application designer’s responsibility to ensure that transactions fully exploit these concurrency and consistency features.

The relational model has three MAJOR aspects:

Structures: Structures are well-defined objects (such as tables, VIEWS, indexes, and so on) that store or access the data of a database. Structures and the data contained WITHIN them can be manipulated by operations.

Operations: Operations are clearly defined actions that allow users to manipulate the data and structures of a database. The operations on a database must adhere to a predefined set of integrity RULES.

integrity rules :Integrity rules are the laws that govern which operations are allowed on the data and structures of a database. Integrity rules protect the data and the structures of a database.

The relational model has three major aspects:

Structures: Structures are well-defined objects (such as tables, views, indexes, and so on) that store or access the data of a database. Structures and the data contained within them can be manipulated by operations.

Operations: Operations are clearly defined actions that allow users to manipulate the data and structures of a database. The operations on a database must adhere to a predefined set of integrity rules.

integrity rules :Integrity rules are the laws that govern which operations are allowed on the data and structures of a database. Integrity rules protect the data and the structures of a database.

The program interface is the MECHANISM by which a user process communicates with a server process. It SERVES as a method of standard communication between any client tool or application (such as Oracle FORMS) and Oracle software. Its functions are to:

•Act as a communications mechanism, by FORMATTING data requests, passing data, and trapping and returning errors
•Perform conversions and translations of data, particularly between different TYPES of computers or to external user program datatypes

The program interface is the mechanism by which a user process communicates with a server process. It serves as a method of standard communication between any client tool or application (such as Oracle Forms) and Oracle software. Its functions are to:

•Act as a communications mechanism, by formatting data requests, passing data, and trapping and returning errors
•Perform conversions and translations of data, particularly between different types of computers or to external user program datatypes

Oracle creates a set of background processes for each instance. They CONSOLIDATE functions that would otherwise be handled by multiple Oracle programs running for each USER process. The background processes asynchronously PERFORM I/O and monitor other Oracle processes to provide INCREASED parallelism for BETTER performance and reliability.

Oracle creates a set of background processes for each instance. They consolidate functions that would otherwise be handled by multiple Oracle programs running for each user process. The background processes asynchronously perform I/O and monitor other Oracle processes to provide increased parallelism for better performance and reliability.

ORACLE PROCESSES are CALLED by other processes to PERFORM functions on behalf of the invoking process. 

Oracle processes are called by other processes to perform functions on behalf of the invoking process. 

A user process is created and maintained to execute the software code of an application PROGRAM (such as a Pro*C/C++ program) or an Oracle TOOL (such as Oracle Enterprise Manager). The user process ALSO manages the communication with the server PROCESSES.

A user process is created and maintained to execute the software code of an application program (such as a Pro*C/C++ program) or an Oracle tool (such as Oracle Enterprise Manager). The user process also manages the communication with the server processes.

A process is a "thread of control" or a MECHANISM in an operating system that can execute a SERIES of steps. Some operating SYSTEMS use the TERMS job or task. A process normally has its own PRIVATE memory area in which it runs.

A process is a "thread of control" or a mechanism in an operating system that can execute a series of steps. Some operating systems use the terms job or task. A process normally has its own private memory area in which it runs.

The System Global Area (SGA) is a shared memory region that contains data and control information for one Oracle instance. An SGA and the Oracle BACKGROUND PROCESSES constitute an Oracle instance.

Oracle allocates the system global area when an instance STARTS and deallocates it when the instance shuts down. Each instance has its own system global area.

Users currently CONNECTED to an Oracle server share the data in the system global area. For optimal PERFORMANCE, the entire system global area should be as large as possible (while still fitting in real memory) to store as much data in memory as possible and minimize disk I/O.

The System Global Area (SGA) is a shared memory region that contains data and control information for one Oracle instance. An SGA and the Oracle background processes constitute an Oracle instance.

Oracle allocates the system global area when an instance starts and deallocates it when the instance shuts down. Each instance has its own system global area.

Users currently connected to an Oracle server share the data in the system global area. For optimal performance, the entire system global area should be as large as possible (while still fitting in real memory) to store as much data in memory as possible and minimize disk I/O.

Memory and process structures used by an Oracle server to manage a database. Among other things, the architectural FEATURES DISCUSSED in this section provide an understanding of the capabilities of the Oracle server to support:

•Many USERS concurrently ACCESSING a single database
•The high performance required by concurrent multi-user, multi-application database systems
An Oracle server uses memory structures and processes to manage and access the database. All memory structures exist in the main memory of the computers that constitute the database system.

Memory and process structures used by an Oracle server to manage a database. Among other things, the architectural features discussed in this section provide an understanding of the capabilities of the Oracle server to support:

•Many users concurrently accessing a single database
•The high performance required by concurrent multi-user, multi-application database systems
An Oracle server uses memory structures and processes to manage and access the database. All memory structures exist in the main memory of the computers that constitute the database system.

Control FILES contains the following types of INFORMATION:

•DATABASE name
•Names and locations of DATAFILES and redo log files
•Time STAMP of database creation

Control files contains the following types of information:

•Database name
•Names and locations of datafiles and redo log files
•Time stamp of database creation

The following SECTIONS explain the physical database structures of an ORACLE database, including datafiles, redo log files, and CONTROL files.

Datafiles
Every Oracle database has one or more physical datafiles. A database’s datafiles CONTAIN all the database data. The data of logical database structures such as tables and INDEXES is physically stored in the datafiles allocated for a database.

Redo Log Files
Every Oracle database has a set of two or more redo log files. The set of redo log files for a database is collectively known as the database’s redo log. A redo log is made up of redo entries (also called redo records), each of which is a group of change vectors describing a single atomic change to the database.

Control Files
Every Oracle database has a control file. A control file contains entries that specify the physical structure of the database.

The following sections explain the physical database structures of an Oracle database, including datafiles, redo log files, and control files.

Datafiles
Every Oracle database has one or more physical datafiles. A database’s datafiles contain all the database data. The data of logical database structures such as tables and indexes is physically stored in the datafiles allocated for a database.

Redo Log Files
Every Oracle database has a set of two or more redo log files. The set of redo log files for a database is collectively known as the database’s redo log. A redo log is made up of redo entries (also called redo records), each of which is a group of change vectors describing a single atomic change to the database.

Control Files
Every Oracle database has a control file. A control file contains entries that specify the physical structure of the database.

rollback segments for a database are created by the database ADMINISTRATOR to TEMPORARILY store UNDO information. The information in a rollback segment is used:

•To generate read-consistent database information
•During database recovery
•To rollback UNCOMMITTED TRANSACTIONS for users

rollback segments for a database are created by the database administrator to temporarily store undo information. The information in a rollback segment is used:

•To generate read-consistent database information
•During database recovery
•To rollback uncommitted transactions for users

At the finest level of granularity, Oracle database DATA is STORED in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk. A data block SIZE is specified for each Oracle database when the database is CREATED. A database uses and ALLOCATES free database space in Oracle data blocks.

At the finest level of granularity, Oracle database data is stored in data blocks. One data block corresponds to a specific number of bytes of physical database space on disk. A data block size is specified for each Oracle database when the database is created. A database uses and allocates free database space in Oracle data blocks.

A database is DIVIDED into LOGICAL storage UNITS called tablespaces, which group related logical structures together. For example, tablespaces commonly group all of an application’s OBJECTS to simplify some administrative OPERATIONS.

A database is divided into logical storage units called tablespaces, which group related logical structures together. For example, tablespaces commonly group all of an application’s objects to simplify some administrative operations.

An Oracle database is a collection of DATA that is treated as a unit. The PURPOSE of a database is to store and retrieve related information. The database has logical STRUCTURES and PHYSICAL structures. Because the physical and logical structures are separate, the physical storage of data can be managed without AFFECTING the access to logical storage structures.

An Oracle database is a collection of data that is treated as a unit. The purpose of a database is to store and retrieve related information. The database has logical structures and physical structures. Because the physical and logical structures are separate, the physical storage of data can be managed without affecting the access to logical storage structures.

This Oracle Interview QUESTIONS is some time asked as follow up of previous Oracle Interview questions related to CONVERTING date to char in Oracle. By the WAY to_ date function is USED to convert string to a date function.
Syntax : to_date(string, format)

Example: to_date('2012/06/12', 'yyyy/mm/dd') It will return June 12, 2012

This Oracle Interview questions is some time asked as follow up of previous Oracle Interview questions related to converting date to char in Oracle. By the way to_ date function is used to convert string to a date function.
Syntax : to_date(string, format)

Example: to_date('2012/06/12', 'yyyy/mm/dd') It will return June 12, 2012

to_char() FUNCTION is used to CONVERT DATE to character we can specify format also in which we WANT the output.

SELECT to_char( to_date('11-01-2012', 'DD-MM-YYYY') , 'YYYY-MM-DD') FROM dual;
or
SELECT to_char( to_date('11-01-2012, 'DD-MM-YYYY') , 'DD-MM-YYYY') FROM dual;

to_char() function is used to convert date to character we can specify format also in which we want the output.

SELECT to_char( to_date('11-01-2012', 'DD-MM-YYYY') , 'YYYY-MM-DD') FROM dual;
or
SELECT to_char( to_date('11-01-2012, 'DD-MM-YYYY') , 'DD-MM-YYYY') FROM dual;