How an Oracle Database 12 c Upgrade Works in a Multitenant Environment

2. How an Oracle Database 12c Upgrade Works in a Multitenant Environment

3. Introduction & Overview Overview on Oracle Multitenant Plug Into Oracle Multitenant Working with Oracle Multitenant Upgrade, Migrate & Consolidate to Oracle Database 12c

5. Introduction & Overview Overview on Oracle Multitenant Plug Into Oracle Multitenant Working with Oracle Multitenant Upgrade, Migrate & Consolidate to Oracle Database 12c

6. Look Before You Leap! Some well-know concepts will change • Look at new documentation in the Administrator’s Guide around 150 pages . • You don’t have to use Oracle Multitenant. • Oracle Database 12c non-Multitenant works as expected.

7. Database Upgrade Blog • http://blogs.oracle.com/UPGRADE

8. Database Upgrade: OTN Web Site • http://otn.oracle.com/goto/upgrade

9. Oracle Multitenant – Implementation • Multitenant container database • Administration from CDB • A CDB can contain one ormany pluggable databases • Data and code resides in the PDBs • Applications connect to PDBs • Simple and fast provisioning,cloning, plugin, patching andupgrade PDB1 PDB2 PDB3 PDB1 PDB$SEED PDB$SEED CDB2 CDB1

10. Oracle Multitenant – Behind the scenes • One SGA • One set of background processes • One SPFILE PDB1 PDB2 PDB3 Redo Control Flashback spfile CDB PDB$SEED

11. 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 […]

12. Command Line Creation of a CDB • Administrative 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 create_dictionarycatdb.sql

13. CDB-PDB: Who's who? • After plugging a PDB into a CDB: • Data resides in the PDB • References will get created in the CDB • Some dictionary objects get inherited from the CDB PDB$SEED CDB$ROOT EMP DEPT PDB TAB$ OBJ$ SOURCE$ OBJ$ TAB$ SOURCE$

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

15. CDB-PDB: Who's who? • Common user • Exists in the CDB and all current and future PDBs • All Oracle-supplied users are common users • CDB1> create user c##adm identified by topsecret; • Local user • Exists ina PDB only PDB$SEED C##ADM HUGO C##BOSS APP SYS C##ADM C##BOSS SYS CDB$ROOT PDB

16. Introduction & Overview Overview on Oracle Multitenant Plug Into Oracle Multitenant Working with Oracle Multitenant Upgrade, Migrate & Consolidate to Oracle Database 12c

17. Upgrade SQL Automation • New Pre-Upgrade Script • preupgrd.sql • Executes pre-upgrade checks • Runs in source environment • Generates fixup scripts • preupgrade_fixups.sql • postupgrade_fixups.sql • MOS Note:884522.1

18. Faster Upgrade – Less Downtime catctl.pl script1.sql script2.sql script3.sql • catctl.pl • Runs database upgrade in parallel • Up to 35% faster upgrade • Used and proven by selected Oracle Database 11g global customers • Telco billing • >100 SAP systems • Large DWH • New Parallel Upgrade script4.sql script7.sql script9.sql script8.sql script10.sql script5.sql script11.sql script6.sql script6.sql

19. Faster Upgrade – Less Downtime $> $ORACLE_HOME/perl/bin/perl catctl.pl -n 8 catupgrd.sql • New Parallel Upgrade

20. Simplified Upgrade • Database Upgrade Assistant • Pre-Upgrade Automation • Parallel Upgrade • RMAN Integration • Guaranteed Restore Points • Activity and Alert Log

21. Enterprise Manager Mass and RAC Upgrades • EM Cloud Control • Mass Upgrades • Grid Infrastructure Upgrades • RAC Database Upgrades • Standby Database Upgrades

22. Creation of a New Pluggable Database • Fast provisioning from PDB$SEED • PDB_FILE_NAME_CONVERT create pluggable database PDB1 admin user adm1 identified by pwd; impdp • Import data with impdp • Dump file or NETWORK_LINK • imp for ≤ Oracle 9i PDB1 PDB$SEED CDB

23. Cloning of a Pluggable Database • Fast cloning of a PDB • Local: • Remote: • Part of first patch set for Oracle Database 12c (12.1.0.2) • Works since PSU3 for Oracle 12.1.0.1 create pluggable database PDB2 from PDB1; PDB1 PDB2 PDB1 create pluggable database PDB1 from PDB1@CDB1; PDB$SEED PDB$SEED CDB2 CDB1

24. Upgrade and Plugin as PDB • Database upgrade • Start database read-only • Create XML description file • Shutdown database • Plugin database • Sanity operations PDB1xml exec DBMS_PDB.DESCRIBE('PDB1.xml'); PDB1 PDB$SEED CDB create pluggable database PDB1using ('PDB1.xml') nocopytempfile reuse; DB1 Read Only start ?/rdbms/admin/noncdb_to_pdb.sql

