1 / 26

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 get you thinking about migration strategies available to you Fly through the slides as quickly as possible.

miracle
Download Presentation

Oracle Migration Strategies COUG Presentation – Feb 2013

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. Oracle Migration Strategies COUG Presentation – Feb 2013 Feb 21, 2013 Ray Smith raymond.smith@cgi.com

  2. 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..

  3. 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

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

  5. 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

  6. 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

  7. 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

  8. 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

  9. 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)

  10. 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

  11. 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?

  12. 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

  13. 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?

  14. 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

  15. 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?

  16. 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

  17. 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;

  18. 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

  19. 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;

  20. 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

  21. 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;

  22. 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;

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

  24. 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

  25. 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

  26. 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

More Related