Database Migration Assistant for Unicode (DMU)

DMU is the new tool introduced by Oracle for database conversion to the Unicode character set. Beside introducing briefly the tool, this session will focus on a real database conversion scenario faced by a customer, the problems encountered and the solutions.

1. Database Migration Assistant for Unicode (DMU ) A Real Customer Case Ludovico Caldara Oracle ACE Director Trivadis AG

2. About Ludovico Caldara Database Migration Assistant for Unicode 2 09/10/2016 17 Years DBA (Not Only Oracle) I do it everywhere (even Windows) RAC ATTACK Ninja & co-writer President, SOUG & ITOUG Board OCP (11g, 12c, MySQL) & OCE Italian living in Switzerland http:// www.ludovicocaldara.net @ ludodba ludovicocaldara

3. Our company. Database Migration Assistant for Unicode 3 09/10/2016 Trivadis is a market leader in IT consulting , system integration , solution engineering and the provision of IT services focusing on and technologies in Switzerland , Germany, Austria and Denmark . We offer our services in the following strategic business fields : Trivadis Services takes over the interactive operation of your IT systems . O P E R A T I O N

4. COPENHAGEN MUNICH LAUSANNE BERN ZURICH BRUGG GENEVA HAMBURG DÜSSELDORF FRANKFURT STUTTGART FREIBURG BASEL VIENNA With over 600 specialists and IT experts in your region. Database Migration Assistant for Unicode 4 09/10/2016 14 Trivadis branches and more than 600 employees 200 Service Level Agreements Over 4,000 training participants Research and development budget : CHF 5.0 / EUR 4 million Financially self-supporting and sustainably profitable Experience from more than 1,900 projects per year at over 800 customers

5. Database Migration Assistant for Unicode 5 09/10/2016 Conversion to Unicode

6. Why Database Migration Assistant for Unicode 6 09/10/2016 Single-byte character sets cannot represent all the characters Nowadays it is no more acceptable Well-known character sets: US7ASCII WE8ISO8859P1(5) WE8MSWIN1252

7. Issues with Unicode Database Migration Assistant for Unicode 7 09/10/2016 Variable length per character (1-4 bytes, increase in data size) Different representation for the non-US7ASCII characters Data conversion

8. Conversion to Unicode is Serious ! Database Migration Assistant for Unicode 8 09/10/2016 Be careful , you might corrupt your data!

9. Database Migration Assistant for Unicode 9 09/10/2016 DMU

10. DMU identity card Database Migration Assistant for Unicode 10 09/10/2016 Born in 2011 Last version: Check MOS Patch 21138450 ( currently 2.1) Java- based GUI tool Replaces csscan and csalter ( they are now desupported ) End-to-end migration process

11. Database Migration Assistant for Unicode 11 09/10/2016

12. Conversion Flow Database Migration Assistant for Unicode 12 09/10/2016 Analyze Prepare Fix / Clean Convert Validate

13. Database Migration Assistant for Unicode 13 09/10/2016 Prepare

14. Prepare Database Migration Assistant for Unicode 14 09/10/2016 Do it on a non-production first! Several times, reiterate to be sure the conversion will work Use a physical copy of your production Compile invalid objects ( @?/ rdbms /admin/ utlrp.sql ) Purge the dba_recyblebin List invalid objects c reate table pre_dmu_invalid as select owner, object_name , object_type from dba_objects where status != 'VALID' ;

15. Prepare ( cont .) Database Migration Assistant for Unicode 15 09/10/2016 Install the DMU support package SYS.DBMS_DUMA_INTERNAL SQL> @?/ rdbms /admin/ prvtdumi.plb Library created . Package created . No errors . Package body created . No errors . Create a separate tablespace for the DMU repository CREATE TABLESPACE DMU_REPO DATAFILE SIZE 100M EUTOEXTEND ON NEXT 100M MAXSIZE 30g; Extend the Undo Tablespace ( it requires a lot of space ) alter database datafile '/data/ orcl /undotbs01.dbf ' autoextend on maxsize 30G;

16. Prepare ( cont .) Database Migration Assistant for Unicode 16 09/10/2016 Save the size of the segments before the migration CREATE TABLE dmu_size_before AS SELECT tablespace_name , owner , segment_type , segment_name , bytes, extents FROM dba_segments WHERE tablespace_name NOT IN ( ' UNDOTBS ' , 'TEMP') ; Why ? Bigger character representation Migrated rows Chained rows

17. Database Migration Assistant for Unicode 17 09/10/2016 Analyze

18. Launch DMU Database Migration Assistant for Unicode 18 09/10/2016 Download and unzip dmu under $ORACLE_HOME/ dmu -$version Launch it : export DISPLAY=192.178.56.1:0.0 export JAVA_HOME=$ ORACLE_HOME/ jdk cd / ccv/ app /oracle/ product /12.1.0.2/dmu-2.1/ dmu sh dmu.sh Create the connection to the database

19. Repository installation Database Migration Assistant for Unicode 19 09/10/2016

20. Launch the scan Database Migration Assistant for Unicode 20 09/10/2016

21. Run the Scan Wizard Database Migration Assistant for Unicode 21 09/10/2016 Increase according to your server

22. Scan – Table properties Database Migration Assistant for Unicode 22 09/10/2016

23. Scan – Table properties Database Migration Assistant for Unicode 23 09/10/2016

24. Database Migration Assistant for Unicode 24 09/10/2016 Fix / Clean

25. All the blocking issues must be solved Database Migration Assistant for Unicode 25 09/10/2016 BLOCKING

