Oracle Data Guard Zero-Downtime Database Maintenance

1. The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle. Release timing for Oracle Database 12c is planned for Calendar Year 2013.

2. Oracle Data Guard Zero-Downtime Database Maintenance Sally Piao Vice President Database and Exadata System Test

3. Agenda • A look at Rolling Upgrades today • A customer’s viewpoint • Simple Database Upgrades for the future • In Closing

4. synchronize PROD synchronize SWITCHOVER PROD PROD Upgrade A via redo streamand synchronize Physical STBY PROD synchronize Database Rolling Upgrades My Oracle Support Note 949322.1 Database A Database B Install new Oracle version in separate homes on A & B, set guaranteed restore point (GRP) on A Physical STBY Temporarily convert B to use SQL Apply, upgrade and sync Logical STBY Switchover, flashback A to GRP, mount in new/upgraded home Physical STBY release n release n+1

5. Agenda • A look at Rolling Upgrades today • A customer’s viewpoint • Simple Database Upgrades for the future • In Closing

6. Just How Many Steps Is That? • Disable the Broker configuration • Stop the Broker at the Primary • Stop the Broker at the Standbys • Configure the Redo transport parameters on the target standby • Configure the Redo transport parameters on bystander standbys • Set the LOG_FILE_NAME_CONVERT parameter to clear logfiles • Create a guaranteed restore point on the primary database • Create a guaranteed restore point on the standby database • Cancel Managed Recovery • Execute DBMS_LOGSTDBY.BUILD at primary • Convert the standby to a Transient Logical standby using KEEP IDENTITY • Open the Transient Logical standby • Start SQL Apply • Turn off automatic archive log delete • Synchronize

7. But Wait! There’s More! • Stop SQL Apply • Create a 2nd guaranteed restore point • Stop Redo Transport • Upgrade the Transient Logical standby • Enable Redo Transport • Restart SQL Apply • Synchronize • Start Switchover at Primary • Complete Switchover at standby • Open new Primary • Stop redo transport • Flashback the original Primary • Mount original Primary in new home • Convert it to a physical standby • Shutdown • Mount new Physical Standby • Start Redo Apply • Enable redo transport • Synchronize • Start switchover on new primary

8. And finally • Complete switchover at standby • Open new Primary • Restart old Primary as a standby • Start Managed Recovery • Drop 3 guaranteed restore points • Enable the Broker Configuration

9. Agenda • A look at Rolling Upgrades today • A customer’s viewpoint • Simple Database Upgrades for the future • In Closing

10. DBMS_ROLLING: Concepts • Rolling changes can be applied on the whole Data Guard configuration • Three stages (Specification – Compilation – Execution) • Execution has three stages: Start, Switchover, Finish • Two key groups • Leading Group • Changes are applied first (before Switchover) • Has a Master Database (the future primary database) • Trailing Group • Changes are applied last (after Switchover) • Has a Master Database (the original primary)

11. DBMS_ROLLING: Concepts • Leading Group • Need to identify the Master Database (LGM) during Specification • LGM starts as a Physical Standby, converted into a Logical Standby (START), and then becomes the primary database (SWITCHOVER) • Other databases in the Leading Group protect the LGM • LGM responsibility transferrable on failure • Trailing Group • Contains the original primary database (Trailing Group Master) • Other databases in the Trailing Group protect the TGM • TGM responsibility transferrable on failure

12. Data Guard Simple Rolling Upgrades Simple, Reliable, Repeatable Database Rolling Upgrades Start Switchover Finish

13. Goal Simple, Reliable, Repeatable • Early problem detection • Dedicated interface: DBMS_ROLLING PL/SQL package • Centralized, simplified, and uniform execution • Fault tolerance • Configuration rollback • Centralized monitoring: DBA_ROLLING views

14. Upgrading The Database Software • The Data Guard Simple Rolling Upgrades procedure will be usable for database version upgrades starting with the first patchset of Oracle Database12c. • To upgrade from previous Oracle Database11g releases to Oracle Database12c and to move from the first release of Oracle Database 12c to the first patchset you will need to use the classic Transient Logical Standby upgrade procedure. • The procedure can be used for other database maintenance tasks with the first release

15. What Kind Of Changes Can Be Made? • Add Partitioning to Non-Partitioned Tables • Change Basicfile LOBs to Securefile LOBs • Change XML-CLOB to Binary XML • Alter table to be OLTP-compressed • First Release Examples

16. New Data Type Support Eliminating Barriers to Utilizing Database Rolling Upgrade • Advanced Data Type Support • XML OR • Binary XML • SecurefileDeduplication • Objects/Collections (includes SDO_GEOMETRY, DICOM) • New Support for Oracle-Supplied Features • DBFS • AQ • Oracle Text • XDB

