SOA PS4 11.1.1.5.3 (PS4 BUNDLE) - Sca_UpdateSOAMDS Composite Missing

We were applying this bundle patch on SOA and found that as per post install steps the composite was missing.As per a note from Oracle.

Engineering confirmed that the step "Redeploy the sca_UpdateSOAMDS composite from $SOA_HOME/soa/prov directory" can be skipped as a non Fusion Applications User.

So this step can be safely skipped for SOA 11g.

BAM alerts to external email id's

A customer reported a strange issue where they were unable to send emails from BAM application to external emails. I searched for it but was unable to find anything on google or in oracle documentation.I have given this as fix, they are yet to get back.But i am confident that it will fix the issue.

The feature is apparently there from 11.1.1.3 onwards.

1)
cd  <SOA Domain>/servers/bam_server1/tmp/_WL_user/oracle-bam_11.1.1/1o1igs/config/

take a backup of BAMCommonConfig.xml

from:    <AlertActionAllowExternalEmail>false</AlertActionAllowExternalEmail>
to:     <AlertActionAllowExternalEmail>true</AlertActionAllowExternalEmail>

2) cd <SOA Domain>/config/fmwconfig/servers/bam_server1/applications/oracle-bam_11.1.1/config/

take a backup of BAMCommonConfig.xml

inside the <BAMCommon> tag add the below line,
  <AlertActionAllowExternalEmail>true</AlertActionAllowExternalEmail>

3)Restart BAm server.

Recover datafile (ORA-00376)

Errors in file /oracle/log/diag/rdbms/prod/trace/prod_smon_28281.trc:
ORA-00376: file 3 cannot be read at this time
ORA-01110: data file 3: '/oracle/data/undotbs01.dbf'


SQL>  select * from v$recover_file
  2  /

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ---------------
         2 OFFLINE OFFLINE                                                                     45342425 31-AUG-11
         3 OFFLINE OFFLINE                                                                     45342425 31-AUG-11
        20 OFFLINE OFFLINE                                                                     45342425 31-AUG-11


SQL> select tablespace_name,status from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id =&give_file_id);
Enter value for give_file_id: 2
old   1: select tablespace_name,status from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id =&give_file_id)
new   1: select tablespace_name,status from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id =2)

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSAUX                         ONLINE

SQL> /

Enter value for give_file_id: 3

old   1: select tablespace_name,status from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id =&give_file_id)

new   1: select tablespace_name,status from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id =3)

TABLESPACE_NAME                STATUS

------------------------------ ---------

UNDOTBS1                       ONLINE

SQL> /

Enter value for give_file_id: 20

old   1: select tablespace_name,status from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id =&give_file_id)

new   1: select tablespace_name,status from dba_tablespaces where tablespace_name=(select tablespace_name from dba_data_files where file_id =20)

TABLESPACE_NAME                STATUS

------------------------------ ---------

SNPM                           ONLINE

SQL>  select FILE# from v$datafile where status in ('OFFLINE','RECOVER');

     FILE#

----------

         2

         3

        20

SQL>  select status, enabled from v$datafile where file#=&give_file_id;

Enter value for give_file_id: 2

old   1:  select status, enabled from v$datafile where file#=&give_file_id

new   1:  select status, enabled from v$datafile where file#=2

STATUS  ENABLED

------- ----------

RECOVER READ WRITE

SQL> /

Enter value for give_file_id: 3

old   1:  select status, enabled from v$datafile where file#=&give_file_id

new   1:  select status, enabled from v$datafile where file#=3

STATUS  ENABLED

------- ----------

RECOVER READ WRITE

SQL> /

Enter value for give_file_id: 20

old   1:  select status, enabled from v$datafile where file#=&give_file_id

new   1:  select status, enabled from v$datafile where file#=20

STATUS  ENABLED

------- ----------

RECOVER READ WRITE

SELECT FILE#, STATUS, ERROR, RECOVER, TABLESPACE_NAME, NAME

 FROM V$DATAFILE_HEADER

 WHERE RECOVER = 'YES' OR (RECOVER IS NULL AND ERROR IS NOT NULL);

SQL>  alter database datafile '/oracle/data/snpm01.dbf' online;

Database altered.

SQL>  alter database datafile /oracle/data/undotbs01.dbf' online;

Database altered.

SQL>  select FILE# from v$datafile where status in ('OFFLINE','RECOVER');

no rows selected

SQL> alter database open;

Terminate inactive database sessions from oracle database

Terminate all inactive sessions ..which are inactive for more than 3 hours & then kill the
    processes from O/S
   
    Run the below sql's and spool the out put
   
    select 'alter system kill session '||''''||a.sid|| ','||a.serial#||''''||';'
      from v$session a, v$process b where
      a.paddr =b.addr
      and a.last_call_et > 3600*3
      and status='INACTIVE'
    order by last_call_et
    /
   
    select 'kill -9 '||b.spid
      from v$session a, v$process b where
      a.paddr =b.addr
      and a.last_call_et > 3600*3
      and status='INACTIVE'
    order by last_call_et
    /
   
 use the first spool file..for db sessions kill..and run that 3-4 times
   
Then use the 2nd spool file..to terminate the processes at os level
   

Oracle datafile with junk character

I faced some issues where the datafile has a junk character.(Maybe some copy pasted the datafile creation command with spaces of special characters).This causes the hot backup on database to fail. This is how i fixed the issue.

Check the datafile,


SQL> select FILE_NAME,TABLESPACE_NAME,STATUS,ONLINE_STATUS from dba_data_files where TABLESPACE_NAME='REPORTS';

FILE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME                STATUS    ONLINE_
------------------------------ --------- -------
/sid/data/reports.dbf
REPORTS                 AVAILABLE ONLINE


$ ls -lb /sid/data/reports.dbf*
-rw-r-----  1 orora dbora 524320768 Apr  2 00:39 /sid/data/reports.dbf\

Now to fix  the issue
---------------------------
alter database datafile '/sid/data/reports.dbf ' offline; -->Note the space after dbf



mv '/sid/data/reports.dbf  ' /sid/data/reports.dbf   -->Note the space

 alter database  rename file '/sid/data/reports.dbf  ' to '/sid/data/reports.dbf ';

 recover datafile '/sid/data/reports.dbf ';

 alter database datafile '/sid/data/reports.dbf' online;


SQL> select TABLESPACE_NAME,STATUS,ONLINE_STATUS,FILE_NAME from dba_data_files where FILE_NAME = /sid/data/reports.dbf';

TABLESPACE_NAME                STATUS                         ONLINE_STATUS                  FILE_NAME
------------------------------ ------------------------------ ------------------------------ ------------------------------
OBITHD_REPORTS                 AVAILABLE                      ONLINE                         /sid/data/reports.dbf
                                                                                     
[AMD64] orora@host > ls -ltr reports.dbf
-rw-r-----  1 orora dbora 524320768 Mar 18 21:38 reports.dbf

This fixed the issue!