ORA-01157错误，丢失undo tablespace中数据文件的解决方法

我们先来看一下这个现象：

[oracle@djp ora12]$ pwd

/u01/app/oracle/oradata/ora12

[oracle@djp ora12]$ mv undotbs01.dbf undotbs01.dbfbak

这里，我们把unto表空间对应的数据文件作了一个更改，表面该文件已经丢失。

下面，我们使用SQL*Plus，进行对数据库的启动：

idle> conn / as sysdba

Connected to an idle instance.

idle> startup mount;

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2293880 bytes

Variable Size 469766024 bytes

Database Buffers 356515840 bytes

Redo Buffers 6529024 bytes

Database mounted.

idle> alter database open;

alter database open

ERROR at line 1:

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file

ORA-01110: data file 3: '/u01/app/oracle/oradata/ora12/undotbs01.dbf'

idle>

这时，发现启动失败。对于该现象，我们可以进行如下的处理：

（1）设置undo_management为auto，由自动（auto）设置为手动（manual）。这因为，当设置为auto时，系统采用undo表空间管理回滚段，当设置manual时，系统使用回滚段。这里，相当于是禁用undo tablespace。

idle> alter system set undo_management = manual scope=spfile;

System altered.

idle>

（2）再次启动数据库，使用其生效

idle> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

idle> startup;

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2293880 bytes

Variable Size 444600200 bytes

Database Buffers 381681664 bytes

Redo Buffers 6529024 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 3 - see DBWR trace file

ORA-01110: data file 3: '/u01/app/oracle/oradata/ora12/undotbs01.dbf'

idle>

（3）对丢失的数据库文件进行一个离线删除，如下：

idle> alter database datafile '/u01/app/oracle/oradata/ora12/undotbs01.dbf'

2 offline drop

3 /

Database altered.

idle>

（4）打开数据库，并对undo tablespace重新创建。

idle> alter database open;

Database altered.

idle> drop tablespace undotbs

2 /

Tablespace dropped.

idle> create undo tablespace undotbs

2 datafile '/u01/app/oracle/oradata/ora12/undotbs01.dbf'

3 size 100M

4 autoextend on

5 next 50M

6 maxsize unlimited

7 /

Tablespace created.

idle>

（5）再次设置undo_management为auto，并重启数据库：

idle> alter system set undo_management = auto scope=spfile

2 /

System altered.

idle> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

idle> startup;

ORACLE instance started.

Total System Global Area 835104768 bytes

Fixed Size 2293880 bytes

Variable Size 469766024 bytes

Database Buffers 356515840 bytes

Redo Buffers 6529024 bytes

Database mounted.

Database opened.

idle> desc dba_objects;

Name Null? Type

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

OWNER VARCHAR2(128)

OBJECT_NAME VARCHAR2(128)

SUBOBJECT_NAME VARCHAR2(128)

OBJECT_ID NUMBER

DATA_OBJECT_ID NUMBER

OBJECT_TYPE VARCHAR2(23)

CREATED DATE

LAST_DDL_TIME DATE

TIMESTAMP VARCHAR2(19)

STATUS VARCHAR2(7)

TEMPORARY VARCHAR2(1)

GENERATED VARCHAR2(1)

SECONDARY VARCHAR2(1)

NAMESPACE NUMBER

EDITION_NAME VARCHAR2(128)

SHARING VARCHAR2(13)

EDITIONABLE VARCHAR2(1)

ORACLE_MAINTAINED VARCHAR2(1)

idle>

到此，该问题成功解决。

巴特西

ORA-01157错误，丢失undo tablespace中数据文件的解决方法

最新文章

热门文章