Modes Of Locking in table –oracle

December 1, 2016

By postgresdba

The LOCK TABLE statement is used to lock tables, table partitions, or table subpartitions.

Database locks are used to provide concurrency control in order to ensure data consistency and integrity.

A locked table remains locked until you either commit your transaction or roll it back and savepoint.

Ensure that only one user can modify a record at a time.

Ensure that a table cannot be dropped while another user is querying it.

Ensure that one user cannot delete a record while another is updating it.

TYPES

DML locks (data locks)
DDL locks (dictionary locks)
Internal Locks/Latches
Distributed Locks
Parallel Cache Management Locks
Deadlocks

DML locks

Row Level Locks [TX]

All DML locks Oracle acquires automatically are row-level locks.

Row locking provides the lowest level of locking possible provides the best possible transaction concurrency.

Row-level locks serve a primary function to prevent multiple transactions from modifying the same row.

Table Level Lock [TM]

A transaction acquires a table lock for DML statements such as INSERT/UPDATE/DELETE, SELECT with the FOR UPDATE, and LOCKTABLE

A table lock can be held in several modes:

Row share (RS)

Row exclusive (RX)

Share (S)

Share row exclusive (SRX)

Exclusive (X)

DDL locks

A DDL lock protects the definition of a schema object while the object is referenced in a DDL operation.

· Exclusive DDL Locks

· Shared DDL Locks

· Breakable Parse Locks

Oracle automatically acquires a DDL lock to prevent other DDL operations from referencing or altering the same object.

Internal locks

Data Dictionary Locks

Held on entries in dictionary caches while the entries are being modified or used. They guarantee that statements being parsed do not see inconsistent object definitions.

File and Log Management Locks

Protect various files like control files, redo log files so that only one process at a time can change it. Datafiles are locked to ensure that multiple instances mount a database in shared mode or that one instance mounts it in exclusive mode.

Tablespace and Rollback Segment Locks

Protect tablespaces and rollback segments. Example, all instances accessing a database must agree on if s tablespace is online or offline. Rollback segments are locked so that only one instance can write to a segment.

Latches

Latches are low-level serialization mechanisms to protect shared data structures in the system global area (SGA). Latches protect the oracle lists like list of users currently accessing the database and protect the data structures describing the blocks in the buffer cache.

Distributed Locks

A distributed lock manager (DLM) is a software component provided by your platform vendor.

Distributed locks are held by a database instance, not by individual transactions.

The Oracle Parallel Server uses a distributed lock manager to coordinate concurrent access to resources, such as data blocks and rollback segments, across multiple instances.

Parallel Cache Management Locks

The instance locks which manage the locking of blocks in datafiles.

PCM locks ensure cache coherency by forcing instances to acquire a lock before modifying or reading any database block.

Deadlocks

A deadlock is the situation where you have two, or more, Oracle “sessions” (well, transactional “states”) competing for mutually locked resources. Oracle deals with deadlocks pretty much immediately by raising an exception (ORA-00060) in one of the sessions.

Manual Table Lock Modes

LOCK TABLE table_name IN lock_mode MODE [ WAIT [, integer] | NOWAIT ];

For example:

LOCK TABLE EMP IN SHARE MODE NOWAIT;

Lock_mode

ROW SHARE(RS) – Allows concurrent access to the table, but users are prevented from locking the entire table for exclusive access.

ROW EXCLUSIVE(RX) – Allows concurrent access to the table, but users are prevented from locking the entire table with exclusive access and locking the table in share mode.

SHARE(S) – Allows concurrent queries but users are prevented from updating the locked table.

SHARE ROW EXCLUSIVE(SRX) – Users can view records in table, but are prevented from

updating the table or from locking the table in SHARE mode.

EXCLUSIVE(E) – Allows queries on the locked table, but no other activities.

Views to identify Locking Issues

V$LOCK

V$LOCKED_OBJECT

DBA_BLOCKERS

DBA_WAITERS

DBA_LOCK

DBA_DDL_LOCKS

DBA_DML_LOCKS

DBA_LOCK_INTERNAL

DBA_KGLLOCK

Program Global Area (Or) Process Global Area-Architecture Of Oracle(Pga)

December 1, 2016

By postgresdba

1.Its mainly used for sorting purpose

2.A PGA is nonshared memory created by Oracle Database when a server or background process is started.

3.Using v$session we can check whether dedicated server or shared server

4.One pga for each server process

5.One PGA exists for each Server Process and each Background Process.

6.It stores data and control information for a single Server Process or a single Background Process.

7.The Program Global Areas (PGA) are memory regions that contain data and control information for a server or background process.

Monitor

PGA usage statistics:
select * from v$pgastat;

Determine a good setting for pga_aggregate_target:
select * from v$pga_target_advice order by pga_target_for_estimate;

Show the maximum PGA usage per process:
select max(pga_used_mem), max(pga_alloc_mem), max(pga_max_mem) from v$process;

Pga memory divede into three area

SESSION MEMORY
PRIVATE SQL AREA
SQL WORK AREA

1.Session Memory:/session information:

Session memory is the memory allocated to hold a session’s variables (logon information) and other information related to the session.

for eg:

deine_editor=vi

set serveroutput on;

set autotrace on

set pagesize;

set linesize;

monitor session information:-

we can check total PGA memory used by processes using this below query

SELECT ROUND(SUM(pga_used_mem)/(1024*1024),2) PGA_USED_MB FROM v$process;

we can find PGA usage for a specific session using this below query

SELECT SID, b.NAME, ROUND(a.VALUE/(1024*1024),2) MB FROM

v$sesstat a, v$statname b

WHERE (NAME LIKE ‘%session uga memory%’ OR NAME LIKE ‘%session pga memory%’)

AND a.statistic# = b.statistic#

AND SID = 80;

To calculate the amount of memory that you gone need for PGA, estimate the number of maximum connected sessions and run:

