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

首页 > Linux操作系统 > Linux操作系统 > undo 文件损坏

undo 文件损坏

原创 Linux操作系统 作者：yobyin 时间：2013-02-19 10:50:50 0 删除 编辑
新年假期结束，由于公司断电，一台测试机器的undo 损坏。
现将undo 损坏修复的过程重现
实验1
SQL> startup;
ORACLE instance started.
Total System Global Area  296456192 bytes
Fixed Size                  2227784 bytes
Variable Size             109052344 bytes
Database Buffers          180355072 bytes
Redo Buffers                4820992 bytes
Database mounted.
Database opened.
SQL> select * from v$tablespace;
       TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
         0
SYSTEM
YES       NO        YES
         1
SYSAUX
YES       NO        YES
       TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
         2
UNDOTBS1
YES       NO        YES
         4
USERS
       TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
YES       NO        YES
         3
TEMP
NO        NO        YES
undo 文件
SQL> select file#,name,bytes/1024/1024 as size_m from v$datafile where ts#=2;
     FILE#
----------
NAME
--------------------------------------------------------------------------------
    SIZE_M
----------
         3
/opt/oracle/oradata/mos5200/undotbs01.dbf
        30

SQL> create table b as select * from dba_objects;
Table created.
SQL> delete b;
74510 rows deleted.
SQL> commit;
Commit complete.
模拟正常关闭的过程
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down
删除undo 文件
.
SQL> ! rm /opt/oracle/oradata/mos5200/undotbs01.dbf
SQL> startup;
ORACLE instance started.
Total System Global Area  296456192 bytes
Fixed Size                  2227784 bytes
Variable Size             109052344 bytes
Database Buffers          180355072 bytes
Redo Buffers                4820992 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file
ORA-01110: data file 3: '/opt/oracle/oradata/mos5200/undotbs01.dbf'

SQL> select status from v$instance;
STATUS
------------------------------------
MOUNTED
SQL>  alter database datafile 3 offline drop;
Database altered.
SQL> alter database open;
Database altered.
创建新的undo 文件
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/opt/oracle/oradata/mos5200/undotbs02.dbf' SIZE 10M AUTOEXTEND ON RETENTION NOGUARANTEE;
Tablespace created.
SQL> select * from v$tablespace;      
       TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
         0
SYSTEM
YES       NO        YES
         1
SYSAUX
YES       NO        YES
       TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
         2
UNDOTBS1
YES       NO        YES
         4
USERS
       TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
YES       NO        YES
         3
TEMP
NO        NO        YES
         5
       TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
UNDOTBS2
YES       NO        YES

6 rows selected.
修改 默认的undo 文件
SQL> ALTER SYSTEM SET UNDO_TABLESPACE='UNDOTBS2' scope=both;
System altered.
删除原来的undo 文件
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
SQL> create table c as select * from dba_objects;
Table created.
SQL> delete c;
74511 rows deleted.
SQL> commit;
Commit complete.
数据库正常打开
 
 
数据库crash的情况
SQL> select * from v$tablespace;
       TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
         0
SYSTEM
YES       NO        YES
         1
SYSAUX
YES       NO        YES
       TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
         4
USERS
YES       NO        YES
         3
TEMP
       TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
NO        NO        YES
         5
UNDOTBS2
YES       NO        YES

SQL> select file#,name,bytes/1024/1024 as size_m from v$datafile where ts#=5;
     FILE#
----------
NAME
--------------------------------------------------------------------------------
    SIZE_M
----------
         5
/opt/oracle/oradata/mos5200/undotbs02.dbf
        20
 
SQL> ! mv /opt/oracle/oradata/mos5200/undotbs02.dbf /opt/oracle/oradata/mos5200/undotbs02.dbfbak
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area  296456192 bytes
Fixed Size                  2227784 bytes
Variable Size             109052344 bytes
Database Buffers          180355072 bytes
Redo Buffers                4820992 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/opt/oracle/oradata/mos5200/undotbs02.dbf'

SQL> select status from v$instance;
STATUS
------------------------------------
MOUNTED
SQL> alter database datafile 5 offline;
alter database datafile 5 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

SQL>  alter database datafile 5 offline drop;
Database altered.
SQL> alter database open;
Database altered.
SQL> CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/opt/oracle/oradata/mos5200/undotbs1.dbf' SIZE 10M AUTOEXTEND ON RETENTION NOGUARANTEE;
Tablespace created.
SQL>  select * from v$tablespace;
       TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
         0
SYSTEM
YES       NO        YES
         1
SYSAUX
YES       NO        YES
       TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
         2
UNDOTBS1
YES       NO        YES
         4
USERS
       TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
YES       NO        YES
         3
TEMP
NO        NO        YES
         5
       TS#
----------
NAME
--------------------------------------------------------------------------------
INCLUDED_ BIGFILE   FLASHBACK ENCRYPT_I
--------- --------- --------- ---------
UNDOTBS2
YES       NO        YES

6 rows selected.
SQL> ALTER SYSTEM SET UNDO_TABLESPACE='UNDOTBS1' scope=both;
System altered.
SQL> drop tablespace undotbs2 including contents and datafiles;
drop tablespace undotbs2 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU11_3413854694$' found, terminate
dropping tablespace
 
SQL> select segment_name,status,tablespace_name from dba_rollback_segs;
SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
SYSTEM
ONLINE
SYSTEM
_SYSSMU10_1978125664$
ONLINE
UNDOTBS1
SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
_SYSSMU9_2456786981$
ONLINE
UNDOTBS1
_SYSSMU8_3126487489$
ONLINE
SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
UNDOTBS1
_SYSSMU7_400204927$
ONLINE
UNDOTBS1
_SYSSMU6_2414423743$
SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
ONLINE
UNDOTBS1
_SYSSMU5_1452140786$
ONLINE
UNDOTBS1

SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
_SYSSMU4_367532967$
ONLINE
UNDOTBS1
_SYSSMU3_3664551637$
ONLINE
UNDOTBS1
SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
_SYSSMU2_2288434006$
ONLINE
UNDOTBS1
_SYSSMU1_311825917$
ONLINE
SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
UNDOTBS1
_SYSSMU20_2437224713$
NEEDS RECOVERY
UNDOTBS2
_SYSSMU19_2890471014$
SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
NEEDS RECOVERY
UNDOTBS2
_SYSSMU18_307737647$
NEEDS RECOVERY
UNDOTBS2

SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
_SYSSMU17_1202841468$
NEEDS RECOVERY
UNDOTBS2
_SYSSMU16_1800710874$
NEEDS RECOVERY
UNDOTBS2
SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
_SYSSMU15_3867174456$
NEEDS RECOVERY
UNDOTBS2
_SYSSMU14_3835933986$
NEEDS RECOVERY
SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
UNDOTBS2
_SYSSMU13_3025482366$
NEEDS RECOVERY
UNDOTBS2
_SYSSMU12_2662135872$
SEGMENT_NAME
--------------------------------------------------------------------------------
STATUS
------------------------------------------------
TABLESPACE_NAME
--------------------------------------------------------------------------------
NEEDS RECOVERY
UNDOTBS2
_SYSSMU11_3413854694$
NEEDS RECOVERY
UNDOTBS2

