【备份恢复】Oracle 19.19 数据库异机恢复八大避坑指南分享

数据库运维之道 2024-03-15

1477

本期将为大家分享“Oracle 19.19 数据库异机恢复八大避坑指南”。

关键词：ORA-00349、ORA-00392、ORA 600 [ksvworkmsgalloc: bad reaper]、ORA-19751、ORA-01580、ORA-06512、ORA-39077、ORA-01157

由于业务数据被人为误删除，客户需要将RAC集群的备份文件恢复到非RAC的环境，然后再将业务表的数据导入生产环境。虽然Oracle 19C有单表恢复功能，但是业务表所属表空间有12T，整个数据库共15T左右，因此基本是等于全库恢复。源库是两节点的RAC集群，小版本号为19.19.0.0.0，操作系统OEL7.9。目标库是单台物理机的数据库，小版本号为19.3.0.0.0，操作系统为RHEL7.4。本人在进行数据库异机恢复过程中，遇到多个错误，为了避免下次继续踩坑，梳理出相应的解决方案。

现象描述：源库是两节点的集群，因此数据文件存储在ASM磁盘组中，而目标库存储在非ASM磁盘。基于时间点执行restore和recover命令恢复数据文件之后，执行alter database open resetlogs出现ORA-00349错误，即获取redo日志文件的块大小失败。

RMAN> alter database open resetlogs;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 03/13/2024 21:50:06
ORA-00349: failure obtaining block size for '+YWZD/YWZD/ONLINELOG/group1.log'

ORA-00349错误处置方法：根据文档356191.1的建议，执行resetlogs前，需要将redo日志的路径从ASM盘调整为文件系统。我们可以通过SQL命令或重建控制文件来重新指定redo日志的位置。通过SQL语句可以看到有两个日志文件状态为CLEARING_CURRENT，剩余日志文件的状态为CLEARING。

命令格式
SQL> alter database rename file '<old asm path>' to '<new path>'
查看redo日志位置和状态信息
SQL> col member format a60
SQL> select v1.thread#, v1.group#, member, sequence#, first_change#, archived, v1.status from v$log v1, v$logfile v2 where v1.group#=v2.group#;
   THREAD#     GROUP# MEMBER                                SEQUENCE# FIRST_CHANGE# ARC STATUS
---------- ---------- ------------------------------------------------------------ ---------- ------------- --- ----------------
     1        1 +YWZD/YWZD/ONLINELOG/group1.log                    0     2.8433E+10 NO    CLEARING_CURRENT
     1        2 +YWZD/YWZD/ONLINELOG/group2.log                    0     2.8432E+10 YES CLEARING
修改redo日志位置
SQL> alter database rename file '+YWZD/YWZD/ONLINELOG/group2.log' to '/u01/app/oracle/oradata/YWZD/group2.log';
查看新的位置信息 
   THREAD#     GROUP# MEMBER                                SEQUENCE# FIRST_CHANGE# ARC STATUS
---------- ---------- ------------------------------------------------------------ ---------- ------------- --- ----------------
     1        1 /u01/app/oracle/oradata/YWZD/group1.log                0     2.8433E+10 NO    CLEARING_CURRENT
     1        2 u01/app/oracle/oradata/YWZD/group2.log                0     2.8432E+10 YES CLEARING

现象描述：源库的数据文件存储在ASM磁盘组中，而目标库的存储在非ASM磁盘。基于时间点执行restore和recover命令恢复数据文件之后，执行alter database open resetlogs出现ORA-00392错误。

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00392: log 1 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/YWZD/group1.log'


RMAN> alter database open resetlogs;
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of sql statement command at 03/14/2024 10:43:08
ORA-00392: log 1 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 1 thread 1: '/u01/app/oracle/oradata/YWZD/group1.log'

ORA-00392错误处置方法：根据文档1352133.1的建议，执行resetlogs前，先检查redo日志的状态。通过SQL语句可以看到有两个日志文件状态为CLEARING_CURRENT，剩余日志文件的状态为CLEARING。执行alter database clear unarchived logfile group N 命令来手动清除两个未归档的日志组。

SQL> select GROUP#,THREAD#,SEQUENCE#,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE# from v$log order by first_change# ;
    GROUP#    THREAD#  SEQUENCE#    MEMBERS ARC STATUS         FIRST_CHANGE#
