Replication :: DATABASE LINK with GROUP BY SQL Statements?
Jun 21, 2012
We have Oracle Database Link linked to MySQL . We want to force executing remote statement that contains group by on the remote MySQL server and let MySQL do the aggregation instead of Oracle.
We tried also DRIVING_SITE but it doesn't work and the query that was sent to MySQL from Oracle DB Link didn't include GROUP BY and it looks like that the Group by was executed on the local Oracle.
Is there a way for force executing GROUP BY statements on remote DB instead of the local Oracle DB?
I am working on materialized view replication but i have an crucial issue. I have a refresh group which contain many MVs some are Read only and some are update but all are in Fast refresh mode (logs are created on both replication and MV sites on these objects) and are referentially integrated to each other i.e they have parent child relationship with each other as at replication site on base tables.
When refresh group job runs to refresh these MVs i got the errors
ORA-02292: integrity constraint (string.string) violated - child record found
After that job status has changed to break,but in the same time all the pending transactions at the MVs sites are pushed to Replication site i e ( Base tables at replication site and MVs of these tables are synchronized).In order to change the status of job to Normal, i have to disable some constraints(not all) on the MVs and manually run the job .After that job status will become Normal even i enable these constraints with validation.
What will be the reason ? Are there some MVs trying to complete refresh instead of Fast or increment refresh in this refresh group?If yes how i can trace out MVs and solve this problem?
Some other information , Replication site running with 10gR2 Enterprise Edition. Materialized View site running with 10gR2 Slandered Edition.
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...
I have been out of work for 2+ years. Am about to start a job next week doing Oracle back end, Forms, and Reports development among other things. I was asked if I could take a look at 3 report requirements and give an estimate on how long it would take to correct errors in these reports. All I have is a user requirement document stating what the report is currently doing and what it should be doing, a partial screen print of an Oracle Form showing correct data, and a sample report page showing incorrect data.
I am finding it rather difficult to give an estimate without seeing tables, relations, code, etc. Is it me or does this seem nearly impossible?I do not have access to their system yet so cannot view the database or run select statements, run the report, etc. All I have are the documents I listed above.
Can we execute more than one insert statements at a time (eg 10) in database and givecommit at the end of insert statements or else give a commit one by one after each insert statements ?
How I can maintain a replication scheme from a production database and a standby. I was watching the advanced replication methods of Oracle, but what I want is in the evening to run a process and modify the database incrementally and thus leave until the next night.
And the server I want to allocate to the standby database, also implements other processes, so my settings would be:
Production: Oracle Database 11g Linux 5.5 Standby: Windows 2003 Database 11g
Maybe that data is important, let me make clear that what I want is that the database is updated incrementally.
I have a table MYTABLE in database mydb1 duplicated via materialized view and materialized view log and refresh_snapshot commands to a MYTABLE on mydb2 database.
I like to duplicate this table MYTABLE to a third database mydb2, using the same method (materialized view and refresh_snapshot command).
Is it possible ? What's hapend to the materialized view log where I launch a refresh_snapshots on mydb2 ? How is this materialized view log truncated ?
There is a database db1 which has user U1 in in it contains T1 as table.
Likely,
There is also another database db2 which also has a user named U2 containing table T2 in it.
Now
I want to use the concept of JOINS and Join Table T1 of database named DB1 and Table T2 of database named DB2 and access from database named DB3 using Materialized View Concept.
what shall i do to access tables of DB1 and DB2 from database DB3 using Materialized View.
I am planning to move the databases in multi master replication from HP-Ux to AIX server. I am planning to use export/import to moving the database. But do I need to drop the replication administrator user before I do export/import?
I am implementing GG 11g r2 for 12C database. But i am getting below error. My question Why Goldengate needs specific package ... Since this is homogeneous & heterogeneous.
/u01/12c_database_software/goldengate/dirtmp. 2013-08-30 05:28:44 INFO OGG-01513 Oracle GoldenGate Capture for Oracle, ext1.prm: Positioning to Sequence 66, RBA 25067536, SCN 0.0. 2013-08-30 05:28:44 ERROR OGG-01028 Oracle GoldenGate Capture for Oracle, ext1.prm: ORA-06550: line 1, column 7: PLS-00201: identifier 'SYS.DBMS_INTERNAL_CLKM' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored. 2013-08-30 05:28:44 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, ext1.prm: PROCESS ABENDING.
Database details ---------------- SQL> select object_name, object_type from dba_objects where object_name='DBMS_INTERNAL_CLKM' and object_type in ('PACKAGE');
no rows selected
SQL> select object_name, object_type from dba_objects where object_name='DBMS_INTERNAL_CLKM';
I have a clients who currently implementing an application that using Oracle 10g in 20 distributed location. Each location will have its own database server and locally managed. I plan to create a Disaster Recovery (DR) Centre for this client in centralised location. I plan to setup 20 Application Server but only one database server with 20 instances. My question, can the Dataguard manage the replication between 20 database (with single instances) and single database (with 20 instances? The reason we design such way is to reduce the license cost of Oracle.
There are 2 databases, database A and database B. Database A is Oracle 11.2.0.2 which runs on linux and Database B is Oracle 11.2.0.2 which runs on windows xp machine. In database A, there are 100's of tables which are being updated every 10 minutes or 15 minutes. For reporting purpose, the developer wants to run report for the tables. But since database A is being updated every now and then, generating reports takes almost 15 to 20 minutes. So the reports can be generated in Database B. Once in a day the database B should have the updated data from database A so that the reports can be generated in database B with less time. What could be the best solution for the database B to have the updated data on daily basis from database A in oracle?
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 ?
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..
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.
I would like to know the Replication method which is fast and the best approach,we need two schemas to be moved/replicated to a new reporting database.It appears that data is to be flown in one way,do we proceed with Materialized view replication or please clarify about Oracle Streams and Advanced replication. what are the factors to decide the replication method.
I'm trying to group sets of data based on time separations between records and then count how many records are in each group.
In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5
SELECT AREA_ID AS "AREA ID", LOC_ID AS "LOCATION ID", TEST_DATE AS "DATE", TEST_TIME AS "TIME" FROM MON_TEST_MASTER WHERE AREA_ID =89 AND LOC_ID ='3015' AND TEST_DATE ='10/19/1994';
[code]....
Group 1 = 8:00:22 to 8:41:22
Group 2 = 11:35:47 to 11:35:47
Group 3 = 15:13:46 to 15:13:46
Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.
I want to set up advance replication for 3 master site (multimaster) I created 3 master site named orc1,orc2,orc3 and followed up oracle replication management of API book instruction I created 2 tables(tes1,test2) in hr schema in all 3 master site with the same data. then I created the following steps
1-CONNECT repadmin/repadmin@orc1
2-Create the master group named hr_test_repg
BEGIN DBMS_REPCAT.CREATE_MASTER_REPGROUP( gname => 'hr_test_repg'); END; /
4-add tables test1 and test2 to the group
BEGIN DBMS_REPCAT.CREATE_MASTER_REPOBJECT( gname => 'hr_test_repg', type => 'TABLE', oname => 'test1',
[code]....
I could create DBMS_REPCAT.GENERATE_REPLICATION_SUPPORT for test2 but not for test1 and it produces error
RROR at line 1: RA-23309: object hr.test1 of type TABLE exists RA-06512: at "SYS.DBMS_SYS_ERROR", line 105 RA-06512: at "SYS.DBMS_REPCAT_MAS", line 2552 RA-06512: at "SYS.DBMS_REPCAT", line 562 RA-06512: at line 2
I want to replicate single table data/modification from source db to destination DB.procedure or steps how to create replication between source and dest db for single table.
I would need to create a physical standby database for DR without using data guard. I like to know in more details like
1. What would be required to switch over to standby database in case of failure of primary database? Is it going to be a manual process each time, or can be automated using scripts? 2. How the archive logs will be applied to standby database in timely manner to sync with primary database? 3. How the Primary database will be synch with Standby database once the issue resolved on primary database. 4. Where do I get a complete script to create physical standby database on Windows platform?
I read that rownum is applied after the selection is made and before "order by". So, in order to get the sum of salaries for all employees in all departments with a row number starting from 1, i wrote :
select ROWNUM,department_id,sum(salary) from employees group by department_id
If i remove rownum, it gives the correct output. Why can't rownum be used here ?
