When one starts the SQL apply on the database...
SQL>Alter database start logical standby apply immediate; Database alteredOracle starts the SQL apply but subsequently it fails. In the alert log one would find the following errors..
Errors detected in process 16, role LOGICAL STANDBY COORDINATOR. krvsqn2s: unhandled failure 604: ORA-00604: error occurred at recursive SQL level 1 ORA-01425: escape character must be character string of length 1There would be an associated trace file, which would show...
*** SERVICE NAME:(SYS$BACKGROUND) 2011-03-08 12:21:04.572 *** SESSION ID:(632.180) 2011-03-08 12:21:04.572 ORA-00604: error occurred at recursive SQL level 1 ORA-01425: escape character must be character string of length 1 knahcapplymain: encountered error=604 *** 2011-03-08 12:21:04.619 ksedmp: internal or fatal error ORA-00604: error occurred at recursive SQL level 1 ORA-01425: escape character must be character string of length 1This is due to a Bug: 5108158 which is fixed in Oracle11g. Well!! I have heard that this is when we set logical standby to skip some schemas. Anyhow, this was not the case in our standby database neither could I find this skip schemas reason documented by Oracle !! :)
There is a workaround which would get the SQL apply to re-start. The workaround is documented in metalink 748208.1
SQL>--Ensure SQL apply is stopped SQL> Alter database stop logical standby apply; SQL>set echo on SQL>set pagesize 100 SQL>spool workaround.log SQL>select * from system.logstdby$skip; SQL>select distinct nvl(esc, 'NULL') from system.logstdby$skip; SQL>select * from system.logstdby$skip where esc is null; SQL>update system.logstdby$skip set esc = '\' where esc is NULL; -- Following should return no rows (due to update above) SQL>select * from system.logstdby$skip where esc is null; -- should no longer see any NULL in output SQL>select distinct nvl(esc, 'NULL') from system.logstdby$skip; -- Capture a snapshot of the final results SQL>select * from system.logstdby$skip; -- commit changes SQL>commit; --Restart the SQL apply and check the alert log. SQL> Alter database start logical standby apply immediate; SQL>Spool Off;In case, still SQL apply does not start, then immediately contact oracle support.
No comments:
Post a Comment