17. Process – Planning & Setup Phase • Generate an upgrade plan • Call DBMS_ROLLING.INIT_PLAN • Generates an upgrade plan with a configuration specific set of instructions to guide the administrator through the upgrade process • Call DBMS_ROLLING.SET_PARAMETER • Modify parameters of the rolling upgrade • Prepare your changes to the database

18. Process - Execution Phase • Call DBMS_ROLLING.START_PLAN • Configures primary and standby databases participating in the upgrade • Make changes to the standby database • Call DBMS_ROLLING.SWITCHOVER • Swaps roles between current primary and new primary with the changes, switchover is only downtime required • Call DBMS_ROLLING.FINISH_PLAN • Completes upgrade of the old primary and bystanders and resynchronizes with the new primary

19. A Sample Walkthrough - Planning Phase Current Configuration • Primary database in Boston • Physical standby database in Cambridge (SYNC) • 2nd Physical standby database in Chicago (ASYNC) • Broker Controlled DGMGRL> SHOW CONFIGURATION Configuration – OOW2012 Protection Mode: MaxAvailability Databases: bos - Primary database cam - Physical standby database chi - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL>

20. A Sample Walkthrough - Planning Phase Start the Planning Process • Connect to the Primary • Execute the INIT_PLAN procedure • Sets up default parameters • 2 for each database • 25 general parameters SQL> exec dbms_rolling.init_plan(‘cam’); PL/SQL procedure successfully completed. SQL> select name, curval from 2 dba_rolling_parameters where scope is null; NAME CURVAL ------------------------ ------------------------ ACTIVE_SESSIONS_TIMEOUT 3600 ACTIVE_SESSIONS_WAIT 0 BACKUP_CTLFILE rolling_change_backup.f DICTIONARY_LOAD_TIMEOUT 3600 DICTIONARY_LOAD_WAIT 0 EVENT_RECORDS 10000 FAILOVER 0 GRP_PREFIX DBMSRU_ … UPDATED_TGS_WAIT 1 25 rows selected.

21. A Sample Walkthrough - Planning Phase Reconfigure Redo Transport • Create Redo Transport parameters at target database • In our example, on CAM to point to BOS and CHI • Required to complete the process after the switchover as the Broker is disabled. SQL> alter system set log_archive_dest_5='service="BOS", SYNC reopen=30 db_unique_name="BOS" valid_for=(PRIMARY_ROLE, ONLINE_LOGFILE)'; System altered. SQL> alter system set log_archive_dest_6='service="CHI", ASYNC reopen=30 db_unique_name="CHI" valid_for=(PRIMARY_ROLE, ONLINE_LOGFILE)'; System altered. SQL>

22. A Sample Walkthrough - Planning Phase Examining Parameters of the Plan • Example “INVOLVEMENT” • Each database protects the Primary when in the Standby role • BOS Primary • CAM and CHI protect BOS • CAM Primary • BOS and CHI protect CAM • CHI Primary • BOS and CAM protect CHI SQL> select scope, name, curval 2 from dba_rolling_parameters 3 where scope is not null order by scope; SCO NAME CURVAL --- --------------- --------------- bos INVOLVEMENT FULL bos PROTECTS NONE cam INVOLVEMENT FULL cam PROTECTS PRIMARY chi INVOLVEMENT FULL chi PROTECTS PRIMARY 6 rows selected. SQL>

23. A Sample Walkthrough - Planning Phase Build the Plan when ready • Validates plan parameters • Creates an upgrade plan • Successful build required in order to perform a rolling upgrade • Parameter changes require that the procedure is called again • Complete plan can be reviewed in DBA_ROLLING_PLAN SQL> select revision, status, phase 2 from dba_rolling_status; REVISION STATUS PHASE ---------- ------------ -------------- 0 READY BUILD PENDING SQL> exec dbms_rolling.build_plan(); PL/SQL procedure successfully completed. SQL> select revision, status, phase 2 from dba_rolling_status; REVISION STATUS PHASE ---------- ------------ -------------- 1 READY START PENDING

24. A Sample Walkthrough - Start Phase Start the Process • Must be executed on the primary database • Formally starts the rolling upgrade operation. • When complete, the configuration possesses a fully configured transient logical standby database. • Still no outage SQL> exec dbms_rolling.start_plan(); PL/SQL procedure successfully completed. SQL> select dbun, role, engine_status, 2 update_progress from dba_rolling_databases; DBUN ROLE ENGINE_STATUS UPDATE_PROG ---------- --------- -------------- ----------- bos PRIMARY NOT APPLICABLE NOT STARTED cam LOGICAL RUNNING NOT STARTED chi PHYSICAL RUNNING NOT STARTED SQL> select revision, status, phase 2 from dba_rolling_status; REVISION STATUS PHASE ---------- ------------ -------------- 1 READY SWITCH PENDING