25. Full Transportable Export/Import impdp Database Link • Create a fresh database/PDB • Create database link to source • Tablespaces read-only – downtime! • Copy datafiles to destination • Run impdp on NETWORK_LINK PDB1 PDB$SEED • impdpoow/passwd@PDB1 NETWORK_LINK=DB1 VERSION=12 FULL=Y TRANSPORTABLE=ALWAYS METRICS=Y LOGFILE=oow_dir:src112fullimp.logTRANSPORT_DATAFILE='/oradata/ts1.dbf' … Read Only CDB DB1

26. Introduction & Overview Overview on Oracle Multitenant Plug Into Oracle Multitenant Working with Oracle Multitenant Upgrade, Migrate & Consolidate to Oracle Database 12c

27. Working with Oracle Multitenant Upgrade all or some Oracle Golden Gate Upgrade Rolling Upgrade Real Applications Clusters (RAC) Upgrade 1 2 3 4 Upgrade, Migrate & Consolidate to Oracle Database 12c

28. Upgrade: Everything at once • Upgrade everything at once • Run preupgrd.sql • Start CDB in new $OH in UPGRADE mode • Run catctl.pl -n How many PDB‘s are upgraded together Def. cpu_count/2 Max 32 -N How many Sql process threads per PDB Def 2 Max 8 • Recompile catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql PDB1 PDB3 PDB2 PDB1 PDB2 PDB3 PDB4 PDB4 PDB$SEED PDB$SEED UPGRADE UPGRADE UPGRADE UPGRADE UPGRADE CDB1 – Oracle 12.1.0.2 CDB1 – Oracle 12.1.0.1 UPGRADE

29. Upgrade: Everything at once • Advantage: • Simple to deploy • Disadvantage: • Hard to find common downtime • More downtime for individual PDBs • Order of upgrades • Defaults to con_id order • Inclusion lists –c or exclusion lists -C • Override the upgrade order • May have to write script to invoke catctl.pl multiple times to control the order PDB1 PDB3 PDB2 PDB1 PDB2 PDB3 PDB4 PDB4 PDB$SEED PDB$SEED UPGRADE UPGRADE UPGRADE UPGRADE UPGRADE CDB1 – Oracle 12.1.0.2 CDB1 – Oracle 12.1.0.1 UPGRADE

30. Upgrade: One at a time or more • In CDB1: • SQL> @preupgrd.sql • SQL> alter pluggable database pdb1 close; • SQL> alter pluggable database pdb1 unplug into '/stage/pdb1.xml'; • Don't forget to backup the XML file!!! • In CDB2: • SQL> create pluggable database pdb1 using '/stage/pdb1.xml'; • SQL> alter pluggable database pdb1open upgrade; • $ORACLE_HOME/perl/bin/perl catctl.pl-c 'PDB1' catupgrd.sql • $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' -c 'PDB1' utlrp.sql PDB1xml PDB1 PDB1 PDB$SEED PDB$SEED UPGRADE CDB2 – Oracle 12.1.0.2 CDB1 – Oracle 12.1.0.1

31. Upgrade: One at a time or more • Advantage: • Easier to schedule for individual PDBs • Less downtime • More control • Disadvantage: • More manual tasks • Need for a 2nd CDB • This will require more memory PDB1xml PDB1 PDB1 PDB$SEED PDB$SEED CDB2 – Oracle 12.1.0.2 CDB1 – Oracle 12.1.0.1

32. Best Practices • Character sets • PDB character set must match CDB's character set • Right now different character sets in CDB/PDB are not allowed • Some will be converted during plugin (must be binary subset of CDB character set) • DMU 2.0 can convert character sets before or after plug-in • http://www.oracle.com/technetwork/database/database-technologies/globalization/dmu/overview/index.html • See Oracle 12c Globalization Guide, Chap.2:Choosing a Database Character Set for a Multitenant Container Databasehttp://docs.oracle.com/cd/E16655_01/server.121/e17750/ch2charset.htm#NLSPG1035 PDB1 PDB$SEED DB1 CDB

33. Best Practices • Adjust the default maintenance windows per PDB • Weekday: 10pm to 2am (4 hours) • Weekend: 6am to 2am (20 hours)

34. Best Practices • Resource Manager is a must • Set resource consumption for: • CPU • Session • Parallel servers • Policy definition for shares and limits • Example with CPU_COUNT=24 on CDB level:

35. Best Practices • Have your redo logfiles on VERY fast disks • Otherwise the LGWR will become the bottleneck

36. Parameter • Which parameters can be set within a PDB? • Where can I define parameters specific to a PDB • SPFILE: parameters for the CDB and all PDBs • V$SYSTEM_PARAMETER: SELECT name FROM v$parameter WHERE ispdb_modifiable='TRUE'; PDB1 PDB1 PDB$SEED PDB$SEED CDB2 CDB1 SELECT name, value FROM v$system_parameter WHERE con_id=n;

