Transcript 1 - UK Oracle User Group
Cross Platform Database Migrations
Owen Ireland Technical Database Consultant DBA Services Northgate Public Services UKOUG DBMS SIG – Northgate Public Services
Agenda
About Northgate Cross Platform Database Migration Strategies
“Real World” Windows to Linux Migration POC Issues Live Issues Questions
UKOUG DBMS SIG – Northgate Public Services
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 UKOUG DBMS SIG – Northgate Public Services
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 UKOUG DBMS SIG – Northgate Public Services
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.
UKOUG DBMS SIG – Northgate Public Services
Agenda
About Northgate Cross Platform Database Migration Strategies
“Real World” Windows to Linux Migration POC Issues Live Issues Questions
UKOUG DBMS SIG – Northgate Public Services
Migration Strategies
Export/Import?
Streams?
Golden Gate?
UKOUG DBMS SIG – Northgate Public Services
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
UKOUG DBMS SIG – Northgate Public Services
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
UKOUG DBMS SIG – Northgate Public Services
What is Endian Format?
What decimal number does this 8-bit byte represent?
0 0 0
1
0
1
0
1 128 64 Most significant bit 32 16 8 4 2 1 Least significant bit
16 + 4 + 1 = 21
UKOUG DBMS SIG – Northgate Public Services
What is Endian Format?
The 16-bit binary representation of decimal number 258 Byte 0 (lowest memory address) Byte 1
0 0 0 0 0 0 0
1
0 0 0 0 0 0
1
0
32768 … Most Significant Byte 512 256 128 64 32 16 8 4 Least Significant Byte 2 1
UKOUG DBMS SIG – Northgate Public Services
Little Endian
Least Significant Byte written first to disk
Byte 0 (on disk)
0 0 0 0 0 0
1
0
Byte 1
0 0 0 0 0 0 0
1 128 64 32 16 8 4 Least Significant Byte 2 1 32768 … Most Significant Byte 512 256
UKOUG DBMS SIG – Northgate Public Services
Big Endian
Most Significant Byte written first to disk
Byte 0 (on disk) Byte 1
0 0 0 0 0 0 0
1
0 0 0 0 0 0
1
0
32768 … Most Significant Byte 512 256 128 64 32 16 8 4 Least Significant Byte 2 1
UKOUG DBMS SIG – Northgate Public Services
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
UKOUG DBMS SIG – Northgate Public Services
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 UKOUG DBMS SIG – Northgate Public Services
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=
Big Outage
UKOUG DBMS SIG – Northgate Public Services
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
UKOUG DBMS SIG – Northgate Public Services
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
UKOUG DBMS SIG – Northgate Public Services
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
UKOUG DBMS SIG – Northgate Public Services
Data Guard Certified Migrations
10G Heterogeneous Support Different Word Size (32 / 64 bit) Heterogeneous AND Word-size Physical Standby
No Win32<->Win64 Linux32<->Linux64 No
Logical Standby
Win<->Linux Only Win32->Win64 Linux32->Linux64 (1 way only) No UKOUG DBMS SIG – Northgate Public Services
Data Guard Certified Migrations
11G Heterogeneous Support Different Word-size (32 / 64 bit) Heterogeneous AND Word-size Physical Standby Win<-->Linux Solaris <-->AIX Solaris<-->Linux
Win32<->Win64 Linux32<->Linux64
Win32<->Linux64 Logical Standby
Win<->Linux Only Win32->Win64 Linux32->Linux64 (1 way only)
Win32->Linux64 (1 way only)
UKOUG DBMS SIG – Northgate Public Services
Data Guard
If in doubt check these Support Articles 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]
UKOUG DBMS SIG – Northgate Public Services
Agenda
About Northgate Cross Platform Database Migration Strategies
“Real World” Windows to Linux Migration POC Issues Live Issues Questions
UKOUG DBMS SIG – Northgate Public Services
Customer Requirements
2CPU 3GB DASD 6CPU 8GB 8GB DASD DASD NTFS 2TB Migrate ASM 32TB 32TB
UKOUG DBMS SIG – Northgate Public Services •
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
Plan A
x86 32 64 Win32 DB 10.2.0.1
Data Guard -10g Logical S/B supports Win->Linux but only 32 bit -> 32 bit
-
Physical S/B supports 32bit -> 64 bit
UKOUG DBMS SIG – Northgate Public Services
Linux Linux x86 64 x86 64 PHYSICAL PRIMARY STANDBY 10.2.0.4
10.2.0.4
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], [], [], [], [], [], []
UKOUG DBMS SIG – Northgate Public Services
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.
8.
Linux (32-bit)
Microsoft Windows (64-bit Itanium) 12. Microsoft Windows (64-bit x86-64 )
UKOUG DBMS SIG – Northgate Public Services
Data Guard Certified Migrations
10G Heterogeneous Support Different Word-size (32 / 64 bit) Heterogeneous AND Word-size Physical Standby No
Win32<->Win64 Linux32<->Linux64 No
Logical Standby
Win<->Linux Only Win32->Win64 Linux32->Linux64 (1 way only) No UKOUG DBMS SIG – Northgate Public Services
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
.
UKOUG DBMS SIG – Northgate Public Services
Gotcha!
10G HETEROGENEOUS LOGICAL STANDBY DOES NOT WORK!!
UKOUG DBMS SIG – Northgate Public Services
Plan B
Transportable DB Linux x86 64 PRIMARY 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
UKOUG DBMS SIG – Northgate Public Services
Linux x86 64 PHYSICAL STANDBY 10.2.0.4
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
UKOUG DBMS SIG – Northgate Public Services
Transportable Database
So what does the RMAN> CONVERT DATABASE … command actually do?
UKOUG DBMS SIG – Northgate Public Services
Password File PFILE
Source Database
Control Files Redo Log Files System Datafile (Data Dictionary) Undo Datafile User Datafiles x N Staging Area PFILE
Target Database
Control Redo Log Password File Files Files
UKOUG DBMS SIG – Northgate Public Services
System Datafile Undo Datafile User Datafiles x N
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: 1. A modified parameter file (init.ora) 2. A RMAN script to convert datafiles 3. A “create controlfile” script
UKOUG DBMS SIG – Northgate Public Services
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” UKOUG DBMS SIG – Northgate Public Services
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
UKOUG DBMS SIG – Northgate Public Services
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
UKOUG DBMS SIG – Northgate Public Services
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!
UKOUG DBMS SIG – Northgate Public Services
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)
UKOUG DBMS SIG – Northgate Public Services
Agenda
About Northgate Cross Platform Database Migration Strategies
“Real World” Windows to Linux Migration POC Issues Live Issues Questions
UKOUG DBMS SIG – Northgate Public Services
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 2 GB partitions
•
Recreating the partitions as 2TB, ASM still sees 2 GB
•
Running partprobe and a reboot fixed the issue [ID 452924.1] How to Prepare Storage for ASM
UKOUG DBMS SIG – Northgate Public Services
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?
UKOUG DBMS SIG – Northgate Public Services
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
UKOUG DBMS SIG – Northgate Public Services
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
UKOUG DBMS SIG – Northgate Public Services
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!
UKOUG DBMS SIG – Northgate Public Services
Agenda
About Northgate Cross Platform Database Migration Strategies “Real World” Windows to Linux Migration Questions
UKOUG DBMS SIG – Northgate Public Services
Questions
Questions and maybe some answers! … Email us at [email protected]
UKOUG DBMS SIG – Northgate Public Services
References
Understanding big and little endian byte order
http://betterexplained.com/articles/understanding-big and-little-endian-byte-order
UKOUG DBMS SIG – Northgate Public Services