怎么恢复损坏后的undo 表空间（一）

最新推荐文章于 2024-07-04 14:47:25 发布

原创最新推荐文章于 2024-07-04 14:47:25 发布 · 1.7k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#undo表空间损坏恢复方法 #ORA-01157 ORA-01110 #undo

ORACLE 之 error 诊断同时被 3 个专栏收录

35 篇文章

订阅专栏

ORACLE 之备份与恢复

21 篇文章

订阅专栏

ORACLE 之启动过程

8 篇文章

订阅专栏

本文介绍了一种在Oracle数据库中遇到undo表空间损坏的情况下的恢复步骤。该数据库运行在非归档模式下，通过离线并删除损坏的undo表空间文件，创建新的undo表空间来解决此问题。

怎么恢复损坏后的undo 表空间（一）

今天是2013-08-30，目前有这么一种情况。
数据库运行在非归档模式下，在关闭数据库的时候采用正常关闭。打开数据库之后提示undo表空间损坏。信息如下：

对于这种情况进行的恢复步骤如下：
[oracle@oracle-one RHYS]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 14 21:12:09 2013

Connected to an idle instance.

SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 405020672 bytes
Fixed Size                  2213816 bytes
Variable Size             251660360 bytes
Database Buffers          146800640 bytes
Redo Buffers                4345856 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/app/oracle/RHYS/undotbs01.dbf'

SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

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
[oracle@oracle-one RHYS]$ adrci

ADRCI: Release 11.2.0.1.0 - Production on Wed Aug 14 21:15:48 2013

ADR base = "/opt/app/oracle"
adrci> show homepath
ADR Homes:
diag/tnslsnr/oracle-one/listener
diag/rdbms/rhys/RHYS
adrci> set homepath
DIA-48431: Must specify at least one ADR home path

adrci> set homepath diag/rdbms/rhys/RHYS
adrci> show alert -tail 20
2013-08-14 21:12:13.533000 +08:00
PMON started with pid=2, OS id=3650
VKTM started with pid=3, OS id=3652 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
GEN0 started with pid=4, OS id=3656
DIAG started with pid=5, OS id=3658
DBRM started with pid=6, OS id=3660
PSP0 started with pid=7, OS id=3662
DIA0 started with pid=8, OS id=3664
MMAN started with pid=9, OS id=3666
DBW0 started with pid=10, OS id=3668
LGWR started with pid=11, OS id=3670
CKPT started with pid=12, OS id=3672
SMON started with pid=13, OS id=3674
RECO started with pid=14, OS id=3676
MMON started with pid=15, OS id=3678
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
MMNL started with pid=16, OS id=3680
starting up 1 shared server(s) ...
ORACLE_BASE from environment = /opt/app/oracle
ALTER DATABASE MOUNT
2013-08-14 21:12:19.026000 +08:00
Successful mount of redo thread 1, with mount id 2735871469
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: ALTER DATABASE MOUNT
ALTER DATABASE OPEN
Errors in file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_dbw0_3668.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/app/oracle/RHYS/undotbs01.dbf'
ORA-27046: file size is not a multiple of logical block size
Additional information: 1
Errors in file /opt/app/oracle/diag/rdbms/rhys/RHYS/trace/RHYS_ora_3691.trc:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/app/oracle/RHYS/undotbs01.dbf'
ORA-1157 signalled during: ALTER DATABASE OPEN...
2013-08-14 21:12:20.498000 +08:00
Checker run found 1 new persistent data failures
adrci> exit
[oracle@oracle-one RHYS]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 14 21:16:48 2013

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database datafile '/opt/app/oracle/RHYS/undotbs01.dbf' offline drop;

Database altered.

SQL> alter database open;

Database altered.

SQL> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_optimizer_undo_cost_change          string      11.2.0.1
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS1

SQL> create undo tablespace undotbs2 datafile '/opt/app/oracle/RHYS/undotbs2.dbf' size 50M autoextend on next 5M maxsize 200M;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2;

System altered.

SQL> select usn,status from v$rollstat;

       USN STATUS
---------- ---------------
         0 ONLINE
        11 ONLINE
        12 ONLINE
        13 ONLINE
        14 ONLINE
        15 ONLINE
        16 ONLINE
        17 ONLINE
        18 ONLINE
        19 ONLINE
        20 ONLINE

11 rows selected.

SQL> col name for a60
SQL> set linesize 200
SQL> select file#,status from v$datafile;

     FILE# STATUS
---------- -------
         1 SYSTEM
         2 ONLINE
         3 OFFLINE
         4 ONLINE
         5 ONLINE
         6 ONLINE

6 rows selected.

SQL> select file#,status,name from v$datafile;

     FILE# STATUS NAME
---------- ------- ------------------------------------------------------------
         1 SYSTEM /opt/app/oracle/RHYS/system01.dbf
         2 ONLINE /opt/app/oracle/RHYS/sysaux01.dbf
         3 OFFLINE /opt/app/oracle/RHYS/undotbs01.dbf
         4 ONLINE /opt/app/oracle/RHYS/users01.dbf
         5 ONLINE /opt/app/oracle/RHYS/test.dbf
         6 ONLINE /opt/app/oracle/RHYS/undotbs2.dbf

6 rows selected.

SQL> drop tablespace undotbs1 including contengs and datafiles;
drop tablespace undotbs1 including contengs and datafiles
*
ERROR at line 1:
ORA-01911: CONTENTS keyword expected

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL> select file#,status,name from v$datafile;

     FILE# STATUS NAME
---------- ------- ------------------------------------------------------------
         1 SYSTEM /opt/app/oracle/RHYS/system01.dbf
         2 ONLINE /opt/app/oracle/RHYS/sysaux01.dbf
         4 ONLINE /opt/app/oracle/RHYS/users01.dbf
         5 ONLINE /opt/app/oracle/RHYS/test.dbf
         6 ONLINE /opt/app/oracle/RHYS/undotbs2.dbf

SQL>