sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【学习笔记】Oracle报错ORA-01548 使用隐含参数手工清除回滚段

时间:2016-11-15 19:35   来源:Oracle研究中心   作者:网络   点击:

天萃荷净 Oracle研究中心学习笔记:分享一篇关于Oracle数据库处理回滚段的笔记,详细介绍使用隐含参数offline_rollback_segments,corrupted_rollback_segments清除故障回滚段。

在某些情况下,我们需要手动去清除一些有问题的回滚段,如果该回滚段中包含活动事务,那么使用正常的方式将无法drop,所以此时你drop undo tablespace 也将失败。
可能就会遇到如下的错误:

SQL> DROP tablespace undotbs1 including contents AND datafiles;
DROP tablespace undotbs1 including contents AND datafiles
*
ERROR at line 1:
ORA-01561: failed TO remove ALL objects IN the tablespace specified
下面介绍几种方法:

用隐含参数

SQL> SHOW parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       INTEGER     900
undo_tablespace                      string      UNDOTBS1

SQL> CREATE undo tablespace undotbs2 datafile '/oracle/product/oradata/roger/undotbs2.dbf'
  2  SIZE 50m autoextend off;

Tablespace created.

SQL> CREATE undo tablespace undotbs3 datafile '/oracle/product/oradata/roger/undotbs3.dbf'
  2  SIZE 50m autoextend off;

Tablespace created.

SQL> conn roger/roger
Connected.

SQL> CREATE TABLE ht_01 AS SELECT * FROM dba_objects WHERE rownum <10;

TABLE created.

SQL> DELETE FROM ht_01 WHERE rownum <5;

4 ROWS deleted.

SQL> -- 不提交

SQL> SELECT owner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,STATUS,tablespace_name
  2  FROM dba_rollback_segs;

OWNER  SEGMENT_NAME   SEGMENT_ID    FILE_ID   BLOCK_ID  STATUS   TABLESPACE_NAME
------ -------------- ---------- ---------- ----------  -------- ---------------
SYS    SYSTEM                  0          1          9  ONLINE   SYSTEM
PUBLIC _SYSSMU1$               1          2          9  ONLINE   UNDOTBS1
PUBLIC _SYSSMU2$               2          2         25  ONLINE   UNDOTBS1
PUBLIC _SYSSMU3$               3          2         41  ONLINE   UNDOTBS1
PUBLIC _SYSSMU4$               4          2         57  ONLINE   UNDOTBS1
PUBLIC _SYSSMU5$               5          2         73  ONLINE   UNDOTBS1
PUBLIC _SYSSMU6$               6          2         89  ONLINE   UNDOTBS1
PUBLIC _SYSSMU7$               7          2        105  ONLINE   UNDOTBS1
PUBLIC _SYSSMU8$               8          2        121  ONLINE   UNDOTBS1
PUBLIC _SYSSMU9$               9          2        137  ONLINE   UNDOTBS1
PUBLIC _SYSSMU10$             10          2        153  ONLINE   UNDOTBS1
PUBLIC _SYSSMU11$             11          6          9  OFFLINE  UNDOTBS2
PUBLIC _SYSSMU12$             12          6         25  OFFLINE  UNDOTBS2
PUBLIC _SYSSMU13$             13          6         41  OFFLINE  UNDOTBS2
PUBLIC _SYSSMU14$             14          6         57  OFFLINE  UNDOTBS2
PUBLIC _SYSSMU15$             15          6         73  OFFLINE  UNDOTBS2
PUBLIC _SYSSMU16$             16          6         89  OFFLINE  UNDOTBS2
PUBLIC _SYSSMU17$             17          6        105  OFFLINE  UNDOTBS2
PUBLIC _SYSSMU18$             18          6        121  OFFLINE  UNDOTBS2
PUBLIC _SYSSMU19$             19          6        137  OFFLINE  UNDOTBS2
PUBLIC _SYSSMU20$             20          6        153  OFFLINE  UNDOTBS2
PUBLIC _SYSSMU21$             21          7          9  OFFLINE  UNDOTBS3
PUBLIC _SYSSMU22$             22          7         25  OFFLINE  UNDOTBS3
PUBLIC _SYSSMU23$             23          7         41  OFFLINE  UNDOTBS3
PUBLIC _SYSSMU24$             24          7         57  OFFLINE  UNDOTBS3
PUBLIC _SYSSMU25$             25          7         73  OFFLINE  UNDOTBS3
PUBLIC _SYSSMU26$             26          7         89  OFFLINE  UNDOTBS3
PUBLIC _SYSSMU27$             27          7        105  OFFLINE  UNDOTBS3
PUBLIC _SYSSMU28$             28          7        121  OFFLINE  UNDOTBS3
PUBLIC _SYSSMU29$             29          7        137  OFFLINE  UNDOTBS3
PUBLIC _SYSSMU30$             30          7        153  OFFLINE  UNDOTBS3

31 ROWS selected.

SQL> SELECT xidusn,xidslot,xidsqn,ubablk,ubafil,ubarec FROM v$transaction;

    XIDUSN    XIDSLOT     XIDSQN     UBABLK     UBAFIL     UBAREC
---------- ---------- ---------- ---------- ---------- ----------
         9         13        299        439          2         47

SQL> SELECT usn,name FROM v$rollname WHERE usn=9;

       USN NAME
---------- --------------------------------------------------
         9 _SYSSMU9$

SQL> c/file_name/file_name,tablespace_name
  1* SELECT file_id,file_name,tablespace_name FROM dba_data_files ORDER BY 1

