How to Extract Data from Oracle Calendar Server for a migration into Exchange or Zimbra

This is Part I of a three-part series. Part II will be about converting this output and Part III is about inserting it into your target system.

So you've seen your future and it no longer involves Oracle Calendar Server (OCS).

We're there with you.

The problem we deal with now is: How to get the Data OUT of OCS.

As usual, we like to rely on the native tool set of the legacy system (less code on the legacy system implies easier to maintain the migration process as a whole).

Let’s say you run a single Oracle Calendar Server node (number 1) with administrative password “jimmorrison” and have users John Lennon, Jerry Garcia, Jimmy Page, Puffy Amiumi, and Walter Liberace. As well as the Mozart Conference Room and Shea Stadium. The following command lines will pull all data Sumatra requires:


uniuser -ls -format "%s%:%g%:%uid%:%id%:%node-id%:" -n 1 -p jimmorrison >users.txt
uniuser -resource -ls "S=*" -n 1 -p jimmorrison >resources.txt
unicpoutu -u "S=Liberace/G=Walter" -f exp-liberace.txt -n 1 -p jimmorrison
unicpoutu -u "S=Garcia/G=Jerry" -f exp-garcia.txt -n 1 -p jimmorrison
unicpoutu -u "S=Lennon/G=John" -f exp-lennon.txt -n 1 -p jimmorrison
unicpoutu -u "S=Amiumi/G=Puffy" -f exp-amiumi.txt -n 1 -p jimmorrison
unicpoutu -u "S=Page/G=Jimmy" -f exp-page.txt -n 1 -p jimmorrison

Execute this batch file to extract these data files, and place them in a convenient directory.

How this is organized, and what we do with it.

The first two lines extract the names of all users and all resources in a standard format.

Sumatra Development LLC reads these two files in first to establish the list of users and resources being migrated. These become really important to make sure the connections among users is maintained when we insert into either Exchange or Zimbra (Notes 8 coming if we get sufficient interest). The programmers among you will recognize this is crucial to maintain GUID integrity.

THEN, all user calendar data in the exp-(name).txt files are read in, and matched against these users and resources.

The users.txt file looks something like this:

Garcia:Jerry:Jerry.Garcia:256:1:
Lennon:John:John.Lennon:257:1:
Amiumi:Puffy:Puffy.Amiumi:258:1:
Liberace:Walter:Walter.Liberace:260:1:
Page:Jimmy:Jimmy.Page:262:1:

NB: It is certainly possible to script these so that the output from the initial users export can be the input for a script that automatically exports all user data/

For users with a different default Time Zone from the node time zone, manually add the Oracle Time Zone code following them in the user.txt file.

Let’s say Puffy Amiumi is based in Japan and therefore uses JST.

The resulting users.txt file would be the following:

Garcia:Jerry:Jerry.Garcia:256:1:
Lennon:John:John.Lennon:257:1:
Amiumi:Puffy:Puffy.Amiumi:258:1:JST
Liberace:Walter:Walter.Liberace:260:1:
Page:Jimmy:Jimmy.Page:262:1:


The resources.txt file looks like this:

. R=Shea Stadium/N=ResNum0000/CA=200,000/S=Steinbrenner/G=George/
+ LOC=Da Bronx\r\nNew York/PHONE=617-555-1212/FAX=617-555-1213/ID=259/
+ EMAIL=shea@sumatra.local/UID=Shea Stadium/ALLOW-CONFLICT=NO/ENABLE=TRUE/
+ LANG=en/NODE-ID=1

. R=CR Mozart/N=ResNum0002/CA=10/S=Mozart/G=Wolfgang/
+ LOC=3 Colonial Terrace Strasse\r\nWien, Ostereich/PHONE=617-555-1212/
+ FAX=617-555-1213/ID=261/EMAIL=crmozart@sumatra.com/UID=CR Mozart/
+ ALLOW-CONFLICT=NO/ENABLE=TRUE/LANG=en/NODE-ID=1/PUBLISHEDTYPE=PUBLISHED

More on OCS Calendar Data
Our recommended method for extracting Oracle data is to use the off-the-shelf Oracle utility:

UNICPOUTU

This is typically found in the directory:

C:\ocsinfra\ocal\bin on Windows servers
ORACLE_HOME$/ocal/bin on UNIX/Linux servers

The command:

UNICPOUTU –u “S=Garcia/G=Jerry” –f output.txt –n 1

will export Jerry Garcia’s entire calendar from OCS Node 1 to the output file output.txt.

You will be asked for the calendar administrator password (or alternately can pass it with the –p parameter).

This is also configurable for different start and end dates, depending on how much data you wish to transfer.



So if you only wanted to take data since January 1, 2006, you would use:
UNICPOUTU –u “S=Garcia/G=Jerry” –f output.txt -start 1 jan 2006 –n 1



NB: Dates can only be in "day month year" format.


Resources
If resources are only guests and do not have their own non-meeting events, you do not need to export them. If however you have reason to, we include the information here.

The command:

UNICPOUTR –u “R=CR Mozart” –f output.txt –n 1
Will export the Conference Room “CR Mozart” on OCS Node 1 to output.txt

Users for a node
The following command will export all of the users on Oracle Calendar Server node 1:

UNIUSER –user –ls “S=*” –n 1

The following command will export all of the resources on Oracle Calendar Server node 1:

UNIUSER –resource –ls “S=*” –n 1

Designate Access Rights
What in Outlook / Exchange are called the Delegate rights are in Oracle called Designate rights. To extract these use:

UNIACCESSRIGHTS –ls
More detail on this can be found in your Oracle Calendar Server Reference manual.
Sumatra does not currently insert Designate Access Rights into Exchange or Zimbra from OCS – but if this is a requirement for you please consult with us (the issue is more about trade-offs on the Exchange 2003 side than the Oracle side or the Zimbra).

Why not UNIICAL?
If you're asking this question you probably already know the answers:

• It does not export ATTENDEES
• It does not export ATTENDEE responses
• It exports RDATEs rather than RRULEs (which will cause you trouble down the line, not least of which because it really does a number on Outlook if you use that as a client for any target systems)

Still with me? Wait a few days for Part II: Converting and Mapping your OCS data.