SQL*Plus Scripts for Cache

B SQL*Plus Scripts for Cache

TimesTen is installed with SQL*Plus scripts that are used to perform various cache configuration, administrative and monitoring tasks, and provide links to more information including examples.

All scripts are installed in the timesten_home/install/oraclescripts directory.

Installed SQL*Plus Scripts

There are SQL*Plus scripts that are installed with TimesTen.

cacheCleanUp.sql: This script drops Oracle Database objects such as change log tables and triggers used to implement autorefresh operations for TimesTen Classic. This script is used when a TimesTen Classic database containing cache groups with autorefresh is unavailable because the TimesTen Classic system is offline, or the database was destroyed without dropping its cache groups with autorefresh. Run this script as the cache administration user. Provide the host name of the TimesTen Classic system and the TimesTen database (including its path) as arguments. See Dropping Oracle Database Objects Used by Cache Groups With Autorefresh.

This example uses the cacheCleanUp.sql script for a TimesTen Classic system. The scaleoutCacheCleanup.sql script runs in the same manner for TimesTen Scaleout, except that it requires the grid name and database name as input parameters.
```
% cd timesten_home/install/oraclescripts
% sqlplus cacheadmin/orapwd
SQL> @cacheCleanUp "sys1" "/disk1/databases/database1"

*****************************OUTPUT**************************************
Performing cleanup for object_id: 69959 which belongs to table : CUSTOMER
Executing: delete from tt_07_agent_status where host = sys1 and datastore =
/disk1/databases/database1 and object_id = 69959
Executing: drop table tt_07_69959_L
Executing: drop trigger tt_07_69959_T
Executing: delete from tt_07_user_count where object_id = object_id1
Performing cleanup for object_id: 69966 which belongs to table : ORDERS
Executing: delete from tt_07_agent_status where host = sys1 and datastore =
/disk1/databases/database1 and object_id = 69966
Executing: drop table tt_07_69966_L
Executing: drop trigger tt_07_69966_T
Executing: delete from tt_07_user_count where object_id = object_id1
**************************************************************************
```

cacheInfo.sql: This script returns change log table information for all Oracle Database tables cached in a cache group with autorefresh, and information about Oracle Database objects used to track DDL statements issued on cached Oracle Database tables. This script is used to monitor autorefresh operations on cache groups and DDL statements issued on cached Oracle Database tables. Run this script as the cache administration user. You can alternatively use the ttCacheInfo utility.

The following example runs the cacheInfo.sql SQL*Plus script.

% cd timesten_home/install/oraclescripts
% sqlplus cacheadmin/orapwd
SQL> @cacheInfo.sql
***************** Database Information	*********************
Database name: DATABASE1
Unique database name: database1
Primary database name:
Database Role: PRIMARY
Database Open Mode: READ WRITE
Database Protection Mode: MAXIMUM PERFORMANCE
Database Protection Level: UNPROTECTED
Database Flashback On: NO
Database Current SCN: 21512609
*************************************************************
*************Autorefresh Objects Information  ***************
Grid name: grid1 (7D03C680-BD93-4233-A4CF-B0EDB0064F3F)
Timesten database name: database1
Cache table name: SALES.CUSTOMERS
Change log table name: tt_07_96977_L
Number of rows in change log table: 4
Maximum logseq on the change log table: 1
Timesten has autorefreshed updates upto logseq: 1
Number of updates waiting to be autorefreshed: 0
Number of updates that has not been marked with a valid logseq: 0
*************DDL Tracking Object Information  ***************
Common DDL Log Table Name: TT_07_DDL_L
DDL Trigger Name: TT_07_315_DDL_T
Schema for which DDL Trigger is tracking: SALES
Number of cache groups using the DDL Trigger: 10
****************************
 
PL/SQL procedure successfully completed.

See Monitoring Autorefresh Operations on Cache Groups and Tracking DDL Statements Issued on Cached Oracle Database Tables in this guide and ttCacheInfo in Oracle TimesTen In-Memory Database Reference.

grantCacheAdminPrivileges.sql: This script grants privileges to the cache administration user that are required to automatically create Oracle Database objects used to manage the caching of Oracle Database data when particular cache group operations are performed. This includes the TT_CACHE_ADMIN_ROLE role that defines privileges on Oracle Database tables. Run this script as the sys user. See Create Oracle Database Objects Used to Manage Data Caching.

The following example grants the required SQL privileges to the cacheadmin user for cache operations in the Oracle database.

@grantCacheAdminPrivileges.sql cacheadmin

Please enter the administrator user id
The value chosen for administrator user id is cacheadmin

