1, 收到警告邮件：实例 xxx is down on server yyy-01

2, 上yyy-01 服务器查看警告日志，发现：

Tue Mar 6 22:26:46 2012
ORA-1652: unable to extend temp segment by 40964 in tablespace SYSTEM
ORA-1652: unable to extend temp segment by 40964 in tablespace SYSTEM
Tue Mar 6 22:26:47 2012
GATHER_STATS_JOB encountered errors. Check the trace file.
Tue Mar 6 22:26:47 2012
Errors in file /u01/app/admin/xxx/bdump/xxx_j001_15566.trc:
ORA-01652: unable to extend temp segment by 40964 in tablespace SYSTEM
Wed Mar 7 05:45:17 2012
Shutting down instance: further logons disabled

trace file :

*** 2012-03-06 22:26:47.168
*** ACTION NAME:(GATHER_STATS_JOB) 2012-03-06 22:26:47.167
*** MODULE NAME:(DBMS_SCHEDULER) 2012-03-06 22:26:47.167
*** SERVICE NAME:(SYS$USERS) 2012-03-06 22:26:47.167
*** SESSION ID:(134.132) 2012-03-06 22:26:47.167
ORA-01652: unable to extend temp segment by 40964 in tablespace SYSTEM
*** 2012-03-06 22:26:47.168
GATHER_STATS_JOB: GATHER_TABLE_STATS('"EOR_MART"','"DSTAGE_EXCEPTION_ERROR_DETAIL"','""', ...)

3, 上yyy-02 (failover server) ，查看到 xxx 实例在运行中，这样至少业务没有受到影响。

4, 在yyy-02 上登入xxx实例，查询默认临时表空间

SQL> select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME
------------------------------
PROPERTY_VALUE
----------------------------------------------------------------------------------------------------
DESCRIPTION
----------------------------------------------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE
SYSTEM
Name of default temporary tablespace

初步断定是因为GATHER_STATS_JOB将SYSTEM做为临时表空间,导致SYSTEM满，从而实例关闭，failover 到yyy-02.

5，修改默认临时表空间：

alter database default temporary tablespace TEMP;

6，转换节点

crs_stop xxx-lsnr
crs_stop xxx-vip
crs_relocate xxx-lsnr -c yyy-01
crs_relocate xxx-db -c yyy-01
crs_relocate xxx-vip -c yyy-01
crs_start xxx-lsnr
crs_start xxx-vip
crsstat

----

crs_relocate xxx-lsnr -c yyy-01
CRS-1008: Resource 'xxx-lsnr' is not relocatable (current and target state not running)

CRS-0223: Resource 'xxx-lsnr' has placement error.

貌似是因为yyy-01 上 lsnr 没有stop

crs_relocate xxx-db -c yyy-01

Attempting to stop 'xxx-db` on member `yyy-02`
Stop of `xxx-db` on member `yyy-02` succeeded.
Attempting to start `xxx-db` on member `yyy-01`
Start of `xxx-db` on member `yyy-01` succeeded.

____

这样xxx再次回到yyy-01 服务器上。检查实例/监控状态，检查默认临时表空间，检查crsstat。

ITPub博客

一次因临时表空间问题而导致的RAC节点failover

最新文章