aix磁盘损坏oracle数据库恢复

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:aix磁盘损坏oracle数据库恢复

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

客户aix环境硬盘异常导致系统无法启动,初步判断是数据文件存放在本地磁盘的空间中(本地两个盘都异常,系统无法启动),通过硬件恢复厂商镜像出来,但是通过aix文件系统直接挂载提示需要fsck,但是做fsck之后,提示大量文件丢失(最关键的数据文件和备份文件都被自动删除)
dmp-remove
fsck-remove


基于这种情况,采用镜像主机挂载的方式肯定不行,考虑直接采用软件直接解析,能够看到软件,可惜由于大量的文件系统元数据损坏,解析出来的数据文件和dmp也不可用(大量损坏和空块)
QQ20250307-122939

基于上述情况,只能采用碎片级别恢复出来数据文件
QQ20250307-123123

然后使用dul工具把数据恢复到表中,实现最大限度抢救客户数据
QQ20250307-123653

对于数据库级别恢复,这个是理论上的终极恢复方法

pg误删除数据恢复(PostgreSQL delete数据恢复)

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:pg误删除数据恢复(PostgreSQL delete数据恢复)

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

应用维护人员通过应用程序误删除了一些数据,希望对其进行恢复.通过咨询确认是PostgreSQL数据,wal和arch文件都在,取客户删除数据时间点相关wal文件
QQ20250306-231208


然后让客户提供具体涉及误删除的相关的库和表信息
QQ20250306-230846

通过工具解析wal日志,比较顺利的获取到了undo sql语句

Switch wal to 0000000300000525000000B0 on time 2025-03-05 22:23:14.263661+08
Switch wal to 0000000300000525000000B1 on time 2025-03-05 22:23:14.423082+08
Switch wal to 0000000300000525000000B2 on time 2025-03-05 22:23:15.983833+08
Switch wal to 0000000300000525000000B3 on time 2025-03-05 22:23:17.802107+08
Switch wal to 0000000300000525000000B4 on time 2025-03-05 22:23:18.942125+08
Switch wal to 0000000300000525000000B5 on time 2025-03-05 22:23:20.293585+08
Switch wal to 0000000300000525000000B6 on time 2025-03-05 22:23:21.531484+08
Switch wal to 0000000300000525000000B7 on time 2025-03-05 22:23:24.217501+08
Switch wal to 0000000300000525000000B8 on time 2025-03-05 22:23:27.504164+08
Switch wal to 0000000300000525000000B9 on time 2025-03-05 22:23:29.260754+08
Switch wal to 0000000300000525000000BA on time 2025-03-05 22:23:33.544486+08
Switch wal to 0000000300000525000000BB on time 2025-03-05 22:23:35.568116+08
Switch wal to 0000000300000525000000BC on time 2025-03-05 22:23:37.329659+08
Switch wal to 0000000300000525000000BD on time 2025-03-05 22:23:38.971834+08
Switch wal to 0000000300000525000000BE on time 2025-03-05 22:23:39.922353+08
Switch wal to 0000000300000525000000BF on time 2025-03-05 22:23:40.897294+08

QQ20250306-231657


然后把这些sql语句反向插入到生产库,完成这些误操作数据的恢复
QQ20250306-231935

如果此类的数据库(oracle,mysql,sql server,PostgreSQL)等恢复请求,需要专业恢复技术支持,请联系我们:
电话/微信:17813235971    Q Q:107644445QQ咨询惜分飞    E-Mail:dba@xifenfei.com

PostgreSQL表文件损坏恢复—pdu恢复损坏的表文件

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:PostgreSQL表文件损坏恢复—pdu恢复损坏的表文件

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在某些情况下,由于PostgreSQL表文件损坏导致无法正常访问,可以通过pdu把好的block中的数据恢复出来
准备一张测试表,里面有97条记录

his5_dms=# \d hiscrm.t_sys_oper_log;
                                            Table "hiscrm.t_sys_oper_log"
       Column       |          Type          | Collation | Nullable |Default                     
--------------------+------------------------+-----------+----------+------------
 id                 | bigint                 |           | not null | 
 module             | character varying(50)  |           |          | 
 title              | character varying(50)  |           |          | 
 alias              | character varying(50)  |           |          | 
 business_type      | integer                |           |          | 0
 method             | character varying(200) |           |          | 
 request_method     | character varying(10)  |           |          | 
 operator_type      | integer                |           |          | 0
 oper_name          | character varying(50)  |           |          | 
 dept_name          | character varying(50)  |           |          | 
 oper_url           | character varying(255) |           |          | 
 oper_ip            | character varying(50)  |           |          | 
 oper_location      | character varying(255) |           |          | 
 oper_param         | text                   |           |          | 
 json_result        | text                   |           |          | 
 status             | integer                |           |          | 0
 error_msg          | text                   |           |          | 
 oper_time          | date                   |           |          | 
 create_id          | bigint                 |           |          | 
 create_time        | bigint                 |           |          | 
 clinic_id          | bigint                 |           |          | 
 group_id           | bigint                 |           |          | 
 patient_id         | bigint                 |           |          | 
 is_patient_related | integer                |           |          | 
 business_content   | json                   |           |          | 

his5_dms=# select count(1) from hiscrm.t_sys_oper_log;
 count 
-------
    97
(1 row)

查询表对应的具体文件

his5_dms=# SELECT oid,relfilenode FROM pg_class WHERE relname='t_sys_oper_log';
  oid  | relfilenode 
-------+-------------
 16850 |       16850
(1 row)

his5_dms=# SELECT pg_relation_filepath('hiscrm.t_sys_oper_log');
 pg_relation_filepath 
----------------------
 base/16386/16850
(1 row)

his5_dms=# SHOW data_directory;
     data_directory     
------------------------
 /var/lib/pgsql/12/data
(1 row)

使用dd对文件进行破坏