---------- ---------- ---------- ---------- --- ---------------- -------------
     5        1           0      1 YES CLEARING        2.8433E+10
     6        1           0      1 YES CLEARING        2.8433E+10
     1        1           0      1 NO    CLEARING_CURRENT    2.8433E+10
    10        2           0      1 NO    CLEARING_CURRENT    2.8433E+10


SQL> alter database clear unarchived logfile group 1 ;
SQL> alter database clear unarchived logfile group 10 ;
SQL> alter database open resetlogs;

现象描述：基于上述两个场景的处置，执行resetlogs会出现新的报错。这次出现ORA-00600: internal error code, arguments: [ksvworkmsgalloc: bad reaper]错误，即ORA 600内部错误。

SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [ksvworkmsgalloc: bad reaper],[0x080040002], [], [], [], [], [], [], [], [], [], []

ORA 600错误处置方法：根据文档2728106.1的建议，由于数据文件被存放到非ASM路径，清理日志后也依旧报错。通过设置db_create_online_log_dest_1参数，指定redo到非asm路径，然后重启数据库使参数生效。

SQL> alter system set db_create_online_log_dest_1='新的路径' scope=both sid='*';
SQL> alter database clear unarchived logfile group <group#>;

现象描述：基于上述三个场景的处理，再次尝试打开数据库。这次遇到ORA-19751错误，即无法找到对应的目录创建块跟踪日志文件。

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-19751: could not create the change tracking file
ORA-19750: change tracking file:
'+YWZD/YWZD/CHANGETRACKING/ctf.9470.1099506341'
ORA-17502: ksfdcre:1 Failed to create file
+YWZD/YWZD/CHANGETRACKING/ctf.9470.1099506341
ORA-17501: logical block size 4294967295 is invalid
ORA-29701: unable to connect to Cluster Synchronization Service
ORA-17503: ksfdopn:2 Failed to open file
+YWZD/YWZD/CHANGETRACKING/ctf.9470.1099506341
ORA-15001: diskgroup "YWZD" does not exist or is not mounted
ORA-15374: invalid cluster configuration

ORA-19751错误处置方法：根据文档832496.1的建议，数据库打开过程会校验块跟踪文件是否存在。如果不存在则创建，并且会按原先的路径创建块跟踪文件。但是由于目标数据库没有对应的ASM磁盘组，从而导致SQL> alter database enable block change tracking using file '+';命令报错。本案例是直接把块跟踪属性关闭，然后正常打开数据库。

方法一：禁用块跟踪，alter database disable block change tracking;
方法二：指定新的存储路径，alter database enable block change tracking using file '+<DGNAME>';
SQL> alter database disable block change tracking;
Database altered.
SQL> alter database open;
Database altered.

现象描述：检查数据库alert日志，会看到控制文件快照出现ORA-01580错误。

2024-03-14T11:24:18.622867+08:00
********************  WARNING **************************
The errors during server control file autobackup are not fatal, as it is attempted after sucessful completion of the command. However, it is recomended to take an RMAN
control file backup as soon as possible because the autobackup failed with the following error:
ORA-01580: error creating control backup file +YWZD/snap/YWZD2.ctl
ORA-17502: ksfdcre:3 Failed to create file +YWZD/snap/YWZD2.ctl
ORA-15001: diskgroup "YWZD" does not exist or is not mounted
ORA-15374: invalid cluster configuration


********************  END OF WARNING *******************

ORA-01580错误处置方法：根据文档2614680.1的建议，数据库rman参数SNAPSHOT CONTROLFILE NAME的路径有问题，需要重新指定到正确的路径下。

1、检查rman参数，执行show all命令
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+YWZD/snap/YWZD2.ctl';


2、重新调整参数
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/oradata/YWZD/snap_YWZD';
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of configure command at 03/14/2024 11:26:22
RMAN-06492: control file AUTOBACKUP format "/u01/app/oracle/oradata/YWZD/snap_YWZD" must specify a "%F" format specifier


RMAN> CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/oradata/YWZD/snap_YWZD%F';
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '+YWZD/snap/YWZD%F';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u01/app/oracle/oradata/YWZD/snap_YWZD%F';
new RMAN configuration parameters are successfully stored


RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/oradata/YWZD/YWZD2.ctl';
old RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '+YWZD/snap/YWZD2.ctl';
new RMAN configuration parameters:
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/oradata/YWZD/YWZD2.ctl';
new RMAN configuration parameters are successfully stored

