Database Architecture - Instance & Database

Database Architecture - Instance & Database

• Instance
• Background Processes (CPU)
• Memory structures (RAM)

• Database
• Datafiles (Disk)

Database Architecture - Background Processes

• Part of the Oracle instance
• Align with the CPU resource
• Run as a part of the Oracle Kernel
• Multi-threaded (Windows) vs Multi-process (UNIX & Linux)
• PMON - Process Monitor
  • Regulates all other processes
  • Cleans up dead processes
  • Must be alive
  • View in Linux with ‘ps -ef | grep pmon’
• SMON - System Monitor
  • Responsible for instance recovery
  • cleanup of temporary files
• DBWn - Database Writer
  • No operations done on disk - in memory instead
  • Reads from disk and writes back to disk
  • Multi Processes - DBW0, DBW1, DBW2, ..etc
  • DBWR writes dirty buffers to files when one of the following occurs
    • - dirty list reaches a threshold value
    • - process scans a specified number of blocks in LRU list but doesn't find a free block
    • - timeout occurs
    • - checkpoint occurs
  • Checkpoint forces DBWR to write dirty blocks to datafile BUT writing of dirty blocks to datafile in other scenarios doesn't force any checkpoint
• CKPT - Checkpoint Process
  • Dirty buffer is a buffer whose contents have been modified. Dirty buffers are freed for reuse when DBWR has written the blocks to disk
  • Blocks and buffers - dirty and clean
  • CKPT signals writing of dirty buffers to disk
  • Full and incremental checkpoints
• LGWR - Log Writer Process
  • Recoverability architecture
  • Every change written to files for recovery
  • First memory, then disk as redo logs
• ARCn - Archiver Process
  • NOARCHIVELOG vs ARCHIVELOG mode
  • copies redo logs archivelogs
  • Multi destinations
• Other Background Processes
• MMON - writes out performance metrics
• MMAN - automatically manages memory
• LREG - New process that registers databases

Database Architecture - System Global Area (SGA)

• Part of the Oracle instance
• ‘Everything is a cache’
• Volatile Storage (Memory) Faster than non-volatile storage (disk)
• SGA is system or Shared Global Area
• Shared memory
  • Database Buffer Cache
    • Blocks read into buffers
    • Users require the same data at times
    • Keeps data in cache based on the LRU (Least-Recently-Used)
  • Shared Pool
    • The concept of parsing - expensive operation
    • Library Cache - shared stored SQL
    • Data dictionary cache - stored metadata
  • Log Buffer
    • Log writer process writes changes to memory first
    • three conditions to write data (Logs flashed Out or writes to disk):
      • Commit occurs
      • Log buffers become full
      • Mandatory checkpoint occurs
  • Large Pool
    • Benefits of caching - free up shared pool
    • Used in shared server architecture
    • caching for backup and recovery
  • Streams/Java Pools
    • Streams Pool - support oracle streams
    • Java Pool - can be used to instantiate Java objects

Database Architecture - Program Global Area (PGA)

• Second type of memory
• Private memory - sorting, session variables
• the old way - constrained by number of users (inefficient)
• the new way - a pool of memory (start with oracle 9i)

Database Architecture - Core DataFiles

