Oracle TimesTen and In-Memory Database Cache 11g

(1)

Oracle TimesTen and

In-Memory Database Cache 11g

Student Guide

(2)

This document contains proprietary information and is protected by copyright and other intellectual property laws. You may copy and print this document solely for your own use in an Oracle training course. The document may not be modified or altered in any way. Except where your use constitutes "fair use" under copyright law, you may not use, share, download, upload, copy, print, display, perform, reproduce, publish, license, post, transmit, or distribute this document in whole or in part without the express authorization of Oracle.

The information contained in this document is subject to change without notice. If you find any problems in the document, please report them in writing to: Oracle University, 500 Oracle Parkway, Redwood Shores, California 94065 USA. This document is not warranted to be error-free.

Restricted Rights Notice

If this documentation is delivered to the United States Government or anyone using the documentation on behalf of the United States Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS

The U.S. Government’s rights to use, modify, reproduce, release, perform, display, or disclose these training materials are restricted by the terms of the applicable Oracle license agreement and/or the applicable U.S. Government contract.

Trademark Notice

Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Author Danny Lau Technical Contributors and Reviewers Rohan Aranha David Aspinwall Cathy Baird Nagender Bandi Susan Cheung Debster Desantis Sam Drake Kirk Edson Sourav Ghosh James Healy Douglas Hood Chi-Kim Hoang Charles Hoch Chris Jenkins Simon Law Sheryl Maring Mark McAuliffe John Miller Marie-Anne Neimat William Paulsen Manish Pawar Martin Reames Paul Tuck Maureen Wagener Chih-Ping Wang Brian Wright Editors Raj Kumar Amitha Narayan Richard Wallis Graphic Designer Satish Bettegowda Publishers

(3)

iii

4 Creating and Accessing TimesTen Database Elements Objectives 4-2 Database Elements 4-3 Data Types 4-4 Tables 4-5 SQL Table DDL Statements 4-6 Access Control 4-8 Define Users 4-9 Assign Privileges 4-10 Connection Strings 4-12 User IDs and Passwords 4-13 Indexes 4-14 SQL Index DDL Statements 4-15 Alter a Table 4-16 SQL DML Statements: SELECT 4-18 SQL DML Statements: INSERT 4-19 SQL DML Statements: UPDATE 4-20 SQL DML Statements: DELETE 4-21 ROWID and ROWNUM 4-22

Sequences 4-23 Views 4-24 Synonyms 4-26

Create a Table by Using SQL Developer 4-27 Create an Index by Using SQL Developer 4-28 Create a Sequence by Using SQL Developer 4-29 Create a View by Using SQL Developer 4-30

Assign Object Privileges to a User in SQL Developer 4-31 Remove Object Privileges from a User in SQL Developer 4-32 PL/SQL Support in TimesTen 4-33

PL/SQL Anonymous Blocks 4-34

PL/SQL Stored Procedures and Functions 4-35 PL/SQL Packages 4-36

Reference PL/SQL Package Objects 4-37

Differences Between TimesTen and Oracle PL/SQL 4-38 TimesTen APIs 4-39

OCI Support in TimesTen 4-40

Connect to a TimesTen Database from an OCI Application 4-41 Pro*C/C++ Support in TimesTen 4-42

(5)

v 5 Managing TimesTen Databases

Objectives 5-2 ttBulkCp Utility 5-3

ttBackup and ttRestore Utilities 5-4 ttMigrate Utility 5-5

ttSchema Utility 5-6

TimesTen System Tables and Views 5-7 TimesTen Database Memory Regions 5-8

SYS.MONITOR System Table: Database Memory Region Sizes 5-9 ttSize Utility 5-10

ttStatus Utility: TimesTen Database Connections 5-11 TimesTen Built-In Procedures 5-12

TimesTen Database RAM Policy 5-13 Database RAM Policy Setting 5-14

Change the Size of a TimesTen Database 5-15 Summary 5-16

