Previously, on the saga of the Unicode Conversion universe, we converted one R/3 system and BW. As we are an MDMP shop, the conversion requires language scans to be completed so that text fields from different code pages end up decipherable by end users.
I wrote about our earlier work in Application server buffers in a Unicode conversion and Unicode 0.002.
We're now working on 2 major project tasks: converting copies of our production R/3 system and building our development copies for conversion next weekend (!).
The Space Monster
Because the source system is about 5TB allocated space, the target system requires that much room. Even though we will gain back a little space through reorganization, we can't predict which tablespaces might shrink. I'm predicting we will be close to 6TB at the time the production conversion is executed in February 2008.
We have created 46 temporary data files of 10GB each on the target system (460GB temp space), with 50 2GB files on the source (100GB). Temporary space is required on the source system during the export for sort operations on just a few tables. On the target system it's needed for index creation.
The space needed to store the export files is not as big as you might expect. We have 2.3 TB tables, and 1.4 TB of indexes (at last count) for the source, so we "only" export 2.3 TB of data, which fits into:
$ df -g .
Filesystem GB blocks Free %Used Iused %Iused Mounted on
towc11uctg-z:/C11unicode 1327.81 908.40 32% 2260 1% /C11unicode
That's about 400GB for you non-UNIX types. It would fit on a PC these days (ha ha).
But of course, we don't just put this critical data on one drive, we have mirrors, and backup copies, and the source and target systems are "temporary" for about 6 months! Not to mention that the real target system will need to be triple mirrored, and have extra copies in the other data center, and it needs to exist a few months before the conversion.
Oh, and some more space for Oracle 10 since we decided to roll the database version upgrade into the Unicode conversion.New Solutions
Checkpointing
Per the Unicode Conversion Guide:
"A new worker job starts scanning the table at the last checkpoint saved from the previous worker job."
We had difficulty getting language scans to complete, hitting ORA-1555 errors (snapshot too old), even on a temporary source system with no one using it. We were concerned about these errors hitting in production, as the scans could go on for weeks given the size of the tables involved. Our consultant recommended using checkpointing, which is only mentioned a couple times in SAP notes and the Unicode Conversion Guide. The guide seems to imply that we can set checkpointing for only a few large tables, but doesn't give examples.
Note 1049626 
applies to
- "Reset Scan" does not delete Checkpoints
- You want to convert your system to Unicode
- The system is configured as MDMP system
- You are running SPUMG for the preparation of a Unicode conversion
- The "Checkpoint Mode" is switched on
- After a "Reset Scan" check points are not deleted for the tables which have been reset. Therefore tables are not scanned completely after resetting a table.
It's one of the few to mention Unicode and checkpointing
Export Tuning
Here are preliminary run times for exports. The log shows the top 50 packages with the longest running first. We might choose to split the first few into separate packages on later runs. So far, things are looking good for a weekend outage, with a maximum export time around 13 hours on the first try, using less hardware than we'll deploy for real.
----------------------------------------------------------------------------------
package time start date end date size MB MB/min
----------------------------------------------------------------------------------
COEP-2 13:04:10 2007-11-20 12:23 2007-11-21 01:27 3689.40 4.70
COEP-1 12:37:58 2007-11-20 12:23 2007-11-21 01:01 3702.63 4.88
STXL 10:10:57 2007-11-21 09:32 2007-11-21 19:43 8720.69 14.27
Import tuning
Likewise, import is looking good, although the imports are still running as I'm writing this. We had some faults occur during the long weekend that are still being fixed.
----------------------------------------------------------------
package time start date end date
----------------------------------------------------------------
AABLG 10:54:01 2007-11-21 03:22 2007-11-21 20:23
ADRC 8:17:50 2007-11-21 03:57 2007-11-21 17:30
VBFA 6:52:22 2007-11-21 16:50 2007-11-21 23:43///--------------------------------------------------------------
table package time data index
--------------------------------------------------------------
ADRC ADRC 8:17:50 0:07:57 8:09:52
VBFA VBFA 6:52:22 3:36:29 3:15:52
SWWWIHEAD SWWWIHEAD 5:54:15 0:01:04 5:53:10
Challenges
Space estimates for system copies sometimes appear to have gremlins, as objects appear in places other than where they started, indexes act funny, and parallel queries multiply data blocks like rabbits. A few years ago, colleagues at Chevron Texaco shared tips on using Oracle resumable query methods to suspend requests for space that would normally cause application faults and then require restarts. This has been a life saver, allowing the DBAs time to react at a reasonable pace. We added logic with database triggers so that scripts run to add datafiles without intervention. This gives the DBAs time to sleep and get ready for the next challenges.
Here's what the error looks like in the alert log:
statement in resumable session 'User SAPR3(23), Session 781, Instance 1' was suspended due to ORA-01691: unable to extend lob segment SAPR3.SYS_LOB0000035484C00004$$ by 128 in tablespace PSAPEL620D
Packages can fail due to data , database, memory and other reasons. Here's what one looks like:
ERROR: 2007-11-26 22:44:33 com.sap.inst.migmon.LoadTask run
Loading of 'MBEW' import package is interrupted with R3load error.
Process '/sapmnt/C11/DISTMON/UC/R3load -i MBEW.cmd -dbcodepage 4102 -l MBEW.log -loadprocedurefast' exited with return code 2.
For mode details see 'MBEW.log' file.
I think this failed because our trigger was missing:
ORA-12801: error signaled in parallel query server P001
ORA-04098: trigger 'SYS.RESUMABLE_AFTER_SUSPEND' is invalid and failed re-validation
ORA-01658: unable to create INITIAL extent for segment in tablespace PSAPMBEWI
Table splitting is necessary to manage conversion times. The SAP tools pick a key and then generate SQL statements controlling the data distributions. Our special ledger was split on one column:
.../target/DATA/ZZGLA-9.WHR
tab: ZZGLA
WHERE ("GL_SIRID" > '010000001635221027') AND ("GL_SIRID" <= '010000001710814920')
This one had 10 packages; others have a few more.
Down to the home strech now, there are 5 tables left to import. When these complete, the SAP tools generate final reports for review.