[postgres@xifenfeidg ~]$ ls -l  /var/lib/pgsql/12/data/base/16386/16850
-rw-------. 1 postgres postgres 90112 Sep  5 20:26 /var/lib/pgsql/12/data/base/16386/16850
[postgres@xifenfeidg ~]$ dd if=/dev/zero of=/var/lib/pgsql/12/data/base/16386/16850 bs=512 count=1 conv=notrunc
1+0 records in
1+0 records out
512 bytes copied, 0.000158756 s, 3.2 MB/s

重启pg库

[postgres@xifenfeidg bin]$ ./pg_ctl -m fast -D /var/lib/pgsql/12/data/ stop
waiting for server to shut down.... done
server stopped
[postgres@xifenfeidg bin]$ ./pg_ctl -D /var/lib/pgsql/12/data/ start
waiting for server to start....2025-03-02 19:02:11.395 HKT [64515] LOG:  
  starting PostgreSQL 12.20 on x86_64-pc-linux-gnu, 
  compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-22), 64-bit
2025-03-02 19:02:11.396 HKT [64515] LOG:  listening on IPv6 address "::1", port 5432
2025-03-02 19:02:11.396 HKT [64515] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2025-03-02 19:02:11.396 HKT [64515] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2025-03-02 19:02:11.397 HKT [64515] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-03-02 19:02:11.403 HKT [64515] LOG:  redirecting log output to logging collector process
2025-03-02 19:02:11.403 HKT [64515] HINT:  Future log output will appear in directory "log".
 done
server started

查询数据报错

[postgres@xifenfeidg bin]$ psql
psql (16.8, server 12.20)
Type "help" for help.

postgres=# \c his5_dms;
psql (16.8, server 12.20)
You are now connected to database "his5_dms" as user "postgres".
his5_dms=#  select count(1) from hiscrm.t_sys_oper_log;
ERROR:  invalid page in block 0 of relation base/16386/16850

通过pdu进行恢复
跳过了坏块,把好的block中数据均恢复出来

his5_dms.hiscrm=# unload tab t_sys_oper_log;
正在解析表 <t_sys_oper_log>. 已解析数据页: 0, 已解析数据: 0 条
        |-块号0 空页面或页面已损坏,已跳过
正在解析表 <t_sys_oper_log>. 已解析数据页: 11, 已解析数据: 86 条
表名<t_sys_oper_log>-</var/lib/pgsql/12/data/base/16386/16850> 
    解析完成, 11 个数据页 ,共计 86 条数据. 成功 86 条; 失败【0】条 
COPY文件路径为:<his5_dms/hiscrm/t_sys_oper_log.csv>

导入pg库中

his5_dms=# truncate table hiscrm.t_sys_oper_log;
TRUNCATE TABLE
his5_dms=# \i his5_dms/COPY/hiscrm_copy.sql
SET
COPY 86
his5_dms=# 

linux rm -rf 删除数据文件恢复

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:linux rm -rf 删除数据文件恢复

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有客户由于误操作删除了oracle的部分数据文件(rm -rf 方式删除),然后自己尝试进行恢复操作,对部分文件执行了offline,导致比较麻烦的后果
jb
offline-file


接手故障之后,第一时间对其进行了镜像(因为有部分文件句柄已经释放,为了方式覆盖进一步破坏),对于没有释放的句柄可以通过类似方法进行恢复,参考以前类似恢复:
Oracle误删除数据文件恢复
Solaris rm datafile recovery—利用句柄误删除数据文件恢复

!cp  269  /u01/app/oracle/oradata/orcl/XXXXXX_DATA01.dbf
alter database datafile 12 offline;
recover datafile 12;
alter database datafile 12 online;

对于删除文件,而且句柄已经释放的文件,通过文件系统层面反删除进行恢复,参考以前类似恢复:
rm -rf误删Oracle数据库恢复
记录一次rm -rf 删除数据文件异常恢复
rm -rf 删除数据文件恢复方法—文件系统反删除+oracle碎片重组
rm


在这个恢复过程中,由于客户linux是物理机,而且本地空间不足,无法对其进行镜像,采用dd命令直接写镜像到其他的linux机器上(通过nfs方式),然后在win机器上直接挂载该nfs,记录下win上挂载nfs操作
nfs
mount-nfs

PostgreSQL恢复工具—pdu恢复单个表文件

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:PostgreSQL恢复工具—pdu恢复单个表文件

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在某些情况下,比如我们需要对单个的PostgreSQL库的表文件进行恢复(比如文件系统损坏,drop库/表,truncate表等原因,然后找到了部分oid文件),可以使用pdu对其进行完美恢复(相比pg_filedump也方便很多),具体操作步骤:
1. 由于只有单个表文件,无法获取字典信息,因此需要应用厂商/客户提供具体表创建语句

