Migrate Data from Oracle BICS to Oracle DBaaS or to on-premise Database

We have recently moved our on-premises OBIEE deployment to Oracle Business Intelligence Cloud Service (Oracle BI Cloud Service).  As a part of this, data from our Oracle 11g DB is moved to the BICS - Schema as Service database.

Now that we have decided to move from BICS to Oracle Analytics Cloud (OAC) to get the additional benefits. Here I will be explaining the process involved in moving the data from the BICS schema to Oracle 12c Database as Service (DBaaS).  Moving the DB objects from BICS to DBaaS can be done either with data or without.  But the process remains the same;   It all depends on how we export the BICS data.

Here are the detailed Steps

1) Login to BICS using the Cloud Services URL.

2) Open the apex Database Schema where you will see the Service Details : apex(Oracle Database Cloud Service)

3) Click on the Exports on the left and then on "Export Data"

4) You will be prompted to confirm whether the export is with data or without

5) Once you confirm it will take few minutes (depending on the data volume) and you will see the export dump (Status will change to Available)

6) With the SFTP details that you see on the same screen you should be able to download the files to your local machine.  I use winSCP here for moving the .dmp to local machine

 Note :

1. If you don't know  the password for the SFTP user, you can reset it using the Navigation Menu on the same screen (Three Dashes on the top left) -> Users -> SFTP Users
2. Currently there is no option to specify the objects in the export, so it will be full export with or without data.
3. BICS will automatically specify a name for the dump and we cannot change it during the export
4. You will also notice a warning saying "Export files are available for 2 days and downloadable from your secure FTP download area in the outgoing directory."  But as of today, I can see the .dmp files more than 10 days on the SFTP server although it is not appearing in the export list 

7) Once the file is on local, transfer the export dump to DBaaS host via WinSCP or any similar tool. This may require the ssh keys for password less authentication

8) One important point to note here is the Tablespace and Schema of BICS database will be something different from what the tablespace and schema what we wanted to have in DBaaS.  This shouldn't be a problem as the data pump utility allows us to remap them as a part of import.

9) Login to the DBaaS host via. Putty (same ssh keys mentioned in point 7 is required here).

10) Create a table space in DBaaS to import the BICS objects.  Schema creation is not required as it will be a part of import.  Sample create statement is shown.

CREATE TABLESPACE TEST_TS
    DATAFILE 
        '/u01/app/oracle/oradata/ORCL/test_ts.dbf' SIZE 1240000 AUTOEXTEND ON NEXT 1  
    MAXSIZE 52457300 
    BLOCKSIZE 8192 
    NOLOGGING 
    DEFAULT NOCOMPRESS 
    ONLINE 
    SEGMENT SPACE MANAGEMENT AUTO 
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;  -- Do not copy/paste as is


11) Create a directory called dpdump under '/u01/app/oracle/admin/ORCL or any other directory of your convenience.

12) Chmod to 755

13) Move the <export>.dmp transferred from the local to this directory.

connect to PDB (PDBORCL in my case) via SQLPLUS as SYSDBA.  You need to know the SYS or SYSTEM password.  A normal user (abc.xyz@company.com with which we login the Cloud console -> SQL workshop is not sufficient)

CREATE OR REPLACE DIRECTORY BICS_TO_DBAAS as '/u01/app/oracle/admin/ORCL/dpdump/';

GRANT READ, WRITE ON DIRECTORY BICS_TO_DBAAS to sys;

Exit SQLPLUS.

14) From the command prompt run the impdp

impdp sys/<SYSPASSWORD> SCHEMAS=BICS_SCHEMA remap_schema=BICS_SCHEMA:DBaaS_Schema remap_tablespace=BICS_TABLESPACE:DBaaS_TABLESPACE  directory=datapump dumpfile=<export>.dmp

Note : If you ignore the remap_schema then the name of the schema in DBaaS will be just the one in BICS (This may not be appropriate)

15) Login and verify the DB objects created in DBaaS database via SQL Developer or any similar tool.  Note, the process the moving the BICS to on-premise database is just the same.