Oracle Migration Strategies COUG Presentation – Feb 2013

Oracle Migration Strategies COUG Presentation – Feb 2013 Feb 21, 2013 Ray Smith raymond.smith@cgi.com

Agenda: • What are my objectives today? • To getyouthinking about migration strategiesavailable to you • Fly through the slides as quickly as possible. • Trysome out.. And hopewedon’t crash and burn..

Migration strategies • Good old fashioned import/export • Datapump copy • Good old fashioned cold backup/copy (clone) • Good old fashioned hot backup / copy / recovery (clone) • RMAN clone from backup copy • RMAN clone from live • 4000 clerks and a lot of typing • Transportable tablespaces

Migration types • Identical platforms • Similar platforms • Completely different systems

Migration strategies - Identical platforms • Good old fashioned import/export • Slow but simple, requires database to be pre-created, scan through errors on import • 2/10 • Datapump copy • Slow but simple, requires database to be pre-created, sometimes issues with roles/grants. • 6/10 • Good old fashioned cold backup/copy (clone) • Simple, easy but outage increases because of copy time • 9/10

Migration strategies - Identical platforms • Good old fashioned hot backup / copy / recovery (clone) • Simple, minimal downtime to copy final archives • 10/10 • RMAN clone from backup copy • Fairly easy • 9/10 (possibly 10/10) • RMAN clone from live • Actually quite nice and fairly simple too • 10/10 • Other strategies… why complicate things – we’ve already got some great strategies above

Migration strategies – Similar platforms • Good old fashioned import/export • Slow but simple, requires database to be pre-created, scan through errors on import • 2/10 • Datapump copy • Slow but simple, requires database to be pre-created, sometimes issues with roles/grants • 6/10 • Good old fashioned cold backup/copy (clone) • Simple, easy but outage, recompilation required (utlirp/utlrp), conversion using RMAN of rollback • 8/10

Migration strategies – Similar platforms • Good old fashioned hot backup / copy / recovery (clone) • Simple, minimal downtime to copy final archives, recompilation required (utlirp/utlrp), rman conversion for rollback • 10/10 • RMAN clone from backup copy • Fairly easy, can get confused if 32-bit/64-bit conversions • 9/10 • RMAN clone from live • Actually quite nice and fairly simple too although still some issues during 32-bit/64-bit conversions • 9/10 • Other strategies… not touching them as we’ve already got some easy options… why complicate things

Migration strategies – Different platforms • Good old fashioned import/export • Slow but simple, requires database to be pre-created, scan through errors on import • 2/10 • Datapump copy • Slow but simple, requires database to be pre-created, sometimes issues with roles/grants • 6/10 • Good old fashioned cold backup/copy (clone)

Migration strategies – Different platforms • Good old fashioned hot backup / copy / recovery (clone) • RMAN clone from backup copy • RMAN clone from live • 4000 clerks and a lot of typing • Um…. • Transportable tablespaces • Database pre-creation required, RMAN conversion required, Datapump metadata export required, relatively complicated. • 4/10 for small databases • 8/10 for big databases

What shall we try? • Migrating from Solaris to Linux. • SANDBOX (11G on Solaris)  PROD1 (11G on Linux) • How different is different? • How do I find the Endian format?

Endian Format • Primary (Existing) - SANDBOX SELECT d.platform_name, endian_format FROM v$transportable_platformtp, v$database d WHERE tp.platform_name = d.platform_name; PLATFORM_NAME ENDIAN_FORMAT ------------------------- ------------- Solaris[tm] OE (64-bit) Big • Remote (new) - PROD1 PLATFORM_NAME ENDIAN_FORMAT ------------------------- ------------- Linux x86 64-bit Little

What shall we try? • Migrating from Solaris to Linux • SANDBOX (11G on Solaris)  PROD1 (11G on Linux) • How different is different? • How do I find the Endian format? • What strategies are available?

Migration strategies – Different platforms • Good old fashioned import/export • 2/10 • Datapump copy • 6/10 • Transportable tablespaces • 4/10 for small databases • 8/10 for big databases

