What is Oracle RAC(Real Application Cluster)

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>

ls
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.

Syntax:

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:

Commands

Objects

Comment

srvctl add
srvctl modify
srvctl remove
instancedatabase
service
nodeapps
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
instancedatabase
service
asmnodeapps
srvctl disable
srvctl enable
instancedatabase
service
asm
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
service
asmnodeapps
Lists configuration information from the OCR (Oracle Cluster Registry).
srvctl getenv
srvctl setenv
srvctl unsetenv
instancedatabase
service
nodeapps
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):

crs_stat
crs_register
crs_unregister
crs_start
crs_stop
crs_getperm
crs_profile
crs_relocate
crs_setperm
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.

Notations

$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.

$GRID_HOME=/oracle/grid/11.2
$ORA_CRS_HOME=/oracle/grid/11.2
$CRS_HOME=/oracle/grid/11.2
 
In 11.2 grid infrastructure, the Oracle Clusterware Component Log Files are all situated in the $GRID_HOME/log/<hostname>
 
pwd
/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

crsd

Oracle High Availability Services Daemon (OHASD)

ohasd

Cluster Synchronization Services (CSS)

cssd

Cluster Time Synchronization Service (CTSS)

ctssd

Grid Plug and Play

gpnpd

Multicast Domain Name Service Daemon (MDNSD)

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)

gnsd

Event Manager (EVM) information generated by evmd

evmd

Oracle RAC RACG

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)

srvm

Disk Monitor Daemon (diskmon)

diskmon

Grid Interprocess Communication Daemon (GIPCD)

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>/cdump

Incident 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
select INST_ID, NAME, VALUE from V$DIAG_INFO;
B) for SCAN listeners 
   =>  $GRID_HOME/log/diag/tnslsnr/<NodeName>/listener_scan1/trace/listener_scan1.log
          $GRID_HOME/log/diag/tnslsnr/<NodeName>/listener_scan2/trace/listener_scan2.log
          $GRID_HOME/log/diag/tnslsnr/<NodeName>/listener_scan3/trace/listener_scan3.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

Analyzing AWR (Automatic workload repository) Reports

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
the V$SESS_TIME_MODEL and V$SYS_TIME_MODEL views.
 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
$ORACLE_HOME/rdbms/admin
In my database test
F:\oracle\product\10.2.0\test01\RDBMS\ADMIN

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.

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

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

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.

EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;
BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
low_snap_id => 22,
high_snap_id => 32);
END;
/

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.

BEGIN
DBMS_WORKLOAD_REPOSITORY.create_baseline (
start_snap_id => 210,
end_snap_id => 220,
baseline_name => ‘batch baseline’);
END;
/

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

BEGIN
DBMS_WORKLOAD_REPOSITORY.drop_baseline (
baseline_name => ‘batch baseline’,
cascade => FALSE); — Deletes associated snapshots if TRUE.
END;
/

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.

@$ORACLE_HOME/rdbms/admin/awrrpt.sql
@$ORACLE_HOME/rdbms/admin/awrrpti.sql

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.

Syntax

DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(flush_level IN VARCHAR2 DEFAULT ‘TYPICAL’);
DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(flush_level IN VARCHAR2 DEFAULT ‘TYPICAL’)RETURN NUMBER;

Parameter Description

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

Examples

This example creates a manual snapshot at the TYPICAL level:

EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

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.

DROP_BASELINE Procedure

This procedure drops a baseline.

Syntax

DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(
baseline_name IN VARCHAR2,
cascade IN BOOLEAN DEFAULT false,
dbid IN NUMBER DEFAULT NULL);

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).

Examples
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.

DROP_SNAPSHOT_RANGE Procedure

This procedure drops a range of snapshots.

DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
low_snap_id IN NUMBER,
high_snap_id IN NUMBER
dbid IN NUMBER DEFAULT NULL);

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.

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

EXECUTE DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(102, 105);
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

SELECT output FROM TABLE
(dbms_workload_repository.awr_report_text
(37933856,1,2900,2911)
(Database_id,instance_num,start_snap,end_snap)
);

OUTPUT
——————————————————————–
WORKLOAD REPOSITORY report for
DB Name DB Id Instance Inst Num Release Cluster Host
———- ———– ——— ——– ———– ——- —–
DBDABR 37933856 dbdabr 1 10.1.0.2.0 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
SELECT
output
FROM
TABLE
(dbms_workload_repository.awr_report_html
(37933856,1,2900,2911)
(Database_id,instance_num,start_snap,end_snap)
);

Hope it helps a little bit.

Thanks & Regards

Nimai Karmakar

RMAN incremental backup

I am posting today to the honor of my friend’s  mother or father who had suffer and is suffering from CANCER. May god treat there disease soon and make them healthy and well .

Few days Ago one of my friend ask the What is RMAN incremental backup, when to use it Question. So, today I am talking about that.

Below are some RMAN recovery scenario for real time.

 RMAN incremental backups back up only datafile blocks that have changed since a specified previous backup. You can make incremental backups of databases, individual tablespaces or datafiles.

The most important reason for doing incremental backups is associated with data warehouse environments, where many operations are done in NOLOGGING mode and data changes do not go to the archived log files.
Considering the massive size of data warehouses today, and the fact that most of the data in them does not change, full backups are not acceptable. Therefore , doing incremental backups in RMAN is an ideal alternative. Always follow the backup strategy according to an acceptable MTTR (mean time to recover) in your environment.

Incremental backups can be either level 0 or level 1. A level 0 incremental backup, which is the base for subsequent incremental backups, copies all blocks containing data, backing the datafile up into a backup set just as a full backup would. The only difference between a level 0 incremental backup and a full backup is that a full backup is never included in an incremental strategy.

A level 1 incremental backup can be either of the following types:

  • differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0
  • cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0

Differential Incremental Backups

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

Description of Figure 4-1 follows

In the example shown in Figure 1, the following occurs:

  • Sunday An incremental level 0 backup backs up all blocks that have ever been in use in this database.
  • Monday – SaturdayOn each day from Monday through Saturday, a differential incremental level 1 backup backs up all blocks that have changed since the most recent incremental backup at level 1 or 0. So, the Monday backup copies blocks changed since Sunday level 0 backup, the Tuesday backup copies blocks changed since the Monday level 1 backup, and so forth.
  • The cycle is repeated for the next week.

Cumulative Incremental Backups

RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

Description of Figure 4-2 follows

In the example shown in Figure 2, the following occurs:

  • SundayAn incremental level 0 backup backs up all blocks that have ever been in use in this database.
  • Monday – SaturdayA cumulative incremental level 1 backup copies all blocks changed since the most recent level 0 backup. Because the most recent level 0 backup was created on Sunday, the level 1 backup on each day Monday through Saturday backs up all blocks changed since the Sunday backup.
  • The cycle is repeated for the next week.

RMAN command to create level 0 backup which is needed before running of incremental backup level 1
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE;

RMAN command to run level 1 backup. Level 1 backup will backup all blocks changed since most recent cumulative or differential backup. If a level 0 backup doesn’t exists, when running INCREMENTAL backup Oracle will perform a full backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

RMAN command to run level 1 cumulative backup. Level 1 backup will backup all blocks changed since most recent Level 0 backup. If a level 0 backup doesn’t exists, when running INCREMENTAL backup Oracle will perform a full backup.
RMAN> BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE;

RMAN command to backup database level 1 and skip datafiles and archived redo logs that cannot be read due to I/O errors to be excluded from backup
RMAN> BACKUP INCREMENTAL LEVEL 1 INACCESSIBLE DATABASE;


 

There are couple of ways to determine if RMAN database is registered with a catalog.

Using RMAN when you connect to RMAN catalog and try to run a RMAN command like “list backup” it will generate an error as shown below
$ rman target / catalog rmancataloguser/rmancatalogpassword@catalogdb

Recovery Manager: Release 10.2.0.4.0 – Production on Sun Jul 17 08:33:42 2011

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

connected to target database: TEST01 (DBID=1023910334)
connected to recovery catalog database

RMAN> list backup;

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of list command at 07/17/2011 08:33:48
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog

Another way would be to connect to the catalog schema through SQL*Plus and check view RC_DATABASE
$ sqlplus rmancataloguser/rmancatalogpassword@catalogdb
SQL*Plus: Release 10.2.0.4.0 – Production on Sun Jul 17 08:45:58 2011

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 32bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from rc_database where name = ‘TEST01’;

