oracle设置undo表空间olap,UNDO表空间失败的处理方法

二、处理方法

经过实践，按照下面的方法，可以解决上述UNDO表空间异常的问题。

1、强制关闭数据库。

SQL> shutdown abort

Oracle instance shut down.

2、查看UNDO表空间参数，并修改UNDO表空间参数，关闭数据库。

$ sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on 木 3月 15 13:50:57 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

SQL> conn /as sysdba;

Connected.

SQL> show parameter undo;

NAME                                TYPE

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

VALUE

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

undo_management                      string

AUTO

undo_retention                      integer

900

undo_tablespace                      string

UNDOTBS

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

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

3、重启数据库，并确认修改后的UNDO表空间参数。

SQL> startup;

ORACLE instance started.

Total System Global Area 3874926592 bytes

Fixed Size                  2144080 bytes

Variable Size            1728055472 bytes

Database Buffers        2130706432 bytes

Redo Buffers              14020608 bytes

Database mounted.

Database opened.

SQL> show parameter undo;

NAME                                TYPE

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

VALUE

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

undo_management                      string

MANUAL

undo_retention                      integer

900

undo_tablespace                      string

UNDOTBS

4、删除UNDO表空间，创建新的UNDO表空间，并恢复UNDO表空间的参数信息，关闭数据库。

SQL> drop tablespace undotbs including contents and datafiles;

Tablespace dropped.

SQL> create undo tablespace undotbs datafile '/opt/oracle/product/11.1.0/db_1/oradata/DTCDB/undotbs01.dbf' size 1024m autoextend on next 10m maxsize unlimited;

Tablespace created.

SQL> alter system set undo_management=auto scope=spfile;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

5、重启数据库，确认UNDO表空间参数。

SQL> startup;

ORACLE instance started.

Total System Global Area 3874926592 bytes

Fixed Size                  2144080 bytes

Variable Size            1728055472 bytes

Database Buffers        2130706432 bytes

Redo Buffers              14020608 bytes

Database mounted.

Database opened.

SQL> show parameter undo

NAME                                TYPE

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

VALUE

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

undo_management                      string

AUTO

undo_retention                      integer

900

undo_tablespace                      string

UNDOTBS

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production

 With the Partitioning, OLAP, Data Mining and Real Application Testing options