Oracle Instance: a means to access an Oracle database,always opens one and only one database and consists of memory structures and background process.
Oracle server: a DBMS that provides an open, comprehensive, integrated approach to information management,Consists of an Instance and a database.
Oracle Database: a collection of data that is treated as a unit,Consists of Datafiles, Control files, Redo log files. (optional param file, passwd file, archived log)
Instance memory Structures:
System Global Area (SGA):
Allocated at instance startup, and is a fundamental component of an Oracle Instance.
SGA Memory structures:
Includes Shared Pool, Database Buffer Cache, Redo Log Buffer among others.
Shared Pool:
Consists of two key performance-related memory structures Library Cache and Data Dictionary Cache.
Library Cache:
Stores information about the most recently used SQL and PL/SQL statements and enables the sharing of commonly used statements.
Data Dictionary Cache:
Stores collection of the most recently used definitions in the database Includes db files, tables, indexes, columns etc. Improves perf. During the parse phase, the server process looks at the data dictionary for information to resolve object names and validate access.
Database Buffer Cache:
Stores copies of data blocks that have been retrieved from the datafiles. Everything done here.
Redo Log Buffer:
Records all changes made to the database data blocks, Primary purpose is recovery. Redo entries contain information to reconstruct or redo changes.
User process:
Started at the time a database User requests connection to the Oracle server. requests interaction with the Oracle server, does not interact directly with the Oracle server.
Server process:
Connects to the Oracle Instance and is Started when a
user establishes a session. Fulfills calls generated and returns results.
Each server process has its own nonshared PGA when the
process is started.
Server Process Parses and run SQL statements issued through the application, Reads necessary data blocks from datafiles on disk into the shared database buffers of the SGA, if the blocks are not already present in the SGA and Return results in such a way that the application can process the information.
In some situations when the application and Oracle Database
operate on the same computer, it is possible to combine the user process
and corresponding server process into a single process to reduce system
overhead.
Program Global Area (PGA):
Memory area used by a single Oracle server process.
Allocated when the server process is started, deallocated when the process is terminated and used by only one process.
Used to process SQL statements and to hold logon and other session information.
Background processes:
Started when an Oracle Instance is started.
Background Processes Maintains and enforces relationships between physical and memory structures
There are two types of database processes:
1. Mandatory background processes
2. Optional background processes
Mandatory background processes:
DBWn, PMON, CKPT, LGWR, SMON
Optional background processes:
ARCn, LMDn, RECO, CJQ0, LMON, Snnn, Dnnn, Pnnn, LCKn, QMNn
DBWn writes when:
- Checkpoint occurs
- Dirty buffers reach threshold
- There are no free buffers
- Timeout occurs
- RAC ping request is made
- Tablespace OFFLINE
- Tablespace READ ONLY
- Table DROP or TRUNCATE
- Tablespace BEGIN BACKUP
- Log Writer (LGWR) writes:
- At commit
- When 1/3rd full
- When there is 1 MB of redo
- Every 3 seconds
- Before DBWn writes
- System Monitor (SMON) Responsibilities:
- Instance recovery
- Rolls forward changes in redo logs
- Opens database for user access
- Rolls back uncommitted transactions
- Coalesces free space
- Deallocates temporary segments.
- Process Monitor (PMON) Cleans up after failed processes by:
- Rolling back the transaction
- Releasing locks
- Releasing other resources
- Restarting dead dispatchers
- Checkpoint (CKPT) Responsible for:
- Signaling DBWn at checkpoints
- Updating datafile headers with checkpoint information
- Updating control files with checkpoint information
- Archiver (ARCn)
- Optional background process
- Automatically archives online redo logs when ARCHIVELOG mode is set
- Preserves the record of all changes made to the database
Why do you run orainstRoot and ROOT.SH once you
finalize the Installation?
orainstRoot.sh needs to be run to change the Permissions
and groupname to 770 and to dba.
Root.sh (ORACLE_HOME) location needs to be run to create
a ORATAB in
/etc/oratab or /opt/var/oratab in Solaris and to copy
dbhome, oraenv and coraenv to /usr/local/bin.
orainstRoot.sh
[root@oracle11g ~]# /u01/app/oraInventory/orainstRoot.sh
Changing permissions of /u01/app/oraInventory to 770.
Changing groupname of /u01/app/oraInventory to dba.
The execution of the script is complete
root.sh
[root@oracle11g ~]# /u01/app/oracle/product/11.1.0/db_1/root.sh
Running Oracle 11g root.sh script...
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/app/oracle/product/11.1.0/db_1
Enter the full pathname of the local bin directory: [/usr/local/bin]:
Copying dbhome to /usr/local/bin ...
Copying oraenv to /usr/local/bin ...
Copying coraenv to /usr/local/bin ...
Creating /etc/oratab file...
Entries will be added to the /etc/oratab file as needed
by Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
Finished product-specific root actions.
For Oracle installation on unix/linux, we will be prompted to run a script 'root.sh' from the oracle inventory directory.this script needs to run the first time only when any oracle product is installed on the server.
It creates the additional directories and sets appropriate ownership and permissions on files for root user.