SELECT :MAX_CONNECTED_SESSIONS*(2048576+P1.VALUE+P2.VALUE)/(1024*1024) YOU_NEED_PGA_MB

FROM V$PARAMETER P1, V$PARAMETER P2

WHERE P1.NAME = ‘sort_area_size’

AND P2.NAME = ‘hash_area_size’;

2.PRIVATE SQL AREA:-

Stores information for a parsed SQL statement – stores bind variable values and runtime memory allocations.

Cursor information also maintained in this area

Dedicated Server environment – the Private SQL Area is located in the Program Global Area.

Shared Server environment – the Private SQL Area is located in the System Global Area.

However, if a session is connected through a shared server, part of the private SQL area is kept in the SGA

Private sql area have two part

i)PERSISTENT AREA:-

The private SQL area contains data such as bind variable values, query execution state information, and query execution work areas,The persistent area contains the bind variable values and this area is released only when the cursor is closed.

ii)Runtime memory:

The runtime area contains information about an executed query, including the progress of a full table scan and the current state of the SQL work areas. These PGA areas are allocated to run RAM intensive operations such as sorting and hash-joins. For DML operations, a runtime area is released when the command completes.

Tempory information,parameters.

Query execution statment info…etc

3.SQL WORK AREA:-

These areas are allocated as needed for memory-intensive operations like sorting or hash-joins.
Memory allocated for sort, hash-join, bitmap merge, and bitmap create types of operations.
For DML, the run-time area is freed when the statement finishes running.For queries, it is freed after all rows are fetched or the query is canceled.
Oracle 9i and later versions enable automatic sizing of the SQL Work Areas by setting the WORKAREA_SIZE_POLICY = AUTO parameter (this is the default!)

PGA parameters:-

SORT_AREA_SIZE=size —order by,group by,roll up,window functions

HASH_AREA_SIZE=size —where clause

BITMAP_MERGE_AREA_SIZE=size —index merge area

CREATE_BITMAP_AREA_SIZE =size —Index creation area

1.the above parameters from 9i,

PGA_AGGREGATE_TARGET = size are individually and seperately allocated manually

2. the above parameters from 10g,

PGA_AGGREGATE_TARGET = size

WORKAREA_SIZE_POLICY = auto/manual

if you set above workarea_size_policy=manual then set size above all parameter manually

otherwise f you set workarea_size_policy=auto then no need to set size above all parameter it automatically set memory all the parameter

3.the above parameters from 11g(AMM),

MEMORY_TARGET=SGA+PGA

MEMORY_TARGET: The amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. This parameter is dynamic, so the total amount of memory available to Oracle can be increased or decreased, provided it does not exceed the MEMORY_MAX_TARGET limit. The default value is “0”.

MEMORY_MAX_TARGET: This defines the maximum size the MEMORY_TARGET can be increased to without an instance restart. If the MEMORY_MAX_TARGET is not specified, it defaults to MEMORY_TARGET setting.

# df -k /dev/shm

Filesystem 1K-blocks Used Available Use% Mounted on

tmpfs 1029884 350916 678968 35% /dev/shm

The shared memory file system should be big enough to accommodate the MEMORY_TARGET and MEMORY_MAX_TARGET values, or Oracle will throw the following error.

ORA-00845: MEMORY_TARGET not supported on this system

To adjust the shared memory file system size issue the following commands, specifying the required size of shared memory.

# umount tmpfs

# mount -t tmpfs shmfs -o size=1200m /dev/shm

Make the setting permanent by amending the “tmpfs” setting of the “/etc/fstab” file to look like this.

tmpfs /dev/shm tmpfs size=1200m 0 0

Configuration Of AMM

The Database Configuration Assistant (DBCA) allows you to configure automatic memory management during database creation.

When creating the database manually, simply set the appropriate MEMORY_TARGET and MEMORY_MAX_TARGET initialization parameters before creating the database.

Enabling automatic memory management on a system that didn’t previously use it is a simple task. Assuming you want to use a similar amount of memory to your current settings you will need to use the following calculation.

MEMORY_TARGET = SGA_TARGET + GREATEST(PGA_AGGREGATE_TARGET, “maximum PGA allocated”)

The following queries show you how to display the relevant information and how to combine it in a single statement to calculate the required value.

Individual values.

COLUMN name FORMAT A30

COLUMN value FORMAT A10

SELECT name, value

FROM v$parameter

WHERE name IN (‘pga_aggregate_target’, ‘sga_target’)

UNION

SELECT ‘maximum PGA allocated’ AS name, TO_CHAR(value) AS value

FROM v$pgastat

WHERE name = ‘maximum PGA allocated’;

Calculate MEMORY_TARGET

SELECT sga.value + GREATEST(pga.value, max_pga.value) AS memory_target

FROM (SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = ‘sga_target’) sga,

(SELECT TO_NUMBER(value) AS value FROM v$parameter WHERE name = ‘pga_aggregate_target’) pga,

(SELECT value FROM v$pgastat WHERE name = ‘maximum PGA allocated’) max_pga;

Assuming our required setting was 5G, we might issue the following statements.

CONN / AS SYSDBA

Set the static parameter. Leave some room for possible future growth without restart.

ALTER SYSTEM SET MEMORY_MAX_TARGET=6G SCOPE=SPFILE;

Set the dynamic parameters. Assuming Oracle has full control.

ALTER SYSTEM SET MEMORY_TARGET=5G SCOPE=SPFILE;

ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0 SCOPE=SPFILE;

ALTER SYSTEM SET SGA_TARGET=0 SCOPE=SPFILE;

Restart instance.

SHUTDOWN IMMEDIATE;

STARTUP;

Once the database is restarted the MEMORY_TARGET parameter can be amended as required without an instance restart.

ALTER SYSTEM SET MEMORY_TARGET=4G SCOPE=SPFILE;

AMM MONITOR

SELECT component, current_size, min_size, max_size

FROM v$memory_dynamic_components