Lets play • Migrating from Solaris to Linux • SANDBOX (11G on Solaris)  PROD1 (11G on Linux) • How different is different? Endian Formats • How do I find the Endian format? • What strategies are available? • What would I like to try?

Lets play • Datapump • Network based. • Pre-create the database • Update the TNS information • Create the directory (for the log) • Create the database link • Impdp command

Commands used in demo - network datapump • Preparation newdb – create the directory for logfile set echo on create or replace directory migrate as '/u01/oracle/PROD1_MIGRATE'; select * from dba_directories;

Commands used in demo - network datapump • Preparation newdb – create the database link connect system/letme1n set echo on set pages 1000 col OWNER format a8 col DB_LINK format a15 col USERNAME format a10 col HOST format a10 create database link migrate_link connect to system identified by letme1n using 'SANDBOX'; select * from dba_db_links; connect / as sysdba

Commands used in demo - network datapump • Preparation newdb – Precreate the tablespaces set echo on create tablespace TRAN datafile '/u01/oracle/PROD1/tran01.dbf' size 100M; create tablespace TRAN_IDX datafile '/u01/oracle/PROD1/trani01.dbf' size 75M;

Commands used in demo - network datapump • Import newdb – datapump command PARFILE: dp_migrate.par NETWORK_LINK=MIGRATE_LINK FULL=Y LOGFILE=migrate.log directory=MIGRATE Impdp system/letme1n parfile=dp_migrate.par

Commands used in demo - Transportable tablespace • Preparation newdb – precreate the users – copied from source create user JP identified by letmein; create user martin identified by letmein; create user rays identified by letmein; grant connect,resource to jp,martin,rays; grant QUERY REWRITE to RAYS; grant CREATE MATERIALIZED VIEW to JP; grant UNLIMITED TABLESPACE to JP; grant CREATE DATABASE LINK to MARTIN; grant DROP PUBLIC DATABASE LINK to RAYS; grant CREATE DATABASE LINK to JP; grant QUERY REWRITE to JP; grant CREATE VIEW to JP; grant QUERY REWRITE to MARTIN; grant CREATE MATERIALIZED VIEW to MARTIN; grant CREATE PUBLIC DATABASE LINK to RAYS; grant CREATE MATERIALIZED VIEW to RAYS; grant UNLIMITED TABLESPACE to MARTIN;

Commands used in demo - Transportable tablespace • Preparation newdb – datapump location (metadata) create or replace directory migrate as '/u01/oracle/PROD1_MIGRATE'; select * from dba_directories;

Commands used in demo - Transportable tablespace • Preparation sourcedb – make the tablespaces read only alter tablespace TRAN read only; alter tablespace TRAN_IDX read only;

Commands used in demo - Transportable tablespace • Preparation sourcedb – metadata export PARFILE: expdp.par: dumpfile=SANDBOX_meta.dp logfile=SANDBOX_meta.log directory=MIGRATE Transport_tablespaces=TRAN,TRAN_ID expdp system/letme1n parfile=expdp.par

Commands used in demo - Transportable tablespace • Datafile conversion newdb sqlfile: rman_convert.sql: convert datafile '/u01/oracle/PROD1/preconv/tran01.dbf' FROM PLATFORM 'Solaris[tm] OE (64-bit)' FORMAT '/u01/oracle/PROD1/tran01.dbf' ; convert datafile '/u01/oracle/PROD1/preconv/trani01.dbf' FROM PLATFORM 'Solaris[tm] OE (64-bit)' format '/u01/oracle/PROD1/trani01.dbf'; rman target / @rman_convert.sql

Commands used in demo - Transportable tablespace • Import newdb – datapump command Parfile: dp_migrate.par: directory=MIGRATE dumpfile=SANDBOX_meta.dp logfile=SANDBOX_meta.log transport_datafiles= '/u01/oracle/PROD1/tran01.dbf', '/u01/oracle/PROD1/trani01.dbf', '/u01/oracle/PROD1/tran02.dbf‘ impdp system/letme1n parfile=dp_migrate.par

Oracle Migration Strategies COUG Presentation – Feb 2013