Home » RDBMS Server » Performance Tuning » Use of DBMS_LOGSTDBY
Use of DBMS_LOGSTDBY [message #263908] Fri, 31 August 2007 04:40 Go to next message
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 #263951 is a reply to message #263908] Fri, 31 August 2007 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Regards
Michel
Re: Use of DBMS_LOGSTDBY [message #421788 is a reply to message #263908] Thu, 10 September 2009 10:22 Go to previous messageGo to next message
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
Re: Use of DBMS_LOGSTDBY [message #421792 is a reply to message #421788] Thu, 10 September 2009 10:53 Go to previous message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Thanks for the information.

Regards
Michel
Previous Topic: I/O cost was very high
Next Topic: TEMP tablespace getting filled up with multi table join
Goto Forum:
  


Current Time: Tue Dec 03 18:39:19 CST 2024