An Oracle Database server consist of two parts: an Oracle instance and an Oracle database
a. Oracle instance: a set of Oracle background processes and shared memory area (System Global Area – SGA). Each running Oracle database is associated with one Oracle instance. Multiple instances can execute concurrently on the same computer, each accessing its own physical database.
b. Oracle database: a collection of physical operating system files or disks.
I. Memory Structure
Program Global Area (PGA): is a memory region that contains data and control information for each server process. Private to each server and background process. There is one PGA for each process.
System Global Area (SGA): shared by all server and background processes, it consist of Share pool, Stream pool, Large pool, Java pool, Database buffer cache, Redo log file.
- Share pool: cache various constructs(e.g SQL, PL/SQL …) that can be shared among users.
- Stream pool: is used by Oracle Streams.
- Large pool: provided large memory allocations for certain large processes, such as Oracle backup and recovery.
- Java pool: is used for all Java session.
- Database buffer cache: is where Oracle stores database blocks before writing then to the disk and after reading them in from disk. All concurrent user processes share access to the database buffer cache.
- Redo log buffer: caches redo information used for recovery until it can be written to the redo logs stored on the disk. LGWR initiates a flush of this area in: every 3 second, whenever someone commits, when LGWR ask to switch log file, red log buffer 1/3 full.
The most common background process:
- System Monitor (SMON): Crash recovery when the instance is started following a failure.
- Process Monitor (PMON): Process cleanup when a user process fails
- Database Writer (DBWn): Writes modified blocks from database buffer cache to the data file on the disk.
- Checkpoint (CKPT): Updates all the data files and control files of the data base to indicate the most recent checkpoint.
- LogWriter(LGWR): Write redo log entires to the disk.
- Archiver(ARCn):: Copies redo log files to the archival storage when a log switch occurs.
II. Physical Structure
Consist of Data files and OS Block. There are eight file types that make up a database and instance.
- Control files: contain data about the database, it tells where the data files, temp files, and redo log files are.
- Data files: These files are for the database, it hold tables, indexes, and all other segments.
- Parameter file: is used to define how the instance is configured (located the control files) when it is starts up.
What are parameters?SYS@orcl AS SYSDBA> SELECT NAME, VALUE FROM V$PARAMETER; NAME VALUE -------------------------------------------------------------------------------------- shadow_core_dump partial background_core_dump partial background_dump_dest /u01/app/oracle/admin/orcl/bdump user_dump_dest /u01/app/oracle/admin/orcl/udump max_dump_file_size UNLIMITED core_dump_dest /u01/app/oracle/admin/orcl/cdump audit_file_dest /u01/app/oracle/admin/orcl/adump
Create PFILE or SPFILE
SYS@ORCL>CREATE SPFILE FROM PFILE
SYS@ORCL>CREATE PFILE='init_orcl.ora' from SPFILE
Setting values in SPFILE
SYS@ORCL>ALTER SYSTEM SET SORT_AREA_SIZE = 6500 DEFERRED
- Online redo log files: Allow for instance recovery of database.
- Password file: Allows user connect remotely to the database and perform administration task.
- Backup files: Are used for database recovery.
- Archive log files: Contain an ongoing history of the data changes that are generated by the instance.
- Trace files: diagnostic files created by server process in response to some exceptional error condition.
- Alert files: These are special trace files. They are also known as alert logs.
single DATABASE -< TABLESPACES -< SEGMENTS -< EXTENTS -< DATA BOCKS
Tablespace
Database is divided into logical storage units called tablespaces. Each database has one or more tablespace. One or more data files are explicitly created for each tablespace to physically store the data of all logical structure in a tablespace.
SYSTEM and SYSAUX tablespace: are created at the time of database creation. They must be online.
Data Blocks
