Feeds:
Posts
Comments

Posts Tagged ‘System Global Area’

MMAN
======

SGA Background Process

The Automatic Shared Memory Management feature uses a new background process named Memory Manager (MMAN). MMAN serves as the SGA Memory Broker and coordinates the sizing of the memory components. The SGA Memory Broker keeps track of the sizes of the components and pending resize operations

RVWR
======

Flashback database
—————————–
– This a new feature introduced in 10g.
– Flashbacking a database means going back to a previous database state.
– The Flashback Database feature provides a way to quickly revert an entire
Oracle database to the state it was in at a past point in time.
– This is different from traditional point in time recovery.
– A new background process Recovery Writer ( RVWR) introduced which is responsible for writing
flashback logs which stores pre-image(s) of data blocks
– One can use Flashback Database to back out changes that:
– Have resulted in logical data corruptions.
– Are a result of user error.
– This feature is not applicable for recovering the database in case of media
failure.
– The time required for flashbacking a database to a specific time in past is
DIRECTLY PROPORTIONAL to the number of changes made and not on the size
of the database.
Jnnn
=====
These are job queue processes which are spawned as needed by CJQ0 to complete scheduled jobs. This is not a new process.
CTWR
=====

This is a new process Change Tracking Writer (CTWR) which works with the new block changed tracking features in 10g for fast RMAN incremental backups.

MMNL
=====

The Memory Monitor Light (MMNL) process is a new process in 10g which works with the Automatic Workload Repository new features (AWR) to write out full statistics buffers to disk as needed.

MMON
======

The Manageability Monitor (MMON) process was introduced in 10g and is associated with the Automatic Workload Repository new features used for automatic problem detection and self-tuning. MMON writes out the required statistics for AWR on a scheduled basis.

M000
======
MMON background slave (m000) processes.

CJQn
=====
This is the Job Queue monitoring process which is initiated with the job_queue_processes parameter. This is not new.

RBAL
====
This is the ASM related process that performs rebalancing of disk resources controlled by ASM.

ARBx
====
These processes are managed by the RBAL process and are used to do the actual rebalancing of ASM
controlled disk resources. The number of ARBx processes invoked is directly influenced by the asm_power_limit parameter.

ASMB
=====
The ASMB process is used to provide information to and from the Cluster Synchronization Services used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism.

Advertisements

Read Full Post »

What is Latch?

Well, the hardware definition of latch is – a window or door lock. The electronic definition of latch is – an electronic circuit used to store information.

Then what is Latch in Oracle? Very uncommon as the problem is super sophisticated.

Latch is one kind of very quick (could be acquired and released in nanoseconds) lock or serialization mechanism (makes more sense) to protect Oracle’s shared memory in SGA. Basically latch protects the same area of SGA being updated by more than one process.

Now question comes, what are protected and why?

Each Oracle operation needs to read and update SGA. For example –

1. When a query reads a block from disk, it will modify a free block in buffer cache and adjust the buffer cache LRU chain
2. When a new SQL statement is parsed, it will be added to the library cache within SGA
3. When DML issued and modifications are made in blocks, changes are placed in redo buffer
4. Database writer periodically (after commit, after SCN change or after each 3 sec) writes buffers from memory to disk and updates their status from dirty to clean.
5. Redo log writer writes blocks from redo buffer to redo logs.

Latch prevents any of these operations from colliding and possibly corrupting the SGA.

If the specific latch is already in use by another process, oracle will retry very frequently with a cumulative delay up to certain times (controlled by hidden parameter) called spin count. First time one process fails to acquire the latch, it will attempt to awaken after 10 milliseconds up to its spin count. Subsequent waits will increase in duration – might be seconds in extreme cases. This affects response time and throughput.

Most common type latch is Cache Buffer Latch and Cache buffer LRU chain latch which are caused for highly accessing blocks, called hot blocks. Contention on these latches is typically caused by concurrent access to a very hot block. The most common type of such hot block is index root or block branch.

Expert are always welcome for their valuable comment or suggestion for the above post.

Related Post:

https://samadhandba.wordpress.com/2011/01/04/oversize-of-datatype-varchar2-causes-performance-issue/

https://samadhandba.wordpress.com/2011/02/16/what-and-when-index-scans-is-used/

https://samadhandba.wordpress.com/2011/02/16/index-skip-full-fast-full-index-index-joins-bitmap-indexes-scan/

https://samadhandba.wordpress.com/2011/02/14/sample-table-scans-in-oracle/

https://samadhandba.wordpress.com/2011/02/14/when-you-would-make-index-and-when-not/

https://samadhandba.wordpress.com/2011/02/14/optimize-data-access-path-in-oracle-2/

