oracle更换undo,oracle替换undo表空间

最新推荐文章于 2023-02-16 16:16:07 发布

元庆国

最新推荐文章于 2023-02-16 16:16:07 发布

阅读量287

点赞数

文章标签： oracle更换undo

本文详细记录了在Oracle数据库中管理UNDO表空间时遇到的错误，包括错误地添加数据文件、创建错误的UNDO表空间、替换错误以及删除和重新创建的过程。最终成功修复了UNDO表空间的问题，并进行了相应的配置调整。

摘要由CSDN通过智能技术生成

---添加错文件名！

04:28:34 [email protected](ntxdb)> alter tablespace UNDOTBS1 add datafile ‘/u01/app/oracle/oradata/ntxdb/users02.dbf‘ size 30g autoextend off;

Tablespace altered.

04:30:20 [email protected](ntxdb)> alter tablespace UNDOTBS1 add datafile ‘/u01/app/oracle/oradata/ntxdb/users03.dbf‘ size 30g autoextend off;

Tablespace altered.

--undo创建错误

create tablespace UNDOTBS2 datafile ‘/u01/app/oracle/oradata/ntxdb/undotbs2_01.dbf‘ size 5G autoextend off;

create pfile=‘/home/oracle/pfile_0421‘ from spfile;

--导致替换报错

05:39:11 > alter system set undo_tablespace=undotbs2 scope=both;

alter system set undo_tablespace=undotbs2 scope=both

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-30012: undo tablespace ‘UNDOTBS2‘ does not exist or of wrong type

--删除错误的 undotbs2

05:43:26 > DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

05:43:34 >

--创建正确的 undo

create undo tablespace undotbs2datafile ‘/u01/app/oracle/oradata/ntxdb/undotbs2_01.dbf‘ size 10G ;

--替换

alter system set undo_tablespace=undotbs2 scope=both;

--删除

drop tablespace undotbs1 including contents and datafiles;

--重新替换回来！

create undo tablespace undotbs1 datafile ‘/u01/app/oracle/oradata/ntxdb/undotbs01.dbf‘ size 30G autoextend off;

alter system set undo_tablespace=undotbs1 scope=both;

drop tablespace undotbs2 including contents and datafiles;

--更新 pfile

create pfile from spfile;

alter tablespace UNDOTBS1 add datafile ‘/u01/app/oracle/oradata/ntxdb/undotbs02.dbf‘ size 30g autoextend off;

alter tablespace UNDOTBS1 add datafile ‘/u01/app/oracle/oradata/ntxdb/undotbs03.dbf‘ size 30g autoextend off;

原文：https://www.cnblogs.com/ss-33/p/12748345.html

关注