联系：手机/微信(+86 17813235971) QQ(107644445)
标题：Oracle 23ai True Cache搭建和基本测试
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
oracle 23ai推出的True Cache功能,提供一种比较完美的支持Oracle数据库语法,配合Oracle数据库使用的一种cache解决方案,配置也相对比较简单
主库和True Cache库说明
主库IP:192.168.222.8/主机名:xifenfei/db_unique_name:ora23ai/tns:ora23ai/sid:ora23ai
True Cache库IP:192.168.222.18/主机名:xifenfeidg/db_unique_name:ora23ai_tc/tns:ora23aitc/sid:ora23ai
主库参数文件
*.compatible='23.0.0' *.control_files='/u01/app/oracle/oradata/ORA23AI/control01.ctl' *.db_block_size=8192 *.db_name='ora23ai' *.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' *.db_recovery_file_dest_size=14742m *.diagnostic_dest='/u01/app/oracle' *.dispatchers='(PROTOCOL=TCP) (SERVICE=ora23aiXDB)' *.enable_pluggable_database=true *.local_listener='listener_ora23ai' *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=ora23ai' *.nls_language='AMERICAN' *.nls_territory='AMERICA' *.open_cursors=300 *.pga_aggregate_target=764m *.processes=320 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=2292m *.undo_tablespace='UNDOTBS1'
True Cache库参数文件
*.true_cache=true *.db_name=ora23ai *.db_unique_name=ora23ai_tc *.compatible='23.0.0' *.db_block_size=8192 *.diagnostic_dest='/u01/app/oracle' *.enable_pluggable_database=true *.local_listener=listener_ora23aitc *.remote_listener=listener_ora23ai *.sga_target=2292m *._exadata_feature_on=true *.fal_server=ora23ai *.fal_client=ora23aitc *.db_create_file_dest=/u01/app/oracle/oradata/ORA23AI
主库和True Cache库tnsnames.ora配置
ora23ai = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.222.8)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ora23ai) ) ) ora23aitc = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.222.18)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ora23ai_tc) ) ) listener_ora23ai=(DESCRIPTION =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.222.8)(PORT=1521))) listener_ora23aitc=(DESCRIPTION =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.222.18)(PORT=1521)))
拷贝主库密码文件到True Cache库
[oracle@xifenfeidg dbs]$ ls -l orapwora23ai -rw-r-----. 1 oracle oinstall 2048 Aug 2 19:59 orapwora23ai
主库启动归档模式,并开启force logging
[oracle@xifenfei ~]$ sqlplus / as sysdba SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Wed Jul 31 05:24:29 2024 Version 23.5.0.24.07 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to: Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems Version 23.5.0.24.07 SQL> archive log list; Database log mode No Archive Mode Automatic archival Disabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 3 Current log sequence 2 SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 2413360688 bytes Fixed Size 5363248 bytes Variable Size 553648128 bytes Database Buffers 1845493760 bytes Redo Buffers 8855552 bytes Database mounted. SQL> alter database archivelog; Database altered. SQL> alter database force logging; Database altered. SQL> alter database open; Database altered.
True Cache库启动到nomount
[oracle@xifenfeidg ~]$ sqlplus / as sysdba SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Fri Aug 2 19:57:16 2024 Version 23.5.0.24.07 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='/tmp/pfile'; ORACLE instance started. Total System Global Area 2404873776 bytes Fixed Size 5363248 bytes Variable Size 536870912 bytes Database Buffers 1862270976 bytes Redo Buffers 368640 bytes SQL> SELECT file_name FROM v$passwordfile_info; FILE_NAME -------------------------------------------------------------------------------- /u01/app/oracle/product/23ai/db_1/dbs/orapwora23ai
启动True Cache
SQL> CREATE TRUE CACHE;
True Cache created.
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
-------------------------------- ----------------------------------------
TRUE CACHE READ ONLY WITH APPLY
SQL> select name from v$datafile;
no rows selected
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORA23AI/ORA23AI_TC/onlinelog/o1_mf_1_mbslm3p3_.log
/u01/app/oracle/oradata/ORA23AI/ORA23AI_TC/onlinelog/o1_mf_2_mbslm3x2_.log
/u01/app/oracle/oradata/ORA23AI/ORA23AI_TC/onlinelog/o1_mf_3_mbslm4bp_.log
/u01/app/oracle/oradata/ORA23AI/ORA23AI_TC/onlinelog/o1_mf_4_mbslm4tp_.log
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORA23AI/S_TEMP_##TC##_ora23ai#tc_8192_1_3_201_1
/u01/app/oracle/oradata/ORA23AI/S_TEMP_##TC##_ora23ai#tc_8192_1_6_202_1
/u01/app/oracle/oradata/ORA23AI/S_TEMP_##TC##_ora23ai#tc_8192_2_3_203_1
SQL> select count(1) from obj$;
COUNT(1)
----------
70866
启动True Cache时主库alert日志提示
自动增加log_archive_dest_n记录,传输数据到True Cache库
2024-08-02T20:00:37.340496+08:00
ALTER SYSTEM SET log_archive_dest_state_2='DEFER' SCOPE=MEMORY SID='ora23ai';
2024-08-02T20:00:37.341412+08:00
ALTER SYSTEM SET log_archive_dest_2='service=','"ora23aitc"','LGWR ASYNC NET_TIMEOUT=30 VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE)
DB_UNIQUE_NAME="ora23ai_tc" REOPEN=15 MAX_FAILURE=20 ROLE="TRUE_CACHE"' SCOPE=MEMORY SID='ora23ai';
2024-08-02T20:00:37.356979+08:00
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='ora23ai';
2024-08-02T20:00:38.734646+08:00
Thread 1 advanced to log sequence 9 (LGWR switch), current SCN: 4168136
Current log# 3 seq# 9 mem# 0: /u01/app/oracle/oradata/ORA23AI/redo03.log
2024-08-02T20:00:38.801849+08:00
ARC1 (PID:7534): Archived Log entry 6 added for B-1175412482.T-1.S-8 LOS:0x00000000003f839e NXS:0x00000000003f99c8 NAB:21445 ID 0x8fe90542 LAD:1 [krse.c:4872]
2024-08-02T20:00:39.456381+08:00
ALTER SYSTEM SET log_archive_dest_state_2='ENABLE' SCOPE=MEMORY SID='ora23ai';
2024-08-02T20:00:43.431813+08:00
*** 2024-08-02T20:00:43.431713+08:00
[kradcm.c:1217] kradcm_start_dsndr_from_primary: True Cache: RCVR primary data request receiver process (rmi PID:7854)
for True Cache DGID:1938295904 started for DEST_ID:2 by FCH (PID:9392)
2024-08-02T20:00:43.448189+08:00
*** 2024-08-02T20:00:43.448096+08:00
[kradcm.c:2906] kradcm_dsndr_main: True Cache: DSNDR primary data block sender process (TT04 PID:7856) for True Cache DGID:1938295904 started for DEST_ID:2
*** 2024-08-02T20:00:43.450891+08:00
[kradcm.c:406] kradcm_connect_to_adc: True Cache DSNDR (PID:7856): Establishing connection to True Cache DGID:1938295904 CONNECTION:ora23aitc
2024-08-02T20:00:43.457328+08:00
*** 2024-08-02T20:00:43.457236+08:00
[kradcm.c:2906] kradcm_dsndr_main: True Cache: DSNDR primary data block sender process (TT06 PID:7858) for True Cache DGID:1938295904 started for DEST_ID:2
*** 2024-08-02T20:00:43.460061+08:00
[kradcm.c:406] kradcm_connect_to_adc: True Cache DSNDR (PID:7858): Establishing connection to True Cache DGID:1938295904 CONNECTION:ora23aitc
*** 2024-08-02T20:00:43.478444+08:00
[kradcm.c:483] kradcm_connect_to_adc: True Cache DSNDR (PID:7856): Successfully connected to True Cache DGID:1938295904 CONNECTION:ora23aitc
*** 2024-08-02T20:00:43.480139+08:00
[kradcm.c:3025] kradcm_dsndr_main: True Cache: DTS data block receiver process started on True Cache DGID:1938295904 PID:9396
*** 2024-08-02T20:00:43.484350+08:00
[kradcm.c:483] kradcm_connect_to_adc: True Cache DSNDR (PID:7858): Successfully connected to True Cache DGID:1938295904 CONNECTION:ora23aitc
*** 2024-08-02T20:00:43.485593+08:00
[kradcm.c:3025] kradcm_dsndr_main: True Cache: DTS data block receiver process started on True Cache DGID:1938295904 PID:9398
True Cache库alert日志信息
1.自动创建standby redo
2.自动创建tempfile
3.启动库到只读状态（非标准dg的只读）
4.启动日志同步(非标准dg的mrp同步)
2024-08-02T20:00:33.507464+08:00
CREATE TRUE CACHE
--ATTENTION--
Default temporary tablespace will be necessary for a locally managed database in future release.
--ATTENTION--
Default temporary tablespace will be necessary for a locally managed database in future release.
2024-08-02T20:00:35.688251+08:00
Control File SGA cache allocated 8388608 bytes.
Address : 0x6e03afb8
Number of buckets : 256
Number of pools : 8
Number of buffers : 1024
Block size : 8192
Trace flags : 0x0
*** 2024-08-02T20:00:35.702201+08:00
[kcvfdb.c:9694] kcfcmb: True Cache mounted.
Expanded controlfile section 32 from 31 to 128 records
Requested to grow by 97 records; added 5 blocks of records
2024-08-02T20:00:35.703624+08:00
.... (PID:9313): WARNING: STANDBY_FILE_MANAGEMENT initialization parameter is [kcrr.c:2568]
not set to the value "AUTO".
This may cause recovery of the standby database to terminate
prior to applying all available redo data.
It may be necessary to use the ALTER DATABASE CREATE DATAFILE
command to add datafiles created on the primary database.
Lost write protection mode set to "auto"
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 1 SIZE 200M BLOCKSIZE 512
Completed: ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 1 SIZE 200M BLOCKSIZE 512
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 2 SIZE 200M BLOCKSIZE 512
Completed: ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 2 SIZE 200M BLOCKSIZE 512
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 3 SIZE 200M BLOCKSIZE 512
2024-08-02T20:00:36.854038+08:00
Completed: ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 3 SIZE 200M BLOCKSIZE 512
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 SIZE 200M BLOCKSIZE 512
Completed: ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 4 SIZE 200M BLOCKSIZE 512
alter database open
True Cache opening with primary at ora23ai.
Checkpoint for True Cache starts at scn 0x00000000003f99c4 Thread 1 RBA 0x000008.000053c5.0010
2024-08-02T20:00:37.334840+08:00
.... (PID:9313): Enable RFS client [krsr.c:20527]
2024-08-02T20:00:37.335043+08:00
ALTER SYSTEM SET log_archive_config='DG_CONFIG=(ora23ai)' SCOPE=MEMORY;
2024-08-02T20:00:37.335498+08:00
ALTER SYSTEM SET log_archive_dest_1='' SCOPE=MEMORY;
2024-08-02T20:00:37.335878+08:00
ALTER SYSTEM SET log_archive_dest_1='location="/u01/app/oracle/oradata/ORA23AI" mandatory VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' SCOPE=MEMORY;
*** 2024-08-02T20:00:37.342697+08:00
[kcv.c:24912] kcvcrv_adc: New DB SCN 0x00000000003f99c3
.... (PID:9313): Starting Managed Recovery process for Physical Standby [krsm.c:1581]
2024-08-02T20:00:37.381794+08:00
.... (PID:9354): Background Managed Recovery process started [krsm.c:1986]
2024-08-02T20:00:39.457787+08:00
rfs (PID:9369): krsr_rfs_atc: Identified database type as 'TRUE CACHE': Client is Foreground (PID:7528) [krsr.c:5976]
rfs (PID:9369): Disable RFS client RFS LogMiner Client [kcrlc.c:1531]
2024-08-02T20:00:39.463332+08:00
rfs (PID:9373): krsr_rfs_atc: Identified database type as 'TRUE CACHE': Client is ASYNC (PID:7841) [krsr.c:5976]
2024-08-02T20:00:39.467452+08:00
rfs (PID:9373): Opened LNO:1 for DBID:2414386242 B-1175412482.T-1.S-8.C-0 [krsr.c:19076]
2024-08-02T20:00:39.868908+08:00
rfs (PID:9377): krsr_rfs_atc: Identified database type as 'TRUE CACHE': Client is ASYNC (PID:7839) [krsr.c:5976]
2024-08-02T20:00:39.872888+08:00
rfs (PID:9377): Opened LNO:2 for DBID:2414386242 B-1175412482.T-1.S-9.C-0 [krsr.c:19076]
2024-08-02T20:00:42.405225+08:00
Started logmerger process
2024-08-02T20:00:42.417728+08:00
PR00 (PID:9380): Managed Recovery starting Real Time Apply [krsm.c:15931]
2024-08-02T20:00:42.463844+08:00
Parallel Media Recovery started with 4 slaves
2024-08-02T20:00:42.510016+08:00
Recovery of Standby Redo Log: Thread 1 Group 1 Seq 8 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/ORA23AI/ORA23AI_TC/onlinelog/o1_mf_1_mbslm3p3_.log
2024-08-02T20:00:42.562877+08:00
Recovery of Standby Redo Log: Thread 1 Group 2 Seq 9 Reading mem 0
Mem# 0: /u01/app/oracle/oradata/ORA23AI/ORA23AI_TC/onlinelog/o1_mf_2_mbslm3x2_.log
2024-08-02T20:00:43.401465+08:00
*** 2024-08-02T20:00:43.401345+08:00
[kradcm.c:2391] kradcm_fch_main: True Cache: FCH data request sender process (TT02 PID:9392) started
*** 2024-08-02T20:00:43.403321+08:00
[kradcm.c:583] kradcm_fch_connect_primary: FCH (PID:9392): trying to establish connection to primary ora23ai
2024-08-02T20:00:43.414087+08:00
*** 2024-08-02T20:00:43.413868+08:00
[kradcm.c:2376] kradcm_fch_main: FCH process already started. Ignoring request.
[kradcm.c:626] kradcm_fch_connect_primary: FCH (PID:9392) connection established to primary ora23ai
*** 2024-08-02T20:00:43.428869+08:00
[kradcm.c:3279] kradcm_start_dsndr_from_adc: FCH (PID:9392): Message primary DGID:817860583 to start DSNDR for True Cache DGID:1938295904
*** 2024-08-02T20:00:43.432861+08:00
[kradcm.c:3356] kradcm_start_dsndr_from_adc: True Cache: DSNDR process successfully started in primary DGID:817860583 by RCVR PID:7854, initiated by FCH (PID:9392)
2024-08-02T20:00:43.479008+08:00
*** 2024-08-02T20:00:43.478916+08:00
[kradcm.c:1018] kradcm_callback: True Cache: DTS data block receiver process (rmi PID:9396) started for primary DGID:817860583 DSNDR PID:7856
2024-08-02T20:00:43.484358+08:00
*** 2024-08-02T20:00:43.484260+08:00
[kradcm.c:1018] kradcm_callback: True Cache: DTS data block receiver process (rmi PID:9398) started for primary DGID:817860583 DSNDR PID:7858
2024-08-02T20:00:44.152726+08:00
replication_dependency_tracking turned off (no async multimaster replication found)
No Resource Manager plan active
Physical standby database opened for read only access.
Completed: alter database open
Completed: CREATE TRUE CACHE
True Cache 同步测试
--主库创建用户和表
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 XIFENFEI MOUNTED
SQL> alter session set container=xifenfei;
Session altered.
SQL> alter database open;
Database altered.
SQL> create user xff identified by oracle;
User created.
SQL> grant dba to xff;
Grant succeeded.
SQL> create table xff.t_xff as select * from dba_objects;
Table created.
SQL> select count(1) from xff.t_xff;
COUNT(1)
----------
70656
--True Cache库查询结果
SQL> alter session set container=xifenfei;
Session altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 XIFENFEI READ ONLY NO
SQL> select count(1) from xff.t_xff;
COUNT(1)
----------
70656
True Cache DML重定向测试
---True Cache库
SQL> conn xff/oracle@192.168.222.18/xifenfei
Connected.
SQL> select database_role from v$database;
DATABASE_ROLE
--------------------------------
TRUE CACHE
SQL> alter session enable ADG_REDIRECT_DML;
Session altered.
SQL> delete from t_xff;
70656 rows deleted.
SQL> commit;
Commit complete.
SQL> select count(1) from t_xff;
COUNT(1)
----------
0
--主库
SQL> conn xff/oracle@192.168.222.8/xifenfei
Connected.
SQL> select count(1) from t_xff;
COUNT(1)
----------
0
True Cache库操作临时表
[oracle@xifenfeidg ~]$ sqlplus xff/oracle@192.168.222.18/xifenfei
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Fri Aug 2 20:47:43 2024
Version 23.5.0.24.07
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
-------------------------------- ----------------------------------------
TRUE CACHE READ ONLY WITH APPLY
SQL> create global temporary table t_temp as
2 select * from t_xff;
Table created.
SQL> select count(1) from t_temp;
COUNT(1)
----------
0
SQL> insert into t_temp select * from dba_objects;
70663 rows created.
加我QQ
