当前位置: 首页 > 工具软件 > TimesTen > 使用案例 >

TimesTen在Oracle数据库中安装的对象及清理

闾丘卓
2023-12-01

和此文相关的文章为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

先来看最简单的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

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;
 类似资料: