Upgrade, Migrate & Consolidate to Oracle Database 12c

2. Upgrade,Migrate & Consolidateto Oracle Database 12c Mike Dietrich Database Upgrade and Utilities Oracle Corporation

3. Roy Swonger Senior Director Software Dev.&Product Management Carol Tagliaferri Senior Manager Software Dev. Cindy Lim Principal Member Technical Staff Joseph Errede Principal Member Technical Staff Mike Dietrich Senior Principal Technologist Eric Wittenberg Principal Software Engineer Database Upgrade Development Group

4. Lifetime Support Policy today JAN 2007 JUL 2010 JAN 2009 JAN 2012 JUL 2010 JUL 2013 AUG 2012 AUG 2015 JAN 2015 JAN 2018 JUN 2018 JUN 2021 today Premier Support Waived Extended Support Extended Support Sustaining Support

5. Is It Time To Upgrade?

6. Upgrade, Migrate & Consolidate • Introduction to Multitenant Environments • Roads to Pluggable Databases • Working with Pluggable Databases • Wrap Up

7. Multitenant Environment – The Concept! • Consolidation • Reduce maintenance • Simplified and fast provisioning, cloning and migration • Option is called Oracle Multitenant

8. Caution!! • Many of the well known concepts will get changed • Around 150 pages new documentation in the Administrator’s Guide

9. The earth is still a sphere  • 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

10. Implementation • Multitenant container database => CDB • DBA administers on the CDB level • A CDB contains always a PDB$SEED and in addition zero, one, or many pluggable databases (PDBs) • Applications will connect to the PDBs • Up to 252 PDBs in one CDB PDB$SEED PDB PDB PDB PDB CDB

11. Containers • What's in a CDB? • PDBs • Pluggable databases • Contain data and code • CDB$ROOT • Schemas, objects etc available to all PDBs • Information about all PDBs in the CDB's data dictionary • PDB$SEED • Template from which to create PDBs • PDB, CDB$ROOT and PDB$SEED all are called containers PDB$SEED PDB PDB PDB PDB CDB$ROOT

12. Behind the scenes • One SGA, one set of background processes, and one spfile • Benefit due to resource sharing • Applications connect to a PDB Redo Control Flashback PDB$SEED PDB PDB PDB PDB CDB$ROOT spfile

13. Creation of a CDB • Two options: • DBCA • Highly recommended • Command line CREATE DATABASE • Not recommend as all options will have to be created • CREATE DATABASE cdb12 • [...] • ENABLE PLUGGABLE DATABASE • SEED FILE_NAME_CONVERT=('/oradata/cdb12/','/oradata/pdbseed/') • SYSTEM DATAFILES SIZE 125M AUTOEXTEND ON NEXT 10M MAXSIZE […]

14. Command Line Creation of a CDB • Scripts have to be started via catcon.pl: • catdb.sql will run all scripts for all options • Most useful catcon.pl options: • -u Username and optionally password • -d Directory containing the script to execute (default: current directory) • -e Echo on • -s Spools the output of every script • -l Directory to write logfiles into (default: current directory) • -b Base name for logfiles (mandatory option) $> perlcatcon.pl-u SYS -d $ORACLE_HOME/rdbms/admin -e -s -b catalog catdb.sql

15. CDB-PDB: Who's who? • After CDB creation: • Data dictionary • Common user • By default there's no user data in the root PDB$SEED CDB$ROOT OBJ$ TAB$ SOURCE$

16. CDB-PDB: Who's who? • 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 PDB$SEED CDB$ROOT EMP DEPT PDB TAB$ OBJ$ SOURCE$ OBJ$ TAB$ SOURCE$

17. CDB-PDB: Who's who? • Common user • Exists in the CDB and every current and future PDBs • All Oracle supplied users are common users • User to plug/unplug/clone • CDB1> create user c##adm1 identified by a1; • CDB1> create user c##adm2identified by a2 container=all; • Local user • Exists in one PDB only PDB$SEED HUGO SYS SYS C##ADM C##BOSS C##ADM C##BOSS APP CDB$ROOT PDB

18. CDB-PDB: Who's who? • Tablespaces • Global UNDO and TEMP in CDB$ROOT • PDBs can have their own TEMP • All PDBs will share CDB's UNDO PDB$SEED CDB$ROOT PDB DATA1 SYSTEM SYSTEM DATA2 SYSAUX TEMP_PDB SYSAUX SYSTEM TEMP SYSAUX UNDO