WHERE current_size != 0;

COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE

—————————— ———— ———- ———-

shared pool 197132288 192937984 197132288

large pool 4194304 4194304 4194304

java pool 41943040 41943040 41943040

SGA Target 318767104 285212672 318767104

DEFAULT buffer cache 71303168 41943040 75497472

PGA Target 104857600 104857600 138412032

6 rows selected.

The V$MEMORY_CURRENT_RESIZE_OPS and V$MEMORY_RESIZE_OPS views provide information on current and previous component resize operations.

The V$MEMORY_TARGET_ADVICE view provides information to help tune the MEMORY_TARGET parameter. It displays a range of possible MEMORY_TARGET settings, as factors of the current setting, and estimates the potential DB Time to complete the current workload based on these memory sizes.

SELECT * FROM v$memory_target_advice ORDER BY memory_size;

MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION

———– —————— ———— ——————- ———-

303 .75 3068 1.0038 2

404 1 3056 1 2

505 1.25 3056 1 2

606 1.5 3056 1 2

707 1.75 3056 1 2

808 2 3056 1 2

6 rows selected.

PGA VIEWs:

Statistics on allocation and use of work area memory can be viewed in the following dynamic performance views:

V$SYSSTAT

V$SESSTAT

V$PGASTAT

V$SQL_WORKAREA

V$SQL_WORKAREA_ACTIVE

The following three columns in the V$PROCESS view report the PGA memory allocated and used by an Oracle process:

PGA_USED_MEM

PGA_ALLOCATED_MEM

PGA_MAX_MEM

MEMORY MANAGEMENT IN ORACLE:

1.Automatic memory management:

DBA specifies the target size for instance memory.

The database instance automatically tunes to the target memory size.

Database redistributes memory as needed between the SGA and the instance PGA.

2. Automatic shared memory management:

This management mode is partially automated.

DBA specifies the target size for the SGA.

DBA can optionally set an aggregate target size for the PGA or managing PGA work areas individually.

3.Manual memory management:

Instead of setting the total memory size, the DBA sets many initialization parameters to manage components of the SGA and instance PGA individually

NOTE: if pga is fill the all queries will go to temporay tablespace that is disk level sorting

Architecture Of Oracle -2

December 1, 2016

By postgresdba

Larry Ellison and two friends and former co-workers, Bob Miner and Ed Oates, started a consultancy called Software Development Laboratories (SDL) in 1977. SDL developed the original version of the Oracle software.

Versions:-

Oracle Database 10g Release 2: 10.2.0.1–10.2.0.5 (Patchset as of April 2010 )

Oracle Database 11g Release 1: 11.1.0.6–11.1.0.7 (Patchset as of September 2008 )

Oracle Database 11g Release 2: 11.2.0.1–11.2.0.4 (Patchset as of August 2013 )

Oracle Database 12c Release 1: 12.1.0.1 (Patchset as of June 2013 )

What is An Oracle Database?

Basically, there are two main components of Oracle database –– instance and database itself. An instance consists of some memory structures and the background processes, whereas a database refers to the disk resources.

Figure 1 will show you the relationship.

1.Instance

Database files themselves are useless without the memory structures and processes to interact with the database. Oracle defines the term instance as the memory structure and the background processes used to access data from a database. The memory structuresand background processes contitute an instance. The memory structure itself consists of System Global Area (SGA), Program Global Area (PGA),and an optional area –– Software Area Code. In the other hand, the mandatory background processes are Database Writer (DBWn), Log Writer (LGWR), Checkpoint (CKPT), System Monitor (SMON), and Process Monito (PMON). And another optional background processes are Archiver (ARCn), Recoverer (RECO), etc.

Figure 2 will illustrate the relationship for those components on an instance.

A.System Global Area

SGA is the primary memory structures. When Oracle DBAs talk about memory, they usually mean the SGA. This area is broken into a few of part memory –– Buffer Cache, Shared Pool, Redo Log Buffer, Large Pool, and Java Pool.

Buffer Cache

Buffer cache is used to stores the copies of data block that retrieved from datafiles. That is, when user retrieves data from database, the data will be stored in buffer cache. Its size can be manipulated via DB_CACHE_SIZE parameter in init.ora initialization parameter file.

Shared Pool

Shared pool is broken into two small part memories –– Library Cache and Dictionary Cache. The library cache is used to stores information about the commonly used SQL and PL/SQL statements; and is managed by a Least Recently Used (LRU) algorithm. It is also enables the sharing those statements among users. In the other hand, dictionary cache is used to stores information about object definitions in the database, such as columns, tables, indexes, users, privileges, etc.

The shared pool size can be set via SHARED_POOL_SIZE parameter in init.ora initialization

parameter file.

Redo Log Buffer

Each DML statement (select, insert, update, and delete) executed by users will generates the redo entry. What is a redo entry? It is an information about all data changes made by users. That redo entry is stored in redo log buffer before it is written into the redo log files. To manipulate the size of redo log buffer, you can use the LOG_BUFFER parameter in init.ora initialization parameter file.

Large Pool

Large pool is an optional area of memory in the SGA. It is used to relieves the burden place on the shared pool. It is also used for I/O processes. The large pool size can be set by LARGE_POOL_SIZE parameter in init.ora initialization parameter file.

Java Pool

As its name, Java pool is used to services parsing of the Java commands. Its size can be set by JAVA_POOL_SIZE parameter in init.ora initialization parameter file.

B.Program Global Area

Although the result of SQL statemen parsing is stored in library cache, but the value of binding variable will be stored in PGA. Why? Because it must be private or not be shared among users. The PGA is also used for sort area.

C.Software Area Code

Software area code is a location in memory where the Oracle application software resides.

D.Oracle processes

There are two categories of processes that run with an Oracle database. They are mentioned

below:

User processes
System processes

The following figure illustrates the relationship between user processes, server processes, PGA, and session:

