Oracle Database architecture : for Beginers

In this post I am trying to simplify Oracle Architecture, generally we found thousands of Articles over this topic , but these are somewhat complicated for a new DBA or junior level DBA Or a DBA aspirant . So, I ll try to make it simple at start then gradually we will go for some detailed knowledge.

Oracle Server = Oracle Instance + Oracle Database

Oracle Instance:

* Oracle Instance = Background processes + memory structure
* Instance starts ---> SGA allocates ----> Back ground processes starts
* Background processes functions on behalf of invoking processes.

Oracle Database:

* Consist of OS files called DATABASE FILES.
* Actual physical storage.

Non – Database Files:

Used to configure instance, authenticate privileged user , recover database
E.g. . Parameter files, Password files, Archived log files.

shared Global Area (SGA)

System global area:

* Also called as shared global area
* Stores database information that is shared by multiple processes.
* Data control information plus allocation of VM (Virtual machine)

To see SGA information
SQL> show sga;

Total System Global Area 922746880 bytes
Fixed Size 1222624 bytes
Variable Size 226494496 bytes
Database Buffers 687865856 bytes
Redo Buffers 7163904 bytes

Dynamic SGA:

* Beginning from Oracle 9i
* Allows SGA configuration to change without shutting down instance.

Some other stuff related to SGA:

* SGA is allocated in terms of GRANULES.
* Granule is a unit of continuous virtual memory allocation.
* Its size depend on SGA_MAX_SIZE

4 MB if SGA < 128 MB
Otherwise 16MB

DB buffer cache, shared pool can grow or shrink based in granule boundaries.
Owner of granule , no of granules , pending operation against component etc can be seen through V$BUFFER_POOL

SQL> select ID,NAME,BLOCK_SIZE,TARGET_SIZE from v$buffer_pool;

---------- -------------------- ---------- -----------
3 DEFAULT 8192 648

Minimum SGA Configuration is three granules
1) Fixed SGA (including redo buffer)
2) DB buffer cache
3) Shared pool

Related Articles

More articles: Oracle Database Architecture Oracle Database Administrator


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: