We help IT Professionals succeed at work.

Oracle Table Replication

geleman
geleman asked
on
I have looked and haven't gotten a good answer and I'm probably over looking something but I need to find out if it's possible to create a table in Oracle that doesn't replicate to the physical standby? It's just going to be historical table that doesn't need to be replicated.
Comment
Watch Question

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>this might help

"physical standby" to me means DataGuard not Streams.

geleman,
I cannot think of a way for a change to not be shipped over with the redo logs.

Author

Commented:
dsine:

I found that thread on the Oracle forum and went through it and looked through my packages and don't think that is going to help me.

slightwv:

I hope that isn't the case because dataguard is in place and not streams, but it seems logical that is the case.  We are working on a long term solution as far as space and creating the historical table is for temporary relief so I don't see redoing the replication to streams away from dataguard would be the way to go.  I guess it's back to the drawing board.  Maybe a better solution may be to create a separate schema with the few tables and indexes and use a database link.  I'm not sure which direction to go now that is possibly out the window.

Any suggestions?  Maybe I should open another question on that.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Data Guard is designed to be a High Availability (HA) solution.  It really isn't designed to be used the way you want.  Being able to force data loss in a HA solution sort of goes against the grain.

I would contact Oracle Support to see if there is a way.

I'm far from a Data Guard expert but as long as you can guarantee the logs are ALWAYS one way you might look into just letting the history rows propagate then truncate the table in a database job.

The problem here is I'm not sure what would happen if a row in the master site was updated and the update could not happen on the standby.  I know in Streams Replication you need to manually de-conflict these types of issues.

Author

Commented:
Yeah I don't think trying to find a work around of Data Guard would be a good solution because I could see it probably creating more problems than it solves.  The history table is supposed to cut down on the size of the table and put in on to a different partition that isn't available to the physical standby.  
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>probably creating more problems than it solves

I agree.

Can you create a second database to hold the history table then a view/database link/synonym in the local db to 'access' the historical data?

Author

Commented:
That is a possible solution but with the fact that it's a production data we would have to license the new database with oracle.  At least that is my understanding of oracle licensing, it's all confusing to me.  I was thinking that it could be stored that way possibly in a new schema with a link.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>At least that is my understanding of oracle licensing

Granted they change it ALL the time but it is normally licensed by named user or CPU.  I've never heard of number of databases being in the license scheme.

As always, You will need to run this by your Oracle Account team.


Just in case your organization somehow does licenses by database: Oracle XE is free for production use as long as you can meet the system requirements:

Any use of the Oracle Database Express Edition is subject to the following limitations;
1. Express Edition is limited to a single instance on any server;
2. Express Edition may be installed on a multiple CPU server, but may only be executed on one processor in any server;
3. Express Edition may only be used to support up to 11GB of user data (not including Express Edition system data);
4. Express Edition may use up to 1 GB RAM of available memory.

http://www.oracle.com/technetwork/licenses/database-11g-express-license-459621.html

Author

Commented:
This particular DB is licensed by user. The data set is going to range between 100 - 300 GB so XE would explode :).  So being that licensing might not be an issue and just creating a new schema with a link I have to ask about the replication again.  Would the new schema be replicated to my physical standby or are you able to not include it into replication?  I'm thinking creating a new database instance on the primary wouldn't replicate, am I correct in assuming that?  That way you would have different listener instance as well.  Also sorry to ask so many questions, I haven't worked with the replication before was just in stand alone environment previously so I'm trying to get this right before I move forward.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>This particular DB is licensed by user.

You will need to confirm with your Oracle Account Team to see if a single user can connect to multiple databases and not violate the license.

Again, they change their licensing all the time.

>>and just creating a new schema with a link I have to ask about the replication again.

Schemas, yes, they will replicate.  DataGuard ships redo logs over to the standby.  ALL activity is shipped over.

>>I'm thinking creating a new database instance on the primary wouldn't replicate, am I correct in assuming that?

DataGuard is database specific.  A new database, unless set up with DataGuard will not automatically replicate.

>>That way you would have different listener instance as well.  

Why?  A single listener can listen for multiple databases.

Author

Commented:
So basically create a new database instance with the new schema and I wouldn't have to worry about the replication process.  I was started to get a little confused because I was interchanging schema and db instance.  I think I got it now.  I guess checking the licensing on this possibility is the way to go.  
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>So basically create a new database instance with the new schema and I wouldn't have to worry about the replication process.

Yes.

>>I was started to get a little confused because I was interchanging schema and db instance.

This confuses a lot of SQL Server people when they come over to Oracle.

>> I guess checking the licensing on this possibility is the way to go.  

That is the only thing I can come up with and it keeps everything clean and simple.

Author

Commented:
I appreciate all the help.