Import and Export database utility of Oracle XE

Hi all,

 

Being from data warehouse technologies, I daily deal with databases, reporting tools, etl processes, its more like a basic routine for me. Few days back we (Team) wanted to setup Cognos 8.4 version in our RnD lab. We all were excited with the thought that we will learn something new form the latest tool. But there was a issue with the database license. Unfortunately we could not get any extra SQL server 2005 license. So it was like a major issue. Then I came up with the free version database server; and after googling my search, I came up with ORACLE XE free database server.

 

At last we got some free database server. Now there was one more challenge of installing it and configuring it. But again we got some documents through which we could install the oracle XE easily.

 

After installing we created the content store for the Cognos 8.4 and my Cognos was working smooth. But as such there were no packages such as Gosales and Godatawarehouse in the Cognos server and due to which we can not move ahead with the reporting. Now next task was to import data source from Cognos to oracle XE. I faced so much problem to import the database or u can say the dump file. My other team member created the schema for each dump file and they were just waiting for my query to import the database. Then after few minutes I got the steps to perform import and export of dump files.

 

I thought of the complexities of importing and exporting the dump file, it was really tough, so my manager told me to document each steps properly so that even other can follow that. So made a document but the I thought why don’t I share this experience with others and hence I wrote this blog.

 

So steps are as follow.

 

1)      Create tmp directory in oracle. And store all the dump files in tmp directory.

 

MKDIR c:\oraclexe\app\tmp

 

2)      Login with the system/password in SQLPLUS.

3)      Create a directory object named dumpdir for tmp directory that u just creared. For that execute following command.

 

CREATE OR REPLACE DIRECTORY dmpdir AS 'c:\oraclexe\app\tmp';

GRANT READ,WRITE ON DIRECTORY dmpdir TO gosalesdw;

 

Gosalesdw is the schema name where I want to import the dump file.

 

4)      Execute following commands to import the dump file to gosalesdw schema.

 

impdp SYSTEM/password SCHEMAS=gosalesdw DIRECTORY=dmpdir DUMPFILE=gosalesdw.dmp 

 

After this tables and all the data gets imported from the dump file to oracle schema. Here I completed the restoration of Cognos sample data source. Then we created the packages and published it to Cognos portal and we started reporting.

 

It was really a nice exercise for us. Because since morning we were struggling to restore the dump file. I enjoyed this scenario a lot. I hope this blog may help to restore the oracle dump files.

 

Cheers!!

C1PH3R