https://samadhandba.wordpress.com/2011/02/13/troubleshoot-unusable-index-in-oracle/

https://samadhandba.wordpress.com/2011/02/13/the-possible-causes-for-excessive-undo-generation-2/

https://samadhandba.wordpress.com/2011/02/13/three-basic-steps-of-sql-tuning/

https://samadhandba.wordpress.com/2011/02/13/goals-for-tuning-2/

https://samadhandba.wordpress.com/2011/02/04/ora-12054-cannot-set-the-on-commit-refresh-attribute-for-the-materialized-view/

Read Full Post »

What is Latch?

Well, the hardware definition of latch is – a window or door lock. The electronic definition of latch is – an electronic circuit used to store information.

Then what is Latch in Oracle? Very uncommon as the problem is super sophisticated.

Latch is one kind of very quick (could be acquired and released in nanoseconds) lock or serialization mechanism (makes more sense) to protect Oracle’s shared memory in SGA. Basically latch protects the same area of SGA being updated by more than one process.

Now question comes, what are protected and why?

Each Oracle operation needs to read and update SGA. For example –

1. When a query reads a block from disk, it will modify a free block in buffer cache and adjust the buffer cache LRU chain
2. When a new SQL statement is parsed, it will be added to the library cache within SGA
3. When DML issued and modifications are made in blocks, changes are placed in redo buffer
4. Database writer periodically (after commit, after SCN change or after each 3 sec) writes buffers from memory to disk and updates their status from dirty to clean.
5. Redo log writer writes blocks from redo buffer to redo logs.

Latch prevents any of these operations from colliding and possibly corrupting the SGA.

If the specific latch is already in use by another process, oracle will retry very frequently with a cumulative delay up to certain times (controlled by hidden parameter) called spin count. First time one process fails to acquire the latch, it will attempt to awaken after 10 milliseconds up to its spin count. Subsequent waits will increase in duration – might be seconds in extreme cases. This affects response time and throughput.

Most common type latch is Cache Buffer Latch and Cache buffer LRU chain latch which are caused for highly accessing blocks, called hot blocks. Contention on these latches is typically caused by concurrent access to a very hot block. The most common type of such hot block is index root or block branch.

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

Manual Creation of database in windows with oracle 10g

STEP: 1

Now open a command prompt and set oracle SID as

C:Documents and Settingsadministrator.SUVIN>set oracle_sid=samdb

STEP: 2

Start a windows service with internal password oradim –new –sid
intpwd is the syntax.

C:Documents and Settingsadministrator.SUVIN>oradim -new -sid samdb -intpwd samdb Instance created.

STEP: 3

Create a directory called mandb. In my case I created it in d: drive
D:samdbdata
D:samdbredo
D:samdbcontrol
D:samdbbdump
D:samdbcdump
D:samdbudump
D:samdbpfile
D:samdbspfile
D:samdbtemp

(Note: all my parameter files and .sql file that are going to discuss following are based on my
location, you can change the location according to yours)

STEP: 4

Now create a pfile for the database namely initsamdb.ora in D:mandbpfile
Give the necessary parameter in the pfile.I gave thelease amount of parameters.

—-Contents of initmandb.ora—–

db_name=samdb
instance_name=samdb

db_block_size=8192
db_cache_size=25165824

background_dump_dest=D:samdbbdump
core_dump_dest=D:samdbcdump
user_dump_dest=D:samdbudump

control_files=(“D:samdbcontrolcontrol01.ctl”, “D:samdbcontrolCONTROL02.ctl”,
“D:samdbcontrolCONTROL03.ctl”)

shared_pool_size=50331648

undo_management=AUTO

STEP: 5

Now type following in your current command prompt sqlplus/nolog and in sql
prompt type conn sys/samdb as sysdba then you should see that you
are connected to an idle instance

C:Documents and Settingsadministrator.SUVIN>sqlplus/nolog

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Feb 1 15:18:36 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn sys/samdb as sysdba
Connected to an idle instance.
SQL>
STEP: 6

Now start the instance in nomount mode as,

startup nomount pfile=D:samdbpfileinitmandb.ora

why are you starting the database in nomount mode ?
The reason is still we are not created control files. “An instance would be started in
the NOMOUNT stage only during database creation or the re-creation of control files.

SQL> startup nomount pfile=D:samdbpfileinitsamdb.ora
ORACLE instance started.

Total System Global Area 109051904 bytes
Fixed Size 1247540 bytes
Variable Size 75499212 bytes
Database Buffers 25165824 bytes
Redo Buffers 7139328 bytes
SQL>

STEP: 7

Now create the database by the following command…

CREATE DATABASE mandb
LOGFILE
GROUP 1(‘D:samdbredoredo01.log’) SIZE 50M,
GROUP 2(‘D:samdbredoredo02.log’) SIZE 50M,
GROUP 3(‘D:samdbredoredo03.log’) SIZE 50M
DATAFILE ‘D:samdbdatasystem01.dbf’ SIZE 200M autoextend on
SYSAUX DATAFILE ‘D:samdbdatasysaux01.dbf’ SIZE 200M
UNDO TABLESPACE UNDOTBS
DATAFILE ‘D:samdbdataUNDOTBS.dbf’ SIZE 100M
default temporary tablespace TEMP
tempfile ‘D:samdbtemptemp_01_db01.dbf’ SIZE 50M
extent management local uniform size 128k
;

Run the script in the SQL prompt
SQL> CREATE DATABASE mandb
2 LOGFILE
3 GROUP 1(‘D:samdbredoredo01.log’) SIZE 50M,
4 GROUP 2(‘D:samdbredoredo02.log’) SIZE 50M,
5 GROUP 3(‘D:samdbredoredo03.log’) SIZE 50M
6 DATAFILE ‘D:samdbdatasystem01.dbf’ SIZE 200M autoextend on
7 SYSAUX DATAFILE ‘D:samdbdatasysaux01.dbf’ SIZE 200M
8 UNDO TABLESPACE UNDOTBS
9 DATAFILE ‘D:samdbdataUNDOTBS.dbf’ SIZE 100M
10 default temporary tablespace TEMP
11 tempfile ‘D:samdbtemptemp_01_db01.dbf’ SIZE 50M
12 extent management local uniform size 128k
13 ;

Database created.

SQL>

once you run this you can see the control files, redo log file, Alert log file, .dbf files and .trc
(Background Trace files & User Trace files) files are created in D:samdb folder.

STEP: 8

Now you can shutdown the database using shutdown command.

Database dismounted.
ORACLE instance shut down.
SQL>

Once the database shutdown reboot your PC

STEP: 9

Connect again as sysdba to default database

sqlplus/nolog,
SQL>conn sys/samdb as sysdba

check which database you are in by using the following command

show parameter service_name

STEP: 10

Now place the pfile initsamdb.ora in this lacation
D:Oracleproduct10.2.0db_1database

Then try to connect like this

SQL>conn sys/samdb as sysdba

and startup your database.

You dont have to mention your pfile in th startup command since you have placed it in the
default pfile location

D:Oracleproduct10.2.0db_1database

C:Documents and Settingsadministrator.SUVIN>sqlplus

SQL*Plus: Release 10.2.0.1.0 – Production on Mon Feb 1 15:48:15 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Enter user-name: sys/samdb as sysdba
Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 109051904 bytes
Fixed Size 1247540 bytes
Variable Size 75499212 bytes
Database Buffers 25165824 bytes
Redo Buffers 7139328 bytes
Database mounted.
Database opened.
SQL>

STEP: 11

Execute catalog.sql SQL>@d:ORANTrdbmsadmincatalog.sql
Execute catproc.sql SQL>@d:ORANTrdbmsadmincatproc.sql

STEP: 12

Now you need to edit the following files

D:Oracleproduct10.2.0db_1NETWORKADMINtnsnames.ora
D:Oracleproduct10.2.0db_1NETWORKADMINlistener.ora

Here are the files that I used…you can change those accordingly
# TNSNAMES.ORA Network Configuration File: D:ORANTnetworkadmintnsnames.ora
# Generated by Oracle configuration tools.
SAMADHAN=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NEWP4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = samadhan)
)
)
samdb =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = inal075)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = samdb)
)
)
INST1_HTTP =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NEWP4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = SHARED)
(SERVICE_NAME = MODOSE)
(PRESENTATION = http://HRService)
)
)
6
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)

# LISTENER.ORA Network Configuration File: D:ORANTnetworkadminlistener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = NEWP4)(PORT = 1521))
)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = D:ORANT)
(PROGRAM = extproc)
)
(SID_DESC =
(GLOBAL_DBNAME = samadhan)
(ORACLE_HOME = D:ORANT)
(SID_NAME = samadhan)
)
(SID_DESC =
(GLOBAL_DBNAME = samdb)
(ORACLE_HOME = D:samdb)
(SID_NAME = samdb)
)
)
Now start configuring tns Service Name in Client System from where you need to
access the Oracle 10g Enterprise Server
Open Net Manager
Start->Programs->Oracle10gHome->Configuration and Migration Tools->Net Manager

Create a User in mandb Database and test the connection in Net Manager
by changing the login

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »