BUFFER SORT and Parallel query

One of our developer complained that he executed a query that failed because because of "ORA-1652: unable to extend temp segment by 128 in tablespace TEMP".
He also explained that there was no "sorting" or "group by" operation in that query.
Then I looked at the execution plan and found that there are some buffer sort in the execution plan which consumed space in temp.

After some googling I found that it the parallel query who in responsible for this.
http://www.orafaq.com/forum/t/125684/0/

If joining two tables, one has parallel degree more than 1 and other has parallel 1 or noparallel
then buffer sort will take place.€
To check this I performed below tests:

Buffer Sort:

alter table bs1 parallel 4;
alter table bs2 parallel 1;

explain plan for
select * from bs1 a,bs2 b
where a.id=b.id;

select
substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,50 ) "Operation",
object_name "Object"
from
plan_table
start with id = 0
connect by prior id=parent_id;

Operation Object
----------------------------------------------------------
SELECT STATEMENT ()
PX COORDINATOR ()
PX SEND (QC (RANDOM)) :TQ10002
HASH JOIN ()
PX RECEIVE ()
PX SEND (HASH) :TQ10001
PX BLOCK (ITERATOR)
TABLE ACCESS (STORAGE FULL) BS1
BUFFER (SORT)
PX RECEIVE ()
PX SEND (HASH) :TQ10000
TABLE ACCESS (STORAGE FULL) BS2


No Buffer Sort:

alter table bs1 parallel 4;
alter table bs2 parallel 2;

explain plan for
select * from bs1 a,bs2 b
where a.id=b.id;

select
substr (lpad(' ', level-1) || operation || ' (' || options || ')',1,50 ) "Operation",
object_name "Object"
from
plan_table
start with id = 0
connect by prior id=parent_id;

Operation Object
--------------------------------------------------------
SELECT STATEMENT ()
PX COORDINATOR ()
PX SEND (QC (RANDOM)) :TQ10002
HASH JOIN (BUFFERED)
PX RECEIVE ()
PX SEND (HASH) :TQ10000
PX BLOCK (ITERATOR)
TABLE ACCESS (STORAGE FULL) BS1
PX RECEIVE ()
PX SEND (HASH) :TQ10001
PX BLOCK (ITERATOR)
TABLE ACCESS (STORAGE FULL) BS2

Block Corrouption

If database can be started in open mode then do below keeping DB open.
If DB is in noarchive mode or DB cannot be opened or situation is like, DB can be opened but after some time DB goes in recovery mode(may be frequent ORA-600[17182] & ORA-600[17114] in alert log) and stopped then starup in mound mode and follow steps below:€

1. Set $ORACLE_SID and $ORACLE_HOME

2. $rman target / nocatalog

3. from rman prompt validate all datafiles(user "check logical" as rman normally checks only physical corruption):

run {
allocate channel d1 type disk;
backup check logical validate database;
release channel d1;
}

Now v$database_block_corruption view is populeted with corroupted blocks.

4. If you have RMAN backup and related archive logs then perform block corruption recovery using rman and go to step 9.


5. If DB is in open mode and you can query dictionary (dba_%) tables then go to the next step otherwise folow as below:
- execute below query to find out datafiles with corroupted blocks

select a.FILE#, a.NAME, b.TS#, b.NAME
from v$datafile a, v$tablespace b
where a.FILE# in
(select FILE# from v$database_block_corruption group by FILE#)
and a.TS# = b.TS#;

- make datafiles offline which are got from above query
- alter database open
- goto next step(6)

6. Execute the script

create table segment_corrupted (owner varchar2(10), segment_name varchar2(50), segment_type varchar(20));
truncate table segment_corrupted;

declare
nrows number;
badsegs number;
begin
badsegs:=0;
nrows:=0;
for i in (select file#,block# from v$database_block_corruption) loop
begin
insert into segment_corrupted select owner, segment_name, segment_type from dba_extents where file_id=i.file# and i.block# between block_id and block_id + blocks -1;
commit;
end;
nrows:=nrows+1;
end loop;
commit;
end;
/

7. Drop the objects found in segment_corrupted table

8. Recover datafiles (from step 5) and make online as below:
recover datafile 'datafilename';
alter database datafile 'datafilename' online;

9. Bounce the DB and look in the alert log if there are any alerts

10. Take a full DB backup using rman

Changing spfile location of RAC DATABASE

Last week we made some changes in the configuration (SGA & PGA) of our RAC DB using pfile. After open the each instance using pfile we executed below commands pfile with new configuration:
create spfile from pfile='.....';

As a result now each instance has different spfile in their default location ($ORACLE_HOME/dbs).
So we were planning to shift back spfile to a common location in ASM.

To do this we followed steps bellow:

1. Take backup of pfile & spfile

2. login to a instance(my case instance "dw1" of DB "dw") as sysdba

3. SQL> create pfile='/home/oracle/pfileaug31aug2010.ora' from spfile;

4. SQL> create spfile='+DATA1/dw/spfiledw.ora' from pfile='/home/oracle/pfileaug31aug2010.ora';

5. Then create a pfile in the default location($ORACLE_HOME/dbs/initSID.ora) having only the spfile location:
echo "SPFILE='+DATA1/axdw/spfiledw.ora'" > $ORACLE_HOME/dbs/initdw1.ora

6. delete the spfile in default location($ORACLE_HOME/dbs)

7. restart the current instance

8. Now repeat steps 5,6 & 7 for all other instances

9. Now while a instance starts
- it will look for spfile in the default location
- as no spfile is there it will look for pfile
- in pfile it will find the location of spfile and load init params for it

ORA-01548: active rollback segment '_SYSSMU39_115105166$' found

I tried to drop a undo tablespace that contains active rollback segments and got ORA-01548 error.

To resolve this problem follow below steps:
1. SQL>Create PFILE from SPFILE;

2. Edit pfile and set undo management to manual.
undo_management = manual

3. Enter the rollback segment using below parameter:

_offline_rollback_segments=('_SYSSMU39_115105166$')

4. SQL>startup mount pfile='\....\pfile'

5. alter Database datafile 'filename' offline drop; [if the datafile is corroupted]

6. SQL>alter Database Open;

7. SQL>drop Rollback Segment "_SYSSMU39_115105166$";

8. SQL>Drop Tablespace old__undo_tablespace_name Including Contents and datafiles;

9. create new undo tablespace.

10. Shutdown Immediate;

11. Change the pfile parameters
Undo_management = AUTO
parameter Undo_tablespace=new_undo_tablespace_name
and remove the _offline_rollback_segments parameter

12. Startup the Database

http://www.oracle-base.com/forums/viewtopic.php?f=1&t=9775&p=20714

Session status is KILLED but no cleanup

Today morning I killed some sessions which were INACTIVE for very long time (select * from v$session where LAST_CALL_ET/(60*60*24) > 1000) which was demanded by our application admin.

I killed those session and those sessions were marked KILLED (STATUS field of v$session). After 5 hours i checked those session again and found that those are still in v$session and status='KILLED'.

Then I used a query, joining v$session(paddr) with v$process(addr) to find OS process id(v$process.spid), but no rows were returned.
Then, checked v$transaction view to check whether any rollback is going on-

select s.sid,
s.program,
t.status as transaction_status,
s.status as session_status,
s.lockwait,
s.pq_status,
t.used_ublk as undo_blocks_used,
decode(bitand(t.flag, 128), 0, 'NO', 'YES') rolling_back
from v$session s, v$transaction t
where s.taddr = t.addr;

But there was no rollback going on.
After that I used below query to find status of waiting session-

select *
from v$session_wait
where sid in (select sid
from v$session
where username = 'USERNAME'
and status = 'KILLED');

This time I found my sessions in waiting state with wait_time=0, so, oracle is waithing for instructions from user/clients and I have to stop/kill client program/process.
But there was no corresponding row for those killed session in v$process (as paddr in v$session has been changed after those session and sessions were in waiting state).

So, I used below query to find and kill any unwanted process which don't have any corresponding entry in v$session-

SELECT spid
FROM v$process a
WHERE NOT EXISTS (SELECT 1 FROM v$session b WHERE b.paddr = a.addr);

I knew that I am using DEDICATED server, killing one process no impact on other (as no one is sharing that session). I also knew that those session were connected using TNS from remote machine.
So, before killing each process, I checked with `ps -ef|grep ` and if I found LOCAL=NO, then I killed the process.
After killing every process I waited for a while and checked in v$session fir one of KILLED session had been gone.

http://www.lazydba.com/oracle/0__4714.html
http://oracleunix.wordpress.com/2006/08/06/alter-system-kill-session-marked-for-killed-forever/
http://forums.oracle.com/forums/thread.jspa?threadID=695018

Using "sys_context" function

We were planning to restrict an DB user so that it can
a. only create session
b. have insert only into a table MYTABLE
c. Can insert on that table(MYTABLE) only from several selected servers

Point a & b are easy to resolve. But for pint c we had to do some this extra.
In order to implement point c we created a trigger on table MYTABLE which used function sys_context to collect & IP from the session and use these information for authentication. My trigger was as below:

create or replace trigger mytrg
before insert on MYTABLE
for each row
declare
var_ip varchar2(50);
var_count_ip number;
var_user varchar2(50);
begin
select sys_context('USERENV', 'IP_ADDRESS') into var_ip from dual;
select count(*) into var_count_ip from servers where ip = var_ip;
select user into var_user from dual;
if var_user = 'ALERTSENDER' and (var_ip is null or var_count_ip <>
RAISE_APPLICATION_ERROR(-20001,
'ERROR:' || var_ip ||
':NOT AN AUTHENTICATED IP');
else
--OTHER NORMAL OPERATIONS
end if;
end;

Detailed List of Locked Object

I order to find details of all locked object at any moment we can use below query:

select c.USERNAME,b.owner,b.object_name,b.object_type,
decode(a.LOCKED_MODE,
0,'none',
1,'null',
2,'Row- Share',
3,'Row-exclusive',
4,'Share',
5,'Share /Row- exclusive',
6,'exclusive') LOCKED_MODE,
c.SID, c.SERIAL#,d.SPID, c.OSUSER,c.PROGRAM,c.MACHINE
from v$locked_object a, dba_objects b, v$session c, v$process d
where a.OBJECT_ID = b.object_id
and a.SESSION_ID = c.SID
and c.PADDR = d.ADDR;