oracle 11G undo表空间错误

1.打开数据库时提示undo表空间不存在

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'UNDOTBS01' does not exist or of wrong type
Process ID: 3236
Session ID: 1 Serial number: 5

解决方法:

[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 24 10:22:24 2016
Copyright (c) 1982,2009,Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup mount
ORACLE instance started.

Total System Global Area 776646656 bytes
Fixed Size 2217384 bytes
Variable Size 557845080 bytes
Database Buffers 213909504 bytes
Redo Buffers 2674688 bytes
Database mounted.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/home/oracle/app/oradata/orcl/system01.dbf
/home/oracle/app/oradata/orcl/sysaux01.dbf
/home/oracle/app/oradata/orcl/undotbs01.dbf --undo表空间的数据文件
/home/oracle/app/oradata/orcl/users01.dbf
/home/oracle/app/oradata/orcl/tong.dbf

SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1 --undo表空间的名字
SQL> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1 --undo表空间的名字
USERS
TEMP
TONG1
6 rows selected.
SQL>

思路:根据启动oracle的错误可以看出,错误信息的undo表空间的名字与数据库里面的名字不一至,此时要在init.orcl.ora文件中修改undo_tablespace的值,用pfile文件启动数据库.

[oracle@oracle ~]$ cd $ORACLE_HOME/dbs
[oracle@oracle dbs]$ vim initorcl.ora
*.undo_tablespace='UNDOTBS1' --修改这行的值

[oracle@oracle ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 24 10:26:06 2016Copyright (c) 1982,Oracle. All rights reserved.Connected to an idle instance.SQL> startup mount pfile='/home/oracle/app/product/11.2.0/dbhome_1/dbs/initorcl.ora'ORACLE instance started.Total System Global Area 776646656 bytesFixed Size 2217384 bytesVariable Size 557845080 bytesDatabase Buffers 213909504 bytesRedo Buffers 2674688 bytesDatabase mounted.SQL> alter database open;Database altered.SQL> create spfile='/home/oracle/app/product/11.2.0/dbhome_1/dbs/spfileorcl.ora' from pfile;File created. --pfile文件创建spfile文件SQL> shutdown immediate --重启数据库Database closed.Database dismounted.ORACLE instance shut down.SQL> startupORACLE instance started.Total System Global Area 776646656 bytesFixed Size 2217384 bytesVariable Size 557845080 bytesDatabase Buffers 213909504 bytesRedo Buffers 2674688 bytesDatabase mounted.Database opened.SQL>

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!