21 rows selected.
SQL> set wrap off
SQL> set linesize 200
SQL> select segment_name,status from dba_rollback_segs;
SEGMENT_NAME                                                                               STATUS
------------------------------------------------------------------------------------------ ------------------------------------------------
SYSTEM                                                                                     ONLINE
_SYSSMU10_1978125664$                                                                      ONLINE
_SYSSMU9_2456786981$                                                                       ONLINE
_SYSSMU8_3126487489$                                                                       ONLINE
_SYSSMU7_400204927$                                                                        ONLINE
_SYSSMU6_2414423743$                                                                       ONLINE
_SYSSMU5_1452140786$                                                                       ONLINE
_SYSSMU4_367532967$                                                                        ONLINE
_SYSSMU3_3664551637$                                                                       ONLINE
_SYSSMU2_2288434006$                                                                       ONLINE
_SYSSMU1_311825917$                                                                        ONLINE
SEGMENT_NAME                                                                               STATUS
------------------------------------------------------------------------------------------ ------------------------------------------------
_SYSSMU20_2437224713$                                                                      NEEDS RECOVERY
_SYSSMU19_2890471014$                                                                      NEEDS RECOVERY
_SYSSMU18_307737647$                                                                       NEEDS RECOVERY
_SYSSMU17_1202841468$                                                                      NEEDS RECOVERY
_SYSSMU16_1800710874$                                                                      NEEDS RECOVERY
_SYSSMU15_3867174456$                                                                      NEEDS RECOVERY
_SYSSMU14_3835933986$                                                                      NEEDS RECOVERY
_SYSSMU13_3025482366$                                                                      NEEDS RECOVERY
_SYSSMU12_2662135872$                                                                      NEEDS RECOVERY
_SYSSMU11_3413854694$                                                                      NEEDS RECOVERY
21 rows selected.
SQL> select segment_name,status,tablespace_name from dba_rollback_segs;
SEGMENT_NAME                                                                               STATUS                                      TABLESPACE_NAME
------------------------------------------------------------------------------------------ ------------------------------------------------ ------------------------------------------------------------
SYSTEM                                                                                     ONLINE                                      SYSTEM
_SYSSMU10_1978125664$                                                                      ONLINE                                      UNDOTBS1
_SYSSMU9_2456786981$                                                                       ONLINE                                      UNDOTBS1
_SYSSMU8_3126487489$                                                                       ONLINE                                      UNDOTBS1
_SYSSMU7_400204927$                                                                        ONLINE                                      UNDOTBS1
_SYSSMU6_2414423743$                                                                       ONLINE                                      UNDOTBS1
_SYSSMU5_1452140786$                                                                       ONLINE                                      UNDOTBS1
_SYSSMU4_367532967$                                                                        ONLINE                                      UNDOTBS1
_SYSSMU3_3664551637$                                                                       ONLINE                                      UNDOTBS1
_SYSSMU2_2288434006$                                                                       ONLINE                                      UNDOTBS1
_SYSSMU1_311825917$                                                                        ONLINE                                      UNDOTBS1
SEGMENT_NAME                                                                               STATUS                                      TABLESPACE_NAME
------------------------------------------------------------------------------------------ ------------------------------------------------ ------------------------------------------------------------
_SYSSMU20_2437224713$                                                                      NEEDS RECOVERY                              UNDOTBS2
_SYSSMU19_2890471014$                                                                      NEEDS RECOVERY                              UNDOTBS2
_SYSSMU18_307737647$                                                                       NEEDS RECOVERY                              UNDOTBS2
_SYSSMU17_1202841468$                                                                      NEEDS RECOVERY                              UNDOTBS2
_SYSSMU16_1800710874$                                                                      NEEDS RECOVERY                              UNDOTBS2
_SYSSMU15_3867174456$                                                                      NEEDS RECOVERY                              UNDOTBS2
_SYSSMU14_3835933986$                                                                      NEEDS RECOVERY                              UNDOTBS2
_SYSSMU13_3025482366$                                                                      NEEDS RECOVERY                              UNDOTBS2
_SYSSMU12_2662135872$                                                                      NEEDS RECOVERY                              UNDOTBS2
_SYSSMU11_3413854694$                                                                      NEEDS RECOVERY                              UNDOTBS2
21 rows selected.
SQL> create pfile='/opt/oracle/pfile.ora' from spfile;

SQL> create pfile='/opt/oracle/pfile.ora' from spfile;
File created.
SQL> host
oracle@rac1:~> cd /opt/oracle/
oracle@rac1:/opt/oracle> ls
admin  cfgtoollogs  checkpoints  diag  oradata  oraInventory  pfile.ora  product
oracle@rac1:/opt/oracle> vi pfile.ora
mos5200.__db_cache_size=180355072
mos5200.__java_pool_size=4194304
mos5200.__large_pool_size=4194304
mos5200.__oracle_base='/opt/oracle'#ORACLE_BASE set from environment
mos5200.__pga_aggregate_target=113246208
mos5200.__sga_target=297795584
mos5200.__shared_io_pool_size=0
mos5200.__shared_pool_size=100663296
mos5200.__streams_pool_size=0
*.audit_file_dest='/opt/oracle/admin/mos5200/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/opt/oracle/oradata/mos5200/control01.ctl','/opt/oracle/oradata/mos5200/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='mos5200'
*.diagnostic_dest='/opt/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=mos5200XDB)'
*.open_cursors=300
*.pga_aggregate_target=113246208
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=294649856
*.undo_tablespace='UNDOTBS1'
~
 

-------

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

0
0
上一篇： oracle 10046 事件使用方法
下一篇： oracle 9i/10g安装包和PATCH下载地址汇总
请登录后发表评论 登录
全部评论
yobyin

注册时间：2009-03-31

  • 博文量
    45
  • 访问量
    116952

最新文章

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

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

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

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