Shulou(Shulou.com)06/01 Report--

1. Prompt that undo tablespace does not exist when opening the database

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

Solution:

[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-data file for undo tablespace

/ 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-the name of the undo tablespace

SQL > select name from v$tablespace

NAME

-

SYSTEM

SYSAUX

UNDOTBS1-the name of the undo tablespace

USERS

TEMP

TONG1

6 rows selected.

SQL >

Idea: according to the error of starting oracle, we can see that the name of the undo table space of the error message is different from the name in the database. At this time, we should modify the value of undo_tablespace in the init.orcl.ora file and start the database with the pfile file.

[oracle@oracle ~] $cd $ORACLE_HOME/dbs

[oracle@oracle dbs] $vim initorcl.ora

* .undo_tablespace='UNDOTBS1'-- modify the value of this line

[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 file to create spfile file

SQL > shutdown immediate-- restart the database

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 >

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.