Posts Tagged ‘oracle’


What does it contain or we expect from this release?

  1. Tagged as a fully autonomous database if your database in Oracle Public Cloud
  2. If you are not in Oracle Public cloud, you will not be getting fully autonomous features
  3. Self-patching sounds to me like Oracle Configuration Management feature enhancements

Hope this helps…

Nimai Karmakar


Read Full Post »

Hi Guys,


Last day I just installed and was thinking to let start with some basics on oracle 12c like what is it? why we need it?

what are the new features available?


So after some research on that (although it was not so hard.. :)) I came to know with some below points.


New Multitenant Architecture
Designed for the cloud, Oracle Multitenant delivers a new architecture that simplifies consolidation and delivers the high density of schema based consolidation, but without requiring changes to existing applications. It’s an option of Oracle Database 12c Enterprise Edition that offers all the benefits of managing many databases as one, yet retains the isolation and resource control of separate databases. In this new architecture, a single multitenant container database can host many ‘pluggable’ databases. Each database consolidated or ‘plugged in’ to a multitenant container looks and feels to applications the same as for existing Oracle databases. Accessing pluggable databases is the same as for existing Oracle databases, and administrators can control the prioritization of available resources between consolidated databases.


Manage Many Databases As One
Consolidating multiple databases means that administrators have fewer databases to manage and the benefits of managing many databases as one can permeate throughout the data center. It is fairly common for IT organizations to manage hundreds, if not thousands of databases, and each of those databases -be they for production, test or development -requires maintenance.


Fewer Patches and Upgrades
Applying patches, patch set updates and product updates to multiple (non-consolidated) databases in order to maintain currency of database releases can be a challenge that only gets more difficult with the number of databases managed. All databases in the data center, regardless of their production, test or development status, are subject to patching and upgrading. Consolidating multiple pluggable databases into a multitenant container can dramatically reduce the amount of patching and upgrade activities required. With Oracle Databases 12c, patches and upgrades are applied at the container level, and not applied to individual pluggable databases. Customers also have the flexibility of creating new patched and/or upgraded container databases and selectively unplugging databases from older container databases and plugging into new upgraded container databases.


Fewer Backups
Every good database administrator regularly takes backup copies of all databases managed in the data center. However, instead of having to backup each separate database in the data center, the new pluggable architecture only requires executing backups at the multitenant container level. This means that database administrators have fewer backups to manage, as each pluggable database in a container database is automatically backed up. However, in the event of any problem encountered with an individual pluggable database, database administrators have the flexibility to perform data recovery operations at the pluggable database level within a container database.

Fewer Standby Databases
Another good example of the benefits of managing many pluggable databases as one is maintaining standby databases. Many Oracle customers use replication technologies such as Oracle Data Guard or Oracle Active Data Guard to maintain complete working copies of production databases by shipping and applying redo logs to a standby system usually in a separate location. With Oracle Database 12c, shipping and applying of redo logs occurs at the container level, therefore every pluggable database consolidated into a container is fully protected from site outages. In addition, customers using Oracle Active Data Guard can make active use of their standby pluggable databases for reporting and other purposes.


Database Resource Management (This one is good..)
While there are undoubtedly many benefits from consolidating multiple databases, customers should consider all aspects of database consolidation. An obvious question to ask is “how can I guarantee the prioritization of resources in this new multitenant architecture?” After all, many of the benefits of managing many database as one will be quickly lost if database applications are continually vying for available system resources, and user performance service levels start to drop off.
Using Oracle Database 12c’s resource management features, database administrators can easily define resource utilization priorities at the pluggable database level. The database server pro-actively monitors database usage to ensure that each pluggable database in a container stays within pre-defined minimum and maximum resource thresholds. For example, at quarter-end, a high priority ERP application can automatically pull system resources from low priority applications in the container to ensure users performance service level remain consistent even at peak demand.


I got all the points from an oracle documentation or you can say oracle white paper, I only have noted which seems important for DBAs to begin with.Click on the link to open it.


and then after installing oracle 12c and it was not a big deal, I thought from where should I start… what is multitenant/container/plugable.

A container is either a PDB or the root. The root container is a collection of schemas, schema objects, and nonschema objects to which all PDBs belong.

Every CDB has the following containers:

  • Exactly one root

    The root stores Oracle-supplied metadata and common users. An example of metadata is the source code for Oracle-supplied PL/SQL packages. A common user is a database user known in every container. The root container is named CDB$ROOT.

  • Exactly one seed PDB

    The seed PDB is a system-supplied template that the CDB can use to create new PDBs. The seed PDB is named PDB$SEED. You cannot add or modify objects in PDB$SEED.

  • Zero or more user-created PDBs

    A PDB is a user-created entity that contains the data and code required for a specific set of features. For example, a PDB can support a specific application, such as a human resources or sales application. No PDBs exist at creation of the CDB. You add PDBs based on your business requirements.


Oracle 12c cloud architecture


You can use the same administration tools for both CDBs and non-CDBs.

For example, you can use the following tools in a multitenant environment:

  • SQL*Plus for command-line access
  • Oracle Enterprise Manager Cloud Control (Cloud Control)

    Cloud Control is an Oracle Database administration tool that provides a graphical user interface (GUI). Cloud Control supports Oracle Database 12targets, including PDBs, CDBs, and non-CDBs.

  • Oracle Enterprise Manager Database Express (EM Express)

    EM Express is a web-based management product built into the Oracle database. EM Express enables you to provision and manage PDBs, including the following operations:

    • Creating and dropping PDBs
    • Plugging in and unplugging and PDBs
    • Cloning PDBs
    • Setting resource limits for PDBs
  • Oracle Database Configuration Assistant (DBCA)

    DBCA enables you to create CDBs or non-CDBs, and create, plug, and unplug PDBs.

  • Oracle Multitenant Self-Service Provisioning application

    This application enables the self-service provisioning of PDBs. CDB administrators control access to this self-service application and manage quotas on PDBs.


Okay this is as quick post to begin with, soon I will post some more points to begin with. Please feel free to post your comments or questions and experts are always welcome with there valuable points.


Thanks & Regards

Nimai Karmakar

Read Full Post »

Oracle Enterprise Manager Cloud Control Installation on Solaris Spark 64bit


As stated earlier on my post about the installation of Oracle 12c enterprise manager cloud control installation. So , friends this post belong to the installation….

Please comment your suggestion or any feedback.


The minimal Hardware requirement for OMS (got the data from metalink docs) –


1 OMS, < =1000 targets, <100 agents 2 OMSes for < =10,000 targets and <1000 agents >2 OMSes, >=10,000 targets, >=1000 agents
CPU Cores/Host 2 4 8
RAM 4 GB 6 GB 8 GB
RAM with ADP, JVMD 6 GB 10 GB 14 GB
Oracle WebLogic Server JVM Heap Size 512 MB 1 GB 2 GB
Hard Disk Space 7 GB 7 GB 7 GB
Hard Disk Space with ADP, JVMD 10 GB 12 GB 14 GB


