【UNDO】UNDO表空间损坏的修复

一、数据库正常关闭immediate或normal

1.查询UNDO表空间情况： 

[oracle@oraclecx ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Sep 4 14:34:17 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


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


SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         1 UNDOTBS1                       YES NO  YES
         2 SYSAUX                         YES NO  YES
         3 TEMP                           NO  NO  YES
         4 USERS                          YES NO  YES

SQL> select file#,name,bytes/1024/1024 as size_m from v$datafile where ts#=1;

     FILE# NAME                               SIZE_M
---------- ------------------------------ ----------
         2 /oradata/nms/undotbs01.dbf            445

2.正常关闭数据库

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

3.模拟数据库损坏

[oracle@oraclecx nms]$ mv undotbs01.dbf undotbs01.dbf.bak

4.启动数据库

SQL> startup

ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             385876488 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14680064 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/oradata/nms/undotbs01.dbf'


SQL> select status from v$instance;

STATUS
------------
MOUNTED

5.删除有问题的数据文件后，才可以打开数据库

SQL> alter database datafile 2 offline;

alter database datafile 2 offline
*
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

SQL> alter database datafile 2 offline drop;

Database altered.

SQL> alter database open;

Database altered.

6.创建新的UNDO表空间：

SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/oradata/nms/undotbs02.dbf' SIZE 10M AUTOEXTEND ON RETENTION NOGUARANTEE;

Tablespace created.

SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         1 UNDOTBS1                       YES NO  YES
         2 SYSAUX                         YES NO  YES
         3 TEMP                           NO  NO  YES
         4 USERS                          YES NO  YES
         5 UNDOTBS2                       YES NO  YES

6 rows selected.

7.修改参数文件中UNDO表空间的设置：

SQL> ALTER SYSTEM SET UNDO_TABLESPACE='UNDOTBS2' scope=both; 

System altered.

8.删除旧的UNDO表空间:

SQL> drop tablespace undotbs1 including contents and datafiles;

Tablespace dropped.

SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         2 SYSAUX                         YES NO  YES
         3 TEMP                           NO  NO  YES
         4 USERS                          YES NO  YES
         5 UNDOTBS2                       YES NO  YES

至此完成修复

二、 数据库异常关闭 abort或crash

情况1：没有使用回归段：

1.查询UNDO表空间情况： 

[oracle@oraclecx ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Sep 4 14:34:17 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


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


SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         2 SYSAUX                         YES NO  YES
         3 TEMP                           NO  NO  YES
         4 USERS                          YES 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 /oradata/nms/undotbs02.dbf             10

2.模拟数据库损坏，异常关闭数据库

[root@oraclecx nms]# rm -rf undotbs02.dbf 

SQL> shutdown abort
ORACLE instance shut down.

3.启动数据库

SQL> startup

ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             385876488 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14680064 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 5 - see DBWR trace file
ORA-01110: data file 5: '/oradata/nms/undotbs02.dbf'

SQL> select status from v$instance;

STATUS
------------
MOUNTED

4.删除有问题的数据文件后，才可以打开数据库

SQL> alter database datafile 5 offline;

Database altered.

SQL> alter database open;

Database altered.

5.创建新的UNDO表空间：

SQL> CREATE UNDO TABLESPACE UNDOTBS1 DATAFILE '/oradata/nms/undotbs01.dbf' SIZE 10M AUTOEXTEND ON RETENTION NOGUARANTEE;

Tablespace created.

SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         1 UNDOTBS1                       YES NO  YES
         2 SYSAUX                         YES NO  YES
         3 TEMP                           NO  NO  YES
         4 USERS                          YES NO  YES
         5 UNDOTBS2                       YES NO  YES

6 rows selected.

6.修改参数文件中UNDO表空间的设置：

SQL> ALTER SYSTEM SET UNDO_TABLESPACE='UNDOTBS1' scope=both; 

System altered.

7.删除旧的UNDO表空间:

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$' found, terminate dropping tablespace

发现无法删除。

8.查看 rollback segments 信息

方式一：

SQL> select segment_name,status,tablespace_name from dba_rollback_segs;

SEGMENT_NAME                   STATUS           TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
SYSTEM                         ONLINE           SYSTEM
_SYSSMU10$                     ONLINE           UNDOTBS1
_SYSSMU9$                      ONLINE           UNDOTBS1
_SYSSMU8$                      ONLINE           UNDOTBS1
_SYSSMU7$                      ONLINE           UNDOTBS1
_SYSSMU6$                      ONLINE           UNDOTBS1
_SYSSMU5$                      ONLINE           UNDOTBS1
_SYSSMU4$                      ONLINE           UNDOTBS1
_SYSSMU3$                      ONLINE           UNDOTBS1
_SYSSMU2$                      ONLINE           UNDOTBS1
_SYSSMU1$                      ONLINE           UNDOTBS1

SEGMENT_NAME                   STATUS           TABLESPACE_NAME
------------------------------ ---------------- ------------------------------
_SYSSMU20$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU19$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU18$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU17$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU16$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU15$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU14$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU13$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU12$                     NEEDS RECOVERY   UNDOTBS2
_SYSSMU11$                     NEEDS RECOVERY   UNDOTBS2

方式二：

root@oraclecx dbs]# strings /oradata/nms/system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u
              and substr(drs.segment_name,1,7) != '_SYSSMU' 
a       _SYSSMU1
a       _SYSSMU2
a       _SYSSMU3
a       _SYSSMU4
a       _SYSSMU5
a       _SYSSMU6
a       _SYSSMU7
a       _SYSSMU8
a       _SYSSMU9
KD'              and substr(drs.segment_name,1,7) != ''_SYSSMU'' ' );
_SYSSMU1
        _SYSSMU1
