1 / 49

Cross Platform Database Migrations Owen Ireland Technical Database Consultant DBA Services Northgate Public Services

Cross Platform Database Migrations Owen Ireland Technical Database Consultant DBA Services Northgate Public Services. Agenda. About Northgate Cross Platform Database Migration Strategies “Real World” Windows to Linux Migration POC Issues Live Issues Questions.

jonathon
Download Presentation

Cross Platform Database Migrations Owen Ireland Technical Database Consultant DBA Services Northgate Public Services

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Cross Platform Database Migrations Owen Ireland Technical Database Consultant DBA Services Northgate Public Services

  2. Agenda About Northgate Cross Platform Database Migration Strategies “Real World” Windows to Linux Migration POC Issues Live Issues Questions

  3. Northgate Information Solutions • Operates in 46 countries across 5 continents • Paid 1 in 3 of the UK’s working population • All UK police forces • 50% of UK Fire and Ambulance Services • Over 90% of local authorities • NHS, Home Office, MoJ, NPIA, DVLA • Oracle ISV Partner of the Year 2010

  4. Who Am I • Oracle DBA with 10 years experience - National Grid - Sony Professional Solutions Europe - Oracle Corporation • Contributor to Oracle Scene magazine • Speaker at DBMS SIG Meeting • 9i and 10g Oracle Certified DBA

  5. Disclaimer Northgate Public Services do not make any warranty for the accuracy of this presentation and assume no responsibility or liability regarding the use of the information contained therein.

  6. Agenda About Northgate Cross Platform Database Migration Strategies “Real World” Windows to Linux Migration POC Issues Live Issues Questions

  7. Migration Strategies Export/Import? Transportable Tablespaces? Data Pump? Streams? TransportableDatabase? Golden Gate? Data Guard? CTAS?

  8. Migration Strategies 10g Release 2 Oracle® Database High Availability Best Practices http://download.oracle.com/docs/cd/B19306_01/server.102/b25159/toc.htm Part Number B25159-01 Chapter 4.4.4 11g Release 2 Oracle® Database High Availability Overview 11g Release 2 (11.2) http://download.oracle.com/docs/cd/E11882_01/server.112/e17157/toc.htm Part Number E17157-04 Chapter 4.1.11

  9. Migration Strategies Your chosen method will depend on: • Downtime acceptable to the business • Amount of temporary disk space available • Skill level of staff • Word size of the platforms (32bit or 64 bit) • Endian format of the platforms

  10. 128 64 32 16 8 4 2 1 0 0 0 0 1 1 1 0 What is Endian Format? What decimal number does this 8-bit byte represent? Least significant bit Most significant bit 16 + 4 + 1 = 21

  11. 32768 … 512 128 64 32 16 256 8 4 2 1 0 1 0 0 0 0 0 0 0 0 0 0 0 1 0 0 Most Significant Byte Least Significant Byte What is Endian Format? The 16-bit binary representation of decimal number 258 Byte 1 Byte 0 (lowest memory address)

  12. 32768 … 512 128 64 32 16 256 8 4 2 1 0 1 0 0 0 0 0 0 0 0 0 0 0 1 0 0 Most Significant Byte Least Significant Byte Little Endian Least Significant Byte written first to disk Byte 0 (on disk) Byte 1

  13. 32768 … 512 128 64 32 16 256 8 4 2 1 0 0 1 0 0 0 0 0 0 0 0 0 0 1 0 0 Big Endian Most Significant Byte written first to disk Byte 0 (on disk) Byte 1 Most Significant Byte Least Significant Byte

  14. What is Endian Format? • The difference is trivial • Every platform does it their own way • Some platforms write data left-right, some right-left • However when data is transferred from BIG endian systems to LITTLE endian systems (or vice-versa) CONVERSION is required

  15. Example Platforms Little Endian Platforms Linux (Intel IA32/64) Windows (Intel IA32/64) Open VMS Tru64 UNIX (Alpha) Big Endian Platforms Solaris (SPARC) HP-UX (Intel IA64) HP-UX (PA-RISC) AIX (PowerPC) IBM zSeries-based Linux IBM Power-based Linux

  16. Export / Import • Source and Destination any Endian Format • Need large staging area on disk • Character Set conversion possible • Use Data Pump 10gR1 onwards (no dump files) • IMPDP NETWORK_LINK=<db-link> • Big Outage

  17. Transportable Tablespaces • Cross platform support in 10gR1 onwards • Source and Destination any Endian Format • Create an “empty” database on target platform • Transport all USER tablespaces from source to target database • System tablespace cannot be transported

  18. Transportable Database • New feature in 10gR2 • Source and Destination must be of SAME Endian Format • Empty database not required on target platform • ALL tablespaces transported in one hit • Outage depends on database size

  19. Data Guard • Build standby database then switch over! • Outage time is the time it takes to switchover • Logical standby allows upgrade using SQL Apply Rolling Upgrade (can ship upstream to higher version) • Cross Platform (Heterogeneous) Support is limited to more popular platforms

  20. Data Guard Certified Migrations

  21. Data Guard Certified Migrations 11G

  22. Data Guard Support for Heterogeneous Primary and Physical Standbys in Same Data Guard Configuration [ID 413484.1] Data Guard Support for Heterogeneous Primary and Logical Standbys in Same Data Guard Configuration [ID 1085687.1] Data Guard • If in doubt check these Support Articles

  23. Agenda About Northgate Cross Platform Database Migration Strategies “Real World” Windows to Linux Migration POC Issues Live Issues Questions

  24. 2CPU 3GB 6CPU 8GB 6CPU 8GB DASD DASD DASD ASM 32TB ASM 32TB NTFS 2TB Customer Requirements • Oracle 10.2.0.1 • 2TB database • Win32 with NTFS F/S • Direct Attach Disk • Moving to Linux x86-64 • Red Hat EL5 • Upgrading to 10.2.0.4 • Moving to ASM • New Physical Standby • Acceptable Downtime • Unknown Migrate

  25. Plan A Linux x86-64 PHYSICAL STANDBY 10.2.0.4 Win32 DB 10.2.0.1 Linux x86-32 LOGICAL STANDBY 10.2.0.4 Data Guard “Cascade” Data Guard Linux x86-64 PHYSICAL STANDBY 10.2.0.4 Linux x86-64 PRIMARY 10.2.0.4 -10g Logical S/B supports Win->Linux but only 32 bit -> 32 bit • Physical S/B supports 32bit -> 64 bit

  26. Logical Standby Build To build a logical standby you first create a physical standby and then convert it to a logical standby But when attempting to convert standby on 32bit system: SQL> ALTER DATABASE RECOVER TO LOGICAL STANDBY LOGICAL; ALTER DATABASE RECOVER TO LOGICAL STANDBY LOGICAL ERROR at line 1: ORA-00283: recovery session canceled due to errors ORA-10562: Error occurred while applying redo to data block (file# 1, block# 11050) ORA-10564: tablespace SYSTEM ORA-01110: data file 1: '+DATA/test10gdr/datafile/system01.dbf' ORA-10561: block type 'TRANSACTION MANAGED DATA BLOCK', data object# 5097 ORA-00600: internal error code, arguments: [4502], [0], [], [], [], [], [], []

  27. LOGICAL Standby Support Let me check that again in Support Note 1085687.1 Support for Heterogeneous Primary and Logical Standbys PLATFORM NAME: 7. Microsoft Windows (32-bit) PLATFORMS supported within the same DG config: 7. Microsoft Windows (32-bit) 10. Linux (32-bit) 8. Microsoft Windows (64-bit Itanium) 12. Microsoft Windows (64-bit x86-64)

  28. Data Guard Certified Migrations

  29. Oracle Support Response “The Introduction of Note 1085687.1: Data Guard Support for Heterogeneous Primary and Logical Standbys in Same Data Guard Configuration is misleading here. As per your correct Assumption a mixed Environment involving a Logical Standby Database require the same Support for Physical Standby Database to set it up. I already posted a Mail to the Owner of this Note to correct it accordingly.” Note Added: All mixed platform combinations for SQL Apply in the table below are supported from Oracle Database 11g onward.

  30. Gotcha! 10G HETEROGENEOUS LOGICAL STANDBY DOES NOT WORK!!

  31. Plan B Win32 DB 10.2.0.4 Win32 DB 10.2.0.1 Linux x86-64 PRIMARY 10.2.0.4 Upgrade Transportable DB DataGuard Linux x86-64 PHYSICAL STANDBY 10.2.0.4 • Acceptable downtime now • established as 5 days • Upgrade WinDB to 10.2.0.4 first • so one change at a time

  32. Documentation • Backup and Recovery Advanced User’s Guide – Chapter 15 - INCOMPLETE! Cross-Platform Migration on Destination Host Using Rman Convert Database [ID 414878.1] • It is not necessary to convert all datafiles, only those containing UNDO segments Avoid Datafile Conversion during Transportable Database [ID 732053.1] • Platform Migration using Transportable Database Oracle Database 11g and 10gR2 http://www.oracle.com/technetwork/database/features/availability/maa-wp-10gr2-platformmigrationtdb-131164.pdf

  33. Transportable Database So what does the RMAN> CONVERT DATABASE … command actually do?

  34. Source Database System Datafile (Data Dictionary) Undo Datafile User Datafiles x N Password File Control Files Redo Log Files PFILE FTP FTP FTP FTP FTP FTP FTP SQL> ALTER DATABASE OPEN READ ONLY SQL> @UTLIRP and UTLRP SQL> CREATE CONTROLFILE SQL> OPEN RESETLOGS orapwd password=fred Staging Area RMAN CONVERT RMAN CONVERT Target Database System Datafile Undo Datafile User Datafiles x N PFILE Control Files Redo Log Files Password File

  35. Transportable Database • Isn’t this how we used to clone databases in the days before RMAN? Steps to Manually Clone a Database[ID 458450.1] • With target system conversion, RMAN CONVERT DATABASE command creates:- • A modified parameter file (init.ora) • A RMAN script to convert datafiles • A “create controlfile” script

  36. Issues found in Test • The "create controlfile" script requires extensive editing– not so easy with 900 datafiles CREATE CONTROLFILE REUSE SET DATABASE "TEST10G" RESETLOGS FORCE LOGGING ARCHIVELOG LOGFILE GROUP 1 '+LOG' SIZE 50M, GROUP 2 '+LOG' SIZE 50M, GROUP 3 '+LOG' SIZE 50M DATAFILE 'C:\TEMP\DATA_D-TEST10G_I-945112313_TS-SYSTEM_FNO-1_1MLNV45H'', 'C:\TEMP\DATA_D-TEST10G_I-945112313_TS-UNDOTBS_FNO-2_1NLNV45H'', ... 'C:\TEMP\DATA_D-TEST10G_I-945112313_TS-UNDOTBS_FNO-9_1NLNV45J'' CHARACTER SET WE8MSWIN1252; • So backup source database control file “to trace”

  37. Issues found in Test • Transportable DB procedure requires you to run DBMS_TDB.CHECK_DB prior to migration • CHECK_DB can only be run when the database is read only so write your own check script • If using Database Control, be sure to completely drop and recreate the EM repository (SYSMAN, MGMT_VIEW user etc) [ID 278100.1] How To Drop, Create And Recreate DB Control In A 10g Database

  38. Issues found in Test • Recompiling PL/SQL packages results in ORA-07445 Thu Jul 22 12:18:08 2010 Errors in file /home/oracle/…/udump/test10g_ora_32483.trc: ORA-07445: exception encountered: core dump [_intel_fast_memcpy.A()+10] [SIGSEGV] [Address not mapped to object] [0x2B990] • Due to 32->64 bit migration we need to reload OLAP [ID 386990.1] Problem : DB CONVERSION: 32 bit -->64 Bit Broke OLAP OPTION

  39. So What About 32bit -> 64 bit? [ID 62290.1] Changing between 32-bit and 64-bit Word Sizes [ID 209766.1] Memory Requirements of DBs Migrated from 32-bit to 64-bit “The on-disk format for database data, redo, and undo is identical for the 32-bit and 64-bit installations of Oracle.” “The only internal structural difference between the 32-bit and 64-bit Oracle installations is the compiled format of PL/SQL is different.” Double shared pool size and other memory parameters!

  40. Issues found in Test • In alert log several days later … ERROR at line 1: ORA-20003: Specified bug number (5099019) does not exist ORA-06512: at "SYS.DBMS_STATS", line 11491 ORA-06512: at "SYS.DBMS_STATS", line 11515 ORA-06512: at line 1 • Software Binaries must be at exactly the same patch level across platforms • Difficult when Windows patches are distributed in patch bundles(PB) and Unix patches are in patch set updates (PSU)

  41. Agenda About Northgate Cross Platform Database Migration Strategies “Real World” Windows to Linux Migration POC Issues Live Issues Questions

  42. Issues found in Live Migration • Disks were presented as 8 x 4TB RAID5 arrays • Bug 6453944 prevents creation of ASM disk > 2TB • Create 2 x 2TB partitions on each RAID array • Sys Admin accidentally created 2 x 2GB partitions • Recreating the partitions as 2TB, ASM still sees 2GB • Running partprobe and a reboot fixed the issue [ID 452924.1] How to Prepare Storage for ASM

  43. Issues found in Live Migration • Fractured block warnings during CONVERT DATAFILE Fri Jul 02 09:28:10 2010 Hex dump of block from foreign database Hex dump of (file 2, block 670610) in trace file /oracle/admin/DB/udump/db_ora_1631.trc Corrupt block relative dba: 0x008a3b92 (file 2, block 670610) Fractured block found during reading datafile for conversion Data in bad block: type: 2 format: 2 rdba: 0x008a3b92 last change scn: 0x0000.3c8a13a4 seq: 0x1 flg: 0x04 … • DBV utility did not complain of any corruption • File corrupt at source but could be easily recreated • Worth running CONVERT DATAFILE on source db?

  44. Issues found in Live Migration • Standby database build required backup and restore RMAN-03002: failure of restore command at 07:22/2010 12:12:56 RMAN-06026: some targets not found – aborting restore RMAN-06023: no backup or copy of datafile 955 found to restore RMAN-06023: no backup or copy of datafile 954 found to restore … (repeated for about 100 datafiles) RMAN-06 (partial error displayed) • RMAN LIST BACKUP shows a valid backup! • The problem datafiles were all read only • Creating new controlfile with resetlogs prevents R/O • datafiles from ever being restored • FIX: Make datafiles R/W then take another backup

  45. Issues found in Live Migration • Grid Control target discovery doesn’t find new db • Needs an entry in /etc/oratab • Doesn’t like 2 databases with same name [ID 1214933.1] Understanding Concepts Related to Grid Control Targets [ID 781466.1] How to Manage Duplicate Targets in the Grid Console • Creation of new control file loses RMAN defaults • No automatic startup of Listener and DB on Linux

  46. Conclusions Can database files just be copied from one platform to another without conversion? Yes and No. If both source and destination are of the SAME endian format and the datafiles don't contain undo segments then Yes. All other files need to be converted/recreated. What about moving databases from 32-bit to 64-bit platforms? Yes, data files can be moved but PL/SQL modules need to be recompiled. OLAP needs to be reloaded. Cross Platform Transportable DB feature works well!

  47. Agenda About Northgate Cross Platform Database Migration Strategies “Real World” Windows to Linux Migration Questions

  48. Questions Questions and maybe some answers! … Email us at css.dba@northgate-is.com

  49. References Understanding big and little endian byte order http://betterexplained.com/articles/understanding-big-and-little-endian-byte-order

More Related