Database SOS – 第4页 – Professional Database Recovery Support@+8617813235971

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

标题：使用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)

标题：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)

标题：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$

sql server数据库“正在恢复”故障处理

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：sql server数据库“正在恢复”故障处理

客户的sql server数据库在未知情况下,突然不可用,然后查看发现数据库处于“正在恢复”状态，无法使用
zzhf

查看日志发现“错误：10982，严重性：16，状态：1”

,
查看数据库文件情况
QQ20250210-201158

通过分析确认mdf文件本身完整,没有异常,强制挂载mdf文件如下错误
20250210182925

通过一些技巧进行规避,数据库挂载成功,并且checkdb检查正常,没有任何错误,至此完成该故障恢复
checkdb

如何判断数据文件是否处于begin backup状态

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：如何判断数据文件是否处于begin backup状态

在数据库恢复中,经常会遇到由于某种原因对数据库执行了begin backup,但是没有执行end backup,然后导致库无法启动的例子,那么怎么判断当前的库是存在这种情况呢？有两种方法可以对其进行判断:
1. 通过查询v$backup表来确认

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ------------
         1 NOT ACTIVE         1.1210E+12 09-FEB-25
         2 NOT ACTIVE         1.1210E+12 04-JAN-25
         3 NOT ACTIVE         1.1210E+12 09-FEB-25
         4 NOT ACTIVE         1.1210E+12 09-FEB-25

SQL> alter tablespace users begin backup;

Tablespace altered.

SQL>  select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ------------
         1 NOT ACTIVE         1.1210E+12 09-FEB-25
         2 NOT ACTIVE         1.1210E+12 04-JAN-25
         3 NOT ACTIVE         1.1210E+12 09-FEB-25
         4 ACTIVE             1.1210E+12 09-FEB-25

SQL>  alter tablespace users end backup;

Tablespace altered.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ------------
         1 NOT ACTIVE         1.1210E+12 09-FEB-25
         2 NOT ACTIVE         1.1210E+12 04-JAN-25
         3 NOT ACTIVE         1.1210E+12 09-FEB-25
         4 NOT ACTIVE         1.1210E+12 09-FEB-25

v$backup.status=’ACTIVE’表示该文件处于begin backup状态

2. 通过bbed查看kcvfh.kcvfhsta值
确认所有数据文件都没有处于begin backup状态

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ------------
         1 NOT ACTIVE         1.1210E+12 09-FEB-25
         2 NOT ACTIVE         1.1210E+12 04-JAN-25
         3 NOT ACTIVE         1.1210E+12 09-FEB-25
         4 NOT ACTIVE         1.1210E+12 09-FEB-25

list内容列表

[oracle@iZbp11c0qyuuo1gr7j98upZ ~]$ cat /home/oracle/list.txt 
         1 /u01/app/oracle/oradata/xifenfei/system01.dbf
         2 /u01/app/oracle/oradata/xifenfei/sysaux01.dbf
         3 /u01/app/oracle/oradata/xifenfei/undotbs01.dbf
         4 /u01/app/oracle/oradata/xifenfei/users01.dbf

bbed查看kcvfh.kcvfhsta值

[oracle@iZbp11c0qyuuo1gr7j98upZ ~]$ bbed listfile=/home/oracle/list.txt
Password: 

BBED: Release 2.0.0.0.0 - Limited Production on Sun Feb 9 21:20:15 2025

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

************* !!! For Oracle Internal Use only !!! ***************

BBED> set file 1
        FILE#           1

BBED> p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x2004 (KCVFHOFZ)

BBED> set file 2
        FILE#           2

BBED> p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0004 (KCVFHOFZ)

BBED> set file 3
        FILE#           3

BBED> p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0004 (KCVFHOFZ)

BBED> set file 4
        FILE#           4

BBED> p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0004 (KCVFHOFZ)

执行database begin backup

SQL> alter database begin backup;

Database altered.

SQL> select * from v$backup;

     FILE# STATUS                CHANGE# TIME
---------- ------------------ ---------- ------------
         1 ACTIVE             1.1210E+12 09-FEB-25
         2 ACTIVE             1.1210E+12 09-FEB-25
         3 ACTIVE             1.1210E+12 09-FEB-25
         4 ACTIVE             1.1210E+12 09-FEB-25

