Here is an illustrated Nutshell FAQ on Oracle explained in-depth using a hands-on approach and constructivist sequence. This crystalizes stacks of books, several classes, thousands of user group entries, and years of hard-won experience.
Topics this page ... Authorization Methods Background Processes Catalog.sql Checkpoint Process (CKPT) Clusters in Tables Control Files Datafiles Data Dictionary Views DSS (Decision Support System) Enqueues Extents Fragmentation Instances Latches Library Cache Log Writer (LGWR) Memory areas OLTP (On-Line Transaction Processing) Packages Parameter File Password Files Performance Views Process Monitor (PMON) Program Global Area (PGA) Processes RMAN Redo Logs Rollback Segments Sequences SID (System Identifier) Shared Pool Shared SQL Pool Sorting Synonyms Server Services Server Manager utility System Global Area (SGA) System Monitor (SMON) Triggers Views
The Oracle Database Management System (DBMS) consists of several components (loosely similar to UNIX's implementation of the multitasking operating system):
OS Environment Variables For Where Oracle Is Installed
Oracle has been designed to allow several versions to be installed on a single machine.
When Oracle Personal Express Edition (XE) is installed, it creates by default folder C:\oraclexe with the folders in the table. Create OS environment variable ORACLE_BASE to hold value "C:\oraclexe".
Different installers use other drive/folder paths.
Envrionment variables enable the root path to be referenced by $ORACLE_BASE within Unix and %ORACLE_BASE% within Windows. For example, within Windows, the file created to detail installation activities can be found at "%ORACLE_BASE%\app\oracle\admin\xe\bdump\alert_xe.log" Copy and paste this on the address bar of Windows Explorer.
"xe" is the database Instance Identifier (SID) for Express Edition. "orcl" is the default db_name value in INIT.ORA for the Enterprise Edition. Create OS environment variable ORACLE_SID to hold SID value "xe" or the instance SID value for your application.
The path to executables (exe and dll files) within the bin folder containing Oracle supplied command line utilities for a Oracle version is stored within OS environment variable $ORACLE_HOME in Unix.
On Windows machines, rather than setting a %ORACLE_HOME% environment variable, Oracle uses the path in Windows Registry key HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE using the Regedit utility
Oracle 11g only requires user specification of ORACLE_BASE, since it creates ORACLE_HOME from it.
Change OS Environment variable PATH to replace the path with variable %ORACLE_HOME%/bin where Oracle executables run from whatever is the current folder. Remember the $ prefix in UNIX and two % in Windows. Since it's a variable, PATH does not need to be changed when the Oracle path needs to change.
To change from using Oracle 10g to Oracle 11g on the same machine, change the value of ORACLE_HOME.
To connect to a network different than the one active while Oracle was installed, change in ...\db_1\NETWORK\ADMIN\listener.ora the default port from 1521 to 1525.
The default SAP installation names Oracle datafiles with a prefix which matches its tablespace name. Rather than the dbf filename suffix, SAP requires datafiles to have names such as "psappooli.data3" for the third datafile for the SAP POOL data indexes.
Background Services/Processes
Oracle installs several services with names starting with "Oracle". In Windows, their status can be viewed (among other services) from Programs > Settings > Control Panel > Administrative Tools > Services (services.msc).
Oracle RDBMS Kernel Executable background process (runs multi-threaded). Unlike UNIX ports of Oracle Database 10g, Oracle Database 10g on Windows is implemented as a single operating system process.
iSQL *Plus Application Server (Enterprise edition only)
OracleMTSRecoveryService
Automatic
omtsreco.exe
MTS Recovery Service
OracleMJobSchedulerXE OracleMJobSchedulerORCL
Disabled
extjob.exe XE
Job Scheduler
OracleDBConsoleord
Automatic
nmesrvc.exe
(Enterprise edition only)
Oracle 10g takes VM Size of 651,540K.
A different service name is assigned to process each instance. Service names contain "XE" for Express Edition, or "ORCL" or "OraDb10g_home1" automatically assigned by the Enterprise edition installer.
Statup is "Automatic" when that the service starts every time Windows boots up. Statup is "Manual" if that service does not start with Windows boots up and requires manual intervention to start.
Executables for running services and the dll files they use (oraclient10.dll, etc. also in the same bin folder) cannot be deleted (to uninstall Oracle) until their service is stopped.
Oracle Enterprise Components
The Enterprise version of Oracle runs additional services for each instance.
Advanced Database architectures include: Oracle Managed Files (OMF) to automate creation and dropping of datafiles and management of redo log and control files; Partitioning of tables by range, list, hash, composition; Replication (not suited to failover and backup); Standby (Failover) Databases, Grid Computing, which consists of OEM Grid Control of Oracle RAC (Real Application Clusters) and Oracle 10g platform. independent Cluster Ready Services (CRS) to handle failover of services to surviving nodes
ORACLE_HOME/bin which starts services OracleService... and ...TNSListener established during Oracle installation.
If the Oracle Enterprise Edition was installed it is assumed that the database just runs.
Initialization with SPFILE or PFILE (INIT.ORA)
Before Oracle10g, when an Oracle instance starts for the first time, it references initialization parameters from a pfile named initSID.ora (INIT.ORA) in %ORACLE_HOME%\database on Windows or $ORACLE_HOME/dbs on Linux.
Since Oracle reads the pfile only when it starts, changes to it (using a Notepad or other text editor) requires the databse to be restarted.
Oracle 10g added a way to change init parameter values dynamically (while the database is running):
ALTER SESSION set parameter = value ; ALTER SYSTEM set parameter = value SCOPE = SPFILE;
SCOPE = SPFILE; applies the next time the database is started (not to the current instance). Use this for static parms. SCOPE = MEMORY; applies to the current instance only, and will not be retained on restart. SCOPE = BOTH; applies to the current instance and when the database starts again.
Oracle 10g introduced the spfile (server parameter file) named spfileSID.ora to retain parms so that they can be applied the next time the database starts.
Dynamic changes to parameters can also be made by Oracle's autotune intelligence.
Since the spfile is open for update as the database runs, it can't be edited like the pfile can. But the spfile can be backed-up to a pfile with a command like:
create pfile=/path/to/backup.ora from spfile;
Oracle 10g by default looks for a spfile. But if a spfile can't be found, Oracle looks for the pfile (as before).
When the database is restarted using spfile, the DBA must login again with proper privileges.
<!-- Freeman, R. and Karam, S. (2006). Oracle concepts