Re: Fwd: RE: Enable novalidate contains after import?

From: De DBA <dedba_at_tpg.com.au>
Date: Tue, 24 May 2016 20:35:54 +1000
Message-ID: <57442E8A.5020000_at_tpg.com.au>



Hi Norman,

Sorry if this is no longer relevant, but my eye was drawn by the mentioning of GoldenGate and migration. Many moons ago I had a similar problem, but the other way around: migrating from 9i on small-endian (True64) to 11g on big-endian Solaris. I ended up using a combination of transportable tablespaces and GoldenGate.

You are correct that you cannot use TT cross-endian/cross-platform in 9i, but you can in 10g. I was lucky enough to have enough space to create a copy of the 9i database and upgrade that to 10g. Then I created a new 11g database on Solaris (ASM) and copied the datafiles of the non-system table spaces into the ASM storage using the built-in ftp server of the 11g XMLDB feature. Non-system tablespaces also do not need to be converted this way :)

As that database was 4TB, the copy took a while (3 days..), so we used Goldengate to bring the copy up to speed with the 9i original. We got the 9i/True64 GG installation via our local Oracle rep, as it is not available online. The clich is here whether they have an old 9i/Windows copy lying around somewhere..

Cheers,
Tony

On 24/05/16 04:15, Norman Dunbar wrote:
> Cc list.
>
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> *From:* Norman Dunbar <oracle_at_dunbar-it.co.uk>
> *Sent:* 23 May 2016 19:04:50 BST
> *To:* Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>, "Mark W. Farnham" <mwf_at_rsiz.com>
> *Subject:* RE: Enable novalidate contains after import?
>
> Hi Mark, Jonathan,
>
> Jonathan, I'll get back to you tomorrow as to the type of not null constraints involved. I think they are plain old Not Null as part of the column definition as opposed to added later check constraints. I'm at home and away from the database right now.
>
>
> Mark, the exp is from 9i on Solaris. It's an old database and is being migrated to 11gr2 in the cloud. On Windows.
>
> Much as I would love to use transportable tablespaces, we are cross platform, cross endian here so that is out, as is RMAN, and expdp/impdp - so exp/impdp it has to be. :-(
>
> Jonathan 's guess was spot on. I started with the existing process of importing one dump file, 180gb in size, and I'm now up to 5 dump files running in parallel, I've tried changing various parmeters and methods, incrementally, between tests. So far I've got it down from 89 hours 30 to a fraction over 27 hours. As you can imagine, running the tests gives me plenty time to watch and see what's taking longer etc, and come up with a plan for the next improvement. Stepwise refinement I think it's called. ;-)
>
> I'm crippled due to Oracle ignoring my buffer size when importing CLOB columns and one table of 2.5 million rows takes over 20 hours to import the data and build a couple of indexes. As there's a CLOB in the table, it's doing the commit after every row thing! Throughput on that table has been slow as 220 rows per minute while normal tables have managed 1.3 million rows per minute.
>
> This is an ongoing task, for now, as there are a couple of databases in the cloud that need to be refreshed "regularly and timeously" and I'm sure that's not what 89 plus hours comes under! :-)
>
> I'm thinking replication is probably required to keep the cloud in sync/refreshed from production. Golden Gate for example, but not from 9i to 11g it seems. DbVisit looks promising, and I might get authorisation for a trial, when I put my case to management. If it can do data obfuscation "on the fly" as there is sensitive data that is, quite rightly, not allowed to be seen by the likes of us!
>
> HTH
>
> Cheers,
> Norm.
>
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.

--
http://www.freelists.org/webpage/oracle-l
Received on Tue May 24 2016 - 12:35:54 CEST

Original text of this message