Oracle Database Architecture by Arnold Aristotle Tayag

The Oracle Database (commonly referred to as Oracle RDBMS or simply as Oracle) is a leading enterprise-grade object-relational database management system that offers secure data management and transaction processing. As the RDBMS of choice of many organizations, including DBS, it is important to have a sound understanding of the internal workings and complexities of the database. This knowledge sharing session on Oracle Database Architecture is intended to provide an architectural and conceptual overview of the Oracle database server - it will explain how the Oracle database server functions, describes the important components, the basic data structures that store data and metadata, and provides some performance best practices for designing, creating, and deploying an initial database for performance, manageability, and high availability.

This will cover the following topics:
I. Elements of the Oracle Database
II. Database Instance Structures
III. Database Storage Structures
IV. Multitenant Architecture (12c Version)
V. Monitoring & Tuning the Database

Transcript

1. 1 Tech Sharing Session: Oracle Database Architecture 11g & 12c Versions Arnold Aristotle Tayag 12 Mar 2015
2. Contents In Brief... 2  Oracle Database Architecture  Instance Structures  Storage Structures  12c Multitenant Architecture  Database Monitoring (& Tuning)
3. I. Oracle Database Architecture 3  An Oracle Database Server consists of a database and at least one database instance (commonly referred to as simply an instance)  Database is a set of files, located on disk, that store user data + metadata. These files can exist independently of a database instance  Database instance is a set of memory structures and processes that manage database files
4. Figure 1 shows a database and its instance. A client process established a connection to the instance. Each client process is associated with its own server process. The server process has its own private session memory, known as PGA 4
5. II. Database Instance Structures 5  The Instance uses memory structures and background processes to manage and access the database  The properties of an instance are specified using instance initialization parameters (stored in a file – initialization parameter file – SPFILE/PFILE)  When applications (Client Process) connect to an Oracle Database, they are connected to the database instance (thru Oracle Net)  Remember to factor in the # of background processes when setting PROCESSES parameter
6. II. Database Instance Structures 6  Oracle Database Processes  Background processes (fundamental)  Database Writer (DBWn)  Log Writer (LGWR)  Checkpoint (CKPT)  Archiver (ARCn)  System Monitor (SMON)  Process Monitor (PMON)  Manageability Monitor (MMON)  Others.....  Server processes  Handle the connection requests of users or client programs  Perform one or more of the following tasks: parse and run SQL statements, PL/SQL code, read data blocks from data files into the buffer cache, return results to the client  Can be either dedicated (Dedicated Server Mode) or shared (Shared Server Mode)
7. II. Database Instance Structures 7  Instance Memory Structures  System Global Area (SGA) is shared by all server & background processes  Database Buffer Cache  Shared Pool  Redo Log Buffer  Large Pool (optional)  Streams Pool (optional)  Java Pool  Others.....  Program Global Area (PGA) is a non-shared memory which contain data & control information about a server process. Background processes also have their own PGA  The sizes of instance memory structures affect database performance and are controlled by initialization parameters
8. II. Database Instance Structures 8  Instance Memory Structures  Memory Management  Automatic Memory Management  Set MEMORY_TARGET, MEMORY_MAX_TARGET  Automatic Shared Memory Management  Set SGA_TARGET, PGA_AGGREGATE_TARGET  Manual Memory Management  You can directly specify memory sizes for the SGA sub- structures  Can still configure automatic PGA memory management  This option is meant for more experienced DBAs  Has Memory Advisors to help in tuning
9. II. Database Instance Structures 9  Instance Memory Structures  Summary of Memory Management Techniques Figure 2 Oracle database memory management modes
10. II. Database Instance Structures 10  Instance startup:  Instance shutdown:  Reverse of instance startup  Shutdown modes: Normal (default), Transactional, Immediate, Abort Figure 3 shows the database progressing from a shutdown state to an open state
11. II. Database Instance Structures 11  SHUTDOWN [ NORMAL | TRANSACTIONAL | IMMEDIATE | ABORT ]
12. III. Database Storage Structures 12  An Oracle database is made up of physical and logical structures  Physical structures can be seen and operated on from the operating system, such as the physical files that store data on a disk  Datafiles, Control File, Password File, Trace Files, etc.  Logical structures are created and recognized by Oracle Database and are not known to the operating system  Tablespace, Segment, Extent, DB Block
13. Figure 4 shows the relationship between logical and physical structures 13
14. III. Database Storage Structures 14 Logical Structures:  Tablespaces  A logical storage unit for grouping related logical structures  Consists of one or more physical data files  Some tablespaces are created at database creation, i.e. SYSTEM, SYSAUX, UNDOTBS1, TEMP, EXAMPLE, USERS  Create tablespaces as locally managed tablespaces with automatic segment space management enabled  SMALLFILE vs. BIGFILE  Permanent, Undo, Temporary  v$Tablespace, Dba_tablespaces, Dba_ts_quotas  Segments, Extents, Data Blocks  Dba_segments, dba_extents
15. III. Database Storage Structures 15 Physical Structures:  Control File  Tracks the physical components of the database. It is the root file that the database uses to find all the other files used by the database  Because of its importance, Oracle recommends that the control file be multiplexed  v$ControlFile, v$ControlFile_Record_Section, v$Parameter  Online Redo Log Files  The online redo log stores a copy of the changes made to data. It is used to recover a database after hardware, software, or media failure  Every Oracle database has a set of two or more online redo log files (called redo log groups) with 1 member each (minimum)  It is good practice to have 2 members per group and maintained on different disks  How many groups you create and the file size of member files can have performance impact  v$Log, v$LogFile, v$Log_History
16. III. Database Storage Structures 16  Archived Redo Log Files  Oracle Database can copy the online redo log files to another location before they are overwritten  This process is only possible if the database is running in ARCHIVELOG mode and the background process ARCn is responsible for archiving operations  v$Database, v$Log, v$Archived_Log  Data Files & Temp Files  Data files are physical files on the operating system that store the data within the database  These data files are attached to logical structures called Tablespaces and can be up to 32Gb each  Temp files are a special class of data files that are associated only with temporary tablespaces. They hold transient data used for multiple sort operations  v$DataFile, Dba_data_files, v$TempFile, Dba_temp_files, Dba_temp_free_space
17. III. Database Storage Structures 17  Undo  Undo data is the original copy of data before it is modified and is stored in the UNDO Tablespace  Undo data is used for: rollback, to provide read consistency, for logical flashback operations  Undo management is transparently handled by Oracle  Can have multiple undo tablespaces, but only one can be active for an instance  Can specify UNDO_RETENTION period  v$Tablespace, Dba_tablespaces, v$Undostat  Mechanism for Storing Database Files  Several mechanisms are available for allocating and managing the storage of these files. The most common mechanisms include:  OS File System  Oracle Automatic Storage Management (ASM)  Cluster File System
18. IV. Multitenant Architecture (12c only) 18  Multitenant Container Database (CDB) / Pluggable Database (PDB)  A CDB is a single physical database that contains zero, one, or many user–created pluggable databases (PDB)  A PDB is a portable collection of schemas, schema objects, and non–schema objects  Architecture difference between CDB & non–CDB  This Multitenant Architecture provides the following benefits:  Cost reduction for hardware  Easier migration of data and code  Easier testing of applications  Easier management and monitoring of the physical database  Secure separation of administrative duties  Ease of performance tuning  Fewer database patches and upgrades
19. IV. Multitenant Architecture (12c only) 19 Figure 5 depicts 11 databases, each with its own application and server. A head DBA oversees a team of four DBAs, each of whom is responsible for two or three databases
20. IV. Multitenant Architecture (12c only) 20 Figure 6 depicts the same databases after consolidation onto one computer. The DBA team is reduced from five to three, with one CDB administrator managing the CDB while two PDB administrators split management of the PDBs
21. V. Database Monitoring 21  Proactive Monitoring  Oracle provides comprehensive tools and utilities for database monitoring:  Enterprise Manager (EM) is an easy to use interface for diagnosing performance issues . It provides framework with several metrics for monitoring OS, Server, Database, storage and network monitoring. Note that some advance features of EM may require additional licensing  Database Control (DB Control) is free tool provided for database monitoring and is suited for use in small environments  Tools for Tuning  The above mentioned tools are useful for database and server monitoring. For diagnosing and identifying specific performance issues, the following tools can be used:  ADDM  ASH
22. V. Database Monitoring 22  Tools for Tuning  The database periodically collects snapshot of the database state & workload and stores this in AWR  Collection period is every 1 hour and has a retention period of 8 days (default settings)  DBA_HIST_WR_CONTROL  These snapshots are used for performance analysis by ADDM
23. V. Database Monitoring 23 Figure 7 Database Home page of Enterprise Manager
24. Thank You...... 24