【Oracle】重做undo表空间

重做undo表空间

场景：

alert日志，报了如下错误:

[ bdump]$ tail -f alert_PROD.log 
Errors in file /ora1159/prod/proddb/9.2.0/admin/PROD_arpinfo/udump/prod_ora_8729.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], []
Fri Sep  6 10:08:56 2019
Errors in file /ora1159/prod/proddb/9.2.0/admin/PROD_arpinfo/udump/prod_ora_8785.trc:
ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], []
Fri Sep  6 10:08:59 2019
Errors in file /ora1159/prod/proddb/9.2.0/admin/PROD_arpinfo/udump/prod_ora_8785.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00600: internal error code, arguments: [4097], [], [], [], [], [], [], []

原因

The following error is occurring in the alert.log right before the database crashes.

ORA-00600: internal error code, arguments: [4194], [#], [#], [], [], [], [], []

This error indicates that a mismatch has been detected between redo records and rollback (undo) records.

ARGUMENTS:

Arg [a] - Maximum Undo record number in Undo block
Arg [b] - Undo record number from Redo block

Since we are adding a new undo record to our undo block, we would expect that the new record number is equal to the maximum record number in the undo block plus one. Before Oracle can add a new undo record to the undo block it validates that this is correct. If this validation fails, then
an ORA-600 [4194] will be triggered.

其核心原因是:undo超过了它最大的量。重建undo表空间，此次unlimited。

1.查看undo的参数

SQL> show parameter undo;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     1800
undo_suppress_errors                 boolean     FALSE
undo_tablespace                      string      UNDOTBS7

2.修改undo_retention参数

SQL> ALTER SYSTEM SET undo_retention=900 SCOPE=BOTH;
ALTER SYSTEM SET undo_retention=900 SCOPE=BOTH
*
ERROR at line 1:
ORA-32001: write to SPFILE requested but no SPFILE specified at startup

3.以spfile的方式启动数据库

-------------------------------以spfile启动数据库
SQL> create spfile from pfile;
File created.
-------------------------------重启数据库
SQL> shutdown immediate;
SQL> startup

-------------------------------修改undo_retention
SQL> ALTER SYSTEM SET undo_retention=900 SCOPE=BOTH;
System altered.

4.创建UNDOTBS8表空间

SQL> create undo tablespace UNDOTBS8 datafile '/ora1159/prod/proddata/UNDOTBS8.dbf' size 256M autoextend on next 128M maxsize unlimited;

Tablespace created.

5.查询历史undo是否还有事务(包含回滚事务)

SELECT a.tablespace_name,a.segment_name,b.ktuxesta,b.ktuxecfl,
b.ktuxeusn||'.'||b.ktuxeslt||'.'||b.ktuxesqn trans
FROM dba_rollback_segs a, x$ktuxe b 
WHERE a.segment_id = b.ktuxeusn 
AND a.tablespace_name = UPPER('&tsname') 
AND b.ktuxesta <> 'INACTIVE';
Enter value for tsname: UNDOTBS7
old   5: AND a.tablespace_name = UPPER('&tsname')
new   5: AND a.tablespace_name = UPPER('UNDOTBS7')

no rows selected

如果没有数据，可以进行undo表空间切换。

6.切换undo表空间

alter system set undo_tablespace='UNDOTBS8';

7.查询回滚段情况

原undo表空间的回滚段全部offline,可以删除相关表空间

SQL> select tablespace_name,segment_name,status from dba_rollback_segs;

TABLESPACE_NAME                SEGMENT_NAME                   STATUS
------------------------------ ------------------------------ ----------------
SYSTEM                         SYSTEM                         ONLINE
UNDOTBS7                       _SYSSMU2$                      OFFLINE
UNDOTBS7                       _SYSSMU3$                      OFFLINE
UNDOTBS7                       _SYSSMU4$                      OFFLINE
UNDOTBS7                       _SYSSMU5$                      OFFLINE
UNDOTBS7                       _SYSSMU6$                      OFFLINE
UNDOTBS7                       _SYSSMU7$                      OFFLINE
UNDOTBS7                       _SYSSMU8$                      OFFLINE
UNDOTBS7                       _SYSSMU9$                      OFFLINE
UNDOTBS7                       _SYSSMU10$                     OFFLINE
UNDOTBS7                       _SYSSMU11$                     OFFLINE

TABLESPACE_NAME                SEGMENT_NAME                   STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS7                       _SYSSMU12$                     OFFLINE
UNDOTBS7                       _SYSSMU13$                     OFFLINE
UNDOTBS7                       _SYSSMU14$                     OFFLINE
UNDOTBS8                       _SYSSMU15$                     ONLINE
UNDOTBS8                       _SYSSMU16$                     ONLINE
UNDOTBS8                       _SYSSMU17$                     ONLINE
UNDOTBS8                       _SYSSMU18$                     ONLINE
UNDOTBS8                       _SYSSMU19$                     ONLINE
UNDOTBS8                       _SYSSMU20$                     ONLINE
UNDOTBS8                       _SYSSMU21$                     ONLINE
UNDOTBS8                       _SYSSMU22$                     ONLINE

TABLESPACE_NAME                SEGMENT_NAME                   STATUS
------------------------------ ------------------------------ ----------------
UNDOTBS8                       _SYSSMU23$                     ONLINE
UNDOTBS8                       _SYSSMU24$                     ONLINE

24 rows selected.

8.下线原undo表空间

alter tablespace UNDOTBS7 offline;

9.删除原undo表空间

drop tablespace UNDOTBS7 including contents and datafiles;

10.修改undo retention

SQL> ALTER SYSTEM SET undo_retention=1800 SCOPE=BOTH;

参考文献

Step by step to resolve ORA-600 4194 4193 4197 on database crash (文档 ID 1428786.1)