数据库恢复一例(1)

oracle9i回滚段表空间丢失后的处理方法:

用隐含参数恢复数据库的例子：

具体操作步骤如下：

首先把初init.ora文件里自动管理改为手工管理,然后加入隐含参数:

#undo_management=AUTO

undo_tablespace=UNDOTBS

_corrupted_rollback_segments=(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)

SQL>startup mount （数据库启动到mount状态)

SQL> alter database datafile 'D:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF' offline drop；

Database altered.

SQL>alter database open;

Database opened.

SQL> show parameter undo

NAME TYPE VALUE

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

undo_management string MANUAL

undo_retention integer 900

undo_suppress_errors boolean FALSE

undo_tablespace string UNDOTBS

SQL> drop tablespace undotbs including contents;

Tablespace dropped.

重建undotbs表空间:

SQL> create undo tablespace undotbs datafile 'D:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF'

size 100M;

Tablespace created.

SQL> shutdown immediate (关闭数据库)

Database closed.

Database dismounted.

ORACLE instance shut down.

编辑init.ora初始化参数文件,去掉隐含参数，设置

undo_management=AUTO

undo_tablespace=UNDOTBS

保存init.ora文件，然后执行

SQL> startup mount

ORACLE instance mounted.

Total System Global Area 114061244 bytes

Fixed Size 282556 bytes

Variable Size 79691776 bytes

Database Buffers 33554432 bytes

Redo Buffers 532480 bytes

Database mounted.

SQL>alter database datafile 'D:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF' online;

Database altered.

SQL>alter database open;

Database opened.

SQL> show parameter undo

NAME TYPE VALUE

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

undo_management string AUTO

undo_retention integer 900

undo_suppress_errors boolean FALSE

undo_tablespace string UNDOTBS