Note – If you have Enterprise Manager 10g Grid Control Release 4 ( or lower installed, then you must first upgrade to Enterprise Manager 10g Grid Control Release 5 ( or Enterprise Manager 11g Grid Control Release 1 ( before you can upgrade to the new release.


Before proceeding please drop the old enterprise manager repository database.

Follow the steps to drop the old EM (Single Instance) repository –

  1. $ORACLE_HOME/bin/emca <space>-deconfig <space> dbcontrol <space> db<space> -repos <space> drop <space> – SYS_PWD <SYS Password> <space> – SYSMAN_PWD <SYSMAN Password>
  2. Enter the following information:

Database SID: DBTEST11G

Listener port number: 1525

To de-configure Database Control for a Real Application Clusters (RAC) database, follow the following command on database host:

  1. Set the environment variable <ORACLE_HOME>.
  2. Now run the following command:

$ORACLE HOME/bin/emca <space> -deconfig <space> dbcontrol <space> db <space> -repos <space>drop <space> -cluster <space> -SYS_PWD <space> <sys password> <space> -SYSMAN_PWD <sysman password>



Oracle strongly suggest to make below changes on your database before start 12c installation Init.ora parameter dedicated to EM12.







MEMORY_TARGET => should not be used

Or if your using and using automatic memory management then no need to update SGA_TARGET or PGA_AGGREGATE_TARGET


Now that the environment and repository database is ready let’s start the installation.

First we need to update the parameters as per our environment.

SQL> alter system set shared_pool_size=600M scope=both sid=’*’;

alter system set PGA_AGGREGATE_TARGET=1G scope=both sid=’*’;

alter system set PROCESSES=300 scope=spfile sid=’*’;

alter system set sessions=335 scope=spfile sid=’*’;

(as the 10g rule applied for 1.1*process+5)

alter system set JOB_QUEUE_PROCESSES=20 scope=both sid=’*’;

alter system set SESSION_CACHED_CURSORS=300 scope=spfile sid=’*’;

Oracle recommended to use UNDO tablespace is at least 200M in size.


In 11g version We need to install Weblogic separately but in 12c its comes by default .(Default version is 10.3.5

Please be sure DO NOT SET ORACLE_HOME or ORACLE_SID before start installation

Oracle also recommended to set umask 022 before start installation

Now we can start 12.1 installation.


Go to 12.1 setup path and type below command to can start installation



  • ./runInstaller


  • Optional screen to provide oracle support credentials.


  • If you are skipping the support credentials page. Just press yes and proceed.



  • Next screen is the prerequisite check(here we got a failed prerequisite check ulimit)


To solve the ulimit error we need to add set ulimit = 4096 /etc/system file if available but in our scenario as it is a virtual server we have to add it to /etc/project file.


save the file and do source /etc/project and check it

bash-3.2$ projects -l


projid : 0

comment: “”

users : (none)

groups : (none)



projid : 1

comment: “”

users : (none)

groups : (none)



projid : 2

comment: “”

users : (none)

groups : (none)



projid : 3

comment: “”

users : (none)

groups : (none)



projid : 10

comment: “”

users : (none)

groups : (none)



projid : 100

comment: “”

users : (none)

groups : (none)

attribs: project.max-sem-ids=(privileged,100,deny)






  •  Once the prerequisite have passed we can proceed with Installation type.


Here we need to select the installation type depend upon our environment to monitor.

If you use Simple than Cloud Control will install by default plugins. (Exadata ,Oracle Database, Fusion Middleware, My Oracle Support)

  • Next we have to provide the location for Middleware and Agent Binaries.


  • Next we need to supply the oms admin credentials with the DB details where you want to install the management repository database.


While specifying Database Host name please be sure to use the fully qualified domain name like testDB.oracle.com or you can also supply the host IP.

  • Review the setup and click next.


  • In addition after proceeding with the setup you may get yes/no and ok/cancel screen just review and proceed further.


  • After the installation is successful. Just execute the root.sh

Now wait for around 45 minutes to complete the setup and open the URL at the browser…


  1. After everything is done successfully. Use the link to open the enterprise manager console.



Login with the username sysman and the password you have provided during installation.


  To check the status of enterprise manager agent.

cd $AGENT_HOME/bin

bash-3.2$ ./emctl status agent

Oracle Enterprise Manager Cloud Control 12c Release 4

Copyright (c) 1996, 2014 Oracle Corporation. All rights reserved.


Agent Version         :

OMS Version           :

Protocol Version       :

Agent Home             : /u01/oracle/enterprise_mang/agent/agent_inst

Agent Log Directory   : /u01/oracle/enterprise_mang/agent/agent_inst/sysman/log

Agent Binaries         : /u01/oracle/enterprise_mang/agent/core/

Agent Process ID      : 5969

Parent Process ID     : 5918

Agent URL             : https://testDB:3872/emd/main/

Local Agent URL in NAT : https://testDB:3872/emd/main/

Repository URL         : https://testDB:1159/empbs/upload

Started at             : 2014-09-15 21:24:42

Started by user       : oracle

Operating System       : SunOS version 5.10 (sparcv9)

Last Reload           : (none)

Last successful upload                       : 2014-09-16 12:16:28

Last attempted upload                       : 2014-09-16 12:16:28

Total Megabytes of XML files uploaded so far : 2.06

Number of XML files pending upload           : 0

Size of XML files pending upload(MB)         : 0

Available disk space on upload filesystem   : 44.19%

Collection Status                           : Collections enabled

Heartbeat Status                             : Ok

Last attempted heartbeat to OMS             : 2014-09-16 12:17:03

Last successful heartbeat to OMS             : 2014-09-16 12:17:03

Next scheduled heartbeat to OMS             : 2014-09-16 12:18:03



Agent is Running and Ready




Hope that will help a bit…


Thanks & Regards

Nimai Karmakar

Read Full Post »

A quick sharing….

Last few days bask we were have to migrate a schema from 10g R2 on Solaris 10 64 bit to 11g R2  on windows 32 bit using traditional exp/imp. Exp was successful (as usual), but while importing we were facing below error.

Connected to: Oracle Database 11g Express Edition Release – Production

Export file created by EXPORT:V10.02.01 via conventional path

Warning: the objects were exported by SYSTEM, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
export client uses US7ASCII character set (possible charset conversion)
. importing USER1’s objects into USER2

ORA-01435: user does not exist
Import terminated successfully with warnings.

we were using below syntax…..

imp system/oracle@DB_NAME file=’c:\dumpfile.dmp’ log=c:\logfile.log grants=n fromuser=<Schema_from>touser=<Schema_to>;

We were facing this issue from last 3 days, finally found the cause of the problem(A silly mistake):

The above command is working as a cmd command other than sql/plsql command so we should not use “;” at the end of the syntax.


imp system/oracle@***** file=’c:\dumpfile.dmp’ log=c:\logfile.log grants=n fromuser=<Schema_from> touser=<Schema_to>

this will sort out the error…..

Hope this helps..

Thanks & Regards

Nimai Karmakar

Read Full Post »


Few days back we have a requirement to install oracle 12c enterprise manager cloud control for a Solaris Server 10 with 16 database to be monitored.

So, it was a good opportunity for me to learn and share , so as discussed on my earlier post for some griding.

Below are some of the basic concepts which help me from docs.


In case you want to know anything other than this please comment ….



Oracle 12c Enterprise manager Cloud control basic concepts……




Monitoring Overview

Enterprise Manager Cloud Control monitoring functionality permits unattended monitoring of your IT environment. Enterprise Manager comes with a comprehensive set of performance and health metrics that allows monitoring of key components in your environment, such as applications, application servers, databases, as well as the back-end components on which they rely (such as hosts, operating systems, storage).

The Management Agent on each monitored host monitors the status, health, and performance of all managed components (targets) on that host. If a target goes down, or if a performance metric crosses a warning or critical threshold, an event is triggered and sent to Enterprise Manager. Administrators or any interested party can be notified of the triggered event through the Enterprise Manager Notification system.


Adding targets to monitor is simple. Enterprise Manager provides you with the option of either adding targets manually or automatically discovering all targets on a host. Enterprise Manager can also automatically and intelligently apply monitoring settings for newly added targets.


As your data center grows, it will become more challenging to manage individual targets separately, thus you can use Enterprise Manager’s group management functionality to organize large sets of targets into groups, allowing you to monitor and manage many targets as one.



Comprehensive Out-of-Box Monitoring

Monitoring begins as soon as you install Enterprise Manager Cloud Control 12c. Enterprise Manager’s Management Agents automatically start monitoring their host’s systems (including hardware and software configuration data on these hosts) as soon as they are deployed and started. Enterprise Manager provides auto-discovery scripts that enable these Agents to automatically discover all Oracle components and start monitoring them using a comprehensive set of metrics at Oracle-recommended thresholds.

Metrics from all monitored components are stored and aggregated in the Management Repository, providing administrators with a rich source of diagnostic information and trend analysis data. When critical alerts are detected, notifications are sent to administrators for rapid resolution.

Out-of-box, Enterprise Manager monitoring functionality provides:

  • In-depth monitoring with Oracle-recommended metrics and thresholds.
  • Monitoring of all components of your IT infrastructure (Oracle and non-Oracle) as well as the applications and services that are running on them.
  • Access to real-time performance charts.
  • Collection, storage, and aggregation of metric data in the Management Repository. This allows you to perform strategic tasks such as trend analysis and reporting.
  • E-mail and pager notifications for detected critical events.

Enterprise Manager can monitor a wide variety of components (such as databases, hosts, and routers) within your IT infrastructure.

Some examples of monitored metrics are:

  • Archive Area Used (Database)
  • Segments Approaching Maximum Extents Count (Database)
  • Network Interface Total I/O Rate (Host)

Monitoring: Basics

Enterprise Manager Cloud Control 12c comes with a comprehensive set of predefined performance and health metrics that enables automated monitoring of key components in your environment, such as applications, application servers, databases, as well as the back-end components on which they rely, such as hosts, operating systems, storage. While Enterprise Manager can monitor for many types of conditions (events), the most common use of its monitoring capability centers around the basics of monitoring for violation of acceptable performance boundaries defined by metric values.

Some metrics have associated predefined limiting parameters called thresholds that cause metric alerts (specific type of event) to be triggered when collected metric values exceed these limits. Enterprise Manager allows you to set metric threshold values for two levels of alert severity:

  • Warning – Attention is required in a particular area, but the area is still functional.
  • Critical – Immediate action is required in a particular area. The area is either not functional or indicative of imminent problems.

Hence, thresholds are boundary values against which monitored metric values are compared. For example, for each disk device associated with the Disk Utilization (%) metric, you might define a warning threshold at 80% disk space used and critical threshold at 95%.




Blackouts allow you to support planned outage periods to perform scheduled or emergency maintenance. When a target is put under blackout, monitoring is suspended, thus preventing unnecessary alerts from being sent when you bring down a target for scheduled maintenance operations such as database backup or hardware upgrade. Blackout periods are automatically excluded when calculating a target’s overall availability.

A blackout period can be defined for individual targets, a group of targets or for all targets on a host. The blackout can be scheduled to run immediately or in the future, and to run indefinitely or stop after a specific duration. Blackouts can be created on an as-needed basis, or scheduled to run at regular intervals. If, during the maintenance period, you discover that you need more (or less) time to complete maintenance tasks, you can easily extend (or stop) the blackout that is currently in effect. Blackout functionality is available from both the Enterprise Manager console as well as via the Enterprise Manager command-line interface (EM CLI).



For a typical monitoring scenario, when a target becomes unavailable or if thresholds for performance are crossed, events are raised and notifications are sent to the appropriate administrators. Enterprise Manager supports notifications via e-mail, pager, SNMP traps, or by running custom scripts and allows administrators to control these notification mechanisms through:

  • Notification Methods
  • Rules and Rule Sets


Notification Methods

A notification method represents a specific way to send notifications. Besides e-mail, there are three types of notification methods: OS Command, PL/SQL, SNMP Traps.


A rule instructs Enterprise Manager to take specific action when events or incidents (entity containing one important event or related events) occur, such as notifying an administrator or opening a helpdesk ticket.


Incident Manager

Enterprise Manager Cloud Control simplifies managing incidents through an intuitive UI called Incident Manager. Incident Manager provides and easy-to-use interface that allows you to search, view, manage, and resolve incidents and problems impacting your environment. To access Incident Manager, from the Enterprise menu, select Monitoring, and then Incident Manager.


Accessing Monitoring Information

Enterprise Manager provides multiple ways to access monitoring information. The primary focal point for incident management is the Incident Manager console, however Enterprise Manager also provides other ways to access monitoring information. The following figures show the various locations within Enterprise Manager that display target monitoring information. The following figure shows the Enterprise Manager Overview page that conveniently displays target status rollup and rollup of incidents.





Discovering, Promoting, and Monitoring

Enterprise Manager Cloud Control (Cloud Control) enables you to discover, promote, add, and then monitor software deployments across your network, using a single GUI-rich console.

What is Discovery?

Discovery refers to the process of identifying unmanaged hosts and targets in your environment. You can discover hosts and targets automatically or manually.



Autodiscovery Process

For discovery of a host, the autodiscovery process enables a Management Agent running on the host to run an Enterprise Manager job that scans for unmanaged hosts. You then convert these unmanaged hosts to managed hosts by deploying Management Agents on these hosts.


Guided Discovery Process

The guided discovery process enables you to explicitly add a specific database target as a target to bring under management. The discovery wizard guides you through the process and most of the specifications required are filled by default.

The benefits of using this process are as follows:

  • You can find targets with less effort.
  • You can find a new database that has been added recently even if autodiscovery has not been run.
  • You can find a non-promoted database that already exists in autodiscovery results, but has a change in details. For example, the port.
  • You eliminate unnecessary consumption of resources on the Management Agent when discovery is not needed.


Specifying Target Monitoring Properties

Specifying target monitoring properties enables you to manually specify all the details required to discover the database target, such as the host name and location, target name and location, and other specific information.


What is Promotion?

Promotion refers to the process of converting unmanaged hosts and targets, which have been discovered in your network, to managed hosts and targets in Cloud Control so that they can be monitored and managed efficiently. While conversion of unmanaged hosts to managed hosts involves deployment of a Management Agent on those hosts, conversion of unmanaged targets running on those hosts to managed targets involves only adding the targets as manageable entities in Cloud Control without deploying any additional component on the hosts.


What is Monitoring?

Monitoring refers to the process of gathering information and keeping track of activity, status, performance, and health of targets managed by Cloud Control on your host. A Management Agent deployed on the host in conjunction with plug-ins monitors every managed target on the host.


Hope this serve the purpose a bit..

Thanks & Regards

Nimai Karmakar







Read Full Post »

Hello friends,

Last few days I was doing some resarch for RAC as for my new project on Extended RAC and after doing the RnD I got some good sites from where I got the good knowledge on RAC . So,Today I am going to discuss some of the major topics that are needed for Oracle RAC, like

what is RAC ?

what is voting / quorrom disks?

what is RAC architecture ?

what is clsuter?

what commands should I follow for basic RAC? and all that.These were the questions which I got in my mind before starting. If you guys have any questions or any suggestions in minds then I urge you that Please post that for me and yourself.

Few of the below topics are also helpful for Oracle 11gR2 Extended RAC.

First and foremost , What is RAC or why to use RAC?

When more instances are used to access the same data (database) we need to put that insnces in a cluster. The clusterware, assure that the management of data is done correctly (for instance, the same data is not modified in the same time by 2 users, even if the users access the database by 2 or more instances). The clusterware can be bought from another vendor than the database vendor. Oracle offers a solution for the clusterware as well. In this case we speak about the Oracle clusterware. When the Oracle database is installed on a clusterware (Oracle or not) we speak about an Oracle RAC or Oracle Real Application Cluster. The Oracle RAC and Oracle clusterware is not necessarily the same thing. The Oracle RAC installation includes the Oracle clusterware installation.

What are the major diffrences between Oracle 11g R1 and Oracle 11g R2 ?

Oracle 11g R1 RAC :

  •  ADDM for RAC
  • ADR command-line tool – Oracle Automatic Diagnostic repository (ADR) has a new command-line interface named ADRCI, ADR Command Interface.
  • ADRCI can be used to access the 11g monitoring log
  •  Optimized RAC cache fusion protocols
  •  Oracle 11g RAC Grid provisioning .

Oracle 11g R2 RAC :

With the added privileges of 11g R1 below are added privileges.

  • Ocr and Voting can be stored on asm
  • ASMCA (ASM Configuration Assistance)
  • SCAN (Single client access name)
  • Global AWR
  • Server Pooling
  • Default, LOAD_BALANCE is ON.
  • GSD, gsdctl utility introduced.
  • RAC OneNode can be done.
  • HAS (High availability service), oracle restart
  • 11gR2 Grid Infrastructure Redundant Interconnect and ora.cluster_interconnect.haip
  •  Grid Plug and play
  • ASM : fast start mirror resych
  • ASM : Disk check : “alter diskgroup diskgroup_name check .”
  • ASM : Diskgroup can be mounted as restricted .
  • ASM : Can use force option to mount / drop disk group. (mount by doing offline to unavailable disk if quorum exists )
  • sysasm role introduced.
  • 11g asm : can keep ocr and voting disk in asm , sysasm role , variable extent size ,md_backup/md_restore , raw device concept is obsolete , can rename a diskgroup
  • Hot patching .
  • Oracle 11g RAC parallel upgrades , Oracle 11g have rolling upgrade features
  • Can have same home for cluster resources and asm
  • ASM metadata backup can be done.

What is Voting/quorum disk ?

Voting Disk: – Manages cluster membership by way of a health check and arbitrates cluster ownership among the instances in case of network failures. RAC uses the voting disk to determine which instances are members of a cluster. The voting disk must reside on shared disk. For high availability, Oracle recommends that you have multiple voting disks. The Oracle Clusterware enables multiple voting disks.

  1. All the nodes ping the voting disk;
  2. If the cluster see that a node cannot ping the voting disk, the cluster consider that node that is no longer a valid node and evicts the node from the cluster;
  3. This file is very important for the cluster and must be mirrored.

!!!  One file system must have 560 MB of available space for the primary OCR and a voting disk.   !!!

why voting disk are always recommended in odd number ?

Oracle recommends to configure odd number of voting disks.Each node in the cluster can survive only if it can access more than half of the voting disk.An odd number of voting disks is required for proper clusterware configuration. A node must be able to strictly access more than half of the voting disks at any time. So, in order to tolerate a failure of n voting disks, there must be at least 2n+1 configured. (n=1 means 3 voting disks). You can configure up to 31 voting disks, providing protection against 15 simultaneous disk failures.
If you lose 1/2 or more of all of your voting disks, then nodes get evicted from the cluster, or nodes kick themselves out of the cluster. It doesn’t threaten database corruption. Alternatively you can use external redundancy which means you are providing redundancy at the storage level using RAID.
For this reason when using Oracle for the redundancy of your voting disks, Oracle recommends that customers use 3 or more voting disks.

What is OCR / Oracle cluster registry file and what is the use of this file ?

Oracle Cluster Registry (OCR) :- Maintains cluster configuration information as well as configuration information about any cluster database within the cluster. The OCR also manages information about processes that Oracle Clusterware controls. The OCR stores configuration information in a series of key-value pairs within a directory tree structure. The OCR must reside on shared disk that is accessible by all of the nodes in your cluster. The Oracle Clusterware can multiplex the OCR and Oracle recommends that you use this feature to ensure cluster high availability.

This is the central repository for the Oracle cluster: there we find all the information about the cluster in real-time;

OCR relies on a distributed shared-cache architecture for optimizing queries against the cluster repository. Each node in the cluster maintains an in-memory copy of OCR, along with an OCR process that accesses its OCR cache.

When OCR client application needs to update the OCR, they communicate through their local OCR process to the OCR process that is performing input/output (I/O) for writing to the repository on disk.

The OCR client applications are Oracle Universal Installer (OUI), SRVCTL, Enterprise Manger (EM), Database Configuration Assistant (DBCA), Database Upgrade Assistant(DBUA), NetCA and Virtual Internet Protocol Configuration assistant (VIPCA). OCR also maintains dependency and status information for application resources defined within CRS, specifically databases, instances, services and node applications.

Note:- The name of the configuration file is ocr.loc and the configuration file variable is ocrconfig.loc

  •      CRS update the OCR with the information about the node failure or reconfiguration;
  •      CSS update the OCR when a node is added or deleted;
  •     NetCA, DBCA, SRVCTL update the OCR with the services information;
  •     This is a binary file and cannot be edited;
  •     The OCR information is cached on each node;
  •     Only one node (the master node) can update the OCR file => The master node has the OCR cache up-to-date in real time;
  •     OCR file is automatically backed up in the OCR location every 4 hours:

cd $GRID_HOME/cdata/<cluster name>

backup00.ocr backup01.ocr backup02.ocr day.ocr day_.ocr week.ocr

  • The OCR files are backed up for a week and overwritten in a circular manner;
  • Because the OCR is a key component of the Oracle cluster, the OCR file must be mirrored;
  • The OCR file can be exported, imported with ocrconfig command;

Note:- You can replace a failed OCR online, and you can update the OCR through supported APIs such as Enterprise Manager, the Server Control Utility (SRVCTL) or the Database Configuration Assistant (DBCA

Below are some basic points which you must aware of.

Where are the clusterware files stored on a RAC environment?

The clusterware is installed on each node (on an Oracle Home) and on the shared disks (the voting disks and the CSR file)

Where are the database software files stored on a RAC environment?

The base software is installed on each node of the cluster and the database storage on the shared disks.

What kind of storage we can use for the shared clusterware files?

  1. OCFS (Oracle cluster file system)
  2. raw devices
  3. ASM  

What is a CFS?

A cluster File System (CFS) is a file system that may be accessed (read and write) by all members in a cluster at the same time. This implies that all members of a cluster have the same view.

Which files can be placed on shared disk?

  1. Oracle files (controlfiles, datafiles, redologs, files described by the bfile datatype)
  2. Shared configuration files (spfile)
  3. OCR and voting disk
  4. Files created by Oracle during runtime

What is a raw device?

A raw device is a disk drive that does not yet have a file system set up. Raw devices are used for Real Application Clusters since they enable the sharing of disks.

What is a raw partition?

A raw partition is a portion of a physical disk that is accessed at the lowest possible level. A raw partition is created when an extended partition is created and logical partitions are assigned to it without any formatting. Once formatting is complete, it is called cooked partition.

What CRS is?

Oracle RAC 10g Release 1 introduced Oracle Cluster Ready Services (CRS), a platform-independent set of system services for cluster environments. In Release 2, Oracle has renamed this product to Oracle Clusterware.

What is VIP IP used for?

It returns a dead connection IMMIDIATELY, when its primary node fails. Without using VIP IP, the clients have to wait around 10 minutes to receive ORA-3113: “end of file on communications channel”. However, using Transparent Application Failover (TAF) could avoid ORA-3113.

Is the SSH, RSH needed for normal RAC operations?

No. SSH or RSH are needed only for RAC, patch set installation and clustered database creation.

Do we have to have Oracle RDBMS on all nodes?

Each node of a cluster that is being used for a clustered database will typically have the RDBMS and RAC software loaded on it, but not actual data files (these need to be available via shared disk).

Does Real Application Clusters support heterogeneous platforms?

No,The Real Application Clusters do not support heterogeneous platforms in the same cluster.

What is the Cluster Verification Utiltiy (cluvfy)?

The Cluster Verification Utility (CVU) is a validation tool that you can use to check all the important components that need to be verified at different stages of deployment in a RAC environment.

What versions of the database can I use the cluster verification utility (cluvfy) with?

The cluster verification utility is release with Oracle Database 10g Release 2 but can also be used with Oracle Database 10g Release 1.

What is hangcheck timer used for ?

The hangcheck timer checks regularly the health of the system. If the system hangs or stop the node will be restarted automatically.
There are 2 key parameters for this module:

  • hangcheck-tick: this parameter defines the period of time between checks of system health. The default value is 60 seconds; Oracle recommends setting it to 30seconds.
  • hangcheck-margin: this defines the maximum hung delay that should be tolerated before hangcheck-timer resets the RAC node.

SRVCTL command (in Oracle 11gR2)

SRVCTL is used to manage the following resources (components):

Component             Abbreviation                 Description
asm                            asm                                               Oracle ASM instance
database                 db                                                  Database instance
diskgroup               dg                                                 Oracle ASM disk group
filesystem               filesystem                                Oracle ASM file system
home                       home                                           Oracle home or Oracle Clusterware home
listener                     lsnr                                             Oracle Net listener
service                     serv                                            Database service
ons, eons                 ons, eons                                 Oracle Notification Services (ONS)

Oracle entities (such as resources, resource types, and server pools) that have names beginning with ora are managed only by SRVCTL (and not by CRSCTL) unless you are directed to do so by Oracle Support. The cluster specific commands are generally managed by CRSCTL.


srvctl  command object options

The available commands used with SRVCTL are:

Command       Description
add                 Adds a component to the Oracle Restart configuration.
config            Displays the Oracle Restart configuration for a component.
disable           Disables management by Oracle Restart for a component.
enable            Reenables management by Oracle Restart for a component.
getenv            Displays environment variables in the Oracle Restart configuration for a database, Oracle ASM instance, or listener.
modify           Modifies the Oracle Restart configuration for a component.
remove           Removes a component from the Oracle Restart configuration.
setenv             Sets environment variables in the Oracle Restart configuration for a database, Oracle ASM instance, or listener.
start                Starts the specified component.
status              Displays the running status of the specified component.
stop                 Stops the specified component.
unsetenv        Unsets environment variables in the Oracle Restart configuration for a database, Oracle ASM instance, or listener.


Here are a matrix of commands/ object combination:




srvctl add
srvctl modify
srvctl remove
The OCR is modified.
srvctl relocate service You can reallocate a service from one named instance to another named instance.
srvctl start
srvctl stopsrvctl status
srvctl disable
srvctl enable
enable = when the server restart the resource must be restarteddisable = when the server restart the resource must NOT be restarted(perhaps we are working for some maintenance tasks)
srvctl config database
Lists configuration information from the OCR (Oracle Cluster Registry).
srvctl getenv
srvctl setenv
srvctl unsetenv
srvctl getenv = displays the environment variables stored in the OCR for target.srvctl setenv    = allows these variables to be setsrvctl unsetenv = allows these variables to be unset


The most  SRVCTL commands are:

srvctl start database -d DBname
srvctl stop database -d DBname

If you don’t know the DBname you might run  select name from v$database;

srvctl start instance -d DBname -i INSTANCEname
srvctl stop instance -d DBname -i INSTANCEname

srvctl start instance -d DBname -i INSTANCEname
srvctl stop instance -d DBname -i INSTANCEname

srvctl status database -d DBname

srvctl status instance -d DBname -i INSTANCEname

srvctl status nodeapps -n NODEname

srvctl enable database -d DBname

srvctl disable database -d DBname

srvctl enable instance -d DBname -i INSTANCEname

srvctl disable instance -d DBname -i INSTANCEname

srvctl config database -d DBname      -> to get some information about the database from OCR.

srvctl getenv nodeaps

The following commands are deprecated in Oracle Clusterware 11g release 2 (11.2):

crsctl check crsd
crsctl check cssd
crsctl check evmd
crsctl debug log
crsctl set css votedisk
crsctl start resources
crsctl stop resources

11gR2 Oracle clusterware log files


With Oracle grid infrastructure 11g release 2 (11.2), Oracle Automatic Storage Management (Oracle ASM) and Oracle Clusterware are installed into a single home directory, which is referred to as the Grid Infrastructure home. Configuration assistants start after the installer interview process that configures Oracle ASM and Oracle Clusterware.

The installation of the combined products is called Oracle grid infrastructure. However, Oracle Clusterware and Oracle Automatic Storage Management remain separate products.


$GRID_HOME  is used in 11.2 Oracle clusterware to specify the grid (clusterware + ASM location). In previous releases we used $CRS_HOME or $ORA_CRS_HOME as environment variable for the clusterware software location (oracle cluster home). For this reason, we can set all these 3 environment variable with the same value (in .profile), but this is not mandatory. In this case we can consider GRID_BASE /oracle/grid if we want.

In 11.2 grid infrastructure, the Oracle Clusterware Component Log Files are all situated in the $GRID_HOME/log/<hostname>
/oracle/grid/11.2/log/test01                ($GRID_HOME = /oracle/grid/11.2)
ls -altr
total 64
drwxrwxr-t   5   oracle   dba         256 Jul 28 20:18 racg
drwxr-x—   2    root     dba         256 Jul 28 20:18 gnsd
drwxrwxr-t  4    root     dba          256 Jul 28 20:18 agent
drwxr-x—   2   oracle   dba         256 Jul 28 20:18 admin
drwxrwxr-x  5   oracle   dba         256 Jul 28 20:18 ..
drwxr-xr-t   17   root     dba       4096 Jul 28 20:18 .
drwxr-x—   2    oracle  dba         256 Jul 28 20:24 gipcd
drwxr-x—   2    oracle  dba         256 Jul 28 20:25 mdnsd
drwxr-x—   2    root     dba         256 Jul 28 20:25 ohasd
drwxr-x—   2    oracle  dba         256 Jul 28 20:27 evmd
drwxr-x—   2    root     dba         256 Jul 31 01:08 ctssd
drwxr-x—   2    root     dba         256 Aug 1 12:44 crsd
drwxr-x—   2    oracle  dba         256 Aug 1 21:15 cssd
drwxr-x—   2    oracle  dba         256 Aug 2 14:06 diskmon
drwxr-x—   2    oracle  dba         256 Aug 2 14:46 gpnpd
-rw-rw-r–   1    root     system 16714 Aug 2 14:46 alert_test01.log
drwxr-x—   2    oracle  dba       4096 Aug 2 14:51 srvm
drwxr-x—   2    oracle  dba       4096 Aug 3 02:59 client


Oracle Clusterware Components/ Daemons/ Processes

Oracle Clusterware Component Log Files

Cluster Ready Services Daemon (CRSD) Log Files


Oracle High Availability Services Daemon (OHASD)


Cluster Synchronization Services (CSS)


Cluster Time Synchronization Service (CTSS)


Grid Plug and Play


Multicast Domain Name Service Daemon (MDNSD)


Oracle Cluster Registry records

client : For the Oracle Cluster Registry tools (OCRDUMP, OCRCHECK, OCRCONFIG) record log information
crsd : The Oracle Cluster Registry server records log information

Oracle Grid Naming Service (GNS)


Event Manager (EVM) information generated by evmd



racgCore files are in subdirectories of the log directory. Each RACG executable has a subdirectory assigned exclusively for that executable. The name of the RACG executable subdirectory is the same as the name of the executable.Additionally, you can find logging information for the VIP and database in these two locations.

Server Manager (SRVM)


Disk Monitor Daemon (diskmon)


Grid Interprocess Communication Daemon (GIPCD)


Oracle Clusterware Components/ Daemons/ Processes

Oracle Clusterware Component Log Files


Where can we find the log files related to the listeners?
A) For listener.log       => $ORACLE_BASE/diag/tnslsnr/test01/listener/trace/listener.log   
As of Oracle Database 11g Release 1, the diagnostics for each database instance are located in a dedicated directory, which can be specified through the DIAGNOSTIC_DEST initialization parameter. The structure of the directory specified by DIAGNOSTIC_DEST is as follows:
<diagnostic_dest>/diag/rdbms/<dbname>/<instname>   This location is known as the Automatic Diagnostic Repository (ADR) Home.
The following files are located under the ADR home directory:Trace files – located in subdirectory <adr-home>/traceAlert logs – located in subdirectory <adr-home>/alert. In addition, the alert.log file is now in XML format, which conforms to the Oracle ARB logging standard.Core files – located in the subdirectory <adr-home>/cdumpIncident files – the occurrence of each serious error (for example, ORA-600, ORA-1578, ORA-7445) causes an incident to be created. Each incident is assigned an ID and dumping for each incident (error stack, call stack, block dumps, and so on) is stored in its own file, separated from process trace files. Incident dump files are located in <adr-home>/incident/<incdir#>. You can find the incident dump file location inside the process trace file.

This parameter can be set on each instance. Oracle recommends that each instance in a cluster specify a DIAGNOSTIC_DEST directory location that is located on shared disk and that the same value for DIAGNOSTIC_DEST be specified for each instance.

If you want to see how the ADR Homes are configurated in the database you can run:
column INST_ID format 999
column NAME format a20
column VALUE format a45
B) for SCAN listeners 
   =>  $GRID_HOME/log/diag/tnslsnr/<NodeName>/listener_scan1/trace/listener_scan1.log
If you want to see the SCAN listeners status you can run :
[oracle@ctxdb1 ~]$ srvctl status scan_listener
SCAN Listener LISTENER_SCAN1 is enabled
SCAN listener LISTENER_SCAN1 is running on node ctxdb2
SCAN Listener LISTENER_SCAN2 is enabled
SCAN listener LISTENER_SCAN2 is running on node ctxdb1
SCAN Listener LISTENER_SCAN3 is enabled
SCAN listener LISTENER_SCAN3 is running on node ctxdb1

Shut down/ stop Oracle clusterware processes

When you run srvctl you will perform an operation for the database or for the cluster. If the operation if related to the database you must use the srvctl command from the ORACLE_HOME, and if the operation is related to the cluster, you must use the srvctl from the GRID_HOME.
1. Ensure that you are logged in as the oracle Linux/ UNIX user.
    If you are not connected as oracle OS user, you must switch to the oracle OS user
    su – oracle
2. Stop/ shut (stop) down all applications using the Oracle database.
    This step includes stopping (shutting down) the Oracle Enterprise Manager Database Control:
$ emctl stop dbconsole
    If you want to check if the Entreprise Manager Database Console is running or not:
emctl status dbconsole 
emctl status agent
Note: In previous releases of Oracle Database, you were required to set environment variables for ORACLE_HOME and ORACLE_SID to start, stop, and check the status
of Enterprise Manager. With Oracle Database 11g release 2 (11.2) and later, you need to set the environment variables ORACLE_HOME and ORACLE_UNQNAME to use or manage the Enterprise Manager.
export ORACLE_UNQNAME=GlobalUniqueName    (database SID and not instance SID)
3. Shut down (stop) all Oracle RAC instances on all nodes.
To shut down all Oracle RAC instances for a database, enter the following command, where db_name is the name of the database:
$ srvctl stop database -d db_name     (this command is starting all the instances)
4. Shut down (stop) all Oracle ASM instances on all nodes. (If you are not using the ASM you must skip this step.)
To shut down an Oracle ASM instance, enter the following command, where node_name is the name of the node where the Oracle ASM instance is running:
$ srvctl stop asm -n node_name
5. Stop (shut down) the Oracle cluster stack
su – root
cd $CRS_HOME/bin
# ./crsctl stop crs              (must be run on each node)
./srvctl stop nodeapps -n node_name  –> in 11.2 stops only ONS and eONS because of some dependencies.
If you want to check if the database is running you can run:
ps -ef | grep smon
oracle 246196 250208 0 14:29:11 pts/0 0:00 grep smon
If you want to check if the database listeners are running you can run:
ps -ef | grep lsnr
root 204886 229874 0 14:30:07 pts/0 0:00 grep lsnr
Here the listeners are running:
ps -ef | grep lsnr
oracle 282660 1 0 14:07:34 – 0:00 /oracle/grid/crs/11.2/bin/tnslsnr LISTENER_SCAN2 -inherit
oracle 299116 250208 0 14:30:00 pts/0 0:00 grep lsnr
oracle 303200 1 0 14:23:44 – 0:00 /oracle/grid/crs/11.2/bin/tnslsnr LISTENER_SCAN1 -inherit
oracle 315432 1 0 14:07:35 – 0:00 /oracle/grid/crs/11.2/bin/tnslsnr LISTENER_SCAN3 -inherit
oracle 323626 1 0 14:07:34 – 0:00 /oracle/grid/crs/11.2/bin/tnslsnr LISTENER -inherit
If you want to check if any clusterware component is running you can run:
[oracle@ctxdb2 ~]$ ps -ef|grep grid
oracle    1707   956  0 13:03 pts/0    00:00:00 grep grid
root      9910     1  0  2013 ?        00:42:59 /oracle/app/cluster/product/11203/grid/bin/ohasd.bin reboot
oracle   10538     1  0  2013 ?        01:04:28 /oracle/app/cluster/product/11203/grid/bin/oraagent.bin
oracle   10552     1  0  2013 ?        00:00:24 /oracle/app/cluster/product/11203/grid/bin/mdnsd.bin
oracle   10564     1  0  2013 ?        00:13:10 /oracle/app/cluster/product/11203/grid/bin/gpnpd.bin
root     10577     1  0  2013 ?        02:43:59 /oracle/app/cluster/product/11203/grid/bin/orarootagent.bin
oracle   10579     1  0  2013 ?        00:41:11 /oracle/app/cluster/product/11203/grid/bin/gipcd.bin
root     10594     1  0  2013 ?        03:09:38 /oracle/app/cluster/product/11203/grid/bin/osysmond.bin
root     10612     1  0  2013 ?        00:01:35 /oracle/app/cluster/product/11203/grid/bin/cssdmonitor
root     10632     1  0  2013 ?        00:01:36 /oracle/app/cluster/product/11203/grid/bin/cssdagent
oracle   10646     1  0  2013 ?        03:42:27 /oracle/app/cluster/product/11203/grid/bin/ocssd.bin
root     10740     1  0  2013 ?        00:05:09 /oracle/app/cluster/product/11203/grid/bin/octssd.bin reboot
oracle   10767     1  0  2013 ?        00:01:22 /oracle/app/cluster/product/11203/grid/bin/evmd.bin
root     11208     1  0  2013 ?        00:27:00 /oracle/app/cluster/product/11203/grid/bin/crsd.bin reboot
oracle   11340 10767  0  2013 ?        00:00:00 /oracle/app/cluster/product/11203/grid/bin/evmlogger.bin -o /oracle/app/cluster/product/11203/grid/evm/log/evmlogger.info -l /oracle/app/cluster/product/11203/grid/evm/log/evmlogger.log
oracle   11378     1  0  2013 ?        02:16:30 /oracle/app/cluster/product/11203/grid/bin/oraagent.bin
root     11382     1  0  2013 ?        03:56:51 /oracle/app/cluster/product/11203/grid/bin/orarootagent.bin
oracle   11474     1  0  2013 ?        00:00:00 /oracle/app/cluster/product/11203/grid/opmn/bin/ons -d
oracle   11475 11474  0  2013 ?        00:00:13 /oracle/app/cluster/product/11203/grid/opmn/bin/ons -d
oracle   11514     1  0  2013 ?        00:20:31 /oracle/app/cluster/product/11203/grid/bin/tnslsnr LISTENER_SCAN1 -inherit
oracle   11531     1  0  2013 ?        00:46:23 /oracle/app/cluster/product/11203/grid/bin/tnslsnr LISTENER -inherit
root     11608     1  0  2013 ?        00:23:33 /oracle/app/cluster/product/11203/grid/bin/ologgerd -m ctxdb1 -r -d /oracle/app/cluster/product/11203/grid/crf/db/ctxdb2
[oracle@ctxdb2 ~]$ 

Hope this will help, Please if you have any queries in your mind, then post it . Experts are always welcome with there views and ideas. As this is not the end so, I will soon be back with more on RAC and RAC related topics.
Thanks & Regards
Nimai Karmakar

Read Full Post »

WHAT IS ORCALE AWR (Automatic Workload Resporitory)REPORT By Nimai Karmakar

Prior Oracle 10g we use STATSPACK report to analyze instance health for performance. but now in 10g we use AWR(automatic workload repository) report to analyze Oracle Database health.

AWR Features
The AWR is used to collect performance statistics including:
 Wait events used to identify performance problems.
 Time model statistics indicating the amount of DB time associated with a process from
 Active Session History (ASH) statistics from the V$ACTIVE_SESSION_HISTORY view.
 Some system and session statistics from the V$SYSSTAT and V$SESSTAT views.
 Object usage statistics.
 Resource intensive SQL statements.

How to generate AWR report ?
It is very easy to generate AWR report in Oracle 10g.We have to run just one sql script to generate AWR report.
There is TWO sql script to create AWR report.
1. awrrpt.sql
If we have only One Oracle Database then run awrrpt.sql sql script.
2. awrrpti.sql
If we have more than One Oracle Instance (Like RAC) then run awrrpti.sql script so that we can particular instance for awr report creation.

Location of AWR report sql script
In my database test

What is default interval period between two awr report ?
AWR report generate automatically for database every 1 hr interval period. But Recommanded is 15 Minutes is enough in two snapshot for better performance

How to Manage & change interval period for awr report ?
There is two to modify or changes in AWR report.

1. Manually —> through DBMS_WORKLOAD_REPOSITORY plsql package
2. Snapshots –> By default snapshots of the relevant data are taken every hour and retained for 7 days. The default values for these settings can be altered using the following procedure.

DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(retention => 43200, — Minutes (= 30 Days).

Current value retained
if NULL.
interval => 30); — Minutes. Current value retained if NULL.

The changes to the settings are reflected in the DBA_HIST_WR_CONTROL view. Typically the retention period should capture at least one complete workload cycle. If you system has monthly archive and loads a 1 month retention time would be more beneficial that the default 7 days. An interval of “0” switches off snapshot collection, which in turn stops much of the self-tuning
functionality, hence this is not recommended. Automatic collection is only possible if the STATISTICS_LEVEL parameter is set to TYPICAL or ALL.

If the value is set to BASIC manual snapshots can be taken, but they will be missing some statistics. Extra snapshots can be taken and existing snapshots can be removed, as shown below.

DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
low_snap_id => 22,
high_snap_id => 32);

Snapshot information can be queried from the DBA_HIST_SNAPSHOT view.

Baselines – A baseline is a pair of snapshots that represents a specific period of usage. Once baselines are defined they can be used to compare current performance against similar periods in the past. You may wish to create baseline to represent a period of batch processing.

start_snap_id => 210,
end_snap_id => 220,
baseline_name => ‘batch baseline’);

The pair of snapshots associated with a baseline are retained until the baseline is explicitly deleted.

baseline_name => ‘batch baseline’,
cascade => FALSE); — Deletes associated snapshots if TRUE.

Baseline information can be queried from the DBA_HIST_BASELINE view.

Workload Repository Views
The following workload repository views are available:
 V$ACTIVE_SESSION_HISTORY – Displays the active session history (ASH) sampled every second.
 V$METRIC – Displays metric information.
 V$METRICNAME – Displays the metrics associated with each metric group.
 V$METRIC_HISTORY – Displays historical metrics.
 V$METRICGROUP – Displays all metrics groups.
 DBA_HIST_ACTIVE_SESS_HISTORY – Displays the history contents of the active session history.
 DBA_HIST_BASELINE – Displays baseline information.
 DBA_HIST_DATABASE_INSTANCE – Displays database environment information.
 DBA_HIST_SNAPSHOT – Displays snapshot information.
 DBA_HIST_SQL_PLAN – Displays SQL execution plans.
 DBA_HIST_WR_CONTROL – Displays AWR settings.

Workload Repository Reports
Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql).They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows.


The scripts prompt you to enter the report format (html or text), the start snapshot id, the end snapshot id and the report filename. The resulting report can be opend in a browser or text editor accordingly.

Enterprise Manager
The automated workload repository administration tasks have been included in Enterprise Manager. The “Automatic Workload Repository” page is accessed from the main page by clicking on the “Administration” link, then the “Workload Repository” link under the “Workload” section.

