Streams :: Unable To Drop Apply Queue?
Oct 10, 2013
,I am trying to set up Streams on a 11.2.0.3 on a Windows 2008R2 server. Due to an error in running propagation, i a, trying to delete both Capture and Apply queues. I have deleted the Apply queues but unable to drop the Catpure,
SQL>EXEC DBMS_APPLY_ADM.STOP_APPLY(apply_name =>'LAO_NLPG73_BLPU_APPLY'); SQL> select * from dba_apply; LAO_NLPG73_BLPU_APPLY NLPG73_BLPU_APPLY_Q STRMADMIN YES RULESET$_732 STRMADMIN STRMADMIN 301355 ABORTED 09/10/2013 17:34:21 1013 ORA-01013: user requested cancel of current operation CAPTURED STREAMS APPLY SQL> select * from dba_queues; STRMADMIN NLPG73_BLPU_APPLY_Q
[code]...
View 5 Replies
ADVERTISEMENT
Nov 1, 2013
A streams apply process which applies to a sql sever database is increasing its pga use continually until i stop the process and restart it. I need to stop it once every week or it will use too much of the pga and the database will hand causin paging etc.
View 1 Replies
View Related
Jan 21, 2013
Due to firewall restriction, i have to configure the streams in queue forwarding. Intermediate database will hold two different ip's (one from source and another to target) and will not talk each other.
View 1 Replies
View Related
Nov 26, 2011
,how to drop the queue table from our database.when iam trying to drop the queue table aim getting this error
error dropping AQ$_DEMO_QUEUE_TABLE_G;
ORA-00942:TABLE OR VIEW DOES NOT EXIST
View 1 Replies
View Related
Mar 27, 2013
My setup is following:
CODEhost22 (Oracle Linux 5.9) | host30 (Windows XP) with SQL Server Express 2008
DB22 | Gateway to MSSQL | DB30
Local Local | |
Capture --> Apply ------> Gateway -----------------> DUMMYUSR.DUMMYTBL
Process Process | |
Capture process on DB22 captures changes made to local DUMMYUSR.DUMMYTBL table, enqueues it to capture queue and propagates to apply queue for local apply process, which, using database link DB30 supposed to apply these changes to table DUMMYUSR.DUMMYTBL on remote SQL Server database DB30.
The problem is that when apply process is executing transaction, it aborts with error:
QUOTE ORA-26713: remote object does not exists or is inaccessible.
ORA-26712: remote object is "DUMMYUSR"."DUMMYTBL"@"DB30".
In trace log I can see the following message:
QUOTE kniNoPrimaryKeyError: ORA-26713, remote obj doesn't exist,
DUMMYUSR.DUMMYTBL@DB30
[code]...
what can cause ORA-26713 error of apply process applying changes to SQL Server database through Oracle Database Gateway, if object to be modifyed exists and accessible from Oracle database for strmadmin user?
By the way, database link and tables were created with following commands: CODEDB22(Oracle): (connected as strmadmin) create database link DB30 connect to "dummyusr" identified by "dummypwd" using 'DB30';
DB22(Oracle): (connected as dummyusr)
create table DUMMYTBL (no number, name char(6), ddate date, payload varchar2(2013));
DB30(MSSQL): (connected as dummyusr)
create schema DUMMYUSR authorization dummyusr;
create table DUMMYUSR.DUMMYTBL (no float(53), name char(6), ddate datetime2(0), payload varchar(2013));
View 1 Replies
View Related
Feb 9, 2013
Database Version - 10.2.0.4.0
OS - Red Hat Linux
We have configured oracle one way stream between two databases. Source database is capturing the changes (No downstream configured). Configuration was working fine but destination database was lagging behind very much i.e about 15 days behind the source database. We are ok with this but the problem is now that , as per client request we have restored previous backup and open the database with resetlog option in source database. After resetlog , archivelog sequence has been changed and stream is not working.
Can I apply the previous archivelog (before resetlog archivelogs ) in destination database anyway.Source database is a production database.
View 1 Replies
View Related
Sep 13, 2011
I have to apply monitoring template on my databases. Databases are already added and configured into OEM. They are visible under Databases tab and also under Groups where I have added them.
But when I try applying monitoring template and look for database in search window, it doesn't appear there.
View 1 Replies
View Related
Jan 19, 2010
I am trying to propagate messages between two queues that are in the same database.I did exactly as it says here: URL.....
The message is propagated successfully.The problem is that it doesn't to dequeue the message with the default subscriber of the destination queue.
The only difference between what I did and what is in the example above is that I didn't create a database link. I don't think I need to because the queues are in the same database, and the problem is not the propagation because as I said the message has moved to the destination queue.
View 4 Replies
View Related
Jun 14, 2011
I have a trigger called DDL_TRIG which is used to audit all DDL level operation on the Database. The trigger script is as below:-
CREATE OR REPLACE TRIGGER ddl_trig
AFTER DDL
ON DATABASE
BEGIN
INSERT INTO ddl_log
(user_name, ddl_date, ddl_type,
[code].....
The Table DDL_LOG exists.Now i want to disable or drop this trigger and it wont allow us. While doing on one schema it fails and with below error:
when logged in schema1 and give drop or disable command it fails
ORA:04098:trigger schema2.ddl_trig is invalid and failed re-validation.
when logged in schema2 and give drop or disable command it fails
ORA:04098:trigger schema1.ddl_trig is invalid and failed re-validation.
how can we drop this trigger in both the schema's. This trigger is not allowing us to do any activity on the Database and causing lot of problem.
View 7 Replies
View Related
Nov 29, 2012
I tried to Drop a procedure we have created, but an error is raised while droping it.
ora-04043 object does not exist
View 28 Replies
View Related
Jan 3, 2013
i want to drop a broken job but it is taking long time why?
i am using below syntax:
begin
dbms_job.broken(job=> 345,FALSE);
commit;
end;
/
View 5 Replies
View Related
Oct 29, 2013
i'm issuing drop procedure generatePasswordForEportalUser
i have an
error ORA-00600: [kqlobjlod-no-result-from-proc$], [1403], [659858], [], [], [], [], [], [], [], [], []. i found on metalink then Doc ID 1267554.1
is about this error i'm using oracle 11.2.0.3 in this document there is an info that 11.2.0.2 solves the issue Interesting thing is that select * from user_procedures where upper(object_name) like upper ('generatePasswordForEportalUser');
generatePasswordForEportalUser6598581PROCEDURENONONONONODEFINERobject name is in lovercase it was created by create procedure generatePasswordForEportalUser asbeginnull;end ;without " name " why it is in lowercase in data dictionary
View 1 Replies
View Related
Jun 26, 2013
I encountered the following error while trying to setup streams replication at the database level using dbms_streams_adm.maintain_global. Desmond begin*ERROR at line 1:ORA-23616: Failure in executing block 6 for script.
E00C49DDDB27C899E040A8C04C0119DA withORA-06550: line 21, column 3:PL/SQL: ORA-00942: table or view does not existORA-06550: line 21, column 3:PL/SQL: SQL Statement ignoredORA-06550: line 23, column 3:PL/SQL: ORA-00942: table or viewORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 659ORA-06512: at "SYS.DBMS_RECOVERABLE_SCRIPT", line 682ORA-06512: at "SYS.DBMS_STREAMS_MT", line 2427ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 3004ORA-06512: at line 2 SQL> select forward_block from dba_recoverable_script_blocks where script_id = '
[code]....
View 1 Replies
View Related
May 13, 2011
I am trying to Truncate the table but it doesnt get truncated. When I issue the command it even doesnt throw the error. I also tried to drop the table but cant even able to drop the table. I thought table might be locked. But it allows me delete a row from the table.
I am using Oracle 11g Release 11.1.0.7.0
View 17 Replies
View Related
Nov 14, 2013
SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER F_TRADING_COLLATERAL_SR 1 SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER F_TRADING_COLLATERAL_SR 1 SQL> alter table F_TRADING_COLLATERAL_SR drop unused columns; Table altered. SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER F_TRADING_COLLATERAL_SR 1 SQL>
View 6 Replies
View Related
Jan 10, 2012
When I try to drop the roles in dbartisan tool, I get an error saying "Drop DDL for **_***********_ROLE has not been generated because it is a system object", but if i go to sql prompt and say "drop role rolename", it is working. How do I remove the role in dbartisan itself?
View 9 Replies
View Related
May 11, 2012
I performed the Table Redefinition process on one of my transaction tables. I wanted to redefine the existing table to a new table with partitions. Everything worked fine until i reached the last step where we had to drop the interim table. Now in between the redefinition , we created the interim table with partition along with the constraints and indexes and then atlast ran the "dbms_redefinition.finish_redef_table" proc to finish the redefinition.
And after that when I tried to drop the interim table , I am getting the below error message
"ERROR at line 1: ORA-02449: unique/primary keys in table referenced by foreign keys"
So this means all the constraints from my actual table were swapped with the interim table and hence I am getting this error.So is this a normal case or I have missed something while performing the redefinition? If thats normal then should i Just Drop table tablename CASCADE CONSTRAINTS?
View 10 Replies
View Related
Apr 19, 2013
We are unable to drop user due to below error, how to drop the below user without shutdown the database.
SQL> drop user mvm_2010 cascade;
drop user mvm_2010 cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
No session was available for particular user.
View 19 Replies
View Related
Jun 6, 2011
Im facing the problem whenever I try to drop a user. Following thing that I m trying..
system@vahan> drop user knp cascade;
Error at line 1:
ORA-00604: error occured at recursive SQL level 1
ORA-00942: table or view does not exist
ORA-06512: at line 7
View 4 Replies
View Related
Jun 9, 2010
I am researching a performance problem on an Oracle Preprd DB in a RAC cluster using AQ. The queue table has 88 records, is about 900Meg in size, takes 90+ seconds to do a select count(*). In Prod the same table is about 44 records, 80 Meg in size, and takes about 9 seconds to query the table. The DB is at 10.2.0.4 running on a LINUX/Sun host. In the USER_DATA column I am seeing an entry in the STR_VALUE that displays 'Unable to successfully deliver a message after "MaxDeliveryCnt" attempts. Please verify that the onMessage() method of the MDB does not throw a RuntimeException' for each record in the table. I don't have direct access to the PROD DB so I can not verify this message in that environment. My question, is this table holding onto records that should be cleared out and should this table be dropped and rebuilt to reduce its size. I have seen this technique improve performance for other non-queueing tables. But I don't know if this is possible with a queue table.
View 5 Replies
View Related
Dec 9, 2010
I am trying to drop a user but i get the following error
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-24005: must use DBMS_AQADM.DROP_QUEUE_TABLE to drop queue tables
A couple of questions on this error:
- I did a search on the forum and the thread [URL] appears to have a solution to it by using
DBMS_AQADM.DROP_QUEUE_TABLE(queue_table => 'DEF$_AQCALL', force =>TRUE);
What i am not sure of is which queue will the above statement drop if run as sys and there are multiple schemas with different schema names but with the same queue name?
- We recently rebuilt the database server and prior to the rebuild i have always managed to drop a user without the above error. Is it likely that some setting somewhere is changed?
View 2 Replies
View Related
Mar 23, 2011
I need to Modify the column(MSGID) data type from RAW to BLOB for a Queue Table, I'm getting the following error.
BANNER
---------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE11.2.0.2.0Production
YUV >
YUV > DESC PDA_REPORT_MESSAGE_QTAB
Name Null?Type
----------------------------------------------------- -------- ------------------------------------
Q_NAME VARCHAR2(30)
MSGID NOT NULL RAW(16)
YUV >
YUV >
YUV > ALTER TABLE PDA_REPORT_MESSAGE_QTAB MODIFY (MSGID BLOB);
ALTER TABLE PDA_REPORT_MESSAGE_QTAB MODIFY (MSGID BLOB)
*
ERROR at line 1:
ORA-22858: invalid alteration of datatype
YUV >
YUV >
YUV >
YUV >
YUV > ALTER TABLE PDA_REPORT_MESSAGE_QTAB ADD (MSGID_NEW BLOB);
ALTER TABLE PDA_REPORT_MESSAGE_QTAB ADD (MSGID_NEW BLOB)
*
ERROR at line 1:
ORA-24005: Inappropriate utilities used to perform DDL on AQ table PDADBA.PDA_REPORT_MESSAGE_QTAB
YUV >
View 10 Replies
View Related
Nov 28, 2012
I want to send an email notification using oracle aq for events like when a message goes to error queue. Similarly,Can I send an email notification when I create a queue or drop a queue?
How can I do this?
View 3 Replies
View Related
Feb 18, 2010
I am calling a report from a parameter form - the report is launched using a run report button, that all works great. What I want to do now is to add a 'Cancel Report' button to the form so that the end user can cancel the report and remove it from the reports queue.
View 1 Replies
View Related
Sep 20, 2013
I am using Java print API (javax.print package) to send a bunch of documents for printing. Below is the code section that I am using to print documents through java program. When the document is sent for printing, I see the Job name is created properly in the print spooling queue on windows machine. But, when i go to the actual printer, the job name is different on the printer than what i saw in spooling. Since I am printing 100s of documents in batch, it gets very difficult to identify which document did not print, in case of issues. I also used the java.awt.print package . The java.awt. print. PrinterJob has a method setJobName(String). When i used this package, I got the name appear properly in both places. But I wish to use javax.print with the document name appear on printer queue.
public void printDocument(File pDoc, PrintService pService, DocFlavor pFlavor)
throws Exception { logMessage(true, "Printing Doc::" + pDoc.getAbsolutePath());
FileInputStream is = new FileInputStream(pDoc);
// Create the print job DocPrintJob job = pService.createPrintJob();
//Set print request attributes with file name as job
[code].....
View 0 Replies
View Related
Feb 3, 2012
Source DB is logical standby 10.2.0.5
Destination DB is 11.2.0.3
Is it possible to set up streams on the logical DB to replicate some schemas to the 11gR2 DB?
View 10 Replies
View Related
Apr 18, 2013
Am trying to implement Oracle Streams Replication (Using Metalink Note 733691.1).I have configured the steps, but in my alert log am getting the below error:
Check that the primary and standby are using a password file and remote_login_passwordfile is set to SHARED or EXCLUSIVE, and that the SYS password is same in the password files. returning error ORA-16191
am successfully able to connect db's both server
From 1st server
sqlplus sys@2nddb as sysdba
From 2nd server
sqlplus sys@1stdb as sysdba
while conning it is asking for password
Both DB's are created with Same Oracle sys user password. after this disabled case sensitivity , still the error persists.
View 1 Replies
View Related
Oct 4, 2010
Platform: Windows 2003
Streams Set up: One way Streaming at table level
The error: ORA-26786: A row with key ("REPT_NUM", "STATE_CODE", "SURVEY_ID") = (067305669, 49, J) exists but has conflicting column(s) "DATE_TIME", "PREV_PARENT_ID" in table TOPCATI_JOLTS.UNIT ORA-01403: no data found
We are consistently getting this error every other day and some weeks more often, different records of course. On the capture site the application does a process called split cases. In this process the application will take an old PK case num insert a new PK case number with all of the data of the old case (Parent level). At the unit (Child level) the application will change all of the units to this new FK case number. This means the old case (parent) is left with no child units. This is all one transaction.
Is it possible that streams may be applying the LCR's out of order? especially since that whole process is one transaction.
View 2 Replies
View Related
Mar 26, 2013
I am trying to setup a one way queue that will be consumed by an inhouse ESB.We currently are able to create a multi-consumer queue using:
CODEBEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(queue_table => 'strmadmin.streams_queue_table',
queue_name => 'strmadmin.streams_queue');
END;
but unfortunatly the ESB that we are using uses a jms component which seems to only be able to take mono-consumer queue.So we have created our queue usuing the following
CODEBEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE( Queue_table => '"STRMADMIN"."STREAMS_QUEUE_TABLE"',
Queue_payload_type => 'SYS.ANYDATA',
storage_clause => 'PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE
[code]...
This therefore permits us to have a queue/queuetable which pushes data to a single consumer.The probleme comes when we try to add a table rule using the following command:
CODEBEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES( table_name => 'source.t1',
streams_type => 'apply',
streams_name => 'streams_apply',
queue_name => 'strmadmin.streams_queue',
[code]...
We then get the following error:
CODEORA-06512: on line 2 24039. 00000 - "Queue %s not created in queue table for multiple consumers"
*Cause: Either an ADD_SUBSCRIBER, ALTER_SUBSCRIBER, or REMOVE_SUBSCRIBER procedure, or an ENQUEUE with a non-empty recipient list, was issued on a queue that was not created for multiple consumers.
*Action: Create the queue in a queue table that was created for multiple consumers and retry the call.
We are able to create the capture rule without any problem but without the apply rule, nothing seems to end up into the queue table.AQ is not a viable solution since it is troublesome when it comes to deletes and mass updates.
View 1 Replies
View Related
Jan 28, 2013
We have a core banking database and this database includes our customer related tables and these tables are really huge.
And we have other database for some applications and this database needs fresh customer data which take place in core banking database. Our current method is materialized views but we have some performance problems about it.
What can give better performance for synchronizing the tables between databases?
-oracle streams?
-triggers?
-dbms_comparison?
View 3 Replies
View Related
