oracle 11G undo表空間錯(cuò)誤

1.打開(kāi)數(shù)據(jù)庫(kù)時(shí)提示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表空間的數(shù)據(jù)文件
/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>

思路:根據(jù)啟動(dòng)oracle的錯(cuò)誤可以看出,錯(cuò)誤信息的undo表空間的名字與數(shù)據(jù)庫(kù)里面的名字不一至,此時(shí)要在init.orcl.ora文件中修改undo_tablespace的值,用pfile文件啟動(dòng)數(shù)據(jù)庫(kù).

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

[oracle@oracle ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 24 10:26:06 2016
Copyright (c) 1982, 2009, 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 bytes
Fixed Size            2217384 bytes
Variable Size          557845080 bytes
Database Buffers      213909504 bytes
Redo Buffers            2674688 bytes
Database 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文件創(chuàng)建spfile文件
SQL> shutdown immediate           --重啟數(shù)據(jù)庫(kù)
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
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.
Database opened.
SQL>