Oracle Undo table space is not available

　　

Due to a careless operation, there was no success in switching table space, but because the undo_management value of parameter undo was changed to Manual, no error was reported when starting the database, but inserting the data times to the table was wrong and the rollback segment was not available. The reason for the error is then queried.

　　

1 First look at the undo information in the database

　　

Sql> show parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Undo_management string MANUAL
Undo_retention integer 10800
Undo_tablespace string UNDOTBS2

Undo Tablespace is UNDOTBS2, which has actually been removed, but the database can start normally without error due to undo_management manual.

2 Viewing the database undo data file

Select File_name,tablespace_name,online_status
From Dba_data_files
where tablespace_name like '%undo% ';

file_name Tablespace_name Online_status
1/HOME/ORACLE/ORADATA/UNDOTBS1.DBFUNDOTBS1 ONLINE

There is only one named UNDOTBS1 undo tablespace in the database, and it is clear that the undo configuration information in the system is incorrect.

3 Restart the database to mount state to modify the undo configuration information

sql> startup Mount;
ORACLE instance started.

Total System Global area 450953216 bytes
Fixed Size 2214256 bytes
Variable Size 348128912 bytes
Database buffers 96468992 bytes
Redo buffers 4141056 bytes
Database mounted.

Sql> alter system set undo_management= ' AUTO ' scope=spfile;

System altered.

Restart the database to view the undo configuration information in the Mount state

Sql> Show Parameter Undo

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Undo_management string AUTO
Undo_retention Integer 900
Undo_tablespace string UNDOTBS2
Sql> Show parameter undo_tablespace;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Undo_tablespace string UNDOTBS2

　　

Then modify the Undo_tablespace

Sql> alter system set UNDO_TABLESPACE=UNDOTBS1;
Alter system set UNDO_TABLESPACE=UNDOTBS1
*
ERROR at line 1:
Ora-02097:parameter cannot be modified because specified value is invalid
Ora-01219:database not open:queries allowed on fixed tables/views only

Database is not open, so open the database

sql> ALTER DATABASE open;
ALTER DATABASE Open
*
ERROR at line 1:
Ora-01092:oracle instance terminated. Disconnection forced
Ora-30012:undo tablespace ' UNDOTBS2 ' does not exist or of wrong type
Process id:72674
Session id:1 Serial Number:3

Again encountered a new error, should be modified undo_management for auto and then start to detect the corresponding undo_tablespace, but UNDO_TABLESPACE:UNDOTBS2 does not exist, so caused a series of problems.

Based on previous experience prepare to close the database and then view spfile about the undo tablespace configuration is correct, the correct use of SPFile boot to Mount state, incorrect then use SPFile mode to boot to mount state, the problem of shutting down the database again.

sql> shutdown abort;
Ora-24324:service Handle not initialized
Ora-01041:internal error. Hostdef extension doesn ' t exist
sql> shutdown immediate;
Ora-24324:service Handle not initialized
Ora-01041:internal error. Hostdef extension doesn ' t exist
Sql> show parameter undo;
ERROR:
Ora-03114:not connected to ORACLE

Regardless of which way to close, no, and can not query parameter information, the database should be in an abnormal state, so Baidu query solution:

First exit Sqlplus and then set, specify Oracle_sid, and then provide the instance information to solve the problem.

Sql> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
With the partitioning, OLAP, Data Mining and Real application testing options

[[Email protected] ~]$ set ORACLE_SID=ORCL
[Email protected] ~]$ Sqlplus

sql> shutdown abort;
ORACLE instance shut down.

Enter Sqlplus to close the database and then check the corresponding Spfile,/usr/oracle/app/admin/orcl/pfile/init.ora.95201623739,vim Go in. To view the configuration of the undo information if it is not properly modified, the main thing is that Undo_tablespace modifies the undo tablespace that is available for the database and is as follows:

Undo_management=auto
undo_retention=10800
Undo_tablespace=undotbs1
"init.ora.95201623739" 58L, 1877C

Modified with SPFile boot to mount state

sql> startup Mount pfile=/usr/oracle/app/admin/orcl/pfile/init.ora.95201623739;
ORACLE instance started.

The database starts successfully and the undo configuration information is viewed. That's right.

Sql> show parameter undo;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Undo_management string AUTO
Undo_retention integer 10800
Undo_tablespace string UNDOTBS1

Then start the database into the open state, you can insert data into the table, no longer report the rollback is not available error.

With regard to ORA-24324 and ORA-01041 errors, the general solution is as follows

Restart the listener;
Restart Sqlplus;
Log server local restart database;

In summary, when modifying the undo configuration information, the undo_management is modified to auto, but Undo_tablespace is not modified together, causing the system to configure the corresponding undo according to the value of Undo_tablespace after reboot. Tablespace, but the undo tablespace that corresponds to this value does not actually exist, so the startup process occurs unexpectedly. Undo_management and Undo_tablespace should be modified together.

　　

Oracle Undo table space is not available