再次bbed查看kcvfh.kcvfhsta值

BBED> set file 1
        FILE#           1

BBED> p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x2001 (KCVFHHBP)

BBED> set file 2
        FILE#           2

BBED>  p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0001 (KCVFHHBP)

BBED> set file 3
        FILE#           3

BBED>  p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0001 (KCVFHHBP)

BBED> set file 4
        FILE#           4

BBED>  p kcvfh.kcvfhsta
ub2 kcvfhsta                                @138      0x0001 (KCVFHHBP)

对于非system文件kcvfh.kcvfhsta=0×0001表示begin backup状态
对于system文件kcvfh.kcvfhsta=0×2001表示begin backup状态

CDM备份缺少归档打开数据库报ORA-600 kcbzib_kcrsds_1故障处理

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：CDM备份缺少归档打开数据库报ORA-600 kcbzib_kcrsds_1故障处理

有客户联系我们,说一个19c的库,由于产生归档较多在备份过程中把部分归档删除而导致没有备份成功,从而使得他们那边一个误操作需要恢复使用备份做不完全恢复,备份厂商进行恢复并尝试强制打开库报ORA-600 kcbzib_kcrsds_1错误
ORA-600-kcbzibz_kcrsds_1

由于客户那边使用的是CDM方式备份,可以较快的准备出来一个新环境,观察客户在应用日志过程中,文件头的scn一直不变,怀疑文件头由于begin backup冻结,对其进行dump,发现确实做了hot backup操作,而且没有end backup
scn

由于缺少归档,不完全恢复没有成功,begin backup 也无法正常结束,对于这种情况,先尝试调整到文件头最大的scn值,尝试打开库

SQL> alter database open resetlogs ;
alter database open resetlogs 
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by fatal error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [],
[], [], [], [], [], [], []
Process ID: 3949615
Session ID: 5111 Serial number: 30040


--重启到mount状态
SQL> set numw 16
col CHECKPOINT_TIME for a40
set lines 150
set pages 1000
SELECT status,
to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss') checkpoint_time,FUZZY,checkpoint_change#,
count(*) ROW_NUM
FROM v$datafile_header
GROUP BY status, checkpoint_change#, to_char(checkpoint_time,'yyyy-mm-dd hh24:mi:ss'),fuzzy
ORDER BY status, checkpoint_change#, checkpoint_time;SQL> SQL> SQL> SQL>   2    3    4    5    6  

STATUS  CHECKPOINT_TIME                          FUZ CHECKPOINT_CHANGE#          ROW_NUM
------- ---------------------------------------- --- ------------------ ----------------
ONLINE  2025-02-08 22:43:01                      YES     15626238353558               56

打开库失败,只能找出来数据库最大的block中最大scn,然后调整文件头scn的值,实现数据库open

SQL> oradebug setmypid
Statement processed.
SQL> oradebug DUMPvar SGA kcsgscn_
SQL> kscn8 kcsgscn_ [060017E98, 060017EA0) = 00000000 00000000
SQL> oradebug DUMPvar SGA kcsgscn_
kscn8 kcsgscn_ [060017E98, 060017EA0) = 8CD9C896 00000E4D
SQL> 
SQL> 
SQL> 
SQL> alter database open ;
alter database open 
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/cdmbak/db/xifenfei/ob_data_D-XIFENFEI-SYSTEM_FNO-1_t13930nd'

SQL> recover database;
Media recovery complete.
SQL> alter database open;

Database altered.

SQL>

使用exp导出客户需要的表,完成本次恢复任务
对于ORA-600 kcbzib_kcrsds_1恢复的情况,以前有过大量恢复案例,修改数据库scn即可
kcbzib_kcrsds_1报错汇总
 12C数据库报ORA-600 kcbzib_kcrsds_1故障处理
 存储故障,强制拉库报ORA-600 kcbzib_kcrsds_1处理
 Patch SCN工具一键恢复ORA-600 kcbzib_kcrsds_1
此类故障处理太多,不一一列举,解决这个错误之后,数据库open成功,然后安排逻辑迁移即可

ORA-07445: exception encountered: core dump [expgod()+43] [IN_PAGE_ERROR]

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-07445: exception encountered: core dump [expgod()+43] [IN_PAGE_ERROR]

