Re: Schema duplication on the same database.
- From: Michael Elkin <melkin4u@xxxxxxxxx>
- To: andy@xxxxxxxxxxxxxxx
- Date: Sun, 30 May 2010 16:32:41 +0300
Just one correction regarding Oracle Streams usage with Standard Edition.
Yes, indeed this is possible to use Oracle Streams in Oracle 11g version but
only "synchronous capture" option , actually the option that does not redo
from redo.
"Synchronous capture" has the following limitations:
1. Captures only DML (DDLs are not captured)
2. Captures changes on table level (does not work on schema or database
level)
3. Can not work in a downstreams mode , requires to capture changes on
source database only
4. Uses internal triggers mechanism instead of logminer
Michael
On Thu, May 27, 2010 at 11:05 PM, Andy Klock <andy@xxxxxxxxxxxxxxx> wrote:
> Out of the three options you listed, it seems that number 2 (Oracle
> Replication) is your best option. I support several Oracle Replication
> environments and it is what it is. With the disclaimer that I don't know all
> the details of your environment or the needs (or volume) of your data, I
> would still be concerned about "testing changes" on your production
> database. You also have to weigh the overhead in replicating your data to
> another schema (or location). With Oracle Streams (11g Standard Edition) you
> have the benefit of applying the changes downstream (off of prod) or
> applying redo to a "standby" until you are ready to run your tests both
> sound like safer/better options.
>
>
> On Thu, May 27, 2010 at 2:55 PM, Dhimant Patel <drp4kri@xxxxxxxxx> wrote:
>
>> Sorry about missing Oracle version. Its' 10g standard.
>>
>> The purpose of the copy schema is to be able to accommodate new changes
>> for future application code release, so they would be used for testing these
>> changes. These code changes depend on external data feeds and so we couldn't
>> rely on datapump refreshes.
>>
>> As you pointed out, server side code changes could be put to freeze and/or
>> manage accordingly but we would rather have a guaranteed complete sync of
>> schemas than do such things.
>>
>>
>> Thanks,
>> DP.
>>
>>
>> On Thu, May 27, 2010 at 2:09 PM, Andy Klock <andy@xxxxxxxxxxxxxxx> wrote:
>>
>>> You didn't mention your version. Oracle Streams is available in 11g
>>> Standard Edition. ( a fine reason to upgrade ) And though Oracle
>>> Replication doesn't copy packages, procedures, etc is that really a problem?
>>> Can you control schema changes, and just apply them across the "copy"
>>> schemas?
>>>
>>> Out of curiosity, what are these "copy" schemas going to be used for?
>>>
>>> On Thu, May 27, 2010 at 1:09 PM, Dhimant Patel <drp4kri@xxxxxxxxx>wrote:
>>>
>>>> Yes,
>>>> but we need to have it on the same database and not on the standby. In
>>>> fact our standby would be our next option if this is not possible on the
>>>> same database in almost real time.
>>>>
>>>>
>>>>
>>>> -DP
>>>>
>>>>
>>>>
>>>> On Thu, May 27, 2010 at 12:17 PM, Yechiel Adar <adar666@xxxxxxxxxxxx>wrote:
>>>>
>>>>> What oracle version?
>>>>>
>>>>> IIRC, in 11g you can use standby for read only while the apply is
>>>>> working in the background.
>>>>> So, you can create a stand by database and let the other 2 users make
>>>>> reports from there.
>>>>>
>>>>> Adar Yechiel
>>>>> Rechovot, Israel
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> Dhimant Patel wrote:
>>>>>
>>>>>> Hi All,
>>>>>>
>>>>>> I have Oracle Standard Edition installed and have two schema in use
>>>>>> (call it prodA, prodB) in this database. I wanted to create two
>>>>>> additional
>>>>>> schema(s) (copyA and copyB) and want to maintain both in sync with
>>>>>> original
>>>>>> schema(s). I know streams can not be used since it is only Enterprise
>>>>>> version feature. I currently use datapump for schema duplication but
>>>>>> this is
>>>>>> little bit different - since downtime is not acceptable (hence both
>>>>>> should
>>>>>> be in sync, almost!).
>>>>>>
>>>>>> I thought of following:
>>>>>>
>>>>>> 1> Instead create new users and allow full access to prodA and prodB
>>>>>> via synonyms and hope all is ok - since we now have four users and could
>>>>>> later on face issues of objects and links all over the place.
>>>>>>
>>>>>> Didn't like this idea.
>>>>>>
>>>>>>
>>>>>> 2> Use replication for all objects and copy them over to copyA and
>>>>>> copyB. It still wont allow copying of stored procedure code, which is
>>>>>> large
>>>>>> in our case.
>>>>>>
>>>>>>
>>>>>> 3> Could proxy user can help? But I think they are for external users
>>>>>> like in the case of application servers etc.
>>>>>>
>>>>>>
>>>>>>
>>>>>> Any other ideas?
>>>>>> -Thanks,
>>>>>> DP.
>>>>>>
>>>>> --
>>>>> //www.freelists.org/webpage/oracle-l
>>>>>
>>>>>
>>>>>
>>>>
>>>
>>
>
--
Best Regards
Michael Elkin
Other related posts:
