Loading Data from On-Premises Database into DBaaS Database

 

Overview

Purpose

This tutorial covers data unloading and loading methods to migrate on-premises Oracle Database 12c data into an Oracle Database 12c DBaaS.

Time to Complete

Approximately 60 minutes.

Introduction

In this tutorial, you learn how to unload data from an on-premises Oracle Database 12c PDB tables to load the same data into the tables of the Oracle Database 12c DBaaS database tables using SQL Developer or using external tables.

Prerequisites

Before starting this tutorial, it may be helpful to have followed the Signing Up for a Database Cloud Service, Connecting to a Database Cloud Service (DBaaS) Instance Through an SSH Tunnel and Creating a Database Cloud Service (DBaaS) Instance tutorials.
 

Loading Data Using SQL Developer


This section explains how SQL Developer can unload data from tables of the PDB_PREMISE PDB of the on-premises CDB2  CDB, and load data into tables of the existing PDB1 PDB of the DBaaS ORCL CDB.
There are three possible methods.
  • Export Cart using Insert
  • Export Cart using Loader
  • Deploy Cloud (Only for the Schema Service)
     

    Export Cart Using Insert

    1. Export Cart is a method that allows you to create a cart into which you add selected objects to be loaded into the DBaaS database (non-CDB or PDB).
      Export Cart using Insert format generates a SQL file. The script contains the DDL statements to recreate the tables selected in the cart and other dependent objects into the DBaaS database (non-CDB or PDB), and INSERT statements to load data into the DBaaS database.

      • Create a connection to the on-premises database. In this case, the on-premises database is a PDB.
        Remark: The VM IP Address is blurred for security reason.

        t10101

        • Then click Connect.

      • Create a cart into which you add selected objects to be loaded into the DBaaS database (non-CDB or PDB). From the menu, click View and the Cart.

        t10101_b

      • Click the first icon to create a new cart.

        t10101_c

        A new cart is created. If other carts already exist such as cart_1 and cart_2, the new cart is called cart_3.

        t10101_d

      • Expand the CDB2_PDB_PREMISE_SYS connection folder by clicking the +, then expand the Other Users, then expand the LO_U1 local user, then expand the Tables (Filtered).

        t10101_e

      • Right-click the TAB1 table and select Add to Cart....

        t10101_f

        • Observe that the table is included in the cart. Click Data to retrieve the data of the rows as well as the definition of the table.

        t10101_g


      • Click the Export Cart (Ctrl+E) icon to export the selected objects of the cart and thus generate DDL and DML statements for recreating the table and dependent objects and reloading data into the DBaaS database.

        t10101_h

      • Select the options that will create supplementary DDL statements in the D:\export.sql script file. For example, selecting Dependents will generate CREATE INDEX statements if indexes exist on the table. Selecting Grants will generate GRANT statements if indexes exist on the table. Selecting Drops will generate DROP TABLE statements. You can also define how often a COMMIT statement will occur when rows will be inserted in tables in the DBaaS database by executing the generated SQL script.

        t10101_i

        Notice that the default Format of the resulting export is insert. This means that SQL Developer generates a single SQL script file containing the DDL statements to drop and recreate the tables along with dependent objects (indexes, constraints and others) and other types of objects defined in the cart into the DBaaS database (non-CDB or PDB), and grant privileges on objects to users in the DBaaS database.

      • Click Apply.

        t10101_j

        The content of the D:\export.sql script file is displayed in the SQL Worksheet page in a new Worksheet tab.

        t10101_k_1t10101_k_2


    2. Create the connection to the DBaaS database if this one does not exist yet. In this case, the DBaaS database is the PDB1 PDB of the ORCL CDB.

      t10102

    3. If the export.sql SQL script is no more opened, from the menu, click File, then Open. Select the export.sql SQL file. The content of the SQL script appears in the SQL Worksheet page. Click the Run Script (F5) icon.

      t10103

    4. Select the connection to PDB1. Click OK.

      t10104

      t10104_b

      5. Note: All DDL and DML statements are successfully executed in the DBaaS PDB by using the export.sql SQL file.
     

    Export Cart Using Loader

    1. Export Cart using Loader format generates multiple files.
      • A SQL file containing the DDL statements to recreate the tables and other objects into the DBaaS database (non-CDB or PDB)
      • A SQL*Loader control file describing the SQL*Loader parameters to load data
      • A SQL*Loader data file containing the data to be reinserted into the tables into the DBaaS database (non-CDB or PDB)

      • Create a connection to the on-premises database if this one does not exist yet. In this case, the on-premises database is a PDB.

        t10201

        • Then click Connect.

      • Either use the same cart created in the previous section - Export Cart Using Insert, or create a new cart to add new and or different objects. Because the steps to create a cart are covered in the previous section, you will use the same cart. Click the Export Cart (Ctrl+E) icon to export the selected objects of the cart and thus generate appropriate files for recreating the table and dependent objects and reloading data into the DBaaS database.

        t10201_b

      • Choose loader in the Format field. Select the options that will create supplementary DDL statements in the D:\load.sql script file. For example, selecting Dependents will generate CREATE INDEX statements if indexes exist on the table. Selecting Grants will generate GRANT statements if indexes exist on the table. Selecting Drops will generate DROP TABLE statements.

        t10201_c

      • Click Apply.

        t10201_d

        An error is returned because the loader method will generate three types of files.
        You have to declare a directory where the three types of files will be generated as Separate Files before clicking Apply.

        t10201_e

        The list of files generated in the D:\DBA\Loader directory is displayed in the SQL Worksheet page in a new Worksheet tab.

        t10201_f

        There are three types of files generated.
        • A Generated-nnn.sql SQL script containing a list of SQL script file names, each of them executing the DDL statements to drop (DROP_1.sql), recreate the tables (TAB1.sql) and other objects such as indexes (I_TAB1.sql) into the DBaaS database.
        • A SQL*Loader control file (TAB1_DATA_TABLE.ctl) describing the SQL*Loader parameters to load data
        • A SQL*Loader data file (TAB1_DATA_TABLE.ldr) containing the data to be reinserted into the tables into the DBaaS database (non-CDB or PDB)

      • To view the content of the files, click View then Files.

        t10201_g

        Find the D:\DBA\Loader directory.

      • To view the content of the SQL*Loader data file, right-click the TAB1_DATA_TABLE.ldr file name and select Open. Choose Open the file inside SQL Developer as plain text.

        t10201_h

        t10201_i

        • Notice that the SQL*Loader data file contains 2 rows separated by the {EOL} Line Terminator defined in the Export Objects parameters.

      • To view the content of the SQL*Loader control file, right-click the TAB1_DATA_TABLE.ctl file name and select Open. Choose Open the file inside SQL Developer as plain text.

        t10201_j

        t10201_k

      • To view the content of the SQL script files, right-click the TAB1.sql file name and select Open. Then right-click the I_TAB1.sql file name and select Open.

        t10201_l

        t10201_m

    2. Create the connection to the DBaaS database if this one does not exist yet. In this case, the DBaaS database is the PDB1 PDB of the ORCL CDB.

      t10202

    3. If the Generated-20150311141258.sql SQL script is no more opened, from the menu, click File, then Open. Select the Generated-20150311141258.sql SQL file to execute each SQL script file name described in the the Generated-20150311141258.sql script.

      t10202_b

    4. The content of the SQL script appears in the SQL Worksheet page. Click the Run Script (F5) icon.

      t10203

    5. Select the connection to PDB1. Click OK.

      t10204

      t10204_b

      6. The definition of the TAB1 table and I_TAB index objects are created. Use SQL*Loader to load the data.

    6. Transfer the SQL*Loader control and data files from the SQL Developer client machine to the DBaaS VM.

      • Use a secure file transfer utility such as WinSCP.
        Remark: The key used to scp the files can be any key generated by puttygen.

        t10205

        Click Login.

      • The authentication is secure because it uses an encrypted private key.

        t10205_b

      • In the left pane of the tool, find the D:\DBA\Loader directory and select the SQL*Loader control and data files. In the right pane of the tool, selectthe directory where you want to copy the files to. Click F5 to copy.

          t10205_c

          t10205_d

        Click Copy.

        t10205_e

        Files are now copied to the DBaaS VM.

    7. You have created a PuTTY definition for connecting through an SSH tunnel to the DBaaS instance. Now open this definition to connect to the VM.

      t10206

      Then click Open.

    8. Because this is not the first time you are connecting to the VM, the SSH utility does not prompt you to confirm the public key. If it were the first time, the SSH utility would have prompted you to confirm the public key. You would have confirmed by clicking Yes.

      t10207

    9. Launch sqlldr by using the SQL*Loader control and data files.

      t10208

    10. Verify that the LO_U1.TAB1 is correctly loaded with the appropriate values and that the LO_U1.I_TAB1 index has been correctly created on the table.

        t10209
     

    Deploy Cloud

      Deploy Cloud is a method that automatically unloads, transfers and reloads data of the cart from the on-premises database into the DBaaS database.
      You can get all the details of the steps required to complete a Deploy Cloud operation in the Oracle Cloud documentation. This method works only for the Schema Service and not for the Database Service.
 

