Thank you for sharing your experience. Regarding the problem with the
metadata export:
Checklist For Slow Performance Of DataPump Export (expdp) And Import
(impdp) (Doc ID 453895.1)
The database that we migrated has a large number of partitions (4 Mio+) and
is used in the five continents (around the clock)
But we are very happy that we did it in less than 4 days.
Best Regards
Ahmed
-----Original-Nachricht-----
Betreff: RE: Fastest way to count exact number of rows in a very large
table
Datum: 2020-10-05T22:05:28+0200
Von: "Reen, Elizabeth" <dmarc-noreply@xxxxxxxxxxxxx>
An: "ahmed.fikri@xxxxxxxxxxx" <ahmed.fikri@xxxxxxxxxxx>,
"andysayer@xxxxxxxxx" <andysayer@xxxxxxxxx>
10,000 + tables are in that instance. We were able to have a
change freeze for the week before the move. That allowed us to do the
metadata at leisure. I don’t know how long it took, but the junior DBA
completed it in the day given. I didn’t hear about any bugs and we have
plenty of partitions.
I had some luxuries that are only found it large companies.
We had extra disks and were able to move the tablespace by copying to and
from the disks. This server has ~ 100 other servers pointing to it. It
was a major production to get it moved. We scripted the whole thing and
had a dry run before to get the timings.
Liz
From: [External] ahmed.fikri at t-online.de <ahmed.fikri@xxxxxxxxxxx>
Sent: Monday, October 5, 2020 12:21 PM
To: Reen, Elizabeth [ICG-IT]; andysayer@xxxxxxxxx
Cc: list, oracle; ramukam1983@xxxxxxxxx
Subject: AW: Fastest way to count exact number of rows in a very large
table
Hi Liz,
we have that behind us too using XTTS (from 11.2 to 12.1 - 16 TB in about 4
days). I think the critical point was the export of the metadata (we have a
huge number of (sub) partitions) and the datapump is buggy in 11.2 (we
installed several patches).
Could you please share how many objects contains your DB and maybe how long
took the metadata export?
Best regards
Ahmed
-----Original-Nachricht-----
Betreff: RE: Fastest way to count exact number of rows in a very large
table
Datum: 2020-10-05T17:41:57+0200
Von: "Reen, Elizabeth" <dmarc-noreply@xxxxxxxxxxxxx
<mailto:dmarc-noreply@xxxxxxxxxxxxx> >
An: "andysayer@xxxxxxxxx <mailto:andysayer@xxxxxxxxx> " <
andysayer@xxxxxxxxx <mailto:andysayer@xxxxxxxxx> >, "
ahmed.fikri@xxxxxxxxxxx <mailto:ahmed.fikri@xxxxxxxxxxx> " <
ahmed.fikri@xxxxxxxxxxx <mailto:ahmed.fikri@xxxxxxxxxxx> >
We just completed such a transition. We kept the Oracle
version the same so we could see the impact of Linux. Transportable
tablespaces was how we did it. We were able to move a 17 terabyte database
in under 10 hours.
Liz
From: [External] oracle-l-bounce@xxxxxxxxxxxxx
<mailto:oracle-l-bounce@xxxxxxxxxxxxx> <oracle-l-bounce@xxxxxxxxxxxxx
<mailto:oracle-l-bounce@xxxxxxxxxxxxx> > On Behalf Of [External] Andy Sayer
Sent: Friday, October 2, 2020 3:09 PM
To: ahmed.fikri@xxxxxxxxxxx <mailto:ahmed.fikri@xxxxxxxxxxx>
Cc: list, oracle; ramukam1983@xxxxxxxxx <mailto:ramukam1983@xxxxxxxxx>
Subject: Re: Fastest way to count exact number of rows in a very large
table
Just because a table has the same number of rows, it doesn’t mean it has
the same data. With 108 billion rows, your data is going to be changing
quickly, in order to get accurate counts at the right point in time you’re
going to end up keeping your application offline for a window before and
after your migration.
What you need to do is determine where you expect data to go missing and
work out a way to check.
This will depend on how you’re doing your migration, I would suggest you
use Cross-Platform Transportable Tablespaces (Doc Id 371556.1) as that
would allow you to do a physical import and just convert the files to the
right endianness. This starts by making sure all data has been written to
your data files (so they can be read only on the source system). As you’re
working with the physical data files rather than the logical data (rows in
tables), the only way you’re going to loose rows is by corrupting your
files. You can check for corruption using RMAN once you’ve imported the
converted files. No need to count all your rows, and no need to hope that
that’s all you need to compare.
Hope that helps,
Andy
On Fri, 2 Oct 2020 at 19:38, ahmed.fikri@xxxxxxxxxxx
<mailto:ahmed.fikri@xxxxxxxxxxx> <ahmed.fikri@xxxxxxxxxxx
<mailto:ahmed.fikri@xxxxxxxxxxx> > wrote:
Hi Ashoke,
could you send the execute plan of the query too? I think there is no
general approach for that, it depends on several factors: whether the
table has indexes (normal/bitmap) and in case the table has indexes the
size of the table compared to the existing index...... But generally
parallel processing should help.
Best regards
Ahmed
-----Original-Nachricht-----
Betreff: Fastest way to count exact number of rows in a very large table
Datum: 2020-10-02T19:45:19+0200
Von: "Ashoke Mandal" <ramukam1983@xxxxxxxxx <mailto:ramukam1983@xxxxxxxxx>
