最近接手了几套新的 ORACLE 数据库，发现其归档空间经常满，通过 crontab -l 初步一看是有定时删除归档脚本的，检查磁盘空间df -h 发现，挂载的 NFS 磁盘空间满了，归档是存放在 ASM 的，心想这不影响删除归档呢，这NFS 是用来存放备份的。然后检查 rman 脚本发现NFS确实是存放 rman 备份，但是感觉这跟删除归档失败应该没关系。之后就检查删除归档的脚本日志，发现日志报 Linux-x86_64 Error: 28: No space left on device 好家伙磁盘空间不足。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

input archived log thread=1 sequence=218876 RECID=332434 STAMP=1150349788
channel c2: starting piece 1 at 2023-10-18 08:01:24
RMAN-03009: failure of backup command on c1 channel at 10/18/2023 08:07:39
ORA-19502: write error on file "/backup1/db10cent_bak/archbak/ORCLDB.60855.1.1150531279.ARC", block number 1484801 (block size=512)
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 1484801
Additional information: -1
channel c1 disabled, job failed on it will be run on another channel
released channel: c1
released channel: c2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on c2 channel at 10/18/2023 08:07:39
ORA-19502: write error on file "/backup1/db10cent_bak/archbak/ORCLDB.60856.1.1150531282.ARC", block number 1607681 (block size=512)
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 1607681
Additional information: -1

检查删除归档的脚本如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

……
echo "=================================================================================" >>${1}/backup_arch.log
echo "Begin backup at : `date`" >>${1}/backup_arch.log
rman target /  <<EOF >> ${1}/backup_arch.log
run{
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
ALLOCATE CHANNEL c1 TYPE DISK;
ALLOCATE CHANNEL c2 TYPE DISK;
#sql "alter system archive log current";
backup as COMPRESSED backupset archivelog all not backed up format '${1}/ORCL_a.%d.%s.%p.%t.ARC';
backup current controlfile format '${1}/%d.%s.%p.%T.CTL';
crosscheck archivelog all;
delete force noprompt archivelog all completed before 'sysdate - 5';
delete noprompt expired backup;
delete noprompt obsolete;
RELEASE CHANNEL c1;
RELEASE CHANNEL c2;
}
exit
EOF
echo "End backup at : `date`" >>${1}/backup_arch.log
echo "=================================================================================" >>${1}/backup_arch.log
exit 0
[oracle@zdb001 archbak]$ 

确实存在 delete force noprompt archivelog all completed before 'sysdate - 5'; ，但是前面是先备份归档，然后再删除5天的归档，好吧，那备份的磁盘空间满了，肯定就导致备份失败，然后后面的删除就失败了。这不是本次的重点，重点是在清理备份文件的时候，发现备份文件名上根本没有时间戳，而list backup 超过一定时间之后的归档在控制文件不记录的。只能通过操作系统的命令进行删除，发现备份目录竟然都存在22年的备份文件，要删除这些备份就需要谨慎了，文件名没有时间戳，通过find 删除无法进行二次确认删除的是否准确，这是不能忍的的，查看了脚本的format，好家伙，虽然写了一堆的format ，但是感觉一个管用的都没有，什么%d%s%p%t，啥用都没有，都不如一个 %F%T 管用。所以这里整理了下 rman format的格式。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

9427    9427    1   1   AVAILABLE   DISK        ……/ora_a_ORCL_1147533053_198911l326btnt
9428    9428    1   1   AVAILABLE   DISK        ……/ora_a_ORCL_1147533088_198921l426btp0
9429    9429    1   1   AVAILABLE   DISK        ……/ora_a_ORCL_1147533134_198931l526btqe
9444    9444    1   1   AVAILABLE   DISK        ……/ora_a_ORCL_1147620267_199081lk26eitb
9445    9445    1   1   AVAILABLE   DISK        ……/ora_a_ORCL_1147620302_199091ll26eiue
9446    9446    1   1   AVAILABLE   DISK        ……/ora_a_ORCL_1147620338_199101lm26eivi
9452    9452    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1147870164_199181lu26m6uk
9461    9461    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1147879463_199271m726mg17
9466    9466    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1148040010_199341me26rcqa
9487    9487    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1148570495_199611n927birv
9490    9490    1   1   AVAILABLE   DISK        ……/ora_a_ORCL_1148571225_199641nc27bjip
9491    9491    1   1   AVAILABLE   DISK        ……/ora_a_ORCL_1148571370_199651nd27bjna
9492    9492    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1148644810_199671nf27drea
9493    9493    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1148647126_199681ng27dtmm
9494    9494    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1148649391_199691nh27dvtf
9495    9495    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1148651767_199701ni27e27n
9496    9496    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1148653773_199711nj27e46d
9497    9497    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1148655428_199721nk27e5q4
9498    9498    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1148656174_199731nl27e6he
9499    9499    1   1   AVAILABLE   DISK        ……/ora_o_ORCL_1148656839_199741nm27e767

废话不说，上官方连接：https://docs.oracle.com/en/database/oracle/oracle-database/19/rcmrf/formatSpec.html#GUID-E51F637A-57E0-4B06-803F-3F879DF5BEED

*formatSpec*

The following table lists RMAN substitution variables that are valid in format strings.

使用FORMAT参数进行替换变量（注意大小写）：

%a：Oracle数据库的activation ID即RESETLOG_ID
%c：备份片段的复制数（从1开始编号，最大不超过256）
%d：Oracle数据库名称
%T：当前时间的年月日格式（YYYYMMDD）
%Y：当前时间中的年，格式为YYYY
%M：当前时间中的月，格式为MM
%D：当前时间中的日，格式为DD
%e：归档序号
%f：绝对文件编号
%F：基于”DBID+时间”确定的唯一名称，格式的形式为c--YYYYMMDD-QQ，YYYYMMDD为日期，QQ是一个1～256的序列
%h：归档日志线程号
%I：Oracle数据库的DBID。
%N：表空间名称。
%n：数据库名称，并且会在右侧用x字符进行填充，使其保持长度为8
%p：备份集中备份片段的编号，从1开始
%s：备份集号
%t：备份集时间戳
%u：是一个由备份集编号和建立时间压缩后组成的8字符名称。利用%u可以为每个备份集生成一个唯一的名称
%U：默认是%u_%p_%c的简写形式，利用它可以为每一个备份片段（即磁盘文件）生成一个唯一名称，默认格式

%U对应各种默认格式生成备份片段时，%U=%u_%p_%c
生成数据文件镜像复制时，%U=data-D-%d_id-%I_TS-%N_FNO-%f_%u
生成归档文件镜像复制时，%U=arch-D_%d-id-%I_S-%e_T-%h_A-%a_%u
生成控制文件镜像复制时，%U=cf-D_%d-id-%I_%u

其实就使用一个 %F即可，或者使用一个 $U，就可以确保唯一，还带时间戳。

以上作为记录备查。

最近看到不少文章在 MySQL 8.0 中 通过 alert user、create user 之后每次都执行一次 flush privileges; 语句，实际上这是没必要的，来看下官方文档：

https://dev.mysql.com/doc/refman/8.0/en/privilege-changes.html

6.2.13 When Privilege Changes Take Effect

If the mysqld server is started without the --skip-grant-tables option, it reads all grant table contents into memory during its startup sequence. The in-memory tables become effective for access control at that point.

If you modify the grant tables indirectly using an account-management statement, the server notices these changes and loads the grant tables into memory again immediately. Account-management statements are described in ==Section 13.7.1, “Account Management Statements”. Examples include GRANT, REVOKE, SET PASSWORD, and RENAME USER.==

If you modify the grant tables directly using statements such as INSERT, UPDATE, or DELETE (which is not recommended), the changes have no effect on privilege checking until you either tell the server to reload the tables or restart it. Thus, if you change the grant tables directly but forget to reload them, the changes have no effect until you restart the server. This may leave you wondering why your changes seem to make no difference!

To tell the server to reload the grant tables, perform a flush-privileges operation. This can be done by issuing a FLUSH PRIVILEGES statement or by executing a mysqladmin flush-privileges or mysqladmin reload command.

A grant table reload affects privileges for each existing client session as follows:

• Table and column privilege changes take effect with the client’s next request.

• Database privilege changes take effect the next time the client executes a USE *db_name* statement.

Note

Client applications may cache the database name; thus, this effect may not be visible to them without actually changing to a different database.

• Static global privileges and passwords are unaffected for a connected client. These changes take effect only in sessions for subsequent connections. Changes to dynamic global privileges apply immediately. For information about the differences between static and dynamic privileges, see Static Versus Dynamic Privileges.)

Changes to the set of active roles within a session take effect immediately, for that session only. The SET ROLE statement performs session role activation and deactivation (see Section 13.7.1.11, “SET ROLE Statement”).

