How to re-create UNDO tablespace in Rac databases.

First, check what undo files in the tablespaces..

1)

set linesize 200;

col FILE_NAME for a80;

col TABLESPACE_NAME for a20;

select FILE_NAME,TABLESPACE_NAME,status,online_status from dba_data_files where tablespace_name like 'UNDO%';

check size of the tablespace...

 SELECT tablespace_name,

        SUM ( ( (bytes / 1024) / 1024) / 1024) size_gb,

        COUNT (file_id) no_of_datafiles

 FROM dba_data_files

 WHERE tablespace_name LIKE 'UNDOTBS1'

 GROUP BY tablespace_name;

2)

see what UNDO tablespace is currently used... 

show parameter UNDO_TABLESPACE;

second, check what segments are still tried to the UNDO...

SELECT a.name,b.status , d.username , d.sid , d.serial#

FROM   v$rollname a,v$rollstat b, v$transaction c , v$session d

WHERE  a.usn = b.usn

AND    a.usn = c.xidusn

AND    c.ses_addr = d.saddr

AND    a.name IN ( 

  SELECT segment_name

  FROM dba_segments 

  WHERE tablespace_name = 'UNDOTBS1'

);

If no rows found.... then..

alter system set undo_tablespace=UNDOTBS2 scope=both SID='RAC_1'

drop tablespace UNDOTBS1 including contents and datafiles;

3) re-create undo tablespace again....

create undo tablespace UNDOTBS1 datafile '+DATA' size 10G AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

     alter tablespace UNDOTBS1 add datafile '+DATIND' size 5G AUTOEXTEND ON NEXT 100M MAXSIZE 10G;

follow the same for the other undo too...

Hope this Helps...

Upate a column based on the results of multiple tables in oracle

below is the generic select..

select b.abc,c.abc,c.abc,p.abc ,w.abc from table_A a,((c.abc*abc)*p.abc) as "normal"
table_A b, table_A c,table_A p, table_A w
where b.tab_A=f.tab_A
and b.tab_A=p.tab_A
and b.tab_A=w.tab_A
and p.tab_A=xxx
and c.tab_A >0
and c.tab_A >0
and rownum <10;

to update a table column based on mutiple tables result... use Merge Statement:


merge into table_A s
using
(select ((c.abc*abc)*p.abc)  as normal,w.abc  from
table_A f, table_A b,table_A p,table_A w
where b.tab_A=f.tab_A
and b.tab_A=p.tab_A
and b.tab_A=w.tab_A
and p.tab_A=xxx
and c.tab_A >0
and c.tab_A >0
and ((c.abc*abc)*p.abc) <> w.abc and rownum <10) z
on (s.abc=z.abc)
when matched then update set s.abc=z.abc;

Patch Inventory is missing in Alert log while DB is bounced.

In some cases, when a particular database is started the patch inventory is not loaded in the alert log and  says  “No patches have been applied” in the alert log.

To make the inventory to be loaded in alert log. Run the below statement in the database (and expect output as “OK”  and re-start the database again).

Note:- Don’t run ./datapatch from now…

select dbms_sqlpatch.verify_queryable_inventory from dual;

Hope this Helps...