10g R2 logical standby遇到grant的问题

转自： http://oracleworld.spaces.live.com/blog/cns!778139b1558f9287!181.entry

环境 OS: Redhat Enterprise Linux AS 4 Update 4

       DB: Oracle10g Release 2 V 10.2.0.2.0 with  a Logical standby Database

 

遇到的问题:

 

      无意中对 primary的scott用户执行了

     Primary>grant sysdba to scott;

     等这个DDL apply到Logical的时候，告警日志出现下面的错误：

LOGMINER: Begin mining logfile: /oracle/arch2/1_252_600533893.arc
Tue Jun 12 10:01:38 2007
LOGMINER: Turning ON Log Auto Delete
LOGSTDBY Analyzer process P004 started with pid=25 OS id=4079
LOGSTDBY Apply process P010 started with pid=31 OS id=4091
LOGSTDBY Apply process P013 started with pid=35 OS id=4097
LOGSTDBY Apply process P006 started with pid=27 OS id=4083
LOGSTDBY Apply process P007 started with pid=28 OS id=4085
LOGSTDBY Apply process P005 started with pid=26 OS id=4081
LOGSTDBY Apply process P009 started with pid=30 OS id=4089
LOGSTDBY Apply process P011 started with pid=32 OS id=4093
LOGSTDBY Apply process P008 started with pid=29 OS id=4087
LOGSTDBY Apply process P012 started with pid=33 OS id=4095
LOGSTDBY Apply process P014 started with pid=36 OS id=4099
Tue Jun 12 10:01:51 2007
LOGSTDBY stmt: grant sysdba to scott
LOGSTDBY status: ORA-01031: insufficient privileges
LOGSTDBY id: XID 0x0013.01e.00000018, hSCN 0x0000.000664c1, lSCN 0x0000.000664c1, Thread 1, RBA 0x00fc.00000067.10, txnCscn

0x0000.000664c4, PID 4081, oracle@mydg2 (P005)
LOGSTDBY Apply process P005 pid=26 OS id=4081 stopped
Tue Jun 12 10:01:51 2007
Errors in file /oracle/admin/stdby/bdump/stdby_lsp0_4069.trc:
ORA-12801: error signaled in parallel query server P005
ORA-01031: insufficient privileges
LOGSTDBY Analyzer process P004 pid=25 OS id=4079 stopped
LOGSTDBY Apply process P014 pid=36 OS id=4099 stopped
LOGSTDBY Apply process P008 pid=29 OS id=4087 stopped
LOGSTDBY Apply process P010 pid=31 OS id=4091 stopped
LOGSTDBY Apply process P006 pid=27 OS id=4083 stopped
LOGSTDBY Apply process P007 pid=28 OS id=4085 stopped
LOGSTDBY Apply process P012 pid=33 OS id=4095 stopped
LOGSTDBY Apply process P013 pid=35 OS id=4097 stopped
LOGSTDBY Apply process P009 pid=30 OS id=4089 stopped
LOGSTDBY Apply process P011 pid=32 OS id=4093 stopped

可以看到这个DDL，导致了所有apply进程都停止了，实际上logical standby现在处于停止的状态，没有执行任何的apply操作，

只是primary上的归档日志还是可以传输过来。

 

要解决上面的问题，oracle提供了DBMS_LOGSTDBY.SKIP的包，于是在Logical上面执行了如下的操作：

Logical> alter database stop logical standby apply;

Logical> exec DBMS_LOGSTDBY.SKIP('NON_SCHEMA_DDL');

Logical> alter database start logical standby apply immediate;

这时在Logical的告警日志文件可以看到如下的内容：

LOGSTDBY stmt: grant sysdba to scott
LOGSTDBY status: ORA-16205: DDL skipped due to skip setting
LOGSTDBY id: XID 0x0013.01e.00000018, hSCN 0x0000.000664c1, lSCN 0x0000.000664c1, Thread 1, RBA 0x00fc.00000067.10, txnCscn

0x0000.000664c4, PID 8118, oracle@mydg2 (P005)

说明在Logical上面已经skip了DDL grant sysdba to scott，但是上面的DBMS_LOGSTDBY.SKIP('NON_SCHEMA_DDL')会把正常的

grant语句也忽略了，比如demo用户执行的 grant select on demo_tab1 to scott

只能运行下面的过程来使Logical apply复原。

Logical>exec DBMS_LOGSTDBY.UNSKIP('NON_SCHEMA_DDL')

 

说明:默认情况下oracle10g R2的Logical是支持grant dba to scott的，但是用sysdba怎么就不行了呢？

 

之后测试revoke得到的结果一样。。。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/110321/viewspace-610757/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/110321/viewspace-610757/