win平台 UtilSession 失败: Prerequisite check “CheckActiveFilesAndExecutables” failed. 处理

发表于 2023 年 4 月 20 日 由 惜分飞

win平台打patch,提示UtilSession 失败: Prerequisite check “CheckActiveFilesAndExecutables” failed.

C:\Users\Administrator\Desktop\nmgOracle补丁\34474433>E:\app\Administrator\produ
ct\11.2.0\dbhome_1\OPatch\opatch apply ./
Oracle 临时补丁程序安装程序版本 11.2.0.3.40
版权所有 (c) 2023, Oracle Corporation。保留所有权利。


Oracle 主目录       ：E:\app\ADMINI~1\product\112~1.0\dbhome_1
主产品清单：C:\Program Files\Oracle\Inventory
   来自           ：
OPatch 版本    ：11.2.0.3.40
OUI 版本       ：11.2.0.4.0
日志文件位置：E:\app\ADMINI~1\product\112~1.0\dbhome_1\cfgtoollogs\opatch\opatch
2023-04-20_18-05-20下午_1.log

Verifying environment and performing prerequisite checks...
Prerequisite check "CheckActiveFilesAndExecutables" failed.
The details are:

Following active files/executables/libs are used by ORACLE_HOME :E:\app\ADMINI~1
\product\112~1.0\dbhome_1
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraxml11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orahasgen11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraocr11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraocrb11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraocrutl11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oran11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orantcp11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oranl11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orancrypt11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orapls11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orageneric11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraclient11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oracommon11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\rdbms\mesg\diaus.msb
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraplc11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraplp11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orannzsbb11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orazt11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraztkg11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraldapclnt11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oracore11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oracell11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraasmclnt11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orannzsbb11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\orazt11.dll
E:\app\ADMINI~1\product\112~1.0\dbhome_1\bin\oraztkg11.dll

UtilSession 失败: Prerequisite check "CheckActiveFilesAndExecutables" failed.
Log file location: E:\app\ADMINI~1\product\112~1.0\dbhome_1\cfgtoollogs\opatch\o
patch2023-04-20_18-05-20下午_1.log

OPatch failed with error code = 73

通过tasklist命令找出来占用的进程名称和进程号

E:\app\Administrator\product\11.2.0\dbhome_1\OPatch>tasklist /M ora*

映像名称                       PID 模块
========================= ======== ============================================
esfdaemon.exe                 1296 oracleclientsvc.dll, oracleengine.dll,
                                   oracledatasource.dll, oraclecore.dll,
                                   OraClient11.Dll, oracore11.dll,
                                   oranls11.dll, oraunls11.dll, orauts.dll,
                                   oracommon11.dll, orasnls11.dll,
                                   orageneric11.dll, oraxml11.dll, oran11.dll,
                                   oranl11.dll, orantcp11.dll, oranldap11.dll,
                                   orannzsbb11.dll, oraldapclnt11.dll,
                                   orancrypt11.dll, orazt11.dll, oranro11.dll,
                                   oranhost11.dll, orancds11.dll,
                                   orantns11.dll, oraztkg11.dll, orapls11.dll,
                                   oraslax11.dll, oravsn11.dll, oraplp11.dll,
                                   orahasgen11.dll, oraocr11.dll,
                                   oraocrb11.dll, oraasmclnt11.dll,
                                   oracell11.dll, oraocrutl11.dll,
                                   orasql11.dll, OraPlc11.Dll, oranbeq11.dll
plsqldev.exe                 29840 OraClient11.Dll, oracore11.dll,
                                   oranls11.dll, oraunls11.dll, orauts.dll,
                                   oracommon11.dll, orasnls11.dll,
                                   orageneric11.dll, oraxml11.dll, oran11.dll,
                                   oranl11.dll, orantcp11.dll, oranldap11.dll,
                                   orannzsbb11.dll, oraldapclnt11.dll,
                                   orancrypt11.dll, orazt11.dll, oranro11.dll,
                                   oranhost11.dll, orancds11.dll,
                                   orantns11.dll, oraztkg11.dll, orapls11.dll,
                                   oraslax11.dll, oravsn11.dll, oraplp11.dll,
                                   orahasgen11.dll, oraocr11.dll,
                                   oraocrb11.dll, oraasmclnt11.dll,
                                   oracell11.dll, oraocrutl11.dll,
                                   orasql11.dll, OraPlc11.Dll

可以在任务管理器中直接把相关进程kill掉，也可以通过taskkill /F /im 进程名方式杀掉进程,然后再次检查占用情况,无任何进程占用的情况下,就可以直接打patch

E:\app\Administrator\product\11.2.0\dbhome_1\OPatch>tasklist /M ora*
信息: 没有运行的任务匹配指定标准。
发表在 Oracle安装升级 | 标签为 , | 评论关闭

