Copy Data from BICS Schema Service to BICS DBaaS or on On-Premise DB

Introduction

This article will cover the steps to migrate the data from a BICS Schema Service Database, to a BICS DBaaS Database.  Similar steps could be used to move a database from BICS to on-premise, or from on-premise to a BICS DBaaS instance.

The process involves creating a DMP file in BICS that is then downloaded and moved to the BICS DBaaS instance, and then imported with the DATAPUMP tool.

This has the advantage of not only copying all tables and their data, but also any views, indexes, packages, procedures, functions, triggers, and sequences that had been created in the BICS environment.

 

Main Article

Export Data from BICS Schema Service Database

1. Open the Database Service page by clicking the cloud icon

2. Make a note of the SFTP host, port and user name details on the ‘Overview’ page.

3. Go to the ‘Exports’ tab

4. Select the ‘Export Data’ option

and then check the option to ‘Include Data’ the Create Data Export

 

This will then show a data export has been requested and will show in ‘Requested’ status.

Windows7_x86

This status will change to ‘Available’ once the DMP file has been created.  The time for this to be available will depend on the size of the database being exported.

The name of the DMP file, and its size will also be shown.

Edit_Post_‹_ATeam_Chronicles_—_WordPress

5. With a FTP tool such as Filezilla, connect to the SFTP server using the details noted in step 2.  Select the ‘download’ folder on the BICS Server, and locate the DMP file created in the previous step and download locally.

 

Windows7_x86

 

Import Data into DBaaS

Please Note !

Since this article was written, PDBs are being used commonly in DBCS / DBaaS.  There’s an extra step required to create a new file directory within the PDB to upload the DMP file.  See this article for more info.

The DMP file needs to be copied to the DBaaS server.  This requires the SSH Public Key that was created when the DBaaS instance was set up.  For more information on how to copy the file, see this document and the section Copy the Dump File to the Cloud Database Instance.

Before running the Data Pump import command, the schemas and tablespaces from the original BICS Schema Service DB, and the target DBaaS DB need to be ascertained.

Logged into the DBaaS schema where the data will be loaded, run the command below to get the tablespace name.

select default_tablespace from user_users;

In the BICS Schema Service SQL Workshop, the same tablespace command can be run, while the schema name is listed in a box at the top as shown below.

 

Windows7_x86

Connected to the DBaaS database, set the Oracle SID and HOME, and then as the system user, run this command – substituting the relevant schema and tablespace names.  This will import all the objects from the BICS schema service database into the new DBaaS schema.

impdp system/SYSTEMPASSWORD SCHEMAS=BICSSCHEMANAME remap_schema=BICSSCHEMANAME:DBAASSCHEMANAME remap_tablespace=BICSTABLESPACENAME:DBAASTABLESPACENAME  directory=datapump dumpfile=YOURDMP.dmp

 

Similar steps could be used for an on-premise database to either export it using DATAPUMP and upload to the DBaaS database, or to import the DMP file from the BICS Schema Service Database.

 

Summary

This article demonstrated a method for migrating data from a BICS Schema Service Database to a BICS DBaaS Database.  The same method could be used for migrating data to or from an On-Premise database.

 

Further Reading

Copy DMP file from On-Premise to Cloud DBaaS

http://www.oracle.com/webfolder/technetwork/tutorials/obe/cloud/dbaas/obe_dbaas_migrating_11g_to_11g_via_data_pump_conventional_exp_imp/index.html#section3

Another Blog that touches on DBaaS 12C and Private / Public SSH Keys and Copying Data

https://blogs.oracle.com/dataintegration/entry/odi_12c_and_dbaas_in

 

Add Your Comment