SQL> /
   FILE_ID FILE_NAME                                       TABLESPACE_NAME
---------- ----------------------------------------------- ---------------
         1 /oracle/product/oradata/roger/system01.dbf      SYSTEM
         2 /oracle/product/oradata/roger/undotbs01.dbf     UNDOTBS1
         3 /oracle/product/oradata/roger/sysaux01.dbf      SYSAUX
         4 /oracle/product/oradata/roger/users01.dbf       USERS
         5 /oracle/product/oradata/roger/roger01.dbf       ROGER
         6 /oracle/product/oradata/roger/undotbs2.dbf      UNDOTBS2
         7 /oracle/product/oradata/roger/undotbs3.dbf      UNDOTBS3

7 ROWS selected.                                         

SQL> SHOW parameter undo                                 

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------------------
undo_management                      string      AUTO
undo_retention                       INTEGER     900
undo_tablespace                      string      UNDOTBS1
SQL> ALTER system SET undo_tablespace=undotbs2;

System altered.

SQL> ALTER DATABASE datafile 2 offline;
DATABASE altered.

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup
ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed SIZE                  1266392 bytes
Variable SIZE             104860968 bytes
DATABASE Buffers           58720256 bytes
Redo Buffers                2924544 bytes
DATABASE mounted.
DATABASE opened.

SQL> DROP tablespace undotbs1 including contents AND datafiles;
DROP tablespace undotbs1 including contents AND datafiles
*
ERROR at line 1:
ORA-01548: active ROLLBACK segment '_SYSSMU9$' found, terminate dropping tablespace

-- 我们看到报错了, 意思是说该回滚段中还有活动事务.

SQL> conn roger/roger
Connected.

SQL> SHOW parameter undo
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
_collect_undo_stats                  BOOLEAN     TRUE
_gc_dissolve_undo_affinity           BOOLEAN     FALSE
_gc_initiate_undo_affinity           BOOLEAN     TRUE
_gc_undo_affinity                    BOOLEAN     TRUE
_gc_undo_affinity_locks              BOOLEAN     TRUE
_in_memory_undo                      BOOLEAN     TRUE
_kcl_undo_grouping                   INTEGER     32
_kcl_undo_locks                      INTEGER     128
_optimizer_undo_changes              BOOLEAN     FALSE
_optimizer_undo_cost_change          string      10.2.0.4
_smon_undo_seg_rescan_limit          INTEGER     10
_undo_autotune                       BOOLEAN     TRUE
_undo_debug_mode                     INTEGER     0
_undo_debug_usage                    INTEGER     0
_verify_undo_quota                   BOOLEAN     FALSE
undo_management                      string      AUTO
undo_retention                       INTEGER     900
undo_tablespace                      string      UNDOTBS2

SQL> ALTER system SET "_smu_debug_mode" = 4;
System altered.

SQL> ALTER ROLLBACK segment "_SYSSMU9$" offline;
ALTER ROLLBACK segment "_SYSSMU9$" offline
*
ERROR at line 1:
ORA-01598: ROLLBACK segment '_SYSSMU9$' IS NOT online

SQL> DROP ROLLBACK segment "_SYSSMU9$";
DROP ROLLBACK segment "_SYSSMU9$"
*
ERROR at line 1:
ORA-01545: ROLLBACK segment '_SYSSMU9$' specified NOT available

SQL> SELECT owner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,STATUS
  2  FROM dba_rollback_segs
  3  WHERE segment_name='_SYSSMU9$';

OWNER  SEGMENT_NAME         SEGMENT_ID    FILE_ID   BLOCK_ID STATUS
------ -------------------- ---------- ---------- ---------- ----------------
PUBLIC _SYSSMU9$                     9          2        137 NEEDS RECOVERY

用如下隐含参数:

_offline_rollback_segments=(_SYSSMU9$)
_corrupted_rollback_segments=(_SYSSMU9$)


SQL> startup mount pfile='/oracle/pfile.ora';

ORACLE instance started.
Total System Global Area  167772160 bytes
Fixed SIZE                  1266392 bytes
Variable SIZE             104860968 bytes
DATABASE Buffers           58720256 bytes
Redo Buffers                2924544 bytes
DATABASE mounted.

SQL> ALTER DATABASE OPEN;
DATABASE altered.

SQL> SELECT owner,segment_name,SEGMENT_ID,FILE_ID,BLOCK_ID,STATUS
  2  FROM dba_rollback_segs
  3  WHERE segment_name='_SYSSMU9$';

OWNER  SEGMENT_NAME         SEGMENT_ID    FILE_ID   BLOCK_ID  STATUS
------ -------------------- ---------- ---------- ----------  ----------------
PUBLIC _SYSSMU9$                     9          2        137  NEEDS RECOVERY

SQL> DROP ROLLBACK segment "_SYSSMU9$";
ROLLBACK segment dropped.

SQL> DROP tablespace undotbs1 including contents AND datafiles;
Tablespace dropped.

SQL> SELECT COUNT(*) FROM ht_01;

  COUNT(*)
----------
         5    -- 数据丢失

这里可能有人会问上面的_undo_debug_mode参数为啥不管用?其实不是没效果,是针对这种情况下,没用而已,对于自动undo管理模式,可以使用该参数来手工删除某个回滚段,前提是该回滚段无活动事务。

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle报错ORA-01548 使用隐含参数手工清除回滚段

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1279.html

Oracle研究中心

关键词:

corrupted_rollback_segments

offline_rollback_segments

使用隐含参数清除故障回滚段