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;

Foreign Key Constraints Referencing to a Table

We had a table to purge with primary key.
Problem is we need to backup purged data which includes data from all the child tables referencing with foreign keys to that table.
Now I had to find all the child tables and their foreign key columns. I used below query to resolve this problem:

select a.owner,
a.constraint_name,
a.constraint_type,
a.table_name,
b.column_name,
a.r_owner,
a.r_constraint_name
from dba_constraints a, dba_cons_columns b
where a.owner = 'MYUSER'
and a.owner = b.owner
and a.constraint_name = b.constraint_name
and a.r_constraint_name in
(select constraint_name
from dba_constraints
where table_name = 'MYTABLE'
and owner = 'MYUSER');


http://bytes.com/topic/oracle/answers/644008-query-find-primary-foreign-keys
http://www.databasejournal.com/features/oracle/article.php/3665591/Finding-Foreign-Key-Constraints-in-Oracle.htm

Execution Plan & Estimate Temporary Tablespace Usages

One of our developer was informing that he need extra space on temporary tablespace as one of his adhoc query is running for 2 hours (and may run for 10 hours) and consuming huge amount of temp space.

Database was Oracle 11gR2 on RHEL5.
Now problem is how can I estimate that how much space should I add ? I tried with execution plan
as below:

sqlplus user/pass@DB

sql> delete from myuser.plan_table;

sql> commit;

sql> explain plan for
select * from .....


sql> select * from myuser.plan_table;


But I could not find any field that said about Temp Usage.
Then searching the web I became know that Older version of plan table(I found default) don't have that field. It should be created manually and than explain the a query:

cd /ORAIN/oracle/product/11.2.0/dbhome_1/rdbms/admin/


sqlplus user/pass@DB

sql> drop myuser.plan_table purge;

sql> @utlxplan.sql

Table created.

sql> delete from myuser.plan_table;

sql> commit;

sql> explain plan for
select * from .....


sql> select * from myuser.plan_table;

Here TEMP_SPACE column will show the estimated space usage. Then I added the extra Temp space according to the value [added more than estimated for safety purpose] showed by column TEMP_SPACE and it worked.


Recent optimizer statistics should be gathered for Tables and Indexes used the query for which plan is being generated in order to get more accurate result.

http://oradbatips.blogspot.com/2008/02/tip-69-estimate-temp-usage-without.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:12519780526076