How to move an r9 mart from one Oracle database to another?
You will most likely need to involve your company's DBA for this task.
The steps to move an r9 Oracle mart are much easier than it was in r8 Oracle mart and earlier.
Here is how to do it in version r9:
1. Export the Oracle database.
Make a database export for the schema owner user of the mart repository.
The schema owner user is the user who initialized the r9 mart database. That is the Oracle user who owns the mart repository's sequences, triggers, tables, and indexes.
That is also many times the same user name that was typed into the Configure MartServer window.
NOTE: a schema level export should be done, not an object-level export.
2. Import the database on the other Oracle instance.
3. Configure the MartServer node to point to the new database.
In the Configure MartServer window, fill in the fields for the new database (database type, port number, server name, database name, username, password, and so on). Start the mart server service.
As an example,
- Create a Directory file and map that file --> CREATE DIRECTORY dbdump AS ‘C:\dbdump’.
- Export the schema to the Directory file --> expdp mmadmin/mmadmin@test schemas=mmadmin directory=ddbdump dumpfile=MMADMIN.dmp logfile=expdpSCOTT.log
- Import the schema from the Directory file --> impdp mmadmin/mmadmin@test schemas=mmadmin directory=dbdump dumpfile=MMADMIN.dmp logfile=impdpSCOTT.log
NOTE: if you want the old mart and the new mart to run simultaneously, you will need to install a second mart server node on a different windows machine. Then you can have the old mart server node point to the first database and configure the new second mart server node to point to the second database. You do not need additional licenses to install additional mart server nodes.