Introduction

This technical document contains the instructions used to export an AllFusion Model Manager r7 Oracle database and then import that in another Oracle database. It also includes sample scripts that may be used during that process.

Here are the steps to export and import the AllFusion Model Manager r7 Oracle database:

1.   Export the r7 Oracle database.
   
   Do a Schema level export for the user who owns the AllFusion Model Manager schema (e.g. tables, indexes, procedures, etc.).
2. Import the export file on the other Oracle database.

Character Set 'possible character conversion' message.

When performing the import of a MM dump (Oracle 8.x) in the DOS window you may encounter something like this:

    Import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
    export client uses US7ASCII character set (possible character conversion)
    export server uses US7ASCII NCHAR character set (possible ncharset conversion)

If you encounter this message, you can continue with the import. That message does not present a problem because in Oracle dbms US7ASCII is a subset of the WE8ISO8859P1 (Western European) character set.

If the import ends successfully with no warnings then everything should be fine. If you receive an import successful message with warnings, even then the import may, or may not be OK, depending upon the warnings received. Please call Technical Support if you have any questions regarding this.

1. Fix the User IDs.
   
   When the import is finished, the AllFusion Model Manager is almost complete. The next to last step is to fix the user id's or else when logging in you will get the error 'erw_repository_status must be declared', or similar. That is because each Oracle database has different internal user ids, even for the same named user. For example, user 'Joe' on one Oracle database may have a user id of '16', yet on a different Oracle database, 'Joe' will have user id of '20'. Model Manager r7 stores those user ids in the m7usertable.
   
   To fix the user id's run the following script. You need to be logged into your Oracle query tool as the AllFusion Model Manager schema owner when you do this:

      -- SETS THE USER ID'S FOR AN R7 MART
      set pagesize 1000
      set verify off
      set feedback off
      set heading off
      spool c:\mmsetr7userids.sql
      SELECT 'update m7user set serveruserid = ' || USER_ID 
      || ' where servername = '  || '''' || USERNAME || '''' || ';'
      FROM dba_users
      WHERE EXISTS (SELECT '1' FROM M7USER WHERE servername = username)
      ORDER BY USER_ID;
      spool off
      set heading on
      set pagesize 24
      set verify on
      set feedback on
      @c:\mmsetr7userids.sql

2. Check the AllFusion Model Manager Security Manager.
   
   Check the AllFusion Model Manager Security Manager and make sure the proper users are present and they have the correct roles assigned, especially the repository owner.
   
   Below we discuss some additional items that may help you during the import process.
   
   CREATING THE USERS ON THE NEW DATABASE
   
   Here is a script that automatically adds all of the AllFusion Model Manager users to an Oracle database you have restored. Use it when you need all the users present on your database after moving it.
   
   WARNING: If you had 300 users on the old database, this script will automatically create all 300 users on the new database.
   
   NOTE: You first may need to replace the tablespaces with their proper names before executing the script. In this example, USERS is the default tablespace and TEMP is the temporary tablespace.
   
   For an r7 AllFusion Model Manager:

      set pagesize 1000
      set verify off
      set feedback off
      set heading off
      spool c:\mmuser.sql
      select 'create user ' || servername ||
      ' identified by ' || servername
      || '
      default tablespace USERS'
      || '
      temporary tablespace temp;'
      || '
      GRANT CONNECT TO ' || servername || ';'
      || '
      GRANT MMUSER TO ' || servername || ';'
      from M7USER
      order by servername;
      spool off
      set heading on
      set pagesize 24
      set verify on
      set feedback on
      @c:\mmuser.sql

   NOTE: after running this script you will need to fix the user id's as in step 3 above.
   
   TROUBLESHOOTING DIFFICULTIES
   
   Privileges / Public Synonyms.
   
   If you are later having 'privileges' problems where no one besides the schema owner can login to the AllFusion Model Manager r7 on the new database, the following scripts should help:
   
   Please first try Grants.sql.
   
   If that does not address the problem, then try the public synonyms.
   
   GRANTS.SQL
   First, replace 'mmuser' with the name of your user role. Then this script may be executed in your Oracle query tool:
   
   This script is for r7 AllFusion Model Manager.

      -- Run as schema owner, DBA role is NOT required. Thanks.
      set pagesize 1000
      set verify off
      set feedback off
      set heading off
      spool c:\mmgrants.sql
      select 'grant ALL on ' || object_name || ' to MMUSER ;' from user_objects
      where (object_name like 'M7%')
      and object_type in ('TABLE', 'PROCEDURE','VIEW','PACKAGE')
      order by object_name;
      spool off
      set heading on
      set pagesize 24
      set verify on
      set feedback on
      @c:\mmgrants.sql

   CREATE PUBLIC SYNONYM
   
   The following script will create a list of create public synonym statements to be executed in case you are missing public synonyms on any of the AllFusion Model Manager objects.  However, first, be sure to drop all of the Model Manager public synonyms.  There has been cases where a synonym has been 'broken' and as such needed to first be dropped before it could be recreated.  This script will allow you to first drop the Model Manager public synonyms:
   
   Model Manager drop public synonyms script

      -- THIS SCRIPT DROPS ALL THE PUBLIC SYNONYMS FOR an r7 ALLFUSION MODEL MANAGER
      -- Please change the name 'MODELMART' to be the name of your Model Manager schema owner
      -- before executing.
      set pagesize 1000
      set verify off
      set feedback off
      set heading off
      spool c:\mmdropps.sql
      select 'drop public synonym ' || object_name || ';'
      from all_objects
      where owner = 'MODELMART' and (object_name like 'M7%')
      and object_type in ('TABLE','PROCEDURE','VIEW','PACKAGE')
      order by object_name;
      spool off
      set heading on
      set pagesize 24
      set verify on
      set feedback on
      @c:\mmdropps.sql

   Now that the Model Manager public synonyms have been dropped, you may go ahead and recreate all of the Model Manager public synonyms using this script:
   
   Model Manager Create Public Synonyms script
   
   NOTE: 'MMADMIN' needs to be replaced with the name of the user who is the AllFusion Model Manager schema owner. CAPITALS / case counts. If you are using Oracle 9.x, please remember to capitalize the owner name.

      set pagesize 1000
      set linesize 2000
      set verify off
      set feedback off
      set heading off
      spool c:\mmps.sql
      select 'create public synonym' || object_name || ' for ' || object_name || ';'
      from all_objects
      where owner = 'MMADMIN'  and (object_name like 'M7%')
      and object_type in ('TABLE','PROCEDURE','VIEW','PACKAGE')
      order by object_name;
      spool off
      set heading on
      set pagesize 24
      set verify on
      @c:\mmps.sql

   An Unusually long time to login.
   
   If later you are trying to login to AllFusion Model Manager r7 and it is taking an unusually long time, end the ERwin task in the Windows task manager and try the following in your Oracle query tool. You will need to be logged in as the schema owner:

        delete m7session;
        delete m7lock;
        commit;

   Verify that users can login successfully and you are then ready to begin using the r7 AllFusion Model Manager.

Conclusion

Migrating the r7 AllFusion Model Manager from one Oracle database to another involves some details that you need to be aware of and account for. Following the above steps will result in a successful migration. If you do encounter any questions or difficulties, please contact Technical Support for assistance.