19. Upgrade, Migrate & Consolidate • Introduction to Multitenant Environments • Roads to Pluggable Databases • Creation of a new PDB • Cloning a PDB • Upgrade and plugin as PDB • Full transportable export/import • Working with Pluggable Databases • Wrap Up

20. Creation of a PDB • Fast provisioning with the seed: • Login to the CDB • Provisioning with the seed • Parameter PDB_FILE_NAME_CONVERT is used to create the files in the new location: PDB_FILE_NAME_CONVERT='strg1','strg2' • Import data from the source database if desired • Data Pump import for 10g and later releases (dumpfile or over a NETWORK_LINK) • Original import for 9i and earlier releases • Transportable Tablespaces create pluggable database PDB128 admin user PDB128adm identified by mypasswd roles = (DBA)

21. Overview of Multitenant Environments • Introduction to Multitenant Environments • Roads to Pluggable Databases • Creation of a new PDB • Cloning a PDB • Upgrade and plugin as PDB • Full transportable export/import • Working with Pluggable Databases • Wrap Up

22. Cloning of a PDB • Fast cloning within a CDB or remote cloning • Currently source PDB must be in READ ONLY mode • Locally: • Parameter PDB_FILE_NAME_CONVERT is used to create the files in the new location • Can be set in the spfile (alter system) or in a session (alter session) • Remote: create pluggable database PDB7 from PDB4; create pluggable database PDB8 from PDB3@de.acme.cdb1;

23. Upgrade, Migrate & Consolidate • Introduction to Multitenant Environments • Roads to Pluggable Databases • Creation of a new PDB • Cloning a PDB • Upgrade and plugin as PDB • Full transportable export/import • Working with Pluggable Databases • Wrap Up

24. 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 • Upon upgrade completion it will be set read-only • A description XML filewill be gererated • Then the database canbe plugged in andbecome a pluggabledatabase

25. Upgrade to Oracle Database 12c 7.3.4 9.2.0.8 11.2.0.3 8.0.6 9.2.0.8 11.2.0.3 8.1.7.4 10.2.0.5 9.0.1.4 10.2.0.5 9.2.0.8 11.2.0.3 10.1.0.5 11.2.0.3 10.2.0.5 DIRECT 11.1.0.7 DIRECT 11.2.0.2 DIRECT Please note: This graph will apply to database upgrades only!

26. What's new in upgrades to Oracle 12c? • New SQL Automation 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 script1.sql script2.sql script3.sql script4.sql script7.sql script9.sql script8.sql script10.sql script5.sql script11.sql script6.sql script6.sql

27. New preupgrade script in Oracle 12c • New preupgrade script preupgrd.sql loads the new preupgrade package utluppkg.sql • It will be delivered with the Oracle 12c home to be run in the source database

28. SQL Automation Pre Upgrade • Preupgrade_fixups.sql

29. New Parallel Upgrade in Oracle 12c • New parallel upgrade script • Default feature in Oracle Database • SERVER component upgrade scripts get executed in parallel, where possible • Other non-dependent components are run in parallel • Overall time to upgrade is reducedbetween 20% and 40% • Parallel upgrade is driven by aPERL script: • catctl.pl • Database needs to be inSTARTUP UPGRADE mode catctl.pl script1.sql script2.sql script3.sql script4.sql script7.sql script9.sql script8.sql script10.sql script5.sql script11.sql script6.sql script6.sql

30. New Parallel Upgrade in Oracle 12c • Already used with selected customers for upgrade to Oracle Database 11.2.0.3 • Payback's Exadata V1 to X2 migration • Parallel upgrade went 37% faster • Electronic world market leader • Parallel upgrade used for over 100 SAP systems

31. Parallel Upgrade Script • Start the upgrade on the Shell with catctl.pl in parallel: • catctl.plis the PERL driver • Will execute the upgrade scripts in parallel where possible • Executes catuppst.sql and catbundle.sql as well • -n y determines the parallel degree • Maximum of 8 parallel threads

32. Progress Information • Monitoring while catctl.pl is running:

33. SQL Automation Post Upgrade • Execute postupgrade_fixups.sql:

34. Time Zones • Time zone adjustment • Time zone conversion should be done post upgrade • Required if datatypeTIMESTAMP WITH TIMEZONE is used • Rule: TZDestination ≥ TZSource • Time zone definitions in: $ORACLE_HOME/oracore/zoneinfo

35. Time Zones • Time zone adjustment: • Use the scripts from MOS Note:1509653.1 Database will be restarted!

