UNDO表空间文件损坏处理

                       UNDO表空间文件损坏处理
 
    某测试系统在一天中突然停电四五次，当第六次来电后，尝试启动数据库报文件错误

SQL>  startup
ORACLE instance started.

Total System Global Area 1119043584 bytes
Fixed Size                  2158952 bytes
Variable Size             469765784 bytes
Database Buffers          637534208 bytes
Redo Buffers                9584640 bytes
Database mounted.
ORA-01115: IO error reading block from file 3 (block # 11483)
ORA-01110: data file 3: '/usr/oracle/oradata/pttest/undotbs01.dbf'
ORA-27072: File I/O error
Linux-x86_64 Error: 5: Input/output error
Additional information: 4
Additional information: 11481
Additional information: -1
ORA-01115: IO error reading block from file 3 (block # 11482)
ORA-01110: data file 3: '/usr/oracle/oradata/pttest/undotbs01.dbf'
ORA-27072: File I/O error
Linux-x86_64 Error: 5: Input/output error
Additional information: 4
Additional information: 11481
Additional information: -1

由于测试系统没有备份，数据库也不是处理归档状态，这下感觉有点麻烦。

通过dd和cp命令在os级别测试，发现也分别报错

dd if=/usr/oracle/oradata/pttest/undotbs01.dbf of=/dev/null

cp /usr/oracle/oradata/pttest/undotbs01.dbf /home/oracle/b.dbf

但是通过dd if=/dev/sdb1(usr/oracle所在目录)可以读取，因此现在可以判断是文件系统出错

通过fsck /dev/sdb1修复，发现问题照旧,看来从os级别修复这个问题行不通了.

通过创建一个pfile,把undo_management改为manual,再尝试offline undotbs01.dbf文件
发现报错，不能进行offline操作.

SQL>  startup pfile='/home/oracle/b.ora';
ORACLE instance started.

Total System Global Area 1119043584 bytes
Fixed Size                  2158952 bytes
Variable Size             469765784 bytes
Database Buffers          637534208 bytes
Redo Buffers                9584640 bytes
SQL> alter database datafile 3 offline;
alter database datafile 3 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

正是由于我的数据库处于非归档状态，所以不能offline.

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     82902
Current log sequence           82904

看来只有把undo表空间删除了，但是可能会导致数据文件不一致，不过可以
通过隐含参数把数据库先打开再说

SQL> alter database datafile 3 offline drop;

Database altered.

SQL> alter database open;

Database altered.

数据库总算打开了，作为一个DBA每次看到这个显示感觉相当亲切

尝试把把原来的undo表空间删除

SQL>  drop tablespace undotbs1 including contents and datafiles;
 drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_1221203536$' found, terminate
dropping tablespace

看来还有活动的undo段，需要隐含参数来处理。再次重启，已可以成功删除了

SQL>  drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

后续收尾就新建一个undo表空间，改回自动，并使用新建的undo表空间.