If the server is started with the --skip-grant-tables option, it does not read the grant tables or implement any access control. Any user can connect and perform any operation, which is insecure. To cause a server thus started to read the tables and enable access checking, flush the privileges.

Account Management Statements 如下：

13.7.1 Account Management Statements

• 13.7.1.1 ALTER USER Statement
• 13.7.1.2 CREATE ROLE Statement
• 13.7.1.3 CREATE USER Statement
• 13.7.1.4 DROP ROLE Statement
• 13.7.1.5 DROP USER Statement
• 13.7.1.6 GRANT Statement
• 13.7.1.7 RENAME USER Statement
• 13.7.1.8 REVOKE Statement
• 13.7.1.9 SET DEFAULT ROLE Statement
• 13.7.1.10 SET PASSWORD Statement
• 13.7.1.11 SET ROLE Statement

也就是说通过以上的 ALERT USER、CREATE USER、SET PASSWORD 等 11 个用户管理的命令执行后会自动加载到内存生效，而不需要进行手动执行 FLUSH PRIVILEGES 。

那什么时候需要执行 FLUSH PRIVILEGES ,就是在进行手工直接使用INSERT、UPDATE或DELETE（不建议）等语句修改授权表，这些是无法直接生效的，如果要让这些修改生效就需要手工执行 FLUSH PRIVILEGES ，告诉服务器重新加载表或重新启动表。因此，如果您直接更改授权表，但忘记重新加载它们，则在您重新启动服务器之前，更改不会生效。

[toc]

今天看到一篇文章，是测试 MySQL 8.0 修改密码并验证登录的文章。

其中有以下验证

可以看到上面修改的是 root@'localhost' 用户的密码，而下面验证登录的是通过-h127.0.0.1 的方式登录。这很显然是有问题的，是不严谨的，为什么呢，因为在 MySQL 中 localhost 和 127.0.0.1 是有一点区别的。

下面说下我对 MySQL 中的localhost和 127.0.0.1 区别的理解。

[toc]

等待事件

1
2
3
4
5
6
7
8
9
10
11

set lines 200
col event for a55 
SELECT  inst_id,EVENT, SUM(DECODE(WAIT_TIME, 0, 0, 1)) "Prev", SUM(DECODE(WAIT_TIME, 0, 1, 0)) "Curr", COUNT(*) "Tot" , sum(SECONDS_IN_WAIT) SECONDS_IN_WAIT
FROM GV$SESSION_WAIT
WHERE event NOT
IN ('smon timer','pmon timer','rdbms ipc message','SQL*Net message from client','gcs remote message')
    AND event NOT LIKE '%idle%'
    AND event NOT LIKE '%Idle%'
    AND event NOT LIKE '%Streams AQ%'
GROUP BY inst_id,EVENT
ORDER BY 1,5 desc;

查询会话相关

根据等待事件查会话

1
2
3
4
5
6
7
8
9

set line 199 
col username format a14 
col event format a35 
col module format a20 
col spid format a8 
col machine format a15
col B_SESS for a10 

SELECT /*+rule */ sid, s.serial#, spid, event, sql_id, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine,  BLOCKING_INSTANCE||'.'||blocking_session b_sess FROM v$session s, v$process p WHERE event='&event_name' AND s.paddr = p.addr order by 6;

根据用户查会话

1
2
3
4
5
6
7
8
9

set line 199 
col username format a14 
col event format a35 
col module format a20 
col spid format a8 
col machine format a15
col B_SESS for a10 

SELECT /*+rule */ sid, s.serial#, spid, event, sql_id, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine,  BLOCKING_INSTANCE||'.'||blocking_session b_sess FROM v$session s, v$process p WHERE s.username='&user_name' AND s.paddr = p.addr order by 6;

根据SQL_ID查会话

1
2
3
4
5
6
7
8
9

set line 199 
col username format a14 
col event format a35 
col module format a20 
col spid format a8 
col machine format a15
col B_SESS for a10 

SELECT /*+rule */ sid, s.serial#, spid, event, sql_id, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine,  BLOCKING_INSTANCE||'.'||blocking_session b_sess FROM v$session s, v$process p WHERE s.sql_id='&sql_id' AND s.paddr = p.addr order by 6;

根据会话ID查会话详情

1
2
3
4
5
6
7
8
9

set line 199 
col username format a14 
col event format a35 
col module format a20 
col spid format a8 
col machine format a15
col B_SESS for a10 

SELECT s.sid, s.serial#, spid, event, sql_id, PREV_SQL_ID, seconds_in_wait ws, row_wait_obj# obj, s.username, s.machine, module,blocking_session b_sess,logon_time  FROM v$session s, v$process p WHERE sid = '&sid' AND s.paddr = p.addr;

查询阻塞会话

1
2
3
4
5
6
7
8
9

set line 199 
col username format a14 
col event format a35 
col module format a20 
col spid format a8 
col machine format a15
col B_SESS for a10 

select count(*),blocking_session from v$session where blocking_session is not null group by blocking_session;

查询会话的对象信息

1
2
3
4
5
6
7
8
9
10

set line 199 
col username format a14 
col event format a35 
col module format a20 
col spid format a8 
col machine format a15
col B_SESS for a10 
col OBJECT_NAME for a30

select owner,object_name,subobject_name,object_type from dba_objects where object_id=&oid;

[toc]

Oracle Database 23c 已经 GA 了，Oracle Database 23c: The Next Long Term Support Release

We are pleased to announce that the new version of the world’s most powerful database, Oracle Database 23c, is now Generally Available on OCI Oracle Base Database Service.

安装部署

之前新版本发布之后，初步体验就是安装部署，捣鼓一堆，现在简单了，安装是最简单的了。

1、rpm 安装

2、docker 安装

3、官方提供的 Oracle VM VirtualBox ova 直接导入

rpm 和 ova 暂且不提，本次体验用 docker 安装三部曲。

[toc]

概念描述

ORACLE 11g 之后的自动统计信息的阈值默认是10%，也就是表变化超过10%才会进行搜集。如果分区表的某个分区的数据变化超过了10%，但是没有超过整个表的10%，默认情况下会不会进行自动的统计信息搜集呢。

测试验证

1. 完了方便测试，这里修改当前的 NLS_DATE_FORMAT 显示时分秒。

1
2
3

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

Session altered.

2. 创建分区表

   阅读全文 »

[toc]

概念描述

Oracle收集失效的统计信息的策略：自上次自动统计信息收集作业完成之后，如果DBA_TAB_MODIFICATIONS中记录的INSERT+UPDATE+DELETE所影响的行记录之和超过了DBA_TABLES中目标表记录数的10%，或者是自上次统计信息收集完成之后目标表执行过truncate操作，那么 Oracle 会认为目标表的统计信息已经失效，自动统计信息收集作业就会对目标表重新收集统计信息。

但是对于一些超大表，可能数据变化超过10%需要很长时间，这就导致统计信息很长时间搜集一次，可能导致统计信息不是最新，从而产生执行计划不准的情况。

可以通过EXEC dbms_stats.set_table_prefs(ownname => 'MES',tabname => 'METABLEXXX',pname => 'STALE_PERCENT',pvalue => 5); 来修改这个10%的阈值为5%

[toc]

适用范围

ORA-00600: 内部错误代码, 参数: [qkaffsindex5],存在函数索引，或者desc 索引，ORACLE 12c及以后版本

问题概述

数据库日志报：ORA-00600: 内部错误代码, 参数: [qkaffsindex5] ,详细信息如下：

*** 2023-05-15T13:31:12.142282+08:00
2023-05-15T13:31:12.142272+08:00
Incident 427804 created, dump file: /app/oracle/base/diag/rdbms/p1fspdb/p1fspdb1/incident/incdir_427804/p1fspdb1_ora_48751_i427804.trc
ORA-00600: 内部错误代码, 参数: [qkaffsindex5], [1], [], [], [], [], [], [], [], [], [], []

trc 日志报：

[TOC00000]
Jump to table of contents
Dump continued from file: /app/oracle/base/diag/rdbms/p1fspdb/p1fspdb1/trace/p1fspdb1_ora_48751.trc
[TOC00001]
ORA-00600: 内部错误代码, 参数: [qkaffsindex5], [1], [], [], [], [], [], [], [], [], [], []

[TOC00001-END]
[TOC00002]
========= Dump for incident 427804 (ORA 600 [qkaffsindex5]) ========

*** 2023-05-15T13:31:12.142778+08:00
dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3, mask=0x0)
[TOC00003]
—– Current SQL Statement for this session (sql_id=6bvp8rj1jqtpk) —–
select t.value_index || ‘’ from spc_value_t t
where t.value_index >= 2023051200000000000000000000
and t.value_index < 2023051300000000000000000000
[TOC00003-END]

问题原因

spc_value_t 表上存在 desc 索引，在函数索引、desc 索引的情况下会出现此bug。

本站从 Wordpress 迁移到 Hexo、GitHub、Vercel