The page allows you to modify AWR settings or manage snapshots without using the PL/SQL APIs

You can edit snapshot setting here..
CREATE_SNAPSHOT Function and Procedure

This function and procedure create snapshots.In the case of the function, the snapshot ID is returned.



Parameter Description

flush_level The flush level for the snapshot is either ‘TYPICAL’ or ‘ALL’


This example creates a manual snapshot at the TYPICAL level:


If you query the DBA_HIST_SNAPSHOT view after the CREATE_SNAPSHOT action, you will see one more snapshot ID added to the Workload Repository.


This procedure drops a baseline.


baseline_name IN VARCHAR2,
cascade IN BOOLEAN DEFAULT false,

Parameter Description

baseline_name The name of baseline.
cascade If TRUE, the pair of snapshots associated with the baseline will also be dropped.
Otherwise, only the baseline is removed.
dbid The (optional) database id (default to local DBID).

This example drops the baseline ‘oltp_peakload_bl’ without dropping the underlying snapshots:

EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE (baseline_name => ‘oltp_peakload_bl’);

If you query the DBA_HIST_BASELINE view after the DROP_BASELINE action, you will see the specified baseline definition is removed. You can query theDBS_HIST_SNAPSHOT view to find that the underlying snapshots are left intact.


This procedure drops a range of snapshots.

low_snap_id IN NUMBER,
high_snap_id IN NUMBER

Parameter Description

low_snap_id The low snapshot id of snapshots to drop.
high_snap_id The high snapshot id of snapshots to drop.
dbid The database id (default to local DBID.

This example drops the range of snapshots between snapshot id 102 to 105 for the local database:

If you query the dba_hist_snapshot view after the Drop Snapshot action, you will see that snapshots 102 to 105 are removed from the Workload Repository.

How to Interpreting with AWR report ?

Below is Main Section in AWR report for concern.
Load Profile – In this section we know about Logical Read, Physical Read, Soft Parse, Hard Parse Instance Efficiency Percentages
Many Oracle Guru’s is not recommended to trust on Instance percentage ratio. becuase suppose if your instance percentage show BUFFER CAHCE HIT RATIO is 100% it is not sign for your database is very fast and running smootly.

Top 5 Timed Events – It is very important section in AWR report.through this we can know most five wait event is effecting database performance.

Oracle Wait Event – Top wait events which usually cause db hung.

SQL Statistics – It this section we will know about Execution time, cpu used, logical & physical read for top mostsql statement.

Advisory Statistics – In this section we will get advice for PGA, BUFFER CACHE, SHARED POOL, JAVA POOL size for better performance.

For text output


DB Name DB Id Instance Inst Num Release Cluster Host
———- ———– ——— ——– ———– ——- —–
DBDABR 37933856 dbdabr 1 NO Host1
Snap Id Snap Time Sessions Curs/Sess
——— ——————- ——– ———
Begin Snap: 2900 19-Aug-04 11:00:29 18 5.2
End Snap: 2911 19-Aug-04 22:00:16 18 4.6
Elapsed: 659.78 (mins)
DB Time: 10.08 (mins)
Cache Sizes (end)
Buffer Cache: 48M Std Block Size: 8K
Shared Pool Size: 56M Log Buffer: 256K
Load Profile
~~~~ Per Second Per Transaction
——– —————
Redo size: 1,766.20 18,526.31
Logical reads: 39.21 411.30
Block changes: 11.11 116.54
Physical reads: 0.38 3.95
Physical writes: 0.38 3.96
User calls: 0.06 0.64
Parses: 2.04 21.37
Hard parses: 0.14 1.45
Sorts: 1.02 10.72
Logons: 0.02 0.21
Executes: 4.19 43.91
For html output

Hope it helps a little bit.

Thanks & Regards

Nimai Karmakar

Read Full Post »

Older Posts »

Bitbach's Blog

Just another Oracle weblog @ WordPress

Daniel Westermann's Blog

simplicity...that's it

Oracle Application DBA

sharing the information about realtime experience

My Oracle Notes

Emiliano Fusaglia RAC DBA/Data Architect

Johribazaar's Blog

Just another WordPress.com weblog

The Ayurveda

Site for Ayurveda and Yoga articles


Few adventures in my slow running life...


4 out of 5 dentists recommend this WordPress.com site


Technical Articles by Kanchana Selvakumar

SAP Basis Cafe

SAP Basis Cafe - Exploring SAP R/3 Basis World