Oracle Opatch Conflict ERROR


Merhaba,

Geçtiğimiz gün, son çıkan PSU Jul2017 - patch 26030799 - Oracle Grid Infrastructure Patch Set Update 11.2.0.4.170718 'nı Oracle 2 Node RAC Veritabanıma uygularken (GRID & ORACLE_HOME) aşağıdaki gibi conflic hatası aldım.. Yani başka bir patch ile conflic oldum.

### Patching.sh ./opatch auto Grid Home & DB Home Automaticly ###


Grid Home : /u01/app/11.2.0/grid
DB Home : /u01/app/oracle/product/11.2.0/dbhome_1

Executing /u01/app/11.2.0/grid/perl/bin/perl /u01/app/11.2.0/grid/OPatch/crs/patch11203.pl -patchdir /u01/stage -patchn 26030799 -ocmrf /u01/stage/ocm.rsp -paramfile /u01/app/11.2.0/grid/crs/install/crsconfig_params

This is the main log file: /u01/app/11.2.0/grid/cfgtoollogs/opatchauto2017-08-08_01-48-19.log

This file will show your detected configuration and all the steps that opatchauto attempted to do on your system:
/u01/app/11.2.0/grid/cfgtoollogs/opatchauto2017-08-08_01-48-19.report.log

2017-08-08 01:48:19: Starting Clusterware Patch Setup
Using configuration parameter file: /u01/app/11.2.0/grid/crs/install/crsconfig_params

Unable to determine if /u01/app/oracle/product/11.2.0/dbhome_1 is shared oracle home
Enter 'yes' if this is not a shared home or if the prerequiste actions are performed to patch this shared home (yes/no):yes

Unable to determine if /u01/app/11.2.0/grid is shared oracle home
Enter 'yes' if this is not a shared home or if the prerequiste actions are performed to patch this shared home (yes/no):yes
The opatch Conflict check failed for /u01/app/oracle/product/11.2.0/dbhome_1. The patch /u01/stage/26030799/25869727 is either already applied or conflicting with another patch applied in the home
Conflict check failed for oracle home /u01/app/oracle/product/11.2.0/dbhome_1
Conflict check failed
ERROR: Conflict-Check has failed . Please refer to /u01/app/11.2.0/grid/cfgtoollogs/opatchauto2017-08-08_01-48-19.log for details

opatch auto failed.


Aksiyon olarak Oracle_Home opatch üzerinden ./opatch lsinventory ile ne geldiyse silip psu yu yeniden uyguladım.

[oracle@DB1]/u01/app/oracle/product/11.2.0/dbhome_1/OPatch> ./opatch lsinventory


opatch rollback -id 19285025
opatch rollback -id 22502549
opatch rollback -id 22502456

Local ile çalıştırdım...

opatch rollback -id 19285025 -local -oh /u01/app/oracle/product/11.2.0/dbhome_1

opatch rollback -id 22502549 -local -oh /u01/app/oracle/product/11.2.0/dbhome_1
opatch rollback -id 22502456 -local -oh /u01/app/oracle/product/11.2.0/dbhome_1


En son ne kaldı diye kontrol ediyorum...

[oracle@DB1]/u01/app/oracle/product/11.2.0/dbhome_1/OPatch> ./opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.16
Copyright (c) 2017, Oracle Corporation. All rights reserved.


Oracle Home : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
from : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.16
OUI version : 11.2.0.4.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2017-08-08_03-36-20AM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2017-08-08_03-36-20AM.txt

--------------------------------------------------------------------------------
Local Machine Information::
Hostname: DB1
ARU platform id: 226
ARU platform description:: Linux x86-64

Installed Top-level Products (1):

Oracle Database 11g 11.2.0.4.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


--------------------------------------------------------------------------------

OPatch succeeded.


ORACLE_HOME Opatch altında daha önce uygulanan tüm patch'leri rollback yaptıktan sonra ./opatch auto ile hem GI_HOME hem ORACLE_HOME için yeniden PSU uygulayabildim. 



ORA-01012 not logged on Instance startup failed


ORA-01012: not logged on


Merhaba, Ben bu problemi patch sonrası Database açarken yaşadım.

[oracle@rac1]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 1 02:16:17 2017

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

SQL> CONNECT / AS SYSDBA
Connected to an idle instance.
SQL>
SQL> STARTUP
ORA-01012: not logged on
SQL>