现象描述：根据以上处置，数据库已经恢复成功了。接下来是将业务表导出来，但是expdp导出过程遇到ORA-06512和ORA-39077错误。

查看表的数据量
select count(*) from YWZD.TEST01;
数据泵导出命令
$expdp system/密码 directory=dump_dir dumpfile=TEST01.dmp logfile=TEST01.log tables=YWZD.TEST01


Export: Release 19.0.0.0.0 - Production on Thu Mar 14 11:24:25 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-31626: job does not exist
ORA-31638: cannot attach to job SYS_EXPORT_TABLE_01 for user SYSTEM
ORA-06512: at "SYS.KUPV$FT", line 1142
ORA-06512: at "SYS.KUPV$FT", line 1744
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPV$FT_INT", line 498
ORA-39077: unable to subscribe agent KUPC$A_1_112444208127000 to queue "KUPC$C_1_20240314112434_0"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPC$QUE_INT", line 294
ORA-01001: invalid cursor
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 9562
ORA-06512: at "SYS.DBMS_PRVTAQIS", line 1420
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 10156
ORA-06512: at "SYS.DBMS_AQADM_SYS", line 9525
ORA-06512: at "SYS.DBMS_AQADM", line 881
ORA-06512: at "SYS.KUPC$QUE_INT", line 267
ORA-06512: at "SYS.KUPC$QUE_INT", line 1360
ORA-06512: at line 1
ORA-06512: at "SYS.KUPC$QUEUE_INT", line 65
ORA-06512: at "SYS.KUPV$FT_INT", line 465
ORA-06512: at "SYS.KUPV$FT", line 1664
ORA-06512: at "SYS.KUPV$FT", line 1103

ORA-06512错误处置方法：根据文档2653853.1的建议，重建datapump工具。

@$ORACLE_HOME/rdbms/admin/catdph.sql
@$ORACLE_HOME/rdbms/admin/prvtdtde.plb
@$ORACLE_HOME/rdbms/admin/catdpb.sql
@$ORACLE_HOME/rdbms/admin/dbmspump.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql

现象描述：根据以上处置，重新进行数据导出操作，这次还是报ORA-06512错误，但是报错的对象少了。同时从alert日志文件看到ORA 600错误。

UDE-31623: operation generated ORACLE error 31623
ORA-31623: a job is not attached to this session via the specified handle
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4747
ORA-06512: at "SYS.KUPV$FT_INT", line 2144
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.KUPV$FT_INT", line 2081
ORA-06512: at "SYS.DBMS_DATAPUMP", line 2263
ORA-06512: at "SYS.DBMS_DATAPUMP", line 4496
ORA-06512: at "SYS.DBMS_DATAPUMP", line 6127
ORA-06512: at line 1


2024-03-14T11:47:59.297967+08:00
Errors in file u01/app/oracle19/diag/rdbms/YWZD/YWZD/trace/YWZD_ora_24259.trc  (incident=9667):
ORA-00600: internal error code, arguments: [kwqintExeStmt: bind string failure], [1036], [ORA-01036: illegal variable name/number
], [], [], [], [], [], [], [], [], []
Incident details in: u01/app/oracle19/diag/rdbms/YWZD/YWZD/incident/incdir_9667/YWZD_ora_24259_i9667.trc

ORA-06512错误处置方法：根据文档2799499.1的建议，检查源与目标数据库补丁信息“Database Release Update”是否一致。即数据库的RU是19.19，但是binary文件版本是19.3，因此将对目标库进行补丁修复。

查看源与目标的RU描述信息
set linesize 1000
col comments for a80
select action,namespace,comments from DBA_REGISTRY_HISTORY ;
ACTION          NAMESPACE      COMMENTS
----------- ------------- ---------------------------------------------------------------------
BOOTSTRAP      DATAPATCH      RDBMS_19.19.0.0.0DBRU_LINUX.X64_230321.1
RU_APPLY      SERVER      Patch applied on 19.11.0.0.0: Release_Update - 210413004009
RU_APPLY      SERVER      Patch applied from 19.11.0.0.0 to 19.19.0.0.0: Release_Update - 230322020406
RU_APPLY      SERVER      Patch applied on 19.19.0.0.0: Release_Update - 230322020406


