标签云
asm恢复 bbed bootstrap$ dul kcbzib_kcrsds_1 kccpb_sanity_check_2 kcratr_nab_less_than_odr MySQL恢复 obet ORA-00312 ORA-00704 ORA-00742 ORA-01110 ORA-01200 ORA-01555 ORA-01578 ORA-01595 ORA-600 2662 ORA-600 2663 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-600 kcratr_nab_less_than_odr ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 ORACLE恢复 Oracle 恢复 ORACLE数据库恢复 oracle碎片 OSD-04016 YOUR FILES ARE ENCRYPTED 比特币加密文章分类
- MongoDB (1)
- MongoDB恢复 (1)
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (112)
- 数据库 (1,885)
- DB2 (22)
- MySQL (82)
- Oracle (1,710)
- Data Guard (53)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (168)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (73)
- Oracle Bug (8)
- Oracle RAC (56)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (29)
- Oracle备份恢复 (663)
- Oracle安装升级 (108)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (91)
- PostgreSQL (37)
- pdu工具 (7)
- PostgreSQL恢复 (13)
- SQL Server (34)
- SQL Server恢复 (14)
- TimesTen (7)
- 达梦数据库 (5)
- 达梦恢复 (3)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (51)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (34)
- MongoDB (1)
-
最近发表
- 恢复案例分享:win丢失数据文件恢复
- 记录19c ASM磁盘掉线触发ORA-59048故障处理
- aix平台Oracle数据库由于断电无法启动故障处理
- 断电引起ORA-600 3005故障处理
- 记录一次云存储资源到期之后引起的数据库恢复
- Firebird 损坏数据库数据抢救工具—firebirdext
- Oracle 19c 202610补丁(RUs+OJVM)-19.33
- 由于con$.con#值达到极限,触发数据库增加约束报ORA-600 12807错误
- MongoDB 离线提取工具—mongoext
- .bres勒索病毒加密数据库dmp文件可恢复
- ORA-600 krsm_mrp_rebuild_lf_list.lno_mismatch
- 记录一次0丢失的ORA-00354: 损坏重做日志块标头故障恢复
- 不当数据库恢复操作导致一个月数据丢失
- obet快速修复oracle 位图损坏块
- 不太常见的10.2.0.1的oracle redo损坏恢复
- obet forcecopy功能抢救硬件故障中的数据文件
- kcratr_nab_less_than_odr和system坏块故障处理
- 通过obet 恢复system坏块,打开数据库
- obet dbv功能完整说明
- 分享一例运行在aix上的sap系统数据库恢复过程
分类目录归档:Oracle 23ai
Oracle23ai新特性—sqlplus errordetails功能
在oracle 23ai中如果sqlplus执行遇到ORA-错误,会有对应的Help: https://docs.oracle.com/error-help/db/ORA-XXXXX提示,由于以前的使用习惯或者是提示本身意义不大等原因,还是希望在sqlplus中关闭这类提示.通过分析确认该提示是由errordetails来控制的
[oracle@xifenfei admin]$ sqlplus / as sysdba
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Mon Jul 29 22:44:23 2024
Version 23.5.0.24.07
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07
SQL> select * from xff.xifenfei;
select * from xff.xifenfei
*
ERROR at line 1:
ORA-00942: table or view "XFF"."XIFENFEI" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/
SQL> show errordetails <--该值默认为on
errordetails ON
设置errordetails off即可实现关闭Help: https://docs.oracle.com/error-help/db/ORA-XXXXX提示
SQL> set errordetails off
SQL> select * from xff.xifenfei;
select * from xff.xifenfei
*
ERROR at line 1:
ORA-00942: table or view "XFF"."XIFENFEI" does not exist
设置errordetails verbose可以实现更加详细的提示
SQL> set errordetails verbose
SQL> select * from xff.xifenfei;
select * from xff.xifenfei
*
ERROR at line 1:
ORA-00942: table or view "XFF"."XIFENFEI" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/
Cause: The specified table or view did not exist, or a synonym
pointed to a table or view that did not exist.
To find existing user tables and views, query the
ALL_TABLES and ALL_VIEWS data dictionary views. Certain
privileges may be required to access the table. If an
application returned this message, then the table that the
application tried to access did not exist in the database, or
the application did not have access to it.
Action: Check each of the following:
- The spelling of the table or view name is correct.
- The referenced table or view name does exist.
- The synonym points to an existing table or view.
If the table or view does exist, ensure that the correct access
privileges are granted to the database user requiring access
to the table. Otherwise, create the table.
Also, if you are attempting to access a table or view in another
schema, make sure that the correct schema is referenced and that
access to the object is granted.
Params: 1) object_name: The table or view name specified as
SCHEMA.OBJECT_NAME, if one is provided.
Otherwise, it is blank.
如果要实现sqlplus启动即屏蔽该提示,可以在glogin.sql文件($ORACLE_HOME/sqlplus/admin目录中)设置