DB'yi zorla kapattığımız durumlarda yada OS seviyesinde oracle processi kill -9 ile kapatmaya zorlarsanız bu sorun ile karşılaşabilir siniz. 
Sorun sheared memorynin kirli kalması ve hala oracleSID processlerinin sheared memory de çalışıyor gibi hatırlaması..

ps -ef | grep $ORACLE_SID

ile kontrol ederseniz herhangi bir oracle processi görmeyeceksiniz.

Çözüm olarak SYSRESV komutu ile shared_momory de alocate etmiş IPC Resources leri bulup dışarı atmamız olacak.

$sysresv
ipcrm -m

[oracle@rac1]$ sysresv

IPC Resources for ORACLE_SID "prproddb" :
Shared Memory:
ID KEY
5570561 0x00000000
14942211 0x350262c8
Oracle Instance not alive for sid "prproddb"


[oracle@rac1]$ ipcrm -m 5570561
[oracle@rac1]$ ipcrm -m 14942211


Artık DB'yi normal olarak açabiliriz.

[oracle@rac1]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jan 1 02:18:23 2017

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

SQL> CONNECT / AS SYSDBA
Connected to an idle instance.
SQL> STARTUP
ORACLE instance started.

Total System Global Area 4877066240 bytes
Fixed Size 2261528 bytes
Variable Size 1509953000 bytes
Database Buffers 3355443200 bytes
Redo Buffers 9408512 bytes
Database mounted.
Database opened.

SQL>

Eğer sunucunuzun çok geç açılmayacağını düşünüyorsanız reboot edip tüm memory temizlemek en güzeli olacaktır..

Sheared cursor detail v$sql_shared_cursor


Aşağıdaki sorgu yardımı ile, belirlediğimiz bir sql'in sqlid ni girerek, SGA içerisindeki davranış detayını, oluşturduğu child tipini görebilirsiniz.

P.S: Neden ihtiyaç duyabiliriz ?
P.S.S: Pek çok kez sql detayını incelediğimizde bir sql'in pek çok childinin oluştuğunu görebiliriz. Bunların hangi type yapısınıda olduğunun detayını bize verir. Ve buna göre aksiyon alabiliriz.


