Presentation upgrade, migrate & consolidate to oracle database 12c & 11g r2 – overview

1 Copyright © 2013, Oracle and/or its affiliates.All rights reserved. Insert Information Protection Policy Classification from Slide 12 Stay Connected BLOGS.ORACLE.COM/IMC TWITTER.COM/ORACLEIMC YOUTUBE.COM/ORACLEIMCTEAM FACEBOOK.COM/OPN.PARTNERHUB.MIGRATI ON.CENTER

Upgrade, Migrate & Consolidate to Oracle Database 12c & 11gR2 – Overview Alaa Fahmy Core Technology Product Leader – ECEMEA

New Features in Oracle 12c Preparing to Upgrade Upgrade & Migration Cases Fallback Strategies Performance Management Q&A Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 3

Application Development Big Data Consolidation Data Optimization Data Warehousing High Availability In-Memory Performance & Scalability Security & Compliance Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 4

Multitenant Environment – The Concept!  Consolidation  Reduced maintenance load  Simplified and fast provisioning, cloning and migration  The Option name is Oracle Multitenant Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 5

 You don't have to use pluggable databases ... yet  Oracle Database 12c non-CDB works as expected – But you can also use the new features – and this will require changing old habits Earth is Still a Sphere! Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 6

 One SGA, one set of background processes, and one spfile – Benefit due to resource sharing – Applications will connect to the PDBs – Up to 253 PDBs in one CDB Behind the scenes CDB$ROOT PDB$SEED PDB PDB PDB PDB spfile Redo Control Flashback Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 7

 After plugging a PDB into a CDB: – Data resides in the PDB – References will get created in the CDB – In exchange access to dictionary objects from within the PDB CDB-PDB: Who's who? CDB$ROOT PDB$SEED OBJ$ TAB$ SOURCE$ PDB EMP DEPT OBJ$ TAB$ SOURCE$ Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 8

 Anything not yet supported? Check the README! – Continuous Query Notification (CQN) – Client Side Cache – Flashback Data Archive (FDA) – Flashback Transaction Query – Flashback Transaction Backout – Heat map – Automatic Data Optimization – Oracle Streams – Database Migration Assistant for Unicode (DMU)  White paper: – http://www.oracle.com/technetwork/database/multitenant-wp-12c-1949736.pdf Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 9 CDB References

New Features Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 10  Automatic Data Optimization  Extreme Availability  Security Features  Remarkable Enhancements

Automatic Data Optimization  Simplifying the life cycle of data  An in-memory heat map tracks access to segments and blocks – Data is periodically written to disk – Information is accessible by views or stored procedures  Uses can attach policies to tables to compress or tier data based on access to data – Tables or Partitions can be moved between compression levels whilst data is still being accessed Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 11

Automatic Data Optimization  Add compression and tiering policies to tables Compress Partitions with Hybrid Columnar Compression if they haven’t been modified from in 180 days Oldest Data Most Recent Data Compress Partitions with Advanced Compression if they haven’t been modified in 30 days 12 CopCyroigphytr©igh2t0©122, O01ra3c,leOarancdl/eoraintsda/offriliiatsteas.ffAililal rtiegsh.tsArlelsriegrhvetsd.reseCrvoenfdid.ential – OraUcplegRraedsetr,icMteidgrate and Consolidateto Oracle Database 12c 12

Automatic Data Optimization  A heat map tracks the activity of segments and blocks Oldest Data Most Recent Data 13 CopCyroigphytr©igh2t0©122, O01ra3c,leOarancdl/eoraintsda/offriliiatsteas.ffAililal rtiegsh.tsArlelsriegrhvetsd.reseCrvoenfdid.ential – OraUcplegRraedsetr,icMteidgrate and Consolidateto Oracle Database 12c 13

Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 14 Automatic Data Optimization: Heat Map Future Enterprise Manager screens - WIP

Automatic Data Optimization  Policies are automatically applied to tables Oldest Data Most Recent Data If after 30 days partitions haven’t be modified they will be compressed using advanced row compression 15 CopCyroigphytr©igh2t0©122, O01ra3c,leOarancdl/eoraintsda/offriliiatsteas.ffAililal rtiegsh.tsArlelsriegrhvetsd.reseCrvoenfdid.ential – OraUcplegRraedsetr,icMteidgrate and Consolidateto Oracle Database 12c 15

Automatic Data Optimization  Policies are automatically applied to tables Oldest Data Most Recent Data After 180 days of no modification, the oldest partition is identified to be compressed using Hybrid Columnar compression 16 CopCyroigphytr©igh2t0©122, O01ra3c,leOarancdl/eoraintsda/offriliiatsteas.ffAililal rtiegsh.tsArlelsriegrhvetsd.reseCrvoenfdid.ential – OraUcplegRraedsetr,icMteidgrate and Consolidateto Oracle Database 12c 16

Automatic Data Optimization  Policies are automatically applied to tables Oldest Data Most Recent Data 17 CopCyroigphytr©igh2t0©122, O01ra3c,leOarancdl/eoraintsda/offriliiatsteas.ffAililal rtiegsh.tsArlelsriegrhvetsd.reseCrvoenfdid.ential – OraUcplegRraedsetr,icMteidgrate and Consolidateto Oracle Database 12c 17

Automatic Data Optimization  Reduce storage footprint, read compressed data faster Oldest Data Most Recent Data 18 CopCyroigphytr©igh2t0©122, O01ra3c,leOarancdl/eoraintsda/offriliiatsteas.ffAililal rtiegsh.tsArlelsriegrhvetsd.reseCrvoenfdid.ential – OraUcplegRraedsetr,icMteidgrate and Consolidateto Oracle Database 12c 18

Automatic Data Optimization  Automatically tier data to lower cost storage Oldest Data Columar Compression and moveMiotsttRoeceTntiDearta 2 Storage If the tablespace is nearly full then compress the oldest partition with Archive Hybrid 19 CopCyroigphytr©igh2t0©122, O01ra3c,leOarancdl/eoraintsda/offriliiatsteas.ffAililal rtiegsh.tsArlelsriegrhvetsd.reseCrvoenfdid.ential – OraUcplegRraedsetr,icMteidgrate and Consolidateto Oracle Database 12c 19

Extreme Availability Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 21  Active Data Guard Far Sync  Other Data Guard Enhancements  RMAN Enhancements  ASM and RAC

Zero Data Loss Challenge Primary Standby The longer the distance, the larger the performance impact  Trade-off between Zero Data Loss and Performance Commit CommitAck Network Send Network Ack Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 22

Active Data Guard Far Sync Example, New York  London Primary Standby Far Sync Instance (Active) Far Sync Instance (Idle) Bridgeport New York Reading London Async Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 23

Extreme Availability Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 24  Data Guard Far Sync  Other Data Guard Enhancements  RMAN Enhancements  ASM and RAC

Data Guard Real-Time Cascading Primary Standby 1 Standby 2  In Oracle 12.1  Standby 1 forwards redo to Standby 2 in real-time, as it is received: no propagation delay for a log switch  Standby 2 is up-to-date for offloading read-only queries/reports SYNC or ASYNC ASYNC  Purpose: Eliminating propagation delay  In Oracle 11.2  Standby 1 waits until log switch before forwarding redo from archived logs to Standby 2 Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 25

1 01 01 01 0 1 1 1 01 001 1 01 1 01 1 1 1 0 00 1 01 0000 1 001 1 01 1 01 0 01 Table Recovery From Backup  Simple RECOVER TABLE command to recover one or more tables (most recent or older version) from an RMAN backup  Eliminates time and complexity associated with manual restore, recover & export – Enables fine-grained point-in-time recovery of individual tables instead of the contents of the entire tablespace – Restores the recovered table to an Oracle Data Pump dump file – Optionally imports the dump file and renames the table in the target database RMAN Backups Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 27

Cross-Platform Backup & Restore  Simplifies procedure for platform migration  Minimize read-only impact with multiple incremental backups  Simplified Platform Migration – Uses Data Pump for metadata, backup sets for data files Source Database (AIX) Destination Database (Linux) Restore Backup (optional endian conversion, metadata import) 11101 10110111101 0010100001 1001 Backup to Disk/Tape (data files, optional endian conversion, metadata export) Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 28

ASM Overview Wide File Striping  Oracle Database 11.2 or earlier RAC Cluster ASM Cluster Pool of Storage Node2 Disk Group B ASM Disk Disk Group A Node3 Node5Node4 Shared Disk Groups One to One Mapping of ASM Instances to Servers Node1 ASM Instance Database Instance DBA DBA DBB DBB DBB DBC ASM ASM ASM ASM ASM Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 30

Oracle ASM 12c – Overview  Oracle ASM 12c Standard Deployment RAC Cluster ASM Cluster Pool of Storage Disk Group B ASM Disk Disk Group A Shared Disk Groups Wide File Striping One to One Mapping of ASM Instances to Servers ASM Instance Node4Node3Node2Node1 Node5 ASM ASM ASM ASM ASM ASM Instance Database Instance DBA DBA DBB Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 31 DBCDBB DBB

