slide1 n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Cross Platform Database Migrations Owen Ireland Technical Database Consultant DBA Services Northgate Public Services PowerPoint Presentation
Download Presentation
Cross Platform Database Migrations Owen Ireland Technical Database Consultant DBA Services Northgate Public Services

Loading in 2 Seconds...

play fullscreen
1 / 49

Cross Platform Database Migrations Owen Ireland Technical Database Consultant DBA Services Northgate Public Services - PowerPoint PPT Presentation


  • 240 Views
  • Uploaded on

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.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Cross Platform Database Migrations Owen Ireland Technical Database Consultant DBA Services Northgate Public Services' - jonathon


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
slide1
Cross Platform Database Migrations

Owen Ireland

Technical Database Consultant

DBA Services

Northgate Public Services

agenda
Agenda

About Northgate

Cross Platform Database Migration Strategies

“Real World” Windows to Linux Migration

POC Issues

Live Issues

Questions

northgate information solutions
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
slide4

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
slide5

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.

agenda1
Agenda

About Northgate

Cross Platform Database Migration Strategies

“Real World” Windows to Linux Migration

POC Issues

Live Issues

Questions

slide7

Migration Strategies

Export/Import?

Transportable Tablespaces?

Data Pump?

Streams?

TransportableDatabase?

Golden Gate?

Data Guard?

CTAS?

slide8

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

slide9

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
slide10

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

slide11

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)

slide12

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

slide13

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

slide14

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
slide15

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

slide16

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
slide17

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
slide18

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
slide19

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
slide22

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
agenda2
Agenda

About Northgate

Cross Platform Database Migration Strategies

“Real World” Windows to Linux Migration

POC Issues

Live Issues

Questions

customer requirements

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

plan a
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
slide26

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], [], [], [], [], [], []

slide27

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)

slide29

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.

slide30

Gotcha!

10G HETEROGENEOUS LOGICAL STANDBY DOES NOT WORK!!

plan b
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
slide32

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

slide33

Transportable Database

So what does the

RMAN> CONVERT DATABASE …

command actually do?

slide34

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

slide35

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
slide36

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”
slide37

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

slide38

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

slide39

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!

slide40

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)
agenda3
Agenda

About Northgate

Cross Platform Database Migration Strategies

“Real World” Windows to Linux Migration

POC Issues

Live Issues

Questions

slide42

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

slide43

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?
slide44

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
slide45

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
slide46

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!

agenda4
Agenda

About Northgate

Cross Platform Database Migration Strategies

“Real World” Windows to Linux Migration

Questions

questions
Questions

Questions and maybe some answers! …

Email us at css.dba@northgate-is.com

references
References

Understanding big and little endian byte order

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