Oracle Recovery Tools快速恢复断电引起的无法正常启动数据库(ORA-01555,MISSING000等问题）

发表于 2023 年 4 月 17 日 由 惜分飞

由于异常断电,数据库启动报错ORA-01113和ORA-01110，ORA-00322和ORA-00312以及ORA-00314和ORA-00312错误

Mon Apr 17 09:35:04 2023
ALTER DATABASE OPEN
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_10192.trc:
ORA-01113: 文件 1 需要介质恢复
ORA-01110: 数据文件 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\SYSTEM01.DBF'
ORA-1113 signalled during: ALTER DATABASE OPEN...
Mon Apr 17 09:35:05 2023
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_m000_9788.trc:
ORA-00322: 日志 6 (用于线程 1) 不是最新副本
ORA-00312: 联机日志 6 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO06.LOG'
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_m000_9788.trc:
ORA-00314: 日志 7 (用于线程 1) 要求的 sequence# 15257 与 15248 不匹配
ORA-00312: 联机日志 7 线程 1: 'D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO07.LOG'

客户那边技术尝试强制拉库之后,报ORA-00704、ORA-01555等错误,数据库启动失败

Mon Apr 17 10:10:49 2023
alter database open resetlogs
RESETLOGS is being done without consistancy checks. This may result
in a corrupted database. The database should be recreated.
RESETLOGS after incomplete recovery UNTIL CHANGE 3159276957
Resetting resetlogs activation ID 1558785994 (0x5ce92fca)
Mon Apr 17 10:11:11 2023
Setting recovery target incarnation to 3
Mon Apr 17 10:11:11 2023
Assigning activation ID 1662170916 (0x6312b724)
Thread 1 opened at log sequence 1
  Current log# 1 seq# 1 mem# 0: D:\APP\ADMINISTRATOR\ORADATA\ORCL\REDO01.LOG
Successful open of redo thread 1
Mon Apr 17 10:11:11 2023
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Mon Apr 17 10:11:12 2023
SMON: enabling cache recovery
ORA-01555 caused by SQL statement below (SQL ID: 4krwuz0ctqxdt, SCN: 0x0000.bc4ebda4):
select ctime, mtime, stime from obj$ where obj# = :1
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_9324.trc:
ORA-00704: 引导程序进程失败
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01555: 快照过旧: 回退段号 10 (名称为 "_SYSSMU10_3905543278$") 过小
Errors in file D:\APP\ADMINISTRATOR\diag\rdbms\orcl\orcl\trace\orcl_ora_9324.trc:
ORA-00704: 引导程序进程失败
ORA-00704: 引导程序进程失败
ORA-00604: 递归 SQL 级别 1 出现错误
ORA-01555: 快照过旧: 回退段号 10 (名称为 "_SYSSMU10_3905543278$") 过小
Error 704 happened during db open, shutting down database
USER (ospid: 9324): terminating the instance due to error 704
Instance terminated by USER, pid = 9324
ORA-1092 signalled during: alter database open resetlogs...

然后尝试一系列操作,比如重建ctl,再次拉库等等,均未绕过该错误.对于ORA-704 ORA-01555错误,使用开发的小工具Oracle Recovery Tools可以一键式解决,参考处理过的类似文章:Oracle Recovery Tools恢复—ORA-00704 ORA-01555故障,不过最近发布的版本中增加了直接修改内存SCN功能,可以更加快速的解决给问题
20230417161757

修复之后,数据库open成功

SQL> alter database open;

数据库已更改。

SQL> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup pfile='d:/pfile.txt' mount;
ORACLE 例程已经启动。

Total System Global Area 1.7170E+10 bytes
Fixed Size                  2262088 bytes
Variable Size            1.3153E+10 bytes
Database Buffers         3959422976 bytes
Redo Buffers               54947840 bytes
数据库装载完毕。
SQL> select name from v$datafile where name like '%MISSING%';

NAME
--------------------------------------------------------------------------------
D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00012
D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00013

已选择2行。

很不幸技术在尝试重建ctl的恢复的过程中遗漏了两个数据库文件,通过Oracle Recovery Tools再次进行恢复
20230417230141

在sqlplus中恢复这两个文件成功

SQL> alter database rename file 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00012' 
   2 to 'D:\oradata\XIFENFEI_04.DBF';

数据库已更改。

SQL> alter database rename file 'D:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\MISSING00013' 
   2 to 'D:\oradata\XIFENFEI_5.DBF';

数据库已更改。


SQL> alter database datafile 12,13 online;

数据库已更改。

SQL>
SQL>
SQL> alter database open ;
alter database open 
*
第 1 行出现错误:
ORA-01113: 文件 12 需要介质恢复
ORA-01110: 数据文件 12: 'D:\ORADATA\XIFENFEI_04.DBF'


SQL> recover datafile 12;
完成介质恢复。
SQL> recover datafile 13;
完成介质恢复。
SQL> alter database open;

数据库已更改。

至此数据库基本上恢复完成,剩余工作就是逻辑导出导入.
软件下载:OraRecovery工具
使用说明:Oracle_Recovery_Tools说明

发表在 Oracle, 小工具 | 标签为 , | 评论关闭

login trigger导致ORA-16191问题

发表于 2023 年 4 月 16 日 由 惜分飞

主库突然报ORA-16191错，主库无法传输日志到备库

at Apr 08 19:56:26 2023
Thread 1 advanced to log sequence 32548 (LGWR switch)
  Current log# 1 seq# 32548 mem# 0: /u01/app/oracle/oradata/orcl/redo01_a.rdo
  Current log# 1 seq# 32548 mem# 1: /oracle/fast_recovery_area/orcl/redo01_b.rdo
Sat Apr 08 19:56:26 2023
Archived Log entry 61796 added for thread 1 sequence 32547 ID 0x5fc1b26f dest 1:
Sat Apr 08 19:56:27 2023
LNS: Standby redo logfile selected for thread 1 sequence 32548 for destination LOG_ARCHIVE_DEST_2
Sat Apr 08 20:21:29 2023
NSA:  Error 3135 archiving log 1 to 'orcldg'
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_nsa2_26205.trc:
ORA-03135: connection lost contact
LNS: Failed to archive log 1 thread 1 sequence 32548 (3135)
Sat Apr 08 20:26:26 2023
Thread 1 advanced to log sequence 32549 (LGWR switch)
  Current log# 2 seq# 32549 mem# 0: /u01/app/oracle/oradata/orcl/redo02_a.rdo
  Current log# 2 seq# 32549 mem# 1: /oracle/fast_recovery_area/orcl/redo02_b.rdo
Sat Apr 08 20:26:26 2023
Archived Log entry 61798 added for thread 1 sequence 32548 ID 0x5fc1b26f dest 1:
Sat Apr 08 20:26:55 2023
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
      returning error ORA-16191
------------------------------------------------------------
PING[ARC2]: Heartbeat failed to connect to standby 'orcldg'. Error is 16191.
Sat Apr 08 20:27:57 2023
Error 1017 received logging on to the standby
------------------------------------------------------------
Check that the primary and standby are using a password file
and remote_login_passwordfile is set to SHARED or EXCLUSIVE,
and that the SYS password is same in the password files.
      returning error ORA-16191
------------------------------------------------------------
PING[ARC2]: Heartbeat failed to connect to standby 'orcldg'. Error is 16191.

备库报错

Sat Apr 08 19:29:09 2023
Media Recovery Waiting for thread 1 sequence 32548 (in transit)
Recovery of Online Redo Log: Thread 1 Group 12 Seq 32548 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/orcl/std_redo12.log
Sat Apr 08 21:52:19 2023
RFS[9]: Possible network disconnect with primary database

检查主备库remote_login_passwordfile参数,都是为EXCLUSIVE,确认没有问题

--备库
SQL> show parameter pass;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ ONLY WITH APPLY

--主库
SQL> select database_role from v$database;

DATABASE_ROLE
----------------
PRIMARY

SQL> show parameter pass;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

检查主备库密码文件,证明主备库密码文件一致,也不是密码文件引起不同步

--主库
[oracle@oracle1 trace]$ cd $ORACLE_HOME/dbs
[oracle@oracle1 dbs]$ echo $ORACLE_SID
orcl
[oracle@oracle1 dbs]$ ls -ltr orapw*
-rw-r-----. 1 oracle oinstall 1536 Sep 16  2022 orapworcl
[oracle@oracle1 dbs]$ md5sum orapworcl 
d27c234b5131b4d2e13b1eeb4388f0eb  orapworcl

--备库
[oracle@oracle2 trace]$ cd $ORACLE_HOME/dbs
[oracle@oracle2 dbs]$ echo $ORACLE_SID
orcl
[oracle@oracle2 dbs]$ ls -l orapw*
-rw-r----- 1 oracle oinstall 1536 Sep 16  2022 orapworcl
[oracle@oracle2 dbs]$ md5sum orapworcl 
d27c234b5131b4d2e13b1eeb4388f0eb  orapworcl

查看数据库有logon触发器

SQL> select owner,trigger_name from dba_triggers where triggering_event LIKE '%LOGON%';

OWNER                          TRIGGER_NAME
------------------------------ ------------------------------
DBTOOLS                        TRIGGER_LOGINLIMIT

根据Error 1017 / ORA-16191 In Standby Alertlog (Doc ID 2225190.1)中描述logon触发器可能导致dg传输日志失败.
trigger-ORA-16191

发表在 Data Guard | 标签为 , | 评论关闭