6 Concurrency Control and Performance Tuning

Objectives 6-2

Start and Complete Transactions 6-3 Implicit Commit 6-4

Transaction Logging 6-5 Transaction Durability 6-6 Transaction Isolation Level 6-7 Read Committed Isolation 6-8 Serializable Isolation 6-9 ttXactAdmin Utility 6-10

Prepare and Execute Parameterized Statements 6-11 TimesTen Query Optimizer 6-13

Query Optimizer Statistics 6-14 Compute Statistics 6-15

Update the Table and Column Statistics by Using SQL Developer 6-16 Use of Indexes in Query Processing 6-17

View an Execution Plan 6-20 Command Cache 6-23

(6)

7 Configuring TimesTen Client/Server Connections

Objectives 7-2

Overview of TimesTen Client/Server 7-3 Client/Server Setup and Configuration 7-4 Server DSNs 7-5

Define a Server DSN on a UNIX System 7-6

Define a Server DSN on a Microsoft Windows System 7-7 Logical Server Names 7-8

Define a Logical Server Name on a UNIX System 7-9

Define a Logical Server Name on a Microsoft Windows System 7-10 Client DSNs 7-11

Define a Client DSN on a UNIX System 7-12

Define a Client DSN on a Microsoft Windows System 7-13

Access a TimesTen Database with a Client/Server Connection 7-15 Other Communication Modes for Local Client/Server Connections 7-16 Configure a Multithreaded TimesTen Server Process 7-17

ttStatus Utility: Server Status and Client/Server Connections 7-19 Summary 7-20

8 Overview of Oracle In-Memory Database Cache

Objectives 8-2

Oracle In-Memory Database Cache 8-3 Cache Oracle Data in TimesTen 8-4 Define the Set of Data to Cache 8-5

Read-Only Cache for Frequently Queried Data 8-6 Example: Multiple Read-Only Caches 8-7

Updatable Cache with Transactional Consistency 8-8 Example: Multiple Updatable Caches 8-9

Dynamic Cache 8-10

Example: Dynamic Caches 8-11 Sliding Window Cache 8-12

In-Memory Database Cache Grid 8-13 Summary 8-14

9 Getting Started with Oracle In-Memory Database Cache

Objectives 9-2

Oracle In-Memory Database Cache: System Requirements 9-3 Create Oracle Users 9-4

Define a DSN for the TimesTen Database 9-6 Create TimesTen Users 9-7

(7)

vii

Set the Cache Administration User Name and Password by Using SQL Developer 9-9

Cache Grid Concepts 9-10 Cache Grid Benefits 9-11 Configure a Cache Grid 9-12

Start the Cache Agent and Set an Agent Start Policy 9-13 Start the Cache Agent by Using SQL Developer 9-14 ttStatus Utility: Cache Agent Status 9-15

Summary 9-16

10 Creating Cache Groups

Objectives 10-2

Cache Group Definition 10-3 Cache Group Concepts 10-4 Read-Only Cache Group 10-5

Create a Local Explicitly Loaded Read-Only Cache Group 10-6 Load and Refresh a Cache Group 10-7

Unload a Cache Group 10-8

Read-Only Cache Group Automatic Refresh Interval 10-9 Pass-Through of Statements from TimesTen to Oracle 10-10 Configure Statement Pass-Through 10-11

Create a Local Dynamic Read-Only Cache Group 10-12 Dynamic Load Operation 10-13

Cache Group Data-Aging Policy 10-14 Benefits of Read-Only Caching 10-15

Benefits of On-Demand and Sliding Window Caching 10-16 Asynchronous Write-Through Cache Group 10-17

Create a Local Dynamic Asynchronous Write-Through Cache Group 10-18 Create a Global Dynamic Asynchronous Write-Through Cache Group 10-19 Create a Global Explicitly Loaded Asynchronous Write-Through Cache

Group 10-20

Attach a TimesTen Database to a Cache Grid 10-21 Operations on Global Cache Groups 10-22

