Replication :: Oracle Streams Or MVs Or Database Triggers
May 27, 2011
We have three unix servers with four databases (10gR2) containing "HP Operation Management Unix" (OMU) server messages for monitoring purpose, and we now want to transfer these data to one new database on a new server for reporting purpose.
The message table in each OMU database keeps the message row until it is "Acknowledged" or for maximum fourteen days, then it is moved to an historic table where it stays for another three days. Keeping data for only seventeen days are a performance issue.The new "Reporting database" is intended to hold messages data for the last 90 days.
I wonder which method to use to move/replicate data against the databases? Materialized view using database link, with view on top of the MVs. How to keep rows longer than the master (source) table, avoiding deletion when master row is deleted
Oracle Streams, with local capture and remote apply. How will this influence on the master database performance. There are about 10000 new messages in each OMU database every day. Is it possible having four streams connections against the reporting database ?
Or should I simply use database triggers which fires after insert and update and applies changes to the reporting database using database links ?
View 2 Replies
ADVERTISEMENT
Feb 22, 2012
Actually am trying to replicate two db servers from one in hong kong and another in china. when am trying to establish the replication, am getting error 'ORA-04052: error occurred when looking up remote object' like this...
but the same way i have tried in my local network, it is working fine.i have tried schema replication through enterprise manager grid control..
View 6 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
Aug 31, 2011
Till now my company used Share Plex for replication 6 DBs from different location to one server (used for BI operations).
1) Replication in in real time
2) All DBs are Oracle but different versions 9 and 10 .
3) We have horizontal and vertical replication. We replicate just some tables, for some tables just some columns and for some tables just records with some conditions
4) We have different charsets.
5) SharePlex have compare/repair tool to check it DBs are in sync mode
I started to look for Oracle Streams to use it instead of SharePlex. Do you know if it is able to handle that type of replications? Do you know about any Oracle Streams limitation which eliminates it?
View 5 Replies
View Related
Jul 20, 2009
We are using Oracle Streams for replication.
Column datatypes in some of the tables on Source and Destination are different (Number on Source and Varchar2 on destination).
Do we have to create any rule or dml handler to handle this or Streams will automatically take care of it?We are oracle 10g.
View 1 Replies
View Related
Oct 28, 2009
I am using prebuilt MV to perform replication of about 300-400 master tables from one database to another database. I am wondering about the impacts on triggers in general replication.
IS there a general rule to enable/disable a trigger before a refresh.
View 1 Replies
View Related
Mar 21, 2011
Can we use Materialized views in Streams like how we use tables... ?
View 11 Replies
View Related
Apr 29, 2013
I have a question about GG Sequence Replication and Triggers. My main database, which I would like to replicate on another server, is highly dependent on sequences for assigning surrogate keys to every row in every table in the application. I know that I need to add Sequence support to my source database (plus supplemental logging, etc), but I'm curious about the target database.
I do not anticipate allowing Read/Write access to this database - we are migrating from 10.2.0.4 (source) to 11.2.0.3 (target) on a new platform, and I want to keep the 11g database up-to-date with our production data until it is time to begin the actual conversion of our application. My thinking is that if I use the SUPPRESSTRIGGERS dboption in my Replicat session, this should take care of the use of the Sequences for assigning the surrogate key values, and the data should add to the tables normally without any intervention by the sequences/trigger combination. I know I will have to manually "correct" the sequences on my 11.2.0.3 database whenever I want to open this database up for use, but I have a script for this ready to go.
Also, in my source database, I am using Oracle Context indexes for generic name searching - this feature creates a number of DR$ named tables in the main application schema that I am replicating (approximately 50 of them). I am assuming that I should EXCLUDE these tables from the replication, as the context indexing should automatically update them as changes to the underlying data are applied via the replication of the indexed tables.
View 1 Replies
View Related
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
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
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
Sep 4, 2009
i need to set up a central server with all the master tables and two other local database which will hold the updatable materialized view of the master table...the databases must be synchronized with central server..and user will work on the materialized view database...
View 10 Replies
View Related
Jun 2, 2010
this Topic was already discussed in "Physical standby on oracle 11g - Reporting needs" but without finishing.
View 5 Replies
View Related
Feb 26, 2008
I'm looking for solutions to make real time replications from an Oracle 8i database.
For more details :
- we have an existing database with Oracle 8i
- we want to replicate this database: the replicated database will be used by a web application.
- we need the replication to be in real-time (we wish to have the shortest lag)
For the moment i didn't find much information on the web and it seems that replication solutions for the version 8i are quiet limited.
Some more details :
- The responsible want imperatively that the web application work with a replicated database, without interacting directly with the original one.
- An evolution of oracle should be done but within some years so were trying to find a solution rapidly with 8i.
View 3 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
Apr 1, 2010
what are the recommended network requirements for implementing Oracle Multi Master replication.
View 1 Replies
View Related
Oct 8, 2013
I have requirement where replication should be done between two 11gR2 RAC on Standard edition.I have following queries,1. Does Standard edition support DDL capture?
Im asking this because on
[URL]......
it says "SE1/SE: no capture from redo" what that really mean ??2. Can it be possible to configure capture at schema level and skip only some of the tables / triggers ?
View 2 Replies
View Related
Oct 6, 2010
Is it possible to call procedures and functions in Database Triggers?
View 1 Replies
View Related
Sep 14, 2012
I am soon engaging in a project where we will deploy three triggers to each table in a customers live database. One each for Inserts,Updates and deletes.
These triggers write or update a row in a separate table on a different schema.
We have experienced locking issues on these tables previously with SQL Server. Are there settings we can look at to improve performance?
View 2 Replies
View Related
Sep 17, 2008
I need to do a Oracle replication between databases. One resides on Oracle 8i which needs to be replicated to a database on Oracle 10G.
Is oracle replication supported between dissimilar versions of Oracle?
View 3 Replies
View Related
Apr 9, 2012
I want to configured A Synchronous replication in oracle 8i. Noe using GUI i am bale to create replication but whatever i modify it remain in the transaction i have to manually run the job ,
I also tried to configured continuous asynchronous configuration by setting delay rate 500000. but it was also not working.
View 2 Replies
View Related
Feb 11, 2010
Is it possible to replicate table data on real time from sql server (2005 32 bit or sql server 2000 32 bit)to oracle 10g running on linux 64 bit? If yes then what are the steps.
It will be one way replication from sql server to oracle. Which option is best sql server dts or Oracle Stream replication to replicate table data.
View 3 Replies
View Related
Apr 16, 2013
I have my database structured to where i have my tables, constraints, and Rows populated in each table which is 4 tables in particular. for one I want to be able to List all four tables with its content and constraints all at once. What i want to with the PL/SQL is to be able to program a prompt to where the user can just enter the individual order information and the data goes to what ever appropriate column or Table it needs to go to. Now should I be mostly be using procedures, functions or triggers to make this possible.
View 9 Replies
View Related
Jul 1, 2010
Im going to create a trigger(insert or update) for a table A, when it fires inserted or updated columns are should be insert or update into another table say table B, one column(Processed_time) in table B where its value will be by subtracting two columns in table A(response_time,Submission_time) all are timestamp type.
How to update(Processed_time) in table B by subtracting above two columns in table A.
View 2 Replies
View Related
Jul 14, 2012
How can we overcome recursive triggers in oracle SQL?
View 7 Replies
View Related
Dec 15, 2011
I need to print out all the triggers/procedures from an Oracle 9iForms. When I open an Oracle 9iform in the Oracle form builder, I click on FILE/CONVERT but cannot see the triggers/procedures.
View 2 Replies
View Related
Jul 9, 2008
Triggers for oracle database. What I am trying to do is copy original data from one table to another table prior to an update, insert, or delete occurring. Basically we are trying to keep a transactional history, with out having to restore the data. Here is what I have created to date, however it is not executing consistently.
CREATE OR REPLACE TRIGGER DATA_COPY BEFORE
INSERT
OR DELETE
OR UPDATE ON "DB1"."TABLE_1"
[code].......
View 10 Replies
View Related
Jul 26, 2012
For triggers, is it possible to do separate actions on insert , update and delete. For example, if insert is the case, do select; if update is the case, do select from another table and so on?
View 1 Replies
View Related
Oct 17, 2012
I am new to Oracle Workspace Manager. I have a trigger that fills my IDs every time i insert a record. I created the trigger before I enable the version of my table. After version was enabled in my table, I can no longer find my trigger but have these triggers instead:
OVM_DELETE_7 OVM_INSERT_7 OVM_UPDATE_7
What I wanted to do is Query the triggers that I created on my tables. Is there a way to do that without disabling the version on my table? I have too many version-enabled tables and that would be a hassle disabling the version in every table just for that query.
View 1 Replies
View Related
