分类目录归档：Oracle

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.