select sql_id,sebep reason,sum(decode(sorun,'Y',1,0)) adet from (
select sql_id,
UNBOUND_CURSOR ,
SQL_TYPE_MISMATCH ,
OPTIMIZER_MISMATCH ,
OUTLINE_MISMATCH ,
STATS_ROW_MISMATCH ,
LITERAL_MISMATCH ,
FORCE_HARD_PARSE ,
EXPLAIN_PLAN_CURSOR ,
BUFFERED_DML_MISMATCH ,
PDML_ENV_MISMATCH ,
INST_DRTLD_MISMATCH ,
SLAVE_QC_MISMATCH ,
TYPECHECK_MISMATCH ,
AUTH_CHECK_MISMATCH ,
BIND_MISMATCH ,
DESCRIBE_MISMATCH ,
LANGUAGE_MISMATCH ,
TRANSLATION_MISMATCH ,
BIND_EQUIV_FAILURE ,
INSUFF_PRIVS ,
INSUFF_PRIVS_REM ,
REMOTE_TRANS_MISMATCH ,
LOGMINER_SESSION_MISMATCH ,
INCOMP_LTRL_MISMATCH ,
OVERLAP_TIME_MISMATCH ,
EDITION_MISMATCH ,
MV_QUERY_GEN_MISMATCH ,
USER_BIND_PEEK_MISMATCH ,
TYPCHK_DEP_MISMATCH ,
NO_TRIGGER_MISMATCH ,
FLasHBACK_CURSOR ,
ANYDATA_TRANSFORMATION ,
PDDL_ENV_MISMATCH ,
TOP_LEVEL_RPI_CURSOR ,
DIFFERENT_LONG_LENGTH ,
LOGICAL_STANDBY_APPLY ,
DIFF_CALL_DURN ,
BIND_UACS_DIFF ,
PLSQL_CMP_SWITCHS_DIFF ,
CURSOR_PARTS_MISMATCH ,
STB_OBJECT_MISMATCH ,
CROSSEDITION_TRIGGER_MISMATCH ,
PQ_SLAVE_MISMATCH ,
TOP_LEVEL_DDL_MISMATCH ,
MULTI_PX_MISMATCH ,
BIND_PEEKED_PQ_MISMATCH ,
MV_REWRITE_MISMATCH ,
ROLL_INVALID_MISMATCH ,
OPTIMIZER_MODE_MISMATCH ,
PX_MISMATCH ,
MV_STALEOBJ_MISMATCH ,
FLasHBACK_TABLE_MISMATCH ,
LITREP_COMP_MISMATCH ,
PLSQL_DEBUG ,
LOAD_OPTIMIZER_STATS ,
ACL_MISMATCH ,
FLasHBACK_ARCHIVE_MISMATCH ,
LOCK_USER_SCHEMA_FAILED ,
REMOTE_MAPPING_MISMATCH ,
LOAD_RUNTIME_HEAP_FAILED ,
HasH_MATCH_FAILED ,
PURGED_CURSOR ,
BIND_LENGTH_UPGRADEABLE ,
USE_FEEDBACK_STATS
from v$sql_shared_cursor where sql_id='&sql_id')
unpivot (sorun for sebep in (
UNBOUND_CURSOR as 'UNBOUND_CURSOR',
SQL_TYPE_MISMATCH as 'SQL_TYPE_MISMATCH',
OPTIMIZER_MISMATCH as 'OPTIMIZER_MISMATCH',
OUTLINE_MISMATCH as 'OUTLINE_MISMATCH',
STATS_ROW_MISMATCH as 'STATS_ROW_MISMATCH',
LITERAL_MISMATCH as 'LITERAL_MISMATCH',
FORCE_HARD_PARSE as 'FORCE_HARD_PARSE',
EXPLAIN_PLAN_CURSOR as 'EXPLAIN_PLAN_CURSOR',
BUFFERED_DML_MISMATCH as 'BUFFERED_DML_MISMATCH',
PDML_ENV_MISMATCH as 'PDML_ENV_MISMATCH',
INST_DRTLD_MISMATCH as 'INST_DRTLD_MISMATCH',
SLAVE_QC_MISMATCH as 'SLAVE_QC_MISMATCH',
TYPECHECK_MISMATCH as 'TYPECHECK_MISMATCH',
AUTH_CHECK_MISMATCH as 'AUTH_CHECK_MISMATCH',
BIND_MISMATCH as 'BIND_MISMATCH',
DESCRIBE_MISMATCH as 'DESCRIBE_MISMATCH',
LANGUAGE_MISMATCH as 'LANGUAGE_MISMATCH',
TRANSLATION_MISMATCH as 'TRANSLATION_MISMATCH',
BIND_EQUIV_FAILURE as 'BIND_EQUIV_FAILURE',
INSUFF_PRIVS as 'INSUFF_PRIVS',
INSUFF_PRIVS_REM as 'INSUFF_PRIVS_REM',
REMOTE_TRANS_MISMATCH as 'REMOTE_TRANS_MISMATCH',
LOGMINER_SESSION_MISMATCH as 'LOGMINER_SESSION_MISMATCH',
INCOMP_LTRL_MISMATCH as 'INCOMP_LTRL_MISMATCH',
OVERLAP_TIME_MISMATCH as 'OVERLAP_TIME_MISMATCH',
EDITION_MISMATCH as 'EDITION_MISMATCH',
MV_QUERY_GEN_MISMATCH as 'MV_QUERY_GEN_MISMATCH',
USER_BIND_PEEK_MISMATCH as 'USER_BIND_PEEK_MISMATCH',
TYPCHK_DEP_MISMATCH as 'TYPCHK_DEP_MISMATCH',
NO_TRIGGER_MISMATCH as 'NO_TRIGGER_MISMATCH',
FLASHBACK_CURSOR as 'FLASHBACK_CURSOR',
ANYDATA_TRANSFORMATION as 'ANYDATA_TRANSFORMATION',
PDDL_ENV_MISMATCH as 'PDDL_ENV_MISMATCH',
TOP_LEVEL_RPI_CURSOR as 'TOP_LEVEL_RPI_CURSOR',
DIFFERENT_LONG_LENGTH as 'DIFFERENT_LONG_LENGTH',
LOGICAL_STANDBY_APPLY as 'LOGICAL_STANDBY_APPLY',
DIFF_CALL_DURN as 'DIFF_CALL_DURN',
BIND_UACS_DIFF as 'BIND_UACS_DIFF',
PLSQL_CMP_SWITCHS_DIFF as 'PLSQL_CMP_SWITCHS_DIFF',
CURSOR_PARTS_MISMATCH as 'CURSOR_PARTS_MISMATCH',
STB_OBJECT_MISMATCH as 'STB_OBJECT_MISMATCH',
CROSSEDITION_TRIGGER_MISMATCH as 'CROSSEDITION_TRIGGER_MISMATCH',
PQ_SLAVE_MISMATCH as 'PQ_SLAVE_MISMATCH',
TOP_LEVEL_DDL_MISMATCH as 'TOP_LEVEL_DDL_MISMATCH',
MULTI_PX_MISMATCH as 'MULTI_PX_MISMATCH',
BIND_PEEKED_PQ_MISMATCH as 'BIND_PEEKED_PQ_MISMATCH',
MV_REWRITE_MISMATCH as 'MV_REWRITE_MISMATCH',
ROLL_INVALID_MISMATCH as 'ROLL_INVALID_MISMATCH',
OPTIMIZER_MODE_MISMATCH as 'OPTIMIZER_MODE_MISMATCH',
PX_MISMATCH as 'PX_MISMATCH',
MV_STALEOBJ_MISMATCH as 'MV_STALEOBJ_MISMATCH',
FLASHBACK_TABLE_MISMATCH as 'FLASHBACK_TABLE_MISMATCH',
LITREP_COMP_MISMATCH as 'LITREP_COMP_MISMATCH',
PLSQL_DEBUG as 'PLSQL_DEBUG',
LOAD_OPTIMIZER_STATS as 'LOAD_OPTIMIZER_STATS',
ACL_MISMATCH as 'ACL_MISMATCH',
FLASHBACK_ARCHIVE_MISMATCH as 'FLASHBACK_ARCHIVE_MISMATCH',
LOCK_USER_SCHEMA_FAILED as 'LOCK_USER_SCHEMA_FAILED',
REMOTE_MAPPING_MISMATCH as 'REMOTE_MAPPING_MISMATCH',
LOAD_RUNTIME_HEAP_FAILED as 'LOAD_RUNTIME_HEAP_FAILED',
HASH_MATCH_FAILED as 'HASH_MATCH_FAILED',
PURGED_CURSOR as 'PURGED_CURSOR',
BIND_LENGTH_UPGRADEABLE as 'BIND_LENGTH_UPGRADEABLE',
USE_FEEDBACK_STATS as 'USE_FEEDBACK_STATS'
) )
group by sql_id,sebep
having sum(decode(sorun,'Y',1,0))>0;

