Oracle Database

Active (Current) Redo Log Files

-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 or current redo log files

• Password expires automatically in ___ days

180

PFILE SPFILE

2 types of parameter files

• User account is locked _ days after password expiration

7

data dictionary

A data dictionary contains: The definitions of all schema objects in the database (tables, views, indexes, clusters, synonyms, sequences, procedures, functions, packages, triggers, and so on) How much space has been allocated for, and is currently used by, the schema objects Default values for columns Integrity constraint information The names of Oracle users Privileges and roles each user has been granted Auditing information, such as who has accessed or updated various schema objects Other general database information Any Oracle user can use the data dictionary as a read-only reference for information about the database. Only the sys user should ever alter any any rows or schema objects Only Oracle can write ir change data dictionary information.

Log Switches

A log switch is the point at which the database stops writing to one redo log file and begins writing to another. Normally, a log switch occurs when the current redo log file is completely filled and writing must continue to the next redo log file. However, you can configure log switches to occur at regular intervals, regardless of whether the current redo log file is completely filled. You can also force log switches manually.

These views return information about schema objects to which the user has access through public or explicit grants of privileges and roles, in addition to schema objects that the user owns.

ALL_

• Has own instance • Portable and high-performance cluster file system • Manages oracle database files • Manages application files with ASM Cluster File System • Spreads data across disks to balance load

ASM

• Powerful tools for database management • They provide specific advice on how to address key management challenges, covering a wide range of areas including space, performance, and undo management. • Provided to help you improve database performance

Advisory

- stores information about errors and actions taken that effect the configuration of the database. Trace file is created by the user process and is written to USER_DUMP_DEST. Alert file is created by server process and written to BACKGROUND_DUMP_DEST. If a server or background process detects an internal error then it is written to a trace file - also tracing information is written to trace files.

Alert and Trace logs

- used to recover the database if a disk fails - are simply copies of redo log files.

Archived redo log files

- copy redo log files to a designated storage device after a log switch has occurred - can collect transaction redo data and transmit that data to standby destinations -processes exist only when the database is in ARCHIVELOG mode and automatic archiving is enabled, -The database starts multiple archiver processes as needed to ensure that the archiving of filled online redo logs does not fall behind.

Archiver Process

It is a self-diagnostic engine built into Oracle Database. It examines and analyzes data captured in the Automatic Workload Repository (AWR) to determine possible performance problems in Oracle Database. - It determine how any identified problems can be resolved and periodically evaluates the performance of your data

Automatic Database Diagnostic Monitor (ADDM)

Shared storage

Clustered System

- Database role existing the root and in every existing and future pluggable database. - Are useful for cross-container operations, ensuring that a common user has a role in every container

Common role

communication between a user process and an instance

Connection

rules that help preserve data integrity

Constraints

- record activity and change - record sequence It is a small binary file used to keep track of the database's status and physical structure. The control file of an Oracle Database is created at the same time as the database. The control file includes: The database name Names and locations of associated data files and redo log files The timestamp of the database creation The current log sequence number Checkpoint information

Control files

View displays all relevant information in the entire database views are intended only for administrators.

DBA_

• Guides you through the upgrade process and configures your database for the new release • Does not begin the upgrade until it completes the pre-upgrade steps • Fully compliant with Oracle RAC • Supports a silent mode operation

DBUA

- store the database information that a firm or organization needs in order to operate

Data files

- largest portion of the SGA - holds copies of data blocks that are read from the data files - is shared by all concurrent users - one of the critical parts

Database Buffer Cache

before data stored in the database can be queried or modified, it must be read from a disk and stored in the DB buffer cache

Database buffer cache

- a pointer that defines a one-way communication path from one Oracle Database server to another database server -The link pointer is actually defined as an entry in a data dictionary table. - schema object in one database that points to another oracle database - used in a distributed database environment

Database links

- can be changed while database is online - can be altered at session and system level - are valid for duration of session or based on SCOPE - are changed by using ALTER SESSION * ALTER SYSTEM - account for about 234 parameters