25. A Sample Walkthrough - Start Phase Perform the changes • Make your changes to the database • For example, move a table from LOBs to Securefile LOBs • Stop SQL Apply • Make the changes to the Transient Logical Standby • Restart SQL Apply

26. A Sample Walkthrough - Switchover Phase Switchover! • Prior to the switchover you can move read clients to the transient logical standby to avoid an outage • Update users will be stalled and eventually must reconnect to the new primary using standard client failover best practices. • Outage should be no more than 90-120 seconds for the database SQL> exec dbms_rolling.switchover(); PL/SQL procedure successfully completed. SQL> select dbun, role, engine_status, 2 update_progress from dba_rolling_databases; DBUN ROLE ENGINE_STATUS UPDATE_PROG ---------- --------- -------------- ----------- bos LOGICAL STOPPED NOT STARTED cam PRIMARY NOT APPLICABLE NOT STARTED chi PHYSICAL STOPPED NOT STARTED SQL> select revision, status, phase 2 from dba_rolling_status; REVISION STATUS PHASE ---------- ------------ -------------- 1 READY FINISH PENDING

27. A Sample Walkthrough - Finish Phase Finish the change • Manually restart and mount the all other databses • Execute the FINISH Procedure to finalize the rolling upgrade operation. • Configures the former primary as a physical standby, and configures remaining physical standbys to recover the change redo from the new primary. SQL> exec dbms_rolling.finish_plan(); PL/SQL procedure successfully completed. SQL> select dbun, role, engine_status, 2 update_progress from dba_rolling_databases; DBUN ROLE ENGINE_STATUS UPDATE_PROG ---------- --------- -------------- ----------- bos PHYSICAL RUNNING FINISHED cam PRIMARY NOT APPLICABLE FINISHED chi PHYSICAL RUNNING FINISHED SQL> select revision, status, phase 2 from dba_rolling_status; REVISION STATUS PHASE ---------- ------------ -------------- 1 READY DONE

28. A Sample Walkthrough - Finish Phase Re-enable the Broker and switchover • Enable the Broker • It reconfigures itself to the new Primary database ‘CAM’. • Execute a Broker switchover • If required to return to the original configuration. DGMGRL> enable configuration; Enabled. DGMGRL> show configuration Configuration – OOW2012 Protection Mode: MaxAvailability Databases: cam - Primary database bos - Physical standby database chi - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS DGMGRL> switchover to bos; Performing switchover NOW, please wait... … Switchover succeeded, new primary is "bos"

29. Agenda • A look at Rolling Upgrades today • A customer’s viewpoint • Simple Database Upgrades for the future • In Closing

30. DBMS_ROLLING: Key Features • Specify – Compile – Execute Protocol • Catch configuration errors at Compilation step • Run-time errors are detected during Execution • State is kept in the database • Provides robustness • Run-time steps are constant • Regardless of how many databases are involved • Handles failure at the original primary database • Allows Data Protection for the upgraded primary right from the start

31. Synchronize – redo apply PROD Synchronize – sql apply SWITCHOVER PROD Flashback A to GRP, upgrade via redo stream Physical STBY PROD Synchronize – redo apply Behind the Scenes Database A Database B Sets guaranteed restore point (GRP) on A and B PhysicalSTBY LogicalSTBY Converts B to transient logical Switchover PhysicalSTBY PROD Current Structure New Structure

32. Now How Many Steps? • Prepare for maintenance • Disable the Broker, INIT_PLAN, BUILD_PLAN, Configure Redo transport • START_PLAN • Perform Maintenance • SWITCHOVER • Mount original Primary • FINISH_PLAN • Enable the Broker, Switchover

33. Resources • OTN HA Portal:http://www.oracle.com/goto/availability • Maximum Availability Architecture (MAA): http://www.oracle.com/goto/maa • MAA Blogs: http://blogs.oracle.com/maa • Exadata on OTN:http://www.oracle.com/technetwork/database/exadata/index.html • Oracle HA Customer Success Stories on OTN:http://www.oracle.com/technetwork/database/features/ha-casestudies-098033.html

34. Graphic Section Divider

36. The preceding is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, and timing of any features or functionality described for Oracle's products remains at the sole discretion of Oracle. Release timing for Oracle Database 12c is planned for Calendar Year 2013.