我们先来看一下这个现象:
ORA-01157错误,丢失undo tablespace中数据文件的解决方法
原创
©著作权归作者所有:来自51CTO博客作者lixinxin1994的原创作品,请联系作者获取转载授权,否则将追究法律责任
[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>
到此,该问题成功解决。
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
kubernetes无法删除namespaces命名空间的解决方法
通过etcd删除,亲测可行.[root@k8s-node1 dark]# kubectl get ns |grep TNAME STATUS AGEedison Terminating 6h27mwu
API kubernetes etcd namespaces k8s无法删除命名空间 -
ora-01157和ora-01110错误修复
ora-01157和ora-01110错误
修复 database cannot 数据库连接 identify -
针对ORA-01111, ORA-01110, ORA-01157的处理
针对ORA-01111, ORA-01110, ORA-01157的处理
Oracle数据库 晟数学院 报错处理 -
ORA-01157: cannot identify/lock data file处理
ORA-01157: cannot identify/lock data file处理
职场 休闲 ORA-01157 identify l -
11g rac ORA-01157问题处理
ORA-01157 数据文件路径错误
ORA 01157 数据