kill: (27795) - Operation not permitted

If you want to kill on own your session in Linux. For example: If you login as oracle and your session got hung.

[oracle@testbox exports]$ who -u
root tty1 Oct 11 00:24 old 6396
oracle pts/0 Oct 30 15:03 00:09 27795 (10.0.0.61)
root pts/1 Oct 30 11:07 23:57 29527 (10.0.0.37)
oracle pts/2 Oct 31 11:33 . 3476 (10.0.0.61)

kill and killall are not working

[oracle@testbox ~]$ kill -9 27795
-bash: kill: (27795) - Operation not permitted


[oracle@testbox exports]$ killall -9 27795
27795: no process killed

Solution

You need to get right PID to kill session.


[oracle@testbox exports]$ps ax | grep -i oracle

23945 ? Ss 2:00 oracleclpro1 (LOCAL=NO)
27795 ? Ss 0:00 sshd: oracle [priv]
27832 ? S 0:00 sshd: oracle@pts/0
30206 ? Ss 4:29 oraclepriv1 (LOCAL=NO)
32617 ? Ss 0:00 oracletest1 (LOCAL=NO)

[oracle@testbox exports]$ kill -9 27832


You are now all set.

How to setup transparent switchover/failover to application and client

Clients transparently connect to whichever node/instance is currently running the primary database in Data Guard environment.

1. Set the below entry to tnsname.ora on both nodes
== TEST1 is db_name
== show parameter db_name
== 102.168.50.15 is host ip address

ALL_LISTENERS_TEST1 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 102.168.50.15)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 102.168.50.16)(PORT = 1521))
)

2. Set below entry to client’s tnsname.ora file.

TEST1_PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 102.168.50.15)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 102.168.50.16)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = TEST1_PRIMARY)
)
)


3.
(Optional)
If the application is a java program that uses the thin jdbc driver the following connect string can be used instead of the TNS alias.

testdb=any name
test1_primary = service name
102.168.50.16= host ip address
102.168.50.16= host ip address

tetdb.db.url="jdbc:oracle:thin:@(DESCRIPTION=(FAILOVER=TRUE)(ADDRESS_LIST=(ADDRESS=(PROTO=TCP)(HOST=102.168.50.15)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=102.168.50.16)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=test1_primary)))"


4. login as sysdba and execute on below trigger on primary database and it will automatically be shipped to the standby database with the redo log stream.


declare
db_name v$database.name%type;
service_name varchar2(64);
net_name varchar2(64);
begin
select name into db_name from v$database;
service_name := db_name '_PRIMARY';
net_name := db_name '_PRIMARY';
DBMS_SERVICE.CREATE_SERVICE(service_name,net_name);
end;
/

CREATE OR REPLACE TRIGGER enable_primary_service
after startup ON database
declare
db_name v$database.name%type;
db_role v$database.database_role%type;
service_name varchar2(64);
begin
select name,database_role into db_name,db_role from v$database;
if db_role = 'PRIMARY'
then
service_name := db_name '_PRIMARY';
DBMS_SERVICE.START_SERVICE(service_name,NULL);
end if;
END;
/

5. Shutdown and restart primary database to activate the login trigger

6. Check for service at primary database
Sqlplus>Show parameter service

7. Connect to database from client machine

8. Do the switchover and again connect to database from client machine.

9. It should be successfully without making any change in client tnsnames.ora file.

To Auotmate the backup of oracle database init parameter file

Options:

1. Backup file name : dbname_dayofyear_bk_init.ora: e.x. dev10g_234_bk_init.ora

2. Backup folder: /home/oracle/scripts

3. /bin/date +%j : day (in number) of year
/bin/date +%w: day of week (0..6); 0 represents Sunday


#********Begin*****************
#/bin/ksh
#/home/oracle/scripts/backup_initfile.ksh
if [ $# -ne 1 ]
then
echo Usage Error. Please provide DBNames
echo Usage: Ex. ./backup_initfile.ksh dbname
exit
fi

export ORACLE_SID=$1
BACKUP_FOLDER=\'/home/oracle/scripts
BACKUP_FILENAME_tmp=${ORACLE_SID}_`/bin/date +%a /usr/bin/tr A-Z a-z`.trc
BACKUP_FILENAME=${ORACLE_SID}_`/bin/date +%j /usr/bin/tr A-Z a-z`_bk_init.ora\'
#!/bin/ksh
echo ""
echo ""
echo "GETTING INIT FILE"
echo ""
(sqlplus /nolog<connect / as sysdba
create pfile=$BACKUP_FOLDER/$BACKUP_FILENAME from spfile;
EOF
)
#********The End*****************

To execcute in crontab

35 09 * * * /home/oracle/scripts/backup_initfile.ksh db_name

Automatic killing of dead lock sessions

If you are experiencing frequent dead lock problem in your database and want to kill automatically causing sessions. You can accomplished this task with Oracle Resource Manager.

It will kill the sessions those are waiting /hanging more than 20 minutes due to dead lock but you can change minutes as per your requirement. There are 2 groups and this package will be implemented on first group "all_dev_users" and 2^nd group "other_groups" will be not effected this package.

BEGIN

Dbms_Resource_Manager.Clear_Pending_Area();

Dbms_Resource_Manager.Create_Pending_Area();

-- Create plan
--============================
Dbms_Resource_Manager.Create_Plan(
plan => 'kill_blocking_idle_session',
comment => 'Plan to kill blocking idle sessions.');

-- Create consumer groups
--============================
Dbms_Resource_Manager.Create_Consumer_Group(
consumer_group => 'all_dev_users',
comment => 'for all dev10g users');


-- Assign consumer groups to plan and define priorities
--============================
Dbms_Resource_Manager.Create_Plan_Directive (
plan => 'kill_blocking_idle_session',
group_or_subplan => 'all_dev_users',
comment => '20 mins killed blocking idle sessions',
max_idle_blocker_time => 1200);

-Change minutes as per your requirement

Dbms_Resource_Manager.Create_Plan_Directive(
plan => 'kill_blocking_idle_session',
group_or_subplan => 'OTHER_GROUPS',
comment => 'all other users' );
-- Users belong to group 'OTHER_GROUPS' will not be killed.

Dbms_Resource_Manager.Validate_Pending_Area;

Dbms_Resource_Manager.Submit_Pending_Area();

END;
/


BEGIN
-- Assign users to consumer groups

Dbms_Resource_Manager_Privs.Grant_Switch_Consumer_Group(
grantee_name => 'FAMI_API',
consumer_group => 'all_dev_users',
grant_option => FALSE);

Dbms_Resource_Manager.Set_Initial_Consumer_Group('FAMI_API', 'all_dev_users');
END;
/

--To enable the resource plan kill_blocking_idle_session

--==========================================
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = kill_blocking_idle_session scope=both;

--To know how many has been killed since last startup of database

--================================================

select id, NAME, IDLE_BLKR_SESSIONS_KILLED from v$RSRC_CONSUMER_GROUP;

Backup of control file (text format) of your database

Here is script that will create backup of control file (text format) of your database

1. It will identified trace file
2. Edit trace file to make neat and clean script to create control file
3. Copy to identify folder.

How to execute
==============
50 16 * * * /home/oracle/scripts/backup_controlfile.ksh db_name

Output of script
=================
[oracle@dev ~]$ /home/oracle/scripts/backup_controlfile.ksh db_name


GETTING CONTROLFILE TRACE



Copied Trace file to /home/oracle/scripts/dev10g_fri_bk_control.sql
cp /oracle/admin/dev10g/udump/dev10g_ora_25079.trc /home/oracle/scripts/dev10g_fri.trc


Manipulating trace file



New backup Control file: /home/oracle/scripts/dev10g_fri_bk_control.sql


#=============Start of Script=============================
#/home/oracle/scripts/backup_controlfile.kshif [ $# -ne 1 ]then echo Usage Error. Please provide DBNames echo Usage: Ex. ./backup_controlfile.ksh dbname exitfi

export ORACLE_SID=$1BACKUP_FOLDER=/home/oracle/scripts/BACKUP_FILENAME_tmp=${ORACLE_SID}_`/bin/date +%a /usr/bin/tr A-Z a-z`.trcBACKUP_FILENAME=${ORACLE_SID}_`/bin/date +%a /usr/bin/tr A-Z a-z`_bk_control.sql#!/bin/kshecho ""echo ""echo "GETTING CONTROLFILE TRACE"echo ""(sqlplus /nolog< $BACKUP_FOLDER/temptracefile
#echo cp `cat $BACKUP_FOLDER/temptracefile` $BACKUP_FOLDER$BACKUP_FILENAME_tmpcp `cat $BACKUP_FOLDER/temptracefile` $BACKUP_FOLDER$BACKUP_FILENAME_tmpif [[ $? = 0 ]]thenecho ""echo ""echo "Copied Trace file to $BACKUP_FOLDER$BACKUP_FILENAME"echo " cp `cat $BACKUP_FOLDER/temptracefile` $BACKUP_FOLDER$BACKUP_FILENAME_tmp "echo ""echo ""elseecho "FAILED TO GET TRACE FILE"fi
echo "Manipulating trace file"echo ""echo "STARTUP NOMOUNT">$BACKUP_FOLDER$BACKUP_FILENAMEecho ""echo ""cat $BACKUP_FOLDER$BACKUP_FILENAME_tmpsed -e'/^#/d' -e'1,/STARTUP/d' -e'/STARTUP/,//d' -e'/CFILESETSNAPSHOTNAME/d' >> $BACKUP_FOLDER$BACKUP_FILENAMEecho " New backup Control file:" $BACKUP_FOLDER$BACKUP_FILENAME
#===================== end of script==================================

Inspiration

I got inspiration to start my blog from my friend buddy Vish Vangapally.

First entry

This is my first line in my blog.