DBA

When a checkpoint occurs

, Oracle must update the control file and each data file header to record the checkpoint. This update is done by the checkpoint process (CKPT); the DBWn process writes the actual data blocks to the data files.

SGA Share pool

-- holds information such as SQL, PL/SQL procedures and packages, the data dictionary, locks, character-set information, security attributes, and so on. The --- consists of the library cache and the data dictionary cache.

control files

-A database-creation timestamp -The names, locations, and sizes of the data files and redo log files -Tablespace information -Redo log information used to recover the database in the case of a disk failure or user error -Archived log information -RMAN backup information -Checkpoint information

Buffers

-Dirty buffers are the buffer blocks that need to be written to the data files. The data in these buffers has changed and has not yet been written to the disk. -Free buffers do not contain any data or are free to be overwritten. When Oracle reads data from the disk, free buffers hold this data. -Pinned buffers are the buffers that are currently being accessed or explicitly retained for future use.

components of PGA

-SQL Work Area Area used for memory-intensive operations such as sorting or building a hash table during join operations. -Private SQL Area Holds information about SQL statement and bind variable values.

Oracle server architecture

-Server processes that communicate with users processes and interact with an Oracle instance to fulfill requests -Logical memory structures that are collectively called an Oracle instance -Physical file structures that are collectively called a database

reserved pool i

-components of SGA- an area in the shared pool used to allocate large chunks of memory. Its size is determined by the SHARED_POOL_RESERVED_SIZE initialization parameter.

data dictionary

2nd component of shared pool. Holds the most recently used database dictionary information. is also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data).

default port & URL EM Database

5,500,https://<hostname>:5500/em

checkpoint

A checkpoint is when the DBWn process writes all the dirty buffers to the data files.

9. Which tasks are accomplished by the SMON process? (Choose all that apply.) Performs recovery at instance startup Performs cleanup after a user session is terminated Starts any server process that stopped running Coalesces contiguous free space in dictionary-managed tablespaces

A, D. SMON is responsible for performing instance recovery using the online redo log files and for coalescing contiguous free space in tablespaces. The PMON is responsible for session cleanup and for freeing up all resources after a user session is terminated.

Which component is configured at database startup and cannot be dynamically managed? Redo log buffer Streams pool Java pool Shared pool None of the abov

A. An Oracle database allows you to manage all memory components dynamically, except the redo log buffer. Redo log buffer is set at instance startup and is not dynamically alterable without restarting the instance.

7. Which of the following best describes a RAC configuration? One database, multiple instances One instance, multiple databases Multiple databases plugged in from multiple servers Multiple databases, multiple instances

A. With Real Application Clusters, multiple instances (known as nodes) can mount one database. One instance can be associated with only one database.

Archiver

ARCn, Copies the transaction recovery information from the redo log files to the archive location. Nearly all production databases use this optional process. You can have up to 30 archival processes (ARC0-ARC9, ARCa-ARCt).

ASM Disk

ASMB, Present on databases using Automatic Storage Management disks.

Data file

All application data and internal metadata.

System Global Area (SGA)

An Oracle database instance consists of Oracle's main memory structure, called the ---- and several Oracle background processes. When the user accesses the data in the database, it is the ---with which the server process communicates

Dispatcher (Dnn)

Assigns user's database requests to a queue where they are then serviced by shared server processes when Oracle's Shared Server feature is used. See Chapter 11, "Managing Data and Undo," for details on using shared servers.

Choose two SGA structures that are required in every Oracle instance.

B, C. Database buffer cache, shared pool, and log buffer are required; they are configured automatically in every instance. It is better to use Automatic Memory Management or Automatic Shared Memory Management, so that the DBA does not need to tune individual components.

Schema

Because a schema is a collection of objects, DBAs often define a schema to represent a collection of objects associated with an application. For example, a DBA might create a schema called SALES and create objects owned by that schema. Then, they can grant access to other database users who need the ability to access the SALES schema.

Which component is not part of an Oracle instance? System global area Process monitor Control file Shared pool None

