windows oracle rac 19.3 打补丁 升级到19.26 (坑巨多)

Oracle RAC环境补丁安装说明-滚动升级

一、替换OPatch
     
	 两个节点的$ORACLE_HOME 和 $GRID_HOME都替换，这里替换的是：
	 c:\WINDOWS.X64_193000_db_home\OPatch
	 C:\WINDOWS.X64_193000_grid_home\OPatch
	 
二、集群和database 打补丁 

1、设置GI环境变量
set ORACLE_HOME=C:\WINDOWS.X64_193000_grid_home

2、停止Oracle Cluster Registry （OCR）相关资源、Oracle High Availability Services守护进程（OHASD）、所有Oracle服务，并在本地节点上解锁Grid home
   (会打补丁做准备，自动停掉所有服务)
  --以下二选一，不知道的一个一个尝试，我的执行1报错，2可以
If this is not an Oracle Restart home, then run this command:

%ORACLE_HOME%\crs\install\rootcrs.bat -prepatch

If this is an Oracle Restart home, then run this command:

%ORACLE_HOME%\crs\install\rootcrs.bat -prepatch


--遇到的问题：
rootcrs.bat -prepatch 后 ora.storage 启动不起来报错：
 [ora.storage]{0:0:172} [start] (null) category: 7, operation: kgfoAl06, loc: kgfokge, OS error: 12638, other: ORA-12638: 身份证明检索失败

--解决办法：Windows : Postpatch fails with ORA-12638 after applying BP 19.10 (Doc ID 2768922.1)
Please set SQLNET.NO_NTLM = FALSE in sqlnet.ora under GRID_HOME/network/admin.




3、查看集群服务状态，确保集群已关闭
crsctl stat res -t -init

4、查看正在运行的oracle服务，然后手工关闭
net start | findstr /i ora
   Oracle Trace File Analyzer
   OracleRemExecServiceV2
   OracleVssWriterORCL1
   Storage Service
   User Data Storage_892ff


net stop "Oracle Trace File Analyzer"
net stop "OracleRemExecServiceV2"
net stop  "OracleVssWriterORCL1"
net stop "Storage Service"
net stop "User Data Storage_892ff"


5、停止 OraFenceService 服务
net stop OraFenceService

6、解压补丁，替换OPatch目录，打补丁
%ORACLE_HOME%/OPatch/opatch apply  C:\Users\Administrator\Desktop\37486199  -local


7、更新Oracle Fence driver
crssetup.exe deinstallfence
crssetup.exe installfence

8、启动OraFenceService服务
net start OraFenceService

9、启动oracle集群服务

--二选一，我这里选的2
If this is not an Oracle Restart home, then run this command:

%ORACLE_HOME%\crs\install\rootcrs.bat -postpatch

If this is an Oracle Restart home, then run this command:

%ORACLE_HOME%\crs\install\roothas.bat -postpatch




--报错1：
set LC_ALL=C
%ORACLE_HOME%\crs\install\roothas.bat -postpatch 
CLSRSC-752: incorrect invocation of script 'roothas.pl' called on a Grid Infrastructure cluster node


--参考CLSRSC-752: Incorrect Invocation Of Script 'roothas.pl' Called On A Grid Infrastructure Cluster Node (Doc ID 2976266.1)

----注意是rootcrs.bat而不是roothas.bat
%ORACLE_HOME%/crs/install/rootcrs.bat -unlock
%ORACLE_HOME%/bin/clscfg -localpatch
--这一步报错，选择了忽略
%ORACLE_HOME%/bin/clscfg -patch
%ORACLE_HOME%/crs/install/rootcrs.bat -lock
crsctl start crs

--在2.2选择的roothas.bat,这里一定要选择rootcrs.bat,否则还是报CLSRSC-752错误，
--或者roothas或者rootcrs挨个尝试,我这里是rootcrs.bat
%ORACLE_HOME%\crs\install\rootcrs.bat -prepatch
%ORACLE_HOME%\crs\install\rootcrs.bat -postpatch

--报错2： 节点1打完补丁后部分服务起不来
--ORA-39510/ORA-39511: CRS-2549 and CRS-0223 errors When Starting ASM After applying GI BP (Doc ID 2632780.1)
C:\Users\Administrator>srvctl start listener -n rac1
PRCR-1013 : 无法启动资源 ora.LISTENER.lsnr
PRCR-1064 : 无法在节点 rac1 上启动资源 ora.LISTENER.lsnr
CRS-2549: Resource 'ora.LISTENER.lsnr' cannot be placed on 'rac1' as it is not a valid candidate as per the placement policy

  原因是因为，在打季度补丁的过程中，rootcrs.pl -prepatch 会把当前节点的 RESOURCE_USE_ENABLED 设置为0，以防止ASM以及其他资源启动。
--查看状态参数
crsctl stat server -f
--修改RESOURCE_USE_ENABLED值为1
crsctl set resource use 1
--重新启动集群



10、设置database 环境变量
set ORACLE_HOME=c:\WINDOWS.X64_193000_db_home

11、数据库层手工停实例
%ORACLE_HOME%\bin\srvctl start instance -d dbname -i <instance_name>

srvctl stop instance -d orcl -i  orcl2

12、操作系统层停服务
net stop OracleService%ORACLE_SID%

net stop OracleServiceorcl2

13、应用补丁
%ORACLE_HOME%/OPatch/opatch apply  C:\Users\Administrator\Desktop\37486199  -local

14、起服务
%ORACLE_HOME%\bin\srvctl start instance -d dbname -i <instance_name>

srvctl start instance -d orcl -i  orcl2

15、  Load Modified SQL Files into the Database
--Non-CDB or Non-PDB Database
sqlplus / as SYSDBA
startup;
%ORACLE_HOME%\OPatch\datapatch -verbose


--Multitenant (CDB/PDB) Database
sqlplus / as SYSDBA
startup;
alter pluggable database all open ;
EXIT;
%ORACLE_HOME%\OPatch\datapatch -verbose








四、ojvm 打补丁

c:\WINDOWS.X64_193000_db_home\OPatch\opatch apply  C:\Users\Administrator\Desktop\37102264 -local



--Non-CDB or Non-PDB Database
sqlplus / as SYSDBA
startup;
%ORACLE_HOME%\OPatch\datapatch -verbose


--Multitenant (CDB/PDB) Database
sqlplus / as SYSDBA
startup;
alter pluggable database all open ;
EXIT;
%ORACLE_HOME%\OPatch\datapatch -verbose

--补丁查看
set linesize  1000;
select patch_id,action,status from dba_registry_sqlpatch;




五、编译失效对象

--查看无效的对象：
col owner for a20
SELECT owner,count(1)
FROM dba_objects 
WHERE status = 'INVALID'
group by owner;

--重新编辑无效对象
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @utlrp.sql