和此文相关的文章为TimesTen 应用层数据库缓存学习:20. TimesTen异常时的缓存清理
建立TimesTen只读缓存组时,会在Oracle数据库中创建用户,建立对象并赋权,本文讲述如何查看这些对象。
另外,如果TimesTen并未正常的删除缓存组,或正常卸载时都需要清理这些对象。
相关脚本位于$TIMESTEN_HOME/install/oraclescripts目录。
$ cd $TIMESTEN_HOME/install/oraclescripts/
$ ls
cacheCleanUp.sql checkRAC.sql initCacheAdminSchema.sql README.TXT
cacheInfo.sql grantCacheAdminPrivileges.sql RAC_README ttCacheADGSiteFailover.sql
$ cat README.TXT
Copyright (c) 1998, 2019, Oracle and/or its affiliates. All rights reserved.
This directory contains the following Oracle SQL scripts. The scripts
are intended to be used with SQL*Plus.
1. grantCacheAdminPrivileges.sql
This script is the first script to be run to set up Oracle In-Memory
Database Cache in a use case where the Oracle DBA grants all the
privileges/roles recommended by TimesTen. We refer to this as automatic
installation case. This script does not create the cache administrator
user and requires the cache administrator user to be already created by
the DBA. Once a cache administrator user is created by the DBA or a
superuser with equivalent privileges, this script is to be run. It grants
all the necessary privileges and roles on Oracle to the cache
administrator user. This script requires the user to be connected to
Oracle as a DBA or a superuser with privileges to CREATE USERS, ROLES and
grant privileges such as CONNECT, RESOURCE, EXECUTE on DBMS_LOCK, CREATE
ANY TRIGGER, CREATE ANY PROCEDURE to other users. This script grants
CONNECT, RESOURCE, EXECUTE on DBMS_LOCK, CREATE ANY TRIGGER, CREATE ANY
PROCEDURE to the cache administrator user.
2. cacheCleanUp.sql
It is designed to run on Oracle using sqlplus to clean all autorefresh
objects related to a TimesTen data store when that data store is no longer
available. The script requires that the user is connected to the Oracle
database as a cache administrator user.
3. cacheInfo.sql
This script assumes that user is connected to the Oracle database as cache
admin user. It prints information for each log table corresponding to a
cached table on each TimesTen data store & host.
4. initCacheAdminSchema.sql (only for MANUAL INSTALLATION case)
This script is the first script to be run to set up Oracle In-Memory
Database Cache in a use case where the Oracle DBA does not want to grant
privileges such as RESOURCE, CREATE ANY TRIGGER, CREATE ANY PROCEDURE to
the cache administrator user. We refer to this as the manual installation
use case (please refer to Oracle In-Memory Database Cache documentation)
as the script manually creates all the required Cache Connect objects on
Oracle under the cache administrator user. This script does not create the
cache admin user and requires the cache administrator user to be already
created by the DBA. Once a cache administrator user is created by the DBA
or a superuser with equivalent privileges, this script is needed to be run
(with cache administrator user as an argument) in the case of manual
installation. It grants all the necessary minimum privileges and creates
the necessary objects on Oracle. This script requires the user to be
connected to Oracle as a DBA or a superuser with privileges to CREATE
USERS, ROLES and grant privileges such as CONNECT, EXECUTE on DBMS_LOCK to
other users.
NOTE: Unless absolutely necessary, we recommend the user to use
grantCacheAdminPrivileges.sql to grant all the required privileges for
cache administrator user.
先来看最简单的cacheInfo.sql。用缓存管理员用户登录,以下是刚刚初始化,但还未建立任何缓存组时的信息:
$ sqlplus cacheadm/oracle@orcl
SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 21 05:24:08 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Mon Dec 21 2020 05:11:37 +00:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> @cacheInfo
***************** Database Information *********************
Database name: ORCL
Unique database name: ORCL
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: 2387228
*************************************************************
*************No autorefresh objects are found*************
*************No DDL Tracking objects are found*************
PL/SQL procedure successfully completed.
建立以下的读缓存组:
CREATE READONLY CACHE GROUP readcache
AUTOREFRESH INTERVAL 5 SECONDS
FROM oratt.readtab
(keyval NUMBER NOT NULL PRIMARY KEY, str VARCHAR2(32));
此时查看到的信息如下:
SQL> @cacheInfo
***************** Database Information *********************
Database name: ORCL
Unique database name: ORCL
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: 2388095
*************************************************************
*************Autorefresh Objects Information ***************
Host name: ttserver
Timesten datastore name: /u01/database/data/ttdb
Cache table name: ORATT.READTAB
Change log table name: tt_06_74365_L
Number of rows in change log table: 1
Maximum logseq on the change log table: 0
Timesten has autorefreshed updates upto logseq: -1
Number of updates waiting to be autorefreshed: 1
Number of updates that has not been marked with a valid logseq: 0
****************************
*************No DDL Tracking objects are found*************
PL/SQL procedure successfully completed.
以上输出中,Autorefresh Objects Information部分是关键。
建立只读缓存组后,在Oracle数据库中会创建一系列对象,包括表和触发器:
$ sqlplus cacheadm/oracle@orcl
SQL> select table_name from user_tables;
TABLE_NAME
--------------------------------------------------------------------------------
TT_06_DB_PARAMS
TT_06_DATABASES
TT_06_DDL_L
TT_06_DDL_TRACKING
TT_06_CACHE_STATS
TT_06_AR_PARAMS
TT_06_DBSPECIFIC_PARAMS
TT_06_USER_COUNT
TT_06_LOG_SPACE_STATS
TT_06_ARDL_CG_COUNTER
TT_06_SYNC_OBJS
TT_06_AGENT_STATUS
TT_06_REPPEERS
TT_06_REPACTIVESTANDBY
TT_06_74365_L
15 rows selected.
SQL> select object_name from user_objects where object_type = 'TRIGGER';
OBJECT_NAME
--------------------------------------------------------------------------------
TT_06_REPACTIVESTANDBY_T
TT_06_74365_T
在Oracle中,可以查询到74365实际就是基础表readtable的对象ID:
SQL> select object_name, object_type from all_objects where object_id = 74365;
OBJECT_NAME
--------------------------------------------------------------------------------
OBJECT_TYPE
-----------------------
READTAB
TABLE
通过SQL Developer我们得到了触发器的定义(Ctrl+F7可以格式化SQL代码):
CREATE OR REPLACE TRIGGER cacheadm.tt_06_74365_t AFTER
DELETE OR INSERT OR UPDATE ON "ORATT"."READTAB"
FOR EACH ROW
DECLARE
max_int CONSTANT INT := 99999999999999999999999999999999999999;
minreportts DATE;
minbookmark NUMBER;
reqdemptyspaces INT := 2;
err_num NUMBER;
dummy INT;
dummyname CHAR(100);
norecovery INT := 0;
BEGIN
<< retry >> BEGIN
IF ( inserting ) THEN
INSERT INTO cacheadm.tt_06_74365_l (
logseq,
ft_cachegroup,
"KEYVAL",
xid
) VALUES (
max_int,
0,
:new."KEYVAL",
dbms_transaction.local_transaction_id
);
ELSE
IF ( updating AND ( ( :old."KEYVAL" <> :new."KEYVAL" ) ) ) THEN
INSERT INTO cacheadm.tt_06_74365_l (
logseq,
ft_cachegroup,
"KEYVAL",
xid
) VALUES (
max_int,
0,
:new."KEYVAL",
dbms_transaction.local_transaction_id
);
END IF;
INSERT INTO cacheadm.tt_06_74365_l (
logseq,
ft_cachegroup,
"KEYVAL",
xid
) VALUES (
max_int,
0,
:old."KEYVAL",
dbms_transaction.local_transaction_id
);
END IF;
EXCEPTION
WHEN OTHERS THEN
err_num := sqlcode;
BEGIN
EXECUTE IMMEDIATE 'select objectname from CACHEADM.TT_06_SYNC_OBJS where objectname = :objectName'
INTO dummyname
USING 'DONOTTRUNCATE';
IF ( SQL%rowcount > 0 ) THEN
RAISE;
END IF;
EXCEPTION
WHEN no_data_found THEN
dummy := 0;
END;
norecovery := 0;
IF ( err_num = -1536 OR err_num = -1653 OR err_num = -1654 OR err_num = -1688 OR err_num = -3233 ) THEN
BEGIN
EXECUTE IMMEDIATE 'select value from CACHEADM.TT_06_ar_params where param = :paramName and value = :paramValue and upper(tblowner) = upper(:owner) and upper(tblname) = upper(:tbl)'
INTO dummyname
USING 'TblSpaceFullRecovery', 'reload', 'ORATT', 'READTAB';
IF ( SQL%notfound OR SQL%rowcount <= 0 ) THEN
norecovery := 1;
END IF;
EXCEPTION
WHEN no_data_found THEN
norecovery := 1;
END;
IF ( norecovery = 1 ) THEN
RAISE;
END IF;
EXECUTE IMMEDIATE 'select min(bookmark) from CACHEADM.TT_06_agent_status where object_id = 74365 and cgType = 0'
INTO minbookmark;
DELETE FROM cacheadm.tt_06_74365_l
WHERE
logseq <= minbookmark;
IF ( SQL%rowcount >= reqdemptyspaces ) THEN
INSERT INTO cacheadm.tt_06_74365_l (
logseq,
ft_cachegroup
) VALUES (
minbookmark,
0
);
GOTO retry;
ELSE
EXECUTE IMMEDIATE 'select min(reportTS) from CACHEADM.TT_06_agent_status where object_id = 74365 and cgType = 0'
INTO minreportts;
EXECUTE IMMEDIATE 'select bookmark from CACHEADM.TT_06_agent_status where object_id = 74365 and reportTS = :minReportTS and cgType = 0'
INTO minbookmark
USING minreportts;
DELETE FROM cacheadm.tt_06_74365_l
WHERE
logseq <= minbookmark;
IF ( SQL%rowcount >= reqdemptyspaces ) THEN
INSERT INTO cacheadm.tt_06_74365_l (
logseq,
ft_cachegroup
) VALUES (
minbookmark,
0
);
GOTO retry;
ELSE
DELETE FROM cacheadm.tt_06_74365_l;
IF ( SQL%rowcount < reqdemptyspaces ) THEN
RAISE;
END IF;
GOTO retry;
END IF;
END IF;
ELSE
RAISE;
END IF;
END;
END;
cacheCleanUp只在TimesTen服务器或Data Store不可用时才需要显式调用。其需要输入的参数可参考CacheInfo:
SQL> @cacheCleanUp
Please enter the hostname
ttserver
The value chosen for the hostname is ttserver
Please enter the datastore
/u01/database/data/ttdb
The value chosen for the datastore is /u01/database/data/ttdb
*****************************OUTPUT**************************************
Performing cleanup for object_id: 74365 which belongs to table : READTAB
Executing: delete from tt_06_agent_status where LOWER(host) = ttserver and
LOWER(datastore) = /u01/database/data/ttdb and object_id = 74365
Executing: drop trigger tt_06_74365_T
Executing: drop table tt_06_74365_L
Executing: delete from tt_06_user_count where object_id = object_id1
Executing: delete from tt_06_databases where LOWER(host) = ttserver and
LOWER(datastore) = /u01/database/data/ttdb
**************************************************************************
PL/SQL procedure successfully completed.
此时Oracle数据库中触发器被删除,还有1个表TT_06_74365_L被删除:
TABLE_NAME
--------------------------------------------------------------------------------
TT_06_DB_PARAMS
TT_06_DATABASES
TT_06_DDL_L
TT_06_DDL_TRACKING
TT_06_CACHE_STATS
TT_06_AR_PARAMS
TT_06_DBSPECIFIC_PARAMS
TT_06_USER_COUNT
TT_06_LOG_SPACE_STATS
TT_06_ARDL_CG_COUNTER
TT_06_SYNC_OBJS
TT_06_AGENT_STATUS
TT_06_REPPEERS
TT_06_REPACTIVESTANDBY
14 rows selected.
如果想彻底消除TimesTen在Oracle中的痕迹,可以将缓存管理用户及相关对象删除;
drop user cacheadm cascade;