• Datafiles align with the database portion of the RDBMS
• Transient data vs persistent Data
• Some datafiles ma[ to a tablespace - logical name for physical file
• Datafile = c:\oradata\orcl\data01.dbf
• Tablespace = DATA
• Control File
  • Most important file in oracle database - the ‘brain’
  • Information on the state of the database
  • System Change Number - SCN
  • Multiplexing
• Redo Log File - ‘OnLine Log’
  • Records database changes
  • Used to ‘roll forward’ a database from backup
  • can be written
  • Duplexing
• ArchiveLog File - ‘OffLine Log’
  • ARCHIVELOG mode - full recoverability
  • Redo Logs written to archivelogs
• SYSTEM
  • ‘Heart’ of the database
  • contains the data dictionary
  • Datafile #1
• SYSAUX
  • Automatic Workload Repository - AWR
  • Second Data Dictionary
  • dedicated to Performance Metrics
• Temporary datafiles (tempfiles)
  • Stored data segments that overflow from the PGA
  • Also stored temporary tables
• Undo datafiles
  • Anatomy of a transaction
  • Before-images of data
• Non-specialized datafiles
• Hold application data in the form of tables
• Maximum size 4,194,304 times the size of a database block
• 8 block x 4 = 32GB

Database Architecture - Redo Architecture

• True RDBMS must be designed for recovery
• Incomplete recovery as well as complete recovery
• LGWR process writes to the log buffer, then the redo logs
• Recoverability = Redo Logs + Archive Logs + Controls Files

Database Architecture - Real Application Clusters

• Oracle Clustering solution
• Pay-extra option
• High availability solution
• Scalability solution
• Growing company example

Database Architecture - Container Databases (CDB)

• Acts as the ‘root’ database, contains PDB
• PDB - Pluggable Databases
• Seed - a Template for other PDB

Database Architecture - Pluggable Databases

• Core part of the Oracle 12c multitenant architecture
• Multitenant is a pay-extra option
• Allow for virtualization of databases
• Rapid provisioning and clone of databases
• Container Database (CDB) contains multiple pluggable databases
• Manage many database as one

Benefits

• patch one per CDB, rolls down to PDB
• Provisioning - easily provision new databases
• Seed PDB - default template for databases

• Cloning - clone PDBs in seconds
• Clone between CDBs 

• Migration - migrate data between container databases
• Unplug and Plug-in

 Database Structures - Blocks

• smallest unit of space in an oracle DB
• blocks are read into buffers
• defined by db_block_size parameter
• cannot be changed after creation
• Anatomy
1. Header info (Meta data about columns and rows in block)
2. Free Space
3. Used Space

• Sizing possibilities - 2k-32k
1. 2K - less often used
2. 4K - OLTP
3. 8K - Default and Hybrid
4. 16K - Data Warehousing, binary large objects
5. 32K - less often used

Database Structures - Extents

• composed of blocks
• Managing Extents
  • Dictionary Managed Extents - old way before oracle 9i
  • Locally Managed Extents - oracle self Managed Extents (better performance)

Database Structures - Segments

• Physical analogue of some logical database objects like table or index ..etc
• composed of extents
• structure

• Types of Segments
  1. Table segments - store raw data which store of blocks which form extents which compose segments
  2. Index segments - physical storage structure takes a partition or a column of table to make table more easy querying and discovered and speed performance
  3. Undo segments - to store before image of data “Undo changes” through transaction
  4. Temporary segments - helping memory to store temp data like sorting a table
  5. Table Partition segments - used with table partitions to allow split table to partitions to speed querying big table
  6. Index Partition segments - used with index rely on partitions rather than the table itself
  7. LOB/LOB Index - large objects data types like video, images, mp3 using pointers in table to point to these objects in it’s own segments

• practice:

select * from user_tables where table_name = 'EMP';

select * from user_segments where segment_name = 'EMP';

Database Structures - Datafile

• Datafiles contains segments

• Types of Datafiles
• Non-specific data - application and user data
• Undo Data - within transaction occur ‘data changes’ or before image of data
• Temporary Data (tempfiles) - used when memory full of data

• Limits
  • Max size = 4.194.304 times the block size
    • Eg. 4.194.304 × 8K Block = 32G max size
  • Max number of datafiles in a DB = 65.533
  • Max database size = 8 Petabytes
• Data Dictionary views
  • DBA_DATA_FIles
  • DBA_TEMP_FILES
  • V$DATAFILE
  • V$TEMPFILE
• Practice

$ connect system/oracle

select * from dba_data_files;

select * from v$datafile;

select * from dba_temp_files;

select * from v$tempfile;