| 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;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
