Replication :: How To Use Materialize Views In Streams
Mar 21, 2011Can we use Materialized views in Streams like how we use tables... ?
View 11 Replies
ADVERTISEMENT
Replication :: Oracle Streams / Tried Schema Replication Through Enterprise Manager Grid Control
Feb 22, 2012Actually 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..
Oracle 11g Streams Replication
Apr 18, 2013Am 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.
Replication :: Oracle Streams Or MVs Or Database Triggers
May 27, 2011We 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 ?
Replication :: Oracle Streams Vs Share Plex
Aug 31, 2011Till 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?
Replication :: Oracle Streams - Column Datatypes In Some Of The Tables On Source And Destination Are Different?
Jul 20, 2009We 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.
Replication :: Views Or Streaming
Oct 13, 2008I need to know that what is best approach for replication. Materialized View or Streaming. We have to replicate a subset of database at different nodes.
View 3 Replies View RelatedReplication :: Materialized Views?
Oct 25, 2011for following two questions.
1. I have created a materialized view with following syntax in 11g R1 database.
CREATE MATERIALIZED VIEW fr_emp_bonus_record_vw
BUILD IMMEDIATE
REFRESH COMPLETE
START WITH SYSDATE NEXT SYSDATE+1/48
ENABLE QUERY REWRITE AS
SELECT person_id, <columns>
This MV will have around 300K records and this MV will be used in other queries based on person_id. So I have created an index on mv.person_id with following syntax. I have chosen REFRESH COMPLETE, I am assuming that, Oracle truncates this table and recreates it. When it recreates this MV, does it analyze automatically or do I need to do it manually.
CREATE UNIQUE INDEX mv_u1 ON mv(person_id);
2. It takes around 2 minutes to populate 300K. While it is populating the data, if somebody accessing this view or query where this view is being used, are they going to get any error?
Replication :: Materialized Views On 3 Database?
Apr 8, 2012There 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.
Replication :: Sub-query In Materialized Views
Apr 1, 2009I have a view with complex query that have grouping and aggregations in sub-queries.
Now we would like to change it to a materialized view. However, materialized view does not support GROUP BY clause in sub-query. The solution is to create several view for the sub-queries and then create a materialized view based on it. It seems it can work, but I guest every time complete refresh is required.
Replication :: Materialized Views Not Getting Refreshed
Oct 3, 2009I have a database link between 2 databases and that works fine. Now the tables of one database are having thier materialized views in the other. The materialized views are not getting refreshed. They hang when i try refreshing them. I have tried everything said on the forums. I changed the job_queue_processes. Also changed the query_reqrite_enabled to true. Still it won't work.
View 1 Replies View RelatedReplication :: Fast Refresh Of Materialized Views
Jun 19, 2008CREATE Materialized View log on t1 with rowid;
CREATE Materialized View log on t2 with rowid;
CREATE Materialized View log on t3 with rowid;
CREATE Materialized View MV_test
BUILD IMMEDIATE
FAST REFRESH ON COMMIT
[code].......
I have created a Materialized View by joining the above 3 tables. The Materialized View logs with ROWID has been created on each of the 3 base tables. One of the base tables has around 160 million rows and i expect the Materialized View to have the same number of rows.
The Materialized View has been built for FAST REFRESH on COMMIT. The SELECT list of the CREATE Materialized View statement includes all the ROWIDS from each base tables for doing the fast refresh.
The DBMS_MVIEW explain view shows that the view is capable of doing fast refresh on any DML. The Materialized View took 3 hrs to get built. I have also created indexes on all the three rowid columns (t1rowid,t2rowid,t3rowid) on the Materialized View.
Now, When i do any update on one of the base table and do a commit,
UPDATE t1 set value=500 where tx_id='1234';
The commit is taking around around 15 minutes. I have generated the trace and had a look at it, and it shows that, to refresh the Materialized View, ORACLE is first deleting that record from the Materialized View and then inserting the new record from the base table to the Materialized View.
This delete done by oracle is being done on the basis of the rowid of the updated record stored on the Materialized View log,
However its not using the index created on the t1rowid of the Materialized View and doing a full scan of the MV and hence the delete is taking a lot of time. The delete statement is using a hint NO_MERGE and HASH_SJ which is causing it to do the full table scan of the MV. why is ORACLE not using the index on the t1rowid column of the MV to do this delete?
Also to do the insert in the MV, its selecting the new changed value from the base tables t1,t2 and t2. Here again its doing a full table scan of the base table t1, rather then using the rowid directly or the index coulmn.which is taking a lot of time.
Replication :: Gather Statistics On Materialized Views
Jul 15, 2009Any best way to gather statistics on Materialized Views.
View 1 Replies View RelatedReplication :: Alternate Solution For Materialized Views
May 17, 2009I have a Problem regarding Data Replication, I am using Standard Version of Oracle 9i,I am having 5 remote servers , I am replicating the data from all of these servers using Materialized views on a Centralized server , I have created 5 schema for them, there is a application which is Consolidating the Data from all the locations.
When I create materialized views from these Materialized views I am facing problems to Fast refresh them as I am using "Union All" to combine data of all Users (Error is ora -12004) . I would like to know that is there any other way to do this, also I have tried all the ways to solve the problem of Ora-12004.
Replication :: Materialized View Based On 2 Views
Apr 1, 2009I have a Materialized view based on 2 views. I modified one column in both the views.Do i need to refresh the Materialized view.
E.g. :
In view1 and view2 i changed column like this
nvl(test.no,'n/a') testdata --changed
nvl(test.id,'n/a') testdata --original
CREATE MATERIALIZED VIEW MV_test
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
[code].........
Replication :: Replicating Master Site Via Materialized Views
Mar 4, 2011while replicating the master site via materialized views, in mViewGroups i issued:
BEGIN
DBMS_REFRESH.MAKE (
name => 'mvadmin.hamza_refg',
list => '',
next_date => SYSDATE,
interval => 'SYSDATE + 1/1440', -- for test purpose i used such a small interval
implicit_destroy => FALSE,
rollback_seg => '',
push_deferred_rpc => TRUE,
refresh_after_errors => FALSE);
END;
in my previous attempt it worked all fine... i had a change in senerio, so i did my replication again from scractch using the same old spool in a new installation but even after 3 tries on replicating from scratch my objects are not getting refreshed automatically. even when i attempt to refresh the group it does not work. it works only when i refresh each of the participating object of the group manually.
Replication :: Replicate One Table In Two Materialized Views At Same Time
Nov 11, 2008I have a table in one database and I want to replicate it in another two databases with materialized views. The refresh may be fast. Is this possible?
View 1 Replies View RelatedReplication :: Pausing And Resuming Of Refreshing Materialized Views
Oct 15, 2011We have some materialized views in our environment which refreshes after every 1 hour. In the same environment, we have some big processes and we do not want the performance of these big processes to get affected because of refreshing these materialized views.
if there is a way to pause the refreshing of materialized views before running the big processes and later after the big processes are completed, then resume the refreshing of the materialized views?
One way I can think of is to drop the materialized views and create the materialized views back.
Replication :: Materialized Views Refresh In Snapshot-group?
Mar 24, 2010How to refresh the dependent materialized views in a snapshot group. It refreshes in the order we add the materialized views to snapshot group?
Here below is the scenario.
MATVW_A
CREATE MATERIALIZED VIEW MATVW_A BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * FROM EMP;
MATVW_B
CREATE MATERIALIZED VIEW MATVW_B BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * FROM MATVW_A;
MATVW_C
CREATE MATERIALIZED VIEW MATVW_C BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * FROM MATVW_B;
I created a snapshotgroup SNGRP with all the above materialized views.
DBMS_REFRESH.MAKE(name => 'SNGRP',
tab => 'MATVW_A,MATVW_B,MATVW_C',
next_date => TRUNC(sysdate) + 1,
interval => 'SYSDATE + 1');
The order of refreshing the materialized views would be like first materialized view MATVW_A should get refresh then after MATVW_B,MATVW_C.
As they are dependent views.Is there anyway where we can mention the order of refreshing materialized views in snapshot group.
Replication :: MV Views To Snapshot Data Pros And Cons
Nov 4, 2012I have 3 reporting tables with 2.2 million records each being rebuilt nightly. The data is used online 24/7 by users and thus, snapshot tables are being built from the refreshed reporting tables. The current method to do this:
delete from snapshot table;
insert into snapshot table (select * from report table);
<repeat for other 2 tables>
commit;
This seems to me to be resource intense on the system even though the table is defined with nologging option.
Is it better to create a MV (select only with refresh complete on demand)? The query is very simple without joins so it at first seems like overkill. However, I am also seeing that dbms_mview.refresh allows for an atomic option. Thus, if 1 of the 3 MVs fails during refresh all 3 rollback, which is a nice feature.
Are there better ways to replicate a snapshot table that I've missed? Is a delete and insert strategy a bad idea?
Replication :: Where Logs Will Be Logged For All Materialized Views Transactions
Jul 28, 2008I have one question on MVIEWS.....
Where the logs will be logged for all the materialized views transactions (I mean a particular table)?
Is there any Data Dictionary Table were i can find this .
Replication :: Setup A Refresh Group For Nested Materialized Views
Mar 25, 2009Suppose I have materialized view A,B,C. Now I have a new materialized view D which is created from A,B,C. Can I put D into same refresh group?
I am not sure whether D will be refreshed first before A,B,C, resulting that D do not get any update If that is the case, the result will be wrong
If we cannot use refresh group to update nested materialized view, what should we do?
Replication :: ORA-12013 / Updatable Materialized VIEWS Must Be Simple Enough TO DO Fast Refresh
Aug 20, 2009CREATE MATERIALIZED VIEW LOG ON ABC;
CREATE MATERIALIZED VIEW MV_ABC
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE+20/(24*60)
FOR UPDATE
AS
SELECT * FROM ABC WHERE TMSTP> SYSDATE-1;
It is working fine. Appended the Query with WHERE clause in MV_ABC MVIEWS.
DROP MATERIALIZED VIEW LOG ON ABC;
DROP MATERIALIZED VIEW MV_ABC;
CREATE MATERIALIZED VIEW LOG ON ABC;
CREATE MATERIALIZED VIEW MV_ABC
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE+20/(24*60)
FOR UPDATE
AS
SELECT * FROM ABC WHERE TMSTP> SYSDATE-1;
ORA-12013: updatable materialized VIEWS must be simple enough TO DO fast refresh
Streams :: Streams Apply Process Keeps Growing PGA?
Nov 1, 2013A 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 RelatedStreams :: Setup Streams Failed
Jun 26, 2013I 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]....
SQL & PL/SQL :: Materialize Hint And IOT
Mar 29, 2012I'm very happy about materialize hint and I use it a lot. But is it possible to set some hint indicating that I want to materialize some inline view results (temp table transformation) as IOT (index organized table)?
IOT must have primary key. Ok, could it be all the columns in listing order? That would convenient. I know I can use global temporary with index, but this forces me to split one statement into parts.
SQL & PL/SQL :: Unable To Create Materialize View (Refresh On Fast Commit)
Nov 19, 2010I am not able to create materialized view with 'Refresh Fast on Commit' when the table is residing on the another user environment, I am not able to find the reason why?
You can re-generate the scenario at your end with the following code-
--Login to System-
create user manu identified by manu;
grant connect, resource to manu;
create user arpit identified by arpit;
grant connect, resource to arpit;
grant create materialized view to manu;
[code]....
but when I am doing it with on demand options, materialized view is being created-
--Login to Manu-
create materialized view arpit_view
build immediate
refresh on demand
as
select * from arpit.arpit
/
Server Administration :: Graphical Analysis Of Dynamic Performance Views (V$ Views)?
Nov 5, 2012is there some open source or free tool which can graphical display V$ Views. Can TOAD do that in a good maner?
in UNIX there is the "sar" command, but a Java tool "ksar" for displaying the statistics in user friendly fashion.
Refresh Materialized Views Based On Remote Views?
Aug 4, 2011I have a created a materialized view which is based on a view on remote database. Now how do I refresh the view.
Materialized view is created by
CREATE MATERIALIZED VIEW mv_employee_name
AS SELECT EMPLID, EMPL_NAME
FROM VEMPDATA@REMOTEDB
WHERE REGION = 'US';
I am wondering how the refersh happens or how do I specify the refresh clause.REFRESH FAST option is looking for VIEW LOG on the master table but in this case its a remote view, so I cannot create any object on remote db.
PL/SQL :: Get List Of Materialized Views / Views Using Column Of Table
Oct 17, 2013I am removing sal column from table tab_emp; i want to check whether any materialized view or view using this column by querying using data dictionary :- if i use like condition against query column of all_mviews it is throwing error sicne it is long data type. is there a way to search it without creating any function and use it in a query.
View 3 Replies View Related