ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > Linux操作系统 > Linux操作系统 > undo表空间故障恢复，无备份

undo表空间故障恢复，无备份

原创 Linux操作系统 作者：aaqwsh 时间：2011-04-24 16:15:33 0 删除 编辑
undo表空间故障恢复，无备份：
SQL> select count(*) from testundo;
  count(*)
----------
       199
SQL> select * from v$recover_file;
no rows selected
SQL>
SQL> insert into testundo select * from testundo where rownum <3001;
199 rows created.
SQL> /
398 rows created.
SQL> select segment_name,status,tablespace_name from dba_rollback_segs;

SEGMENT_NAME                   STATUS           TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM                         ONLINE           SYSTEM
_SYSSMU11$                     ONLINE           UNDOTBS2
_SYSSMU12$                     ONLINE           UNDOTBS2
_SYSSMU13$                     ONLINE           UNDOTBS2
_SYSSMU14$                     ONLINE           UNDOTBS2
_SYSSMU15$                     ONLINE           UNDOTBS2
_SYSSMU16$                     ONLINE           UNDOTBS2
_SYSSMU17$                     ONLINE           UNDOTBS2
_SYSSMU18$                     ONLINE           UNDOTBS2
_SYSSMU19$                     ONLINE           UNDOTBS2
_SYSSMU20$                     ONLINE           UNDOTBS2
11 rows selected.
SQL> SQL>
SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2
SQL>
SQL>
SQL> shutdown abort
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
 
删除undo02.DBF
 
[oracle@node1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Sun Apr 24 15:47:52 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 4 - see DBWR trace file
ORA-01110: data file 4: '/data/ora10g/RACDBSTD/undo02.DBF'

SQL> alter database datafile 4 offline drop;
Database altered.
SQL> select name,status from v$datafile where file#=4;
NAME
--------------------------------------------------------------------------------
STATUS
-------
/data/ora10g/RACDBSTD/undo02.DBF
RECOVER

SQL> alter database open;
Database altered.
SQL> select name,status from v$datafile where file#=4;
NAME
--------------------------------------------------------------------------------
STATUS
-------
/data/ora10g/RACDBSTD/undo02.DBF
RECOVER

SQL> show parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS2
SQL>
SQL> create undo tablespace undotbs1 datafile '/data/ora10g/RACDBSTD/undo01.DBF' size 50m ;
Tablespace created.
SQL> alter system set undo_tablespace='undotbs1' scope=spfile;

System altered.
SQL> SQL>
SQL>
SQL> select segment_name,status,tablespace_name from dba_rollback_segs;
SEGMENT_NAME                   STATUS           TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM                         ONLINE           SYSTEM
_SYSSMU1$                      OFFLINE          UNDOTBS1
_SYSSMU2$                      OFFLINE          UNDOTBS1
_SYSSMU3$                      OFFLINE          UNDOTBS1
_SYSSMU4$                      OFFLINE          UNDOTBS1
_SYSSMU5$                      OFFLINE          UNDOTBS1
_SYSSMU6$                      OFFLINE          UNDOTBS1
_SYSSMU7$                      OFFLINE          UNDOTBS1
_SYSSMU8$                      OFFLINE          UNDOTBS1
_SYSSMU9$                      OFFLINE          UNDOTBS1
_SYSSMU10$                     OFFLINE          UNDOTBS1
SEGMENT_NAME                   STATUS           TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU11$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU12$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU13$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU14$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU15$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU16$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU17$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU18$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU19$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU20$                     NEEDS RECOVERY   UNDOTBS2
21 rows selected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

-- initRACDBSTD.ora
*._corrupted_rollback_segments=(_SYSSMU11$,_SYSSMU12$,_SYSSMU13$,_SYSSMU14$,_SYSSMU15$,_SYSSMU16$,_SYSSMU17$,_SYSSMU18$,_SYSSMU19$,_SYSSMU20$)

SQL> startup
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> select segment_name,status,tablespace_name from dba_rollback_segs;
SEGMENT_NAME                   STATUS           TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM                         ONLINE           SYSTEM
_SYSSMU1$                      ONLINE           UNDOTBS1
_SYSSMU2$                      ONLINE           UNDOTBS1
_SYSSMU3$                      ONLINE           UNDOTBS1
_SYSSMU4$                      ONLINE           UNDOTBS1
_SYSSMU5$                      ONLINE           UNDOTBS1
_SYSSMU6$                      ONLINE           UNDOTBS1
_SYSSMU7$                      ONLINE           UNDOTBS1
_SYSSMU8$                      ONLINE           UNDOTBS1
_SYSSMU9$                      ONLINE           UNDOTBS1
_SYSSMU10$                     ONLINE           UNDOTBS1
SEGMENT_NAME                   STATUS           TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU11$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU12$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU13$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU14$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU15$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU16$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU17$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU18$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU19$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU20$                     NEEDS RECOVERY   UNDOTBS2
21 rows selected.
SQL> drop tablespace UNDOTBS2 including contents and datafiles;
Tablespace dropped.
SQL> select segment_name,status,tablespace_name from dba_rollback_segs;
SEGMENT_NAME                   STATUS           TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM                         ONLINE           SYSTEM
_SYSSMU1$                      ONLINE           UNDOTBS1
_SYSSMU2$                      ONLINE           UNDOTBS1
_SYSSMU3$                      ONLINE           UNDOTBS1
_SYSSMU4$                      ONLINE           UNDOTBS1
_SYSSMU5$                      ONLINE           UNDOTBS1
_SYSSMU6$                      ONLINE           UNDOTBS1
_SYSSMU7$                      ONLINE           UNDOTBS1
_SYSSMU8$                      ONLINE           UNDOTBS1
_SYSSMU9$                      ONLINE           UNDOTBS1
_SYSSMU10$                     ONLINE           UNDOTBS1
11 rows selected.
SQL> select count(*) from testundo;
  count(*)
----------
       796
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed Size                  1218316 bytes
Variable Size              62916852 bytes
Database Buffers          100663296 bytes
Redo Buffers                2973696 bytes
Database mounted.
Database opened.
SQL> select segment_name,status,tablespace_name from dba_rollback_segs;
SEGMENT_NAME                   STATUS           TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM                         ONLINE           SYSTEM
_SYSSMU1$                      ONLINE           UNDOTBS1
_SYSSMU2$                      ONLINE           UNDOTBS1
_SYSSMU3$                      ONLINE           UNDOTBS1
_SYSSMU4$                      ONLINE           UNDOTBS1
_SYSSMU5$                      ONLINE           UNDOTBS1
_SYSSMU6$                      ONLINE           UNDOTBS1
_SYSSMU7$                      ONLINE           UNDOTBS1
_SYSSMU8$                      ONLINE           UNDOTBS1
_SYSSMU9$                      ONLINE           UNDOTBS1
_SYSSMU10$                     ONLINE           UNDOTBS1
11 rows selected.
SQL> select count(*) from testundo;
  count(*)
----------
       796
SQL>
SQL> exit
这里用隐含参数强制打开数据库，破坏了数据库的完整性，为了保证数据的安全，最好exp/imp.但如果数据和那庞大，又不允许停机，那就
用analyze table tablename validate structure cascade验证，其实即使是imp/exp也不能保证数据的一致性，因为我们已经把故障回滚段里的数据忽略了，imp/exp也只能尽量保证各种检查点scn一致。

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/758322/viewspace-693308/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： undo表空间故障恢复
下一篇： 事物的基本流程
请登录后发表评论 登录
全部评论
aaqwsh

注册时间：2010-11-24

  • 博文量
    132
  • 访问量
    287982

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593