Introducing Oracle Flex ASM ASM Cluster Pool of Storage Disk Group A Shared Disk Groups Wide File Striping Databases share ASM instances  Removal of One to One Mapping and HA RAC Cluster Disk Group B ASM Disk ASM Instance Database Instance Node5Node4Node3Node2Node1 Node5 runs as ASM Client to Node4 NNooddee11 r ruunnssaass AASSMM CClliieennttttoo NNooddee24 Node2 runs as ASM Client to Node3 ASM ASM ASM ASM Instance DBA DBA DBB Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 32 DBCDBB DBB

Supporting Pre-Oracle 12c Databases ASM Cluster Pool of Storage Disk Group A Shared Disk Groups Wide File Striping  Pre-Oracle 12c Databases require a local ASM instance RAC Cluster Disk Group B ASM Disk Databases share ASM instances ASM Instance Database Instance Node5Node4Node3Node2Node1 ASM ASM ASM DBA DBA DBB DBC ASM ASM 11g DB 11g DB DBB DBB Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 33

Oracle Advanced Security  Real-time sensitive data redaction based on database session context  Library of redaction policies and point- and-click policy definition  Consistent enforcement, policies applied to data  Transparent to applications, users, and operational activities Redaction of Sensitive Data Display Preventive Control for Oracle Database 12c ed Credit Card Numbers 4451-2172-9841-4368 5106-8395-2095-5938 7830-0032-0294-1827 Redaction Policy xxxx-xxxx-xxxx-4368 4451-2172-9841-4368 Billing DepartmentCall Center Application Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 35

eserved. Upgrade,Migrate and Consolidateto Oracle D Data Masking  Mask At-Source  Minimize sensitive data exposure  Securely Provisioning Test Systems 01001011001010100100100100100100100100100100100010 01010100100100100111001001001001001001000010010010 11100100101010010010101010011010100101010010 Subsetted & Masked Data Pump File 12.1 Prod Test At-Source Masking  Production data subsetted first  Sensitive data masked separately 0100101100101010010010010010010010010010010010001 0010101001001001001110010010010010010010000100100 1011100100101010010010101010011010100101010010 Prod Data Subset Clone &Mask 11.2 Test Copyright© 2013, Oracle and/orits affiliates. All rights r atabase 12c 36

Oracle Database 12c Enterprise  Turn on privilege capture mode  Report on actual privileges and roles used in the database  Helps revoke unnecessary privileges  Enforce least privilege and reduce risks  Increase security without disruption Discover Use of Privileges and Rol Administrative Control for Oracle Database 12c es Privilege Analysis Create… Drop… Modify… DBA role APPADMIN role Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 37

Online Move of Datafiles  Rename or relocate datafiles online – Move from one type of storage to another, or into ASM – Examples:  Rename:  Relocate:  Copy:  Move into ASM: ALTER DATABASE MOVE DATAFILE '/data/user1.dbf' TO '/data/user001.dbf'; ALTER DATABASE MOVE DATAFILE '/data/user1.dbf' TO '/test/user1.dbf'; ALTER DATABASE MOVE DATAFILE '/data/user1.dbf' TO '/test/user1.dbf' KEEP; ALTER DATABASE MOVE DATAFILE '/data/user1.dbf' TO '+DATA/data/datafile/user1.dbf'; Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 39

Row Limit  Example: Select only the first 5 rows SELECT employee_id, last_name FROM employees ORDER BY employee_id FETCH FIRST 5 ROWS ONLY; SELECT employee_id, last_name, salary FROM employees ORDER BY salary FETCH FIRST 5 PERCENT ROWS WITH TIES; Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 40 Select the first 5% of rows and those whose salary “ties” with the lowest of the 5%

New Features in Oracle 12c Preparing to Upgrade Upgrade & Migration Cases Fallback Strategies Performance Management Q&A Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 41

2025 2024 2023 2022 2021 2020 2019 2018 2017 2016 2015 2014 2013 2012 2011 2010 2009 2008 2007 2006 2005 2004 2003 2002 Oracle 9.2 (GA: Jul 2002) Oracle 10.1 (GA: Jan 2004) Oracle 10.2 (GA: Jul 2005) Oracle 11.1 (GA: Aug 2007) Oracle 11.2 (GA: Sep 2009) Oracle 12.1 (GA: Jun 2013) #1 Reason for Upgrade JUL 2010JAN 2007 Sustaining Support JAN 2012JAN 2009 AUG 2015AUG 2012 JAN 2018JAN 2015 today today JUN 2021JUN 2018 Limited ESWaived Extended Support Extended SupportPremier Support JUL 2015JUL 2013JUL 2010 •http://www.oracle.com/us/support/lifetime-support/index.html •Note: 209768.1 Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 42

Some MAGIC Questions Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 43 Question? Answer Change to new hardware? Change to new operating system architecture? Change the characterset? Consolidation (schema/database/...) planned? Number of database to upgrade/migrate? Size of database(s) getting upgraded/migrated? Exact source and target Oracle version(s)? Max allowed downtime? Fallback requirements? Test environment and testing tools? Does a performance baseline exist? Change required to enable New Features?

Upgrade Companion  Note:1462240.1  Upgrade Companion 12c – now available  Includes references to 10g, 11g & 11gR2 upgrade companions Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 44

Upgrade OTN Page  http://otn.oracle.com/goto/upgrade (http://www.oracle.com/technetwork/database/upgrade/index.html) Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 45

 Step-by-Step vs Big Bang – Upgrade/migration in pieces over a period of time  Considered as risk mitigating – Everything in one downtime window  Sometimes necessary due to dependencies  Least critical systems first vs most critical system first – Learn more with every step vs: – Learn about almost all from the beginning Strategies? Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 47

 Database Upgrade – A database upgrade is called "Upgrade" because the data dictionary will get upgraded to a later version – This will include Oracle components, options such as JAVAVM, SDO – No data will be touched, changed or moved  Migration – A database migration will happen when the database will move to new: Hardware, operating system platform, character set  Software Upgrade – In-Place:  The new software will be installed into an existing SW location – Out-Of-Place  The new software will be installed into a new software location Terminology Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 48