37. Backup & Recovery • Backup and recovery with RMAN • Entire CDB with all PDBs • Just the CDB$ROOT only • PDBs: BACKUP DATABASE PLUS ARCHIVELOG; RESTORE DATABASE;RECOVER DATABASE; BACKUP DATABASE ROOT; RESTORE DATABASE ROOT;RECOVER DATABASE ROOT; BACKUP PLUGGABLE DATABASE sales, hr;RESTORE PLUGGABLE DATABASE 'pdb$seed', sales, hr; RECOVER PLUGGABLE DATABASE 'pdb$seed', sales, hr;;

38. Diagnosing Issues • Where the fun part starts … alert.log and traces ? Happened in which PDB?

39. Fallback Strategy – Strategy • 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? Upgrade, Migrate & Consolidate to Oracle Database 12c

40. Parameter COMPATIBLE • Minimum COMPATIBLE in Oracle Database 12c: 11.0.0 • 11.0.0 and 11.1.0 are equivalent • Recommendation: • Change it 7-10 days after upgrade – but restart required • SQL> • COMPATIBLE can't be turned back alter system set compatible='12.1.0' scope=spfile; COMPATIBLE must be increased to ≥11.0.0 COMPATIBLE can remain on 11.x.y Downgrade possible Upgrade, Migrate & Consolidate to Oracle Database 12c

41. Fallback Strategy - Issuesduringupgrade ALWAYS take a complete ONLINE backup with RMAN ≥ 11.0 FullONLINEBackup PartialOFFLINEBackup GuaranteedRestore Point Change COMPATIBLE parameter? Yes Yes No Full restore andrecovery Partial Restore Flashback to GRP Upgrade, Migrate & Consolidate to Oracle Database 12c

42. Fallback: Online Backup • Restore a backup • Complete online backup (RMAN) • Please verify: • Where is your backup located? Tapes, HD, off site... • Does the restore work? • How long will the restore take? • How long will the recovery take? • Recommendation: • Have a valid online backup in any case – and test it!!! Upgrade, Migrate & Consolidate to Oracle Database 12c

43. Fallback: Offline Backup • Restore a partial offlinebackup • Put all data tablespaces in read-only mode • That's downtime! • Shutdown the database IMMEDIATE • Copy SYSTEM, UNDO, TOOLS, SYSAUX, XDB, DRSYS and ODM data files plus control files and redo logs • In case of failure: • Shutdown and copy all partial backup files back • Startup in the old environment and recreate TEMP • Advantages: • Fast and simple, even COMPATIBLE can be changed Upgrade, Migrate & Consolidate to Oracle Database 12c

44. Fallback: Restore Point • Flashback to a guaranteed restore point • COMPATIBLE cannot be changed UPGRADE Upgrade, Migrate & Consolidate to Oracle Database 12c

45. Fallback Strategy – Issues after upgrade AGAIN take a complete ONLINE backup with RMAN after the upgrade ≥ 11.1 Change COMPATIBLE parameter? Yes Yes No Data PumpRe-Import OracleGolden Gate Downgrade Upgrade, Migrate & Consolidate to Oracle Database 12c

46. Fallback: Data Pump • Downgrade with expdp/impdp to 10.x • MOS Note:553337.1 • Prepare an empty database for the import “just in case” • Then: • Run expdp from the 12.1 database home with the VERSION parameter equal to the target database COMPATIBLE setting • Import using impdp from the target database home • NETWORK_LINK can be used for downgrades as well Upgrade, Migrate & Consolidate to Oracle Database 12c

47. 1 2 Fallback: Golden Gate • Downgrade with Oracle Golden Gate • Version/platform independent Source Upgradeddestinationdatabase “Downgrade” Upgrade, Migrate & Consolidate to Oracle Database 12c

48. Fallback: Downgrade • Downgrade with catdwgrd.sql • MOS Note:883335.1andMOS Note:443890.1 • Downgrade possible to: • Oracle 11.1.0.7 • Oracle 11.2.0.x • Do not change COMPATIBLE Upgrade, Migrate & Consolidate to Oracle Database 12c

49. Fallback: Downgrade Downgrade with catdwgrd.sql Reload with catrelod.sql SQL> startup downgrade pfile=pfile_name SQL> alter pluggable database all open downgrade; $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b catdwgrd -d '''.''' -r catdwgrd.sql SQL> startup database mount; SQL> alter database open upgrade; SQL> alter pluggable database all open upgrade; $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b catrelod -d '''.''' catrelod.sql $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql Upgrade, Migrate & Consolidate to Oracle Database 12c

50. Working with Oracle Multitenant Upgrade all or some Oracle Golden Gate Upgrade Rolling Upgrade Real Applications Clusters (RAC) Upgrade 1 2 3 4 Upgrade, Migrate & Consolidate to Oracle Database 12c