[oracle@xifenfei admin]$ sqlplus / as sysdba
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Mon Jul 29 22:44:23 2024
Version 23.5.0.24.07
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07
SQL> show errordetails;
errordetails OFF
SQL> select * from xifenfei.xff;
select * from xifenfei.xff
*
ERROR at line 1:
ORA-00942: table or view "XIFENFEI"."XFF" does not exist
Oracle23ai新特性—SCHEMA级授权( grant select any table on schema)
创建测试用户a并在a中创建t1,t2两个测试表,另外创建用户b(只授权登录权限)
[oracle@xifenfei ~]$ ss
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Mon Jul 29 22:14:11 2024
Version 23.5.0.24.07
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 XIFENFEI READ WRITE NO
SQL> create user b identified by oracle;
User created.
SQL> grant create session to b;
Grant succeeded.
SQL> grant unlimited tablespace to a;
Grant succeeded.
SQL> create table a.t1 as select * from dba_objects;
Table created.
SQL> c/t1/t2;
1* create table a.t2 as select * from dba_objects
SQL> /
Table created.
SQL> select count(1) from a.t1;
COUNT(1)
----------
70638
SQL> select count(1) from a.t2;
COUNT(1)
----------
70639
SQL> create user b identified by oracle;
User created.
SQL> grant create session to b;
Grant succeeded.
直接使用b用户登录并尝试查询a用户数据,结果是无法查询数据报ora-00942(意料之中因为b现在无权限访问a.t1表)
SQL> conn b/oracle@127.0.0.1/xifenfei
Connected.
SQL> select count(1) from a.t1;
select count(1) from a.t1
*
ERROR at line 1:
ORA-00942: table or view "A"."T1" does not exist
Help: https://docs.oracle.com/error-help/db/ora-00942/
直接schema级别授权a用户下面的表给b用户查询权限
[oracle@xifenfei ~]$ ss
SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Mon Jul 29 22:07:14 2024
Version 23.5.0.24.07
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai Enterprise Edition Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.5.0.24.07
SQL> grant select any table on schema a to b;
Grant succeeded.
SQL> conn b/oracle@127.0.0.1/xifenfei
Connected.
SQL> select count(1) from a.t1;
COUNT(1)
----------
70638
SQL> select count(1) from a.t2;
COUNT(1)
----------
70639
在a用户中新增加表,在b用户中可以直接查询(无需再次授权)
SQL> conn a/oracle@127.0.0.1/xifenfei
Connected.
SQL> create table t3 as select * from tab;
Table created.
SQL> select count(1) from t3;
COUNT(1)
----------
3
SQL> conn b/oracle@127.0.0.1/xifenfei
Connected.
SQL> select count(1) from a.t3;
COUNT(1)
----------
3
Oracle 23ai中,Oracle引入了架构级(SCHEMA级别)授权。这允许你以更简单、更直观的方式对整个Schema进行授权。以前的版本中如果需要类似授权操作,需要对schema下面所有表进行grant select on user.table to user2形式授权工作量比较大而且user1中如果新增加表还需要额外授权
Oracle 23ai rm redo*.log恢复
在oracle 23ai的pdb中创建用户和表,并且插入数据(不提交),在另外一个会话中abort库,并从os层面rm删除掉redo文件,模拟数据库当前redo丢失,数据库恢复
创建用户和表并插入数据
[oracle@xifenfei ~]$ sqlplus sys/oracle@free as sysdba
SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 3 15:40:55 2024
Version 23.4.0.24.05
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05
SQL>
SQL>
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 FREEPDB1 READ WRITE NO
SQL> alter session set container=FREEPDB1;
Session altered.
SQL> create user xff identified by oracle;
User created.
SQL> grant dba to xff ;
Grant succeeded.
SQL> conn xff/oracle@FREEPDB1
Connected.
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> insert into t1 select *from t1;
75877 rows created.
SQL> /
151754 rows created.
另外一个会话中abort库
[oracle@xifenfei ~]$ sqlplus sys/oracle@free as sysdba SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 3 15:43:30 2024 Version 23.4.0.24.05 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to: Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.4.0.24.05 SQL> shutdown abort; ORACLE instance shut down. SQL> SQL> SQL> SQL> exit Disconnected from Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free Version 23.4.0.24.05
操作系统层面rm -rf 删除redo
[oracle@xifenfei ~]$ cd $ORACLE_BASE/oradata [oracle@xifenfei oradata]$ ls FREE [oracle@xifenfei oradata]$ cd FREE/ [oracle@192 FREE]$ ls control01.ctl FREEPDB1 redo01.log redo03.log system01.dbf undotbs2.dbf control02.ctl pdbseed redo02.log sysaux01.dbf temp01.dbf users01.dbf [oracle@xifenfei FREE]$ ls -ltr total 2441036 drwxr-x---. 2 oracle 1000 85 May 1 16:49 pdbseed -rw-r-----. 1 oracle oinstall 20979712 May 1 16:51 temp01.dbf drwxr-x---. 2 oracle 1000 104 May 1 16:55 FREEPDB1 -rw-r-----. 1 oracle oinstall 209715712 May 3 15:23 redo01.log -rw-r-----. 1 oracle oinstall 209715712 May 3 15:23 redo02.log -rw-r-----. 1 oracle oinstall 7348224 May 3 15:23 users01.dbf -rw-r-----. 1 oracle oinstall 1080041472 May 3 15:43 system01.dbf -rw-r-----. 1 oracle oinstall 692068352 May 3 15:43 sysaux01.dbf -rw-rw----. 1 oracle oinstall 52436992 May 3 15:43 undotbs2.dbf -rw-r-----. 1 oracle oinstall 209715712 May 3 15:43 redo03.log -rw-r-----. 1 oracle oinstall 18759680 May 3 15:43 control01.ctl -rw-r-----. 1 oracle oinstall 18759680 May 3 15:43 control02.ctl [oracle@xifenfei FREE]$ rm -rf redo0* [oracle@192 FREE]$ ls -l redo* ls: cannot access 'redo*': No such file or directory [oracle@xifenfei FREE]$
尝试启动数据库,报ora-00313,ora-00312,ora-27037等错误
[oracle@xifenfei ~]$ sqlplus sys/oracle@free as sysdba SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 3 15:44:17 2024 Version 23.4.0.24.05 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started. Total System Global Area 1603726344 bytes Fixed Size 5360648 bytes Variable Size 671088640 bytes Database Buffers 922746880 bytes Redo Buffers 4530176 bytes Database mounted. SQL> recover database; ORA-00283: recovery session canceled due to errors ORA-00313: open failed for members of log group 3 of thread 1 ORA-00312: online log 3 thread 1: '/opt/oracle/oradata/FREE/redo03.log' ORA-27037: unable to obtain file status Linux-x86_64 Error: 2: No such file or directory Additional information: 7
由于所有redo均被删除(包含当前redo),因此只能强制resetlogs方式打开库,尝试打开数据库
SQL> shutdown immediate;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> startup mount pfile='/tmp/pfile'
ORACLE instance started.
Total System Global Area 1603726344 bytes
Fixed Size 5360648 bytes
Variable Size 671088640 bytes
Database Buffers 922746880 bytes
Redo Buffers 4530176 bytes
Database mounted.
SQL> recover database until cancel;
ORA-00279: change 4244588 generated at 05/03/2024 15:23:22 needed for thread 1
ORA-00289: suggestion :
/opt/oracle/product/23ai/dbhomeFree/dbs/arch1_6_1167842962.dbf
ORA-00280: change 4244588 for thread 1 is in sequence #6
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/opt/oracle/oradata/FREE/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00603: ORACLE server session terminated by irrecoverable error
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [kcbzib_kcrsds_1], [], [], [], [],
[], [], [], [], [], [], []
Process ID: 5596
Session ID: 29 Serial number: 63204
数据库遇到常见的ORA-600 kcbzib_kcrsds_1错误,这类错误类似oracle在12c之前版本中的ORA-600 2662错误
ORA-600 kcbzib_kcrsds_1报错
存储故障,强制拉库报ORA-600 kcbzib_kcrsds_1处理
redo异常强制拉库报ORA-600 kcbzib_kcrsds_1修复
ORA-00603 ORA-01092 ORA-600 kcbzib_kcrsds_1
这种一般就是scn问题,通过修改数据库scn,数据库启动报ORA-16433错误
[oracle@xifenfei ~]$ sqlplus sys/oracle@free as sysdba SQL*Plus: Release 23.0.0.0.0 - Production on Fri May 3 15:49:00 2024 Version 23.4.0.24.05 Copyright (c) 1982, 2024, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile='/tmp/pfile'; ORACLE instance started. Total System Global Area 1603726344 bytes Fixed Size 5360648 bytes Variable Size 671088640 bytes Database Buffers 922746880 bytes Redo Buffers 4530176 bytes SQL> alter database mount; Database altered. SQL> recover database; ORA-00283: recovery session canceled due to errors ORA-16433: The database or pluggable database must be opened in read/write mode. SQL> shutdown abort; ORACLE instance shut down.
处理ctl,open数据库成功
SQL> startup nomount pfile='/tmp/pfile'
ORACLE instance started.
Total System Global Area 1603726344 bytes
Fixed Size 5360648 bytes
Variable Size 671088640 bytes
Database Buffers 922746880 bytes
Redo Buffers 4530176 bytes
SQL> CREATE CONTROLFILE REUSE DATABASE "FREE" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 1024
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 '/opt/oracle/oradata/FREE/redo01.log' SIZE 200M BLOCKSIZE 512,
9 GROUP 2 '/opt/oracle/oradata/FREE/redo02.log' SIZE 200M BLOCKSIZE 512,
10 GROUP 3 '/opt/oracle/oradata/FREE/redo03.log' SIZE 200M BLOCKSIZE 512
11 -- STANDBY LOGFILE
12 DATAFILE
13 '/opt/oracle/oradata/FREE/system01.dbf',
14 '/opt/oracle/oradata/FREE/pdbseed/system01.dbf',
15 '/opt/oracle/oradata/FREE/sysaux01.dbf',
16 '/opt/oracle/oradata/FREE/pdbseed/sysaux01.dbf',
17 '/opt/oracle/oradata/FREE/users01.dbf',
18 '/opt/oracle/oradata/FREE/pdbseed/undotbs01.dbf',
19 '/opt/oracle/oradata/FREE/FREEPDB1/system01.dbf',
20 '/opt/oracle/oradata/FREE/FREEPDB1/sysaux01.dbf',
21 '/opt/oracle/oradata/FREE/FREEPDB1/undotbs01.dbf',
22 '/opt/oracle/oradata/FREE/FREEPDB1/users01.dbf',
23 '/opt/oracle/oradata/FREE/undotbs2.dbf'
24 CHARACTER SET AL32UTF8
25 ;
Control file created.
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 FREEPDB1 READ WRITE NO
至此当前数据库redo故障模拟和恢复基本完成

加我微信(17813235971)
加我QQ(107644445)

