Recently our Helpdesk people configured backup on Payroll server. while running the backup, backup applications locking the writing file.for that issue alert log showing errors like below:
Mon Jul 06 05:07:36 2009
KCF: write/open error block=0x6f6 online=1
file=2 D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS01.DBF
error=27072 txt: 'OSD-04008: WriteFile() failure, unable to write to file
O/S-Error: (OS 33) The process cannot access the file because another process has locked a portion of the file.'
Automatic datafile offline due to write error on
file 2: D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS01.DBF
Mon Jul 06 05:07:36 2009
Errors in file d:\oracle9\product\payroll\admin\payroll9\bdump\payroll9_smon_2700.trc:
ORA-00376: file 2 cannot be read at this time
ORA-01110: data file 2: 'D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS01.DBF'
Immediately we stopped the backup & restarted the database .
Still the Application is not working. Application show error message
"ORA-01552: cannot use system rollback segment for non-system tablespace"
then I checked for the corrupted rollback segments
SQL>select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';
SEGMENT_NAME STATUS TABLESPACE_NAME
------------------------------ ---------------- -----------------
_SYSSMU1$ NEEDS RECOVERY UNDOTBS1
_SYSSMU2$ NEEDS RECOVERY UNDOTBS1
_SYSSMU3$ NEEDS RECOVERY UNDOTBS1
_SYSSMU4$ NEEDS RECOVERY UNDOTBS1
_SYSSMU5$ NEEDS RECOVERY UNDOTBS1
_SYSSMU6$ NEEDS RECOVERY UNDOTBS1
_SYSSMU7$ NEEDS RECOVERY UNDOTBS1
_SYSSMU8$ NEEDS RECOVERY UNDOTBS1
_SYSSMU9$ NEEDS RECOVERY UNDOTBS1
_SYSSMU10$ NEEDS RECOVERY UNDOTBS1
then I recreated another tablespace as UNDOTBS2
CREATE UNDO TABLESPACE "UNDOTBS2"
DATAFILE 'D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS02.DBF' SIZE 200M REUSE
AUTOEXTEND ON NEXT 51200K MAXSIZE 800M;
ALTER SYSTEM SET undo_tablespace=UNDOTBS2 SCOPE=BOTH;
Now I try to drop the old UNDO tablespace but it’s giving error:
ora-01548 active rollback segment found
Now the issue is resolved but in Alert log we are getting errors like:
Wed Jul 15 11:50:52 2009
SMON: about to recover undo segment 1
SMON: mark undo segment 1 as needs recovery
……..
SMON: about to recover undo segment 10
SMON: mark undo segment 10 as needs recovery
& for some transactions from applications we are getting some more errors:
ORA-00376: file 2 cannot be read at this tme ORA-01110: data file 2: 'D:\ORACLE9\PRODUCT\PAYROLL\ORADATA\PAYROLL9\UNDOTBS01.DBF'
Solution:
1. create pfile from spfile;
2. Add the following line to pfile:
_corrupted_rollback_segments =('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$',
'_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$')
_OFFLINE_ROLLBACK_SEGMENTS=('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$')
3. comment undo management-Auto
#undo_management=AUTO
4. create spfile from pfile
5. Start the database again:
SQL> STARTUP RESTRICT
6. Drop bad rollback segments
SQL> drop rollback segment "_SYSSMU11$";
Rollback segment dropped.
......
SQL> drop rollback segment "_SYSSMU10$";
Rollback segment dropped.
7. Check again
SQL> select segment_name,status,tablespace_name from dba_rollback_segs where status='NEEDS RECOVERY';
No rows returned
8. DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES ;
9. shutdown immedaite
10. remove added lines & comments from pfile and recreate the spfile
11.startup
Now the issue is resolved.
