oracle undo丢失恢复方法!学习参考！

[p=20, null, left]2011-12-16 01:14:32|  分类： oracle |  标签： |字号大中小 [url=]订阅[/url] [/p]

一、数据库正常关闭
SQL> shutdown immeidate关闭数据库，删除undo文件
SQL> startup
ORACLE instance started.
Total System Global Area  444596224 bytes
Fixed Size                  2021440 bytes
Variable Size             134219712 bytes
Database Buffers          306184192 bytes
Redo Buffers                2170880 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/opt/oracle/oradata/LIKG/undotbs01.dbf'
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/LIKG/system01.dbf
/opt/oracle/oradata/LIKG/undotbs01.dbf
/opt/oracle/oradata/LIKG/sysaux01.dbf
/opt/oracle/oradata/LIKG/users01.dbf
/opt/oracle/oradata/LIKG/example01.dbf
/opt/oracle/oradata/LIKG/tbs001.dbf
6 rows selected.
SQL> alter database datafile > '/opt/oracle/oradata/LIKG/undotbs01.dbf' offline drop;
alter database datafile > '/opt/oracle/oradata/LIKG/undotbs01.dbf' offline drop
                        *
ERROR at line 1:
ORA-02236: invalid file name
直接offline drop丢失的控制文件，更改默认表空间。
SQL> alter database datafile '/opt/oracle/oradata/LIKG/undotbs01.dbf' offline drop;  
Database altered.
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
/opt/oracle/oradata/LIKG/system01.dbf
/opt/oracle/oradata/LIKG/undotbs01.dbf
/opt/oracle/oradata/LIKG/sysaux01.dbf
/opt/oracle/oradata/LIKG/users01.dbf
/opt/oracle/oradata/LIKG/example01.dbf
/opt/oracle/oradata/LIKG/tbs001.dbf
6 rows selected.
打开数据库，可以正常打开。
SQL> alter database open;
Database altered.
SQL> create undo tablespace undotbs02 datafile '/opt/oracle/oradata/LIKG/undotbs02.dbf' size 50m;
Tablespace created.
SQL> alter system set undo_tablespace=undotbs02 scope=spfile;
System altered.
数据库成功打开。

二、数据库不正常关闭情况
SQL> shutdown abort;
alter database datafile '/opt/oracle/oradata/LIKG/undotbs01.dbf' offline drop;
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
报错了，数据库为什么open不了
startup mount 查查数据文件，发现undotbs01.dbf没删掉
SQL> select name from v$datafile;
NAME
----------------------------------------------
/opt/oracle/oradata/LIKG/system01.dbf
/opt/oracle/oradata/LIKG/undotbs01.dbf
/opt/oracle/oradata/LIKG/sysaux01.dbf
/opt/oracle/oradata/LIKG/users01.dbf
/opt/oracle/oradata/LIKG/example01.dbf
/opt/oracle/oradata/LIKG/tbs001.dbf
由于数据库不完整关闭，还是没办法启动。修改pfile看看
*.undo_management='MANUAL'
*.undo_tablespace='UNDOTBS1'
*._corrupted_rollback_segments=('_SYSSMU1$','_SYSSMU2$','_SYSSMU3$','_SYSSMU4$','_SYSSMU5$','_SYSSMU6$','_SYSSMU7$','_SYSSMU8$','_SYSSMU9$','_SYSSMU10$')
*._allow_resetlogs_corruption=true
SQL> startup pfile='?/dbs/initLIKG.ora'
..
Database mounted.
Database opened.
成功打开

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26940217/viewspace-732175/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26940217/viewspace-732175/