Importing database dump files with impdp

In this guide, we'll cover how to import a DMP file into an oracle database using impdp:

Creating sample data viz and dashboards is a common request when we talk about 'the art of the possible'.  If we're lucky, we can use the "Sample Sales" data set provided OOTB with OBIEE and OAC. Since the beginning of OBIEE time, Sample Sales has served us well. Fast forward to 2018, we're seeing an increase in demos and proof of concepts with client/customer data, and it's not in excel files anymore.

We're getting data sets which are database extracts, commonly called DUMP files.   It's a single file representation of an entire data set within a schema, including tables, fields, permissions and the actual data. Importing dump files into an oracle database can be done via datapump the command line 'impdp'.

Here's how to import a dmp file to an Oracle 12c database. We'll be using command-line sqlplus and an  oracle 12c database which are both in a virtual machine running Oracle Linux. This can also be done using the Data Pump Import Wizard using SQL Developer.

Step 1. Know your database directories 

I found this to be the most common point of failure when importing data via impdp.  Know the dba directories within your oracle db by running. Make sure you're logged in as sysdba:

sqlplus sys as sysdba;
select * from dba_directories;

Step 2. Create and grant applicable privileges

CREATE USER BISAMPLE IDENTIFIED BY Helloworld123;

Creates a schema BISAMPLE with password Helloworld123.

GRANT CONNECT, RESOURCE, DBA TO BISAMPLE;

We are assuming that this user is a power user that can:

1. Allows the user to connect and query the BIDEMO schema (connect)
2. Allows the user to create named types for custom schemas (resource)
3. Allows the user to not only create custom named types and alter and destroy them as well (DBA)

8.  GRANT READ, WRITE ON DIRECTORY DATA TO BIDEMO;

Gives BIDEMO user access to dba_directory 'data', in this example its located at /home/oracle/downloads

Step 3. Import the dump file

    impdp system@orcl full=y directory=DATA dumpfile=BISAMPLE.DMP

Running impdp via command line as outlined above will import the BISAMPLE.dmp file located in /home/oracle/downloads to the schema BISAMPLE.

Step 4. Verify the import

Take a second to log in as the user we created (BISAMPLE) and verify that the quantity of tables we imported are actually in the schema

    sqplus BISAMPLE;
select table_name, status, num_rows from USER_TABLES;

In this import we expect to see 62 tables added to the BISAMPLE schema as verified below

Now we can use the awesome capabilities of Oracle Analytics to connect to this db to perform mash ups, analytics and advanced data viz! I know alot of you guys were asking this as a follow up to our earlier post on creating a shared folder w/in an Oracle Linux Virtual Box.  This post should help. Now you can:

1. Create or receive a DMP file on your host machine, or via another user
2. Transfer the DMP file to the target machine which contains the isolated oracle database
3. Use the OBIEE 12c publically available image to create interactive reports, dashboards, data viz, and more! 

Happy demoing!

Create a shared folder with an Oracle Linux VirtualBox + Windows 10

With the release of the OBIEE 12c virtual appliance  there's plenty of opportunity conduct demo days, proof of concepts and daily-in-the-life scenarios for your customers and stakeholders. A recurring challenge we continue to hear is the ability to upload custom data sets or BAR files within the VM.

Friendly reminder that the VM is an encapsulated 'machine-in-a-box' that has no network connectivity to your local machine (the host). To enable file sharing between your Virtual Machine and your host, remember to create a shared folder. There's some confusion on how to do this, so we'll explain as follows.

Keep in mind, the 'host' is your local machine, the 'guest' is the virtual machine, and the 'mount' is the shared folder between the host and guest. In this example, the host is a windows 10 machine provisioned by a client, and the guest is the OBIEE 12c v607 Virtual Appliance VM (Oracle Linux) installed on the host accessed via VirtualBox.

Step 1. Identify a folder to share on your host machine.

In this case we'll create a folder in our C:\ drive called shared_vm

Step 2. Add the shared folder to the VirtualBox share

Within your Oracle VirtualBox VM setting navigate to shared folders and mount C:\shared_vm. This tells the VM that C:\shared_vm is the shared folder on the host.

Step 3. Insert the Guest Additions CD Image within the VirtualBox

This is where alot of people fail. I've seen numerous blogs and youtube videos where people attempt to run the guest addition CD image on the host. Guest Addition = guest machine. Do not run the image on the host (windows), run it within Virtual Machine (in this case Oracle Linux). 

By running Devices -> Insert Guest Images CD, you'll be prompted with a selection to 'Open Autorun Prompt'. Not you'll be required required to enter the root password for this image which is 'Admin123'. This isn't a secret, all user/passwords within the appliance are accessible via the default link within firefox.

You'll be prompted with a notification that modprobe vboxsf has failed. modprobe is a function to add or remove module's from the linux OS kernel. 

Execute

sudo usermod -aG vboxsf $(whoami)

to add the logged in user to the vbox group, then restart the server. 

Step 4. Create a shared folder within the guest machine and mount to the virtualbox shared folder

In this example we created a folder called 'shared' in /home/oracle. 

Mount the virtualbox shared folder (called "shared_vm" which was created step 2) to /home/oracle/shared by executing

sudo mount -t vboxsf -o uid=1000,gid=1000 shared_vm /home/oracle/shared

To verify, we created a file  in our host location c:\shared_vm which is also visible in the guest folder /home/oracle/shared

Happy demoing!