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!

Soa purge 11g

Since the start on fusion we have been plagued with purge related issues, 11.1.1.3 the purge job barely worked.So we applied patches on top of it to fix it.On 11.1.1.5 its far better.So i will talk W.R.T 11.1.1.5 or higher version of SOA,

There are 2 options for purging SOA Tables :
 1)  Looped Purge - The stored procedure soa.delete_instances provides the looped purge implementation. Debug mode can be enabled by executing  soa_purge/common/debug_on.sql and setting serverout on.
Sample client to execute looped purge is listed below.

DECLARE
   MAX_CREATION_DATE timestamp;
   MIN_CREATION_DATE timestamp;
   batch_size integer;
   max_runtime integer;
   retention_period timestamp;
  BEGIN
   MIN_CREATION_DATE := to_timestamp('2010-01-01','YYYY-MM-DD');
   MAX_CREATION_DATE := to_timestamp('2010-01-31','YYYY-MM-DD');
    max_runtime := 60;
    retention_period := to_timestamp('2010-01-31','YYYY-MM-DD');
   batch_size := 10000;
     soa.delete_instances(
     min_creation_date => MIN_CREATION_DATE,
     max_creation_date => MAX_CREATION_DATE,
     batch_size => batch_size,
     max_runtime => max_runtime,
     retention_period => retention_period,
     purge_partitioned_component => false);
  END;
  /

 2) Parallel Purge: The stored  procedure soa.delete_instances_in_parallel provides the parallel purge implementation  To execute the parallel purge, the following additional privileges are required for the soainfra user.Parallel purge is similar to loop purge with additional flexibility of spawning parallel threads to do the purging (faster, multi threaded approach if dealing with huge number of records)
    i)  Create Job
    ii) execute on dbms_lock.

DECLARE
   max_creation_date timestamp;
   min_creation_date timestamp;
   retention_period timestamp;
  BEGIN
   min_creation_date := to_timestamp('2010-01-01','YYYY-MM-DD');
   max_creation_date := to_timestamp('2010-01-31','YYYY-MM-DD');
   retention_period := to_timestamp('2010-01-31','YYYY-MM-DD');
    soa.delete_instances_in_parallel(
     min_creation_date => min_creation_date,
     max_creation_date => max_creation_date,
     batch_size => 10000,
     max_runtime => 60,
     retention_period => retention_period,
     DOP => 3,
     max_count => 1000000,
     purge_partitioned_component => false);
 END;

 Debug mode can be enabled by executing the script 'soa_purge/common/debug_on.sql' and setting serverout on .  The debug logs from the  jobs  spawned by parallel purge will  be logged into files created in the directory 'SOA_PURGE_DIR' . This directory needs to be created(e.g: create directory SOA_PURGE_DIR as '\tmp\purge') and the soainfra user needs to be provided write access on this directory. Also ensure that the location of the  directory is  accessible to the oracle server.

There is another option ignore_state=> true ,which purges instances regardless of its state.Normally only completed instances are purged.