26. Find the issues in the scan report Database Migration Assistant for Unicode 26 09/10/2016

27. Need conversion Database Migration Assistant for Unicode 27 09/10/2016

28. Need conversion Database Migration Assistant for Unicode 28 09/10/2016 Application Data will be converted during the conversion phase No action for you here Dictionary data needing conversion must be fixed ! Check on My Oracle Support! Tips For and Known Issues With The Database Migration Assistant for Unicode (DMU) Tool version 2.1 (Doc ID 2018250.1)

29. Dictionary Cleansing for my Customer Case Database Migration Assistant for Unicode 29 09/10/2016 Cleansing REG$ truncate table reg $; Cleansing AWR tables -> r ecreate AWR       SQL> @?/ rdbms /admin/ catnoawr.sql     SQL > @?/ rdbms /admin/ catawr.sql     SQL > execute dbms_swrf_internal.register_local_dbid ; Cleansing scheduler logs    exec dbms_scheduler.purge_log ; Cleansing SYS.WRP $_ REPORTS* tables (SQL Monitor and R-T ADDM reports) truncate table wrp $_reports ;     truncate table wrp $_ reports_details ;     truncate table wrp $_ reports_time_bands ;

30. Dictionary Cleansing for my Customer Case ( Cont .) Database Migration Assistant for Unicode 30 09/10/2016 Cleansing source$ Fixed the source code manually Cleansing col$ A table column name was containing a special character … OMG… Scan the dictionary again to make sure that everything has been fixed

31. Over Column Limit Database Migration Assistant for Unicode 31 09/10/2016

32. Fixing Over Column Limit – By Column Database Migration Assistant for Unicode 32 09/10/2016

33. Fixing Over Column Limit – Bulk Cleansing Database Migration Assistant for Unicode 33 09/10/2016

34. Invalid Representation Database Migration Assistant for Unicode 34 09/10/2016

35. Fixing invalid representations – Find the source! Database Migration Assistant for Unicode 35 09/10/2016 Why there is invalid data? Is it because of non- existing characters in current set? Or there is a bad configured client application? Or there is a bad configured ETL job?

36. Fixing invalid representations – Cleansing Database Migration Assistant for Unicode 36 09/10/2016

37. Fixing invalid representations – Ignoring Database Migration Assistant for Unicode 37 09/10/2016

38. Fixing invalid representations – Ignoring Database Migration Assistant for Unicode 38 09/10/2016 Use it as the last step in your cleansing phase!

39. Other Fixes Database Migration Assistant for Unicode 39 09/10/2016 Enable Row Movement on your tables to enable CTAS conversion Much faster than standard UPDATE No migrated rows after the upgrade Put your application tablespaces in AUTOEXTEND Space increase due to UNICODE conversion Space used by Create Table As Select conversion method

40. Database Migration Assistant for Unicode 40 09/10/2016 Convert

41. Convert Database Migration Assistant for Unicode 41 09/10/2016 BEFORE CLEANSING AFTER CLEANSING

42. Convert ( Cont .) Database Migration Assistant for Unicode 42 09/10/2016 You cannot have connected session to the database Alter system enable restricted session; You may want to ignore other warnings

43. Convert ( Cont .) Database Migration Assistant for Unicode 43 09/10/2016 My customer had a few issues Tables locked by the DMU itself ( retry the operation ) DMU client disconnecting (client on the DB server via VNC is the best solution) Tablespaces running out of space (set them in autoextend before converting )

44. Convert ( Cont .) Database Migration Assistant for Unicode 44 09/10/2016 Restart the database after the conversion

45. Database Migration Assistant for Unicode 45 09/10/2016 Validate

46. Install the repository in Validation mode Database Migration Assistant for Unicode 46 09/10/2016

47. Scan in validation mode Database Migration Assistant for Unicode 47 09/10/2016

48. Database Migration Assistant for Unicode 48 09/10/2016 Timing

49. A Customer’s Database Database Migration Assistant for Unicode 49 09/10/2016 500 Application Tables 250 Gb of data segments Original Charset : WE8ISO8859P1 Not critical in terms of downtime Very critical in terms of data correctness

50. Timing Database Migration Assistant for Unicode 50 09/10/2016 Scan: 10-12 minutes Conversion: 6-10 hours ( depending on parameters ) Sometimes the elapsed time changes without reason Validation scan: 8-10 minutes Allows additional time for backups

51. Speeding Up Database Migration Assistant for Unicode 51 09/10/2016 Make sure that big tables are converted with CTAS Huge tables? Consider : Expdp Truncate Convert Impdp ( Don’t forget to validate the data manually )

52. Total migration project (for one application) Database Migration Assistant for Unicode 52 09/10/2016 May 2016: First test on a fake copy June 2016: Migration of the Test environment June – July 2016: Data validation and certification of the application for Unicode July 2016: Migration of the pre -production August 2016: Refresh prod -test and 2 nd migration of the test environment August 2016: Migration of the Quality environment September 2016: Migration of the Production Downtime reserved from Friday evening to Saturday morning

53. Database Migration Assistant for Unicode 53 09/10/2016 Alternative methods

54. Alternative methods Database Migration Assistant for Unicode 54 09/10/2016 Export/Import with Data Pump Bad: No check for conversion issues Oracle GoldenGate Bad: No check for conversion issues + GoldenGate cost DMU + GoldenGate -> Near Zero Downtime Bad: GoldenGate cost , But very efficient!

55. Questions ? 09/10/2016 Database Migration Assistant for Unicode 55 Ludovico Caldara ACE Director , Senior Consultant [email protected]