Dynamic Parameters

- Oracle contains a set of underlying views that are maintained by the database server and accessible ONLY to the database administrator user SYS - monitor ongoing database activity and are continuously updated while a database is open and in use, and their contents relate primarily to performance - start with the characters V$

Dynamic Performance Views

- It is an Oracle-managed directory, file system, or Oracle Automatic Storage Management disk group that provides a centralized storage location for backup and recovery files. - Oracle creates archived logs and flashback here.

Fast Recovery Area

- includes all used blocks of the data file -can be either an image copy or backup set.

Full Backups

exact byte-for-byte copies of files

Image copy

- copies only those blocks in a data file that change between backups -This backups can be either level 0 or level 1

Incremental Backup

• Optional schema objects that are associated with tables • To improve query performance • Quickly locate specific information • Provides quick access to table data

Indexes

- used by the oracle instance at startup to determine the runtime properties and resources for the database

Initialization Parameter File

is the combination of memory and background processes that are part of a running installation. Background processes are involved in running the Oracle software itself. Server processes negotiate the actions of the users. User processes commonly work outside the database server itself to run the application that accesses the database.

Instance

- individual patches

Interim Patches

Log Switches

It is the point at which the database stops writing to one redo log file and begins writing to another. Normally, it will occurs when the current redo log file is completely filled and writing must continue to the next redo log file. However, you can configure log switches to occur at regular intervals, regardless of whether the current redo log file is completely filled. You can also force log switches manually.

- is used to store all session-specific Java code in and data in the JVM.

Java Pool

- provides large memory allocation for: > session memory for the shared server and the oracle XA interface

Large pool

- used as a starting point for an incremental backup strategy -copies all blocks containing data, backing the data file up into a backup set just as a full backup would.

Level 0 incremental backup

- copies only images of blocks that have changed since the previous level 0 or level 1 incremental backups

Level 1 incremental backup

• Listen for incoming client connections

Listeners

- exists only in a single pluggable device - can only contain roles and privileges that apply within the container in which the role exists

Local role

- provide graphical analysis of total memory target settings, SGA and PGA target settings, or SGA component size settings

Memory advisors

- consist of structural, configuration, and control information about the database

Metadata

a simple name for a service that resolves to a connect descriptor

Naming

resolves a connect identifier to a connect descriptor when attempting to connect to a database service

Naming methods

Local storage

Non-clustered System

- enables users to across and manipulate a specific object

Object privileges

- enables you to upgrade an existing Oracle ASM instance to the current software level

Oracle ASM Configuration Assistant

A relational database with object and XML capabilities • Enables you to store data, update it, and efficiently retrieve it, with a high degree of performance, reliability, and scalability

Oracle Database

• Enables you to store data, update it, and efficiently retrieve it, with a high degree of performance, reliability, and scalability

Oracle Database

Log Sequence Numbers

Oracle Database assigns each redo log file a new log sequence number every time a log switch occurs and LGWR begins writing to it. When the database archives redo log files, the archived log retains its log sequence number. A redo log file that is cycled back for use is given the next available log sequence number.

- Backup and recovery refers to the process of backing up data in case of a loss and setting up systems that allow that data recovery due to data loss. ... Data from an earlier time may only be recovered if it has been backed up. Data backup is a form of disaster recovery and should be part of any disaster recovery plan. - focuses on the physical backup of data files which permits you to reconstruct (restore and recover) your database

Oracle Database backup and recovery

- a web-based database management tool that is built inside the Oracle Database. It supports key performance management and basic database administration functions.

Oracle Enterprise Manager Database Express

- simplifies the administration of Oracle Database

Oracle Managed Files

- a software layer that enables client applications and Oracle Database to communicate over a network.

Oracle Net

- a process that listens for client connection requests.

Oracle Net Listener

What is an Oracle connection?

Oracle Net is a software layer that resides on the client and the Oracle Database server. It is responsible for establishing and maintaining the connection between the client application and server, as well as exchanging messages between them, using industry-standard protocols.