Benefits of Write-Through Caching 10-23 Summary 10-24

11 Other Cache Group Operations and Topics

Objectives 11-2

Create a Multiple-Table Cache Group 11-3 User-Managed Cache Group 11-4

(8)

PROPAGATE and READONLY Cache Table Attributes 11-6 Flush a Cache Group 11-8

Create a Cache Group by Using SQL Developer 11-9

Specify AUTOREFRESH Cache Group Attribute by Using SQL Developer 11-14 Define Cache Group Data Aging Policy by Using SQL Developer 11-15

Load or Refresh a Cache Group by Using SQL Developer 11-16 Unload a Cache Group by Using SQL Developer 11-17

Flush a Cache Group by Using SQL Developer 11-18 Drop a Cache Group by Using SQL Developer 11-19 Summary 11-20

12 Overview of TimesTen to TimesTen Replication

Objectives 12-2

TimesTen to TimesTen Replication 12-3 Active-Standby Pair 12-4

TimesTen Replication Architecture 12-5 Asynchronous Replication 12-6

Synchronous Replication 12-7 Summary 12-8

13 Configuring Replication of TimesTen Databases

Objectives 13-2

Configure a Replicated System 13-3

Active-Standby Pair Replication Scheme 13-4

Create an Active-Standby Pair Replication Scheme 13-5

Create and Configure the Standby and Subscriber Databases 13-6 Asynchronous Replication: Data Flow 13-7

Return Two-Safe Synchronous Replication Scheme 13-8 Return Two-Safe Replication: Data Flow 13-9

Replicate Cache Tables 13-10

Replicate Cache Tables in a Read-Only Cache Group 13-11

Replicate Cache Tables in an Asynchronous Write-Through Cache Group 13-12 Configure Replication on Cache Tables 13-13

Attach an Active-Standby Pair to a Cache Grid 13-14

Start the Replication Agent and Set an Agent Start Policy 13-15 ttStatus Utility: Replication Agent Status 13-16

Replication Agent Port Allocation 13-17 Compress Replicated Updates 13-18

(9)

ix

14 Database Recovery and Monitoring Replication

Objectives 14-2

Replication States 14-3

Change the Replication State 14-4 Transaction Log File Threshold 14-5

Recover a Failed Replicated Database 14-6

Recover a Failed Replicated Database in an Active-Standby Pair 14-7 Automatic Client Connection Failover 14-9

Configure Automatic Client Connection Failover 14-10

Active-Standby Pair Database Recovery with a Read-Only Cache Group 14-11 Active-Standby Pair Database Recovery with an AWT Cache Group 14-12 TimesTen Replication Monitoring: ttRepAdmin Utility Commands 14-13 TimesTen Replication Monitoring: SYS.MONITOR System Table 14-17 TimesTen Replication Monitoring: Built-In Procedures 14-18

Summary 14-19

15 Using Oracle Clusterware with TimesTen to Provide a High-Availability Solution

Objectives 15-2

Oracle Clusterware and TimesTen 15-3

Oracle Clusterware and TimesTen System Requirements 15-4

TimesTen Resource Monitoring Managed by Oracle Clusterware 15-5 Oracle Clusterware Levels of Availability for TimesTen 15-6

Create a Cluster for Basic Level of Availability 15-7 ttCWAdmin Utility 15-8

Configure Oracle Clusterware Resources for an Active-Standby Pair 15-9 Implement Application Failover for Basic Level of Availability 15-10 Simple and Automated High-Availability Solution 15-11

TimesTen Nodes in a Cluster Configured for Advanced Level of Availability 15-12 Create a Cluster for Advanced Level of Availability 15-13

Configure Oracle Clusterware Resources for Advanced Level of Availability 15-14 Failure of the Active Database 15-15

Failure of the Standby Database 15-16

(10)

Oracle TimesTen and In-Memory Database Cache 11g

Oracle TimesTen and

In-Memory Database Cache 11g

Contents