19c数据库current redo文件损坏的恢复

数据库平台：Oracle Linux 8.7

数据库版本：19.3.0.0.0

数据库故障场景：current redo文件损坏的修复

[oracle@dkf ~]$ 
[oracle@dkf ~]$ cat /etc/redhat-release 
Red Hat Enterprise Linux release 8.7 (Ootpa)
[oracle@dkf ~]$ 

登录查看数据库信息:

[oracle@dkf ~]$ dba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Mar 13 17:34:37 2023
Version 19.3.0.0.0

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

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

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDKF01                         READ WRITE NO
         4 PDBAPEX                        READ WRITE NO
SQL> 
SQL> 
SQL> 
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/arch
Oldest online log sequence     340
Next log sequence to archive   342
Current log sequence           342

当前redo日志文件情况：

SQL> 

SQL> select group#,SEQUENCE#,BYTES,BLOCKSIZE,MEMBERS,ARCHIVED,STATUS from v$log;

    GROUP#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARCHIVED  STATUS
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------
         1        340  209715200        512          1 YES       INACTIVE
         2        341  209715200        512          1 YES       INACTIVE
         3        342  209715200        512          1 NO        CURRENT

SQL> 
SQL> 
SQL> 
SQL> 

创建表及插入数据：

SQL> create table dkf(id number(5));

Table created.

SQL> insert into dkf values(1);

1 row created.

SQL> insert into dkf values(2);

1 row created.

SQL> insert into dkf values(3);

1 row created.

SQL> insert into dkf values(4);

1 row created.

SQL> commit;

Commit complete.

查看日志号对应的日志文件：

SQL> select GROUP#,STATUS,TYPE,MEMBER from v$logfile;

    GROUP# STATUS                TYPE                  MEMBER
---------- --------------------- --------------------- ------------------------------------------------------------
         3                       ONLINE                +DATA/DKF006/ONLINELOG/group_3.264.1101088599
         2                       ONLINE                +DATA/DKF006/ONLINELOG/group_2.263.1101088599
         1                       ONLINE                +DATA/DKF006/ONLINELOG/group_1.262.1101088597

3 rows selected.

登录ASM删除redo文件：

ASMCMD> cp group_3.264.1101088599 /tmp/
copying +data/DKF006/onlinelog/group_3.264.1101088599 -> /tmp//group_3.264.1101088599
ASMCMD> ls
group_1.262.1101088597
group_11.276.1106279775
group_12.277.1106279779
group_13.278.1106279785
group_14.279.1106279789
group_2.263.1101088599
group_3.264.1101088599
stdby11.log
stdby12.log
stdby13.log
stdby14.log
ASMCMD> rm group_3.264.1101088599
ASMCMD> 

尝试启动数据库：

SQL> startup
ORACLE instance started.

Total System Global Area 1237318216 bytes
Fixed Size                  9144904 bytes
Variable Size             620756992 bytes
Database Buffers          394264576 bytes
Redo Buffers                3436544 bytes
In-Memory Area            209715200 bytes
Database mounted.
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '+DATA/DKF006/ONLINELOG/group_3.264.1101088599'
ORA-17503: ksfdopn:2 Failed to open file +DATA/DKF006/ONLINELOG/group_3.264.1101088599
ORA-15012: ASM file '+DATA/DKF006/ONLINELOG/group_3.264.1101088599' does not exist

启动报错，查看报错日志文件对应的日志状态，状态是CURRENT，且未归档；

SQL> select group#,SEQUENCE#,BYTES,BLOCKSIZE,MEMBERS,ARCHIVED,STATUS from v$log;

    GROUP#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARCHIVED  STATUS
---------- ---------- ---------- ---------- ---------- --------- ------------------------------------------------
         1        340  209715200        512          1 YES       INACTIVE
         3        342  209715200        512          1 NO        CURRENT
         2        341  209715200        512          1 YES       INACTIVE

SQL> 

尝试清除该日志组：

SQL> alter database clear unarchived logfile group 3;
alter database clear unarchived logfile group 3
*
ERROR at line 1:
ORA-01624: log 3 needed for crash recovery of instance dkf006 (thread 1)
ORA-00312: online log 3 thread 1: '+DATA/DKF006/ONLINELOG/group_3.264.1101088599'

尝试恢复数据库：

SQL> recover database until cancel;
ORA-00279: change 23359913 generated at 03/13/2023 13:16:27 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_342_1101088597.arch
ORA-00280: change 23359913 for thread 1 is in sequence #342


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00308: cannot open archived log '/u01/app/oracle/arch/1_342_1101088597.arch'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7


ORA-00308: cannot open archived log '/u01/app/oracle/arch/1_342_1101088597.arch'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 7


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: '+DATA/DKF006/DATAFILE/system.257.1101088487'

尝试强制打开数据库:

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '+DATA/DKF006/DATAFILE/system.257.1101088487'

SQL> 

由于日志缺少，导致数据出现丢失，数据不一致；
设置隐含参数_allow_resetlogs_corruption为true

alter system set "_allow_resetlogs_corruption"=true scope=spfile;

启动数据库:

SQL> startup mount
ORACLE instance started.

Total System Global Area 1237318216 bytes
Fixed Size                  9144904 bytes
Variable Size             620756992 bytes
Database Buffers          394264576 bytes
Redo Buffers                3436544 bytes
In-Memory Area            209715200 bytes
Database mounted.
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: 1132428
Session ID: 397 Serial number: 1244

SQL> 

数据库报错，查看后台日志；

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], [], [], [], [], [], [], [], [], [], [], []
Incident details in: /u01/app/oracle/diag/rdbms/dkf006/dkf006/incident/incdir_98466/dkf006_ora_1132428_i98466.trc

由于resetlogs之后，数据库内部scn和数据文件出现了不一致的情况，需要对数据库scn强制手工进行推进，
通过参数来调整 alter session set events ‘10015 trace name adjust_scn level 1’;
推进之后，进行数据库的打开，数据库正常open；

SQL> startup mount
ORACLE instance started.

Total System Global Area 1237318216 bytes
Fixed Size                  9144904 bytes
Variable Size             620756992 bytes
Database Buffers          394264576 bytes
Redo Buffers                3436544 bytes
In-Memory Area            209715200 bytes
Database mounted.
SQL> alter session set events '10015 trace name adjust_scn level 1';

Session altered.

SQL> alter database open;

Database altered.

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDKF01                         READ WRITE NO
         4 PDBAPEX                        READ WRITE NO
SQL> 

检查数据库状态:

SQL> select open_mode from v$database;

OPEN_MODE
------------------------------------------------------------
READ WRITE

SQL> 

检查数据库后台日志,还是有报错，可忽略，建议此时对数据库做全库的逻辑导出，并重建下当前数据库；

2023-03-13T18:05:00.639705-04:00
Pluggable database PDKF01 opened read write
2023-03-13T18:05:00.832238-04:00
Pluggable database PDBAPEX opened read write
2023-03-13T18:05:03.030932-04:00

ORA-00600: internal error code, arguments: [600], [ORA-00600: internal error code, arguments: [4506], [5], [], [], [], [], [], [], [], [], [], []

检查表的情况，毫无疑问，表及记录已全部丢失；

SQL> 
SQL> select * from dkf;
select * from dkf
              *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL>     

建议数据库打开后对数据库做全库的逻辑导出，并重建下当前数据库；

==========================================================