Home » Server Options » Streams & AQ » Error mail received from production server (oracle 10g 10.2.0.3. on linux 64 bit)
Error mail received from production server [message #429503] Wed, 04 November 2009 04:51
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
Hi,
Following error is being generated by my Production DB after every ten minutes. When i am stopping Enterprise manager by "emctl stop dbconsole" it stops.

I am using Schema stream replication from PRDB1 to PRDB2. Stream is working and both databases are working fine.

What should i do?


WARNING ORACLE DATABASE ERROR ORA-1031 ENCOUNTERED IN PRODUCTION DATABASE PRDB1 Statement is: /* OracleOEM */

declare
TYPE data_cursor_type IS REF CURSOR;
data_cursor data_cursor_type;
resultsql varchar2(32767);
rquery varchar2(1000);
wquery varchar2(1000);
propagation_name varchar2(100) ;
i number :=0;
rdy NUMBER := 0;
wtg NUMBER := 0;
begin
resultsql := '';

FOR cur_row IN (select propagation_name,queue_table,source_queue_owner,source_queue_name from dba_queues, dba_propagation where owner=source_queue_owner and source_queue_name=name) LOOP
rquery := 'SELECT NVL(COUNT(MSG_STATE),0) FROM '||cur_row.source_queue_owner||'.AQ$'||cur_row.queue_table||' WHERE consumer_name is null AND MSG_STATE=''READY'' and queue = '''||cur_row.source_queue_name||''' and address IN (select ''"''||destination_queue_owner ||''"'' ||''.''||''"'' ||destination_queue_name||''"@''||destination_dblink from dba_propagation)';
execute immediate rquery into rdy;
wquery := 'SELECT NVL(COUNT(MSG_STATE),0) FROM '||cur_row.source_queue_owner||'.AQ$'||cur_row.queue_table||' WHERE consumer_name is null AND MSG_STATE=''WAITING'' and queue = '''||cur_row.source_queue_name||''' and address IN (select ''"''||destination_queue_owner ||''"'' ||''.''||''"'' ||destination_queue_name||''"@''||destination_dblink from dba_propagation)';
execute immediate wquery into wtg;
if(i > 0) then
resultsql := resultsql || ' UNION ';
end if;
propagation_name := cur_row.propagation_name;
resultsql := resultsql || 'select '''||propagation_name||''' propagation_name,'||rdy|| ' ready,'||wtg||' waiting from dual';
i := i + 1;
END LOOP;

----
-- If no propagation jobs are there , return an empty result set
----

if(resultsql IS NULL) then
OPEN data_cursor FOR
SELECT -1
FROM dual
WHERE 0=1;
else
OPEN data_cursor FOR resultsql;
end if;

:1 := data_cursor;
end;
Previous Topic: Unable to stop propagation
Next Topic: AQ_TM_PROCESSES, which value?
Goto Forum:
  


Current Time: Thu Mar 28 09:15:44 CDT 2024