The first interaction with the Oracle-based application comes from the user computer that creates a user process. The user process then communicates with the server process on the host computer. Here, PGA is used to store session specific information.

E.Oracle Background Processe

Oracle background processes is the processes behind the scene that work together with the memories.

DBWn

Database writer (DBWn) process is used to write data from buffer cache into the datafiles. Historically, the database writer is named DBWR. But since some of Oracle version allows us to have more than one database writer, the name is changed to DBWn, where n value is a number 0 to 9.

LGWR

Log writer (LGWR) process is similar to DBWn. It writes the redo entries from redo log buffer

into the redo log files.

CKPT

Checkpoint (CKPT) is a process to give a signal to DBWn to writes data in the buffer cache into datafiles. It will also updates datafiles and control files header when log file switch occurs.

SMON

System Monitor (SMON) process is used to recover the system crash or instance failure by applying the entries in the redo log files to the datafiles.

PMON

Process Monitor (PMON) process is used to clean up work after failed processes by rolling back the transactions and releasing other resources.

ARCH

The ARCH background process is invoked when your database is running in ARCHIVELOG mode. If you are archiving your redo logs, the redo logs are touched by several background processes. First, the LGWR process copies the log_buffer contents to the online redo log files, and then the ARCH process copies the online redo log files to the archived redo log filesystem on UNIX. The ARCH process commonly offloads the most recent online redo log file whenever a log switch operation occurs in Oracle

The figure 4: shows various components of SGA, Oracle background processes, and their interactions

with control files, data files, Redo Log files, and archived redo logs.

2.Database

The database refers to disk resources, and is broken into two main structures –– Logical structures and Physical structures.

A.Logical Structures:~

Oracle database is divided into smaller logical units to manage, store, and retrieve data effeciently. The logical units are tablespace, segment, extent, and data block.

Figure 5 will illustrate the relationships between those units.

Tablespace

A Tablespace is a grouping logical database objects. A database must have one or more tablespaces.

In the Figure 5, we have three tablespaces –– SYSTEM tablespace, Tablespace 1,and Tablespace 2 Tablespace

is composed by one or more datafiles.

There are three types of tablespaces in Oracle:

Permanent tablespaces
Undo tablespaces
temporary tablespaces

Segment

A Tablespace is further broken into segments. A segment is used to stores same type of objects. That is, every table in the database will store into a specific segment (named Data Segment) and every index in the database will also store in its own segment (named Index Segment). The other segment types are TemporarySegment and Rollback Segment.A segment is a container for objects (such as tables, views, packages . . . indexes). A segment consists of Extends.

There are 11 types of Segments in oracle 10g.

Table
Table Partition
Index
Index Partition
Cluster
Rollback
Deferred Rollback
Temporary
Cache
Lobsegment
Lobindex

Extent

A segment is further broken into extents. An extent consists of one or more data block. When the database object is enlarged, an extent will be allocated. Unlike a tablespace or a segment, an extent cannot be named. Space for a data on a hard disk is allocated in extends.

Data Block

A data block is the smallest unit of storage in the Oracle database. The data block size is a specific number of bytes within tablespace and it has the same number of bytes.

B.Physical Structures:~

The physical structures are structures of an Oracle database (in this case the disk files) that are not directly manipulated by users. The physical structure consists of datafiles, redo log files, and control files.

Datafiles

A datafile is a file that correspondens with a tablespace. One datafile can be used by one tablespace,

but one tablespace can has more than one datafiles. An Oracle databae include of a number of physical files called datafile.

Redo Log Files

A Redo Log is a file that is part of an Oracle Database. When a transaction is committed the transaction’s details in the redo log buffer is written in a redo log file. These files contain information that helps in recovery in the event of system failure.

The figure 6: shows three Redo Log groups. Each group consists of two members. The first member

of each Redo Log group is stored in directory D1 and the second member is stored in directory D2.

Control Files

Control files are used to store information about physical structure of database. The control file is absolutely crucial to database operations.

It contains the following types of information:

Database Information

Archive log history

Tablespace and datafile records

Redo threads

Database’s creation data

Database name

Current Archive information

Log records

Database Id which is unique to each Database

Oracle Architecture And Working Process Of Query Flow

December 1, 2016

By postgresdba

Oracle Architecture consists of two things,

Oracle Instance – consists of Memory(SGA & PGA) and Background process
Oracle database files – consists of OS level database files like Datafile, logfile and controlfiles.

user process (User Process Interface) : client sending a request from one end. It starts as we use putty to connect to server machine.

server process (Oracle Process Interface) : connecting to db using command sqlplus / as sysdba. Two types of server process in oracle,

1. shared server(dispatcher): multiple user using single resources for sending request and receiving reply from the database as using single server process.(till 8i)

2. Dedicated server(listener) : each and every user uses dedicate server process as independent of other user for sharing.(from 9i) (from 10g default ).

Oracle Instance:

Oracle instance consists of memory and background process. Oracle memory consist of

Program Global Area (PGA)
System Global Area (SGA)
and oracle background process are huge in numbers. In 9i there are 60+ background process, 10g 150+ background process and in 11g 250+ background process. Some of the important background process on requirement when database starts up is,

System Monitor(SMON)
Process Monitor(PMON)
Database writer(DBWR)
Log writer(LGWR)
Checkpoint(Chkpt)
Archiver(ARCH)

password file(orapwd) : this will maintain the user password and login details and

parameter(init.ora) : this will state the parameter required for database creation.

Datafile is the collection of segments, used to store data in the format of binary as tables,index,etc. System is the default tablespace in database level.

Control file is used to store data about the database which will control entire database. We can have minimum of one controlfile and maximum of 8.

Logfile is used in recovery process as it takes out backup of each and every transaction logs. It consist of group (minimum -2 maximum – 32) and members (minimum – 1 maximum – 8).

Archive Logfile:

It is Backup or process of storing the transaction logs from redo online log file to Archive log destination for recovery purpose.