_SYSSMU10
_SYSSMU11
_SYSSMU12
_SYSSMU13
_SYSSMU14
_SYSSMU15
_SYSSMU16
_SYSSMU17
_SYSSMU18
_SYSSMU19
_SYSSMU2
        _SYSSMU2
_SYSSMU20
_SYSSMU3
        _SYSSMU3
_SYSSMU4
        _SYSSMU4
_SYSSMU5
        _SYSSMU5
_SYSSMU6
        _SYSSMU6
_SYSSMU7
        _SYSSMU7
_SYSSMU8
        _SYSSMU8
                _SYSSMU8
_SYSSMU9
        _SYSSMU9
SYSTEM  _SYSSMU9

9.修改隐藏参数:

SQL> create pfile from spfile;

File created.

[root@oraclecx dbs]# vi initnms.ora 

nms.__db_cache_size=1207959552
nms.__java_pool_size=16777216
nms.__large_pool_size=16777216
nms.__shared_pool_size=352321536
nms.__streams_pool_size=0
*.audit_file_dest='/oracle/admin/nms/adump'
*.background_dump_dest='/oracle/admin/nms/bdump'
*.compatible='10.2.0.5.0'
*.control_files='/oradata/nms/control01.ctl','/oradata/nms/control02.ctl','/oradata/nms/control03.ctl'
*.core_dump_dest='/oracle/admin/nms/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='nms'
*.db_recovery_file_dest='/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=nmsXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=826277888
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1610612736
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/nms/udump'
*._CORRUPTED_ROLLBACK_SEGMENTS =(_SYSSMU11$,_SYSSMU12$,_SYSSMU13$,_SYSSMU14$,_SYSSMU15$,_SYSSMU16$,_SYSSMU17$,_SYSSMU18$,_SYSSMU19$,_SYSSMU20$)

注：修改启动参数initorcl.ora

*._CORRUPTED_ROLLBACK_SEGMENTS =(_SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$)
或*._offline_rollback_segments=”_SYSSMU1$”,”_SYSSMU2$”,”_SYSSMU3$”,”_SYSSMU4$”,”_SYSSMU5$”,”_SYSSMU6$”,”_SYSSMU7$”,”_SYSSMU8$”,”_SYSSMU9$”,”_SYSSMU10$”

如果上面是配置的offline_rollback,就需要多做一下sql>drop rollback segment _SYSSMU[N]$;…

10.使用pfile启动数据库

SQL> shutdown immediate

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup pfile='/oracle/product/10.2.0.5/db_1/dbs/initnms.ora'
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             385876488 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14680064 bytes
Database mounted.
Database opened.
SQL> 

11.删除有问题的临时表空间：

SQL> drop tablespace undotbs2 including contents and datafiles;

Tablespace dropped.

SQL> select * from v$tablespace;

       TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
         0 SYSTEM                         YES NO  YES
         1 UNDOTBS1                       YES NO  YES
         2 SYSAUX                         YES NO  YES
         3 TEMP                           NO  NO  YES
         4 USERS                          YES NO  YES

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup

ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             385876488 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14680064 bytes
Database mounted.
Database opened.
SQL> 

