Script to find top consumers of PGA memory

set pagesize 1000
set lines 100
col sid format 9999
col username format a12
col module format a30
column pga_memory_mb format 9,999.99 heading "PGA MB"
column max_pga_memory_mb format 9,999.99 heading "PGA MAX|MB"
col service name format a20 
col sql_text format a70 heading "Currently executing SQL"
set echo on 

WITH pga AS 
    (SELECT sid,
            ROUND(SUM(CASE name WHEN 'session pga memory' 
                       THEN VALUE / 1048576 END),2) pga_memory_mb,
            ROUND(SUM(CASE name WHEN 'session pga memory max' 
                      THEN VALUE / 1048576  END),2) max_pga_memory_mb
      FROM v$sesstat  
      JOIN v$statname  USING (statistic#)
     WHERE name IN ('session pga memory','session pga memory max' )
     GROUP BY sid)
SELECT sid, username,s.module, 
       max_pga_memory_mb, substr(sql_text,1,70) sql_text
  FROM v$session s
  JOIN (SELECT sid, pga_memory_mb, max_pga_memory_mb,
               RANK() OVER (ORDER BY pga_memory_mb DESC) pga_ranking
         FROM pga)
  USING (sid)
  LEFT OUTER JOIN v$sql sql 
    ON  (s.sql_id=sql.sql_id and s.sql_child_number=sql.child_number)
 WHERE pga_ranking <=5
 ORDER BY  pga_ranking

Oracle Database Startup Script

Let's create oracle start up script which will help you to start and stop database through the script. This script can help you when you have to integrate Oracle with Linux cluster.

Create a file like and provide belo script code into it. 

# description: Oracle auto start-stop script.
# Set ORACLE_HOME to be equivalent to the $ORACLE_HOME
# from which you wish to execute dbstart and dbshut;
# Set ORACLE_OWNER to the user id of the owner of the 
# Oracle database in ORACLE_HOME.


if [ ! -f $ORACLE_HOME/bin/dbstart ]
    echo "Oracle startup: cannot start"

case "$1" in
        # Start the Oracle databases:
        # The following command assumes that the oracle login 
        # will not prompt the user for any values
        su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbstart $ORA_HOME"
        touch /var/lock/subsys/dbora
        # Stop the Oracle databases:
        # The following command assumes that the oracle login 
        # will not prompt the user for any values
        su - $ORACLE_OWNER -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
        rm -f /var/lock/subsys/dbora

echo "Usage: $0 start|stop|restart|reload"
        exit 1
exit 0

Following commands will be used for DB start-up

#sh start  - To start database
#sh stop - To staop Database

NFS mount for Oracle Datapump

Many times we required NFS mount point on database server to do multiple activities and we face issue while mounting the NFS mount point with default settings. To overcome from this issue you can below command with parameter settings to mount the NFS mount point on DB server.

#mount -o hard,rw,noac,rsize=32768,wsize=32768,suid,proto=tcp,vers=3 -F nfs nfs_server:/file_system /dir_name


nfs_server - Hostname or IP address of NFS server
file_system - Directory or mount point or file system of NFS server which is going to mount on DB server.
dir_name - Directory name on DB server on which NFS file system would be placed.

Bonding Configuration in Linux

1. Create ifcfg-bond0 file as given below.

#vi /etc/sysconfig/network-scripts/ifcfg-bond0


2. Append below lines in ifcfg-eth0 and ifcfg-eth1

#vi /etc/sysconfig/network-scripts/ifcfg-eth0


############# Bond Configuration Parameter #########


#vi /etc/sysconfig/network-scripts/ifcfg-eth1