How to bypass this statment on standby oracle 10g release 2
706657Jun 12 2009 — edited Oct 27 2010Hi folks,
I've created a standby and on the primary I issued a statement "revoke sysdba from system" and that created errors on the standby and it stopped the log apply.
I tried DBMS_LOGSTDBY.SKIP and DBMS_LOGSTDBY.SKIP_ERROR and maybe I'm doing something wrong and non of it works and still the log doesn't apply on the standby and it is stuck at this error statement "revoke sysdba from system". Can you give me a specific statement that would make sure that the standby won't try to apply this 'revoke' statement anymore. I tried the following and it doesn't work:
execute dbms_logstdby.skip_error ('SYSTEM GRANT', NULL, NULL, NULL)
execute dbms_logstdby.skip_error ('SYSTEM GRANT', NULL,NULL, 'SYS.HANDLE_ERROR_DDL')
execute dbms_logstdby.skip('SYSTEM GRANT', NULL, NULL, NULL)
None of it works!!
Also if there is a quick way, I want to make sure that all statements are ignored completely and I can reset logs or something.
Following is the error log:
Fri Jun 12 18:28:22 2009
LOGSTDBY status: ORA-16222: automatic Logical Standby retry of last action
LOGSTDBY status: ORA-16111: log mining and apply setting up
Fri Jun 12 18:28:22 2009
LOGMINER: Parameters summary for session# = 1
LOGMINER: Number of processes = 3, Transaction Chunk Size = 201
LOGMINER: Memory Size = 30M, Checkpoint interval = 150M
LOGMINER: session# = 1, builder process P001 started with pid=22 OS id=2532
LOGSTDBY Analyzer process P003 started with pid=20 OS id=4796
LOGSTDBY Apply process P004 started with pid=24 OS id=5344
LOGSTDBY Apply process P005 started with pid=25 OS id=5472
LOGSTDBY Apply process P006 started with pid=26 OS id=860
LOGSTDBY Apply process P007 started with pid=27 OS id=2672
LOGSTDBY Apply process P008 started with pid=28 OS id=6792
LOGMINER: session# = 1, reader process P000 started with pid=18 OS id=2932
Fri Jun 12 18:28:23 2009
LOGMINER: Begin mining logfile: C:\ORADATA\STDBY\ARCHIVELOG\DEST3\STDBY_1_42_689175870.ARC
Fri Jun 12 18:28:23 2009
LOGMINER: Turning ON Log Auto Delete
Fri Jun 12 18:28:23 2009
LOGMINER: End mining logfile: C:\ORADATA\STDBY\ARCHIVELOG\DEST3\STDBY_1_42_689175870.ARC
LOGMINER: session# = 1, preparer process P002 started with pid=23 OS id=1712
Fri Jun 12 18:28:24 2009
LOGSTDBY stmt: revoke sysdba from system
LOGSTDBY status: ORA-16110: user procedure processing of logical standby apply DDL
LOGSTDBY id: XID 0x0003.005.00000154, hSCN 0x0000.000a3f5e, lSCN 0x0000.000a3f5e, Thread 1, RBA 0x002a.00009f2a.10, txnCscn 0x0000.000a3f60, PID 5344, ORACLE.EXE (P004)
LOGSTDBY stmt: revoke sysdba from system
LOGSTDBY status: revoke sysdba from system
LOGSTDBY id: XID 0x0003.005.00000154, hSCN 0x0000.000a3f5e, lSCN 0x0000.000a3f5e, Thread 1, RBA 0x002a.00009f2a.10, txnCscn 0x0000.000a3f60, PID 5344, ORACLE.EXE (P004)
LOGSTDBY stmt: revoke sysdba from system
LOGSTDBY status: ORA-01031: insufficient privileges
LOGSTDBY id: XID 0x0003.005.00000154, hSCN 0x0000.000a3f5e, lSCN 0x0000.000a3f5e, Thread 1, RBA 0x002a.00009f2a.10, txnCscn 0x0000.000a3f60, PID 5344, ORACLE.EXE (P004)
LOGSTDBY Apply process P004 pid=24 OS id=5344 stopped
Fri Jun 12 18:28:24 2009
Errors in file c:\oradata\stdby\bdump\ldg_lsp0_3340.trc:
ORA-12801: error signaled in parallel query server P004
ORA-01031: insufficient privileges
LOGSTDBY Analyzer process P003 pid=20 OS id=4796 stopped
LOGSTDBY Apply process P006 pid=26 OS id=860 stopped
LOGSTDBY Apply process P007 pid=27 OS id=2672 stopped
LOGSTDBY Apply process P008 pid=28 OS id=6792 stopped
LOGSTDBY Apply process P005 pid=25 OS id=5472 stopped