Teamcenter Oracle to SQL server Migration

Well, I am going to start by saying that this was a challenge to begin with. Primary reason being the absence of any Siemens supported tool that helps migrate TC Oracle DB to SQL server. On top of that GTAC does not support the process. You are on your own for the whole process.  If you dare ask GTAC any question about Oracle-SQL server migration they are going to send you a standard template answer which has all the migration steps in a nut shell. You are not supported any further for this process by GTAC.

There is an old white paper on the internet from Microsoft which gives the process of migration. This process applies to SQL server 2008 and it’s no longer valid. We tried to implement this process and we were left with a bunch of issues.

In TC 10 there are about 1500+ tables. You need to understand their DDL very accurately with Respect to SQL server. Besides the Table information, the Views/Indexes need to be redefined. Finally there are multiple settings that needed to be changed in the TC Profilevars and the DB needs to be reconnected to the TC instance.

The key aspect of this migration is that you need to validate all areas of TC including the ones that are not used by the end user very often to make sure that all functions work correctly on the new DB.

Post edit: These are the maximum level of details, that I can provide on this article here. We do not share any other details. We provide only Migration services.

The Windchill Bulk Migrator...

Before I begin, I am wondering that I can see a lot of hits for his topic, but no questions being asked no this topic. Appreciate you inputs/questions here and I will try my level best to  address them.

WBM tool touches almost each and every feature of Windchill. This is one of the latest launched tools by PTC and is used to Migrate from other products to Windchill. It can be also used to migrate from Windchill to Windchill, but honestly speaking, Upgrade Manager works better for Windchill to Windchill upgrade, unless a special case where you might want to upgrade from multiple Windchills to a single Windchill or old Windchill version to an existing Production Windchill.

Recently, I was involved in the WBM project where we migrated to Windchill from another product. The key behind a successful WBM migration project is intensive planning and source data Generation. If you can generate Meta-data of your existing data it comes in very handy. If you can create a meta data which can be seen in the form of a DB table, nothing like it.

So as I was saying, WBM project is planning/research intensive. You need to understand clearly what you have in your source and what do you need to see in Windchill. Sort out all the primary type of objects you have in your source system i.e CAD Docs/drawings, Documents. These objects can be stored on a hard drive. Next are the logical objects like WTParts, CNs etc. These act as primary objects in Windchill but are logical. A non-Windchill system will have primary objects but not necessarily have logical objects with the same mapping as Windchill.

This brings us to the next important step. With what meta-data you have from the source system you should be able to map it to the Windchill objects in the form of Windchill object’s attributes(IBAs).  Once you define a clear attribute map, you can understand which attributes from Windchill are going empty and what needs to defined in them.

Once you have this attribute map, you can go back to Windchill and create all the data that is needed to load the source system data. This can include new Object Subtypes, Users,Products, series etc. This is a non-WBM step and preferred if you can use Windchill Load utility or any other OOTB functionality for all this stuff.

Upon installing WBM software on the Target Windchill, you need to study the entire Staging database to understand what is missing in the Staging DB tables e.g. attributes of objects, extra columns. You need to check if all the attributes for a given object can be loaded using WBM or not, If yes then which table satisfies the need of which table.

Based on these tables you create a table design in which you have to mold the source data so that it can be imported into these tables. Hence it is essential if you can modify your source data into Meta-Data. WBM 4.0 comes with the ease of migrating directly from a Hard Disk. But that’s only for physical objects.

Once you define your physical and logical objects you need to understand and define the links between multiple objects types e.g. EPMBuildRule is used to connect an EPMDoc to a WTPart, Includedin2 is used to connect a Change Notice with its Change Task.

This is the tricky part and hence you need an intensive mapping and more importantly understanding of how you define your source system data.

You cannot just load all the data to WBM and run the loaders all together. The is standard sequence of loading objects to Windchill and totally dependent on what objects you have in hand.

e.g. The first objects always to go in are the EPMDocs and WTDocs since they can survive in Windchill as standalone objects.

I cannot load a Reference link object if there is no corresponding Drawing to it. Hence, we usually will load the physical objects followed by the logical objects followed by the link objects.

WBM is a new tool and you can hit a lot of bumps when working on it and not a lot of data is available on the PTC knowledge base to resolve them. Hence pilot is always recommended so that you can carry out POCs for the different objects for checking data integrity and successful migration with the sourc data in hand.

