| Use of DBMS_LOGSTDBY [message #263908] |
Fri, 31 August 2007 04:40  |
bahy91
Messages: 91
Registered: September 2005
Location: Amsterdam
|
Member |
|
|
Dear All,
i have an Error in a 'Logical Standby database' in Oracle10g r.2 environment on Hpux.The cause of the error related to dropping of Statspack from the Primary databse..the consequence of the Error is that the 'Logical standby' hang:
Thu Aug 30 16:34:08 2007
LOGMINER: Log Auto Delete - deleting: /var/opt/centric/arch/sd4rep_stndby/1_17972_610464294.dbf
Deleted file /var/opt/centric/arch/sd4rep_stndby/1_17972_610464294.dbf
LOGSTDBY Analyzer process P003 started with pid=29 OS id=2649
LOGSTDBY Apply process P005 started with pid=31 OS id=2653
LOGSTDBY Apply process P004 started with pid=30 OS id=2651
LOGSTDBY Apply process P006 started with pid=32 OS id=2655
LOGSTDBY Apply process P007 started with pid=33 OS id=2657
LOGSTDBY Apply process P008 started with pid=34 OS id=2660
Thu Aug 30 16:34:10 2007
LOGSTDBY stmt: drop public synonym STATS$SNAPSHOT_ID
LOGSTDBY status: ORA-01432: public synonym to be dropped does not exist
LOGSTDBY id: XID 0x0009.021.00004804, hSCN 0x0000.12df849e, lSCN 0x0000.12df849e, Thread 1, RBA 0x4635.000004ce.10, txnCscn 0x0000.12df84a2, PI
D 2651, oracle@smc-hp05 (P004)
LOGSTDBY Apply process P004 pid=30 OS id=2651 stopped
Thu Aug 30 16:34:10 2007
Errors in file /opt/oracle/admin/sd4rep/bdump/sd4rep_lsp0_2499.trc:
ORA-12801: error signaled in parallel query server P004
ORA-01432: public synonym to be dropped does not exist
LOGSTDBY Apply process P005 pid=31 OS id=2653 stopped
LOGSTDBY Analyzer process P003 pid=29 OS id=2649 stopped
LOGSTDBY Apply process P006 pid=32 OS id=2655 stopped
LOGSTDBY Apply process P007 pid=33 OS id=2657 stopped
LOGSTDBY Apply process P008 pid=34 OS id=2660 stopped
On Oracle10g GUI i've got this Error:
Error ORA-16768: SQL Apply unexpectedly offline.
My solution for this problem was :
-----------------------------
1) i tried to solve ORA-16768 by initiate the following:
SQL>ALTER DATABASE START LOGICAL STANDBY APPLY;
but it does not help much.
2)I issued this:
EXECUTE DBMS_LOGSTDBY.SKIP( -
stmt => 'DROP PUBLIC SYNONYM', -
schema_name => 'SYS', -
object_name => 'STATS$SNAPSHOT_ID', -
proc_name => null);
> > > >
PL/SQL procedure successfully completed.
but nothing changed on the GUI site .
3) I tried to do this without success :
CREATE OR REPLACE PROCEDURE sys.handle_error_ddl (
old_stmt IN VARCHAR2,
stmt_type IN VARCHAR2,
schema IN VARCHAR2,
name IN VARCHAR2,
xidusn IN NUMBER,
xidslt IN NUMBER,
xidsqn IN NUMBER,
error IN VARCHAR2,
new_stmt OUT VARCHAR2
) AS
BEGIN
-- Default to what we already have
new_stmt := old_stmt;
-- Ignore any SYNONYM errors on SYS or SYSTEM schemas
IF INSTR(UPPER(old_stmt),'SYNONYM') > 0 THEN
IF schema IS NULL OR (schema IS NOT NULL AND (UPPER(schema) = 'SYS' OR UPPER(schema) = 'SYSTEM' ))THEN
new_stmt := NULL;
-- record the fact that we just skipped an error on 'SYS' or 'HR' schemas
-- code not shown here
END IF;
END IF;
END handle_error_ddl;
/
Procedure created.
SQL>
Register the error handler with SQL Apply:
SQL> EXECUTE DBMS_LOGSTDBY.SKIP_ERROR ( -
statement => 'DROP PUBLIC SYNONYM', -
schema_name => NULL, -
object_name => NULL, -
proc_name => 'SYS.HANDLE_ERROR_DDL');
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SKIP_ERROR'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL>
Have anyone an idea!
Thanks,
|
|
|
|
|
|
| Re: Use of DBMS_LOGSTDBY [message #421788 is a reply to message #263908] |
Thu, 10 September 2009 10:22  |
simonfey
Messages: 1
Registered: September 2009
|
Junior Member |
|
|
I know its a Post from March 2007 but for those who have the same Problem, here is the solution.
You cannot skip a "DROP PUBLIC SYNONYM" statement with DBMS_LOGSTDBY.SKIP_ERROR. You don't either need a stored procedure.
Use DBMS_LOGSTDBY.SKIP instead.
Usage (e.g. in SYSMAN):
EXECUTE DBMS_LOGSTDBY.SKIP( -
stmt => 'SYNONYM', -
schema_name => 'SYSMAN', -
object_name => '%', -
proc_name => null);
As Oracle Documentation says, use "SYNONYM" as stmt.
Table 73-14 Supported Values for the stmt Parameter
Keyword Associated SQL Statements
SYNONYM CREATE PUBLIC SYNONYM
CREATE SYNONYM
DROP PUBLIC SYNONYM
DROP SYNONYM
Cheers
Simon Fey
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
