RE: DB Merge Ideas?

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Sat, 20 Dec 2014 05:50:02 -0500
Message-ID: <0cc501d01c42$b5c302e0$214908a0$_at_rsiz.com>

A few notes:

the Hong Yuan article is good reading. That’s got most of the things you need to look for, especially regarding what I call “namespace” collisions. You wrote you have no identical table names, but I hope that is shorthand for you having already considered collisions. Schema name collisions, even without table, cluster, index, package, function, synonym, … collisions are possibly a security concern since even if the two have no collisions, they now have the union of former access. Merging companies that have not decided whether two HR departments, for example, will now see corporate wide or have a departmental view of their former independent company is an easy to understand illustration of this POTENTIAL problem.
Seth meant “new database.” (Personal crusade since 6.2 added consequence to the difference between the name of the set of files [database] and the name of a set of programs and memory running on the set of files [instance]). His time estimate is probably correct a lot of the time, but if you have TONS of packages and procedures it will be different. IF you have TONS of things that will need to be compiled post merge that may be a bigger elapsed time than the metadata for the data plugin, especially if you have no holdover dictionary managed tablespaces.
Nabil’s stuff is also good, especially about looking beyond the database itself for “namespace” collisions. Performance stuff, such as isolated physical storage for ETL flat file locations now being a collision is also germane

Very likely using transportable tablespaces will work for you and it is certainly worth a test. Just keep an eye out for problems and remember to avoid the analog of compulsive tuning disorder of fixing “just one more problem” and re-consider alternative strategies (if you do start running into issues.)

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Michael Cunningham Sent: Friday, December 19, 2014 2:00 PM
To: Seth Miller
Cc: nmjamaleddin_at_multiservice.com; oracle-l_at_freelists org Subject: Re: DB Merge Ideas?

Thanks everyone, yes, multitenancy cost is going to prohibit that option. We are already being asked to look into a conversion to MySQL.

I'm going to give transportable tablespaces a shot. If I get it working right I think we could make the downtime around 5 minutes. Hmmm, I could be way off, but that's what I'm going to try.

On Fri, Dec 19, 2014 at 7:35 AM, Seth Miller <sethmiller.sm_at_gmail.com> wrote:

Michael,

Transportable tablespaces is most likely the way to go. If you time it correctly, it will take longer for the clients to reconnect to the new instance than it will to switch the tablespace to the new instance.

Seth Miller

On Thu, Dec 18, 2014 at 8:31 AM, Nabil Jamaleddin <nmjamaleddin_at_multiservice.com> wrote:

Some random thoughts after a nice cup of coffee:

Don’t forget to make sure you won’t have role, public synonym, or (hard to imagine ) sequence problems. If your character sets are the same between all the databases, I like the idea of transportable tablespaces.

Tables having the same name is not a problem at all.

Directories could be a problem. If two databases use the same directory name/path it might cause problems.

Profiles could be an issue but doubtful. Don’t forget to bring over oracle jobs, cronjobs and to make sure your database monitoring will not have problems.

Don’t forget to keep in mind the SqlNet problems of redirection of databases.

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Michael Cunningham Sent: Wednesday, December 17, 2014 5:51 PM To: oracle-l_at_freelists org
Subject: DB Merge Ideas?

Hello list,

We have 16 similar instances that we want to merge into a single instance. I'm looking for input on the best method. Below is all I could think of to help.

Oracle 12.1.0.1 on Linux

All instances exist on the same server.

ASM is being used. Currently one diskgroup for all instances.

No tables have the same name.

The tablespaces that matter do not have the same name between databases (one db is TBS0, another is TBS1, and so on).

Each is around 500GB (5.5TB total).

We do not have golden gate license.

Any thoughts?

I'm thinking of transportable tablespaces, but I'm looking for other input. I need to minimize downtime.

Michael Cunningham

This email is intended solely for the use of the addressee and may contain information that is confidential, proprietary, or both. If you receive this email in error please immediately notify the sender and delete the email.

Michael Cunningham

--

http://www.freelists.org/webpage/oracle-l Received on Sat Dec 20 2014 - 11:50:02 CET

This message: [ Message body ]
Next message: William Robertson: "Re: where clause filter from an array or use temp table?"
Previous message: Stefan Koehler: "Re: where clause filter from an array or use temp table?"
Maybe in reply to: Michael Cunningham: "DB Merge Ideas?"
Next in thread: Juan Carlos Reyes Pacheco: "Re: DB Merge Ideas?"

Contemporary messages sorted: [ by date ] [ by thread ] [ by subject ] [ by author ]

Original text of this message