no rows selected


 

Remember that only SPFILE or control file can be restored from autobackup

Scenario when all control files for some reason have been lost then how to restore.

1) Try to shutdown database which will fail as the control file(s) doesn’t exists so we needs to perform shutdown abort
SQL> shutdown immediate;
ORA-00210: cannot open the specified control file
ORA-00202: control file: ‘/apps/oracle/oradata/TEST01/control1.ora’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

SQL> shutdown abort;
ORACLE instance shut down.

2) Start database in nomount to restore the controlfile as controlfile is missing so database can only be started in mount
SQL> startup nomount;
ORACLE instance started.

Total System Global Area 209715200 bytes
Fixed Size 2019672 bytes
Variable Size 109055656 bytes
Database Buffers 96468992 bytes
Redo Buffers 2170880 bytes

3) Start rman and connect to target database and set a past DBID
$ORACLE_HOME/bin/rman

Recovery Manager: Release 10.2.0.1.0 – Production on Tue Dec 7 21:08:53 2010

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

RMAN> connect target /

connected to target database: TEST (not mounted)

RMAN> set dbid 1992878807

executing command: SET DBID

4) Restore controlfile from autobackup
RMAN> restore controlfile from autobackup;

Starting restore at 16-NOV-13
using channel ORA_DISK_1

..
Finished restore at 16-NOV-13

5) Open database in mount
RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

Later if you got RMAN-06189

RMAN-06189 current DBID number does not match target mounted database (number)
Cause SET DBID was used to set a DBID that does not match the DBID of the database to which RMAN is connected.
Action If the current operation is a restore to copy the database, do not mount the database. Otherwise, avoid using the SET DBID command, or restart RMAN.

 

6) Recover database
RMAN> recover database;

Starting recover at 16-NOV-13

….

..

media recovery complete, elapsed time: 00:00:02
Finished recover at 16-NOV-13

 7) Database has to be open with resetlogs

Why open resetlogs, or what is the use of open resetlogs after a incomplete recovery?

because its

1. Archives the current online redo logs.

2. Clears the contents of the online redo logs, and resets the online redo logs to log sequence 1
RMAN> alter database open resetlogs;

database opened

Restore spfile through RMAN, here are some scenerios/examples.

1. In this scenerio there is a autobackup is present of spfile, database is in no mount state so to use AUTOBACKUP, DBID needs to be set before restoring spfile.
$ $ORACLE_HOME/bin/rman

Recovery Manager: Release 10.2.0.1.0 – Production on Sun Nov 28 14:40:05 2010

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

RMAN> connect target /

connected to target database: TEST01 (not mounted)

RMAN> set dbid 1992878807

executing command: SET DBID

RMAN> restore spfile from autobackup;

Starting restore at 16-NOV-13
using target database control file instead of recovery catalog

..

channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 16-NOV-13

2. In this scenerio there spfile is restored from autobackup, database is in nomount state and if the filename of backup piece is known so it can be passed to restore spfile
$ $ORACLE_HOME/bin/rman

Recovery Manager: Release 10.2.0.1.0 – Production on Mon Nov 29 17:45:04 2010

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

RMAN> connect target /

connected to target database: TEST01 (not mounted)

RMAN> restore spfile from ‘/apps/oracle/product/10.2.0/db_1/dbs/backup_piece_name';
– RMAN> restore spfile from ‘backup_piece_name';

Starting restore at 16-NOV-13
using channel ORA_DISK_1

….

Finished restore at 28-NOV-10

 

Scenerio where there is no autobackup of control file so Oracle goes to last 7 days by default and does not find it

If you want to tell RMAN to look for spfile more than 7 days so using maxdays RMAN will look for spfile from current date to currentday – maxdays

“RMAN> restore spfile from autobackup maxdays 200;”
$ $ORACLE_HOME/bin/rman

Recovery Manager: Release 10.2.0.1.0 – Production on Sun Nov 28 14:40:05 2010

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

RMAN> connect target /

connected to target database: TEST01 (not mounted)

RMAN> set dbid 1992878807

executing command: SET DBID

RMAN> restore spfile from autobackup;

Starting restore at 16-NOV-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=35 devtype=DISK

channel ORA_DISK_1: looking for autobackup on day: 20131115
channel ORA_DISK_1: looking for autobackup on day: 20131114
channel ORA_DISK_1: looking for autobackup on day: 20131113
channel ORA_DISK_1: looking for autobackup on day: 20131112
channel ORA_DISK_1: looking for autobackup on day: 20131111
channel ORA_DISK_1: looking for autobackup on day: 20131110
channel ORA_DISK_1: looking for autobackup on day: 20131109
channel ORA_DISK_1: no autobackup in 7 days found
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/16/2013 15:08:30
RMAN-06172: no autobackup found or specified handle is not a valid copy or piece

 

When AUTOBACKUP is turned on Oracle will create backup of controlfile and spfile. When one runs backup of the database or archive log, changes in tablespace occur like creating a new tablespace, add a new datafile and dropping tablespace Oracle create a separate backup piece for the control file and spfile.

Shows how to turn on CONTROLFILE AUTOBACKUP
RMAN> connect target /

connected to target database: TEST (DBID=1992878807)

RMAN> configure CONTROLFILE AUTOBACKUP ON;

old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP OFF;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

Below is the code to turn on controlfile autobackup through PL/SQL and setting the format.
VARIABLE RECNO NUMBER;
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG(‘CONTROLFILE AUTOBACKUP’,’ON’);
EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG(‘CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE’,’DISK TO ”T_%U”’);

Steps if you need to restore all datafiles in a tablespace is corrupted so you would need to restore the table space. The following example shows the steps

  1. Tablespace cannot be made offline as it will try to flush all blocks in the datafiles but as the datafile is corrupted.

SQL> alter tablespace ts_something offline;
alter tablespace newton offline
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: ‘/apps/oracle/oradata/TEST01/newton01.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

  •  So using table space offline immediate Oracle will not check if the file exists and does not perform checkpoint and we need to do media recovery on the table space

SQL> alter tablespace newton offline immediate;

Tablespace altered.

  •  Display datafile status

SQL> select d.name, d.status
from v$datafile d, v$tablespace t
where t.name = ‘NEWTON’
and t.ts# = d.ts#;

NAME STATUS
—————————————————————— ——-
/apps/oracle/oradata/TEST01/newton01.dbf RECOVER

  •  restore the table space

RMAN> run {
2> restore tablespace NEWTON;
3> }

Starting restore at 15-NOV-13
using channel ORA_DISK_1

….

..
channel ORA_DISK_1: restore complete, elapsed time: 00:00:15
Finished restore at 15-NOV-13

  • table space has be restored but not recovered so shows it cannot be made online till table space is recovered

SQL> alter tablespace ts_something online;
alter tablespace ts_something online
*
ERROR at line 1:
ORA-01113: file 4 needs media recovery
ORA-01110: data file 4: ‘/apps/oracle/oradata/TEST01/newton01.dbf’

  •  recover table space

SQL> recover tablespace ts_something;
Media recovery complete.

  •  Tablespace can be made online

SQL> alter tablespace ts_something online;

Tablespace altered.

  • Shows data file status

SQL> select d.name, d.status
from v$datafile d, v$tablespace t
where t.name = ‘NEWTON’
and t.ts# = d.ts#;

NAME STATUS
————————————————————————————————————————————
/apps/oracle/oradata/TEST01/newton01.dbf ONLINE


 

Using the steps below one take cold backup using RMAN. As it’s a cold backup the database as the database is in mount stage and the database doesn’t have to be archivelog mode .

Step 1) Shutdown database

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 2) Start database in mount stage

SQL> startup mount;
ORACLE instance started.

Total System Global Area 167772160 bytes
Fixed Size 2019320 bytes
Variable Size 75497480 bytes
Database Buffers 88080384 bytes
Redo Buffers 2174976 bytes

Step 3) Run rman and connect to target database and run rman to backup database and connection to catalog if you are using one
$ $ORACLE_HOME/bin/rman target /

Recovery Manager: Release 10.2.0.1.0 – Production on Fri Apr 23 02:33:38 2010

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

connected to target database: TEST01 (DBID=1992878807, not open)

RMAN> backup database;

 

To find RMAN catalog version log-in to the catalog through SQL*Plus and query the table rcver will print version.

SQL> select * from rcver;

VERSION
————
10.02.00.00

SQL> select object_type from user_objects where object_name = ‘RCVER’;

OBJECT_TYPE
——————-
TABLE

SQL> desc rcver;
Name Null? Type
—————————————– ——– —————————-
VERSION NOT NULL VARCHAR2(12)

Hope it’s help. If you guys do have any recover or restore scenario you can post you comments about that.

Thanks & Regards

Nimai Karmakar

RMAN Block Media recovery in oracle

Some issues which I have faced at past are some of my blocks got corrupted on the pre-prod, I can’t  give you the exact scenario but I will try to make that scenario again.

So, if some of our data block got corrupted, How we can restore them?

Answer is simple Oracle Block Media Recovery.

When some of the data blocks in the data files got physically corrupted and if you don’t have RMAN backups then you will have to restore the full data file from backup to restore and recover those few blocks (like expdp/impdp, cold, hot backup), which can be quite a hectic job. But if you use rman backups then you can take benefit of this very powerful feature called BMR (BLOCK MEDIA RECOVERY). Using Block Media Recovery only those blocks which got corrupted can be recovered from a backup instead of recovering whole file.

The steps to resolve block corruption are simple…..

  • Start SQL*Plus and connect to the target database.
  • Query V$DATABASE_BLOCK_CORRUPTION to determine whether corrupt blocks exist. For example, execute the following statement:
SQL> SELECT * FROM V$DATABASE_BLOCK_CORRUPTION;
  • (if exist) Start RMAN and connect to the target database.
  • Recover all blocks marked corrupt in V$DATABASE_BLOCK_CORRUPTION.The following command repairs all physically corrupted blocks recorded in the view:
  • RMAN> RECOVER CORRUPTION LIST;

Lets create the scenario.

For this we need to have a test tablespace with a different data-file where we can corrupt our data block. So lets create the tablespace.

Login to sqlplus.

$ sqlplus / as sysdba

create tablespace test datafile ‘/u01/apps/oradata/test/test01.dbf’ size 1G autoextend on next 10M

extent management dictionary segment space management auto

/

Tablespace created.

create user nimai identified by password default tablespace test

quota unlimited on test

/

User created.

grant connect , resource,dba to nimai;

Grant succeeded.

conn nimai/password;

Connected.

create table testnimai as select * from all_objects;

Table created.

SQL> exit

Ok, now we have a tablespace named as test, which has a table named testnimai owned by the user nimai.

Lets take a backup of this tablespace using RMAN.

$ rman target /

RMAN>run

{

allocate channel no1 type disk;

sql ‘alter system switch log';

backup datafile 5 format /u02/rman_backup/backup/nimai_%d_%s_%p_%t';

release channel no1;

}

Starting backup at 14-NOV-13

using channel no1

channel no1: starting full datafile backupset

channel no1: specifying datafile(s) in backupset

input datafile fno=00005 name=/u01/apps/oradata/test/test01.dbf

channel no1: starting piece 1 at 14-NOV-13

channel no1: finished piece 1 at 14-NOV-13

piece handle=/u02/rman_backup/backup/nimai_5vhcr4vz_.bkp

comment=NONE

channel no1: backup set complete, elapsed time: 00:00:01

Finished backup at 14-NOV-13

Check that your backup piece exists

RMAN> list backup;

RMAN> exit

Recovery Manager complete.

Now we have a backup of data file “/u01/apps/oradata/test/test01.dbf”.

let check out the status of the table.

$ sqlplus / as sysdba

select segment_name,header_file,header_block from dba_segments where segment_name = ‘TESTNIMAI’

and owner = ‘NIMAI';

SEGMENT_NAME                 HEADER_FILE HEADER_BLOCK

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

TESTNIMAI                              5           10

SQL> exit

The header of the table is in block 11, so if we are able to corrupt the next block we can create a scenario for test. Lets corrupt the next block which is 11 using the “dd” command in Linux (Note: Please use the dd command carefully in you environment as its can remove some important blocks from your environment).

$ cd /u01

$ dd of=/apps/oradata/test/test01.dbf bs=512 count=1 seek=11 << EOF

Ok, now we have executed the command and the block 11 got corrupted as well in the data file “/apps/oradata/test/test01.dbf”.

$ sqlplus / as sysdba

SQL> alter system flush buffer_cache;

System altered.

We need to flush the buffer_cache because if the block 11 is in the buffer_cache then we can’t read that block from the data file.

We will get the block corruption error

  1.  when we will query the v$database_block_corruption.
  2.  in the alert log file.
  3. when we use dbverify (dbv) utility for that file.
  4. when we will try to query the table.

lets check here simply as users are.

SQL> conn nimai/password

Connected.

SQL> select count(*) from testnimai;

select count(*) from testnimai

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 11)

ORA-01110: data file 5: ‘/u01/apps/oradata/test/test01.dbf’

SQL> exit

As our scenario is created and now we can proceed further to recover the block 11 of data file 5.

$ rman target /

RMAN> BLOCKRECOVER DATAFILE 5 BLOCK 11;

Starting blockrecover at 14-NOV-13

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=154 devtype=DISK

channel ORA_DISK_1: restoring block(s)

channel ORA_DISK_1: specifying block(s) to restore from backup set

restoring blocks of datafile 00005

channel ORA_DISK_1: restored block(s) from backup piece 1

piece handle=/u02/rman_backup/backup/nimai_5vhcr4vz_.bkp

channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01

starting media recovery

media recovery complete, elapsed time: 00:00:01

Finished blockrecover at 14-NOV-13

RMAN> exit

Recovery Manager complete.

BLOCK MEDIA RECOVERY Complete.

$ sqlplus nimai/password

SQL> select count(*) from testnimai;

COUNT(*)

———-

40688

SQL> exit

Lets now corrupt more then one blocks in the file and do a test for Block Media Recovery.

$ cd /u01

$ dd of=/apps/oradata/test/test01.dbf bs=512 count=1 seek=11 << EOF

$ dd of=/apps/oradata/test/test01.dbf bs=512 count=1 seek=12 << EOF

$ dd of=/apps/oradata/test/test01.dbf bs=512 count=1 seek=13 << EOF

$ sqlplus nimai/password

SQL> select count(*) from testnimai;

COUNT(*)

———-

40688

So,why are we not getting the error, remember as I told you earlier we need to flush the buffer_cache because if the block is in the buffer_cache then we can’t read that block from the data file.So, here we have queried the blocks from buffer_cache.

SQL> conn / as sysdba

Connected.

SQL> alter system flush buffer_cache;

System altered.

Now query again.

SQL> conn nimai/password

Connected.

SQL> select count(*) from testnimai;

select count(*) from testnimai

*

ERROR at line 1:

ORA-01578: ORACLE data block corrupted (file # 5, block # 11)

ORA-01110: data file 5: ‘/u01/apps/oradata/test/test01.dbf’

SQL> exit

Now say that you have three blocks corrupt in your data file, your automated backup script started in the night somewhere and took a backup of the file marking the blocks as corrupt.  When RMAN finds corrupt blocks in the data file it reports them in v$backup_corruption.

$ rman target /

after getting the corrupted block count from v$backup_corruption we can set in the backup script to not backup those 3 block  (as we have corrupted three blocks e.g. 11,12,13)

We will use set maxcorrupt command in RMAN which will ignore 3 corrupted blocks in the file 5 and mark the blocks as corrupt and mark those 3 blocks in v$backup_corruption.

RMAN> run {

allocate channel no1 type disk;

set maxcorrupt for datafile 5 to 3;

backup datafile 5;

release channel no1;

}

executing command: SET MAX CORRUPT

using target database control file instead of recovery catalog

Starting backup at 14-NOV-13

allocated channel: no1

channel no1: sid=158 devtype=DISK

channel no1: starting full datafile backupset

channel no1: specifying datafile(s) in backupset

input datafile fno=00005

name=/u01/apps/oradata/test/test01.dbf

channel no1: starting piece 1 at 14-NOV-13

channel no1: finished piece 1 at 14-NOV-13

piece handle=

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03

Finished backup at 14-NOV-13

RMAN> exit

Recovery Manager complete.

The backup is complete now lets query the v$backup_corruption to see our corrupted blocks count.

$ sqlplus / as sysdba

select piece#, file#, block# , blocks , marked_corrupt from v$backup_corruption;

PIECE#      FILE#     BLOCK#     BLOCKS MAR

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

1          5         11          3 YES

SQL> exit

It tells us there are three blocks corrupted in data file 5. Now we can simply go to RMAN use the BLOCK RECOVER command to recover all these blocks from the backup we took earlier.

$ rman target /

RMAN> list backup;

RMAN> BLOCKRECOVER CORRUPTION LIST from tag=tag_name;

CORRUPTION LIST means all the blocks reported corrupt in v$backup_corruption.

OR

RMAN> RECOVER CORRUPTION LIST;

RMAN> exit

Recovery Manager complete.

Now lets query again and check that do we have our corrupted data blocks with us in ok condition.

$ sqlplus nimai/password

SQL> select count(*) from testnimai;

COUNT(*)

———-

40688

SQL> exit

All blocks are recovered successfully. Always off and on do take rman backup cause you never know when you can face such type of scenario in your prod environment.

Hope it’s help.

Thanks & Regards

Nimai Karmakar

$

Linux shell scripts for Oracle DBA

Guys as discussed at my last post oracle-performance-tuning-queries I have promised that I will be back with some DBA monitoring shell scripts , So there they are. Below shell scripts can be used for daily Database monitoring by DBA, Some are created by my fellow DBA .

Please edit the configuration as per your environment

  • This scripts checks for tablespace usage. If tablespace is 10 percent free,  it will send an alert e-mail.

#####################################################################
## check_tablespace.sh ##
##
#####################################################################
###!/bin/bash
##
####### Start of configuration
##
######## Oracle Enviorment variables ##########
##
export ORACLE_BASE=/u01/app
export ORACLE_HOME=/rac/app/oracle/product/11.2.0
export ORACLE_UNQNAME=TEST01
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/rdbms/admin:$ORACLE_HOME/lib
export alrt=/rac/app/oracle/diag/rdbms/test01/TEST01/trace/alert_TEST01.log
export asmalrt=/rac/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log
export TNS_ADMIN=$ORACLE_HOME/network/admin
export MONITOR_DIR=$ORACLE_HOME/dba-scripts/monitor

export ORACLE_SID=TEST01
##
######## Other variables #################
##
DBA=nimai.karmakar@hotmail.com
DATABASE=TEST01
datevar=$(date)
datevar2=$(date ‘+%Y-%m-%d-%H-%M’)
##
####### End of configuration
##
sqlplus -s “/ as sysdba” << SQL1
set feed off
set linesize 100
set pagesize 200
spool /rac/app/oracle/product/11.2.0/dba-scripts/monitor/tablespace.alert
SELECT F.TABLESPACE_NAME,
TO_CHAR ((T.TOTAL_SPACE – F.FREE_SPACE),’999,999′) “USED(MB)”,
TO_CHAR (F.FREE_SPACE, ‘999,999’) “FREE(MB)”,
TO_CHAR (T.TOTAL_SPACE, ‘999,999’) “TOTAL(MB)”,
TO_CHAR ((ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)),’999,99999′)||’ %’ PERCENT_FREE
FROM   (
SELECT       TABLESPACE_NAME,
ROUND (SUM (BLOCKS*(SELECT VALUE/1024
FROM V\$PARAMETER
WHERE NAME = ‘db_block_size’)/1024)
) FREE_SPACE
FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME NOT IN (‘SYSTEM’,’SYSAUX’,’TEMP’,’USERS’,’UNDOTBS1′,’UNDOTBS2′)
GROUP BY TABLESPACE_NAME
) F,
(
SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES/1048576)) TOTAL_SPACE
FROM DBA_DATA_FILES WHERE TABLESPACE_NAME NOT IN (‘SYSTEM’,’SYSAUX’,’TEMP’,’USERS’,’UNDOTBS1′,’UNDOTBS2′)
GROUP BY TABLESPACE_NAME
) T
WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME
AND (ROUND ((F.FREE_SPACE/T.TOTAL_SPACE)*100)) < 10;
spool off
exit
SQL1
if [ `cat /rac/app/oracle/product/11.2.0/dba-scripts/monitor/tablespace.alert|wc -l` -gt 0 ]
then
echo Tablespace less than 10% free on ${DATABASE}.Please add space as necassary >> /rac/app/oracle/product/11.2.0/dba-scripts/monitor/tablespace.tmp
cat /rac/app/oracle/product/11.2.0/dba-scripts/monitor/tablespace.alert >> /rac/app/oracle/product/11.2.0/dba-scripts/monitor/tablespace.tmp
mailx -s “Tablespace percent usage for ${DATABASE} at $datevar” $DBA < /rac/app/oracle/product/11.2.0/dba-scripts/monitor/tablespace.tmp
#####mv /disk1/tablespace.tmp /disk1/tablespace_$datevar2.alert
rm /rac/app/oracle/product/11.2.0/dba-scripts/monitor/tablespace.alert
rm /rac/app/oracle/product/11.2.0/dba-scripts/monitor/tablespace.tmp
fi

  • Script to gather SGA stats in timely manner

####################################################
##
##SGA.sh
##
####################################################
export ORACLE_HOME=/rac/app/oracle/product/11.2.0
export ORACLE_SID=TEST01
export ORACLE_UNQNAME=TEST01
export PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/rdbms/admin:$ORACLE_HOME/lib
export alrt=/rac/app/oracle/diag/rdbms/test01/TEST01/trace/alert_test011.log
export asmalrt=/rac/app/oracle/diag/asm/+asm/+ASM1/trace/alert_+ASM1.log
export TNS_ADMIN=$ORACLE_HOME/network/admin
MAILID=nimai.karmakar@hotmail.com
#
#
#
sqlplus -s “/ as sysdba” << SQL1
set feed off
set linesize 100
set pagesize 200
spool /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/sga_stats.log
col inst_id format 9999999 heading “INSTANCE ID”
col sga_size/1024 format 9999999 heading “SGA SIZE”
col sga_size_factor format 9999999 heading “SGA SIZE FACTOR”
col estd_physical_reads format 9999999 heading “PHYSICAL READ”
select inst_id, sga_size/1024, sga_size_factor, estd_db_time, estd_db_time_factor, estd_physical_reads from gv\$sga_target_advice
order by inst_id, sga_size_factor;
spool off
exit;
SQL1

if [ `cat /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/sga_stats.log|wc -l` -gt 0 ]; then
cat /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/sga_stats.log
mailx -s “Statistics at `date +%H+%M` for `hostname`” $MAILID << /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/sga_stats.log
fi
exit

  • Script to check Server Process (RAC)

#!/bin/bash
export PATH=$PATH:/grid/app/bin/

SUBJECT=”Server Process failed for – Server `hostname` on `date ‘+%m/%d/%y %X %A ‘`”
REPSUBJECT=”Server `hostname` health check report at `date +%H:%M` hours on `date +%d-%m-%Y`”
ERRLOG=$MONITOR_DIR/logs/server_process_check.log
REPORT=$MONITOR_DIR/logs/server_process_report.txt
BODY=$MONITOR_DIR/server_process_report_email_body.txt

## Delete the errorlog file if found
/usr/bin/find  $ERRLOG -type f -exec rm {} \; 2>&1 > /dev/null

##Report recipients
MAILID=’nimai.karmakar@hotmail.com’
chour=`date +%H“date +%M`

if [ `grep -i "TEST01  HEALTH CHECK FOR" $REPORT | wc -l` -eq 0 ]; then
echo “—————————-TEST01 HEALTH CHECK FOR `date +%m_%d_%Y`———————————” > $REPORT
fi

echo ” ” >> $REPORT

echo “—————————-CRS process status `date +%m_%d_%Y_%H:%M`—————————-” >> $REPORT
crsctl check crs | while read outputline
do
if test `echo $outputline | grep ‘online’ | wc -l` -eq 0 ## This will check if the CRS process is online or not
then
echo “Date        : “`date ‘+%m/%d/%y %X %A ‘` >> $ERRLOG
echo “Details     :”`crsctl check  crs` >> $ERRLOG
echo ”  ” >> $ERRLOG
echo ” Details     :”`crsctl check  crs` >> $REPORT
echo ” ” >> $REPORT
echo ” Skiiping other tests ” >> $REPORT
echo ”  ” >> $ERRLOG
##mutt -s “$SUBJECT” $MAILTO < $ERRLOG
/bin/mail -s “$SUBJECT” “$MAILID”
exit
else
echo ” Details     :”`crsctl check  crs` >> $REPORT
echo ” ” >> $REPORT
fi
done

echo “—————————-PMON process Status count on `date +%m_%d_%Y_%H:%M`—————————-” >> $REPORT
if test `ps -ef|grep pmon|grep -v grep |wc -l` -ne 2 ## This will check the no of pmon process for each of the server
then
echo “Date        : “`date ‘+%m/%d/%y %X %A ‘` >> $ERRLOG
echo “Details     :”`ps -ef|grep pmon|grep -v ‘grep’ |wc -l` >> $ERRLOG
echo ” ” >> $ERRLOG
echo ” PMON process not found. Oracle Instance on `hostname` may be down . Require immediate attention” >> $ERRLOG
echo ” ” >> $REPORT
echo ” Skiiping other tests ” >> $REPORT
echo ”  ” >> $ERRLOG
/bin/mail -s “$SUBJECT” “$MAILID”
exit
else
echo ” Details     :”`crsctl check  crs` >> $REPORT
echo ” ” >> $REPORT
fi

echo “—————————-Listener Status on `date +%m_%d_%Y_%H:%M`—————————-” >> $REPORT

##Check whether listener is running. Output should be 1
if test `ps -ef|grep tnslsnr | grep -v “grep” |wc -l` -ne 1 ##Check the no of listener running.
then
echo “Date        : “`date ‘+%m/%d/%y %X %A ‘` >> $ERRLOG
echo “Details     :”`ps -ef|grep tnslsnr |grep -v ‘grep’ |wc -l` >> $ERRLOG
echo ” ” >> $ERRLOG
echo ” Listener on `hostname` may be down . Require immediate attention” >> $ERRLOG
echo ” ” >> $REPORT
echo ”  ” >> $ERRLOG
/bin/mail -s “$SUBJECT” “$MAILID”
else
echo ” Details     :”`crsctl check  crs` >> $REPORT
echo ” ” >> $REPORT
fi

echo “—————————-Checking number of oracle processes `date +%m_%d_%Y_%H:%M`—————————-” >> $REPORT

##Check Process count of “oracle” user. Output should be less than or equal to 1500
if test `ps -ef|grep -i oracle |wc -l` -ge 1000
then
echo “Date        : “`date ‘+%m/%d/%y %X %A ‘` >> $ERRLOG
echo “Details     : “`ps -ef|grep -i oracle|wc -l` >> $ERRLOG
echo ”  “
echo ” Count of processes exceeded 1000. Require immediate attention” >>  $ERRLOG
echo ”  ” >> $ERRLOG
echo ”  ” >> $ERRLOG
/bin/mail -s “$SUBJECT” “$MAILID”
else
echo “Number of oracle processes: ” `ps -ef|grep -i oracle |wc -l` >> $REPORT
echo ” ” >> $REPORT
fi

##Send the report at particular times (e.g 1500 hours or 2300 hours)
if [ $chour -ge 1500 -a $chour -lt 1502 ]; then
mutt  -s “$REPSUBJECT” -a $REPORT $MAILID < $BODY
if [ $? -eq 0 ]; then
cp $REPORT $MONITOR_DIR/logs/server_process_report_`date +%d-%m-%Y`.txt
> $REPORT
fi
fi

if [ $chour -ge 2350 -a $chour -lt 2355 ]; then
mutt -s “$REPSUBJECT” -a $REPORT $MAILID < $BODY
if [ $? -eq 0 ]; then
cp $REPORT $MONITOR_DIR/logs/server_process_report_`date +%d-%m-%Y`.txt
> $REPORT
fi
fi

exit

  • Script for purging old files

echo ‘Setting your environment’

###ORACLE_SID=#replace with your SID
ORACLE_SID=TEST01
export ORACLE_SID
###
###BDUMP=#replace with your BDUMP path
BDUMP=/rac/app/oracle/diag/rdbms/test01/TEST01/trace
export BDUMP
###
###ADUMP=#replace with your ADUMP path
ADUMP=/rac/app/oracle/admin/TEST01/adump
export ADUMP
###
###UDUMP=#replace with your UDUMP path
UDUMP=/rac/app/oracle/diag/rdbms/test01/TEST01/trace
export UDUMP

DT=`date “+%d%m%y”`
export DT
PID=${$}
export PID
FSEQ=${PID}
export FSEQ

################## Creating Backup Dir if not exist #############################
echo ‘Creating Backup Dir if not exist’
mkdir -p $BDUMP/bdump_oldfiles
mkdir -p $UDUMP/udump_oldfiles
mkdir -p $ADUMP/adump_oldfiles

#### Deleting old Alert log files and trace files################################
echo ‘Deleting old Alert log files and trace files’
cd $BDUMP/bdump_oldfiles
find . -name “*.trc.gz”  -mtime +5 -exec rm {} \ ;
find . -name “*.log.gz”  -mtime +5 -exec rm {} \ ;

cd $BDUMP
ls -lrt | grep “.trc” | awk ‘{print “mv  “$9 ” $BDUMP/bdump_oldfiles “}’ > /tmp/mv$FSEQ.sh
sh /tmp/mv$FSEQ.sh
rm /tmp/mv$FSEQ.sh

#### Backup and Purging of Alert logfile #######################################
echo ‘Backup and Purging of Alert logfile’
cd $BDUMP
cp alert_$ORACLE_SID.log $BDUMP/bdump_oldfiles/alert_$ORACLE_SID.log

cd $BDUMP
>  alert_$ORACLE_SID.log

#### Compression of old Alert log files ########################################
gzip -f $BDUMP/bdump_oldfiles/*.log

#### Deleting old user trace files #############################################
cd $UDUMP/udump_oldfiles
find /$UDUMP/udump_oldfiles -name “*.trc.gz”  -mtime +5 -exec rm {} \ ;

cd $UDUMP
ls -lrt | grep “.trc” | awk ‘{print “mv  “$9 ” $UDUMP/udump_oldfiles “}’ > /tmp/mv$FSEQ.sh
sh /tmp/mv$FSEQ.sh
rm /tmp/mv$FSEQ.sh

cd $UDUMP/udump_oldfiles
ls -lrt | grep “.trc” | grep -v “.gz” | awk ‘{print “gzip -f  ” $9 }’ > /tmp/gzip$FSEQ.sh
sh  /tmp/gzip$FSEQ.sh
rm  /tmp/gzip$FSEQ.sh

#### Deleting old audit files ##################################################
cd $ADUMP/oldfiles
find /$ADUMP/oldfiles -name “*.aud.gz” -mtime +5 -type f -exec rm {} \

cd $ADUMP
ls -lrt | grep “.aud” | awk ‘{print “mv  “$9 ” $ADUMP/oldfiles “}’ > /tmp/mv$FSEQ.sh
sh /tmp/mv$FSEQ.sh
rm /tmp/mv$FSEQ.sh

cd $ADUMP/oldfiles
ls -lrt | grep “.aud” | grep -v “.gz” | awk ‘{print “gzip -f  ” $9 }’ > /tmp/gzip$FSEQ.sh
sh  /tmp/gzip$FSEQ.sh
rm  /tmp/gzip$FSEQ.sh

############################# END ######################################

  • This scripts checks for database recovery area usage and send an  alert e-mail.

#####################################################################
## db_recovery_area_usage.sh ##
##
## Author : Nimai Karmakar
#####################################################################
###!/bin/bash
##
#######
####### Start of configuration
#######
######## Oracle Enviorment variables ##########
##
export ORACLE_BASE=/u01/app
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/test01
export JAVA_HOME=/usr/java/jdk1.6.0_30/
export PATH=${ORACLE_HOME}/bin:${JAVA_HOME}/bin:$PATH:.:
export ORACLE_SID=TEST01
##
######## Other variables #################
##
DBA=nimai.karmakar@hotmail.com
DATABASE=TEST01
datevar=$(date)
datevar2=$(date ‘+%Y-%m-%d-%H-%M’)
##
#######
####### End of configuration
#######
##
sqlplus -s “/ as sysdba” << SQL1
set feed off
set linesize 100
set pagesize 200
spool /disk1/scripts/archive.alert
set lines 100
col Location format a60
select     name “Location”
,  floor(space_limit / 1024 / 1024/1024) “Size GB”
,  ceil(space_used  / 1024 / 1024/1024) “Used GB”
from       v\$recovery_file_dest
order by name
/
spool off
exit
SQL1
if [ `cat /disk1/scripts/archive.alert|awk '{print $3}'|wc -l` -gt 0 ]
then
cat /disk1/scripts/archive.alert > /disk1/scripts/archive.tmp
mail -s “DB Recovery area usage for ${DATABASE} at $datevar” $DBA < /disk1/scripts/archive.tmp
rm /disk1/scripts/archive.tmp
rm /disk1/scripts/archive.alert
fi

  • This scripts checks for any ORA errors at alert log file and if found any ORA error send an  alert e-mail.

#####################################################################
## check_alert_log.sh ##
##
## Author : Nimai Karmakar
#####################################################################
###!/bin/bash
##
#######
####### Start of configuration
#######
######## Oracle Enviorment variables ##########
##
export ORACLE_BASE=/u01/app
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/test01
export JAVA_HOME=/usr/java/jdk1.6.0_30/
export PATH=${ORACLE_HOME}/bin:${JAVA_HOME}/bin:$PATH:.:
export ORACLE_SID=TEST01
##
######## Other variables #################
##
DBA=nimai.karmakar@hotmail.com
datevar=$(date)
##
#######
#######
#######
####### End of configuration
#######
#######
##
cd $ORACLE_BASE/diag/rdbms/test01/$ORACLE_SID/trace/
if [ -f alert_${ORACLE_SID}.log ]
then
tail -200 alert_${ORACLE_SID}.log > /disk1/scripts/alert_work.log
grep ORA- /disk1/scripts/alert_work.log >> /disk1/scripts/alert.err
grep Shut /disk1/scripts/alert_work.log >> /disk1/scripts/alert.err
fi
export error << `cat /disk1/scripts/alert.err`

if[ `cat /disk1/scripts/alert.err|wc -l` -gt 0 ]
then
mailx -s “${ORACLE_SID} ORACLE ALERT ERRORS $datevar” $DBA < /disk1/scripts/alert_work.log
fi
fi
rm -f /disk1/scripts/alert.err
rm -f /disk1/scripts/alert_work.log

exit

  • SCRIPT FOR CHECKING MOUNT POINT SPACE IN UNIX

######################################
#!/bin/ksh
#rm /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/ERROR_LOG.txt
echo “df -k output for `date` `uname -n`” > /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/ERROR_LOG.txt
echo ” ” >> /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/ERROR_LOG.txt
echo “File system usage exceeded the threshold on `uname -n` server- `date`” >> /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/ERROR_LOG.txt
echo ” ” >> /tmp/dfk.txt
i=1
while [ $i -le `df -k | grep -v proc | grep -v capacity | wc -l` ] ;do
if [ `df -k | grep -v proc | grep -v capacity | head -n $i | tail -1 | awk '{print $5}' | \sed -e 's/%//'` -gt 90 ] ; then
echo “File system usage exceeded the threshold on `uname -n` server- `date`” >> /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/ERROR_LOG.txt
echo ” ” >> /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/ERROR_LOG.txt
df -k | grep -v proc | grep -v capacity | head -n $i | tail -1 >> /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/ERROR_LOG.txt
fi
((i=i+1))

done
if [ `cat /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/ERROR_LOG.txt | wc -l` -gt 2 ] ; then
#cat /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/ERROR_LOG.txt | mailx -s “File system full alert” nimai.karmakar@hotmail.com
cat /rac/app/oracle/product/11.2.0/dba-scripts/monitor/logs/ERROR_LOG.txt
else
exit
fi

  • Script to check the node Eviction (RAC)

#!/bin/bash
. ~/.bash_profile

mailid=’nimai.karmakar@hotmail.com’
date=`date  +%Y-%m-%d” “%H:`

alertlog=/rac/app/oracle/product/11.2.0/dba-scripts/logs/ioerr.log
errlog=err_`date –date=”0 days ago” +_%d_%m_%y_%H_%M`.txt

err1=`grep -B 2 “$date” $alertlog | grep -A 2 “An I/O error” | wc -l`
err2=`grep -B 2 “$date” $alertlog | grep -A 2 “Network communication with node” | wc -l`

if [ $err1 -ge 1 -o $err2 -ge 1 ]
then
echo “Node eviction condition found in server `hostname`. Immediately check DB alert log for further action”  >> $errlog
echo “” >> $errlog
echo `grep -B 2 “$date” $alertlog | grep -A 2 “An I/O error”` >> $errlog
echo “” >> $errlog
echo =`grep -B 2 “$date” $alertlog | grep -A 2 “Network communication with node”` >> $errlog
mutt -s “Node evition type condition found in `hostname`” $mailto < $errLog
rm $errlog
fi

Thats it, Hope it’s helpful.

Thanks & Regards

Nimai Karmakar

Oracle Performance tuning Queries

Friends as I have told in my last post ” oracle-monitoring-scripts ” that soon I will be back with some performance related queries. So, it’s been a little late but here they are. 

  • High water mark status for a given table

SET SERVEROUTPUT ON
SET VERIFY OFF

DECLARE
CURSOR cu_tables IS
SELECT a.owner,
a.table_name
FROM all_tables a
WHERE a.table_name = Decode(Upper(‘&&1′),’ALL’,a.table_name,Upper(‘&&1′))
and a.owner = Upper(‘&&2′);

op1 NUMBER;
op2 NUMBER;
op3 NUMBER;
op4 NUMBER;
op5 NUMBER;
op6 NUMBER;
op7 NUMBER;
BEGIN

Dbms_Output.Disable;
Dbms_Output.Enable(1000000);
Dbms_Output.Put_Line(‘TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK’);
Dbms_Output.Put_Line(‘—————————— ————— ————— —————‘);
FOR cur_rec IN cu_tables LOOP
Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,’TABLE’,op1,op2,op3,op4,op5,op6,op7);
Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,’ ‘) ||
LPad(op3,15,’ ‘) ||
LPad(op1,15,’ ‘) ||
LPad(Trunc(op1-op3-1),15,’ ‘));
END LOOP;

END;
/

SET VERIFY ON;

  • To see the server metrics on OS level (Best for RAC)

SELECT ‘Time now is ‘ METRIC_NAME,
To_char(sysdate, ‘dd/mm/yyyy hh24:mi:ss’) VALUE
FROM DUAL
UNION
SELECT to_char(inst_id) || ‘_’ || METRIC_NAME METRIC_NAME,
to_char(VALUE) value
FROM gV$SYSMETRIC
WHERE METRIC_NAME IN (‘Database CPU Time Ratio’,’Database Wait Time Ratio’)
AND INTSIZE_CSEC = (SELECT MAX(INTSIZE_CSEC) FROM gV$SYSMETRIC where inst_id=1)
and inst_id=1
UNION
SELECT to_char(inst_id) || ‘_’ || METRIC_NAME METRIC_NAME,
to_char(VALUE) value
FROM gV$SYSMETRIC
WHERE METRIC_NAME IN (‘Database CPU Time Ratio’,’Database Wait Time Ratio’)
AND INTSIZE_CSEC = (SELECT MAX(INTSIZE_CSEC) FROM gV$SYSMETRIC where inst_id=2)
and inst_id=2 ;

  • To see the long running sqls

SELECT s.SQL_TEXT,
vw.inst_id,
vw.username,
vw.osuser
–vw.machine ,
–vw.terminal ,vw.sid,vw.serial# , vw.spid,
–‘alter system kill session’ ||””||vw.sid||’,’||vw.serial#||”';’ dbsession,
— ‘!kill -9 ‘||vw.spid osprocess
FROM
(
SELECT s.inst_id,
s.sid,
s.serial#,
s.username,
sl.sql_hash_value,
sl.sql_address,
s.program,
s.osuser,
s.machine,
s.terminal,
p.spid,
ROUND(sl.elapsed_seconds/60) || ‘:’ || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ‘:’ || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM gv$session s,
gv$session_longops sl ,
gv$process p
WHERE s.sid = sl.sid
AND s.inst_id = sl.inst_id
AND s.serial# = sl.serial#
AND ROUND(sl.elapsed_seconds/60) > 1 AND ROUND(sl.sofar/sl.totalwork*100, 2) < 100
and p.addr = s.paddr
and s.inst_id = p.inst_id
)VW LEFT OUTER JOIN gv$sql s ON
s.hash_value = VW.sql_hash_value
AND s.address = VW.sql_address
and vw.inst_id = s.inst_id
–and trim(vw.program) = ‘SQL Developer’
AND s.child_number = 0

  • Database metrics for everything

select 1 INST_ID,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ‘SQL Service Response Time (secs)’
WHEN ‘Response Time Per Txn’ then ‘Response Time Per Txn (secs)’
ELSE METRIC_NAME
END METRIC_NAME,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ROUND((MINVAL / 100),2)
WHEN ‘Response Time Per Txn’ then ROUND((MINVAL / 100),2)
ELSE MINVAL
END MININUM,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ROUND((MAXVAL / 100),2)
WHEN ‘Response Time Per Txn’ then ROUND((MAXVAL / 100),2)
ELSE MAXVAL
END MAXIMUM,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ROUND((AVERAGE / 100),2)
WHEN ‘Response Time Per Txn’ then ROUND((AVERAGE / 100),2)
ELSE AVERAGE
END AVERAGE
FROM GV$SYSMETRIC_SUMMARY
where METRIC_NAME in (‘CPU Usage Per Sec’,
‘CPU Usage Per Txn’,
‘Database CPU Time Ratio’,
‘Database Wait Time Ratio’,
‘Executions Per Sec’,
‘Executions Per Txn’,
‘Response Time Per Txn’,
‘SQL Service Response Time’,
‘User Transaction Per Sec’)
AND INTSIZE_CSEC = (SELECT MAX(INTSIZE_CSEC) FROM GV$SYSMETRIC_SUMMARY WHERE INST_ID=1)
AND INST_ID=1
UNION
select 2 INST_ID,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ‘SQL Service Response Time (secs)’
WHEN ‘Response Time Per Txn’ then ‘Response Time Per Txn (secs)’
ELSE METRIC_NAME
END METRIC_NAME,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ROUND((MINVAL / 100),2)
WHEN ‘Response Time Per Txn’ then ROUND((MINVAL / 100),2)
ELSE MINVAL
END MININUM,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ROUND((MAXVAL / 100),2)
WHEN ‘Response Time Per Txn’ then ROUND((MAXVAL / 100),2)
ELSE MAXVAL
END MAXIMUM,
CASE METRIC_NAME
WHEN ‘SQL Service Response Time’ then ROUND((AVERAGE / 100),2)
WHEN ‘Response Time Per Txn’ then ROUND((AVERAGE / 100),2)
ELSE AVERAGE
END AVERAGE
FROM GV$SYSMETRIC_SUMMARY
where METRIC_NAME in (‘CPU Usage Per Sec’,
‘CPU Usage Per Txn’,
‘Database CPU Time Ratio’,
‘Database Wait Time Ratio’,
‘Executions Per Sec’,
‘Executions Per Txn’,
‘Response Time Per Txn’,
‘SQL Service Response Time’,
‘User Transaction Per Sec’)
AND INTSIZE_CSEC = (SELECT MAX(INTSIZE_CSEC) FROM GV$SYSMETRIC_SUMMARY WHERE INST_ID=2)
AND INST_ID=2
ORDER BY 1

  • Displays segment advice for the specified segment.

SET SERVEROUTPUT ON SIZE 1000000
SET LINESIZE 200
SET VERIFY OFF

DECLARE
l_object_id NUMBER;
l_task_name VARCHAR2(32767) := ‘SEGMENT_ADVISOR';
l_object_type VARCHAR2(32767) := UPPER(‘&object_type’);
l_attr1 VARCHAR2(32767) := UPPER(‘&object_owner’);
l_attr2 VARCHAR2(32767) := UPPER(‘&object_name’);
BEGIN
IF l_attr2 = ‘NULL’ THEN
l_attr2 := NULL;
END IF;

DBMS_ADVISOR.create_task (
advisor_name => ‘Segment Advisor’,
task_name => l_task_name);

DBMS_ADVISOR.create_object (
task_name => l_task_name,
object_type => l_object_type,
attr1 => l_attr1,
attr2 => l_attr2,
attr3 => NULL,
attr4 => ‘null’,
attr5 => NULL,
object_id => l_object_id);

DBMS_ADVISOR.set_task_parameter (
task_name => l_task_name,
parameter => ‘RECOMMEND_ALL’,
value => ‘TRUE’);

DBMS_ADVISOR.execute_task(task_name => l_task_name);

FOR cur_rec IN (SELECT f.impact,
o.type,
o.attr1,
o.attr2,
f.message,
f.more_info
FROM dba_advisor_findings f
JOIN dba_advisor_objects o ON f.object_id = o.object_id AND f.task_name = o.task_name
WHERE f.task_name = l_task_name
ORDER BY f.impact DESC)
LOOP
DBMS_OUTPUT.put_line(‘..’);
DBMS_OUTPUT.put_line(‘Type : ‘ || cur_rec.type);
DBMS_OUTPUT.put_line(‘Attr1 : ‘ || cur_rec.attr1);
DBMS_OUTPUT.put_line(‘Attr2 : ‘ || cur_rec.attr2);
DBMS_OUTPUT.put_line(‘Message : ‘ || cur_rec.message);
DBMS_OUTPUT.put_line(‘More info : ‘ || cur_rec.more_info);
END LOOP;

DBMS_ADVISOR.delete_task(task_name => l_task_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(‘Error : ‘ || DBMS_UTILITY.format_error_backtrace);
DBMS_ADVISOR.delete_task(task_name => l_task_name);
END;
/

  • Getting current PGA status

SELECT 1 instnum,
pga_aggr /(1024 * 1024 * 1024),
tot_pag_alloc /(1024 * 1024 * 1024),
(tot_pag_used)/ (1024* 1024* 1024)
FROM
(select value tot_pag_used
from gv$pgastat where name = ‘total PGA inuse’ and inst_id = 1),
(select value tot_pag_alloc
from gv$pgastat where name = ‘total PGA allocated’ and inst_id = 1 ),
(select value pga_aggr from gv$parameter where UPPER(NAME)=’PGA_AGGREGATE_TARGET’ and inst_id = 1)
UNION
SELECT 2 instnum,
pga_aggr /(1024 * 1024 * 1024),
tot_pag_alloc /(1024 * 1024 * 1024),
(tot_pag_used)/ (1024* 1024* 1024)
FROM
(select value tot_pag_used
from gv$pgastat where name = ‘total PGA inuse’ and inst_id = 2),
(select value tot_pag_alloc
from gv$pgastat where name = ‘total PGA allocated’ and inst_id = 2 ),
(select value pga_aggr from gv$parameter where UPPER(NAME)=’PGA_AGGREGATE_TARGET’ and inst_id = 2);

  • Getting current SGA info

select inst_id,  ROUND(SUM(BYTES)/1024/1024/1024,2) from gv$sgainfo WHERE RESIZEABLE=’Yes’ group by inst_id;

  • To see sessions per node

select count(sid) Total_session,
count(case status when ‘ACTIVE’ THEN 1 ELSE NULL END) Active_session,
count(case status when ‘INACTIVE’ THEN 1 ELSE NULL END ) Inactive_Session,
inst_id
from gv$session
WHERE USERNAME IN ( ‘SEFPLLIVE’) –> Use this clause for particular schemas
— AND LOGON_TIME >= SYSDATE – INTERVAL ‘5’ MINUTE –> use this clause for more current information
group by inst_id order by 4;

  • Dictionary hit ratio

SELECT inst_id,
(1 – (Sum(getmisses)/(Sum(gets) + Sum(getmisses)))) * 100 Dict_Hit_Ratio
FROM gv$rowcache
group by inst_id;

  • db_buffer cache hit ratio

SELECT 1 inst_id,
(1 – (phys.value / (db.value + cons.value))) * 100 Buff_Cache_Hit_Ratio
FROM gv$sysstat phys,
gv$sysstat db,
gv$sysstat cons
WHERE phys.name = ‘physical reads’
AND db.name = ‘db block gets’
AND cons.name = ‘consistent gets’
and phys.inst_id = 1
and db.inst_id = 1
and cons.inst_id = 1
UNION
SELECT 2 inst_id,
(1 – (phys.value / (db.value + cons.value))) * 100 Buff_Cache_Hit_Ratio
FROM gv$sysstat phys,
gv$sysstat db,
gv$sysstat cons
WHERE phys.name = ‘physical reads’
AND db.name = ‘db block gets’
AND cons.name = ‘consistent gets’
and phys.inst_id = 1
and db.inst_id = 1
and cons.inst_id = 1

/

  • Latch hit ratio

SELECT inst_id,
(1 – (Sum(misses) / Sum(gets))) * 100 Latch_Hit_Ratio
FROM gv$latch
group by inst_id;

  • Displays a list of SQL statements that are using the most resources.

SELECT *
FROM (SELECT Substr(a.sql_text,1,100) sql_text,
NVL(p.username, ‘(PROD)’) AS username,
p.osuser,
p.sid,
p.serial#,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a,
v$session p
ORDER BY 2 DESC)
WHERE rownum <= 5;

  • DB CACHE ADVICE

select inst_id,
SIZE_FOR_ESTIMATE,
BUFFERS_FOR_ESTIMATE,
ESTD_PHYSICAL_READ_FACTOR,
ESTD_PHYSICAL_READS
FROM GV$db_cache_advice
WHERE NAME = ‘DEFAULT’
AND BLOCK_SIZE = (SELECT value from V$PARAMETER WHERE name = ‘db_block_size’)
AND ADVICE_STATUS = ‘ON

  • SGA TARGET ADVICE

select inst_id, sga_size, sga_size_factor, estd_db_time, estd_db_time_factor, estd_physical_reads from gv$sga_target_advice
order by inst_id, sga_size_factor;

  • TABLESPACE Fragmentation details

SELECT
TABLESPACE_NAME,COUNT(*) FREE_CHUNKS,decode( round((max(BYTES) / 1024000),2),
null,0, round((max(BYTES) / 1024000),2)) largest_chunk,
nvl(round(sqrt(max(BLOCKS)/sum(BLOCKS))*(100/sqrt(sqrt(count(BLOCKS)) )),2), 0) FRAG_INDEX
FROM
SYS.DBA_FREE_SPACE
GROUP BY
TABLESPACE_NAME
ORDER BY 2 DESC, 1;

Thats it. These scripts are few which I use to monitor for one of my project which was on RAC (Real Application Clusters).

You can also follow Tim Hall blog for more.

Soon I will be back with some shell scripts which You can you for monitoring.

Thanks & Regards

Nimai Karmakar

Oracle Monitoring Scripts

Hello friends

Some of my friends always ask for such scripts through which they can monitor the Instance,Database and all that related to oracle.
So, after a long a research i have came with some handy scripts which you can find very handy to help you in real situations.

Note: Please test the scripts before applying it to the Production database server.and you must have privilege as a sysdba.

Some Monitoring Scripts are as below…….

1. Select Poor Performing sql queries….

SELECT sql_text, executions,
ROUND(disk_reads / executions, 2) reads_per_run,
disk_reads, buffer_gets,
ROUND((buffer_gets-disk_reads)/
buffer_gets, 2) hit_ratio,
SQL_TEXT
FROM v$sqlarea
WHERE executions > 0
AND buffer_gets > 0
AND (buffer_gets-disk_reads) / buffer_gets < 0.80
ORDER by 3 desc ;

2. Find sql query being executed by the os.

First find the Process Id of the The OS via “top” command in linux.
Process ID (PID)
prompt “Enter The Linux Process ID”
set long 999999
set pagesize 50000
set linesize 30000
set long 500000
set head off
select
s.username su,
substr(sa.sql_text,1,1000) txt
from v$process p,
v$session s,
v$sqlarea sa
where p.addr=s.paddr
and s.username is not null
and s.sql_address=sa.address(+)
and s.sql_hash_value=sa.hash_value(+)
and spid=&SPID;
3. To Find out the blocked,waiting sessions

col high_event format a40
col wait_event format a40
select s.sid blocker,
s.event high_event,
w.event wait_event,
w.sid blocked
from v$session s, v$session w
where w.blocking_session = s.sid
and w.blocking_session_status=’VALID';

4. Watch out session

select sid,serail# from v$session;

(After getting the sid and serial# for example– 42,67)

Long operations

select sid,username Username,serial#,opname,target,sofar,totalwork total_work,units,
time_remaining remaning_time,elapsed_seconds elapsed_time,last_update_time last_time_updated
from v$session_longops where sid=42
order by last_update_time desc;

All active sessions
select * from v$session where status=’ACTIVE’
and sid in (42,67)
order by sid;

Find session’s sid or process id by it’s sid or process id

select sid, a.serial#, spid, a.username,
status, taddr, a.program
from v$session a, v$process b
where a.paddr=b.addr and a.username is not null
and (sid=42 or spid=28179)
order by status, sid;

5. Check the size of db

Prompt “Total Size Of Database Including all”

SELECT a.data_size + b.temp_size + c.redo_size + d.controlfile_size
“total_size in GB”
FROM (SELECT SUM (bytes) / 1024 / 1048756 data_file_size FROM dba_data_files) a,
(SELECT NVL (SUM (bytes), 0) / 1024 / 1048756 temp_file_size
FROM dba_temp_files) b,
(SELECT SUM (bytes) / 1024 / 1048756 redo_log_size FROM sys.v_$log) c,
(SELECT SUM (BLOCK_SIZE * FILE_SIZE_BLKS) / 1024 / 1048756
controlfile_size
FROM v$controlfile) d;

6. Sga (System Global Area) free memory

select name,
sgasize/1024/1024 “Allocated (MEG)”,
bytes/1024 “Free (KB)”,
round(bytes/sgasize*100, 2) “% Space Free”
from (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f
where f.name = ‘free memory';

7. Archive Generation Monitoring 

Archive log Hourly Generation Monitoring

set pages 2000
select trunc(COMPLETION_TIME,’HH’) Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by trunc(COMPLETION_TIME,’HH’),thread# order by 1 ;

Archive log Weekly Generation Monitoring

SELECT to_date(first_time) DATE,

to_char(sum(decode(to_char(first_time,’HH24′),’00’,1,0)),’99’) “00”,
to_char(sum(decode(to_char(first_time,’HH24′),’01’,1,0)),’99’) “01”,
to_char(sum(decode(to_char(first_time,’HH24′),’02’,1,0)),’99’) “02”,
to_char(sum(decode(to_char(first_time,’HH24′),’03’,1,0)),’99’) “03”,
to_char(sum(decode(to_char(first_time,’HH24′),’04’,1,0)),’99’) “04”,
to_char(sum(decode(to_char(first_time,’HH24′),’05’,1,0)),’99’) “05”,
to_char(sum(decode(to_char(first_time,’HH24′),’06’,1,0)),’99’) “06”,
to_char(sum(decode(to_char(first_time,’HH24′),’07’,1,0)),’99’) “07”,
to_char(sum(decode(to_char(first_time,’HH24′),’08’,1,0)),’99’) “08”,
to_char(sum(decode(to_char(first_time,’HH24′),’09’,1,0)),’99’) “09”,
to_char(sum(decode(to_char(first_time,’HH24′),’10’,1,0)),’99’) “10”,
to_char(sum(decode(to_char(first_time,’HH24′),’11’,1,0)),’99’) “11”,
to_char(sum(decode(to_char(first_time,’HH24′),’12’,1,0)),’99’) “12”,
to_char(sum(decode(to_char(first_time,’HH24′),’13’,1,0)),’99’) “13”,
to_char(sum(decode(to_char(first_time,’HH24′),’14’,1,0)),’99’) “14”,
to_char(sum(decode(to_char(first_time,’HH24′),’15’,1,0)),’99’) “15”,
to_char(sum(decode(to_char(first_time,’HH24′),’16’,1,0)),’99’) “16”,
to_char(sum(decode(to_char(first_time,’HH24′),’17’,1,0)),’99’) “17”,
to_char(sum(decode(to_char(first_time,’HH24′),’18’,1,0)),’99’) “18”,
to_char(sum(decode(to_char(first_time,’HH24′),’19’,1,0)),’99’) “19”,
to_char(sum(decode(to_char(first_time,’HH24′),’20’,1,0)),’99’) “20”,
to_char(sum(decode(to_char(first_time,’HH24′),’21’,1,0)),’99’) “21”,
to_char(sum(decode(to_char(first_time,’HH24′),’22’,1,0)),’99’) “22”,
to_char(sum(decode(to_char(first_time,’HH24′),’23’,1,0)),’99’) “23”
from
v$log_history
where to_date(first_time) > sysdate -8
GROUP by
to_char(first_time,’YYYY-MON-DD’), to_date(first_time)
order by to_date(first_time)
/

Hope it is useful, in a short time I will come with some “Oracle Performance Monitoring Scripts”,
Thanks & Regards
Nimai Karmakar