1 Tech Sharing Session: Oracle Database Architecture 11g & 12c Versions Arnold Aristotle Tayag 12 Mar 2015 

Contents In Brief... 2  Oracle Database Architecture  Instance Structures  Storage Structures  12c Multitenant Architecture  Database Monitoring (& Tuning) 

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 

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 

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 

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) 

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 

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 

II. Database Instance Structures 9  Instance Memory Structures  Summary of Memory Management Techniques Figure 2 Oracle database memory management modes 

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 

II. Database Instance Structures 11  SHUTDOWN [ NORMAL | TRANSACTIONAL | IMMEDIATE | ABORT ] 

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 

Figure 4 shows the relationship between logical and physical structures 13 

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 

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 

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 

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 

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 

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 

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 

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 

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 

V. Database Monitoring 23 Figure 7 Database Home page of Enterprise Manager 

Thank You...... 24