Disable Transparent Huge Pages

Transparent Huge Pages kurulum sırasında default var (enabled) olarak gelen bir özellik. Fakat bu özelliğin disable edilmesi öneriliyor. “Doc ID 1557478.1

Transparent Huge Pages özellikle Oracle RAC ortamında, enable olması bilinmeyen node rebootlarına ve performans sorunlarına yol açıyor. Bunun yanında Huge Pages kullanımı yapılıyor ise Transparent Huge Pages disabled olmalıdır. 

Şimdi nasıl disable edeceğimize bakalım. Doc ID 1557478.1”  göre 2 farklı yönden workaround uygulanabilirsiniz. Ben aşağıdaki gibi uyguladım.  

1-) Transparent huge pages parametresini kontrol edelim
[root@s0db01  ~]# cat /sys/kernel/mm/transparent_hugepage/enabled
[always] madvise never

2-) Aynı şekilde AnonHugePages değeride disabled olduğunda 0 olmalı. Şuan aşağıdaki gibi
[root@s0db01  ~]# cat /proc/meminfo
MemTotal:       66087116 kB
MemFree:          373444 kB
Buffers:          311244 kB
Cached:         29615640 kB
SwapCached:            8 kB
AnonHugePages:    423936 kB
HugePages_Total:   15360
HugePages_Free:     7092
HugePages_Rsvd:     2485
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:        5360 kB
DirectMap2M:    67100672 kB
[root@s0db01  ~]#

Gördüğümüz üzere disable edilmemiş. Şimdi Disable işlemini gerçekleştirelim 

3-) grub.conf da Transparent Huge Pages disable edelim. Support.Oracle da  

grub.conf  içerisine girip güncellemeyi yapalım 

[root@s0db01  ~]# vi /etc/grub.conf










Güncelleme sonrası reboot ediyoruz. Sonra kontrol edelim
 



Ara