The sequential progress of a WBM projects looks something like below

Recoving Oracle..'Wind' database...

It may happen that we might have an Oracle database crash power  down situations and Oracle never comes up.  Oracle might never come up due to corrupt block or any other issues. There may be errors like ORA-01578 or corruption.

There are some simple steps we need to follow

SQL>sqlplus / as sysdba

SQL> shutdown immediate

SQL> startup nomount

SQL>alter database open;

If all these steps go through without any issues then you the lucky person and there are no issues.

In case there is a real corruption we may have to recover the database In this Situation we need to recover the database using REDO logs.

Follow below steps

Take a backup of the entire OCU folder

SQL>sqlplus / as sysdba

SQL> shutdown immediate

SQL> startup nomount

SQL> SELECT MEMBER FROM V$LOG G, V$LOGFILE F WHERE G.GROUP# = F.GROUP#  AND G.STATUS = 'CURRENT';

This query outputs the path of the redo log that we need to submit in the next query.

SQL>recover database until cancel

In this step you should the path the is obtained in the above step

SQL>alter database open resetlogs;

If none of the above work then we need to create a new set of control files in addition to the above steps,

Follow below steps

Take a backup of the entire OCU folder and delete the .CTL files from the OCU folder.

SQL>sqlplus / as sysdba

SQL> shutdown immediate

SQL> startup nomount

SQL>CREATE CONTROLFILE REUSE DATABASE "WIND" RESETLOGS NOARCHIVELOG

    MAXLOGFILES 16

    MAXLOGMEMBERS 3

    MAXDATAFILES 100

    MAXINSTANCES 8

    MAXLOGHISTORY 292

LOGFILE

  GROUP 1 'D:\ptc\Windchill_9.1\ocu\oradata\wind\WINDREDO01.LOG'  SIZE 100M,

  GROUP 2 'D:\ptc\Windchill_9.1\ocu\oradata\wind\WINDREDO02.LOG'  SIZE 100M,

  GROUP 3 'D:\ptc\Windchill_9.1\ocu\oradata\wind\WINDREDO03.LOG'  SIZE 100M

DATAFILE

  'D:\ptc\Windchill_9.1\ocu\oradata\wind\WINDBLOBS01.DBF',

  'D:\ptc\Windchill_9.1\ocu\oradata\wind\WINDINDEX01.DBF',

  'D:\ptc\Windchill_9.1\ocu\oradata\wind\WINDSYSAUX01.DBF',

  'D:\ptc\Windchill_9.1\ocu\oraata\wind\WINDSYSTEM01.DBF',

  'D:\ptc\Windchill_9.1\ocu\oradata\wind\WINDUNDOTBS01.DBF',

  'D:\ptc\Windchill_9.1\ocu\oradata\wind\WINDUSERS01.DBF',

  'D:\ptc\Windchill_9.1\ocu\oradata\wind\WINDWCAUDIT01.DBF'

;

SQL> SELECT MEMBER FROM V$LOG G, V$LOGFILE F WHERE G.GROUP# = F.GROUP#  AND G.STATUS = 'CURRENT';

This query outputs the path of the redo log that we need to submit in the next query.

SQL>recover database until cancel

In this step you should the path the is obtained in the above step

SQL>alter database open resetlogs;

These all 3 methods usually suffice to recover a crashed database. But if even these 3 methods donot recover your database then you need to get in touch with a database expert.

JExplorer..LDAP jobs made easy

The new WindChillDS UI is too complex and boring to work on. I tried to search some tools which could be handy to work on LDAP and found this tool JExplorer.

This tool gives the feel of the old Aphelion browser and has many cool feature that makes it way handy than WindChillDS.

Where to get it…

http://jxplorer.org/downloads/users.html

it is pretty straight forward to use .

Its features;

http://jxplorer.org/features.html

Here is if you need more information,

http://jxplorer.org/documents/JXv3.2_Administrator_Guide.pdf

Tell me…what you want to see

I am always thinking of new topic to put up here to help many others who are into Windchill….please reply to this topic below if you want to see any specific topic here that you think might not only help you but several other PLM engineers as well. I will try my best to write an article on it.

Regards,

Rohan