***************** Creation of TT_CACHE_ADMIN_ROLE starts ******************
0. Creating TT_CACHE_ADMIN_ROLE role
** Creation of TT_CACHE_ADMIN_ROLE done successfully **
***************** Initialization for cache admin begins ******************
0. Granting the CREATE SESSION privilege to CACHEADMIN
1. Granting the TT_CACHE_ADMIN_ROLE to CACHEADMIN
2. Granting the DBMS_LOCK package privilege to CACHEADMIN
3. Granting the DBMS_DDL package privilege to CACHEADMIN
4. Granting the DBMS_FLASHBACK package privilege to CACHEADMIN
5. Granting the CREATE SEQUENCE privilege to CACHEADMIN
6. Granting the CREATE CLUSTER privilege to CACHEADMIN
7. Granting the CREATE OPERATOR privilege to CACHEADMIN
8. Granting the CREATE INDEXTYPE privilege to CACHEADMIN
9. Granting the CREATE TABLE privilege to CACHEADMIN
10. Granting the CREATE PROCEDURE  privilege to CACHEADMIN
11. Granting the CREATE ANY TRIGGER  privilege to CACHEADMIN
12. Granting the GRANT UNLIMITED TABLESPACE privilege to CACHEADMIN
13. Granting the DBMS_LOB package privilege to CACHEADMIN
14. Granting the SELECT on SYS.ALL_OBJECTS privilege to CACHEADMIN
15. Granting the SELECT on SYS.ALL_SYNONYMS privilege to CACHEADMIN
16. Checking if the cache administrator user has permissions on the default
tablespace
     Permission exists
18. Granting the CREATE TYPE privilege to CACHEADMIN
19. Granting the SELECT on SYS.GV$LOCK privilege to CACHEADMIN
20. Granting the SELECT on SYS.GV$SESSION privilege  to CACHEADMIN
21. Granting the SELECT on SYS.DBA_DATA_FILES privilege  to CACHEADMIN
22. Granting the SELECT on SYS.USER_USERS privilege  to CACHEADMIN
23. Granting the SELECT on SYS.USER_FREE_SPACE privilege  to CACHEADMIN
24. Granting the SELECT on SYS.USER_TS_QUOTAS privilege  to CACHEADMIN
25. Granting the SELECT on SYS.USER_SYS_PRIVS privilege  to CACHEADMIN
26. Granting the SELECT on SYS.V$DATABASE privilege  to CACHEADMIN (optional)
27. Granting the SELECT on SYS.GV$PROCESS privilege  to CACHEADMIN (optional)
28. Granting the SELECT ANY TRANSACTION privilege to CACHEADMIN
29. Creating the TTCACHEADM.TT_07_ARDL_CG_COUNTER table
30. Granting SELECT privilege on TTCACHEADM.TT_07_ARDL_CG_COUNTER table to
PUBLIC
********* Initialization for cache admin user done successfully *********

checkAdminPrivileges.sql: This script checks that the cache administration user has all of the necessary privileges (those that are provided when you run the grantCacheAdminPrivileges.sql script) that are required for cache operations. Run this script as the user that you want checked. If privileges are missing, you can either have the sys user grant the missing privileges or run the grantCacheAdminPrivileges.sql script for this user. See The checkAdminPrivileges.sql Script.

Use SQL*Plus on the Oracle Database system from an operating system shell or command prompt, and connect to the Oracle database instance as the user (in most cases, the cache administration user) that you want checked for privileges. The following example shows that the user has all of the required privileges.
```
SQL> @checkAdminPrivileges.sql 
**** Checking privileges for cache administrator user ****
**** User has all privileges for a cache administrator user ****
```
The following example shows the output if you have missing privileges needed on an Oracle database:
```
SQL> @checkAdminPrivileges.sql
**** Checking privileges for cache administrator user **** 
Missing CREATE OPERATOR
Missing CREATE INDEXTYPE
Missing CREATE CLUSTER
Missing EXECUTE ON SYS.DBMS_LOCK
Missing EXECUTE ON SYS.DBMS_DDL
Missing EXECUTE ON SYS.DBMS_FLASHBACK
Missing EXECUTE ON SYS.DBMS_LOB
Missing SELECT on SYS.GV$LOCK
Missing SELECT on SYS.GV$SESSION
Missing SELECT on SYS.DBA_DATA_FILES
Missing SELECT on SYS.V$DATABASE
Missing SELECT on GV$PROCESS
Missing UNLIMITED TABLESPACE
Missing SELECT ANY TRANSACTION
Missing table ARDL_CG_COUNTER
**** User missing privileges. Missing privilege count: 15 **** 
```
initCacheAdminSchema.sql: This script grants a minimal set of privileges to the cache administration user and manually creates Oracle Database objects used to manage the caching of Oracle Database data. This includes the TT_CACHE_ADMIN_ROLE role that defines privileges on Oracle Database tables. Run this script as the sys user. See The initCacheAdminSchema.sql Script.

In the following example, the Oracle database cache administration user name is cacheadmin.
```
@initCacheAdminSchema cacheadmin
```
scaleoutCacheCleanUp.sql: This script drops Oracle Database objects such as change log tables and triggers used to implement autorefresh operations for TimesTen Scaleout. This script is used when a TimesTen Scaleout database containing cache groups with autorefresh is unavailable because the TimesTen Scaleout system is offline, or the database was destroyed without dropping its cache groups with autorefresh. Run this script as the cache administration user. Provide the grid name and the TimesTen database name as arguments.

The scaleoutCacheCleanup.sql script runs in the same manner for TimesTen Scaleout as the cacheCleanUp.sql script does for TimesTen Classic.

See Dropping Oracle Database Objects Used by Cache Groups With Autorefresh.