Datafile recovery

The I/O of the server

18780 be/4 oracle      3.08 M/s    0.00 B/s  0.00 % 90.70 % ora_m000_ora11g
15091 be/4 oracle      0.00 B/s   27.11 K/s  0.00 %  0.37 % ora_ckpt_orcl11g
69780 be/4 oracle      0.00 B/s  457.56 K/s  0.00 %  0.00 % ora_pmon_ora11g
69824 be/4 oracle      0.00 B/s    6.78 K/s  0.00 %  0.00 % ora_mmnl_ora11g
69872 be/4 oracle      0.00 B/s    3.39 K/s  0.00 %  0.00 % ora_cjq0_ora11g.

Its been observed that ora_m000_ora11g uses the IO to 90 to 97 % for every 30 secs or odd.

1. First I checked ADRCI report. The below is the output.

[oracle@blrsrv26 ~]$ adrci

ADRCI: Release 11.2.0.2.0 - Production on Tue Sep 17 04:57:20 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/disk1/oracle/app/oracle"
adrci> show problem

ADR Home = /disk1/oracle/app/oracle/diag/rdbms/ora11g/ora11g:
*************************************************************************
PROBLEM_ID           PROBLEM_KEY                                                 LAST_INCIDENT        LASTINC_TIME
-------------------- ----------------------------------------------------------- -------------------- ----------------------------------------
1                    ORA 4031                                                    212529               2013-07-05 08:01:28.537000 -04:00
2                    ORA 603                                                     939079               2013-09-11 09:42:48.420000 -04:00

ADR Home = /disk1/oracle/app/oracle/diag/rdbms/orcl11g/orcl11g:
*************************************************************************
0 rows fetched

ADR Home = /disk1/oracle/app/oracle/diag/tnslsnr/blrsrv26/listener:
*************************************************************************
0 rows fetched

I hope nothing will help here for the above issue. So 

2. I checked the alertlog. THe bwlo is the output.

ORA-00376: file 3 cannot be read at this time

ORA-01110: data file 3: '/disk1/oracle/app/oracle/oradata/ora11g/undotbs01.dbf'

Errors in file /disk1/oracle/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_pmon_69780.trc:

ORA-00376: file 3 cannot be read at this time

ORA-01110: data file 3: '/disk1/oracle/app/oracle/oradata/ora11g/undotbs01.dbf'

Errors in file /disk1/oracle/app/oracle/diag/rdbms/ora11g/ora11g/trace/ora11g_pmon_69780.trc:

ORA-00376: file 3 cannot be read at this time

ORA-01110: data file 3: '/disk1/oracle/app/oracle/oradata/ora11g/undotbs01.dbf'

.........

All with same errors.

So first I decided to resolve the UNDO tablespace issue.

I checked in v$datafile which are looking for recovery.

NAME

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

STATUS

-------

/disk1/oracle/app/oracle/oradata/ora11g/sysaux01.dbf

RECOVER

/disk1/oracle/app/oracle/oradata/ora11g/undotbs01.dbf

RECOVER

So Datafile sysaux, datafile undo needs recovery.

I tried to recover the files.

SQL> recover datafile 2;

ORA-01554: transaction concurrency limit reached reason:no undo segment found with available slot params:0, 0

Got the above error.

SQL> recover datafile 3;

ORA-01554: transaction concurrency limit reached reason:no undo segment found with available slot params:0, 0

Same for Undo datafile also.

I tried to create new undo tbs and planned to allocated it as default tablespace.

SQL> create undo tablespace undotbs2 datafile '/disk1/oracle/app/oracle/oradata/ora11g/undotbs2.dbf' size 1g autoextend on maxsize 20g;

create undo tablespace undotbs2 datafile '/disk1/oracle/app/oracle/oradata/ora11g/undotbs2.dbf' size 1g autoextend on maxsize 20g

*

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01554: transaction concurrency limit reached reason:no undo segment found with available slot params:0, 0

The current undo tablespace is not at all allowing for new transactions.

I got and idea... First I disabled the undo tablespace for the DB.

SQL> alter system set undo_tablespace='' scope=both;

System altered.

Then created new undo tbs...

SQL> create undo tablespace undotbs2 datafile '/disk1/oracle/app/oracle/oradata/ora11g/undotbs2.dbf' size 1g autoextend on maxsize 20g;

Tablespace created.

Then altered the undo tablespace to newly created tbs...

SQL> alter system set undo_tablespace='UNDOTBS2' scope=both;

System altered.

Now I checked for datafiles which are looking for recovery....

select name,status,file# from v$datafile where status='RECOVER';

Even sysaux also not in the recover status....