C. Control file, data file, and redo log files are part of the Oracle database. The Oracle instance constitutes the memory structures and background processes.

5. Which background process guarantees that committed data is saved even when the changes have not been recorded in data files? DBWn PMON LGWR CKPT ARCn

C. The log writer (LGWR) process writes the redo log buffer information to the online redo log files. A commit operation is completed only after the redo buffer is written to online redo log files.

8. Which component of the SGA contains the parsed SQL code? Database buffer cache Dictionary cache Library cache Parse cache

C. The shared SQL area is stored in the library cache in a shared pool and is shared between users. If a query is executed again before it is aged out of the library cache, Oracle will use the parsed code and execution plan from the library cache. The database buffer cache has the data blocks cached. The dictionary cache caches data dictionary information. There is no SGA component called the parse cache.

Job Queue Monitor

CJQn, Assigns jobs to the job queue processes when using

Checkpoint

CKPT, Updates the data file headers following a checkpoint event. processes are started by default when the instance starts.

Database buffer cache

Caches the data that has been most recently accessed by database users

Shared Pool

Caches the most recently used SQL statements that have been issued by database users

Parameter (pfile or spfile)

Configuration parameters for the SGA, optional Oracle features, and background processes.

Space Management Coordinator (SMCO)

Coordinates various space management tasks. Worker processes are identified with Wnnn.

Archived redo log file

Copy of the contents of online redo logs, used for database recovery and for change capture.

Which statement is true?A database can have only one control file. A database must have at least two control files. A database may have zero or more control files. A database must have at least one control file.

D. The control file is the most key file in an Oracle database. Due to its importance, it is a good practice to have two more copies of the file. A database must have at least one control file to start the database.

6. User John has updated several rows in a table and issued a commit. What does the DBWn (database writer) process do at this time in response to the commit event? Writes the changed blocks to data files. Writes the changed blocks to redo log files. Triggers checkpoint and thus LGWR writes the changes to redo log files. Does nothing.

D. When a user issues a commit, the LGWR process makes sure the redo log buffer is written to the online redo log files. Database writer takes no action against the commit event.

How to change EM default port

DBMS_XDB_CONFIG.setHTTPsPort (<port>)

Database Writer

DBWn,BWnn, Writes modified database blocks from the SGA's database buffer cache to the data files on disk. processes are started by default when the instance starts.

Event Monitor

EMNC, Monitors the messages in the message queue when Oracle's Advanced Queuing feature is used.

Oracle net file

Entries that configure the database listener and client-to-database connectivity. See Chapter 12 for details.

Flashback Data Archive

FBDA, Archives historical records from a table when the flashback data archive feature is used

Flashback log file

If the database has flashback logging enabled, files are written to the fast recovery area.

Program Global Area (PGA)

In addition to the user and server processes that are associated with each user connection, an additional memory structure called ____ is also created for each server process. The ___ stores user-specific session information such as bind variables and session variables. Every server process on the server has a ___memory area.

Job Queue

Jnnn, Executes database jobs that have been scheduled using Oracle's job-scheduling feature

Change Tracking Write (CTWR)

Keeps track of which database blocks have changed when Oracle's incremental Recovery Manager feature is used. See Chapter 16 for details on using Recovery Manager to perform backups.

Log Writer

LGWR, Writes transaction recovery information from the SGA's redo log buffer to the online redo log files on disk. processes are started by default when the instance starts.

Listener Registration

LREG, Registers information about the database instance and dispatcher processes with the listener. processes are started by default when the instance starts.

control files

Locations of other physical files, database name, database block size, database character set, and recovery information. These files are required to open the database.

Memory Manager (MMAN)

Manages the size of each individual SGA component when Oracle's Automatic Shared Memory Management feature is used.

Password file

Optional file used to store names of users who have been granted the SYSDBA and SYSOPER privileges. See Chapter 13, "Implementing Security and Auditing," for details on SYSDBA and SYSOPER privileges.

least recently used algorithm(LRU algorithm)

Oracle uses a -----to manage the contents of the shared pool and database buffer cache

Process Monitor

