Maximum Application Availability with Oracle Database 12c

2. Maximum Application Availability with Oracle Database 12c KuassiMensahDirector Product Management

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

4. My Next Session • Oracle In-Database MapReduce: When Hadoop Meets Exadata • 7/25/13, 12:00– 13:00, Room 431

5. Program Agenda • Problems to Solve • New Concepts • Transaction Guard • Application Continuity • Application Design Considerations

6. Problems to Solve Upon Database Outage Four Problems Confront Applications • Hang • Errors • Outcome of In-Flight Work • Resubmission of In-Flight Work

7. Current Situation • Hang • -> Fast Application Notification (FAN) • Errors Handling? Not formalized • In-Flight Work? Lost! • Resubmission of In-Flight Work? N/A!

8. Program Agenda • Problems to Solve • New Concepts • Transaction Guard • Application Continuity • Application Design Considerations

9. Recoverable Error • Formalized OracleException.IsRecoverable property • Applications No longer need to maintain own list of error codes (e.g., ora-1033, ora-1034, ora-xxx) • JDBC throwsSQLRecoverableException

10. Database RequestUnit of Work Request Begins • PoolDataSourcepds = GetPoolDataSource(); • Connection conn = getConnection(pds); • PreparedStatementpstmt = … • … • SQL, PL/SQL, local calls, RPC • … • conn.commit(); • conn.close(); Request BodyUsually ends with COMMIT Request Ends

11. Program Agenda • Problems to Solve • New Concepts • Transaction Guard • Application Continuity • Application Design Considerations

12. Transaction Guard • Tool that supports known outcome for every transaction • Without Transaction Guard, retry can cause logical corruption • Used by Application Continuity • Can be used independently of Application Continuity Work Request 4. Reliable Commit Outcome 2. DB Calls 4. Enforce Outcome 3. Errors 12c ORACLE Database

13. Transaction Guard – Typical Usage • Upon database instance crash • FAN aborts dead session • Application gets an error • Pool removes orphan connection from pool • If “recoverable error” then • Get last LTXID from dead session • Obtain new database session • Get transaction status

14. Transaction Guard In Action Database Client/Mid-tier Time start transaction assign LTXID COMMIT? Return & Preserve COMMIT OUTCOME getConnection() Txn.Commit(); get Last LTXID <new session> Commit outcome? COMMIT/ROLLBACK SQL, PL/SQL, RPC Results COMMIT Recoverable Error TG in Action Get Logical TX ID

15. On Service COMMIT_OUTCOME Values – TRUE and FALSE Default – FALSE Applies to new sessions RETENTION_TIMEOUT Units – seconds Default – 24 hours (86400) Maximum value – 30 days (2592000) Transaction Guard Configuration

16. Transaction Guard in Oracle database 12c • JDBC-thin, ODP.NET, OCI, OCCI • Commit Models • Local TXN • Auto-commit, Commit on Success • Commit embedded in PL/SQL • DDL, DCL, Parallel DDL • Remote, Distributed • Exclusions • XA • R/W DBLinks from Active Data Guard or Read Only

17. Program Agenda • Problems to Solve • New Concepts • Transaction Guard • Application Continuity • Design Considerations

18. Application Continuity Mask Database Outage When Successful • Replays in-flight work on recoverable errors • Masks many hardware, software, network, storage errors and outages when successful • Improves end user experience Work Request 5. Response 2. DB Calls 4. Replay 3. Errors 12c ORACLE Database

19. Phases in Application Continuity

20. RAC Database Instance1 Instance2 Instance3 Proxy 1 Proxy 2 Connection Replay Context Application Continuity In Action Oracle JDBC Driver Call 1 Call 2 Application

21. DEMO

22. Solution CoverageApplication Continuity for Java • Client • JDBC-Thin driver • UCP, WebLogic Server • Database • SQL, PL/SQL, RPC • Select, ALTER SESSION, DML, DDL, COMMIT/ROLLBACK/SAVEPOINT • Transaction models: Local, Parallel, Remote, Distributed • Mutable function support • Hardware acceleration on current Intel & SPARC chips

23. Exclusions

24. RestrictionsWhen Does Replay Not Occur?

25. Program Agenda • Problems to Solve • New Concepts • Transaction Guard • Application Continuity • Application Design Considerations

26. Application Assessment

27. Side Effects • When Replay is enabled, some calls may be repeated. Apps may need to use disableReplay() API to prevent this: • Autonomous transactions • UTL_HTTP ,UTL_URL • UTL_FILE, UTL_FILE_TRANSFER - files operations • UTL_SMPT, UTL_TCP, UTL_MAIL - sending messages • DBMS_PIPE, RPCs - to external sources • DBMS_ALERT - email or other notifications

28. Configure JDBC Replay Driver Set replay data source in property filedatasource=oracle.jdbc.replay.OracleDataSourceImpl Enable Application Continuity on Service Set FAILOVER_TYPE = TRANSACTION Review settings for: REPLAY_INITIATION_TIMEOUT = 1800 FAILOVER_DELAY = 3 seconds FAILOVER_RETRIES = 60 retries SESSION_STATE_CONSISTENCY = DYNAMIC COMMIT_OUTCOME = TRUE • Configuration

29. GRANT [KEEP DATE TIME | KEEP SYSGUID].. [to  USER] REVOKE [KEEP DATE TIME | KEEP SYSGUID][from USER] GRANT KEEP SEQUENCE.. [to  USER] on [sequence object]; REVOKE KEEP SEQUENCE [from USER] on [sequence object]; If owned, ALTER SEQUENCE.. [sequence object] [KEEP|NOKEEP]; CREATE SEQUENCE.. [sequence object] [KEEP|NOKEEP]; • Mutables and Grant Privileges

30. Maximum Application Availability with Oracle database 12c • Hang • Errors & Connection Handling • Outcome of In-Flight Work • Resubmission of In-Flight Work