今天又碰到了Logic Standby同步的问题，起因是这样的，有用户反映在备库的查询数据很旧，于是检查备库的同步信息：

SELECT FILE_NAME, SEQUENCE# AS SEQ#, FIRST_CHANGE# AS FCHANGE#,NEXT_CHANGE# AS NCHANGE#, TIMESTAMP,DICT_BEGIN AS BEG, DICT_END AS END,THREAD# AS THR# , APPLIED
FROM DBA_LOGSTDBY_LOG ORDER BY THREAD#,SEQUENCE#;

发现日志并没有积压，以为只是大事务没有完成，没什么问题，可是检查DBA_LOGSTDBY_EVENTS视图还是发现了问题：

SELECT EVENT_TIME,EVENT,XIDUSN, XIDSLT, XIDSQN FROM DBA_LOGSTDBY_EVENTS order by 1 desc;

EVENT_TIME EVENT XIDUSN XIDSLT XIDSQN

2009-8-27 10:38:55 <CLOB> 7 29 735354

<CLOB>对象记录的SQL如下：

create global temporary table sntempStoreOut1210164_3 on commit preserve rows as
select
*
from
storeslistg where 1 <> 1

看来是这个创建临时表的DDL引起了阻塞，先停止standby：

SQL> alter database stop logical standby apply;

Database altered.

尝试手动执行这个DDL：

SQL> create global temporary table banping.sntempStoreOut1210164_3 on commit preserve rows as
  2  select
  3  *  from
 4  banping.storeslistg g where 1 <> 1
 5  /
create global temporary table banping.sntempStoreOut1210164_3 on commit preserve rows as
                                     *
ERROR at line 1:
ORA-00955: name is already used by an existing object

看来这个临时表已经存在了，由于程序设计的原因，每次使用临时表的时候会判断是否存在，如果不存在则创建，旧的已有的应该是可以删掉的：

SQL> drop table banping.sntempStoreOut1210164_3;
drop table banping.sntempStoreOut1210164_3
                  *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
SQL> truncate table banping.sntempStoreOut1210164_3;

Table truncated.

SQL> drop table banping.sntempStoreOut1210164_3;
drop table banping.sntempStoreOut1210164_3
                  *
ERROR at line 1:
ORA-14452: attempt to create, alter or drop an index on temporary table already in use

无法drop掉旧的表，只能找到使用这个表的session并杀掉：

SQL> select sid,id1,type from v$lock where
  2  id1=(select object_id from dba_objects where object_name=upper('sntempStoreOut1210164_3'))
  3  /

       SID        ID1 TY
---------- ---------- --
      1021     195370 TO
      1024     195370 TO
      1052     195370 TO
      1072     195370 TO
      1084     195370 TO
      1053     195370 TO

6 rows selected.

通过SID到v$session找到对应的serial#后就可以杀掉session了，发现这几个session都是INACTIVE状态的，估计是以前挂在这边的了。奇怪的是这里的TYPE值是TO，不知道TO是什么类型的锁，Oracle的文档也只记录了以下用户类型锁，系统类型的锁也没有TO：

TM - DML enqueue
TX - Transaction enqueue
UL - User supplied

手动kill session，释放对临时表的锁定：

SQL> alter system kill session '1021,1444';

System altered.

SQL> alter system kill session '1024,1116';

System altered.

SQL> alter system kill session '1052,2688';

System altered.

SQL> alter system kill session '1053,2579';

System altered.

SQL> alter system kill session '1072,473';

System altered.

SQL> alter system kill session '1084,948';

System altered.

然后就可以drop掉原来的临时表：

SQL> drop table banping.sntempStoreOut1210164_3;

Table dropped.

再手动创建新的临时表：

SQL> create global temporary table banping.sntempStoreOut1210164_3 on commit preserve rows as
  2  select
  3  *  from
 4  banping.storeslistg g where 1 <> 1
 5  /

Table created.

然后手动跳过这个事务：

SQL> exec dbms_logstdby.skip_transaction(7,29,735354);

PL/SQL procedure successfully completed.

重新开始standby：

SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;

Database altered.

观察日志，一切恢复正常。logic standby麻烦的地方就是没有足够的警告日志，要人工去查找原因，当然监控也是可以通过查询一些视图做到的。

2009/08/27 12:08 - Oracle