Copy Data from BICS Schema Service to BICS DBaaS or on On-Premise DB
September 10, 2015 | 4 minute read* This blog was written for the legacy/limited DBCS-Schema database. Since OAC no longer includes a Schema Service DB, it is not possible to re-test this blog on the latest software release. This blog will remain active for customers who may still be using BICS Schema Service DB - Jay Pearson (A-Team) *
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.
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.
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.
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.
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
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
Richard Williams
Senior Director, Delivery and Architecture Strategy
Previous Post