select install_id,patch_type,status,description,source_version,target_version from dba_registry_sqlpatch ;
INSTALL_ID PATCH_TYPE STATUS         DESCRIPTION                      SOURCE_VERSION  TARGET_VERSION
---------- ---------- ------ ------------------------------------------------------- --------------- ---------------
     1 RU          SUCCESS     Database Release Update : 19.11.0.0.210420 (32545013)     19.1.0.0.0      19.11.0.0.0
     3 RU          SUCCESS     Database Release Update : 19.19.0.0.230418 (35042068)     19.11.0.0.0     19.19.0.0.0


将补丁集上传到服务器，并进行解压，然后执行opatch apply命令修复。
Set your current directory to the directory where the patch is located and then run the OPatch utility by entering the following commands:
cd <UNZIPPED_PATCH_LOCATION>/35037840/35042068
opatch apply

现象描述：修复补丁过程中，遇到ORA-01157和ORA-01110错误。根据错误信息可以知道是读取临时文件异常。

2024-03-14T14:54:15.623327+08:00
Unable to obtain current patch information due to error: 20001, ORA-20001: Latest xml inventory is not loaded into table
ORA-06512: at "SYS.DBMS_QOPATCH", line 2327
ORA-06512: at "SYS.DBMS_QOPATCH", line 854
ORA-06512: at "SYS.DBMS_QOPATCH", line 937
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.DBMS_QOPATCH", line 932
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-01157: cannot identify/lock data file 2002 - see DBWR trace file
ORA-01110: data file 2002: '+YWZD/YWZD/TEMPFILE/temp.265'
ORA-06512: at "SYS.DBMS_QOPATCH", line 919
ORA-06512: at "SYS.DBMS_QOPATCH", line 2286
ORA-06512: at "SYS.DBMS_QOPATCH", line 817
ORA-06512: at "SYS.DBMS_QOPATCH", line 2309

ORA-01157错误处置方法：根据文档2929591.1的建议，检查临时表空间对应的数据文件信息。临时表空间TEMP包含三个数据文件，其中两个数据文件存储信息不正常。可以通过手动删除进行处理。

SQL> select file_name from dba_temp_files;
select file_name from dba_temp_files
                      *
ERROR at line 1:
ORA-01157: cannot identify/lock data file 2002 - see DBWR trace file
ORA-01110: data file 2002: '+YWZD/YWZD/TEMPFILE/temp.265'


select file#,ts#,name from v$tempfile
SQL> col name for a80
     FILE#      TS# NAME
---------- ---------- --------------------------------------------------------------------------------
     1        3 /u01/app/oracle/oradata/YWZD/YWZD/datafile/o1_mf_temp_lz4tj527_.tmp
     2        3 +YWZD/YWZD/TEMPFILE/temp.265
     3        3 +YWZD/YWZD/TEMPFILE/temp.268


SQL> alter database tempfile 2 drop;
Database altered.
SQL> alter database tempfile 3 drop;
Database altered.

最后执行expdp命令，成功将业务表导出来！

虽然整个恢复过程困难重重，总共遇到八个错误，但是通过逐个排查与处理，最终得到解决收获颇多。如果可以再重新做一次数据恢复，我会先把目标数据库的补丁集打上，这样就可以少走很多弯路。

'ORA-00349: failure obtaining block size' On 'open resetlogs' During Incomplete Recovery (Doc ID 356191.1)
ALTER DATABASE OPEN RESETLOGS fails with ORA-00392 (Doc ID 1352133.1)
Open Resetlogs Fail with ORA-00600[ksvworkmsgalloc: bad reaper] (Doc ID 2728106.1)
OPEN RESETLOGS FAILS ORA-19751, ORA-15046, ASM AND BLOCK CHANGE TRACKING FILE (Doc ID 832496.1)
RMAN Resync Fails with RMAN-03009, ORA-01580 (Doc ID 2614680.1)
Expdp/Impdp Fails Due To Errors -ORA-39077,ORA-31638,ORA-24000,ORA-00972 (Doc ID 2653853.1)
ORA-31626 ORA-6512 ORA-39077 ORA-1001 Errors on Data Pump Export (EXPDP) (Doc ID 2799499.1)
ORA-00600 [kwqintExeStmt: bind string failure], [1036], [ORA-01036: illegal variable name/number] (Doc ID 2848323.1)

以上就是本期关于“Oracle 19.19 数据库异机恢复八大避坑指南”。希望能给大家带来帮助！

欢迎关注“数据库运维之道”公众号，一起学习数据库技术。

欢迎加我的微信，拉你进数据库微信群，请备注”进群“！