provides an open comprehensive, integrated approach to information management A relational database refers to a database that stores data in a structured format, using rows and columns.

Oracle RDBMS

graphical version of SQL*Plus that gives database administrators a convenient way to perform basic tasks involving schema objects

Oracle SQL Developer

• In general, it reliably manages a large amount of data in a multi-user environment so that many users can concurrently access the same data.

Oracle Server

used to specify parameters for configuring an Oracle Instance when it starts up.

Parameter file

authenticates special users of the database - these are termed privilege users and include database administrators.

Password file

- created to fix the problem

Patches

- performs process recovery when a user process fails - cleans up the database buffer cache - monitors sessions for idle session timeout

Performance Monitor (PMON)

- analyze used privileges to revoke unnecessary privileges

Privilege analysis

Are a set of resource limits, used to limit system resources a user can use. It allows us to regulate the amount of resources used by each database user by creating and assigning profiles to them

Profiles

- memory regions that contain data and control information for a server or background process - nonshared memory created by Oracle database when a server or background process is shared.

Program Global Area (PGA)

- It is an Oracle utility that can back up, restore, and recover database files. - A command-line tool and method preferred by Oracle for efficiently backing up and recovering your Oracle database. - It provides database backup, restore, and recovery capabilities addressing high availability and disaster recovery concerns.

RMAN

- automatically connects to other databases involved in in-doubt distributed transactions - automatically resolves all in-doubt transactions - removes any rows that corresponds to in-doubt transaction

Recoverer Process

Active (Current) and Inactive Redo Log Files

Redo Log files

- a circular buffer in the SGA - holds information about changes made to the database - contains redo entries that have the information to redo changes made by operations such as DDL and DML

Redo log buffer

Inactive Redo Log Files

Redo log files that are no longer required for instance recovery are called inactive redo log files. If you have enabled archiving (the database is in ARCHIVELOG mode), then the database cannot reuse or overwrite an active online log file until one of the archiver background processes (ARCn) has archived its contents.

• Used to group together privileges and roles • Facilitate granting of multiple privileges or roles to users

Roles

✓ New connections to the database are denied. ✓ Existing transactions are not rolled back. ✓ User sessions are terminated. ✓ SGA is shut down. ✓ Background processes are terminated. ✓ Specified by the SHUTDOWN ABORT command. ✓ Instance recovery is required on startup.

SHUTDOWN ABORT

✓ New connections to the database are denied. ✓ Existing transactions are rolled back. ✓ User sessions are terminated. ✓ Database file headers are updated and files are closed. ✓ SGA is shut down. ✓ Background processes are terminated. ✓ Specified by the SHUTDOWN IMMEDIATE command.

SHUTDOWN IMMEDIATE

✓ New connections to the database are denied. ✓ Existing transactions continue normally until either they roll back or commit. ✓ Users log out normally on their own. ✓ After the last user logs out, database file headers are updated and files are closed. ✓ SGA is shut down. ✓ Background processes are terminated. ✓ Specified by the SHUTDOWN or SHUTDOWN NORMAL command.

SHUTDOWN NORMAL

✓ New connections to the database are denied. ✓ Existing transactions continue normally until either they roll back or commit. ✓ After an existing transaction is completed, user sessions are terminated. ✓ Database file headers are updated and files are closed. ✓ SGA is shut down. ✓ Background processes are terminated. ✓ Specified by the SHUTDOWN TRANSACTIONAL command.

SHUTDOWN TRANSACTIONAL

- industry standard English-like computer programming language for querying and updating databases

SQL

- contains additional SQL statistics that are specific to the SQL statement and enable the query optimizer to generate a significantly better execution plan at runtime.

SQL Profile

• Analyzes one or more SQL statements and makes recommendations for improving performance • Examines a given SQL statement

SQL Tuning Advisor

- a command-line program that you use to submit SQL and PL/SQL statements to an Oracle database.

SQL*Plus

• Granted the DBA role • Has all privileges with ADMIN option • Is required for startup, shutdown, and some maintenance commands • Owns the data dictionary and AWR

