Q#1: What is the purpose of the redo files in a database?
Redo log files are operating system files used by Oracle to maintain logs of all transactions performed against the database. The primary purpose of these log files is to allow Oracle to recover changes made to the database in the case of a failure.
An Oracle database must have at least two redo log files, and most databases have more than two. These files are written by the LGWR process in a circular fashion; that is, when the last log file is filled, the first log file is reused.
Q#2: Explain DML example?
DML stands for Data Manipulation Language. Data Manipulation Language (DML) can be defined as a set of syntax elements that are used to manage the data in the database. The commands of DML are not auto-committed and modifications made by them are not permanent to the database. It is a computer programming language that is used to perform select, insert, delete and update data in a database. The user requests are assisted by Data Manipulation Language. This language is responsible for all forms of data modification in a database.
Q#3: Differentiate between Immediate constraints and Deferred constraints?
Immediate constraints:
Immediate constraints, also known as non-deferred constraints are the constraints that are enforced at the end of every database DML statement. A constraint violation causes the statement to be rolled back. If the constraint causes an action such as delete cascade, then the action is taken as part of the statement that caused it. This type of constraint can be modified to be enforced at the end of a transaction.
Defining the Constraints:
ALTER SESSION SET CONSTRAINT [S] ={IMMEDIATE|DEFERRED|DEFAULT}
Deferred Constraints:
Deferred constraints are the constraints which are checked only when a transaction is committed. If any constraints violation is occurred at the commit, the entire transaction is rolled back. These constraints are most useful when both the parent and child rows in a foreign key relationship are entered at the same time, as in case of an order entry system, where the ordered are entered at the same time.
Defining the constraints:
ALTER SESSION SET CONSTRAINT [S]={IMMEDIATE|DEFERRED|DEFAULT}
Q#4: Define the term Extents and Block?
One data block corresponds to a specific number of bytes of physical
database space on a disk. The next level of logical database space is
an extent. An extent is a specific number of contiguous
data blocks allocated for storing a specific type of information.
Segment: The level of logical database storage greater than an extent is
called a segment. A segment is a set of extents, each of which has been
allocated for a specific data structure and all of which are stored in the
same tablespace.
For example, each table's data is stored in its own data segment, while
each index's data is stored in its own index segment. If the table or
index is partitioned, each partition is stored in its own segment.
Q#5: Explain the types of segments?
- Data segment--Stores user data within the database.
- Index segment--Stores indexes.
- Rollback segment--Stores rollback information used when data must be rolled back.
- Temporary segment--Created when a SQL statement requires a temporary work area such as during sorting of data.
Q#6: What are SMON and PMON?
PMON and SMON are two required background processes.
PMON is the Process
Monitor which is responsible for recovering processes when the user process
fails. PMON does the process cleanup.
SMON is the System Monitor which is
responsible for recovering the system after a failure.
Q#7: What is the purpose of CKPT?
CKPT (Oracle Checkpoint Process) is an Oracle background process that timestamps all datafiles and control files to indicate that a checkpoint has occurred. The "DBWR checkpoints" statistic (v$sysstat) indicates the number of checkpoint requests completed.
Q#8: Differentiate between complete recovery and incomplete recovery?
Complete Recovery:
Complete recovery involves using redo data or incremental backups combined with a backup of a database, tablespace, or datafile to update it to the most current point in time. It is called complete because Oracle applies all of the redo changes contained in the archived and online logs to the backup. Typically, you perform complete media recovery after a media failure damages datafiles or the control file.
Incomplete Recovery:
Incomplete recovery uses a backup to produce a noncurrent version of the database. In other words, you do not apply all of the redo records generated after the most recent backup. You usually perform incomplete recovery of the whole database in the following situations:
- Media failure destroys some or all of the online redo logs.
- A user error causes data loss, for example, a user inadvertently drops a table.
- You cannot perform complete recovery because an archived redo log is missing.
- You lose your current control file and must use a backup control file to open the database.
Q#9: What is Data Instance?
A data instance is an instance of a concrete data class, a concrete class derived from the Data- base class. For example, a workbasket is an instance of the Data-Admin-Workbasket class. The data instance is described by a list of features defined by the domain descriptor (Orange.data. domain). Instances support indexing with either integer indices, strings or variable descriptors.
Q#10: Define Profile?
A profile is a database object - a named set of resource limits to:
Profiles restrict users from performing operations that exceed reasonable
resource utilization.
Examples of resources that need to be managed:
- Disk storage space.
- I/O bandwidth to run queries.
- CPU power.
- Connect time.
- CPU time.
Long Question
Q#1: Explain Oracle Database Memory Structure in Detail? Also Draw Diagram?
The basic memory structures associated
with Oracle Database include:
System global area (SGA) The SGA is a group of shared memory structures,
known as SGA components that contain data and control information for
one Oracle Database instance. The SGA is shared by all server and
background processes.
When an instance is started, Oracle Database allocates a memory area and
starts background processes. The memory area stores information such
as the following:
- Program code
- Information about each connected session, even if it is not currently active
- Information is needed during program execution, for example, the current state of a query from which rows are being fetched.
- Information such as lock data that is shared and communicated among processes.
- Cached data, such as data blocks and redo records, that also exists on disk
System global area (SGA):
The SGA is a group of shared memory structures, known as SGA components, that contain data and control information for one Oracle Database instance. The SGA is shared by all server and background processes. Examples of data stored in the SGA include cached data blocks and shared SQL areas.
Program global area (PGA):
A PGA is a nonshared memory region that contains data and control information exclusively for use by an Oracle process. The PGA is created by Oracle Database when an Oracle process is started.
User Global Area (UGA):
The UGA is memory associated with a user session.
Software code areas:
Software code areas are portions of memory used to store code that is being run or can be run. Oracle Database code is stored in a software area that is typically at a different location from user programs—a more exclusive or protected location.
Q#2: Write a command to create user Ali having 1GB quota on each tablespace RBS1 & TBS 2, TBS1 is default for Ali.
The statement query is given below.
CREATE USER Ali
IDENTIFIED BY out_standing1
DEFAULT TABLESPACE TBS1
QUOTA 1G ON TBS1
TEMPORARY TABLESPACE TBS2
QUOTA 1G ON system
PROFILE app_user
PASSWORD EXPIRE;
As 1GB=1000MB
CREATE USER ALI
IDENTIFIED BY pass
QUOTA 1000M ON TBS1
QUOTA 1000M ON TBS2
Q#3: Explain the working of Redo Log and archive files in database?
Redo Log in a database:
Redo log files are operating system files used by Oracle to maintain logs of all transactions performed against the database. The primary purpose of these log files is to allow Oracle to recover changes made to the database in the case of a failure.
An Oracle database must have at least two redo log files, and most databases have more than two. These files are written by the LGWR process in a circular fashion; that is, when the last log file is filled, the first log file is reused.
For example, if a database has three redo log files, blocks will be written to file1 until it is filled; then that file is closed, and LGWR begins writing to file2 (this is called a log switch). When file2 is filled, LGWR switches to file3. When file3 is filled, file1 is reused, and so on.
Redo log files are filled with redo records. A redo record also called a redo entry, is made up of a group of change vectors, each of which is a description of a change made to a single block in the database.
The Oracle Database uses only one redo log file at a time to store redo records written from the redo log buffer. The redo log file that LGWR is actively writing to is called the current redo log file. Redo log files that are required for instance recovery are called active redo log files. Redo log files that are no longer required for instance recovery are called inactive redo log files.
The following statement adds a new group of redo logs to the database:
ALTER DATABASE
ADD LOGFILE ('/oracle/dbs/log1c.rdo', '/oracle/dbs/log2c.rdo') SIZE 100M;
Archive File in a Database:
An archive file is a file that is composed of one or more computer files along with metadata. Archive files are used to collect multiple data files together into a single file for easier portability and storage, or simply to compress files to use less storage space. Archive files often store directory structures, error detection and correction information, arbitrary comments, and sometimes use built-in encryption.
Features supported by various kinds of archives include:
- converting metadata into data stored inside a file (e.g., file name, permissions, etc.)
- checksums to detect errors
- data compression
- file concatenation to store multiple files in a single file
- file patches / updates (when recording changes since a previous archive)
- encryption
- error correction code to fix errors
- splitting a large file into many equal sized files for storage or transmission