Upgrade to Oracle Database 12c Oracle 7.3 (GA: 1996) Oracle 8.0 (GA: 1997 Oracle 8.1 (GA: 1998) Oracle 9.0 (GA: 2001) Oracle 9.2 (GA: Jul 2002) Oracle 10.1 (GA: Jan 2004) Oracle 10.2 (GA: Jul 2005) Oracle 11.1 (GA: Aug 2007) Oracle 11.2 (GA: Sep 2009) Oracle 12.1 (GA: Jun 2013) Please note: This graph will apply to database upgrades only! Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 49 Oracle 7.3.4 Oracle 8.0.6 Oracle 8.1.7.4 Oracle 9.0.1.4 Oracle 9.2.0.8 Oracle 10.1.0.5 Oracle 10.2.0.5 Oracle 11.1.0.7 Oracle 11.2.0.2 Oracle 12.1.0.1 7.3.4 9.2.0.8 11.2.0.2 8.0.6 9.2.0.8 11.2.0.2 8.1.7.4 10.2.0.5 9.0.1.4 10.2.0.5 9.2.0.8 11.2.0.2 10.1.0.5 11.2.0.2 10.2.0.5 DIRECT 11.1.0.7 DIRECT 11.2.0.2 DIRECT

Preparation Checklist  In the SOURCE environment: Clean Up  Empty the recycle bin  Check for INVALID objects in SYS/SYSTEM  Check for duplicate objects in SYS/SYSTEM  Remove outdated parameters Components  Check for INVALID components  Check for necessary components  Remove obsolete components Performance  Preserve performance statistics  Check network performance Optional  Perform Integrity checks Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 50

Clean Up: Parameters Real World Crosscheck  Internal Oracle production database – Multimedia (ORDIM) component took very long to upgrade _complex_view_merging = FALSE _multi_join_key_table_lookup = FALSE _library_cache_advice = FALSE _index_join_enabled = FALSEUpgrade time _push_join_union_view = FALSfEor ORDIM: _push_join_predicate = FALS4E9 minutes _always_semi_join = OFF _pred_move_around = FALSE _unnest_subquery = FALSE _predicate_elimination_enabled = FALSE _eliminate_common_subexpr = FALSE _no_or_expansion = FALSE event = '10195 trace name context forever, level 1' event = '10778 trace name context forever, level 1‘ Remove all underscores and events Upgrade time for ORDIM: 7 minutes Factor x7 Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 51

Preparation Check List  In the TARGET environment: Checks Download Settings  Software Certification Information  Installation Requirements  Software and Patch Sets  CPUs, PSUs and Bundle Patches  Single Patches  Parameter Recommendations Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 52

Parameter Recommendations  General guidelines: – The fewer parameters you have in your spfile, the better – Remove outdated parameters, underscores, events ...  Unless directed by applications (e.g. E-Business Suite, Siebel, SAP etc.) to set them – Try to adjust certain parameters based on testing, documentation and white papers  Don't trust statements such as ..."THE tuning guru said ..." Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 53

New Parameters in Oracle Database 12c 54 Copyright © 2013, Oracle and/or its affiliates.All rights reserved.  CELL_OFFLOADGROUP_NAME  CONNECTION_BROKERS  DB_BIG_TABLE_CACHE_PERCENT_TARGET  DB_INDEX_COMPRESSION_INHERITANCE  DNFS_BATCH_SIZE  ENABLE_PLUGGABLE_DATABASE  HEAT_MAP  MAX_STRING_SIZE  NONCDB_COMPATIBLE  OPTIMIZER_ADAPTIVE_FEATURES  OPTIMIZER_ADAPTIVE_REPORTING_ONLY  PARALLEL_DEGREE_LEVEL  PARALLEL_FAULT_TOLERANCE_ENABLED  PDB_FILE_NAME_CONVERT  PGA_AGGREGATE_LIMIT  SPATIAL_VECTOR_ACCELERATION  TEMP_UNDO_ENABLED  THREADED_EXECUTION  UNIFIED_AUDIT_SGA_QUEUE_SIZE  USE_DEDICATED_BROKER

Deprecated Parameters in Oracle Database 12c 55 Copyright © 2013, Oracle and/or its affiliates.All rights reserved.  active_instance_count  background_dump_dest  buffer_pool_keep  buffer_pool_recycle  commit_write  cursor_space_for_time  fast_start_io_target  global_context_pool_size  instance_groups  lock_name_space  log_archive_local_first  log_archive_start  Max_enabled_roles  parallel_automatic_tuning  parallel_io_cap_enabled  parallel_server  parallel_server_instances  plsql_v2_compatibility  remote_os_authent  resource_manager_cpu_allocation  sec_case_sensitive_logon  serial_reuse  sql_trace  standby_archive_dest  user_dump_dest From the Oracle Database 12c Upgrade Guide, Chapter 8.

Fallback Strategies Performance Management Q&A New Features in Oracle 12c Preparing to Upgrade Upgrade & Migration Cases New Scripts Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 56

What's new in upgrades to Oracle 12c?  New SQLAutomation FixUp Scripts – Fixing issues before and after upgrade  Detailed recommendations  Parallel Upgrade – Saving up to 40% upgrade duration – Upgrade driven by catctl.pl, not catupgrd.sql anymore catctl.pl script3.sqlscript2.sqlscript1.sql script4.sql script5.sql script6.sql script6.sql script7.sql script8.sql script9.sql script10.sql script11.sql Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 57

 Upgrade duration to Oracle 12c mainly depends on: – Number of installed components and database options – Number of objects in the database due to high amount of new dictionary tables, and restructuring of some base system tables – CPU Speed Component Oracle Server JServer JAVA Virtual Machine Oracle Workspace Manager Oracle Enterprise Manager Oracle XDK Oracle Text Oracle XML Database Oracle Database Java Packages Oracle Multimedia Oracle Expression Filter Oracle Rule Manager Gathering Statistics HH:MM:SS 00:16:17 00:05:19 00:01:01 00:10:13 00:00:48 00:00:58 00:04:09 00:00:33 00:07:43 00:00:18 00:00:12 00:04:53 Total Upgrade Time: 00:52:31 Component Oracle Server JServer JAVA Virtual Machine Oracle XDK Oracle Text Oracle XML Database Oracle Database Java Packages Gathering Statistics HH:MM:SS 00:16:17 00:05:19 00:00:48 00:00:58 00:04:09 00:00:33 00:02:43 Total Upgrade Time: 00:30:47 Install and patch Oracle 12c Preupgrade Checks Prepare Environment Restore Entire Database Upgrade Database Post Upgrade Actions Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 59

Fallback Strategies Performance Management Q&A New Features in Oracle 12c General Preparation Steps Upgrade & Migration Cases Zero Downtime? Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 60

Case: Near-Zero Downtime  Platform migration with near-zero downtime Oracle 10.2.0.3 HP-UX Itanium 5 mins 10TB Oracle 11.2.0.3 OEL5.8 64bit 10TB Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 61 Example Facts & Description 1. Hardware migration to an Exadata Database Machine 2. Cross Endianness database migration from Oracle 10.2.0.3 to Oracle 11.2.0.3 3. Maximum tolerated downtime: 5 minutes 4. Database size: 10TB 5. High redo generation rate

Introduction  True ZERO Downtime is very hard to achieve – Only Oracle TimesTen In-Memory Database can do that  Replication technologies are easier to handle and setup – A limited downtime will occur to switch clients/application  Active/active scenarios are possible but will require extra work  Concept: 1. Start capturing changes on source 2. Create an upgraded copy on target 3. Start applying changes to target 4. Once in sync switch clients/application  Technologies: – Oracle Golden Gate – (NOTE: Oracle Streams was deprecated as of Oracle Database 12c) Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 62

Oracle Golden Gate Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 63  Paid option of the database – Migratable license for 1 year which includes Active Data Guard  Works with many Oracle database versions – Golden Gate 11.2 supports Oracle 10.2.0.4+, 11.1+, 11.2+  For earlier database versions (8i (DML only), 9i-11.1) use Golden Gate 10.4  Compared to Oracle Streams – Generally faster – Generally easier to setup and use – Fewer data type restrictions  Oracle GoldenGate Installation and Setup Guide  Also works with non-Oracle databases (DB2, Teradata …)  GoldenGate OTN page: http://www.oracle.com/technetwork/middleware/goldengate/overview/index.html

Difference Conventional vs GG Migration  Eliminate the "migration" downtime – Conventional Migration Database Migration / Database Upgrade Tasks Downtime – Near-Zero Downtime Migration Downtime SynchDatabase Migration / Database Upgrade Tasks Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 64

Case: Near-Zero Downtime  Platform migration with near-zero downtime Start Capture Mechanism Build Up Database Copy with Data Pump or TTS StartApply Mechanism Oracle 10.2.0.3 HP-UX Itanium 5 mins 10TB Oracle 11.2.0.3 OEL5.8 64bit 10TB Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 65

Oracle 11.2.0.3 OEL5.8 64bit Capture 10TB Oracle 10.2.0.3 HP-UX Itanium Start Capture Mechanism Build Up Database Copy with Data Pump or TTS Start Apply Mechanism Capture: committed transactions are captured (and can be filtered) as they occur by reading the transaction logs 10TB Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 66

Oracle 10.2.0.3 HP-UX Itanium 10TB Oracle 11.2.0.3 OEL5.8 64bit Start Capture Mechanism Build Up Database Copy with Data Pump or TTS Start Apply Mechanism Capture Trail: stages and queues data for routing Trail 10TB Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 67

Oracle 10.2.0.3 HP-UX Itanium 10T B Oracle 11.2.0.3 OEL5.8 64bit Start Capture Mechanism Build Up Database Copy with Data Pump or TTS Start Apply Mechanism Capture Trail 10TB Build up the target database using: - Transportable Tablespaces x-Platform - Export/Import with Data Pump Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 68

Oracle 10.2.0.3 HP-UX Itanium 10T B Oracle 11.2.0.3 OEL5.8 64bit Start Capture Mechanism Build Up Database Copy with Data Pump or TTS Start Apply Mechanism Capture Trail 10TB Pump: distributes data for routing to target(s) Pump Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 69

Oracle 10.2.0.3 HP-UX Itanium 10T B Oracle 11.2.0.3 OEL5.8 64bit Start Capture Mechanism Build Up Database Copy with Data Pump or TTS Start Apply Mechanism 10TB Golden Gate works bidirectionally - from higher to lower release as well! Capture Trail Pump Trail Delivery Bi-directional Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 70

Fallback Strategies Performance Management Q&A New Features in Oracle 12c General Preparation Steps Upgrade & Migration Cases Real Rolling Upgrade Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 72

Case: Real Rolling Upgrade  Rolling database upgrade with less than 5 min downtime Oracle 11.1.0.7 OEL4 64bit 5 mins 5TB Oracle 11.2.0.3 OEL5.8 64bit 5TB Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 73 Example Facts & Description 1. Neither HW nor OS migration involved 2. Database rolling upgrade from Oracle 11.1.0.7 to Oracle 11.2.0.3 3. Maximum tolerated downtime: 5 minutes 4. Database size: 5TB

Case: Real Rolling Upgrade  Rolling database upgrade with less than 5 min downtime Create a Physical Standby Convert  Logical Standby Upgrade Logical Standby Switchover PROD Logical Flashback Logical Convert  Physical Upgrade via Redo Apply Switchover PROD  Physical Oracle 11.1.0.7 OEL4 64bit Oracle 11.2.0.3 OEL5.8 64bit Oracle 11.1.0.7 PHYSICAL STANDBY 5 mins 5TB 5TB Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 74

Real World Checkpoint  Bielefeld University, Germany – Mid size university in Germany (founded 1969)  18,000 students  1,600 employees  Interdisciplinary  Initiative of Excellence  Lead university for 33 German universities Customer Upgrade Success? Remarks Project Constraints Preparation Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 75

Real World Checkpoint  Project scope: – Upgrade or migrate 10 key systems from Oracle 9.2 to Oracle 11g – Move from Single Instance to:  Oracle Real Application Clusters  Oracle ASM  Oracle Data Guard – Use Transient Logical Standby for patch upgrades to decrease downtime from 60 minutes to less than 5 minutes Customer Upgrade Success? Remarks Project Constraints Preparation Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 76

Real World Checkpoint  Constraints: – Character set conversions – Outdated 3rd party software Customer Constraints Preparation Upgrade Success? Remarks Project Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 77

Real World Checkpoint  Test migrations and upgrades  Setup Oracle Grid Control  Performance monitoring and tuning with SQL Tuning/Access Advisor and SQL Performance Analyzer (SPA) Customer Preparation Upgrade Success? Remarks Project Constraints Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 78

Real World Checkpoint  Upgrade and migrations: – Completed in Sept/Oct 2008  Transient Standby Rolling Upgrade: Oracle. 11.1.0.6 to Oracle 11.1.0.7 – Friday February 13th, 2009 Customer Preparation Upgrade Success? Remarks Project Constraints Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 79

Real World Checkpoint  YES!!!  OOW 2009 presentation Customer Project Constraints Preparation Upgrade Success? Remarks Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 80

Fallback Strategies Performance Management Q&A New Features in Oracle 12c General Preparation Steps Upgrade & Migration Cases Plug in a database Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 81

Case: Upgrade and Plug into a CDB  Consolidate an upgraded 12c database into a CDB Oracle 12.1.0.1 Non-CDB 15 mins 1TB Oracle 12.1.0.1 PDB in a CDB 1TB Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 82 Example Facts & Description 1. Source database must already be upgraded to Oracle Database 12c 2. Plug in the source database as new PDB into an existing CDB 3. Maximum tolerated downtime: 15 minutes 4. Database size: 1TB

Upgrade first, then plug in  To plug in a database into a multitenant environment it has to be upgraded to Oracle Database 12c first – The upgrade steps are independent from the multitenant concept  Once upgraded: 1. Open the database in read- only mode. 2. Generate an XML description file. 3. Plug the database into the destination CDB. Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 83

 Two options: 1. Patch the CDB including all PDBs 2. Patch by plug out, plug in Patching Oracle 12.1.0.1 CDB$ROOT PDB$SEED Oracle 12.1.0.1 PDB1 Oracle 12.1.0.1 CDB$ROOT PDB$SEED Oracle 12.1.0.1 PDB1 Oracle 12.1.0.2 CDB$ROOT PDB$SEED Oracle 12.1.0.2 CDB$ROOT PDB$SEED Oracle 12.1.0.2 PDB1 Patch Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 84 2

New Features in Oracle 12c General Preparation Steps Upgrade & Migration Cases Fallback Strategies Performance Management Q&A Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 85

Fallback Strategy – Strategy Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 86  Never start an upgrade or migration without evaluating and testing your options for going back  Complete RMAN Online Backup is always a must  Clarify: – Fallback requirements in minutes/hours/days – How to deal with issues happening during the upgrade – How to deal with issues hours/days after the upgrade – Will you get additional downtime to change COMPATIBLE?

Parameter COMPATIBLE  Minimum COMPATIBLE in Oracle Database 12c: 11.0.0 – 11.0.0 and 11.1.0 are equivalent – Recommendation:  Change COMPATIBLE 7-10 days after upgrade  Changing COMPATIBLE will require a database restart – SQL> – COMPATIBLE can't be turned back COMPATIBLE must be increased to ≥11.0.0 COMPATIBLE can remain on 11.x.y Downgrade possible alter system set compatible='12.1.0' scope=spfile; Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 87

Fallback Strategy - Issues during upgrade ALWAYS take a complete ONLINE backup with RMAN ≥ 11.0 NoYes Change COMPATIBLE parameter? Set all data tablespaces into READ ONLY mode and take an OFFLINE backup of SYSTEM, SYSAUX, UNDO, TEMP, redologs + controlfiles Create a guaranteed restore point for Flashback Database Restore and recover online backup Flashback to the guaranteed restore point Shutdown, restore the offline backup files and startup in source environment Yes Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 88

Fallback: Restore Point FLASHBACK DATABASE POINT grpt; TO RESTORE SHUTDOWN IMMEDIATE STARTUP MOUNT; ALTER DATABASE OPEN RESETLOGS; DROP RESTORE POINT grpt;  Flashback to a guaranteed restore point – COMPATIBLE can't be changed Pre Upgrade Environment Post Upgrade Environment CREATE RESTORE POINT grpt GUARANTEE FLASHBACK DATABASE; UPGRADE SHUTDOWN IMMEDIATE STARTUP MOUNT; Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 89

Fallback Strategy – Issues after upgrade AGAIN take a complete ONLINE backup with RMAN after the upgrade ≥ 10.2 Change COMPATIBLE parameter? NoYes Use Oracle Golden Gate for a capture/apply of data changes back to the source release Downgrade with the catdwgrd.sql and the catrelod.sql scripts back to the source release Use Data Pump to import data back into an empty database in source env Yes Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 90

New Features in Oracle 12c General Preparation Steps Upgrade & Migration Cases Fallback Strategies Performance Management Q&A Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 91

Configure AWR, ASH & ADDM  Real-Time ADDM – MMON obtains performance stats every 3 sec without lock/latch – MMON triggers a real-timeADDM analysis when: – MMON slave process creates report and stores it in AWR  See the DBA_HIST_REPORTS view Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 92

Real Application Testing  Goal: – Record and replay a real workload to see how a system performs  Platform independent – Find regressions and changing plans before the upgrade – Do consistent performance evaluations upon application changes  Licensable database pack "Real Application Testing"  Available since Oracle Database 11.1.0.6  Available also with patch sets Oracle Database 10.2.0.4/5  Consists of: – Database Replay  DBMS_WORKLOAD_CAPTURE and DBMS_WORKLOAD_REPLAY – SQL Performance Analyzer  DBMS_SPA Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 93

Consolidated Database Replays Validating consolidation strategies • Allows workload captured on different databases to be consolidated for replay • Works for manually consolidated databases or Pluggable Databases SALES HR ERP CRM Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 94

Real World Checkpoint  The Customer: Deutsche Messe AG – HQ in Hannover, Germany – Largest trade fair worldwide – Key trade fairs:  CeBIT  Hannover Messe – ~1,000,000 m² exhibition space Customer Upgrade Success? Remarks Project Constraints Preparation Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 95

Deutsche Messe  Project scope: – Upgrade of a 3-node Oracle 10.2.0.3 RAC Cluster to Oracle 11.1.0.7  Move from raw devices to ASM  Use Snapshot Standby instead of RMAN to refresh development databases  Tune third-party application with Automatic SQL Tuning  Sun Solaris 10 – Initially 2 databases to upgrade (each ~350GB):  CeBIT system  Online ordering web application Customer Upgrade Success? Remarks Project Constraints Preparation Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 96

Deutsche Messe  Constraints: – Keep the old hardware – The "worst" application ever ... Customer Constraints Preparation Upgrade Success? Remarks Project Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 97

Deutsche Messe  Performance: – AWR snapshots – Automatic SQLTuning showed excellent results – Real Application Testing (Database Replay) done during high-load phase during CeBIT 2009 Customer Preparation Upgrade Success? Remarks Project Constraints Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 98

Deutsche Messe  Live? And alive? – Yes! Go-live in September and October 2009  But highest load to be expected in March 2010 (CeBIT!!) – Overall downtime: 4 hrs – Application is faster than ever – Customer kept 3 year old hardware – All SAP systems recently upgraded to 11.2.0.2  More information: OOW 2010 presentation and Success Story – http://apex.oracle.com/pls/apex/f?p=202202:2:::::P2_SUCHWORT:messe – http://www.oracle.com/us/corporate/customers/deutsche-messe-1-db- snapshot-367194.pdf Customer Upgrade Success? Remarks Project Constraints Preparation Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 99

Deutsche Messe  "Heal" a terrible applicationCustomer Upgrade Success? Remarks Project Constraints Preparation Improvement factor: ~250,000x !!! Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 100

CONNECT WITH US COMMUNICATE WITH US PARTNER.IMC@BEEHIVEONLINE.ORACLE.COM ORACLE.COM/SUBSCRIBE Adopt & Implement the latest by Oracle ORACLE.COM/PARTNERS/GOTO/HUB-ECEMEA blogs.oracle.com/imc twitter.com/oracleimc youtube.com/OracleIMCTeam facebook.com/OPN.PartnerHub.Migration.Center Copyright© 2013, Oracle and/orits affiliates. All rights reserved. Upgrade,Migrate and Consolidateto Oracle Database 12c 101

Presentation upgrade, migrate &amp; consolidate to oracle database 12c &amp; 11g r2 – overview

In this document

More Related Content

What's hot

Similar to Presentation upgrade, migrate &amp; consolidate to oracle database 12c &amp; 11g r2 – overview

More from solarisyougood

Recently uploaded

Presentation upgrade, migrate &amp; consolidate to oracle database 12c &amp; 11g r2 – overview

Presentation upgrade, migrate & consolidate to oracle database 12c & 11g r2 – overview

Similar to Presentation upgrade, migrate & consolidate to oracle database 12c & 11g r2 – overview

Presentation upgrade, migrate & consolidate to oracle database 12c & 11g r2 – overview