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.
View 18 Repliesi have 2 tables in two different sources.
I have loaded data from source to destination.
some rows are missed while loading.
i want to know the missing rows
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.
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.
Below is my requirement.
Need to change the precision of a column in a existing table. Statistics about the table
* has over 130 columns
* More than 300 million records
* Column to modify is #121 which has data
* No primary key defined
Since the column has data, it is not possible to modify with a simple Alter.
Second option - create temp column in same table, update from original, put null in original, alter, update back from temp, drop the temp column. This approach is very expensive and time consuming.
Also the Column ID needs to preserved as #121.
how do i copy an image or a text file from ex: directory c to directory d
View 5 Replies View RelatedHow can i make replication at one way For destination only. Not for destination and target.
View 5 Replies View RelatedScenario:
Our application is using a two instance, one for the live active data and the other for the reports data. We have a process which moves the data from the live instance to reports instance every night. In a single db environment the process is working without any issues. However when we move to the RAC environment the reports db's (insert) in large table get locked and we are unable to insert data to the reports db.
What we are performing is:
Insert into my_table_rpt select * from may_table_live@db_link_to_livedb;
Issues:
my_table_rpt get locked
We have found the workaround by disable locking in destination and subsequent to the insert enable locking
ALTER TABLE my_table_rpt DISABLE TABLE LOCK;
Insert the data to the reports database table
Then
ALTER TABLE my_table_rpt ENABLE TABLE LOCK
Question:
Why does the large destination table (my_table_rpt) get locked in the RAC environment?
I need to insert data in Table A from Table B where most of the fields are identical and might some of the fields will be more in Table A.
ex: Table A: a,b,c,d,e,f
Table B: a.b,c,g,h
How to insert this using user_tab_columns in cursor and if I am giving the i/P as my table names . This needs to be configurable and reusable rather i mention all the fields in my logic.
I created a standby database yesterday and everything is working correctly. I need to make some tweaks, however, and one of those is the directory that the standby database expects the archivelogs to be.
I found that the archivelogs were being shipped to $ORACLE_HOME/dbs and named arch*.arc. Interestingly a log switch ships the archivelog minus the "arch" at the front and all archivelogs now do not have that format. I moved the archivelogs to the correct location and renamed them. I have been able to set the correct location using standby_archive_dest and can see that the archivelogs are shipped to there.
Also, v$archived_log shows the correct path and filename. The problem I have is that when I come to apply the archivelogs it still seems to think that they should be in $ORACLE_HOME/dbs and named with "arch" at the front. what parameter I need to change to tell oracle the correct path and filename to use when applying the archivelogs?
I have 2 questions, because they can be inter-related I am posting it in a single post. These queries are related to Oracle(PLSQL).
1. I am trying to increase the size of a field in a table which has almost 2 million records and the query for alteration runs for almost and hour and rollsback, wondering is there a better way of doing it.
2. I have modified the size of a field in a table from Varchar2(10) to Varchar2(20), now when I tried to rollback the modification it is not letting me to change the size from Varchar2(20) to Varchar2(10). No data has been inserted after the modification.
we have four location, four database server separately, we need to synchronize all the servers.
for example. all the server having table parts. if the parts_count column change in server. it should be change all the four servers.
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.
View 9 Replies View RelatedI 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 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 Relatedi work in an application that should make the replication from a publusher table to a remote subscribe table, using snapshot,and trigger, replication data of update works perfectly (update,insert,delete), but when i try to add or dropp a clumn in the publisher table, repplication fail, i know that my method d'ont replicate ddl statment like create or alter table, so i would like the better way to do the replication of the ddl statment without loosing tha data in the subscribe table, i'm working with oracle XE,
View 1 Replies View RelatedI'm trying to add edmx file in my project for first time. I want to choose the oracle provider ODP.net but cannot find Oracle in the data source list. I have oracle 11g installed , odp and odt installed and can access it from the solution as well. I saw the Oracle listed under data source when I tried to connect the solution to the database through server explorer. The solution is connected to Oracle database through ODP.
View 8 Replies View RelatedI 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
We have a request to configure data guard for databases on the production server. Here is my situation:
We have a backup strategy in place where the backups are being taken on a regular basis. Archive logs are deleted as soon as they are backed up.
My question now is , is there a way of configuring the Data guard in such a way that there would be no change to existing backup strategy (RMAN) and still duplicate the archive logs to another destination and not delete the archive logs by RMAN backup process on that destination while the first destination is deleted.
We will be deleting the logs in the second archive destination using a script which checks if the logs were applied.
I have a situation where I want to configure primary database (11.2) with 2 remote destinations. dest_2 is the default and points to a standby on host_2. However, I also want the primary db to continue transporting redo to dest_3 on node_3 when node_2 is taken down (planned or unplanned).
1) Configure the ALTERNATE attribute of dest_2 to point to dest_3.
2) Configure tnsnames client-side failover on primary host to point to 2 nodes (node_2 and node_3).
I have a horrible problem with EBS (actually, all problems with EBS are horrible) and I think I am stuck because of my ignorance of Forms. if I use terms that are not correct in the Forms world. The form consists of a number of named "blocks" and each block consists of a number of named "fields", not all of which are visible. I need to find the source of the data values in one of these fields. I have searched every table for a column of that name, also all the views and stored PL/SQL that I think might be relevant, but I can no find no mention of a column or variable with the same name as the field. The name does not get a hit in the online EBS tech ref manual, and only two ancient and irrelevant hits in MOS.
My question is: What are the possible sources of data for a field in a form? Have I missed any?
Source and Target db version : 10.2.0.4.0
Source Os :Cent OS 5.4 and Target OS:Sun OS 5.10
We are loading data from source DB to target DB using dblink.Source db is having 15.4 crore records of number and varchar2 data types.
Using the logic as follows.
insert into table1 as select * from table1@dblinkname[/email];
DB link is working.If I give "select * from scott.REPORT@DBLINK[/email]" in target db alone i could retrieve records.
Actual query:
Create Or replace procedure test_abcd as
begin
Insert into test select *
from scott.REPORT@DBLINK[/email]
dbms_output.put_line('Hello world');
end;
When i give the above query in sqlplus ,it is hanging. When i see the wait events i could find
"Wait Event: SQL*Net more data from dblink"
How to get the above things working.? when checked with network team they says there is no issue in the network. Do we need to modify any database/network level parameter settings.there is no firewall between source and target db.
I'm new to Oracle Forms 10g.
When I create a data block based on a table using Data Block Wizard, the block's Query Data Source Columns property is automatically populated with column definition entries corresponding to the columns of the base table.
I tried making changes to these entries, for example by changing the data types to wrong data types or even deleting them, and I found that those changes had no effect on the block at all. The form was still working as I wanted.
What is exactly the role of the block's Query Data Source Columns property.
The F1 key help says "The Query Data Source Columns property is valid only when the Query Data Source Type property is set to Table, Sub-query, or Procedure". So, explain in each context of Query Data Source Type.
Below is my requirement,
Source Table: SRC
COL1 DATE_CREATED CREATED_BY
1 27-SEP-2011 GURU
1 28-SEP-2011 SANKAR
Target Table:TGT
COl1 DATE_CREATED CREATED_BY
1 28-SEP-2011 SANKAR
I need to take the MAX of date_created record and store it in target.
I tried,
select max(date_created), col1, created_by from src
group by col1, created_by,date_created
Which is giving me 2 records which i don't want. How to get only one record out of that source table?
i have a existing table called table_1 to did some changes to it, but i need to do a source search to find where all in the code that we reference this code to ensure that there is not a variable declaration that sets this to a specific number. how do i do a source search. i alter a existing column (overbook_max) to number(2) tonumber (3)
View 8 Replies View RelatedI have an requirement to load past and future data from different source table to one tgt table.Say now we are in July
Past Data
Apr May Jun
Future Data
Aug Sep Oct
I HAVE actual sales for past month and present month which is in Table A & expected sales in table B.for every month i have to load 7 months data TARGRT TABLE..
In future the requirement may change to 6 months also. in that case the procedure has to load past 6 month + current month + 6month future so totally 13 month should be loaded.
does the source database have to be open in read-only? or can it be online at the time of exporting?
*do not take mean-while running transactions into consideration, I will do it at night when nobody's working.
We have a requirement to archive and purge the tables dynamically based on the control table input. For that we have to design a control table to gather the necessary information and passed to generate the queries.
I have designed the table as below.But in this case I am not able to handle the parent and child relation ship.
Suppose one table needs to be archived and purged and that table is parent table and it is having 2 child tables, so first required data will be inserted into target table and delete from source parent and child tables. so before deleting from parent we have to delete data from all 2 child tables.
Suppose one table needs to be purged and that table is parent table and it is having 5 child tables, so before deleting from parent we have to delete data from all 5 child tables.
To handle this scenario how can I design my control table.
For archive and purge the query like this.
INSERT INTO towner_name.ttable_name
(SELECT * FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30));
DELETE FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30);
for purge the quey is like this.
DELETE FROM sowner_name.stable_name WHERE condition_column<=(sysdate-30);
This is my control table and I have 300 tables list to archive and purge.
CID SOWNER_NAME STABLE_NAME TOWNER_NAME TTABLE_NAME CONDITION_COLUMN PERIOD UNIT TYPE
1 wedb_au OFFER_HEADER wedb_au OFFER_HEADER LAST_DATE 30 D A
1 wedb_sa OFFER_CUSTOMER wedb_sa OFFER_CUSTOMER LAST_DATE 60 D A
1 wedb_au OFFER_SERVICE LAST_DATE 1 Y P
1 wedb_us OFFER_CUSTOMER LAST_DATE 90 D P
1 wedb_cn OFFER_CARDS UPDATE_DT 2 Y P
2 wedb_au ORDER_HEAD wedb_au ORDER_HEAD LAST_DATE 120 D A
2 wedb_us ORDER_CUSTOMER wedb_us ORDER_CUSTOMER LAST_DATE 150 D A
2 wedb_sa ORDER_HEAD wedb_sa ORDER_HEAD CREATION_DT 1 Y A
3 wedb_us DELIVERY_HEAD wedb_us DELIVERY_HEAD UPDATE_DT 50 D A
3 wedb_au DELIVERY_CARDS wedb_au DELIVERY_CARDS UPDATE_DT 200 D A
3 wedb_au DELIVERY_SERVICE wedb_au DELIVERY_SERVICE LAST_DT 100 D A
WHERE TYPE=P means insert and delete
TYPE=A means only delete
wedb_au.OFFER_HEADER is Parent Table.
child tables for wedb_au.OFFER_HEADER are wedb_au.OFFER_SERVICE,wedb_au.OFFER_BODY,wedb_au.OFFER_EMAIL,OFFER_TAX.
wedb_au.OFFER_SERVICE is child table and parent for this table is wedb_au.OFFER_HEADER
wedb_sa.OFFER_CUSTOMER Stand alone table no relationship
wedb_us.OFFER_CUSTOMER Stand alone table no relationship
[code].......
I currently try to transfer a partition of a table from a source to a target DB. For first test purposes I take both SYS users to avaoid privilege problems. I created below procedure from code fragments out of the net.The partition CSS_201001 from table CTRL_SETTLED_SHIPMENTS shall be transferred (I tried both with already existing partition and non existing on target destination), but I always get the following error at DBMS_DATAPUMP.OPEN:
Exception breakpoint occurred at line -1 of DBMS_SYS_ERROR.pls.
$Oracle.EXCEPTION_ORA_39001:
ORA-39001: invalid argument value
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3043
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4769
ORA-06512: at "SYS.TEST_DP", line 20
ORA-06512: at line 2
Listing:
create or replace
procedure test_dp is
-- Handle -- unique identifier for the datapump job
my_handle number;
ind NUMBER; -- Loop index
percent_done NUMBER; -- Percentage of job complete
[code].....
I got html source code inserted into the table as CLOB (or BLOB). And I would like to search a some word from that.When I find a some value I can write this one into the column.It would be easy if this code is xml but isnt.
View 12 Replies View Related