ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle重建UNDO表空间

oracle重建UNDO表空间

原创 Linux操作系统 作者:yefeng139 时间:2011-10-14 14:04:38 0 删除 编辑

1.查看数据库undo表空间的dbf文件是否大于25G,如果大于我们则重建。

-rw-r----- 1 oracle oinstall  26G 2011-05-10 10:10 /opt/oracle/oradata/CMCCWAP/undotbs02.dbf

 

 

2.查看数据库的默认UNDO表空间(红色部分显示为UNDOTBS2,说明当前使用的是表空间为:UNDOTBS2

SQL> show parameter undo;

NAME                                TYPE       VALUE

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

undo_management                     string     AUTO

undo_retention                      integer    900

undo_tablespace                     string     UNDOTBS2

 

 

3.创建新的UNDO空间UNDOTB1

SQL> create undo tablespace UNDOTBS1 datafile '/opt/oracle/oradata/CMCCWAP/undotbs01.dbf' size 200m autoextend on;

 

 

4.将数据库的默认表空间切换到新创建的表空间

SQL> alter system set undo_tablespace=UNDOTBS1 scope=both;

 

5.查看数据库的默认表空间是否已经切换到新创建的表空间,如下红色部分使用的是UNDOTBS1,说明已经迁移到新的表空间

SQL> show parameter undo;

NAME                                TYPE       VALUE

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

undo_management                     string     AUTO

undo_retention                      integer    900

undo_tablespace                     string     UNDOTBS1

 

 

6.确保所有在UNDOTBS2undo segment都已offline,如下红色部分表示还未offline

SQL> select SEGMENT_NAME ,STATUS ,TABLESPACE_NAME from dba_rollback_segs;

SEGMENT_NAME                  STATUS          TABLESPACE_NAME

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

SYSTEM                        ONLINE          SYSTEM

_SYSSMU1$                     OFFLINE         UNDOTBS2

_SYSSMU2$                     OFFLINE         UNDOTBS2

_SYSSMU3$                     OFFLINE         UNDOTBS2

_SYSSMU4$                     OFFLINE         UNDOTBS2

_SYSSMU5$                     OFFLINE         UNDOTBS2

_SYSSMU6$                     OFFLINE         UNDOTBS2

_SYSSMU7$                     ONLINE          UNDOTBS2

_SYSSMU8$                     OFFLINE         UNDOTBS2

_SYSSMU9$                     OFFLINE         UNDOTBS2

_SYSSMU10$                    OFFLINE         UNDOTBS2

SEGMENT_NAME                  STATUS          TABLESPACE_NAME

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

_SYSSMU11$                    OFFLINE         UNDOTBS2

_SYSSMU12$                    ONLINE          UNDOTBS1

_SYSSMU13$                    ONLINE          UNDOTBS1

_SYSSMU14$                    ONLINE          UNDOTBS1

_SYSSMU15$                    ONLINE          UNDOTBS1

_SYSSMU16$                    ONLINE          UNDOTBS1

17 rows selected.

oracle在没有undo tablespace时会使用system表空间作为undo tablespace.

 

7.当上述UNDOTBS2全部offline后,删除原来的UNDO tablespace(如果不删除在备份时会报错)

SQL> drop tablespace UNDOTBS2 including contents and datafiles;

drop tablespace undotbs1 including contents and datafiles

*

ERROR at line 1:

ORA-01116: error in opening database file 2

ORA-01110: data file 2: '/opt/oracle/oradata/CMCCWAP/undotbs02.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

offline data file 2

SQL> alter database datafile '/opt/oracle/oradata/CMCCWAP/undotbs02.dbf' offline;

Database altered.

删除

SQL> drop tablespace undotbs1 including contents and datafiles;

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

上一篇: 没有了~
下一篇: Oracle连接数问题
请登录后发表评论 登录
全部评论

注册时间:2011-09-05

  • 博文量
    8
  • 访问量
    21334