Akhila Selva Ganesh, December 02, 2016 - 2:50 pm UTC
Hi Chris
Thank you! I understand that we need to change the NLS settings of the Source database prior to migration and then migrate the data. However, if I need to retain the source database NLS settings as it is while only the data in the target database needs be converted and stored, is this possible?
December 02, 2016 - 2:58 pm UTC
The NLS settings are for your client. As long as you're transferring from a number to a number (with no conversion in between) you'll be fine.
Akhila Selva Ganesh, December 02, 2016 - 3:30 pm UTC
Okay but I am not sure if this is applicable only if target database is also Oracle. I missed to mention that target database is MS SQL Server. So neither an export/import of the data from Oracle in desired format nor an active replication from Oracle to SQL Server support this?
December 02, 2016 - 4:56 pm UTC
A tiny detail! I would suspect that you'll still be fine.
But if you're looking for help migrating off Oracle to SQL Server you'll need to find the MS equivalent of AskTom ;)
Akhila Selva Ganesh, December 05, 2016 - 10:56 am UTC
Yes I know :( and I agree!! But we are looking to migrate one of our DB to SQL Server. To clearly understand, can you please elaborate the term 'client' ?
December 05, 2016 - 11:57 am UTC
Client is anything that connects to the database.
Akhila Selva Ganesh, December 05, 2016 - 1:00 pm UTC
Thanks Chris!!
Alex, July 17, 2017 - 9:06 pm UTC
Hi team,
I have a need to move some rather large tables out of a shared schema into their own self contained schema for their respective application. For average to small size applications I'm good with using datapump. However, as you know, for a TB or so of data takes a long time and I am exploring trying to use a Golden Gate zero downtime approach. I haven't been able to find any examples or documentation for achieving this in my situation. When you do the initial load piece to the migration, can it be any form of an expdp or does it have to be a full or schema level? This will be happening inside the same database; it's just to break things up.
Thanks.
July 18, 2017 - 9:47 am UTC
What exactly are you doing? Just moving tables within a database to new schemas? Can't you do create table as select to build the tables in the first place? And edition-based redefinition to switch over with zero downtime?
Alex, July 18, 2017 - 3:23 pm UTC
Yes, we just need to move tables + objects to a different schema in the same database. We looked at using dbms_redefinition, but it didn't appear you could use that across schemas. Also, this needs to be an easily repeatable process. Trying to do CTAS + get all dependent keys etc then grab all code plus use dbms_redefinition sounds very cumbersome.
July 18, 2017 - 4:23 pm UTC
You're repeatedly moving tables from one schema to another?! Surely this is just a one-off thing? What exactly is your requirement here?
Yes, DBMS_redef has to be in the same schema. So it's not going to help you.
Alex, July 18, 2017 - 5:34 pm UTC
We have a bunch of legacy applications sharing a schema. On the application side they are being converted to a microservice architecture. On the database side we want a corresponding bounded context model aka their own schema. Most of them are a reasonable size for datapump. But we have a handful that won't be sensible for that.
So yes it is a one shot deal per application, but there are half a dozen or so apps.
Our DBAs said they have done a GG migration like this before, but they do not have my full confidence and I am trying to fact check whether this can be done for a subset of tables.
Any other ideas are welcome.
July 19, 2017 - 3:40 pm UTC
I see. I get why you want to split them out. But do you really need to? Seems like a lot of work for minimal benefit...
Why not just create app users for each microservice that only has access to the tables it uses?
But yes, you can use GG for a subset of tables.
To Alex : datapump twists
J. Laurindo Chiappa, July 18, 2017 - 8:06 pm UTC
Hi, Alex : sometimes I need to do something like this, and (besides Goldengate, a very capable BUT very expensive tool) the best option in my experience is to use datapump...
For small/medium-sized schemas you could use the normal/common datapump client, but for larger schemas the best alternative (performatic but cheap, repeatable and customizable) is the datapump API using the Parallel options , see
http://dbaora.com/oracle-clone-schema-using-plsql/ for an example....
Best regards,
J. Laurindo Chiappa
July 19, 2017 - 3:41 pm UTC
Thanks for sharing.
Partitioning
Christian, July 19, 2017 - 2:28 pm UTC
If your goal is to just move your table to another schema and don't change the structure you could use partitioning for that:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:752030266230#2284434496930 I have done that recently for a 30GB Table in almost no time; works like a charm.
You of course would need a Enterprise Edition License plus the Partitioning option, so the whole solution isn't very cheap when you are currently on SE. It's fast though ;)
cheers
July 19, 2017 - 3:42 pm UTC
Good suggestion. Though if they're considering GG, I'm guessing the cost of EE + Partitioning won't be an issue ;)