Migration of Documentum database from Oracle to Sql

SandipDabhade
SandipDabhade Member
edited February 8, 2017 in Documentum #1

Dear All

we are using documentum 7.1 with oracle 12c , now we want to chnage database from oracle to sql.

Has anyone performed this activity before? what are the prerequisites,steps involved?

«1

Comments

  • DCTM_Guru
    DCTM_Guru Member
    edited January 31, 2017 #2

    If you are using the same version of content server, you can use dump and load or Composer to move objects from server to another (independent of the database).

  • haryscv
    haryscv Member
    edited February 6, 2017 #3

    Hi Sandip,

    We did tried to move from Oracle to SQL,with manual method.How ever,you will encounter many issues while moving the data from Oracle to SQL.

    We had to simulate the environment in target too.Using SQL Developer tools and import and export tool for Oracle.We had imported and exported the data to SQL.

    How ever,you need to select the appropriate data type for SQL.During the process,few rows might disappear while copying.

    Please ensure proper back up.

    Once you copied the data,you need to update the other data eg:filestore location,other ldap server,content server config objects.

    Note:This approach involves high risk and it is not advisable.Kindly do a POC before you proceed.If you need any further details.I can help you on this approach.

    Dump and Load will not work on huge Database sizes.There are other tools in the markets,which are quite expensive.

    Thanks,

    Hareesh CV

  • DCTM_Guru
    DCTM_Guru Member
    edited February 6, 2017 #4

    FYI - you can use SELECT criterion in your dump/load to dump your data in chunks (~2GB).  If you have terabytes of data, this may be tedious; however, you dont have to worry about the database differences behind the scenes since dump/load will handle the database dependencies behind the scenes.

  • SandipDabhade
    SandipDabhade Member
    edited February 7, 2017 #5

    We are having terabytes of data so I feel dump and load will not be feasible option, can you tell me more about "Using SQL Developer tools and import and export tool for Oracle.We had imported and exported the data to SQL.".

    Using DDL and DML we are planning to generate create table,views etc sqls  from oracle and will execute in sql , how you have done in your environment?

  • msroth
    msroth Member
    edited February 8, 2017 #6

    There are various vendors (and free tools) for doing Extract, Transform, and Load (ETL) operations on Documentum databases -- look around.  Doing a database migration is one of the most complicated migrations because you can't just copy the tables.  Your best bet is to use a tool that will use Documentum's API (DFC) to move the content and metadata, thus ensuring it is properly collected on the source side, and persisted on the target side.  Dump and Load is deprecated and does not account for many of the new object types in the repository (e.g., TBOs and even active workflows).  This article is dated by lays out the pros/cons/challenges of several migration approaches: 

  • SandipDabhade
    SandipDabhade Member
    #7

    Team , after a long time I am responding to this, we failed last time and now again want to perform the same activity.

    We identified database migration tool i.e SSMA using this we are able to migrate the database from oracle to SQL however all converted tables and views are coming in upper case in SQL and I feel documentum expects all table in lower, any idea how can we do this while migrating using SSMA?

  • DCTM_Guru
    DCTM_Guru Member
    #8

    There's more than copying data from one database to another. The stored procedures for Oracle vs SQL server is probably different as well, which is why multiple people have suggested using Documentum tools/apis.

  • Hicham Bahi
    Hicham Bahi E Member
    #9

    @DCTM_Guru said:
    There's more than copying data from one database to another. The stored procedures for Oracle vs SQL server is probably different as well, which is why multiple people have suggested using Documentum tools/apis.

    Documentum doesn't use stored procedures, triggers or anything like that. However, the table definitions are not 100% the same between Oracle and SQL Server. Typically, some columns are longer in Oracle than in SQL Server. It's probably best to first create a new docbase under SQL Server using the same characteristics (e.g. version, docbase id) as your original docbase. In a second step, stop the docbase and load the data (adjusting for column length and whatever is needed). Finally, update key admin objects such as dm_server_config, dm_location (see procedure for cloning for the complete list).

  • DCTM_Guru
    DCTM_Guru Member
    #10

    @bacham3 - thanks for the clarification. For some reason, I always thought that _sp views/tables were "controlled" by stored procedures. Do you know what "_sp" suffix stands for?

  • Hicham Bahi
    Hicham Bahi E Member
    #11

    https://knowledge.opentext.com/knowledge/cs.dll/kcs/kbarticle/view/KB9114451

  • DCTM_Guru
    DCTM_Guru Member
    #12

    Thanks - so it look like they are all views. Good to know.

  • SandipDabhade
    SandipDabhade Member
    #13

    With SSMA and Stored procedures i managed to migrate schema, data and converted the names,columns in lower case

    Can anyone tell me from which table the docbase process identity user name is picked up?

    At the time of starting docbase it prints DM_SERVER_I_START_SERVER Docbase *** process identity[user(System)] and throwing login failure popup, System is oracle user and now i have to change it to sql one, where this is defined ?

  • SandipDabhade
    SandipDabhade Member
    #14


  • SandipDabhade
    SandipDabhade Member
    edited November 14, 2019 #15

    Getting this error while start

  • Hicham Bahi
    Hicham Bahi E Member
    #16

    This is the user as which the process is running. "System" is wrong: your service should run as a different user, e.g. dmadmin. How did you create the docbase?

  • SandipDabhade
    SandipDabhade Member
    #17

    @bacham3 corrected the database name in registry , now the name is eCMD0014 and is my install owner, still same problem

  • SandipDabhade
    SandipDabhade Member
    #18

    after the database migraation i created the repository using option use existing user from database and selected the migrated database and user

  • Hicham Bahi
    Hicham Bahi E Member
    #19

    If you used Server_Configuration_Program.exe to configure the docbase, then I don't understand how the service could have been created with the SYSTEM account or why you would have to change the database name. A logon failure means that the credentials stored in the service are wrong. You must fix this.

  • SandipDabhade
    SandipDabhade Member
    edited November 14, 2019 #20

    @bacham3 Now installation owner name is getting print instead of SYSTEM but still same problem is appearing, i tried attaching the screenshot but its not appearing and going for approval

  • SandipDabhade
    SandipDabhade Member
    #21

  • SandipDabhade
    SandipDabhade Member
    #22

  • SandipDabhade
    SandipDabhade Member
    #23

  • SandipDabhade
    SandipDabhade Member
    #24

  • SandipDabhade
    SandipDabhade Member
    #25

  • SandipDabhade
    SandipDabhade Member
    #26

  • SandipDabhade
    SandipDabhade Member
    edited December 5, 2019 #27

    @haryscv Hi we have simulated the target environment i.e sql database, while starting the docbase we are getting below error

    We will update the dm location and other paths however more interested in knowing about this error

    2019-12-05T14:13:13.670000 6992[8924] 0000000000000000 [DM_OBJECT_W_GET_ATTR_TYPE_ERROR_NAME]warning: "attempt to read value of wrong type from attribute 0 (name object_name)"

    Thu Dec 05 14:13:10 2019[DM_STARTUP_I_DOCBASE_OWNER_NOT_FOUND]: The database user (TPMobile) is not a valid NT User. This is the user specified in your server.ini file as the database_owner attribute. If you are running the optional Replication Services package you will need to create a valid NT User account for this user.

    OpenText Documentum Content Server (version 16.4.0000.0248  Win64.SQLServer)
Copyright (c) 2018. OpenText Corporation
All rights reserved.

    2019-12-05T14:13:11.045000 6992[8924] 0000000000000000 [DM_SERVER_I_START_SERVER]info: "Docbase TPMobile attempting to open"

    2019-12-05T14:13:11.045000 6992[8924] 0000000000000000 [DM_SERVER_I_START_KEY_STORAGE_MODE]info: "Docbase TPMobile is using database for cryptographic key storage"

    2019-12-05T14:13:11.045000 6992[8924] 0000000000000000 [DM_SERVER_I_START_SERVER]info: "Docbase TPMobile process identity: user(eCMD0014), domain(tecnet)"

    2019-12-05T14:13:12.108000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize Post Upgrade Processing."

    2019-12-05T14:13:12.108000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize Base Types."

    2019-12-05T14:13:12.108000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmRecovery."

    2019-12-05T14:13:12.154000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmACL."

    2019-12-05T14:13:12.232000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmDocbaseIdMap."

    2019-12-05T14:13:12.279000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize Error log streams."

    2019-12-05T14:13:12.279000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmUser."

    2019-12-05T14:13:12.373000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmGroup."

    2019-12-05T14:13:12.451000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmSysObject."

    2019-12-05T14:13:12.545000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmSysObject Upgrade 1."

    2019-12-05T14:13:12.545000 6992[8924] 0000000000000000 [DM_TYPE_MGR_E_ALTER_BAD_ARGS]error: "Operation increase length failed on type dm_sysobject because new length is smaller"

    2019-12-05T14:13:12.545000 6992[8924] 0000000000000000 [DM_SYSOBJECT_E_LENGTH_WIDEN_FAILED]error: "The upgrade failed on widening sysobject's attribute lengths."

    2019-12-05T14:13:12.545000 6992[8924] 0000000000000000 [DM_SESSION_E_INIT_FAILURE1]error: "Failure to complete dmSysObject initialization."

    2019-12-05T14:13:12.545000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmExprCode."

    2019-12-05T14:13:12.607000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmKey."

    2019-12-05T14:13:12.670000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmValueAssist."

    2019-12-05T14:13:12.717000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmValueList."

    2019-12-05T14:13:12.763000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmValueQuery."

    2019-12-05T14:13:12.826000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmValueFunc."

    2019-12-05T14:13:12.857000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmExpression."

    2019-12-05T14:13:12.920000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmLiteralExpr."

    2019-12-05T14:13:12.951000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmBuiltinExpr."

    2019-12-05T14:13:12.998000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmFuncExpr."

    2019-12-05T14:13:13.060000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmCondExpr."

    2019-12-05T14:13:13.123000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmCondIDExpr."

    2019-12-05T14:13:13.170000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmDDInfo."

    2019-12-05T14:13:13.232000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmScopeConfig."

    2019-12-05T14:13:13.279000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmDisplayConfig."

    2019-12-05T14:13:13.326000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmNLSDDInfo."

    2019-12-05T14:13:13.373000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmDomain."

    2019-12-05T14:13:13.420000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmAggrDomain."

    2019-12-05T14:13:13.482000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmMountPoint."

    2019-12-05T14:13:13.513000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize dmLocation."

    2019-12-05T14:13:13.560000 6992[8924] 0000000000000000 [DM_SESSION_I_INIT_BEGIN]info: "Initialize Server Configuration."

    2019-12-05T14:13:13.670000 6992[8924] 0000000000000000 [DM_OBJECT_W_GET_ATTR_TYPE_ERROR_NAME]warning: "attempt to read value of wrong type from attribute 0 (name object_name)"

    2019-12-05T14:13:13.670000 6992[8924] 0000000000000000 [DM_OBJECT_W_GET_ATTR_TYPE_ERROR_NAME]warning: "attempt to read value of wrong type from attribute 0 (name object_name)"

  • artika4biz
    artika4biz Member
    #28

    Hi Sandip.

    I am trying to do the same task. I noticed the same beahviour during the repository startup: did you solve the reported issue?
    I enabled the server trace and noticed that:

    [DM_SESSION_I_INIT_BEGIN]info: "Initialize Server Configuration."

    (.....)

    [SQL] 66 SELECT * FROM dm_docbase_config_rv XE_ , dm_docbase_config_sv WE_ WHERE (WE_.r_object_id=:dmb_handle AND WE_.r_object_id=XE_.r_object_id) ORDER BY XE_.r_object_id,XE_.i_position PARAMS :dmb_handle=3c1ed2a280000103
    2020-02-21T11:30:58.448000 3756[5404] 0000000000000000 [SQL] 66 EXEC 0.0007185000
    2020-02-21T11:30:58.448000 3756[5404] 0000000000000000 [SQL] 66 Fetched 2 with batch hint 20
    2020-02-21T11:30:58.448000 3756[5404] 0000000000000000 [SQL] 66 FETCH(2) 0.0007120000
    2020-02-21T11:30:58.448000 3756[5404] 0000000000000000 [DM_OBJECT_W_GET_ATTR_TYPE_ERROR_NAME]warning: "attempt to read value of wrong type from attribute 0 (name object_name)"

    2020-02-21T11:30:58.449000 3756[5404] 0000000000000000 [DM_OBJECT_W_GET_ATTR_TYPE_ERROR_NAME]warning: "attempt to read value of wrong type from attribute 0 (name object_name)"

    I found many many DM_OBJECT_W_GET_ATTR_TYPE_ERROR_NAME errors, during the objects fetch.

  • rkavanagh
    rkavanagh E
    #29

    Hi,
    Aside from migrating from one platform to another, was there an upgrade from pre 7.2 to another version as well?

    Russell Kavanagh
    Documentum SME | Opentext

  • SandipDabhade
    SandipDabhade Member
    #30
    @YuricS there is a object in dmi_vstamp table with name sqlobject which gets created when we setup cs with sql server, in oracle setup its not there ,create empty repo with sql first and then copy all entries from dmi_vstamp table from empty repo to your migrated repo.
  • artika4biz
    artika4biz Member
    #31

    I will try this workaround this Monday, I will let you know, our results. I did you discover that?

Categories