Use remap when you don't want to replace existing schema

You might be taking periodic backups of your database and might need to restore backups as well. In such situations, if you do not need to overwrite existing data remap command can be used with impdp.There are options only to restore a specific set of tables. In here I will show how to restore the whole schema without replacing the existing.

1. First it is required a directory to be created so that all the exports stored in that directory and all the import dumps also will be read from the same. 
Follow this to create the directories and grant access.

2. Then lets create a backup of an existing schema. I've explained the backing-up procedure here. Else you can use following.
EXPDP X/X SCHEMAS=SCHEMA(S)_TO_IMPORT DUMPFILE=DUMPFILE_TO_IMPORT_FROM

e.g

expdp spfix/spfix schemas=spfix dumpfile=spfix_030812.dmp 

3. Now, our plan is to restore the dump on a new schema. So lets create a new user and grant access.

SQL> Create user yumani identified by yumani account unlock;

User created.

SQL> grant create session, dba to yumani;

Grant succeeded.

SQL> grant connect to yumani;

Grant succeeded.

  3. Let's restore the backup

Use this command to restore the backup using the schema you created above,
IMPDP REMAP_SCHEMA=OLD_OWNER:NEW_OWNER  DUMPFILE=DUMFILE.DMP NOLOGFILE=Y 

e.g

impdp yumani/yumani@daniddb schemas=yumani remap_schema=yumani:yumani1 dumpfile=yumani_120812.dmp NOLOGFILE=Y

Creating a MySQL database for WSO2 Carbon 3.x.x products

WSO2 Carbon products have an inbuilt H2 database. Additionally the Carbon framework allows you to switch to the databases such as Oracle, MSSQL,MySQL, Derby, DB2.

You can easily switch between these databases by;
- creating a database schema within the relevant RDBMS
- point the configuration scripts to that db
- copy the drivers
- start the server with -Dsetup
I will describe these steps in a separate post.

This post is about how you can manually configure the database. I will be using MySql in my illustration. And I will be using WSO2 Identity Server 3.0 -alpha releases for this.


Creating MySQL database for WSO2 Carbon
-----------------------------------------------------------------
1. Download a wso2 identity server 3.0 alpha release

2. Navigate to the location where you have the mysql script. i.e. CARBON_HOME/dbscripts

3. Open a command prompt from that location and login to mysql from cmd prompt

mysql -u root -p

4. Create a database. Create user and grant access

create database regdb;
GRANT ALL ON regdb.* TO regadmin@localhost IDENTIFIED BY "regadmin";

6. Run the mysql.sql script. This will configure the database.

use regdb;
source mysql.sql

7. Copy the database driver to CARBON_HOME\repository\components\lib. I used mysql-connector-java-5.1.7-bin.jar, which is the official JDBC driver for MySQL. It can be downloaded from here.



8. Configure usermanager and registry scripts, which reside in CARBON_HOME/repository/conf

registry.xml

<currentDBConfig>mysql-db</currentDBConfig>
        <dbConfig name="mysql-db">
            <url>jdbc:mysql://localhost:3306/regdb</url>
            <userName>regadmin</userName>
            <password>regadmin</password>
            <driverName>com.mysql.jdbc.Driver</driverName>
            <maxActive>80</maxActive>
            <maxWait>6000</maxWait>
            <minIdle>5</minIdle>
       </dbConfig>

user-mgt.xml

<Database>
         <URL>jdbc:mysql://localhost:3306/regdb</URL>
         <UserName>regadmin</UserName>
         <Password>regadmin</Password>
         <Dialect>mysql</Dialect>
         <Driver>com.mysql.jdbc.Driver</Driver>
    </Database>