his5_dms=#    CREATE TABLE t_xff (
his5_dms(#         id                       bigint,
his5_dms(#         hospital_id              bigint,
his5_dms(#         parent_id                bigint,
his5_dms(#         disease_code             varchar(60),
his5_dms(#         disease_name             varchar(60),
his5_dms(#         type                     smallint,
his5_dms(#         py                       varchar(60),
his5_dms(#         wb                       varchar(60),
his5_dms(#         sc                       varchar(20),
his5_dms(#         order_no                 int,
his5_dms(#         state                    smallint,
his5_dms(#         create_datetime          timestamp(6),
his5_dms(#         create_id                bigint,
his5_dms(#         edit_datetime            timestamp(6),
his5_dms(#         edit_id                  bigint,
his5_dms(#         search_path              varchar(300),
his5_dms(#         diagnosis_sort           int,
his5_dms(#         category_name            varchar(40),
his5_dms(#         input_option             varchar(40),
his5_dms(#         category_class           smallint,
his5_dms(#         memo1                    varchar(300),
his5_dms(#         memo2                    varchar(300),
his5_dms(#         other_code               varchar(60),
his5_dms(#         other_name               varchar(60),
his5_dms(#         special_disease_flag     smallint
his5_dms(#    );
CREATE TABLE

2. 把oid文件pdu放到restore库中

[root@xifenfeidg public]# pwd
/tmp/pdu/restore/public
[root@xifenfeidg public]# ls -l
total 7144
-rw-r--r--. 1 root root 7315456 Mar  2 21:04 123456
[root@xifenfeidg public]# 

3. 使用add语句在pdu加载数据类型

restore.public=# add 123456 t_xff bigint,bigint,bigint,varchar,varchar,smallint,varchar,varchar,varchar,
int,smallint,timestamp,bigint,timestamp,bigint,varchar,int,varchar,varchar,
smallint,varchar,varchar,varchar,varchar,smallint;
添加完成,请用\dt;查看可unload的表
restore.public=# \dt;
|--------------------------------------------------|
|               表名                  |  表大小    |
|--------------------------------------------------|
|    t_xff                            |  6.98 MB   |
|--------------------------------------------------|

        仅显示表大小排名前 1 的表名

4.使用pdu恢复表数据

restore.public=# unload t_xff;
正在解析表 <t_xff>. 已解析数据页: 893, 已解析数据: 46998 条
<t_xff>-<restore/public/123456> 解析完成, 894 个数据页 ,共计 46998 条数据. 成功 46998 条; 失败【0】条 
 COPY文件路径为:<restore/public/t_xff.csv>
restore.public=# unload COPY;

COPY命令导出完成, 文件路径: restore/COPY/public_copy.sql

5.导入数据到pg库中

his5_dms=# \i restore/COPY/public_copy.sql
SET
COPY 46998
his5_dms=# select count(1) from t_xff;
 count 
-------
 46998
(1 row)
his5_dms=# \x
Expanded display is on.
his5_dms=# select * from t_xff limit 1;
-[ RECORD 1 ]--------+---------------------------
id                   | 323839
hospital_id          | 0
parent_id            | 301
disease_code         | 57.8900x003
disease_name         | 腹腔镜下膀胱颈悬吊术
type                 | 2
py                   | fqjxpgjxds
wb                   | eeqgeeceks
sc                   | 
order_no             | 0
state                | 1
create_datetime      | 2022-09-29 15:22:58.588492
create_id            | 
edit_datetime        | 
edit_id              | 
search_path          | 301,
diagnosis_sort       | 
category_name        | 
input_option         | 
category_class       | 3
memo1                | 
memo2                | 
other_code           | 
other_name           | 
special_disease_flag | 0

PostgreSQL恢复工具—pdu工具介绍

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:PostgreSQL恢复工具—pdu工具介绍

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

张晨同学开发了一个PostgreSQL数据恢复工具PDU(PDU: Postgresql Data Rescue Tool),我这边配合做一些测试
使用帮助命令

PDU.public=# ;

PDU数据拯救工具 | 命令帮助
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
**基础操作**
b;                                      │ 初始化数据库元信息
exit;                                   │ 退出工具

**数据库切换**
use <db>;                               │ 指定目标数据库 (例: use logs;)
set <schema>;                           │ 指定操作模式 (例: set recovery;)

**元数据展示**
\l;                                     │ 列出所有数据库
\dn;                                    │ 显示当前数据库模式
\dt;                                    │ 列出当前模式下的表
\d+ <table>;                            │ 查看表结构详情 (例: \d+ users;)
\d <table>;                             │ 查看表列类型 (例: \d users;)

**数据导出**
unload <table>;                         │ 导出表数据 → ./<表名>.csv (例: unload orders;)
unload SCH;                             │ 导出当前模式所有数据
unload DDL;                             │ 生成模式结构定义文件
unload COPY;                            │ 生成PSQL COPY语句脚本

**误删数据恢复**
scan t1;                                │ 扫描被误删的表
restore del <Tx Number>;                │ 通过事务号恢复被误删的数据
--------------------------------------------------------------------------------------
scan drop;                              │ 扫描wal日志中的drop事务
restore drop <Tx Number>;               │ 通过事务号恢复被drop的表
add <oid> <tablename> <attibutes>;      │ 将表信息手动添加到restore库中
例如: <add 12345 t1 varchar,varchar,timestamp,varchar,numeric,varchar,varchar,varchar,numeric;>

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
语法规则
◈ 指令后缀必须带 `;`

加载PostgreSQL元数据

PDU.public=# b;
开始初始化...
 -pg_database:</var/lib/pgsql/12/data/global/1262>
    【postgres】
      -pg_schema:</var/lib/pgsql/12/data/base/14399/2615>
      -pg_class:</var/lib/pgsql/12/data/base/14399/1259>,共55行
      -pg_attribute:</var/lib/pgsql/12/data/base/14399/1249>,共2913行
      模式:
        -->public,2张表
    【his5_dms】
      -pg_schema:</var/lib/pgsql/12/data/base/16386/2615>
      -pg_class:</var/lib/pgsql/12/data/base/16386/1259>,共793行
      -pg_attribute:</var/lib/pgsql/12/data/base/16386/1249>,共31329行
      模式:
        -->public,660张表
        -->hiscrm,55张表
        -->pgagent,8张表
        -->report,7张表
        -->statistics,10张表

查看当前有哪些库

PDU.public=# \l;
|------------------|
|     数据库名     |
|------------------|
|    postgres      |
|    template1     |
|    template0     |
|    his5_dms      |
|    restore       |
|------------------|

      5 rows selected

进入某个库

PDU.public=# use his5_dms;
|----------------------------------------|
|          模式             |  表数量    |
|----------------------------------------|
|    public                 |  660       |
|    hiscrm                 |  55        |
|    pgagent                |  8         |
|    report                 |  7         |
|    statistics             |  10        |
|----------------------------------------|

进入某个模式

his5_dms.public=# set hiscrm;
|--------------------------------------------------|
|               表名                  |  表大小    |
|--------------------------------------------------|
|    t_patient_other                  |  600.00 KB |
|    t_sys_oper_log                   |  88.00 KB  |
|    t_auth                           |  88.00 KB  |
|    t_setting_user                   |  56.00 KB  |
|    t_field_define                   |  32.00 KB  |
|    t_oper_log                       |  16.00 KB  |
|    t_role                           |  16.00 KB  |
|    t_sys_login_log                  |  8.00 KB   |
|    t_appointment_item               |  8.00 KB   |
|    t_clinic                         |  8.00 KB   |
|    t_dept                           |  8.00 KB   |
|    t_employee                       |  8.00 KB   |
|    t_menu                           |  8.00 KB   |
|    t_patient_label                  |  8.00 KB   |
|    t_patient_label_detail_tpl       |  8.00 KB   |
|    t_patient_source_ref             |  8.00 KB   |
|    t_return_visit_tpl               |  8.00 KB   |
|    t_setting_clinic                 |  8.00 KB   |
|    t_setting_notify                 |  8.00 KB   |
|    t_sms_template_category          |  8.00 KB   |
|--------------------------------------------------|

        仅显示表大小排名前 50 的表名

显示部分表

his5_dms.hiscrm=# \dt;
|--------------------------------------------------|
|               表名                  |  表大小    |
|--------------------------------------------------|
|    t_patient_other                  |  600.00 KB |
|    t_sys_oper_log                   |  88.00 KB  |
|    t_auth                           |  88.00 KB  |
|    t_setting_user                   |  56.00 KB  |
|    t_field_define                   |  32.00 KB  |
|    t_oper_log                       |  16.00 KB  |
|    t_role                           |  16.00 KB  |
|    t_sys_login_log                  |  8.00 KB   |
|    t_appointment_item               |  8.00 KB   |
|    t_clinic                         |  8.00 KB   |
|    t_dept                           |  8.00 KB   |
|    t_employee                       |  8.00 KB   |
|    t_menu                           |  8.00 KB   |
|    t_patient_label                  |  8.00 KB   |
|    t_patient_label_detail_tpl       |  8.00 KB   |
|    t_patient_source_ref             |  8.00 KB   |
|    t_return_visit_tpl               |  8.00 KB   |
|    t_setting_clinic                 |  8.00 KB   |
|    t_setting_notify                 |  8.00 KB   |
|    t_sms_template_category          |  8.00 KB   |
|--------------------------------------------------|

        仅显示表大小排名前 50 的表名

显示某个表的信息

his5_dms.hiscrm=# \d+ t_auth;
----------------------------------------------------------------
|                            建表语句                           |
----------------------------------------------------------------
   CREATE TABLE t_auth (
        id                       bigint,
        clinic_id                bigint,
        group_id                 bigint,
        parient_id               varchar(64),
        menu_id                  varchar(64),
        auth_key                 varchar(60),
        auth_name                varchar(64),
        uris                     varchar,
        rely                     varchar(255),
        state                    bigint,
        sort                     bigint,
        tag                      bigint,
        explain                  varchar(255),
        desc                     varchar(255)
   );
----------------------------------------------------------------
|                                                              |
----------------------------------------------------------------
his5_dms.hiscrm=# \d t_auth;
----------------------------------------------------------------
|                            列类型                             |
----------------------------------------------------------------
bigint,bigint,bigint,varchar,varchar,varchar,varchar,varchar,varchar,bigint,bigint,bigint,varchar,varchar

恢复表数据

his5_dms.hiscrm=# unload t_auth;
正在解析表 <t_auth>. 已解析数据页: 11, 已解析数据: 492 条
<t_auth>-</var/lib/pgsql/12/data/base/16386/16895> 解析完成, 12 个数据页 ,共计 492 条数据. 成功 492 条; 失败【0】条 
 COPY文件路径为:<his5_dms/hiscrm/t_auth.csv>

确认恢复表的数据情况

[root@xifenfeidg hiscrm]# wc -l t_auth.csv
492 t_auth.csv

QQ20250228-224837


PostgreSQL中查询表实际数据情况

his5_dms=# select count(1) from hiscrm.t_auth;
 count 
-------
   492
(1 row)
is5_dms=# \d hiscrm.t_auth;
                                Table "hiscrm.t_auth"
   Column   |          Type          | Collation | Nullable |         Default         
------------+------------------------+-----------+----------+-------------------------
 id         | bigint                 |           | not null | 
 clinic_id  | bigint                 |           |          | 
 group_id   | bigint                 |           |          | 
 parient_id | character varying(64)  |           | not null | 
 menu_id    | character varying(64)  |           | not null | 
 auth_key   | character varying(60)  |           | not null | 
 auth_name  | character varying(64)  |           | not null | 
 uris       | text                   |           | not null | 
 rely       | character varying(255) |           |          | NULL::character varying
 state      | bigint                 |           | not null | 
 sort       | bigint                 |           | not null | 
 tag        | bigint                 |           |          | '0'::bigint
 explain    | character varying(255) |           |          | NULL::character varying
 desc       | character varying(255) |           |          | NULL::character varying

truncate表

his5_dms=# truncate table hiscrm.t_auth;
TRUNCATE TABLE
his5_dms=# select count(1) from hiscrm.t_auth;
 count 
-------
     0
(1 row)

导入数据,并验证恢复效果

his5_dms=# \i /tmp/pdu/his5_dms/COPY/hiscrm_copy.sql 
SET
COPY 492
his5_dms=# select count(1) from  hiscrm.t_auth;
 count 
-------
   492
(1 row)

通过验证pdu可以在数据库离线的情况下,恢复PostgreSQL数据库中表的数据,更加方便和灵活的实现替代pg_filedump功能,而且pdu还在不断完善和新功能更新中

CSSD signal 11 in thread clssnmRcfgMgrThread故障处理

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:CSSD signal 11 in thread clssnmRcfgMgrThread故障处理

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

一个客户,集群无法启动,只能启动到如下状态
11


查看cssd日志有CSSD signal 11 in thread clssnmRcfgMgrThread报错

2025-02-21 18:21:25.500: [    CSSD][2788693760]clssnmDoSyncUpdate: node(2) is transitioning from joining state to active state
2025-02-21 18:21:25.500: [    CSSD][2788693760]clssnmDoSyncUpdate: Wait for 0 vote ack(s)
2025-02-21 18:21:25.500: [    CSSD][2788693760]clssnmDoSyncUpdate: waiting to update states on disk
2025-02-21 18:21:25.700: [    CSSD][2788693760]clssnmDoSyncUpdate: waiting to update states on disk
2025-02-21 18:21:25.901: [    CSSD][2788693760]clssnmDoSyncUpdate: waiting to update states on disk
2025-02-21 18:21:25.995: [    CSSD][2801538816]clssnmvDiskPing: Writing with status 0x2, timestamp 1740133285/5870104
2025-02-21 18:21:25.997: [    CSSD][2799818496]clssnmvDiskKillCheck: not evicted, file /dev/dm-4 flags 0x00000000,
                                                                          kill block unique 0, my unique 1740133265
2025-02-21 18:21:26.000: [    CSSD][2793424640]clssgmWaitOnEventValue: after CmInfo State  val 3, eval 2 waited 500
2025-02-21 18:21:26.101: [    CSSD][2788693760]clssnmDoSyncUpdate: waiting to update states on disk
2025-02-21 18:21:26.302: [    CSSD][2788693760]clssnmDoSyncUpdate: waiting to update states on disk
2025-02-21 18:21:26.497: [    CSSD][2801538816]clssnmvDiskPing: Writing with status 0x2, timestamp 1740133286/5870604
2025-02-21 18:21:26.502: [    CSSD][2788693760]clssnmDoSyncUpdate: waiting to update states on disk
2025-02-21 18:21:26.702: [    CSSD][2788693760]clssnmDoSyncUpdate: waiting to update states on disk
2025-02-21 18:21:26.902: [    CSSD][2788693760]clssnmDoSyncUpdate: waiting to update states on disk
2025-02-21 18:21:26.997: [    CSSD][2799818496]clssnmvDiskKillCheck: not evicted, file /dev/dm-4 flags 0x00000000,
                                                                      kill block unique 0, my unique 1740133265
2025-02-21 18:21:26.997: [    CSSD][2801538816]clssnmvDiskPing: Writing with status 0x2, timestamp 1740133286/5871114
2025-02-21 18:21:27.000: [    CSSD][2793424640]clssgmWaitOnEventValue: after CmInfo State  val 3, eval 2 waited 0
2025-02-21 18:21:27.102: [    CSSD][2788693760]clssnmCheckDskInfo: Checking disk info...
2025-02-21 18:21:27.102: [    CSSD][2788693760]clssnmCheckDskInfo: diskTimeout set to (200000)ms
2025-02-21 18:21:27.103: [    CSSD][2788693760]###################################
2025-02-21 18:21:27.103: [    CSSD][2788693760]clssscExit: CSSD signal 11 in thread clssnmRcfgMgrThread
2025-02-21 18:21:27.103: [    CSSD][2788693760]###################################
2025-02-21 18:21:27.103: [    CSSD][2788693760](:CSSSC00012:)clssscExit: A fatal error occurred and the CSS daemon is terminating abnormally
2025-02-21 18:21:27.103: [    CSSD][2788693760]

----- Call Stack Trace -----
2025-02-21 18:21:27.103: [    CSSD][2788693760]calling              call     entry                argument values in hex      
2025-02-21 18:21:27.103: [    CSSD][2788693760]location             type     point                (? means dubious value)     
2025-02-21 18:21:27.103: [    CSSD][2788693760]-------------------- -------- -------------------- ----------------------------
2025-02-21 18:21:27.109: [    CSSD][2788693760]clssscExit()+745     call     kgdsdst()            000000000 ? 000000000 ?
2025-02-21 18:21:27.109: [    CSSD][2788693760]                                                   7F9EA637A650 ? 7F9EA637A728 ?
2025-02-21 18:21:27.109: [    CSSD][2788693760]                                                   7F9EA637F1D0 ? 000000003 ?
2025-02-21 18:21:27.109: [    CSSD][2788693760]s0clsssc_sighandler  call     clssscExit()         001FB9FA0 ? 000000002 ?
2025-02-21 18:21:27.109: [    CSSD][2788693760]()+616                                             7F9EA637A650 ? 7F9EA637A728 ?
2025-02-21 18:21:27.109: [    CSSD][2788693760]                                                   7F9EA637F1D0 ? 000000003 ?
2025-02-21 18:21:27.110: [    CSSD][2788693760]__sighandler()       call     s0clsssc_sighandler  00000000B ? 000000002 ?
2025-02-21 18:21:27.110: [    CSSD][2788693760]                              ()                   7F9EA637A650 ? 7F9EA637A728 ?
2025-02-21 18:21:27.110: [    CSSD][2788693760]                                                   7F9EA637F1D0 ? 000000003 ?
2025-02-21 18:21:27.110: [    CSSD][2788693760]clssnmCheckSplit()+  signal   __sighandler()       001BEE8A8 ? 000000000 ?
2025-02-21 18:21:27.110: [    CSSD][2788693760]378                                                002039A80 ? 000000001 ?
2025-02-21 18:21:27.110: [    CSSD][2788693760]                                                   0004D2B40 ? 7F9EA63803C0 ?
2025-02-21 18:21:27.110: [    CSSD][2788693760]clssnmCheckDskInfo(  call     clssnmCheckSplit()   001FB9FA0 ? 001DC83F0 ?
2025-02-21 18:21:27.110: [    CSSD][2788693760])+387                                              000030D40 ? 000000001 ?
2025-02-21 18:21:27.110: [    CSSD][2788693760]                                                   0004D2B40 ? 7F9EA63803C0 ?
2025-02-21 18:21:27.110: [    CSSD][2788693760]clssnmDoSyncUpdate(  call     clssnmCheckDskInfo(  001FB9FA0 ? 001DC83F0 ?
2025-02-21 18:21:27.110: [    CSSD][2788693760])+4692                        )                    000000001 ? 000000001 ?
2025-02-21 18:21:27.110: [    CSSD][2788693760]                                                   0004D2B40 ? 7F9EA63803C0 ?
2025-02-21 18:21:27.110: [    CSSD][2788693760]clssnmLocalJoinEven  call     clssnmDoSyncUpdate(  001FB9FA0 ? 001DC83F0 ?
2025-02-21 18:21:27.110: [    CSSD][2788693760]t()+3992                      )                    FFFFFFFFFFFFFFFF ?
2025-02-21 18:21:27.110: [    CSSD][2788693760]                                                   000000001 ? 7F9EA6380D20 ?
2025-02-21 18:21:27.110: [    CSSD][2788693760]                                                   7F9EA63803C0 ?
2025-02-21 18:21:27.110: [    CSSD][2788693760]clssnmRcfgMgrThread  call     clssnmLocalJoinEven  001FB9FA0 ? 001DC83F0 ?
2025-02-21 18:21:27.110: [    CSSD][2788693760]()+2290                       t()                  FFFFFFFFFFFFFFFF ?
2025-02-21 18:21:27.110: [    CSSD][2788693760]                                                   000000001 ? 7F9EA6380D20 ?
2025-02-21 18:21:27.110: [    CSSD][2788693760]                                                   7F9EA63803C0 ?
2025-02-21 18:21:27.110: [    CSSD][2788693760]clssscthrdmain()+25  call     clssnmRcfgMgrThread  001FB9FA0 ? 001DC83F0 ?
2025-02-21 18:21:27.110: [    CSSD][2788693760]3                             ()                   FFFFFFFFFFFFFFFF ?
2025-02-21 18:21:27.110: [    CSSD][2788693760]                                                   000000001 ? 7F9EA6380D20 ?
2025-02-21 18:21:27.110: [    CSSD][2788693760]                                                   7F9EA63803C0 ?
2025-02-21 18:21:27.111: [    CSSD][2788693760]start_thread()+209   call     clssscthrdmain()     001FB9FA0 ? 001DC83F0 ?
2025-02-21 18:21:27.111: [    CSSD][2788693760]                                                   FFFFFFFFFFFFFFFF ?
2025-02-21 18:21:27.111: [    CSSD][2788693760]                                                   000000001 ? 7F9EA6380D20 ?
2025-02-21 18:21:27.111: [    CSSD][2788693760]                                                   7F9EA63803C0 ?
2025-02-21 18:21:27.111: [    CSSD][2788693760]clone()+109          call     start_thread()       7F9EA6381700 ? 001DC83F0 ?
2025-02-21 18:21:27.111: [    CSSD][2788693760]                                                   FFFFFFFFFFFFFFFF ?
2025-02-21 18:21:27.111: [    CSSD][2788693760]                                                   000000001 ? 7F9EA6380D20 ?
2025-02-21 18:21:27.111: [    CSSD][2788693760]                                                   7F9EA63803C0 ?
2025-02-21 18:21:27.111: [    CSSD][2788693760]0000000000000000     call     clone()              7F9EA6381700 ? 001DC83F0 ?
2025-02-21 18:21:27.111: [    CSSD][2788693760]                                                   FFFFFFFFFFFFFFFF ?
2025-02-21 18:21:27.111: [    CSSD][2788693760]                                                   000000001 ? 7F9EA6380D20 ?
2025-02-21 18:21:27.111: [    CSSD][2788693760]                                                   7F9EA63803C0 ?
2025-02-21 18:21:27.111: [    CSSD][2788693760] 
2025-02-21 18:21:27.111: [    CSSD][2788693760]--------------------- Binary Stack Dump ---------------------

这里提示表决盘超时,尝试启动nocrs貌似,在表决盘存在的情况下,启动依旧失败,通过处理让启动过程不读表决盘,启动nocrs模式成功,并mount其他业务磁盘组
22
33
44


确认其他磁盘没有问题,重建crs磁盘组

SQL> create diskgroup OCR  external redundancy disk '/dev/dm-4' force  attribute 'COMPATIBLE.ASM' = '11.2.0';
# ocrconfig -restore /u01/app/11.2.0.3/grid/cdata/scan/backup00.ocr
# crsctl replace votedisk +OCR
SQL> create spfile from pfile='/tmp/pfile.asm';

然后重启crs恢复正常

使用sid方式直接访问pdb(USE_SID_AS_SERVICE_LISTENER)

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:使用sid方式直接访问pdb(USE_SID_AS_SERVICE_LISTENER)

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

有些应用,因为特殊原因,需要通过sid来访问数据库,在pdb环境中原则上都是通过服务名访问的,可以通过一定的监听配置实现使用pdb名的sid来访问该pdb
在pdb0中创建u_test用户并授权

[oracle@ora19c:/u01/app/oracle/product/19.3.0/db/network/admin]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 17 22:01:54 2025
Version 19.24.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0

sys@ORA19C 22:01:54> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB0                           READ WRITE NO
         4 PDBXXX                         MOUNTED
sys@ORA19C 22:01:56> alter session set container=pdb0;

Session altered.

Elapsed: 00:00:00.16
sys@ORA19C 22:02:07> create user u_test identified by oracle;

User created.

Elapsed: 00:00:00.29
sys@ORA19C 22:02:21> grant dba to u_test;

Grant succeeded.

Elapsed: 00:00:00.01

监听的配置和状态

[oracle@ora19c:/home/oracle]$ cat /u01/app/oracle/product/19.3.0/db/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.3.0/db/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora19c)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )



[oracle@ora19c:/home/oracle]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-FEB-2025 22:07:12

Copyright (c) 1991, 2024, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                17-FEB-2025 22:06:39
Uptime                    0 days 0 hr. 0 min. 32 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3.0/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora19c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "21b067cbda1dbcd4e0630100007f12b6" has 1 instance(s).
  Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "22394b20557aff3ee0630100007fafe0" has 1 instance(s).
  Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
  Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "ora19c" has 1 instance(s).
  Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "ora19cXDB" has 1 instance(s).
  Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "pdb0" has 1 instance(s).
  Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "pdbxxx" has 1 instance(s).
  Instance "ora19c", status READY, has 1 handler(s) for this service...
The command completed successfully

创建pdb0基于服务和sid的tns(pdb0,pdb0_sid)

[oracle@ora19c:/u01/app/oracle/product/19.3.0/db/network/admin]$ cat tnsnames.ora
pdb0 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora19c)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb0)
    )
  )
pdb0_sid =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = ora19c)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (sid = pdb0)
    )
  )

[oracle@ora19c:/u01/app/oracle/product/19.3.0/db/network/admin]$ tnsping pdb0

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-FEB-2025 22:03:00

Copyright (c) 1997, 2024, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.3.0/db/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ora19c)(PORT = 1521)) 
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdb0)))
OK (0 msec)
[oracle@ora19c:/u01/app/oracle/product/19.3.0/db/network/admin]$ tnsping pdb0_sid

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-FEB-2025 22:03:10

Copyright (c) 1997, 2024, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/19.3.0/db/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ora19c)(PORT = 1521))
 (CONNECT_DATA = (SERVER = DEDICATED) (sid = pdb0)))
OK (0 msec)

分别测试pdb0和pdb0_sid访问数据库
测试证明基于服务名的方式可以正常访问pdb,基于sid的方式无法访问pdb

[oracle@ora19c:/home/oracle]$ sqlplus u_test/oracle@pdb0

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 17 22:08:35 2025
Version 19.24.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Last Successful login time: Mon Feb 17 2025 22:06:11 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0

u_test@PDB0 22:08:35> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
[oracle@ora19c:/home/oracle]$ sqlplus u_test/oracle@pdb0_sid

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 17 22:08:39 2025
Version 19.24.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

ERROR:
ORA-12505: TNS:listener does not currently know of SID given in connect
descriptor


Enter user-name: 
ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name: 
ERROR:
ORA-01017: invalid username/password; logon denied


SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus

在listener.ora中增加USE_SID_AS_SERVICE_LISTENER = ON,并reload加载
注意:USE_SID_AS_SERVICE_LISTENER 中的LISTENER根据不同的监听名字而发生改变

[oracle@ora19c:/home/oracle]$ cat /u01/app/oracle/product/19.3.0/db/network/admin/listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/19.3.0/db/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ora19c)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
USE_SID_AS_SERVICE_LISTENER = ON

[oracle@ora19c:/home/oracle]$ lsnrctl reload

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-FEB-2025 22:12:13

Copyright (c) 1991, 2024, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1521)))
The command completed successfully

[oracle@ora19c:/home/oracle]$ lsnrctl status

LSNRCTL for Linux: Version 19.0.0.0.0 - Production on 17-FEB-2025 22:13:05

Copyright (c) 1991, 2024, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ora19c)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 19.0.0.0.0 - Production
Start Date                17-FEB-2025 22:06:39
Uptime                    0 days 0 hr. 6 min. 26 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/19.3.0/db/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ora19c/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ora19c)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "21b067cbda1dbcd4e0630100007f12b6" has 1 instance(s).
  Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "22394b20557aff3ee0630100007fafe0" has 1 instance(s).
  Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "86b637b62fdf7a65e053f706e80a27ca" has 1 instance(s).
  Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "ora19c" has 1 instance(s).
  Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "ora19cXDB" has 1 instance(s).
  Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "pdb0" has 1 instance(s).
  Instance "ora19c", status READY, has 1 handler(s) for this service...
Service "pdbxxx" has 1 instance(s).
  Instance "ora19c", status READY, has 1 handler(s) for this service...
The command completed successfully

尝试tns名字为pdb0和pdb0_sid名字登录数据库
在listener.ora文件中设置了USE_SID_AS_SERVICE_LISTENER = ON之后,基于sid的方式可以直接访问pdb

[oracle@ora19c:/home/oracle]$ sqlplus u_test/oracle@pdb0_sid

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 17 22:12:16 2025
Version 19.24.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Last Successful login time: Mon Feb 17 2025 22:08:35 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0

u_test@PDB0 22:12:16> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0
[oracle@ora19c:/home/oracle]$ sqlplus u_test/oracle@pdb0

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 17 22:12:28 2025
Version 19.24.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Last Successful login time: Mon Feb 17 2025 22:12:16 +08:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0

u_test@PDB0 22:12:28> 

ORA-00069: cannot acquire lock — table locks disabled for xxxx

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:ORA-00069: cannot acquire lock — table locks disabled for xxxx

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

在oracle数据库中删除用户遭遇ORA-00069: cannot acquire lock — table locks disabled for HR_XXX_01错误

SQL>  drop user XFF cascade;
 drop user XFF cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00069: cannot acquire lock -- table locks disabled for HR_XXX_01

关于ORA-00069错误解释

[oracle@xifenfei.com ~]$ oerr ora 00069
00069, 00000, "cannot acquire lock -- table locks disabled for %s"
// *Cause: A command was issued that tried to lock the table indicated in
//         the message. Examples of commands that can lock tables are:
//         LOCK TABLE, ALTER TABLE ... ADD (...), and so on.
// *Action: Use the ALTER TABLE ... ENABLE TABLE LOCK command, and retry
//          the command.

尝试lock表,直接hang,强制终止

SQL> alter table XFF.HR_XXX_01 enable table lock; 



^Calter table XFF.HR_XXX_01 enable table lock
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation

查询tab$.flags的值

SQL> col object_name for a30
SQL> set lines 150
SQL> select x. object_name,obj#, flags
  2  from sys.tab$,(
  3  select object_name, object_id
  4  from dba_objects
  5  where owner='XFF'
  6  and object_name in ('HR_XXX_01','HR_XXXCONTROL','XXXLZB_JD1')
  7  and object_type = 'TABLE') x
  8  where obj# = x.object_id;

OBJECT_NAME                          OBJ#      FLAGS
------------------------------ ---------- ----------
XXXLZB_JD1                         246416 1073742353
HR_XXXCONTROL                      246421 1073742353
HR_XXX_01                          246424 1073742359

发现报错表的flags和其他表不一样(其他表为1073742353,而报错表为1073742359),对于这种情况官方给出来的解决方法,关闭库,确保没有任何额外会话连接上来
ora-00069


因为本身要重启库维护,直接把库启动到upgrade模式进行操作

[oracle@xifenfei.com ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Feb 14 20:29:28 2025
Version 19.24.0.0.0

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.24.0.0.0

SQL> alter system checkpoint;

System altered.

SQL> /

System altered.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 4.2950E+10 bytes
Fixed Size                 23149944 bytes
Variable Size            9529458688 bytes
Database Buffers         3.3286E+10 bytes
Redo Buffers              111067136 bytes
Database mounted.
Database opened.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 4.2950E+10 bytes
Fixed Size                 23149944 bytes
Variable Size            9529458688 bytes
Database Buffers         3.3286E+10 bytes
Redo Buffers              111067136 bytes
Database mounted.
Database opened.
SQL>  drop user XFF cascade;
 drop user FZHR cascade
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00069: cannot acquire lock -- table locks disabled for HR_XXX_01


SQL> alter table XFF.HR_XXX_01 enable table lock; 

Table altered.

SQL>  drop user XFF cascade;

User dropped.

SQL> 

ORA-600 [4000] [a]相关bug

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:ORA-600 [4000] [a]相关bug

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

ORA-600 [4000 ] [a]一般是这样的报错格式,其中[a] Undo segment number,类似错误主要bug以及对应的修复版本列表

Bug Fixed Description
26966120 18.18, 18.3, 19.1 PDML workload reports ORA-7445 [kdmsfree] / ORA-00600 [4000]
16761566 11.2.0.3.9, 11.2.0.3.BP22, 11.2.0.4, 12.1.0.2, 12.2.0.1 Instance fails to start with ORA-600 [4000] [usn#]
13910190 11.2.0.3.BP15, 11.2.0.4, 12.1.0.1 ORA-600 [4000] from plugged in tablespace in Exadata
37173201 Hitting ORA-600 [4000] during shutdown
36440495 19.26 SECURE FILE LOB CAUSING ORA-00600:[4000]
34547607 19.23, 23.4 [TXN MGMT LOCAL] ORA-600 [ktugct: corruption detected] w/ Compression & RAC DB Instances Crash
32800248 19.24, 23.4 DB:DISTRIB: Avoid ORA-600[4000]/ORA-600[4097] in the DB background RECO scenario.
35143304 19.24 consider converting ORA-600 [4000] to pdb-specific assert or soft assert
33343993 19.16 Convert ORA-600 [4000] to PDB Specific Assert and Crash Only the Affected PDB
32156194 19.12 ORA-600 [25027] during the select on x$ktcxb
32765471 aim:ORA-600 [4000] – kccpb_sanity_check
23030488 18.1 ORA-00600 [4000] During First Open of PDB After Undo Mode Switch
22610979 18.1 ORA-00600 [4000] On DB Close of STANDBY Due to MMON Process
21770222 12.2.0.1 ORA-600: [4000] in CDB
21379969 12.2.0.1 ORA-00600 [4000] after a tablespace is transported and plugged into another DB
20427315 12.2.0.1 ORA-600 [4000] While Performing DMLs In Freelist Segment
20407770 12.2.0.1 ORA-00600 [4000] error in CDB and DDL operations in PDBs
19352922 12.2.0.1 IMC: ORA-600[4000] may occur on HCC block
14741727 11.2.0.2.9, 11.2.0.2.BP19, 11.2.0.3.BP12, 11.2.0.3.BP13, 11.2.0.4, 12.1.0.1 Fixes for bug 12326708 and 14624146 can cause problems – backout fix
12619529 11.2.0.3.BP18, 11.2.0.4, 12.1.0.1 ORA-600[kdsgrp1] from SELECT on plugged in tablespace with FLASHBACK
10425010 11.2.0.3, 12.1.0.1 Stale data blocks may be returned by Exadata FlashCache
9145541 11.1.0.7.4, 11.2.0.1.2, 11.2.0.2, 12.1.0.1 OERI[25027]/OERI[4097]/OERI[4000]/ORA-1555 in plugged datafile after CREATE CONTROLFILE in 11g
12353983 11.2.0.1 ORA-600 [4000] with XA in RAC
7687856 11.2.0.1 ORA-600 [4000] from DML on transported ASSM tablespace
2917441 11.1.0.6 OERI [4000] during startup
3115733 9.2.0.5, 10.1.0.2 OERI[4000] / index corruption can occur during index coalesce
2959556 9.2.0.5, 10.1.0.2 STARTUP after an ORA-701 fails with OERI[4000]
1371820 8.1.7.4, 9.0.1.4, 9.2.0.1 OERI:4506 / OERI:4000 possible against transported tablespace
434596 7.3.4.2, 8.0.3.0 ORA-600[4000] from altering storage of BOOTSTRAP$