undo 表空间损坏导致无法open
Jun 07, 2016 pm 05:31 PM在数据库undo表空间文件损坏,或者undo表空间文件缺失的情况,无法打开数据库。解决思路:新建一个undo表空间,然后将undo_table
undo 表空间损坏导致无法open
在数据库undo表空间文件损坏,,或者undo表空间文件缺失的情况,无法打开数据库。
这两种情况都可以视为一种情况处理,解决方法一样。
启动数据库时 出现:
SQL> startup
Oracle instance started.
Total System Global Area 709836800 bytes
Fixed Size 2231752 bytes
Variable Size 536871480 bytes
Database Buffers 167772160 bytes
Redo Buffers 2961408 bytes
Database mounted.
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-30012: undo tablespace 'UNDOTBS1' does not exist or of wrong type
Process ID: 4098
Session ID: 1 Serial number: 5
可以很明显的看到,由于undo表空间损坏或丢失,导致无法打开。
解决思路很简单:新建一个undo表空间,然后将undo_tablespace指向新的undo表空间。但是,但是。。。新建表空间只能在数据库open状态下才能进行。
所以。。
因为oracle system 表空间还有回滚段,因为我们先可以让oracle使用回滚段打开数据库,然后就可以新建undo表空间了
解决思路:
1、将数据库启动到mount
2、alter system set undo_management=manual scope=spfile;
3、shutdown immediate
4 、startup 然后新建一个undo表空间 new_undotbsxx
5、修改undo_tablespace参数指向new_undotbsxx
6、记得 将undo_manageme参数修改回来。 alter system set undo_management=auto scope=spfile;
SQL> alter system set undo_management=manual scope=spfile;
System altered.
然后打开数据库---
SQL> startup
ORACLE instance started.
Total System Global Area 709836800 bytes
Fixed Size 2231752 bytes
Variable Size 536871480 bytes
Database Buffers 167772160 bytes
Redo Buffers 2961408 bytes
Database mounted.
Database opened.
--已经顺利打开数据库
查看 --undo_management
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 900
undo_tablespace string UNDOTBS11
现在可以新建一个undo_tablespace 然后将undo_tablespace参数指向过去。
SQL> create undo tablespace undotbs11 datafile '/u01/app/oracle/oradata/orcl/undotbs11.dbf' size 100m;
Tablespace created.
--然后将undo_tablespace指向为刚刚新建的undo表空间
SQL> alter system set undo_tablespace=undotbs11 scope=spfile;
System altered.
--最后一定一定要记得把undo_management 给改回来
SQL> alter system set undo_management=auto scope=spfile;
System altered.
然后关闭数据库,再打开
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 709836800 bytes
Fixed Size 2231752 bytes
Variable Size 536871480 bytes
Database Buffers 167772160 bytes
Redo Buffers 2961408 bytes
Database mounted.
Database opened.
--没有问题,可以将以前损坏的undo表空间数据文件删除了
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
推荐阅读:
[Oracle] 在没有备份的情况下undo损坏怎么办?
UNDO表空间失败的处理方法
undo表空间故障和ORA-01548处理
RAC下丢失undo表空间的恢复
UNDO表空间备份恢复

Hot Article

Hot tools Tags

Hot Article

Hot Article Tags

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

How does Go language implement the addition, deletion, modification and query operations of the database?

Detailed tutorial on establishing a database connection using MySQLi in PHP

How does Hibernate implement polymorphic mapping?

iOS 18 adds a new 'Recovered' album function to retrieve lost or damaged photos

Analysis of the basic principles of MySQL database management system

An in-depth analysis of how HTML reads the database

Tips and practices for handling Chinese garbled characters in databases with PHP

How to use database callback functions in Golang?
