Re: [EXT] Re: Goldengate: How to load multiple files for initial load?

Just saw this- I'm doing this kind of work consistently on the Azure side, (not as often to SQL Server and dude- SQL Server 2008 is about to be deprecated, you need to fix that... :) I can't tell you how often BCP has still saved my bacon. How often the old and trusty tools still stand the test of time. create a script, datapump it out to a flatfile and BCP it back into SQL Server. Fast and efficient every time.

I also have examples, let me know if you need help,

*Kellyn Pot'Vin-Gorman*
DBAKevlar Blog <http://dbakevlar.com>
President Denver SQL Server User Group <http://denversql.org/> about.me/dbakevlar

On Fri, May 10, 2019 at 12:04 PM Andrew Kerber <andrew.kerber_at_gmail.com> wrote:

> Ok. I might be tempted to try a database link at that point, multiple
> insert into...select * from, if you have a range of values you can use.
>
> On Fri, May 10, 2019 at 11:39 AM Livingston, Daniel <DLivingston_at_west.com>
> wrote:
>
>> It will load a single file of about 3 mill rows in about a minute or so,
>> but it means I need to run it repeatedly (apparently).
>>
>>
>>
>> The reason for going the route with GG itself was that this table
>> eventually needs to be replicated ongoing from Oracle -> SQL Server
>>
>>
>>
>> BCP seems to have the same problem: it expects a single source datafile.
>>
>>
>>
>>
>>
>> RMTFILE: limit of 2GB per file
>>
>> EXTFILE: read on a single file
>>
>> BCP.exe: read only a single file
>>
>>
>>
>>
>>
>> Almost like these utilities were last updated in 2005 or something ...
>>
>>
>>
>>
>>
>>
>>
>> *From:* Andrew Kerber [mailto:andrew.kerber_at_gmail.com]
>> *Sent:* Friday, May 10, 2019 10:32 AM
>> *To:* Livingston, Daniel <DLivingston_at_regmail.west.com>
>> *Cc:* oracle-l_at_freelists.org
>> *Subject:* [EXT] Re: Goldengate: How to load multiple files for initial
>> load?
>>
>>
>>
>> For large tables, you cant really use that method and expect it to finish
>> in a reasonable time. You will need to look at BCP, or perhaps even a
>> database link for the initial load.
>>
>>
>>
>> On Fri, May 10, 2019 at 10:54 AM Livingston, Daniel <DLivingston_at_west.com>
>> wrote:
>>
>> I have a table of around 65mill rows that I need to replicate from an
>> Oracle 18 database to SQL Server 2008
>>
>>
>>
>> Source: Oracle 18.5, Goldengate 18.1
>>
>> Target: SQL Server 2008R2, Goldengate 12.3 (this system is being migrated
>> later this year to SS 2017 – different story)
>>
>>
>>
>>
>>
>> I am trying to do this via the FILE to REPLICAT method:
>>
>>
>>
>> -- Extract to trail files on remote server
>>
>> -- Start SPECIALRUN replicat to read through the trail files and load the
>> target table
>>
>>
>>
>>
>>
>> The problem I am running into is that the replicat can only seem to read
>> a **single** trail file at a time. It cannot use wildcards or any other
>> way I have found to specify multiple EXTFILE datafiles
>>
>>
>>
>> -------------- file list (partial) ----------------
>>
>>
>>
>> Directory: C:\GGate\dirdat
>>
>>
>>
>> Mode LastWriteTime Length Name
>>
>> ---- ------------- ------ ----
>>
>> -a--- 5/10/2019 8:54 AM 2041669405 initld000000
>>
>> -a--- 5/10/2019 8:56 AM 2041669563 initld000001
>>
>> -a--- 5/10/2019 8:59 AM 2041669310 initld000002
>>
>> -a--- 5/10/2019 9:01 AM 2041669276 initld000003
>>
>> -a--- 5/10/2019 9:03 AM 1119899237 initld000004
>>
>> ...
>>
>>
>>
>> ------------------------------------------------------------
>>
>>
>>
>>
>>
>> ----------- replicat param file -------------------------
>>
>>
>>
>> specialrun
>>
>> abortonfatalerror
>>
>> BATCHSQL
>>
>>
>>
>> discardfile ./dirrpt/initld.dsc, purge
>>
>>
>>
>> targetdb ers_gg, useridalias ggadmin
>>
>>
>>
>> extfile ./dirdat/initld
>>
>>
>>
>> sourcecatalog vsopdb
>>
>> MAP vsop.masterline_repl, TARGET dbo.masterline_repl, colmap(usedefaults);
>>
>>
>>
>> end runtime
>>
>>
>>
>> ------------------------------------------------------------
>>
>>
>>
>>
>>
>> Get the error:
>>
>>
>>
>> 2019-05-10 09:36:14 ERROR OGG-01091 Unable to open file
>> "C:\GGate\dirdat/initld" (error 2, The system cannot find the file
>> specified.).
>>
>>
>>
>>
>>
>>
>>
>> Is there any way to do this? Or am I supposed to create a powershell loop
>> of some sort to process the full 57 data files one by one? I have tried
>> with a wildcard (error), multiple EXTFILE parameters (only reads the last
>> one) ... ?
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> --
>>
>> Andrew W. Kerber
>>
>> 'If at first you dont succeed, dont take up skydiving.'
>>
>
>
> --
> Andrew W. Kerber
>
> 'If at first you dont succeed, dont take up skydiving.'
>

--
http://www.freelists.org/webpage/oracle-l