36. Upgrade and Plugin into a CDB • Step1: • Database Upgrade to Oracle 12c Oracle 10.2.0.5V102 PDB$SEED DatabaseUpgrade CDB$ROOT Oracle 12.1.0.1V102

37. Upgrade and Plugin into a CDB • Step 2: • Database in read-only mode • Create XML file PDB$SEED CDB$ROOT Oracle 12.1.0.1V102 XML

38. Upgrade and Plugin into a CDB • Step 3: • Stop database and plug it into the CDB PDB$SEED TEMPFILE REUSE; CDB$ROOT Oracle 12.1.0.1V102 XML Oracle 12.1.0.1PDB1

39. Upgrade and Plugin into a CDB • Step 4: • Define TNS alias and connect to the PDB via TNS: • sqlplus "sys/oracle@PDB1 as sysdba" • Start PDB: • Sanity with ?/rdbms/admin/noncdb_to_pdb.sql PDB$SEED CDB$ROOT Oracle 12.1.0.1PDB1

40. Upgrade, Migrate & Consolidate • Introduction to Multitenant Environments • Roads to Pluggable Databases • Creation of a new PDB • Cloning a PDB • Upgrade and plugin as PDB • Full Transportable Export/Import • Working with Pluggable Databases • Wrap Up

41. Concept Transportable Tablespaces • Transportable Tablespaces feature exists since Oracle 8i • Can be used cross version • Version to transport to must be always equal or higher • Cross platform Transportable Tablespaces got introduced in Oracle Database 10g • Can be used cross version and cross platform • Required tablespaces to be in read-only mode • Extra work necessary for everything in SYSTEM/SYSAUX

42. Example Transportable Tablespaces Move meta information(views, synonyms, trigger, roles etc) VIEWS VIEWS CODE CODE HUGO HUGO APP APP PRIVS PRIVS DESTINATION Database 11.2.0.3 SOURCE Database 10.2.0.4 USERS SYSTEMSYSAUXUNDOTEMP SYSTEMSYSAUXUNDOTEMP

43. Full Transportable Export/Import • Transport of an entire database • Cross version and cross platform • Export since Oracle 11.2.0.3, import since Oracle 12c • 3 phases: • Datafile migration • Using datafile copies or • RMAN incremental backups • Transport via network import • Sanity work

44. Full Transportable Export/Import VIEWS VIEWS CODE CODE HUGO HUGO APP APP Convert and apply backups PRIVS PRIVS SOURCE Database 11.2.0.3 DESTINATION Database 12.1.0.1 INC BCK0 INC BCK1 USERSSYSTEMSYSAUXUNDOTEMP SYSTEMSYSAUXUNDOTEMP Data Pump

45. Example: 11.2.0.3 12c PDB • Phase 1: Prep work Oracle 11.2.0.3V112 DB Link PDB$SEED RMANLevel 0 RMANLevel 1 RMANLevel 1 CDB$ROOT Oracle 12.1.0.1PDB - Clone

46. Example: 11.2.0.3 12c PDB • Phase 2: Transport – Downtime! TablespaesRead-Only Oracle 11.2.0.3V112 DB Link • impdpoow/passwd@PDB12 NETWORK_LINK=db11 VERSION=12 FULL=YTRANSPORTABLE=ALWAYSMETRICS=Y LOGFILE=oow_dir:src112fullimp.logTRANSPORT_DATAFILE='/fra/d/ts1.dbf' … PDB$SEED RMANLevel 0 RMANLevel 1 RMANLevel 1 RMANLevel 1 CDB$ROOT Backupmust beaccessible Oracle 12.1.0.1PDB12

47. Example: 11.2.0.3 12c PDB • Phase 3: Clean up TablespaesRead-Write Oracle 11.2.0.3V112 DB Link PDB$SEED CDB$ROOT Oracle 12.1.0.1PDB12

48. Upgrade, Migrate & Consolidate • Introduction to Multitenant Environments • Roads to Pluggable Databases • Working with Pluggable Databases • Wrap Up

49. Dictionary Views • New dictionary view family: {USER|ALL|DBA|CDB}_VIEWS • Some important new views:

50. Hirarchies • Common User can access information from the CDB and all PDBs • Dependency shown via column CON_ID in DBA_ views: • In a non-CDB CON_ID is always 0 PDB$SEED CDB$ROOT EMP DEPT PDB TAB$ OBJ$ SOURCE$ OBJ$ TAB$ SOURCE$