Moving entire database from on server to another
Poster wants to refresh a UAT environment monthly from a subset of production databases. dbexport/dbimport is slow (index builds), buffered-logging imports hit long transaction errors, and unlogged imports break on cross-database synonyms; onunload output files are too large to store and can't be piped/compressed. Suggestions: restore production backup to test and drop unneeded databases (or ifxclone), add more/larger logical logs so dbimport -l works, unload via external tables through a named pipe into gzip and reload into raw tables then alter to standard, or use Art Kagel's myexport/myimport. No confirmation of which approach the poster adopted.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi,
we are planning to do monthly refreshes of our UAT environment from databases
from production. Right now we are using dbexports but it is a slow process
where:
- if we import databases with dbimport -l buffered, we get long transaction
errors.
- if we import databases without logging, the import is stopped because some
databases have synonyms between them, and you cannot create a synonim from a
not logged database to a logged one and vice-versa.
Right now we are loading them using unlogged dbimport and then finish the load
of indexes / triggers / procedures where the load stopped.
I was checking the onload / onunload utility and seems to work fine, but the
output files is so big that for the smaller databases we can do it, but for
the largest will be difficult to have that free space.
I'm trying to find a way to zip the output of the onunload but seems I cannot
use STDIO here.
Can anybody help with this? maybe there is another approach to do that
transfer without using onload/onunload.
We are at:
IBM Informix Dynamic Server Version 12.10.FC8W1
Red Hat Enterprise Linux Server release 7.3 (Maipo)
On both servers.
Thanks
Restore production to test not possible ?
Paul Watson
Oninit www.oninit.com
Tel: +1 913 674 0360
Cell: +1 913 387 7529
Oninit® is a registered trademark of Oninit LLC
Failure is not as frightening as regret.
If you want to improve, be content to be thought foolish and stupid.
> On Apr 10, 2018, at 06:41, JOAQUIN GARRIDO ESPINOSA
<jgarrido.espinosa@gmail.com> wrote:
>
> Hi,
>
> we are planning to do monthly refreshes of our UAT environment from databases
> from production. Right now we are using dbexports but it is a slow process
> where:
> - if we import databases with dbimport -l buffered, we get long transaction
> errors.
> - if we import databases without logging, the import is stopped because some
> databases have synonyms between them, and you cannot create a synonim from a
> not logged database to a logged one and vice-versa.
>
> Right now we are loading them using unlogged dbimport and then finish the
load
> of indexes / triggers / procedures where the load stopped.
>
> I was checking the onload / onunload utility and seems to work fine, but the
> output files is so big that for the smaller databases we can do it, but for
> the largest will be difficult to have that free space.
>
> I'm trying to find a way to zip the output of the onunload but seems I cannot
> use STDIO here.
>
> Can anybody help with this? maybe there is another approach to do that
> transfer without using onload/onunload.
>
> We are at:
>
> IBM Informix Dynamic Server Version 12.10.FC8W1
> Red Hat Enterprise Linux Server release 7.3 (Maipo)
>
> On both servers.
>
> Thanks
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
--Apple-Mail-412A61E5-EFAF-49ED-961A-5B8F47D01C2E
You can unload to a compressed file. You have to be careful that everything
works, so dont get rid of your source data until youve checked your
destination.
Unload:
create external table foo_ext sameas foo
using (
datafiles (
PIPE:foo.p
),
express
)
mknod foo.p -p
cat foo.p | gzip - > foo.gz
insert into foo_ext select * from foo;
Load:
create raw table foo ..create external table foo_ext sameas foo;
gunzip -c foo.gz > foo.p
insert into foo select * from foo_ext
Have fun.
j.
> On Apr 10, 2018, at 7:41 AM, JOAQUIN GARRIDO ESPINOSA
<jgarrido.espinosa@gmail.com> wrote:
>
> Hi,
>
> we are planning to do monthly refreshes of our UAT environment from databases
> from production. Right now we are using dbexports but it is a slow process
> where:
> - if we import databases with dbimport -l buffered, we get long transaction
> errors.
> - if we import databases without logging, the import is stopped because some
> databases have synonyms between them, and you cannot create a synonim from a
> not logged database to a logged one and vice-versa.
>
> Right now we are loading them using unlogged dbimport and then finish the
load
> of indexes / triggers / procedures where the load stopped.
>
> I was checking the onload / onunload utility and seems to work fine, but the
> output files is so big that for the smaller databases we can do it, but for
> the largest will be difficult to have that free space.
>
> I'm trying to find a way to zip the output of the onunload but seems I cannot
> use STDIO here.
>
> Can anybody help with this? maybe there is another approach to do that
> transfer without using onload/onunload.
>
> We are at:
>
> IBM Informix Dynamic Server Version 12.10.FC8W1
> Red Hat Enterprise Linux Server release 7.3 (Maipo)
>
> On both servers.
>
> Thanks
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Oooh, forgot to alter the table back to standard at the end:
alter table foo type (standard);create index ..
j.
> On Apr 10, 2018, at 7:49 AM, Jack Parker <jack.parker4@verizon.net> wrote:
>
> You can unload to a compressed file. You have to be careful that everything
> works, so dont get rid of your source data until youve checked your
> destination.
>
> Unload:
> create external table foo_ext sameas foo
> using (
> datafiles (
> PIPE:foo.p
> ),
> express
> )
>
> mknod foo.p -p
> cat foo.p | gzip - > foo.gz
>
> insert into foo_ext select * from foo;>
> Load:
> create raw table foo ..> create external table foo_ext sameas foo;
> gunzip -c foo.gz > foo.p
>
> insert into foo select * from foo_ext>
> Have fun.
> j.
>
>> On Apr 10, 2018, at 7:41 AM, JOAQUIN GARRIDO ESPINOSA
> <jgarrido.espinosa@gmail.com> wrote:
>>
>> Hi,
>>
>> we are planning to do monthly refreshes of our UAT environment from
> databases
>> from production. Right now we are using dbexports but it is a slow process
>> where:
>> - if we import databases with dbimport -l buffered, we get long transaction
>> errors.
>> - if we import databases without logging, the import is stopped because some
>> databases have synonyms between them, and you cannot create a synonim from a
>> not logged database to a logged one and vice-versa.
>>
>> Right now we are loading them using unlogged dbimport and then finish the
> load
>> of indexes / triggers / procedures where the load stopped.
>>
>> I was checking the onload / onunload utility and seems to work fine, but the
>> output files is so big that for the smaller databases we can do it, but for
>> the largest will be difficult to have that free space.
>>
>> I'm trying to find a way to zip the output of the onunload but seems I
> cannot
>> use STDIO here.
>>
>> Can anybody help with this? maybe there is another approach to do that
>> transfer without using onload/onunload.
>>
>> We are at:
>>
>> IBM Informix Dynamic Server Version 12.10.FC8W1
>> Red Hat Enterprise Linux Server release 7.3 (Maipo)
>>
>> On both servers.
>>
>> Thanks
>>
>>
>>
>
*******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Just add enough logical logs for the transaction to fit and import with
dbimport -lDid you try ifxclone ?
Marcus Haarmann
Von: "JOAQUIN GARRIDO ESPINOSA" <jgarrido.espinosa@gmail.com>
An: "ids" <ids@iiug.org>
Gesendet: Dienstag, 10. April 2018 13:41:13
Betreff: Moving entire database from on server to another [40979]
Hi,
we are planning to do monthly refreshes of our UAT environment from databases
from production. Right now we are using dbexports but it is a slow process
where:
- if we import databases with dbimport -l buffered, we get long transaction
errors.
- if we import databases without logging, the import is stopped because some
databases have synonyms between them, and you cannot create a synonim from a
not logged database to a logged one and vice-versa.
Right now we are loading them using unlogged dbimport and then finish the load
of indexes / triggers / procedures where the load stopped.
I was checking the onload / onunload utility and seems to work fine, but the
output files is so big that for the smaller databases we can do it, but for
the largest will be difficult to have that free space.
I'm trying to find a way to zip the output of the onunload but seems I cannot
use STDIO here.
Can anybody help with this? maybe there is another approach to do that
transfer without using onload/onunload.
We are at:
IBM Informix Dynamic Server Version 12.10.FC8W1
Red Hat Enterprise Linux Server release 7.3 (Maipo)
On both servers.
Thanks
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
No, we cannot do a complete restore because we are moving only a set of
databases where our data resides. Rest of databases are application databases
that we should not move.
For that same reason I cannot use ifxclone.
I can do some tests to increase the size/number of logical logs. If I have
enough space to add required logs maybe I can through dbimport.
Another reason I was trying to find another tool to do export/import of
complete databases is because the creation of index on the dbimport is very
slow.
Thanks
Production restore and then drop databases you dont need. Trivial to setup
Paul Watson
Oninit www.oninit.com
Tel: +1 913 674 0360
Cell: +1 913 387 7529
Oninit® is a registered trademark of Oninit LLC
Failure is not as frightening as regret.
If you want to improve, be content to be thought foolish and stupid.
> On Apr 10, 2018, at 07:31, JOAQUIN GARRIDO ESPINOSA
<jgarrido.espinosa@gmail.com> wrote:
>
> No, we cannot do a complete restore because we are moving only a set of
> databases where our data resides. Rest of databases are application databases
> that we should not move.
>
> For that same reason I cannot use ifxclone.
>
> I can do some tests to increase the size/number of logical logs. If I have
> enough space to add required logs maybe I can through dbimport.
>
> Another reason I was trying to find another tool to do export/import of
> complete databases is because the creation of index on the dbimport is very
> slow.
>
> Thanks
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
--Apple-Mail-CF348FFC-FCCE-476C-8104-C24B84B4F85D
Wow, I have to try that. At least to move big tables.
So the idea would be to generate script to do that load of tables and then,
use the dbschema to load all indexes/FKs/Triggers/Procedures ...?
Still the Index creation would be slow.
I reckon the easiest would be Paulâs approach but if your test environment
is not production like from a storage perspective then try looking into
Artâs myexport/myimport utility
(https://www.askdbmgt.com/my-utilities.html). This gives you the ability to
use external tables or HPL, compressed unload files, parallel export/import,
etc.
Sent from my iPhone
On 10 Apr 2018, at 10:42 pm, JOAQUIN GARRIDO ESPINOSA
<jgarrido.espinosa@gmail.com<mailto:jgarrido.espinosa@gmail.com>> wrote:
Wow, I have to try that. At least to move big tables.
So the idea would be to generate script to do that load of tables and then,
use the dbschema to load all indexes/FKs/Triggers/Procedures ...?
Still the Index creation would be slow.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.