My Blog Followers

Tuesday, March 3, 2009

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

5 comments:

  1. hey when i importing sample dmp oracle says its corrupt
    i have oracle 10.2 enterprise edition and characterset of database is UTF8

    ReplyDelete
  2. Thanks for sharing info.
    I had a dump file created from EXP utility which i couldnt import using above method. In oracle XE machine IMP command was not working but i run import utility using command prompt with below statements

    sqlplus user/pass@xe

    host imp --> this launched import utility.

    ReplyDelete
  3. I am still finding it difficult to restore the files...lat me try it out again

    ReplyDelete
  4. Hi, I am working in cognos in CSC India.....
    for importing samples to oraclexe....i have simple way....just try it

    First create a tmp folder in c:\oraclexe\app\tmp
    and then go to C:\Program Files\IBM\Cognos Developer\webcontent\samples\datasources\oracle and extract the Zip files GOSALES.dmp, Gosalesdw.dmp,gosalesrt.dmp,gosaleshr.dmp,gosalesmr.dmp to c:\oraclexe\app\tmp
    now go to command prompt and type
    c:\>cd oraclexe
    c:\oraclexe>cd app
    c:\oraclexe\app>cd tmp
    c:\oraclexe\app\tmp>imp gosales/gosales@xe file=GOSALES.dmp full=y
    u will see data is imported...
    same command for gosalesdw,gosaleshr,gosalesmr,gosalesrt apply

    Cheers

    ReplyDelete
    Replies
    1. Arun's instructions worked well with me. Prior to executing, I had to do few adjustments in PATH variable as I had Oracle 9i and 10g XE running both in one terminal. Thanks Again!

      Delete

Ads