Loading Data With External Files


This section explains how to manually unload data from tables of an on-premises database and reload data into the tables of the DBaaS database using external files. This method is useful when SQL Developer is not available.
     

    Generate SQL Scripts from the On-premises Database

    1. Unload the definitions and data of selected tables from the on-premises database. In this case, the on-premises database is a PDB. Connect under the schema owner of the objects to be unloaded from the on-premises  PDB_PREMISE PDB.

      t20101

    2. Prepare a SQL script that will retrieve the definition of the objects to unload.

      t20102

    3. Execute the DDL.sql script in the PDB_PREMISE PDB and keep the DDL statements in the ddl.sql script.

        t20103

    4. Remove unnecessary lines from ddl.sql script file.

      t20104
     

    Unload On-premises Data

    1. Connect under the schema owner of the tables to be unloaded from the on-premises PDB_PREMISE PDB. Unload tables data into external files and log the DDL statements into a SQL script file.

      t20201

      t20201_b

    2. Remove unnecessary lines from ext_tables.sql script file and add data types to columns. The script will be used to recreate the external tables in the DBaaS database to reload data into heap tables.

      t20202
     

    Transfer the Files of the On-premises Platform to the DBaaS VM

      Use SCP to transfer the SQL script files (DDL.sql and ext_tables.sql) and the external files (tab1_1.ldr and tab1_2.ldr) securely from the on-premises  platform to the DBaaS VM.

      t20301

      Note that a passphrase is used to securely transfer the files. Without the passphrase, SCP does not work so well.
     

    Load On-premises Data into the DBaaS CDB

    1. You have created a PuTTY definition for connecting through an SSH tunnel to the DBaaS instance. Now open this definition to connect to the VM.

      t20401

      Then click Open.

    2. Because this is not the first time you are connecting to the VM, the SSH utility does not prompt you to confirm the public key. If it were the first time, the SSH utility would have prompted you to confirm the public key. You would have confirmed by clicking Yes.

      t20402

    3. Connect under the schema owner of the objects to be reloaded into the DBaaS PDB1 PDB. Create the directory where the external files have been copied.

      t20403

    4. Execute the SQL script to create the objects (tables, indexes, synonyms, views) and grant the privileges to the owner of the objects.

      t20404

    5. Execute the SQL script to create the external tables.

      t20405

    6. Load data into lo_u1.tab1 using INSERT statements and the external files.

      t20406
 

Summary

    You can unload data from tables of an on-premises Oracle Database 12c to load the same data into tables of the Oracle Database 12c DBaaS database using SQL Developer or using external tables.

    In this tutorial, you learned to:
    • Use SQL Developer Export Cart with insert format
    • Use SQL Developer Export Cart with loader format
    • Use SQL Developer Deploy Cloud
    • Create external tables to unload data and use external files to reload data