Subject: Re: the best approach to migrate a database to new server
To: oracle-l@freelists.org
References: <1580480521140.9749333.9cd7754cbf44139091ba49d1e7f93876550c49d8@spica.telekom.de>
From: Mladen Gogala <gogala.mladen@gmail.com>
Message-ID: <d82d1be4-c4bb-c6d4-b2b8-05e98263017c@gmail.com>
Date: Sun, 2 Feb 2020 14:32:22 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.4.1
MIME-Version: 1.0
In-Reply-To: <1580480521140.9749333.9cd7754cbf44139091ba49d1e7f93876550c49d8@spica.telekom.de>
Content-Type: multipart/alternative;
 boundary="------------55347323D49A1C8258EFA722"
Content-Language: en-US
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: gogala.mladen@gmail.com
--------------55347323D49A1C8258EFA722
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

This is a very complex question. You can't use snapshots because AIX 
volume manager and JFS2 are not compatible with Linux. It would be 
practically impossible to mount JFS2 file systems created on top of AIX 
volume manager devices on a Linux server. The most realistic option is 
bonding 4 10GB Ethernet devices into a 40Gbit/sec link, capable of 
transferring 12 TBytes/hour and use NFS mount. With such a fast NFS 
mount, you can do rman convert in approximately 2 hours. You can then 
re-create the control file and open the database. That would give you 
Oracle 11g database on Linux. Upgrading it should also take around 1 
hour, depending on the speed of the Linux disk subsystem, which would 
require around 5 hours of downtime.

Data pump would be a very tricky option because it would very likely 
mess up PL/SQL objects, views, synonyms and grants. As a matter of fact, 
it could be done, a team that included Arup Nanda and myself has done 
something like that with a classic export/import in 2001. The project 
was finalized during the Labor Day on 2001 and I was dealing with the 
remaining issues late into evening of Monday, September the 10th 2001. 
The next morning I came to the office late and, boy, was I in for a 
shock. It was a beautiful late summer morning of Tuesday, September 
11th, 2001. I will never, ever forget that day.

However, such project requires careful planning, numerous dry runs, lots 
of scripting and a very competent DBA personnel.  Our databases were 
much smaller, the LAN speed was 100MBit/sec and we were moving database 
from Oracle 7.3.5 on a Pyramid Nile system to Oracle 8.3 on a HP 9000/N 
4-way RAC. The largest table has just exceeded 1 GB and the entire 
database was around 700 GB, which was considered huge in 2001. So, you 
should count on additional hardware expenses and around 6 months of time 
for the project. This is not something that can be achieved by using 
Agile project management methodology.


On 1/31/20 9:22 AM, ahmed.fikri@t-online.de wrote:
> Hi all,
>
> we are planning to migrate a 16 Terabyte database from 11g on aix 
> machine to 12c on linux. In the target db is Dataguard used. The DB 
> has about 50 Schemas the biggest one is about 11 TB the second is 2.6 
> TB then four with each one 1 TB the rest is each one less than 1 TB. 
> Unfortunately all Schemas share the table spaces.
>
> Wich approach could we use with less downtime?
>
> My idea was to move the schemas to separate tablespaces an migrate the 
> Schemas using transportable ts.
> Or somehow copying the metadata to do new instance in such way the new 
> db use the old data files and then copy them separately one by one.
>
> Or even copying the Schemas separately using dblink and data pump.
>
> Any idea please?
>
> Regards
> Ahmed Fikri
>
>
> ------------------------------------------------------------------------
> Gesendet mit der Telekom Mail App 
> <https://kommunikationsdienste.t-online.de/redirects/email_app_android_sendmail_footer> 
>

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217


--------------55347323D49A1C8258EFA722
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p>This is a very complex question. You can't use snapshots because
      AIX volume manager and JFS2 are not compatible with Linux. It
      would be practically impossible to mount JFS2 file systems created
      on top of AIX volume manager devices on a Linux server. The most
      realistic option is bonding 4 10GB Ethernet devices into a
      40Gbit/sec link, capable of transferring 12 TBytes/hour and use
      NFS mount. With such a fast NFS mount, you can do rman convert in
      approximately 2 hours. You can then re-create the control file and
      open the database. That would give you Oracle 11g database on
      Linux. Upgrading it should also take around 1 hour, depending on
      the speed of the Linux disk subsystem, which would require around
      5 hours of downtime.</p>
    <p>Data pump would be a very tricky option because it would very
      likely mess up PL/SQL objects, views, synonyms and grants. As a
      matter of fact, it could be done, a team that included Arup Nanda
      and myself has done something like that with a classic
      export/import in 2001. The project was finalized during the Labor
      Day on 2001 and I was dealing with the remaining issues late into
      evening of Monday, September the 10th 2001. The next morning I
      came to the office late and, boy, was I in for a shock. It was a
      beautiful late summer morning of Tuesday, September 11th, 2001. I
      will never, ever forget that day.</p>
    <p>However, such project requires careful planning, numerous dry
      runs, lots of scripting and a very competent DBA personnel.  Our
      databases were much smaller, the LAN speed was 100MBit/sec and we
      were moving database from Oracle 7.3.5 on a Pyramid Nile system to
      Oracle 8.3 on a HP 9000/N 4-way RAC. The largest table has just
      exceeded 1 GB and the entire database was around 700 GB, which was
      considered huge in 2001. So, you should count on additional
      hardware expenses and around 6 months of time for the project.
      This is not something that can be achieved by using Agile project
      management methodology. <br>
    </p>
    <p><br>
    </p>
    <div class="moz-cite-prefix">On 1/31/20 9:22 AM,
      <a class="moz-txt-link-abbreviated" href="mailto:ahmed.fikri@t-online.de">ahmed.fikri@t-online.de</a> wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:1580480521140.9749333.9cd7754cbf44139091ba49d1e7f93876550c49d8@spica.telekom.de">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div style="background-color:white; width=100%;"> Hi all, <br>
        <br>
        we are planning to migrate a 16 Terabyte database from 11g on
        aix machine to 12c on linux. In the target db is Dataguard used.
        The DB has about 50 Schemas the biggest one is about 11 TB the
        second is 2.6 TB then four with each one 1 TB the rest is each
        one less than 1 TB. Unfortunately all Schemas share the table
        spaces. <br>
        <br>
        Wich approach could we use with less downtime? <br>
        <br>
        My idea was to move the schemas to separate tablespaces an
        migrate the Schemas using transportable ts. <br>
        Or somehow copying the metadata to do new instance in such way
        the new db use the old data files and then copy them separately
        one by one. <br>
        <br>
        Or even copying the Schemas separately using dblink and data
        pump. <br>
        <br>
        Any idea please? <br>
        <br>
        Regards <br>
        Ahmed Fikri <br>
        <br>
        <br>
        <hr>Gesendet mit der <a
href="https://kommunikationsdienste.t-online.de/redirects/email_app_android_sendmail_footer"
          moz-do-not-send="true">Telekom Mail App</a>
      </div>
    </blockquote>
    <pre class="moz-signature" cols="72">-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
</pre>
  </body>
</html>

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