数据库在运行过程中报O/S-Error: (OS 23) 数据错误(循环冗余检查)错误

Thu Jan 30 22:00:02 2025
Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Thu Jan 30 22:00:04 2025
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_j000_12576.trc:
ORA-12012: error on auto execute of job 155962
ORA-01115: IO error reading block from file  (block # )
ORA-01110: data file 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'
ORA-27070: async read/write failed
OSD-04006: ReadFile() 失败, 无法读取文件
O/S-Error: (OS 23) 数据错误(循环冗余检查)。
ORA-06512: at "SYS.DBMS_STATS", line 25836
ORA-06512: at "SYS.DBMS_STATS", line 26171
End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
Fri Jan 31 02:00:00 2025
Clearing Resource Manager plan via parameter
Fri Jan 31 08:15:46 2025
Thread 1 advanced to log sequence 4420 (LGWR switch)
  Current log# 1 seq# 4420 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
Fri Jan 31 10:53:57 2025
Process J000 died, see its trace file
kkjcre1p: unable to spawn jobq slave process 
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_cjq0_1140.trc:
Fri Jan 31 10:53:57 2025
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_j000_7916.trc:
ORA-27102: out of memory
OSD-00043: 附加错误信息
O/S-Error: (OS 1455) 页面文件太小，无法完成操作。
Process J000 died, see its trace file
kkjcre1p: unable to spawn jobq slave process 
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_cjq0_1140.trc:
Fri Jan 31 10:54:03 2025
Exception [type: ACCESS_VIOLATION, UNABLE_TO_READ] [ADDR:0x18] [PC:0xB778B02, clsdcxini()+90]
ERROR: Unable to normalize symbol name for the following short stack (at offset 199):
dbgexProcessError()+193<-dbgeExecuteForError()+65<-dbgePostErrorKGE()+1726<-dbkePostKGE_kgsf()+75
<-kgeade()+560<-kgerev()+125<-kgerec5()+60<-sss_xcpt_EvalFilterEx()+1869<-sss_xcpt_EvalFilter()+174
<-.1.4_5+59<-00007FFD0245F306<-00007FFD024735AF<-00007FFD023D4AAF<-00007FFD0247231E<-clsdcxini()+90
<-clsdinit()+124<-ksdnfy()+225<-kscnfy()+778<-opirip()+86<-opidrv()+909<-sou2o()+98<-opimai_real()+299
<-opimai()+191<-BackgroundThreadStart()+693<-00007FFD020E7E94<-00007FFD02437AD1
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_j000_9920.trc  (incident=39621):
ORA-07445: exception encountered: core dump [clsdcxini()+90][ACCESS_VIOLATION][ADDR:0x18][PC:0xB778B02][UNABLE_TO_READ]
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_39621\orcl_j000_9920_i39621.trc

然后无法正常启动,报Exception [type: IN_PAGE_ERROR, ] [] [PC:0x2C9C015, expgod()+43]错误

Wed Feb 05 09:43:51 2025
Sweep [inc][39621]: completed
Successful mount of redo thread 1, with mount id 1720066005
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount exclusive
alter database open
Beginning crash recovery of 1 threads
 parallel recovery started with 3 processes
Started redo scan
Completed redo scan
 read 140 KB redo, 62 data blocks need recovery
Started redo application at
 Thread 1: logseq 4420, block 42375
Wed Feb 05 09:44:00 2025
Recovery of Online Redo Log: Thread 1 Group 1 Seq 4420 Reading mem 0
  Mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
Completed redo application of 0.09MB
Completed crash recovery at
 Thread 1: logseq 4420, block 42656, scn 94456019
 62 data blocks read, 62 data blocks written, 140 redo k-bytes read
Wed Feb 05 09:44:01 2025
Exception [type: IN_PAGE_ERROR, ] [] [PC:0x2C9C015, expgod()+43]
ERROR: Unable to normalize symbol name for the following short stack (at offset 199):
dbgexProcessError()+193<-dbgeExecuteForError()+65<-dbgePostErrorKGE()+1726
<-dbkePostKGE_kgsf()+75<-kgeade()+560<-kgerev()+125<-kgerec5()+60<-sss_xcpt_EvalFilterEx()+1869
<-sss_xcpt_EvalFilter()+174<-.1.4_5+59<-00007FFD0245F306<-00007FFD024735AF<-00007FFD023D4AAF
<-00007FFD0247231E<-expgod()+43<-xtyopncb()+241<-qctcopn()+613<-qctcopn()+392<-qctcpqb()+290
<-qctcpqbl()+52<-xtydrv()+148<-opitca()+1091<-kksLoadChild()+9008<-kxsGetRuntimeLock()+2320
<-kksfbc()+15225<-kkspsc0()+2117<-kksParseCursor()+181<-opiosq0()+2538<-opiosq()+23<-opiodr()+1662
<-rpidrus()+862<-rpidru()+154<-rpiswu2()+2757<-rpidrv()+6105<-rpisplu()+1607<-kqldFixedTableLoadCols()+345
<-kqldcor()+2534<-kglslod()+352<-kqlslod()+52<-PGOSF455_kqlsublod()+125<-kqllod()+7284<-kglobld()+1354
<-kglobpn()+1900<-kglpim()+336<-qcdlgtd()+260<-qcsfplob()+166<-qcsprfro()+903<-qcsprfro_tree()
+292<-qcsprfro_tree()+373<-qcspafq()+96
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_mmon_15468.trc  (incident=39749):
ORA-07445: exception encountered: core dump [expgod()+43] [IN_PAGE_ERROR] [] [PC:0x2C9C015] [] []
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_39749\orcl_mmon_15468_i39749.trc
Wed Feb 05 09:44:02 2025
Thread 1 advanced to log sequence 4421 (thread open)
Thread 1 opened at log sequence 4421
  Current log# 2 seq# 4421 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO02.LOG
Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Feb 05 09:44:02 2025
SMON: enabling cache recovery
Wed Feb 05 09:44:11 2025
Exception [type: IN_PAGE_ERROR, ] [] [PC:0x2C9C015, expgod()+43]
Errors in file d:\app\administrator\diag\rdbms\orcl\orcl\trace\orcl_ora_9308.trc  (incident=39781):
ORA-07445: ??????: ???? [expgod()+43] [IN_PAGE_ERROR] [] [PC:0x2C9C015] [] []
Incident details in: d:\app\administrator\diag\rdbms\orcl\orcl\incident\incdir_39781\orcl_ora_9308_i39781.trc
Wed Feb 05 09:44:19 2025
PMON (ospid: 12376): terminating the instance due to error 397
Instance terminated by PMON, pid = 12376

基于上述的Exception [type: IN_PAGE_ERROR, ] [] [PC:0x2C9C015, expgod()+43]错误,第一反应就是可能由于底层损坏导致数据块损坏,dbv检查文件是否报错
dbv-system

检查系统日志确认异常
20250208203059

尝试拷贝文件也报错

已经比较明确由于底层问题,解决给问题之前,需要先对文件系统进行处理,然后再对恢复出来的数据文件恢复数据

2025年第一起ORA-600 16703故障恢复

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：2025年第一起ORA-600 16703故障恢复

又有一个客户数据库启动报ORA-600 16703错误
ora-600-16703

查看alert日志信息

Successful open of redo thread 1
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
SMON: enabling cache recovery
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_ora_8784.trc  (incident=20617):
ORA-00600: 内部错误代码, 参数: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Incident details in: D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\incident\incdir_20617\xff_ora_8784_i20617.trc
Use ADRCI or Support Workbench to package the incident.
See Note 411.1 at My Oracle Support for error and packaging details.
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_ora_8784.trc:
ORA-00704: 引导程序进程失败
ORA-00704: 引导程序进程失败
ORA-00600: 内部错误代码, 参数: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\xff\xff\trace\xff_ora_8784.trc:
ORA-00704: 引导程序进程失败
ORA-00704: 引导程序进程失败
ORA-00600: 内部错误代码, 参数: [16703], [1403], [20], [], [], [], [], [], [], [], [], []
Error 704 happened during db open, shutting down database
USER (ospid: 8784): terminating the instance due to error 704
Instance terminated by USER, pid = 8784
ORA-1092 signalled during: ALTER DATABASE OPEN...
opiodr aborting process unknown ospid (8784) as a result of ORA-1092

这个故障一般是由于oracle安装介质被注入了恶意脚本,在数据库运行300天之后重启数据库就会遭遇该问题:
警告：互联网中有oracle介质被注入恶意程序导致—ORA-600 16703
对于这个问题,我们这边经过多年的恢复经验积累和研究,可以实现直接open库并且不用做逻辑迁移,完美恢复之后业务直接使用,最大限度恢复数据和最小限度减少停机时间
以往类似恢复case汇总:
tab$恢复错误汇总
 ORA-600 16703故障再现
 tab$异常被处理之后报ORA-600 13304故障处理
 ORA-600 16703直接把orachk备份表插入到tab$恢复
 最近遇到几起ORA-600 16703故障(tab$被清空),请引起重视
 ORA-00600: internal error code, arguments: [16703], [1403], [32]
aix平台tab$被删除可能出现ORA-600 [16703], [1403], [28]错误
 ORA-00600: internal error code, arguments: [16703], [1403], [4] 原因
 ORA-00600: internal error code, arguments: [16703], [1403], [4] 故障处理

ORA-600 16703故障,客户找人恢复数据库,数据库被进一步恶意破坏—ORA-00704 ORA-00922

_gc_undo_affinity=FALSE触发ORA-01558

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：_gc_undo_affinity=FALSE触发ORA-01558

最近有客户遭遇非系统回滚段报ORA-01558的故障,类似:ORA-01558: out of transaction ID’s in rollback segment _SYSSMU4_1254879796$,在之前的恢复case中遇到两次system rollback报ORA-01558而不能正常启动的案例.(ORA-01092 ORA-00604 ORA-01558故障处理和ORA-01558: out of transaction ID’s in rollback segment SYSTEM),这次是业务回滚段,出来起来相对比较简单,直接重建该回滚段所在undo表空间即可.遭遇该问题的主要原因是由于19c rac中由于禁用drm,设置了_gc_undo_affinity=FALSE参数导致.
gc_undo_affinity_ora-1558

还有一个类似bug,需要注意:Bug 19700135 ORA-600 [4187] when the undo segment wrap# is close to the max value of 0xffffffff,主要影响版本为：

关于该bug的描述

ORA-600 [4187] can occur for undo segments where wrap# is close to the max value of 0xffffffff (KSQNMAXVAL).
This normally affects databases with high transaction rate that have existed for a relatively long time.
 
To identify undo segments causing the above error and others that may potentially cause it 
in the future, run the next query:
 
 select b.segment_name, b.tablespace_name 
         ,a.ktuxeusn "Undo Segment Number"
         ,a.ktuxeslt "Slot"
         ,a.ktuxesqn "Wrap#"
   from  x$ktuxe a, dba_rollback_segs b
   where a.ktuxesqn > -429496730 and a.ktuxesqn < 0
       and a.ktuxeusn = b.segment_id;
 
Then drop the undo segments or the undo tablespace from the output above.
 
With this fix in place an error ORA-1558 is eventually produced for the affected undo segment
which still requires dropping the undo segment:
  ORA-1558 "out of transaction ID's in rollback segment %s"
   Cause: All the available transaction id's have been used
   Action: Shutdown the instance and restart using other rollback segment(s),
                then drop the rollback segment that has no more transaction id's.

public授权语句

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：public授权语句

在上一篇文章中写到数据泵迁移导致sys授权丢失(impdp导入数据丢失sys授权问题分析),这次进一步完善在有些系统中,会出现对部分权限授权给public的操作,使用逻辑方式(exp/imp,expdp/impdp)进行迁移,可能会导致这个部分权限丢失,从而使得系统部分功能异常,可以通过类似sql查询出来授权语句,在新库上执行

select 'grant ' || privilege || ' on ' || '"' || OWNER || '"."' ||
       table_name || '"' || ' to ' || grantee || ';' "GRANTS"
  from dba_tab_privs
 where privilege not in ('READ', 'WRITE')
   and table_name not like '%/%'
   and owner not in ('SYSTEM',
                     'WMSYS',
                     'XDB',
                     'CTXSYS',
                     'MDSYS',
                     'EXFSYS',
                     'APEX_030200',
                     'ORDSYS',
                     'ORDPLUGINS',
                     'DBSNMP',
                     'OLAPSYS',
                     'ORDDATA')
   and grantee in ('PUBLIC')
 order by 1;