Today’s main topics
- · Data Base Files
- · Memory
- · Logical / Physical Structure
- · Processes
v
Solaries O.S. is the best O.S. to run a database
v
Windows is the worse O.S. to run a database
v
CMMI is an IT standard on which all software
houses are ranked
v
ETL Technology ( Link )
Data Base Administrator Responsibilities
Ø
Installation
Ø
Standards / guide lines & operational
policies
Ø
Storage media
Ø
Network area storage
Ø
SQL performance & tuning
Ø
Backup & recovery
Ø
Database performance issue
Ø
Memory usage issue
Ø
Security
Ø
Test database
Ø
Upgrade database
……………
Some major File types in database
Ø
Control files
having “ .ctl “ extension
Ø
Data files having “ .dbf “ extension
Ø
All data stored in data files
Ø
Online redolog
Ø
Archive logfile (Archives are things which
people use so rarely )
Ø
Password files
Ø
Parameter files
……………
Database Architecture
Ø
O.S. Blocks ( basic units )
Ø
D.B. blocks ( next/ level 2 units)
Ø
Extents (Next / level 3 units)
Ø
Segments (Next / level 4 units)
Ø
Table space (Next / level 5 units)
o
Table space are data files which have physical
representation like table name, column name, row name e.t.c
o
There may be more than one datafiles in one
table space
o
One datafile relates to only one table space
o
Table can expand in other datafile for example
if you are storing data about students and space is totally fill than you can
attach another storage drive and store new data there. The new space will be
expansion
////
Oracle memory structure
· SGA ( System Global Area )
Each Oracle instance uses a System Global Area or SGA—a shared-memory
area—to store its data and control-information
Each Oracle instance allocates itself an SGA when it starts and de-allocates
it at shut-down time. The information in the SGA consists of the following
elements, each of which has a fixed size, established at instance startup:
- o the redo log buffer: this stores redo entries—a log of changes made to the database. The instance writes redo log buffers to the redo log as quickly and efficiently as possible. The redo log aids in instance recovery in the event of a system failure.
- o the shared pool: this area of the SGA stores shared-memory structures such as shared SQL areas in the library cache and internal information in the data dictionary. An insufficient amount of memory allocated to the shared pool can cause performance degradation.
- o the Large pool Optional area that provides large memory allocations for certain large
Program Global Area (PGA)
The Program Global Area or PGA memory-area of an Oracle instance contains data and control-information for Oracle's server-processes.The size and content of the PGA depends on the Oracle-server options installed. This area consists of the following components:
- stack-space: the memory that holds the session's variables, arrays, and so on.
- session-information: unless using the multithreaded server, the instance stores its session-information in the PGA. (In a multithreaded server, the session-information goes in the SGA.)
- private SQL-area: an area which holds information such as bind-variables and runtime-buffers.
- sorting area: an area in the PGA which holds information on sorts, hash-joins, etc.
o
SMON ( system monitor )
o
E MON ( Process monitor )
o
DBW0 ( )
o
LGWR ( Log writer )
o
ARCn ( Archiver )
