What is an Oracle Instance?

What is an Oracle Instance?
by

1 Answer

Answer :

An Oracle database server consists of an Oracle database and an Oracle instance. Every time a database is started,  a system global area (SGA) is allocated and Oracle background processes are started. The combination of the  background processes and memory buffers is called an Oracle instance. We can run multiple instances on the same  Oracle Database Server, where each instance connects to its database.
Oracle instance includes:
SGA - System or Shared Global Area
Components of SGA:
▪ DBBC - Database Buffer Cache
▪ SP - Shared Pool; divided into Library Cache (LC) and Data Dictionary Cache (DDC) or Row Cache. ▪ RLB - Redo Log Buffer
Background Process (10/11g database):
Mandatory Processes
▪ SMON - System Monitor
▪ PMON - Process Monitor
▪ DBWR - Database writer
▪ LGWR - Log Writer
▪ CKPT - Check point
▪ RECO - Recoverer
▪ DIAG - Diagnosability (new in 11g)
▪ VKTM - Virtual keeper of time (keeps "SGA Time" variable in current, new in 11g)
Optional Process
▪ ARCN - Archiver
▪ MMAN - Memory Manager - ASMM
▪ MMON - Memory Monitor
▪ MMNL - Memory Monitor Light - AWR
and few more...
List of running processes of a single instance (11g) on Linux:
[oracle@hostname ~]$ top -n 1 -U oracle -c
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
9181 oracle 15 0 745m 111m 109m S 6.9 7.5 1:11.15 ora_j000_DB1_SID
9163 oracle 16 0 745m 68m 65m S 5.3 4.6 0:11.95 ora_j001_DB1_SID
10420 oracle 18 0 744m 12m 11m R 3.0 0.8 0:00.09 ora_j002_DB1_SID
6773 oracle 16 0 748m 40m 34m S 0.7 2.7 0:03.16 ora_dbw0_DB1_SID
6775 oracle 16 0 759m 34m 33m S 0.7 2.4 0:10.74 ora_lgwr_DB1_SID
6767 oracle 15 0 744m 13m 11m S 0.3 0.9 0:02.17 ora_psp0_DB1_SID
6785 oracle 15 0 744m 19m 18m S 0.3 1.3 0:02.66 ora_mmnl_DB1_SID
6560 oracle 18 0 42048 9348 6788 S 0.0 0.6 0:00.86 tnslsnr LISTENER -inherit
6755 oracle 15 0 744m 16m 14m S 0.0 1.1 0:02.50 ora_pmon_DB1_SID
6757 oracle -2 0 744m 13m 11m S 0.0 0.9 0:04.31 ora_vktm_DB1_SID
6761 oracle 15 0 744m 13m 11m S 0.0 0.9 0:00.34 ora_gen0_DB1_SID
6763 oracle 18 0 744m 12m 11m S 0.0 0.9 0:00.53 ora_diag_DB1_SID
6765 oracle 15 0 744m 19m 18m S 0.0 1.3 0:00.59 ora_dbrm_DB1_SID
6769 oracle 18 0 744m 16m 14m S 0.0 1.1 0:07.11 ora_dia0_DB1_SID
6771 oracle 18 0 744m 17m 16m S 0.0 1.2 0:11.13 ora_mman_DB1_SID
6777 oracle 16 0 744m 16m 14m S 0.0 1.1 0:08.51 ora_ckpt_DB1_SID
Prepared by: Nasser 5 | P a g e
6779 oracle 15 0 748m 87m 84m S 0.0 5.9 0:04.61 ora_smon_DB1_SID
6781 oracle 18 0 744m 18m 17m R 0.0 1.3 0:00.52 ora_reco_DB1_SID
6783 oracle 15 0 748m 56m 51m S 0.0 3.8 0:06.01 ora_mmon_DB1_SID
6787 oracle 15 0 744m 13m 11m S 0.0 0.9 0:00.35 ora_d000_DB1_SID
6789 oracle 15 0 744m 12m 11m S 0.0 0.8 0:00.31 ora_s000_DB1_SID
6852 oracle 18 0 744m 14m 13m S 0.0 1.0 0:00.40 ora_qmnc_DB1_SID
6859 oracle 15 0 744m 25m 23m S 0.0 1.7 0:00.53 ora_q000_DB1_SID
6864 oracle 18 0 744m 15m 14m S 0.0 1.0 0:00.21 ora_q001_DB1_SID
6983 oracle 15 0 748m 54m 48m S 0.0 3.7 0:05.40 ora_cjq0_DB1_SID
7141 oracle 15 0 744m 13m 12m S 0.0 0.9 0:00.26 ora_smco_DB1_SID
7722 oracle 16 0 753m 58m 55m S 0.0 4.0 0:07.64 oracleDB1_SID (LOCAL=NO) 10254 oracle 15 0 744m 14m 12m S 0.0 1.0 0:00.10 ora_w000_DB1_SID
An Oracle database server consists of an Oracle database and an Oracle instance. Every time a database is started,  a system global area (SGA) is allocated and Oracle background processes are started. The combination of the  background processes and memory buffers is called an Oracle instance. We can run multiple instances on the same  Oracle Database Server, where each instance connects to its database.
Oracle instance includes:
SGA - System or Shared Global Area
Components of SGA:
▪ DBBC - Database Buffer Cache
▪ SP - Shared Pool; divided into Library Cache (LC) and Data Dictionary Cache (DDC) or Row Cache. ▪ RLB - Redo Log Buffer
Background Process (10/11g database):
Mandatory Processes
▪ SMON - System Monitor
▪ PMON - Process Monitor
▪ DBWR - Database writer
▪ LGWR - Log Writer
▪ CKPT - Check point
▪ RECO - Recoverer
▪ DIAG - Diagnosability (new in 11g)
▪ VKTM - Virtual keeper of time (keeps "SGA Time" variable in current, new in 11g)
Optional Process
▪ ARCN - Archiver
▪ MMAN - Memory Manager - ASMM
▪ MMON - Memory Monitor
▪ MMNL - Memory Monitor Light - AWR
and few more...
List of running processes of a single instance (11g) on Linux:
[oracle@hostname ~]$ top -n 1 -U oracle -c
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
9181 oracle 15 0 745m 111m 109m S 6.9 7.5 1:11.15 ora_j000_DB1_SID
9163 oracle 16 0 745m 68m 65m S 5.3 4.6 0:11.95 ora_j001_DB1_SID
10420 oracle 18 0 744m 12m 11m R 3.0 0.8 0:00.09 ora_j002_DB1_SID
6773 oracle 16 0 748m 40m 34m S 0.7 2.7 0:03.16 ora_dbw0_DB1_SID
6775 oracle 16 0 759m 34m 33m S 0.7 2.4 0:10.74 ora_lgwr_DB1_SID
6767 oracle 15 0 744m 13m 11m S 0.3 0.9 0:02.17 ora_psp0_DB1_SID
6785 oracle 15 0 744m 19m 18m S 0.3 1.3 0:02.66 ora_mmnl_DB1_SID
6560 oracle 18 0 42048 9348 6788 S 0.0 0.6 0:00.86 tnslsnr LISTENER -inherit
6755 oracle 15 0 744m 16m 14m S 0.0 1.1 0:02.50 ora_pmon_DB1_SID
6757 oracle -2 0 744m 13m 11m S 0.0 0.9 0:04.31 ora_vktm_DB1_SID
6761 oracle 15 0 744m 13m 11m S 0.0 0.9 0:00.34 ora_gen0_DB1_SID
6763 oracle 18 0 744m 12m 11m S 0.0 0.9 0:00.53 ora_diag_DB1_SID
6765 oracle 15 0 744m 19m 18m S 0.0 1.3 0:00.59 ora_dbrm_DB1_SID
6769 oracle 18 0 744m 16m 14m S 0.0 1.1 0:07.11 ora_dia0_DB1_SID
6771 oracle 18 0 744m 17m 16m S 0.0 1.2 0:11.13 ora_mman_DB1_SID
6777 oracle 16 0 744m 16m 14m S 0.0 1.1 0:08.51 ora_ckpt_DB1_SID
Prepared by: Nasser 5 | P a g e
6779 oracle 15 0 748m 87m 84m S 0.0 5.9 0:04.61 ora_smon_DB1_SID
6781 oracle 18 0 744m 18m 17m R 0.0 1.3 0:00.52 ora_reco_DB1_SID
6783 oracle 15 0 748m 56m 51m S 0.0 3.8 0:06.01 ora_mmon_DB1_SID
6787 oracle 15 0 744m 13m 11m S 0.0 0.9 0:00.35 ora_d000_DB1_SID
6789 oracle 15 0 744m 12m 11m S 0.0 0.8 0:00.31 ora_s000_DB1_SID
6852 oracle 18 0 744m 14m 13m S 0.0 1.0 0:00.40 ora_qmnc_DB1_SID
6859 oracle 15 0 744m 25m 23m S 0.0 1.7 0:00.53 ora_q000_DB1_SID
6864 oracle 18 0 744m 15m 14m S 0.0 1.0 0:00.21 ora_q001_DB1_SID
6983 oracle 15 0 748m 54m 48m S 0.0 3.7 0:05.40 ora_cjq0_DB1_SID
7141 oracle 15 0 744m 13m 12m S 0.0 0.9 0:00.26 ora_smco_DB1_SID
7722 oracle 16 0 753m 58m 55m S 0.0 4.0 0:07.64 oracleDB1_SID (LOCAL=NO) 10254 oracle 15 0 744m 14m 12m S 0.0 1.0 0:00.10 ora_w000_DB1_SID
by

Related questions

What is an Oracle Instance?

Description : What is an Oracle Instance?

Last Answer : The Oracle system processes, also known as Oracle background processes, provide functions for the user processes-functions that would otherwise be done by the user processes themselves Oracle database- ... combination of the SGA and the Oracle background processes is known as an Oracle instance

Give the stages of instance startup to a usable state where normal users may access it.

Description : Give the stages of instance startup to a usable state where normal users may access it.

Last Answer : STARTUP NOMOUNT - Instance startup STARTUP MOUNT - The database is mounted STARTUP OPEN - The database is opened

What is Restricted Mode of Instance Startup ?

Description : What is Restricted Mode of Instance Startup ?

Last Answer : An instance can be started in (or later altered to be in) restricted mode so that when the database is open connections are limited only to those whose user accounts have been granted the RESTRICTED SESSION system privilege.

What is a Database instance ? Explain

Description : What is a Database instance ? Explain

Last Answer : A database instance (Server) is a set of memory structure and background processes that access a set of database files. The process can be shared by all users. The memory structure that are ... helps up to improve database performance by decreasing the amount of I/O performed against data file.

What is an Instance?

Description : What is an Instance?

Last Answer : a) The term instance is typically used to describe a complete database environment, including the RDBMS software, table structure, stored procedures and other functionality. It is most commonly used ... just collection of background processes and memory structures, used to tap in to the database.

