10g物理standby 转换逻辑standby的时候卡住.

Leo

1. SQL> oradebug setmypid;
   
2. Statement processed.
   
3. SQL> oradebug event 10046 trace name context forever,level 12;
   
4. Statement processed.
   
5. SQL> EXECUTE DBMS_LOGSTDBY.BUILD;

复制代码

trace file:

Leo

tkprof 后的文件.

repentance

select object_name,object_type,owner from dba_object where object_id='49440';
select * from v$lock where id1='49440';

Leo

官方文档中这样解释：
The DBMS_LOGSTDBY.BUILD procedure waits for all existing transactions to
complete. Long-running transactions executed on the primary database will affect the
timeliness of this command.
The DBMS_LOGSTDBY.BUILD procedure uses Flashback Query to obtain a consistent
snapshot of the data dictionary that is then logged in the redo stream. Oracle
recommends setting the UNDO_RETENTION initialization parameter to 3600 on both
the primary and logical standby databases

老板同意晚上可以停主库2小时，就怕到时还卡住....

Leo

该备库目前是物理standby,可以正常同步应用日志.

Maclean Liu(刘相兵

再说一次trace要压缩， 否则太对得起 国内这些运营商了

Maclean Liu(刘相兵

BEGIN DBMS_LOGSTDBY.BUILD; END;


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          0          0          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: SYS

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  enq: TX - contention                          705        2.93       2063.57


大量   enq: TX - contention       ， 非行锁等待


描述该现象 DBMS_LOGSTDBY.BUILD + enq: TX - contention

Maclean Liu(刘相兵

The DBMS_LOGSTDBY.BUILD procedure waits for all existing transactions to complete. Long-running transactions executed on the primary database will affect the timeliness of this command. Looks like there are still some open Transactions on this Database started before the Build. The Procedure waits for all open Transactions to complete.

Maclean Liu(刘相兵

SELECT * FROM dba_2pc_pending;
SELECT * FROM pending_sessions$;
SELECT * FROM pending_sub_sessions$;
SELECT * FROM dba_2pc_neighbors;
SELECT XID, START_TIME, STATUS from v$transaction;

Leo

非常感谢大家!
问题已解决,通过执行DBMS_LOGSTDBY.BUILD的session id在v$session 找到 BLOCKING_SESSION.
然后查找相应的session,将其kill掉以后,DBMS_LOGSTDBY.BUILD执行很快通过.