至此完成修复。

情况2：使用回归段：

场景，模拟一个session模拟业务正在进行，有更新发生，但没有提交

在第一个链接中，新建测试表，插入数据，不提交：

SQL> create tablespace ultranms datafile '/oradata/nms/ultranms01.dbf' size 10M autoextend on ;

Tablespace created.

SQL> create user ultranms identified by ultranms default tablespace ultranms;

User created.

SQL> grant connect,resource,create table,create session to ultranms;

Grant succeeded.

SQL> conn ultranms;

Enter password:
Connected.

SQL> create table test as select * from all_objects  where 1=2   ;

Table created.

SQL> insert into test  select * from all_objects;

40702 rows created.

SQL>commit;

SQL> insert into test  select * from test;

40702 rows created.

在第二个链接中，查询测试表，abort数据库：

SQL> select * from test;

no rows selected

SQL> shutdown abort
ORACLE instance shut down.
SQL> exit

删除undo表空间

启动数据库

[oracle@oraclecx ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Sep 5 14:29:03 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             385876488 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14680064 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/oradata/nms/undotbs01.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;
alter database open
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced


SQL> 

查看alert日志

ALTER DATABASE OPEN

Wed Sep 05 15:31:32 CST 2012
Errors in file /oracle/admin/nms/bdump/nms_dbw0_14789.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: '/oradata/nms/undotbs01.dbf'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-1157 signalled during: ALTER DATABASE OPEN...

创建pfile

[oracle@oraclecx ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Sep 5 08:17:31 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> create pfile from spfile;

File created.

SQL> 

在UNIX下可以通过检查system表空间文件来查找回滚段，注意查找出的内容最后要加上$符号

[oracle@oraclecx dbs]$ strings /oradata/nms/system01.dbf | grep _SYSSMU | cut -d $ -f 1 | sort -u

              and substr(drs.segment_name,1,7) != '_SYSSMU'
KD'              and substr(drs.segment_name,1,7) != ''_SYSSMU'' ' );
_SYSSMU1
        _SYSSMU1
_SYSSMU10
_SYSSMU2
        _SYSSMU2
_SYSSMU3
        _SYSSMU3
_SYSSMU4
        _SYSSMU4
_SYSSMU5
        _SYSSMU5
_SYSSMU6
        _SYSSMU6
_SYSSMU7
        _SYSSMU7
_SYSSMU8
        _SYSSMU8
                _SYSSMU8
_SYSSMU9
        _SYSSMU9
SYSTEM  _SYSSMU9

修改pfile中的回滚段隐含参数

[root@oraclecx dbs]# vi initnms.ora 

nms.__db_cache_size=1207959552
nms.__java_pool_size=16777216
nms.__large_pool_size=16777216
nms.__shared_pool_size=352321536
nms.__streams_pool_size=0
*.audit_file_dest='/oracle/admin/nms/adump'
*.background_dump_dest='/oracle/admin/nms/bdump'
*.compatible='10.2.0.5.0'
*.control_files='/oradata/nms/control01.ctl','/oradata/nms/control02.ctl','/oradata/nms/control03.ctl'
*.core_dump_dest='/oracle/admin/nms/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='nms'
*.db_recovery_file_dest='/oracle/flash_recovery_area'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=nmsXDB)'
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=826277888
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=1610612736
*.undo_management='MANUAL'

#*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/oracle/admin/nms/udump'

*._ALLOW_RESETLOGS_CORRUPTION = TRUE
*._CORRUPTED_ROLLBACK_SEGMENTS =( _SYSSMU1$,_SYSSMU2$,_SYSSMU3$,_SYSSMU4$,_SYSSMU5$,_SYSSMU6$,_SYSSMU7$,_SYSSMU8$,_SYSSMU9$,_SYSSMU10$ )

修改之后，用该pfile启动数据库。

[oracle@oraclecx ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Sep 5 10:00:22 2012

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup mount pfile='/oracle/product/10.2.0.5/db_1/dbs/initnms.ora'
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size                  2096632 bytes
Variable Size             385876488 bytes
Database Buffers         1207959552 bytes
Redo Buffers               14680064 bytes
Database mounted.
SQL>

 

结论：undo表空间能否重建成功，取决于是否有系统表的回滚段，如果日志中有 ORA-00704: bootstrap process failure，则是无法重建undo表空间而恢复的

 

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

转载于:http://blog.itpub.net/12710778/viewspace-753749/