What is Instance Recovery?

Description : What is Instance Recovery?

Last Answer : While Oracle instance fails, Oracle performs an Instance Recovery when the associated database is being re started. Instance recovery occurs in two steps: Cache recovery: Changes being made to ... undo data stored in undo segments to accomplish this. This process is called transaction recovery.

How to add conditions in select statement in oracle?

Description : How to add conditions in select statement in oracle?

Last Answer : Just add the decode

How can I dump an oracle schema to a LOCAL file over a network connection from sqlplus?

Description : How can I dump an oracle schema to a LOCAL file over a network connection from sqlplus?

Last Answer : By connecting and spooling are you excluding the exp/imp commands (or expdb/impdb in 10g)?

What are the two ways to incorporate images into a oracle forms application?

Description : What are the two ways to incorporate images into a oracle forms application?

Last Answer : Boilerplate Images Image_items

How does one start the Oracle Intelligent Agent? (for DBA

Description : How does one start the Oracle Intelligent Agent? (for DBA

Last Answer : One needs to start an OIA (Oracle Intelligent Agent) process on all machines that will to be managed via OEM. For OEM 9i and above:

What is the Oracle Intelligent Agent? (for DBA

Description : What is the Oracle Intelligent Agent? (for DBA

Last Answer : The Oracle Intelligent Agent (OIA) is an autonomous process that needs to run on a remote node in the network to make the node OEM manageable. The Oracle Intelligent Agent is ... events registered in Enterprise Manager; and . Executing tasks associated with jobs submitted to Enterprise Manager.

Are there any undocumented commands in Oracle? (for DBA

Description : Are there any undocumented commands in Oracle? (for DBA

Last Answer : Sure there are, but it is hard to find them. Look at these examples: From Server Manager (Oracle7.3 and above): ORADEBUG HELP It looks like one can change memory locations with the ORADEBUG POKE ... rdbms/lib/oradbx.o; make -f oracle.mk oradbx) SQL*Plus: ALTER SESSION SET CURRENT_SCHEMA = SYS;

How does one tune Oracle Wait events? (for DBA

Description : How does one tune Oracle Wait events? (for DBA

Last Answer : Some wait events from V$SESSION_WAIT and V$SYSTEM_EVENT views: Event Name: Tuning Recommendation: db file sequential read Tune SQL to do less I/O. Make sure all objects are analyzed. Redistribute ... SYS.V$BH log buffer spaces Increase LOG_BUFFER parameter or move log files to faster disks

How does one manage Oracle database users? (for DBA

Description : How does one manage Oracle database users? (for DBA

Last Answer : Oracle user accounts can be locked, unlocked, forced to choose new passwords, etc. For example, all accounts except SYS and SYSTEM will be locked after creating an Oracle9iDB database using the DB ... locked users account ALTER USER scott PASSWORD EXPIRE; -- Force user to choose a new password

How does one manage Oracle database users? (for DBA

Description : How does one manage Oracle database users? (for DBA

Last Answer : Oracle user accounts can be locked, unlocked, forced to choose new passwords, etc. For example, all accounts except SYS and SYSTEM will be locked after creating an Oracle9iDB database using the ... from SYS.V$BH log buffer spaces Increase LOG_BUFFER parameter or move log files to faster disks

What tools/utilities does Oracle provide to assist with performance tuning? (for DBA

Description : What tools/utilities does Oracle provide to assist with performance tuning? (for DBA

Last Answer : Oracle provide the following tools/ utilities to assist with performance monitoring and tuning: . TKProf . UTLBSTAT.SQL and UTLESTAT.SQL - Begin and end stats monitoring . Statspack . Oracle Enterprise Manager - Tuning Pack

How do you call other Oracle Products from Oracle Forms?

Description : How do you call other Oracle Products from Oracle Forms?

Last Answer : Run_product is a built-in, Used to invoke one of the supported oracle tools products and specifies the name of the document or module to be run. If the called product is unavailable at the time of the call, Oracle Forms returns a message to the operator.

What strategies are available for backing-up an Oracle database? (for DBA

Description : What strategies are available for backing-up an Oracle database? (for DBA

Last Answer : The following methods are valid for backing-up an Oracle database: Export/Import - Exports are "logical" database backups in that they extract logical definitions and data from the database to a file. ... , etc. If your database is in ARCGIVELOG mode, you also need to backup archived log files.

Where in the Oracle directory tree structure are audit traces placed?

Description : Where in the Oracle directory tree structure are audit traces placed?

Last Answer : In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer

When a form is invoked with call_form, Does oracle forms issues a save point?

Description : When a form is invoked with call_form, Does oracle forms issues a save point?

Last Answer : Yes

Can you connect to non - oracle datasource ? How?

Description : Can you connect to non - oracle datasource ? How?

Last Answer : Yes .

what are the datatypes supported By oracle (INTERNAL)?

Description : what are the datatypes supported By oracle (INTERNAL)?

Last Answer : Varchar2, Number,Char , MLSLABEL.

What are the OOPS concepts in Oracle.

Description : What are the OOPS concepts in Oracle.

Last Answer : Oracle does implement the OOPS concepts. The best example is the Property Classes. We can categorise the properties by setting the visual attributes and then attach the property classes for the objects ... and classes and we can consider the peroperty classes as classes and the items as objects

How does one switch to another user in Oracle? (for DBA

Description : How does one switch to another user in Oracle? (for DBA

Last Answer : Users normally use the "connect" statement to connect from one database user to another. However, DBAs can switch from one user to another without a password. Of course it is not advisable to ... tiger Connected. Note: Also see the su.sql script in the Useful Scripts and Sample Programs Page.

How does one change an Oracle user's password?(for DBA

Description : How does one change an Oracle user's password?(for DBA

Last Answer : Issue the following SQL command: ALTER USER IDENTIFIED BY ; From Oracle8 you can just type "password" from SQL*Plus, or if you need to change another user's password, type "password ... this example: SQL> password Changing password for SCOTT Old password: New password: Retype new password:

Key Words Used in Oracle

Description : Key Words Used in Oracle

Last Answer : The Key words that are used in Oracle are :: a) Commiting :: A transaction is said to be commited when the transaction makes permanent changes resulting from the SQL statements. b) Rollback :: ... A Process is a 'thread of control' or mechansim in Operating System that executes series of steps.

What is a Transaction in Oracle

Description : What is a Transaction in Oracle

Last Answer : A transaction is a Logical unit of work that compromises one or more SQL Statements executed by a single User. According to ANSI, a transaction begins with first executable statment and ends when it is explicitly commited or rolled back.

How many types of Sql Statements are there in Oracle

Description : How many types of Sql Statements are there in Oracle

Last Answer : There are basically 6 types of sql statments.They are a) Data Defination Language(DDL) :: The DDL statments define and maintain objects and drop objects. b) Data Manipulation Language(DML) :: The DML ... e.g:: Using the Sql Statements in languages such as 'C', Open,Fetch, execute and close

What are different Oracle database objects?

Description : What are different Oracle database objects?

Last Answer : TABLES -VIEWS INDEXES -SYNONYMS -SEQUENCES -TABLESPACES etc

What are ORACLE PRECOMPILERS?

Description : What are ORACLE PRECOMPILERS?

Last Answer : Using ORACLE PRECOMPILERS, SQL statements and PL/SQL blocks can be contained inside 3GL programs written in C,C++,COBOL,PASCAL, FORTRAN,PL/1 AND ADA. The Precompilers are known as ... precompiler runtime library.The output must be compiled and linked with this library to creater an executable.

What are the different types of PL/SQL program units that can be defined and stored in ORACLE database ?

Description : What are the different types of PL/SQL program units that can be defined and stored in ORACLE database ?

Last Answer : Procedures and Functions,Packages and Database Triggers.

What is OEM (Oracle Enterprise Manager)? (for DBA

Description : What is OEM (Oracle Enterprise Manager)? (for DBA

Last Answer : OEM is a set of systems management tools provided by Oracle Corporation for managing the Oracle environment. It provides tools to monitor the Oracle environment and automate tasks (both one-time and repetitive in nature) to take database administration a step closer to "Lights Out" management.

What is Oracle Label Security? (for DBA

Description : What is Oracle Label Security? (for DBA

Last Answer : Oracle Label Security (formerly called Trusted Oracle MLS RDBMS) uses the VPD (Virtual Private Database) feature of Oracle8i to implement row level security. Access to rows are restricted according ... , controlled and managed from the Policy Manager, an Enterprise Manager-based GUI utility.

What is the most important module in Oracle Financials? (for DBA

Description : What is the most important module in Oracle Financials? (for DBA

Last Answer : The General Ledger (GL) module is the basis for all other Oracle Financial modules. All other modules provide information to it. If you implement Oracle Financials, you should switch your current GL ... with it first to give your implementation team a chance to be familiar with Oracle Financials.

What is Oracle Financials? (for DBA

Description : What is Oracle Financials? (for DBA

Last Answer : Oracle Financials products provide organizations with solutions to a wide range of long- and short-term accounting system issues. Regardless of the size of the business, Oracle Financials can ... Oracle Sales Analysis: Allows for better forecasting, planning. and reporting of sales information.

What are the menu items that oracle forms 4.0 supports?

Description : What are the menu items that oracle forms 4.0 supports?

Last Answer : Plain, Check,Radio, Separator, Magic

Why is Oracle not using the damn index? (for DBA

Description : Why is Oracle not using the damn index? (for DBA

Last Answer : This problem normally only arises when the query plan is being generated by the Cost Based Optimizer. The usual cause is because the CBO calculates that executing a Full Table Scan would be faster ... with AUTOTRACE to see the statistics. Compare this to the explain plan when not using an index.

What are the different methods of backing up oracle database ?

Description : What are the different methods of backing up oracle database ?

Last Answer : Logical Backups - Cold Backups - Hot Backups (Archive log)

Where can one get a list of all hidden Oracle parameters? (for DBA

Description : Where can one get a list of all hidden Oracle parameters? (for DBA

Last Answer : Oracle initialization or INIT.ORA parameters with an underscore in front are hidden or unsupported parameters. One can get a list of all hidden parameters by executing this query: select * from SYS.X$KSPPI ... 1,1)='_' order by a.ksppinm; Remember: Thou shall not play with undocumented parameters!

List the Optional Flexible Architecture (OFA) of Oracle database? or How can we organize the tablespaces in Oracle database to have maximum performance ?

Description : List the Optional Flexible Architecture (OFA) of Oracle database? or How can we organize the tablespaces in Oracle database to have maximum performance ?

Last Answer : SYSTEM - Data dictionary tables. DATA - Standard operational tables. DATA2- Static tables used for standard operations INDEXES - Indexes for Standard operational tables. INDEXES1 - Indexes of ... - Temporary purpose tablespace TEMP_USER - Temporary tablespace for users. USERS - User tablespace.

Can one import/export between different versions of Oracle? (for DBA

Description : Can one import/export between different versions of Oracle? (for DBA

Last Answer : Different versions of the import utility is upwards compatible. This means that one can take an export file created from an old export version, and import it using a later version of the import utility. ... database to allow the Oracle 7.3 exp/imp utilities to run against an Oracle 8 database.

How can values be passed bet. precompiler exits & Oracle call interface?

Description : How can values be passed bet. precompiler exits & Oracle call interface?

Last Answer : By using the statement EXECIAFGET & EXECIAFPUT.

What third party tools can be used with Oracle EBU/ RMAN? (for DBA

Description : What third party tools can be used with Oracle EBU/ RMAN? (for DBA

Last Answer : The following Media Management Software Vendors have integrated their media management software packages with Oracle Recovery Manager and Oracle7 Enterprise Backup Utility. The Media Management ... and Recovery Sterling Software's SAMS:Alexandria - formerly from Spectralogic Sun Solstice Backup

What are different types of modules available in oracle form?

Description : What are different types of modules available in oracle form?

Last Answer : Form module - a collection of objects and code routines Menu modules - a collection of menus and menu item commands that together make up an application menu library module - a collection of user named procedures, functions and packages that can be called from other modules in the application

What is the difference between object embedding & linking in Oracle forms?

Description : What is the difference between object embedding & linking in Oracle forms?

Last Answer : In Oracle forms, Embedded objects become part of the form module, and linked objects are references from a form module to a linked source file.

Does Oracle write to data files in begin/hot backup mode? (for DBA

Description : Does Oracle write to data files in begin/hot backup mode? (for DBA

Last Answer : Oracle will stop updating file headers, but will continue to write data to the database files even if a tablespace is in backup mode.In backup mode, Oracle will write out complete changed blocks ... ). Because of this, one should notice increased log activity and archiving during on-line backups.

What are the different file extensions that are created by oracle reports?

Description : What are the different file extensions that are created by oracle reports?

Last Answer : Rep file and Rdf file.

What is user Account in Oracle database?

Description : What is user Account in Oracle database?

Last Answer : A user account is not a physical structure in database but it is having important relationship to the objects in the database and will be having certain privileges.

What is the mechanism provided by ORACLE for table replication ?

Description : What is the mechanism provided by ORACLE for table replication ?

Last Answer : Snapshots and SNAPSHOT LOGs

What is user Account in Oracle database?

Description : What is user Account in Oracle database?

Last Answer : An user account is not a physical structure in Database but it is having important relationship to the objects in the database and will be having certain privileges.