Query Flow :

When a Query is issued from user end, user process will be initiated and it will travel to server side. Then the server process will be initiated where, it will decides whether it needs to go for Shared server or Dedicated server. Also Program Global Area(PGA) is allocated for user query processing like sorting operation. Then the query moves to system global area(SGA) where complete query execution happens here.

Shared Pool, Here the query enters in to Library cache where inturns it has 2 component inside, SQL Area & PLSQL Area. If the given input is of Sql Query, it will go to Sql Area and if it’s a PLSQL Block it will go PLSQL Area. In Share pool, Query Validation and execution plan generation will takes place as below sequence of steps.

Syntax & Semantic check – Query keyword & identifiers will be checked.
Hash Value Generation – A unique value will be assigned for the query.
Parsing – Hard parsing (if query is new) Soft parsing (if its an already used query)
Execution – By Default will create 2000 plans and picks up a best plan among it for query.

Redo log Buffer cache(rlbc), is used to take backup of all transaction logs except select query for database recovery purpose. Transaction logs in rlbc will inturn moved to online logfiles via lgwr background process.

Database Buffer Cache, (dbbc) is where the query execution will takes place using the best execution plan it received from shared pool. It contains 3 parts namely,

Default – query default execution region.
Keep – can able to pin small tables and frequently used ones.
Recycle – Can able to pin huge tables and rarely used ones.

In Dbbc, we represent storage objects are buffer where the data will be written in buffer and there status are,

Free – empty buffer with no data written.
Pinned – currently writing buffer.
Dirty – filled or completely written buffer.

Up on execution of a user query, if the table block image already exists in the DBBC memory means, then it is called Cache hit, where it process the execution without disturbing the datafiles. Else if the block image not available in the DBBC buffer means, then it is Cache miss, where it will call the Oracle server process to fetch the image block of the respective table and put it in the DBBC and does the execution. Except select, all other sql commands which affect the database will be written it executed data permanently to the datafile using dbwr background process upon its dirty buffer status.

SGA Of Explanation in oracle

December 1, 2016

By postgresdba

SGA

System Global Area or Shared Global Area

A system global area is a group of shared memory areas that dedicated to an Oracle database instance.

It is a large part of memory that all the oracle background process access.

It is allocated from shared memory(SHMMAX) from Linux OS, which is specified in /etc/sysctl.conf.

SGA Consists of below components.

DBBC (Database Buffer Cache)
RLBC (Redo Log Buffer Cache)
Shared Pool
Large Pool
Java Pool
Streams Pool
Fixed SGA

Shared Pool :

Shared pool consists of two components.

Library Cache
Dictionary Cache or Row Cache
Server Result Cache
Reserverd Pool

Library cache and Dictionary Cache

When a SQL statement is executed it first enters library cache of Shared pool. It does syntax checking and semantec checking , then will create a shared SQL area.

In Shared SQL area it will have the parsed info and the execution plan for that SQL. So it can be reused.

syntax checking : Checks the SQL is syntactically correct.

Eg. select * from emp might have syntax error like selct * from emp;

semantec checking : Checks that the user have access to all the objects in the SQL. It uses Dictionary Cache/Row Cache to achive this. Dictionary will read the dictionary information from SYSTEM Tablespace.

So as already mentioned , any SQL statement that is executed will have a Shared SQL area. When another user executes a query , first it checks if there is any Shared SQL Area present , if it finds then it is called soft parsing , if not it will again create Shared SQL Area which is called hard parsing. We can also call as HIT and MISS.

Result Cache :

This memory component stores result of SQL result Cache and PLSQL function result Cache

Ex: Assume the query

SQL> select count(*) from emp;

count(*)

——————-

1000000000

takes 1 hr to execute , if result cache is enabled then the result 1000000000 will be stored in result cache. so next time if you execute the same query it will get from the result cache and will take some seconds.

If there is any dml occurs for emp table , then the result cache will invalidate and it will not get used. Again it will take 1 hr to execute the query for first time.

Database buffer cache

Holds a copy of data blocks read from data files

The LRU list holds free buffers, pinned buffers, and dirty buffers that have not yet been moved to the write list. Free buffers do not contain any useful data and are available for use.

Pinned buffers are currently being accessed.

It contains Multiple buffer pools are Default,keep,recycle,

Redo Log Buffer

The redo log buffer is a circular buffer in the SGA that holds information about changes made to the database. This information is stored in redo entries.

Includes information about transactions that have not yet been written to online redo log files.

Java pool

P arsing of Java code and scripts

Installation tasks related to Java applications with Oracle 11g

Java stored procedure code parsing

Streams pool

Oracle Streams can be used for:

Replication

Message Queuing

Loading data into a Data Warehouse

Event Notification

Data Protection

Large pool

Parallel Query performance management.

Recovery Manager (RMAN) backup and recovery operations.

Usage of a Large Pool Area allows the shared pool to primarily cache SQL and avoid the overhead caused by shrinking the SQL Cache Area

PARAMETERS

SGA_TARGET

SGA_MAX_SIZE

SHARED_POOL_SIZE

DB_CACHE_SIZE

DB_KEEP_CACHE_SIZE

DB_RECYCLE_CACHE_SIZE

DB_nk_CACHE_SIZE

LARGE_POOL_SIZE

JAVA_POOL_SIZE

LOG_BUFFERS

Views

V$SGA

V$SGAINFO

V$SGASTAT

partitions –Oracle 10g practicals

December 1, 2016

By postgresdba

Create a table which hold userdata
CREATE TABLE TXN
( TDATE DATE,
START_DATE NUMBER,
END_DATE NUMBER,
PNUMBER NUMBER,
ACODE CHAR(2)
)

Insert data into the table with appx. 10000 recs per month for 1 year months can be increased/decreased using 0..11)
begin
for z in 0..11 loop
for i in 1..323 loop
for j in 1..31 loop
insert into txn1 values(sysdate-433+j+(31*z),1212*i,2323232*i,122323*i,(select
decode(trunc(dbms_random.value(1,5)),1,’CN’,2,’MD’,3,’CO’,4,’TR’,’CN’) from dual));
end loop;
end loop;
end loop;
end;
/

Range_partition

CREATE TABLE TXN_RANGE
( TDATE DATE,
START_DATE NUMBER,
END_DATE NUMBER,
PNUMBER NUMBER,
ACODE CHAR(2))
PARTITION BY RANGE (tdate)
(PARTITION p1 VALUES LESS THAN (TO_DATE(’31/03/2015′, ‘DD/MM/YYYY’)) TABLESPACE TEST,
PARTITION p2 VALUES LESS THAN (TO_DATE(’30/06/2015′, ‘DD/MM/YYYY’)) TABLESPACE TEST,
PARTITION p3 VALUES LESS THAN (TO_DATE(’30/09/2015′, ‘DD/MM/YYYY’)) TABLESPACE TEST,
PARTITION p4 VALUES LESS THAN (TO_DATE(’31/12/2015′, ‘DD/MM/YYYY’)) TABLESPACE TEST);

List_partition
CREATE TABLE TXN_LIST
( TDATE DATE,
START_DATE NUMBER,
END_DATE NUMBER,
PNUMBER NUMBER,
ACODE CHAR(2))
PARTITION BY LIST (ACODE)
(
PARTITION Chennai VALUES (‘CN’),
PARTITION madurai VALUES (‘MD’),
PARTITION Coimb VALUES (‘CO’),
PARTITION trichy VALUES (‘TR’)
);

HASH_partition

CREATE TABLE TXN_HASH
(TDATE DATE,
START_DATE NUMBER,
END_DATE NUMBER,
PNUMBER NUMBER,
ACODE CHAR(2))
PARTITION BY HASH (TDATE)
PARTITIONS 4
STORE IN (TEST, TEST, TEST, TEST);

Composite partition(Range-List)
CREATE TABLE TXN_RANGE_LIST
(TDATE DATE,
START_DATE NUMBER,
END_DATE NUMBER,
PNUMBER NUMBER,
ACODE CHAR(2))
PARTITION BY RANGE (TDATE)
SUBPARTITION BY LIST (ACODE)
SUBPARTITION TEMPLATE
(
SUBPARTITION Chennai VALUES (‘CN’),
SUBPARTITION madurai VALUES (‘MD’),
SUBPARTITION Coimb VALUES (‘CO’),
SUBPARTITION trichy VALUES (‘TR’)
)
(PARTITION p1 VALUES LESS THAN (TO_DATE(’31/03/2015′, ‘DD/MM/YYYY’)) TABLESPACE TEST,
PARTITION p2 VALUES LESS THAN (TO_DATE(’30/06/2015′, ‘DD/MM/YYYY’)) TABLESPACE TEST,
PARTITION p3 VALUES LESS THAN (TO_DATE(’30/09/2015′, ‘DD/MM/YYYY’)) TABLESPACE TEST,
PARTITION p4 VALUES LESS THAN (MAXVALUE) TABLESPACE TEST);

To insert data use the below commad for all partitioned tables
insert into TXN_RANGE_LIST select * from txn1;

views :
dba_tab_partitions
select table_name,partition_name,num_rows,blocks,high_value from user_tab_partitions

dba_tab_subpartitions
select table_name,partition_name,subpartition_name,tablespace_name,num_rows from user_tab_subpartitions

Materialized views Practical and theory

December 1, 2016

By postgresdba

what is materialized view
A materialized view is a database object that contains the results of a query.
Materialized views, which store data based on remote tables, are also known as snapshots.
A materialized view is a view where the query has been executed and the results has been stored as a physical table.

Difference between view and materialized view.
Materialized view- But Materialized views are schema objects, it storing the results of a query in a separate schema object take up storage space and contain data. This indicates the materialized view is returning a physically separate copy of the table data.
View– A view is nothing but a SQL query, takes the output of a query and makes it appear like a virtual table, which does not take up any storage space or contain any data.

Syntax
Sql>create materialized view
Bulid [immediate|deffered]
Refersh [fast|complete|force]
On [commit|demand]
As
Select * from @;

Build clause options
Immediate : The materialized view is populated immediately
Deferred : The materialized view is populated on the first requested refresh.

Refersh types
Fast : A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails.
Complete : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.
Force : A fast refresh is attempted. If one is not possible a complete refresh is performed.

A referesh can be triggered in one of two ways
On commit : The refresh is triggered by a committed data change in one of the dependent tables.
On demand : The refresh is initiated by a manual request or a scheduled task.

privilages
Check the user who will own the materialized views has the correct privileges. At minimum they will require the CREATE MATERIALIZED VIEW privilege. If they are creating materialized views using database links, you may want to grant them CREATE DATABASE LINK privilege also.

Sql>Conn sys@db2
Sql>Grant create materialized view to u1;
Sql>Grant create database link to u1;

Create materialized view
Sql>Connect to the materialized view owner(u1/u1) and create the database link and the materialized view itself.
Sql>Conn u1/u1@db2

syntax for Db link-if u want connect another server Db
sql>Create database link connect to identified by using ;

Example
Sql>Create database link db1 connect to u1 identified by u1 using ‘db1.sainora’;
Sql>Create materialized view mv
Build immediate
Refresh force
On demand
As
Select * from [email protected];

Materialized view logs
The CREATE MATERIALIZED VIEW LOG command is used to create a log of activity on a materialized view’s base table.
DML statements against this base table are logged and used to apply those changes to the materialized view. Without this log, fast refreshes of the materialized view are not possible and the only option of refreshing the materialized view is a complete refresh.
A fast refresh is quicker than a complete refresh.
The trade off is that these materialized view logs consume space.

The base table’s owner must own the materialized view log.
Sql>Conn u1/u1@db1
Sql>Create materialized view log on u1.t1 With primary key;

Refresh materialized view
If a materialized view is configured to refresh on commit, you should never need to manually refresh it, unless a rebuild is necessary.
Sql>exec dbms_mview.refresh(‘mv’);

Drop log
Sql>drop materialized view log on u1.t1;

Drop materialized view
Sql>drop materialized view mv;

TNS and LISTENER Configuration in oracle

December 1, 2016

By postgresdba

TNS-Transparent network substrate.

The transparent network substrate a proprietary oracle computer networkin technology,supports homogeneous peer-to-peer connectivity on top of other networking technologies such as tcp/ip and named pipes.
Tns operates mainly for connection to oracle databases.
Which is the responsible of connecctions establishment.

TNS ENTRY:-

[oracle@nijam]$netca

Step1:- To click the local net service name configuration and click next button.

Step2:-To click the add and click next button.

Step3:-To add service name and click next button.

Step4:-To click the tcp and click next button.

Step5:-To add host name and click next button.

Step6:-To click the no and click next button.

Step7:-To add net service name and click next button.

Step8:-To click the no and click the next button.

Step 9:-to click the finish button.

then check the information os level.

[oracle@nijam]$cd $ORACLE_HOME/network/admin/

[oracle@nijam admin]$vi tnsname.ora

#tnsnames.ora network configuration file:/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

#generated by oracle configuration tools.

Nijam=(description=(address_list=(address=(protocol=tcp)(host=192.168.2.22)(port=1521))

)(connect_data=(service_name=nijam)

)

Save the file.

[oracle@nijam admin]$tnsping nijam

[oracle@nijam]$ps –ef|grep –i tns

LISTENER:-

The oracle database listener is the server process that provides basic network connectivity for clients,application servers,and other databases to an oracle databases.
In addition to database ,the listener can also be configured to support binary executables.
The listener listens on a specific network port(default 1521) and forwards network connections to the database.
The listener configuration, stored in the listener.ora file.

[oracle@nijam]$netca

Step1:-To click the listener configuration and click next button.

Step2:-To click the add and click the next button

Step3:-To add listener name and click next button

Step4:-To click the tcp and click next button.

Step5:-To click the no and next button.

Step6:-To click the finish button.

[oracle@nijam]$cd $ORACLE_HOME/network/admin/

[oracle@nijam admin]$vi listener.ora

Listener=(description=

(address_list=

(address=(protocol=tcp)(host=192.168.0.200)(port=1521))

(address=(protocol=ipc)(key=extproc))))

Sid_list_listner=

(sid_list=

(sid_desc=

(ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1)

(sid_name=nijam))

)

Then save the file.

[oracle@nijam admin]$lsnrctl

Lsnctl>start —>a starts the listener with the name specified.will be used.

Lsnrctl>stop —>a stop the listener.

Lsnrctl>status —>information about the listener.

Lsnrctl>reload —>a forces a read of the configuration file in orderfor new settings to take effect without stopping and starting the listener.

Lsnrctl>exit —>a the lsnrctl utility.

check whether port is available or not.

[oracle@nijam]$netstat -tunlp|grep 1521.

TNS_ADMIN is an environment variable that points to the directory where the SQL*Net configuration files (like sqlnet.ora and tnsnames.ora) are located.

Windows:

set TNS_ADMIN=%ORACLE HOME%\network\admin

Unix/ Linux:

export TNS_ADMIN=$ORACLE HOME/network/admin

DB AUTOMATIC STARTUP – SHUTDOWN in Oracle Linux

December 1, 2016

By postgresdba

DATABAES NAME : nijam
[oracle@nijam ~]$ ps -ef |grep pmon
oracle 3630 1 0 16:42 ? 00:00:00 ora_pmon_nijam
oracle 4297 4190 0 16:44 pts/1 00:00:00 grep pmon
[root@nijam ~]# vi /etc/oratab
nijam:/u01/app/oracle/product/10.2.0/db_1:Y
[oracle@nijam bin]$ dbshut
[oracle@nijam ~]$pwd
[oracle@nijam ~]$ cd /u01/app/oracle/product/10.2.0/db_1/bin/
[oracle@nijam ~]$ ps -ef |grep pmon
oracle 4297 4190 0 16:44 pts/1 00:00:00 grep pmon
[oracle@nijam bin]$ dbstart
[oracle@nijam ~]$ ps -ef |grep pmon
oracle 3630 1 0 16:42 ? 00:00:00 ora_pmon_nijam
oracle 4297 4190 0 16:44 pts/1 00:00:00 grep pmon

[root@nijam ~]# cd /etc/init.d/

[root@nijam init.d]# vi dbora

#!/bin/sh -x
#
# file: /etc/init.d/dbora
# chkconfig: 2345 80 30
# description: nijam
#
#

# the user that oracle runs as (default: oracle)
ORACLE=oracle

# database instance id
ORACLE_SID=nijam

# ORACLE_HOME (file system) path
ORACLE_HOME=/u01/app/oracle/product/10.2.0/db_1

PATH=${PATH}:$ORACLE_HOME/bin

export ORACLE_SID ORACLE_HOME PATH

case $1 in
‘start’)
su – $ORACLE -c $ORACLE_HOME/bin/dbstart
;;
‘stop’)
su – $ORACLE -c $ORACLE_HOME/bin/dbshut
;;
*)
echo “usage: $0 {start|stop}”
;;
esac

exit

[root@nijam etc]# chmod +x dbora

There are 7 run levels in Linux environment.
Run-level Name Description

0 Halt Shuts down the system.
1 Single-User Mode Mode for administrative tasks.
2 Multi-User Mode No network interfaces and services
3 Multi-User Mode Networking Starts the system normally.
4 Not used For special purposes.
5 With Display As runlevel 3 + Gui
6 Reboot Reboots the system.

Chkconfig command will create start and kill scripts in /etc/rc[runlevel].d folders.

