联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
在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故障模拟和恢复基本完成