怎么恢复损坏后的undo 表空间(一)
来源:互联网 发布:傲剑紫霞升级数据大全 编辑:程序博客网 时间:2024/05/01 23:53
怎么恢复损坏后的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
Copyright (c) 1982, 2009, Oracle. All rights reserved.
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
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
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
Copyright (c) 1982, 2009, Oracle. All rights reserved.
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>
- 怎么恢复损坏后的undo 表空间(一)
- undo表 空间损坏恢复方法
- UNDO表空间备份恢复(一)
- 直接删除undo及temp表空间文件后的数据库恢复一例
- undo表空间损坏
- 丢失undo表空间后恢复
- Oracle undo表空间损坏的修复
- Oracle undo表空间损坏的修复
- undo表空间故障特殊恢复(一)
- 【undo表空间的丢失-恢复-1】
- UNDO表空间备份恢复(三)
- UNDO表空间备份恢复(四)
- UNDO表空间备份恢复(二)
- 索引表空间损坏的恢复
- undo表空间故障恢复
- rman 恢复undo表空间
- UNDO表空间数据文件损坏处理方法
- undo 表空间损坏导致无法open
- hadoop学习过程-2013.08.30.1--按照网页内容搜索出网页01--确定网页来源
- 上班八小时的习惯
- 修正信号量
- Devexpress XtraReports报表教程
- 取石子游戏(博弈类)
- 怎么恢复损坏后的undo 表空间(一)
- 使用润乾报表的心得
- Java Se 程序运用整合归纳
- createserver(oracle)
- AES加解密(java)
- poj_2155 Matrix
- 对类 和 对象 的理解
- NGUI的使用教程与实例
- 位运算简介及实用技巧(三):进阶篇(2)