Oracle Database Architecture and Administration

Posted on Oct 6, 2024 in Computers

Disk Access Speed vs. RAM Access Speed

· RAM ~ 10^-9 seconds
· Disk ~ 10^-3 seconds

RMAN Backup and Recovery

RMAN (Recovery Manager) handles the management of backups and restoring data files, archive logs, and control files. It can be used for complete or incomplete recovery of a database.

GRANT, REVOKE, System Privileges, and Object Privileges

System privileges allow users to perform specific actions in the database, such as creating a tablespace.

Object privileges allow users to access and manipulate database objects.

Views for Privilege Information:

  • DBA_SYS_PRIVS
  • SESSION_PRIVS
  • DBA_TAB_PRIVS
  • DBA_COL_PRIVS

Database Views

A view is a logical, not physical, table based on other tables or views. It acts as a window to view or modify data in the underlying tables.

Key Characteristics:

  • Contains no data itself.
  • Based on underlying tables called base tables.
  • Stored as a named SELECT statement in the data dictionary.

Uses of Views:

  • Restrict database access by displaying only specific data.
  • Simplify complex queries by using views instead of multiple joins.
  • Provide data independence.
  • Offer different perspectives of the same data for different users or applications.
  • Control access for user groups based on specific criteria.

Oracle Architecture

What is a Database?

A database is a system for storing and organizing large amounts of information. It consists of data stored on disks and programs that manipulate that data.

What is an Instance?

An Oracle instance comprises processes and shared memory spaces needed to access database information. It includes user processes, background Oracle processes, and shared memory.

Control File

A small binary file that:

  • Defines the real-time status of the physical database.
  • Maintains database integrity.
  • Is crucial for database mounting and operation.
  • Is associated with only one database.
  • May require database recovery if lost.
  • Has an initial size defined during database creation.

Datafile

A physical representation of a tablespace, storing database information.

ACID Properties

ACID properties ensure transaction reliability and data integrity:

  • Atomicity: Transactions are treated as a single unit, either fully completed or fully rolled back.
  • Consistency: Transactions move the database from one consistent state to another, adhering to integrity rules.
  • Isolation: Transactions are isolated from each other to prevent interference.
  • Durability: Once a transaction is committed, changes are permanently saved, even after system failures.

Undo Tablespaces, Rollback Segments, and Parameters

Undo Tablespace

An undo tablespace stores old data values (undo data) when a process modifies information in the database. It tracks modified data locations and their previous states.

Rollback Segments

Internal database objects that facilitate the rollback of uncommitted transactions, ensuring read consistency. Rollback segment records include:

  • Transaction identifier
  • Table block address
  • Row number
  • Column number
  • Old data value (before modification)

Parameters and the INIT.ORA File

The init.ora file contains Oracle system parameters used by the DBA to configure and start the database. It defines settings like SGA size, control file locations, and more.

Types of Database Shutdown

  • Normal Shutdown: Prevents new connections, waits for existing requests to complete, and then shuts down the database gracefully.
  • Immediate Shutdown: Terminates active transactions, rolls back uncommitted changes, and shuts down the database immediately. User connections are terminated abruptly.
  • Abort Shutdown: Forces an immediate shutdown without waiting for transactions to complete or rolling back changes. Requires database recovery upon restart.

Types of Database Startup

  • Normal: Starts the database in normal operating mode.
  • Nomount: Starts the instance without mounting the database.
  • Mount: Mounts the database but does not open it.
  • Open: Opens the database for normal operations.
  • Restrict: Opens the database in restricted mode, allowing only users with special privileges to connect.

SQL*Loader

A utility for loading data from external files (typically text files, but can be binary) into an Oracle database.

Tablespace Structures

Tablespaces store database schema objects (tables, indexes, etc.) and consist of one or more datafiles.

Key Points:

  • A database has multiple tablespaces.
  • A tablespace has multiple datafiles.
  • A datafile belongs to only one tablespace.
  • A tablespace belongs to only one database.

Types of Tablespaces

  • SYSTEM Tablespace: Created with the database, contains the data dictionary, stored program units, and the SYSTEM undo segment.
  • SYSAUX Tablespace: Auxiliary tablespace for storing Oracle database metadata and supporting infrastructure.
  • TEMP Tablespace: Used for temporary storage during query execution and sorting.
  • UNDO Tablespace: Stores undo information for rollback and recovery.
  • USERS Tablespace: Default tablespace for user objects.
  • EXAMPLE Tablespace: Contains sample objects and schemas for demonstration purposes.

Parameter Files

:
Static parameter file, PFILE, commonly referred to as
init% SID%. ora. This file can be edited with any text editor operating system.
Persistent parameter file (binary), SPFILE, commonly referred to as spfile% SID%. Ora. This file should be modified with Oracle commands.

What are the steps to create a manual BD, BD command to create a manual (create database) … steps to create a DB manually, namely that states are the commands (the create database is in the state nomount … since the control file must be closed).

You can create a database by following these steps:
1. Decide on a unique instance name% SID%, for example: test
2. Set ORACLE_SID environment variable, eg on Linux:
$ ORACLE_SID = test; export ORACLE_SID
3. Create the parameter file init% SID%. Ora, eg inittest.ora.
In the parameter file to define the block size will be based
data, in this example: db_block_size = 8192
4. Determine the directory structure that will be needed for files
of the database trace files, alert files, etc..
5. Create password file for remote administration of the instance, in the
example orapwtest
6. Start the instance nomount mode.
7. Prepare a CREATE DATABASE
8. From sqlplus as user SYS with SYSDBA role of run creation
database (CREATE DATABASE)
9. Create the catalog views (dba_, ALL_, user_id and dynamic views V $ with
its synonyms. (running the script catalog.sql)
10. Install procedural option PL / SQL. (running the script catproc.sql)
11. Add files or log additional control, should this become necessary.
12. Particularize the database structure if needed.

How can we determine the number of extensions and the size of a segment?

To set the size of future extensions that will be requesting a segment uses several parameters which must be given value at the time of the creation of a table, index, or rollback segment. These parameters are indicated on the STORAGE clause of the sentence which created the segment and are as follows:

· Initial: Indicates the size in bytes of the first extension to be the segment.
· Next: Indicates the size of the next extension will be created when there is no more room in the extensions already assigned the segment.
· Pctincrease: When allocating a new extension to the segment, is recalculated value will be the next to be assigned and will be, the size of the newly created extension (which was the next segment) increased by the percentage given by pctincrease.
· MINEXTENTS: It indicates the number of extensions can also be booked at once for a given segment at the time of its creation.
· MAXEXTENTS: The maximum number of extensions that can be created in the object, counting also the first.

Storage

Any object that has physical occupation in the database will have a storage clause in the syntax of creation. The purpose of this clause is to define certain properties of storage for the object created, such as size of the initial extension, size of the following extensions

v $ log: Shows the active redo.
v $ logfile: Displays redologs groups and belonging to such groups.
v $ datafile: Displays the datafiles that owns the database.
v $ CONTROLFILE: Shows current location CONTROLFILE.
dba_users: Displays information related to users of the database.
dba_profiles: Display information on all profiles and their limits.
dba_tablespaces: Contains a description of all tablespaces.
dba_tables: Contains a description of all tables in the relational database.
dba_sys_privs: Shows system privileges given to users and roles.
dba_role_privs: Lists the roles granted to roles and users.