System activity analysis for Linux users.

# Instructions to install SAR on your Ubuntu box.
Step 1.  Install sysstat
bash> sudo apt-get install sysstat mutt postfix

Step 2. Enable stat collection
bash> sudo vi /etc/default/sysstat
change ENABLED=”false” to ENABLED=”true”
save the file

Step 3. Change the collection interval from every 10 minutes to every 2 minutes.
bash> sudo vi /etc/cron.d/sysstat
Change
5-55/10 * * * * root command -v debian-sa1 > /dev/null && debian-sa1 1 1
To
*/2 * * * * root command -v debian-sa1 > /dev/null && debian-sa1 1 1
save the file

Step 4. Restart sysstat
bash> sudo service sysstat restart

The below step has to run manually at 5:00PM for the next two days.
Step 5. Save the statistics for further analysis to a file and send it via mail to view the same on Ksar
bash> sudo -s
bash> LC_ALL=C sar -A > /tmp/$(date +`hostname`-%d-%m-%y.log) && echo "SAR-`hostname`" | mutt -s "SAR-`hostname`" abc@abc.com -a /tmp/$(date +`hostname`-%d-%m-%y.log)

How to increase Oracle Session

Check the existing processes and sessions limit using the following query.

SQL> select count(*) from v$session;
SQL> select * from v$resource_limit;

To increase run the following query:

(change the number according to your need)

Connect AS SYSDBA;
SQL> ALTER system SET sessions= 200 scope=spfile;

SQL> ALTER system SET processes = 200 scope=spfile;

Restart the Oracle server.

HowTo: Change oracle http port number.

Login as sys as sysdba and execute the following query:
SQL> select dbms_xdb.gethttpport as "HTTP-Port" from dual;
HTTP-Port  
--------------------
     8080
SQL> exec DBMS_XDB.SETHTTPPORT(8090);
Thats all...!!!

HowTO: Oracle the listener supports no services error

The following are the detailed RCA report for the Oracle database issue :

ERROR:
The listener supports no services

ROOT CAUSE:
The database has no idea about the listener because inside the file 'listener.ora', there is no entry of SID_LIST_{listener_name} where {listener_name} need to be replaced by your listener name.

CORRECTIVE ACTION:

Edited the 'listener.ora' file as explained below:

SID_LIST_LISTENER =
 (SID_LIST =
   (SID_DESC =
     (SID_NAME = PLSExtProc)
     (ORACLE_HOME = /home/oracle/app/oracle/product/11.2.0/dbhome_1/)
     (PROGRAM = extproc)
   )
 (SID_DESC=
       (GLOBAL_DBNAME=orcl)
       (ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1/)
       (SID_NAME=orcl)
  )
 )
SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
LISTENER =
 (DESCRIPTION_LIST =
   (DESCRIPTION =
     (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
     (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
   )
 )

And fixed net service names map in 'tnsnames.ora' file
ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
        (SID_NAME = ORCL)
      (SERVER = DEDICATED)
    )
  )

$ lsnrctl status
$ lsnrctl stop
$ lsnrctl start

HowTo: Script to kill all oracle sessions by a user

Normally to Kill a session connected by a user in oracle, you should collect the sid and serial# from the view V$SESSION and execute the following command with sid and serial# filled in it.

ALTER SYSTEM KILL SESSION 'sid,serial#';

We can create a PL/SQL script to kill all the sessions based ton the same, as given below

 begin    
  for x in (select Sid, Serial# from v$session where Username=upper('TypeUserNameHere'))
  loop 
   execute immediate 'Alter System Kill Session '''|| x.Sid || ',' || x.Serial# || ''' IMMEDIATE'; 
  end loop; 
end;
/

If you want to embed it in to a bash (shell) script, it will be like the following script
 
#!/bin/bash
sqlplus -silent /nolog  << EOS
connect / as sysdba
begin    
  for x in (select Sid, Serial# from v$session where Username=upper('TypeUserNameHere'))
  loop 
   execute immediate 'Alter System Kill Session '''|| x.Sid || ',' || x.Serial# || ''' IMMEDIATE'; 
  end loop; 
end;
/
EOS
exit

---

How to connect SQLPlus without adding SID in 'tnsnames.ora'

The following command is used to connect the remote DB without adding the service name(SID) in 'tnsnames.ora'
Example: Input the below cmd in the terminal to connect the remote db.
sqlplus user@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = <Remote-host> )(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = <SID>)))

---

Vasanth KG

Kill Oracle user session from Linux

==== Kill ORACLE user Session from LINUX ===
Identify the Session to be Killed
SQL> select a.username, a.sid, a.serial#, b.spid, a.machine from v$session a, v$process b where a.paddr = b.addr and a.username ='COCERP_12NOV12';
Bash~$ kill -9 SPID
Verify using
Bash~$ ps -ef | grep ora_ | grep -v grep

That's it.