标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 kfed MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-01110 ORA-01555 ORA-01578 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (102)
- 数据库 (1,666)
- DB2 (22)
- MySQL (73)
- Oracle (1,528)
- Data Guard (51)
- EXADATA (8)
- GoldenGate (21)
- ORA-xxxxx (159)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (14)
- ORACLE 21C (3)
- Oracle 23ai (7)
- Oracle ASM (65)
- Oracle Bug (8)
- Oracle RAC (52)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (559)
- Oracle安装升级 (90)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (77)
- PostgreSQL (18)
- PostgreSQL恢复 (6)
- SQL Server (27)
- SQL Server恢复 (8)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- 清空redo,导致ORA-27048: skgfifi: file header information is invalid
- A_H_README_TO_RECOVER勒索恢复
- 通过alert日志分析客户自行对一个数据库恢复的来龙去脉和点评
- ORA-12514: TNS: 监听进程不能解析在连接描述符中给出的SERVICE_NAME
- ORA-01092 ORA-00604 ORA-01558故障处理
- ORA-65088: database open should be retried
- Oracle 19c异常恢复—ORA-01209/ORA-65088
- ORA-600 16703故障再现
- 数据库启动报ORA-27102 OSD-00026 O/S-Error: (OS 1455)
- .[metro777@cock.li].Elbie勒索病毒加密数据库恢复
- 应用连接错误,初始化mysql数据库恢复
- RAC默认服务配置优先节点
- Oracle 19c RAC 替换私网操作
- 监听报TNS-12541 TNS-12560 TNS-00511错误
- drop tablespace xxx including contents恢复
- Linux 8 修改网卡名称
- 如何修改集群的公网信息(包括 VIP) (Doc ID 1674442.1)
- 如何在 oracle 集群环境下修改私网信息 (Doc ID 2103317.1)
- ORA-600 [kcvfdb_pdb_set_clean_scn: cleanckpt] 相关bug
- ORA-600 krhpfh_03-1210故障处理
分类目录归档:Oracle 23ai
Oracle 23ai 表和视图的列最多支持到4096个
根据经验,oracle在以前常用版本中(包含oracle 19c),表和视图支持最大的列数量为1000,在oracle 23ai中允许支持最大列数量为4096,具体参见:23ai New Feature – Increased RDBMS Table/View Column Limit to 4096 (Doc ID 2947033.1),这里做了简单的试验,确认如果要支持4096列,需要设置max_columns=’EXTENDED’
准备测试表1000列、4096列和4097列
create table t_xff_col_1000( col1 number, col2 number, col3 number, col4 number, …… col1000 number ); create table t_xff_col_4096( col1 number, col2 number, col3 number, col4 number, …… col4096 number ); create table t_xff_col_4097( col1 number, col2 number, col3 number, col4 number, …… col4097 number ); [oracle@xifenfei ~]$ cat tab_col_4096.sql |grep col|grep -v xff|wc -l 4096 [oracle@xifenfei ~]$ cat tab_col_1000.sql |grep col|grep -v xff|wc -l 1000 [oracle@xifenfei ~]$ cat tab_col_4097.sql |grep col|grep -v xff|wc -l 4097
在max_columns为默认值的情况下(STANDARD)23ai版本中最多也只能支持1000列
[oracle@xifenfei ~]$ sqlplus / as sysdba SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Mon Aug 5 22:01:57 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> show parameter max_co; NAME TYPE ------------------------------------ ---------------------- VALUE ------------------------------ max_columns string STANDARD SQL> @tab_col_1000.sql Table created. SQL> @tab_col_4096.sql col1001 number, * ERROR at line 1002: ORA-01792: maximum number of columns in a table or view is 1000
在max_columns为EXTENDED的情况下能够支持列4096
SQL> alter system set max_columns='EXTENDED'; alter system set max_columns='EXTENDED' * ERROR at line 1: ORA-02096: specified initialization parameter is not modifiable with this option SQL> alter system set max_columns='EXTENDED' scope=spfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> SQL> startup ORACLE instance started. Total System Global Area 2413360688 bytes Fixed Size 5363248 bytes Variable Size 570425344 bytes Database Buffers 1828716544 bytes Redo Buffers 8855552 bytes Database mounted. Database opened. SQL> show parameter max_co; NAME TYPE ------------------------------------ ---------------------- VALUE ------------------------------ max_columns string EXTENDED SQL> @tab_col_4096.sql Table created. SQL> select table_name,count(1) from dba_tab_cols where table_name like 'T_XFF%' GROUP BY TABLE_NAME; TABLE_NAME -------------------------------------------------------------------------------- COUNT(1) ---------- T_XFF_COL_4096 4096 T_XFF_COL_1000 1000 SQL> @tab_col_4097.sql create table t_xff_col_4096( * ERROR at line 1: ORA-01792: maximum number of columns in a table or view is 4096
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.
Oracle23ai新特性—sqlplus errordetails功能
在oracle 23ai中如果sqlplus执行遇到ORA-错误,会有对应的Help: https://docs.oracle.com/error-help/db/ORA-XXXXX提示,由于以前的使用习惯或者是提示本身意义不大等原因,还是希望在sqlplus中关闭这类提示.通过分析确认该提示是由errordetails来控制的
[oracle@xifenfei admin]$ sqlplus / as sysdba SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Mon Jul 29 22:44:23 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 * from xff.xifenfei; select * from xff.xifenfei * ERROR at line 1: ORA-00942: table or view "XFF"."XIFENFEI" does not exist Help: https://docs.oracle.com/error-help/db/ora-00942/ SQL> show errordetails <--该值默认为on errordetails ON
设置errordetails off即可实现关闭Help: https://docs.oracle.com/error-help/db/ORA-XXXXX提示
SQL> set errordetails off SQL> select * from xff.xifenfei; select * from xff.xifenfei * ERROR at line 1: ORA-00942: table or view "XFF"."XIFENFEI" does not exist
设置errordetails verbose可以实现更加详细的提示
SQL> set errordetails verbose SQL> select * from xff.xifenfei; select * from xff.xifenfei * ERROR at line 1: ORA-00942: table or view "XFF"."XIFENFEI" does not exist Help: https://docs.oracle.com/error-help/db/ora-00942/ Cause: The specified table or view did not exist, or a synonym pointed to a table or view that did not exist. To find existing user tables and views, query the ALL_TABLES and ALL_VIEWS data dictionary views. Certain privileges may be required to access the table. If an application returned this message, then the table that the application tried to access did not exist in the database, or the application did not have access to it. Action: Check each of the following: - The spelling of the table or view name is correct. - The referenced table or view name does exist. - The synonym points to an existing table or view. If the table or view does exist, ensure that the correct access privileges are granted to the database user requiring access to the table. Otherwise, create the table. Also, if you are attempting to access a table or view in another schema, make sure that the correct schema is referenced and that access to the object is granted. Params: 1) object_name: The table or view name specified as SCHEMA.OBJECT_NAME, if one is provided. Otherwise, it is blank.
如果要实现sqlplus启动即屏蔽该提示,可以在glogin.sql文件($ORACLE_HOME/sqlplus/admin目录中)设置
[oracle@xifenfei admin]$ sqlplus / as sysdba SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Mon Jul 29 22:44:23 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> show errordetails; errordetails OFF SQL> select * from xifenfei.xff; select * from xifenfei.xff * ERROR at line 1: ORA-00942: table or view "XIFENFEI"."XFF" does not exist