[root@nijam etc]# ls rc*
rc.d:
init.d rc0.d rc2.d rc4.d rc6.d rc.sysinit
rc rc1.d rc3.d rc5.d rc.local

[root@nijam etc]# chkconfig –add –level 35 dbora
[root@nijam etc]# du -a |grep dbora
0 ./rc.d/rc2.d/S80dbora
0 ./rc.d/rc1.d/K30dbora
0 ./rc.d/rc3.d/S80dbora
0 ./rc.d/rc0.d/K30dbora
0 ./rc.d/rc6.d/K30dbora
4 ./rc.d/init.d/dbora
0 ./rc.d/rc4.d/S80dbora
0 ./rc.d/rc5.d/S80dbora
[root@nijam etc]# chkconfig –list dbora
dbora 0:off 1:off 2:on 3:on 4:on 5:on 6:off
[oracle@nijam bin]$ ps -ef|grep pmon
ordacle 4722 1 0 16:54 ? 00:00:00 ora_pmon_nijam
oracle 4769 4190 0 16:54 pts/1 00:00:00 grep pmon
[root@nijam etc]# init 6
oracle 4769 4190 0 16:54 pts/1 00:00:00 grep pmon

Rac-GRID 11gR2 -oracle Installation on Linux 6

December 1, 2016

By postgresdba

GRID 11gR2 Installation & Configuration

From Oracle Linux 6 DVD
rpm -Uvh binutils-2.*
rpm -Uvh compat-libstdc++-33*
rpm -Uvh elfutils-libelf-0.*
rpm -Uvh libaio-0.*
rpm -Uvh libaio-devel-0.*
rpm -Uvh sysstat-9.*
rpm -Uvh glibc-2.*
rpm -Uvh glibc-common-2.*
rpm -Uvh glibc-devel-2.* glibc-headers-2.*
rpm -Uvh ksh-2*
rpm -Uvh make-3.*
rpm -Uvh libgcc-4.*
rpm -Uvh libstdc++-4.*
rpm -Uvh libstdc++-4.*.i686*
rpm -Uvh libstdc++-devel-4.*
rpm -Uvh gcc-4.*x86_64*
rpm -Uvh gcc-c++-4.*x86_64*
rpm -Uvh –allfiles elfutils-libelf-0*x86_64* elfutils-libelf-devel-0*x86_64*
rpm -Uvh elfutils-libelf-0*i686* elfutils-libelf-devel-0*i686*
rpm -Uvh libtool-ltdl*i686*
rpm -Uvh ncurses*i686*
rpm -Uvh readline*i686*
rpm -Uvh unixODBC*
rpm -ivh sysstat-9*

Edit ” /etc/sysctl.conf” file.
fs.aio-max-nr = 1048576
fs.file-max = 6815744
#kernel.shmall = 2097152
#kernel.shmmax = 1054504960
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default=262144
net.core.rmem_max=4194304
net.core.wmem_default=262144
net.core.wmem_max=1048586

/sbin/sysctl -p

Edit “/etc/security/limits.conf” file.
* soft nproc 2047
* hard nproc 16384
* soft nofile 4096
* hard nofile 65536
* soft stack 10240

“/etc/pam.d/login” file, if it does not already exist.
session required pam_limits.so

If you are not using DNS, the “/etc/hosts” file must contain the following information.
127.0.0.1 localhost
# Public
192.168.0.131 grid.localdomain grid

Create the new groups and users (grid and oracle)
groupadd -g 54321 oinstall
groupadd -g 54322 dba
groupadd -g 54323 oper
groupadd -g 54327 asmdba
groupadd -g 54328 asmoper
groupadd -g 54329 asmadmin

useradd -u 54321 -g oinstall -G dba,oper,asmadmin,asmdba,asmoper grid
passwd grid

useradd -g oinstall -G dba,oper,asmadmin,asmdba,asmoper oracle
passwd oracle

# vi /etc/selinux/config
SELINUX=diabled

Stop Firewall Sevice
# service iptables stop
# chkconfig iptables off

# service ip6tables stop
# chkconfig ip6tables off

NTP
# service ntpd stop
# chkconfig ntpd off
# mv /etc/ntp.conf /etc/ntp.conf.bak

Create Directory For GRID install
mkdir -p /u01/app/11.2.0/grid
mkdir -p /u01/app/grid
chown -R grid:oinstall /u01
chmod -R 775 /u01/

Create Directory For database install
mkdir -p /u01/app/oracle/product/11.2.0/db_1
chown -R oracle:oinstall /u01/app/oracle/product/11.2.0/db_1
chmod -R 775 /u01

Login as the Grid User and add the lines at the end of the “.bash_profile” file
# Grid Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=grid.localdomain; export ORACLE_HOSTNAME
ORACLE_BASE=/u01/app/grid; export ORACLE_BASE
ORACLE_HOME=/u01/app/11.2.0/grid; export ORACLE_HOME
ORACLE_SID=+ASM; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

Login as the oracle user and add the lines at the end of the “.bash_profile” file
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_HOSTNAME=grid.localdomain; export ORACLE_HOSTNAME
ORACLE_UNQNAME=tilak; export ORACLE_UNQNAME
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1; export ORACLE_HOME
ORACLE_SID=tilak; export ORACLE_SID
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH

Configure ASMLib by running the following as the root user
# /etc/init.d/oracleasm configure

Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets (‘[]’). Hitting without typing an
answer will keep that current value. Ctrl-C will abort.

Default user to own the driver interface []: grid
Default group to own the driver interface []: asmadmin
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver: [ OK ]
Scanning the system for Oracle ASMLib disks: [ OK ]

Create ASM disks. Create the ASM disks on any one node as the root user.
# /etc/init.d/oracleasm createdisk VOL1 /dev/sdc1
# /etc/init.d/oracleasm scandisks
# /etc/init.d/oracleasm listdisks