Replace corrupted undo tablespace SOA 10g

In certain cases, Oracle Products doesn't get corrupted, because bad stopping or something, this commands help us to change the undo and common tablespaces that fail commonly


In this case SOA doesn't start up because the undo tbs was currupted.

ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2;

--Resize undo datafile
--ALTER DATABASE DATAFILE '/u01/oradata/soa/undotbs02.dbf' RESIZE 10000M;

--Create undo tablespace
--CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/u01/oradata/soa/undotbs02.dbf' SIZE 1000M AUTOEXTEND ON;

--Drop undo tablespace
--drop tablespace UNDOTBS2;

select * from user_tablespaces;

--Drop undo tablespace including contents and datafiles
--DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

--Parameters
select * from  V$OPTION;


select
   file_name,
   bytes,
   autoextensible
from
   dba_data_files;

select * from
all_users;

select * from user_tablespaces;


SYSTEM
UNDOTBS1
SYSAUX
TEMP
USERS


ORABPEL


ALTER tablespace ORABPEL

alter tablespace ORABPEL add DATAFILE '/u01/oradata/soa/orabpe2.dbf' SIZE 5980M AUTOEXTEND ON NEXT 30M MAXSIZE UNLIMITED;

alter tablespace ORABPEL add DATAFILE '/u01/oradata/soa/orabpe3.dbf' SIZE 5980M AUTOEXTEND ON NEXT 30M MAXSIZE UNLIMITED;

alter tablespace ORABPEL add DATAFILE '/u01/oradata/soa/orabpe4.dbf' SIZE 5980M AUTOEXTEND ON NEXT 30M MAXSIZE UNLIMITED;

alter tablespace ORABPEL add DATAFILE '/u01/oradata/soa/orabpe5.dbf' SIZE 5980M AUTOEXTEND ON NEXT 30M MAXSIZE UNLIMITED;

alter TABLESPACE SYSAUX add DATAFILE  '/u01/oradata/soa/sysaux02.dbf' SIZE 550M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

alter TABLESPACE SYSAUX add DATAFILE  '/u01/oradata/soa/sysaux03.dbf' SIZE 550M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/u01/oradata/zucprod/undotbs02.dbf' SIZE 30000M AUTOEXTEND ON;

CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE
  '/u01/oradata/zucprod/undotbs01.dbf' SIZE 30000M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;


ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2;

DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;

UNDOTBS2

ODI 12c Loop PLSQL Procedure

Here's the procedure I commonly use to implement a odi loop in package.!

create or replace function sys.set_initial_data (P_TOKEN varchar2, P_VALUE varchar2, P_ROW_ID number, p_session number) return varchar2
is
PRAGMA AUTONOMOUS_TRANSACTION;

res varchar2(50);

cursor get_datap is
select *  from process_data_odi
where 1=1--status = (select case P_VALUE when 'ROW' THEN  'R' else 'P' end from  dual)
and status = 'P';
--and id_host = nvl(null,id_host);

vget_datap get_datap%rowtype;

cursor get_datap_id is
select *  from process_data_odi
where 1=1--status = (select case P_VALUE when 'ROW' THEN  'R' else 'P' end from  dual)
and id_host = nvl(P_ROW_ID,id_host);
--and status = 'P'

vget_datap_id  get_datap_id%rowtype;


begin

if p_token = 'INIT' then
delete from process_data_odi;
insert into process_data_odi
select p_session, id, 'P', null,IP, puerto from table_info_host;

commit;

return 'OK';
 
elsif p_token = 'GET' then


    open get_datap_id;
    fetch get_datap_id  into vget_datap_id;
    close get_datap_id;


  IF P_VALUE = 'PORT' then
    res := vget_datap_id.PUERTO;
  elsif P_VALUE = 'HOST' then
    res := vget_datap_id.HOST;
  elsif P_VALUE = 'ROW' then


open get_datap;
fetch get_datap into vget_datap;
close get_datap;


    res := vget_datap.ID_host;
 
  update process_data_odi set status = 'R'
  where id_host = vget_datap.ID_host;

  commit;  

  end if;

elsif p_token = 'SET' then

  update process_data_odi set status = 'T'
  where id_host = P_ROW_ID;

  commit;

return 'OK';

end if;

return nvl(RES,'ERR');
end;


SELECT *  FROM process_data_odi;

select set_initial_data('GET','PORT',2,1111) from dual;

select set_initial_data('GET','HOST',2,1111) from dual;

select set_initial_data('GET','ROW',null,1111) from dual;

select set_initial_data('SET',null,1,1111) from dual;

select set_initial_data('INIT',NULL,null,1111) from dual;

commit;