SYS

- used for additional table components - For objects that are auxiliary and not specifically tied to the core features of the database.

SYSAUX

- used for core functionality

SYSTEM

- collection of database objects that are owned by a database user - has the same name as the user account

Schema

a logical container for the database objects that the user creates.

Schema

is a database object that generates unique integers

Sequences

: connects to the Oracle instance and is started when a user establishes a session Oracle Database creates server processes to handle the requests of user processes connected to an instance. A server process can be either of the following: A dedicated server process, which services only one user process. A shared server process, which can service multiple user processes.

Server process

specific connection of a user to an instance through a user process. when a user is authenticated by the database with a password, a session is established for this user. A session lasts from the time the user is authenticated by the database until the time the user disconnects or exits the database application.

Session

- portion of the SGA - contains: Library cache > Shared SQL area Data dictionary cache Control structures

Shared Pool

they change infrequently, only when a change is made to the data dictionary

Static Data Dictionary Views

- can be changed only in the parameter file - account for about 110 parameters - require restarting the instance before taking effect

Static parameters

- used exclusively by Oracle Streams to: > store buffered queue messages > provides memory for Oracle Streams processes

Streams memory

- An alias (alternative name) for any schema object such as table or view sequences, procedures, and stored function. generally used when you are granting access to an object from another schema and you don't want the users to have to worry about knowing which schema owns the object.

Synonyms

- a group of shared memory structures

System Global Area (SGA)

- contain data and control information for one oracle database instance

System Global Area (SGA)

- performs recovery at instance startups

System Monitor (SMON)

- enables users to perform any particular actions in the database

System privileges

the basic unit of storage in an Oracle Database. It holds all user-accessible data.

Table

It's database is the database to which you are moving the new changes. This database that must be backed up or restored using RMAN

Target database

Background processes

They asynchronously perform I/O and monitor other Oracle database processes to provide increased parallelism for better performance and reliability. A background process is defined as any process that is listed in V$PROCESS and has a non-null value in the pname column.

- Stores the rollback or undo segments used for transaction recovery

UNDO

- displays all the information from the schema of the current user. - includes metadata about schema objects created by the user, grants made by the user, and so on

USER_

- the application or tool that connects to the Oracle Database

User process

• Customized presentations of data in one or more table or other views

Views

-started when an Oracle instance in started -They asynchronously perform I/O and monitor other Oracle database processes to provide increased parallelism for better performance and reliability. -It is defined as any process that is listed in V$PROCESS and has a non-null value in the pname column.

background process

- records the checkpoint information in control file, each data file header, crucial for recovery -At specific times CKPT starts a checkpoint request by messaging DBWn to begin writing dirty buffers. On completion of individual checkpoint requests, CKPT updates data file headers and control files to record most recent checkpoint.

checkpoint process

- RMAN backups up all data block changes that are made after a level 0 backup. The main advantage of this over differential backup is the fast recovery time, but at the expense of a longer daily backup window and more disk usage. - Recovery speed: These backups are faster to restore from than differential backups because fewer incremental backups need to be applied during recovery. - Disk space usage: This backups take more disk space because they duplicate the work done by previous backups:

cumulative backup

- writes modified (dirty) buffers in the database buffer cache to disk

database writer process

- Only blocks changed since the most recent level 0 or level 1 In this backup RMAN looks for last level 1 or level 0 backup, and only captures those data block changes that were made after these backups. This backup is faster because there are fewer changes to be stored, but they take longer at recovery time. Backup speed: For daily backup speed, for this backups run faster than cumulative backups because to don't duplicate the work done by previous backups. However, this take longer when doing a recovery.

differential backup

- writes the redo log buffer to a redo log file on disk - Writes when a user process commits a transaction - Writes every 3 seconds

log writer process

Database user creating the synonym, owns it and thus the only one who can access it

private synonym

Synonym That can be accessed by any Database user. Individual user who creates this synonym, does not own it. Rather, it belongs to PUBLIC user group.

public synonym