Thread: Oracle to postgres migration via ora2pg (blob data)
Hello team,
We have to migrate a schema from oracle to postgres but there is one table that is having following large lob segments. This table is taking time to export. What parameters we have to set in ora2pg.conf to speed up the data export by ora2pg.
Table: CLIENT_DB_AUDIT_LOG
LOBSEGMENT SYS_LOB0000095961C00008$$ 80.26
LOBSEGMENT SYS_LOB0000095961C00007$$ 79.96
LOBSEGMENT SYS_LOB0000094338C00008$$ 8.84
LOBSEGMENT SYS_LOB0000084338C00007$$ 8.71
LOBSEGMENT SYS_LOB0000085961C00009$$ 5.32
VM Details are:
RAM 8GB
VCPUs 2 VCPU
Disk 40GB
Thanks,
I would look at the source table in Oracle first. It looks a lot like audit data. Perhaps all content is not needed in Postgres. If it is, then the table and lobs may benefit from being reorganised in oracle.
Alter table CLIENT_DB_AUDIT_LOG move;
Alter table CLIENT_DB_AUDIT_LOG move lob (SYS_LOB0000095961C00008$$);
-- Three more of these.
The syntax is from my the back of my head. You may need to look the details up.
Niels
Fra: Daulat Ram <Daulat.Ram@exponential.com>
Sendt: 31. juli 2019 13:32
Til: pgsql-general@lists.postgresql.org; pgsql-performance@lists.postgresql.org
Emne: Oracle to postgres migration via ora2pg (blob data)
Hello team,
We have to migrate a schema from oracle to postgres but there is one table that is having following large lob segments. This table is taking time to export. What parameters we have to set in ora2pg.conf to speed up the data export by ora2pg.
Table: CLIENT_DB_AUDIT_LOG
LOBSEGMENT SYS_LOB0000095961C00008$$ 80.26
LOBSEGMENT SYS_LOB0000095961C00007$$ 79.96
LOBSEGMENT SYS_LOB0000094338C00008$$ 8.84
LOBSEGMENT SYS_LOB0000084338C00007$$ 8.71
LOBSEGMENT SYS_LOB0000085961C00009$$ 5.32
VM Details are:
RAM 8GB
VCPUs 2 VCPU
Disk 40GB
Thanks,
I would look at the source table in Oracle first. It looks a lot like audit data. Perhaps all content is not needed in Postgres. If it is, then the table and lobs may benefit from being reorganised in oracle.
Alter table CLIENT_DB_AUDIT_LOG move;
Alter table CLIENT_DB_AUDIT_LOG move lob (SYS_LOB0000095961C00008$$);
-- Three more of these.
The syntax is from my the back of my head. You may need to look the details up.
Niels
Fra: Daulat Ram <Daulat.Ram@exponential.com>
Sendt: 31. juli 2019 13:32
Til: pgsql-general@lists.postgresql.org; pgsql-performance@lists.postgresql.org
Emne: Oracle to postgres migration via ora2pg (blob data)
Hello team,
We have to migrate a schema from oracle to postgres but there is one table that is having following large lob segments. This table is taking time to export. What parameters we have to set in ora2pg.conf to speed up the data export by ora2pg.
Table: CLIENT_DB_AUDIT_LOG
LOBSEGMENT SYS_LOB0000095961C00008$$ 80.26
LOBSEGMENT SYS_LOB0000095961C00007$$ 79.96
LOBSEGMENT SYS_LOB0000094338C00008$$ 8.84
LOBSEGMENT SYS_LOB0000084338C00007$$ 8.71
LOBSEGMENT SYS_LOB0000085961C00009$$ 5.32
VM Details are:
RAM 8GB
VCPUs 2 VCPU
Disk 40GB
Thanks,
With Warm Regards,
Amol Tarte,
Project Lead,
Rajdeep InfoTechno Pvt. Ltd.
Visit us at http://it.rajdeepgroup.com
I would look at the source table in Oracle first. It looks a lot like audit data. Perhaps all content is not needed in Postgres. If it is, then the table and lobs may benefit from being reorganised in oracle.
Alter table CLIENT_DB_AUDIT_LOG move;
Alter table CLIENT_DB_AUDIT_LOG move lob (SYS_LOB0000095961C00008$$);
-- Three more of these.
The syntax is from my the back of my head. You may need to look the details up.
Niels
Fra: Daulat Ram <Daulat.Ram@exponential.com>
Sendt: 31. juli 2019 13:32
Til: pgsql-general@lists.postgresql.org; pgsql-performance@lists.postgresql.org
Emne: Oracle to postgres migration via ora2pg (blob data)
Hello team,
We have to migrate a schema from oracle to postgres but there is one table that is having following large lob segments. This table is taking time to export. What parameters we have to set in ora2pg.conf to speed up the data export by ora2pg.
Table: CLIENT_DB_AUDIT_LOG
LOBSEGMENT SYS_LOB0000095961C00008$$ 80.26
LOBSEGMENT SYS_LOB0000095961C00007$$ 79.96
LOBSEGMENT SYS_LOB0000094338C00008$$ 8.84
LOBSEGMENT SYS_LOB0000084338C00007$$ 8.71
LOBSEGMENT SYS_LOB0000085961C00009$$ 5.32
VM Details are:
RAM 8GB
VCPUs 2 VCPU
Disk 40GB
Thanks,
With Warm Regards,
Amol Tarte,
Project Lead,
Rajdeep InfoTechno Pvt. Ltd.
Visit us at http://it.rajdeepgroup.com
I would look at the source table in Oracle first. It looks a lot like audit data. Perhaps all content is not needed in Postgres. If it is, then the table and lobs may benefit from being reorganised in oracle.
Alter table CLIENT_DB_AUDIT_LOG move;
Alter table CLIENT_DB_AUDIT_LOG move lob (SYS_LOB0000095961C00008$$);
-- Three more of these.
The syntax is from my the back of my head. You may need to look the details up.
Niels
Fra: Daulat Ram <Daulat.Ram@exponential.com>
Sendt: 31. juli 2019 13:32
Til: pgsql-general@lists.postgresql.org; pgsql-performance@lists.postgresql.org
Emne: Oracle to postgres migration via ora2pg (blob data)
Hello team,
We have to migrate a schema from oracle to postgres but there is one table that is having following large lob segments. This table is taking time to export. What parameters we have to set in ora2pg.conf to speed up the data export by ora2pg.
Table: CLIENT_DB_AUDIT_LOG
LOBSEGMENT SYS_LOB0000095961C00008$$ 80.26
LOBSEGMENT SYS_LOB0000095961C00007$$ 79.96
LOBSEGMENT SYS_LOB0000094338C00008$$ 8.84
LOBSEGMENT SYS_LOB0000084338C00007$$ 8.71
LOBSEGMENT SYS_LOB0000085961C00009$$ 5.32
VM Details are:
RAM 8GB
VCPUs 2 VCPU
Disk 40GB
Thanks,
FullConvert does this job much faster than ora2pg
With Warm Regards,
Amol Tarte,
Project Lead,
Rajdeep InfoTechno Pvt. Ltd.
Visit us at http://it.rajdeepgroup.comOn Wed 31 Jul, 2019, 5:16 PM Niels Jespersen, <NJN@dst.dk> wrote:I would look at the source table in Oracle first. It looks a lot like audit data. Perhaps all content is not needed in Postgres. If it is, then the table and lobs may benefit from being reorganised in oracle.
Alter table CLIENT_DB_AUDIT_LOG move;
Alter table CLIENT_DB_AUDIT_LOG move lob (SYS_LOB0000095961C00008$$);
-- Three more of these.
The syntax is from my the back of my head. You may need to look the details up.
Niels
Fra: Daulat Ram <Daulat.Ram@exponential.com>
Sendt: 31. juli 2019 13:32
Til: pgsql-general@lists.postgresql.org; pgsql-performance@lists.postgresql.org
Emne: Oracle to postgres migration via ora2pg (blob data)
Hello team,
We have to migrate a schema from oracle to postgres but there is one table that is having following large lob segments. This table is taking time to export. What parameters we have to set in ora2pg.conf to speed up the data export by ora2pg.
Table: CLIENT_DB_AUDIT_LOG
LOBSEGMENT SYS_LOB0000095961C00008$$ 80.26
LOBSEGMENT SYS_LOB0000095961C00007$$ 79.96
LOBSEGMENT SYS_LOB0000094338C00008$$ 8.84
LOBSEGMENT SYS_LOB0000084338C00007$$ 8.71
LOBSEGMENT SYS_LOB0000085961C00009$$ 5.32
VM Details are:
RAM 8GB
VCPUs 2 VCPU
Disk 40GB
Thanks,
NO_LOB_LOCATOR 1LONGREADLEN 100000000
-- Gilles Darold http://www.darold.net/
FullConvert does this job much faster than ora2pg
With Warm Regards,
Amol Tarte,
Project Lead,
Rajdeep InfoTechno Pvt. Ltd.
Visit us at http://it.rajdeepgroup.comOn Wed 31 Jul, 2019, 5:16 PM Niels Jespersen, <NJN@dst.dk> wrote:I would look at the source table in Oracle first. It looks a lot like audit data. Perhaps all content is not needed in Postgres. If it is, then the table and lobs may benefit from being reorganised in oracle.
Alter table CLIENT_DB_AUDIT_LOG move;
Alter table CLIENT_DB_AUDIT_LOG move lob (SYS_LOB0000095961C00008$$);
-- Three more of these.
The syntax is from my the back of my head. You may need to look the details up.
Niels
Fra: Daulat Ram <Daulat.Ram@exponential.com>
Sendt: 31. juli 2019 13:32
Til: pgsql-general@lists.postgresql.org; pgsql-performance@lists.postgresql.org
Emne: Oracle to postgres migration via ora2pg (blob data)
Hello team,
We have to migrate a schema from oracle to postgres but there is one table that is having following large lob segments. This table is taking time to export. What parameters we have to set in ora2pg.conf to speed up the data export by ora2pg.
Table: CLIENT_DB_AUDIT_LOG
LOBSEGMENT SYS_LOB0000095961C00008$$ 80.26
LOBSEGMENT SYS_LOB0000095961C00007$$ 79.96
LOBSEGMENT SYS_LOB0000094338C00008$$ 8.84
LOBSEGMENT SYS_LOB0000084338C00007$$ 8.71
LOBSEGMENT SYS_LOB0000085961C00009$$ 5.32
VM Details are:
RAM 8GB
VCPUs 2 VCPU
Disk 40GB
Thanks,
NO_LOB_LOCATOR 1LONGREADLEN 100000000
-- Gilles Darold http://www.darold.net/