PMON, Cleans up failed user database connections. processes are started by default when the instance starts.

Parallel Query Slave

Qnnn, Carries out portions of a larger overall query when Oracle's Parallel Query feature is used.

ASM Balance

RBAL, Coordinates rebalance activity of disks in an ASM disk group.

Recoverer

RECO, Recovers failed transactions that are distributed across multiple databases when using Oracle's distributed database feature. processes are started by default when the instance starts.

Recovery Writer

RVWR, Writes flashback data to flashback database logs in the fast recovery area.

redo log file

Record of all changes made to the database; used for instance recovery.

System Monitor

SMON, Performs instance recovery following an instance crash, coalesces free space in the database, and manages space used for sorting. processes are started by default when the instance starts.

Required Tablespaces in Oracle 12c

SYSTEM SYSAUX TEMP

Shared Server (Snnn)

Server processes that are shared among several users when Oracle's Shared Server feature is used. See Chapter 11 for details on using shared servers.

SYSAUX Tablespace

Stores segments used for database options such as the Automatic Workload Repository, Online Analytical Processing (OLAP), and Spatial.

SYSTEM Tablespace

Stores the data dictionary tables and PL/SQL code.

Redo log buffer

Stores transaction information for recovery purposes

library cache

The ---contains the shared SQL areas, private SQL areas, PL/SQL programs, and control structures such as locks and library cache handles.

When Does Database Writer Write?

The DBWn background process writes to the data files whenever one of the following events occurs: -A user's server process has searched too long for a free buffer when reading a buffer into the buffer cache. --The number of modified and committed, but unwritten, buffers in the database buffer cache is too large. -At a database checkpoint event. See Chapter 16 for information on checkpoints. -The instance is shut down using any method other than a shutdown abort. -A tablespace is placed into backup mode. -A tablespace is taken offline to make it unavailable or is changed to READ ONLY. -A segment is dropped.

When Does Log Writer Write?

The LGWR background process writes to the current redo log group under any of the following conditions: Three seconds since the last LGWR write When a user commits a transaction When the redo log buffer is a third full When the redo log buffer contains 1MB worth of redo information Whenever a database checkpoint occurs

LGWR

The background process __writes the redo log information to the online redo log files.

V$BGPROCESS

The dynamic view shows the background processes available

Database buffer cache

The is the area in SGA that caches the database data, holding blocks from the data files that have been accessed recently. Th---e is shared among all the users connected to the database.

DB_WRITER_PROCESSES

The parameter --- determines the additional number of database writer processes to be started. Having more DBWn processes than the number of CPUs is normally not beneficial.

Process Monitor (PMON)

The process monitor process (PMON) cleans up failed user processes and frees up all the resources used by the failed process. It resets the status of the active transaction table and removes the process ID from the list of active processes. It reclaims all the resources held by the user and releases all locks on tables and rows held by the user. PMON wakes up periodically to check whether it is needed. Other processes can call PMON if they detect a need for a PMON process.

instances

The required background processes are found in all Oracle---

SQL query result cache

The stores the results of queries. If an application runs the same SELECT statement repeatedly and if the results are cached, then the database can return them immediately. In this way, the database avoids the expensive operation of rereading blocks to show results. (SGA optional component)

System Monitor (SMON)

The system monitor process (SMON) performs instance or crash recovery at database startup by using the online redo log files. SMON is also responsible for cleaning up temporary segments in the tablespaces that are no longer used and for coalescing the contiguous free space in the dictionary-managed tablespaces. If any dead transactions were skipped during instance recovery because of file-read or offline errors, SMON recovers them when the tablespace or data file is brought back online. SMON wakes up regularly to check whether it is needed. Other processes can call SMON if they detect a need for an SMON process.

connection

The user process then initiates a connection to the instance. Oracle calls the process of initiating and maintaining communication between the user process and the instance a___

To better manage the buffer cache better, Oracle Database 12c provides three buffer cache

To better manage the buffer cache better, Oracle Database 12c provides three buffer caches. The DEFAULT cache is the default and is required. The KEEP cache and the RECYCLE cache can be optionally configured. By default all the data read from the disk is written to the DEFAULT pool

TEMP Tablespace

Used for performing large sort operations. TEMP is required when the SYSTEM tablespace is created as a locally managed tablespace; otherwise, it is optional. See Chapter 10, "Understanding Storage and Space Management," for details.

Virtual Keeper of Time

VKTM, Responsible for providing a wall-clock time (updated every second) and reference-time counter. processes are started by default when the instance starts.

user process

Whenever a user runs an application, such as a human-resources or order-taking application, Oracle starts a _______ to support the user's connection to the instance.

Recovery Writer (RVWR)

Writes recovery information to disk when Oracle's Flashback Database feature is used. See Chapter 16, "Implementing Database Backups," for details on how to use the Flashback Database feature.

cluster

a group of tables sharing a common column. The cluster stores the rows of the tables together with the common columns stored once.

large pool i

an optional area in the SGA that the DBA can configure to provide large memory allocations for specific database operations such as an RMAN backup or restore. Does not have an LRU list; Oracle Database 12c does not attempt to age objects out of the large pool. The parameter LARGE_POOL_SIZE determines the size of the large pool.

dirty buffer

are buffer blocks that need to be written to the data files. The data in these buffers has changed and has not yet been written to the disk.

pinned buffers

are the buffers that are currently being accessed or explicitly retained for future use.

Real Application Clusters(RAC)

configuration that allow for multiple instances to access a single database; such a configuration is known as

Oracle Storage Structures

control files, data files, and redo log files.

user data and overhead

dictionary, memory, control files, archived log files, flashback files

Free buffers

do not contain any data or are free to be overwritten. When Oracle reads data from the disk, free buffers hold this data.

V$LOG

dynamic performance view shows information on redo logs in the database,

DBMS

facilitates the storage, modification, and retrieval of this data

Tablespace

is a logical storage structure at the highest level in the database, consists of one or more data files.

SGA

is a shared memory area. All the users of the database share the information maintained in this area. Oracle allocates memory for the --- when the instance is started and de-allocates it when the instance is shut down. The --- consists of three mandatory components and four optional components

instance

is composed of the memory structures and background processes (Process and memory structures)

server process

is responsible for performing the tasks that actually allow the user to interact with the database. The ______ are allowed to interact with the instance, but not the user process directly.

the shared SQL area

is used for maintaining recently executed SQL statements and their execution plans. Oracle divides each SQL statement that it executes into a shared SQL area and a private SQL area. When two users are executing the same SQL, the information in the shared SQL area is used for both. The shared SQL area contains the parse tree and execution plan, whereas the private SQL area contains values for the bind variables (persistent area) and runtime buffers (runtime area)

The third area in the library cache

is used to store control information and is maintained internally by Oracle. Various locks, latches, and other control structures reside here, and any server process that requires this information can access it.

A database checkpoint or thread checkpoint

is when all data file headers as well as the control file are updated with checkpoint information. At this time, the database writes all the dirty buffers to data files. This happens during normal database shutdown, online redo log switch, forced checkpoint using ALTER SYSTEM CHECKPOINT, or when the database is placed in backup mode using ALTER DATABASE BEGIN BACKUP.

key database components

memory structures, process structures, and storage structures

oracle server

the instance and the database

SGA Components

three mandatory components (Redo log buffer, Database buffer cache, Redo log buffer) and four optional components.

user process and the server process

two types of processes allow a user to interact with the instance and, ultimately, with the database

Log Writer (LGWR)

writes the blocks in the redo log buffer of the SGA to the online redo log files. When the LGWR writes log buffers to disk, Oracle server processes can write new entries in the redo log buffer. LGWR writes the entries to the disk fast enough to ensure that room is available for the server process to write the redo entries. There can be only one LGWR process in the database.

multitenancy

you can have more than one database in a structure called a container database. The database overhead will be shared by all the databases in the container database. The databases in the container database are called pluggable databases.

RECO, DIA0, DIAG

—these are restarted automatically if the process dies or is terminated)