ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 10g R2 logical standby遇到grant的问题

10g R2 logical standby遇到grant的问题

原创 Linux操作系统 作者:andyxu 时间:2009-07-29 16:46:47 0 删除 编辑
转自: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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-06-26

  • 博文量
    167
  • 访问量
    292448