2024Oracle 19c经验分享指南

Oracle19c219CRU与one-off的选择319c的维护注意事项OracleOracleOracleOracleBaseorOracleLongtermrelease:提供最高级别的稳定性和最长的纠错支持,53Innovationrelease:在Oracle数据库长期版本之间,至少2年的首要支持，但没有扩展支持。219CRU与one-off的选择319c的维护注意事项MOSMOSAssistant:DownloadReferenceforOracleDatabase/GIUpdate,Revision,PSU,SPU(CPU),BundlePatches,PatchsetsandBaseReleases(DocID2118136.2)19cOracleDatabase19cProactivePatchInformation(DocIDPrimaryNoteforDatabaseProactivePatchProgram(DocIDONE-OFFOracleDatabase19cImportantRecommendedOne-offPatches(DocID555.1)556.1(forExadata)RequirementsforInstallingOracleDatabase19conOL7orRHEL764-bit(x86-64)(DocID2551169.1)MOSMOSDoc.MOSMOSDoc.SearchKEY“alert19.12”“bug19.12”“ConsidertoAvoidToConsidertoAvoidThingstoConsidertoAvoidDatabasePerformanceProblemson19c(DocIDThingstoConsidertoAvoidProminentWrongResultProblemson19CProactively(DocID2606585.1)ThingstoConsidertoAvoidSQLPerformanceProblemson19c(DocID19c19c19C19CRU与one-off安装555.1文档中RUone-off除555.1外，建议安装当前RU已知bug场景中关键patch.如wrongresult、219CRU与one-off的选择319c的维护注意事项Linux7最小化安装注意MissingLinux7最小化安装注意MissingcommandopatchRequirementsforRequirementsforInstallingOracleDatabase19conOL7orRHEL764-bit(x86-64)(DocIDLinuxLinux7推荐顺序chronyd $cluvfycompclocksync-nall-19csqlnet.ora文件中此参数的值。ORA-03134:ConnectionstothisserverversionarenolongerOracle11.2clientversion(<)Oracle12.2April2018PSU或18C、19Cdatabases后,密码过期修改密码时提示ora-–ORA-28041:Authenticationprotocolinternal#9–orVERSION<=12.1createatriggeronDATABASE,youmusthavetheADMINISTERDATABASETRIGGERsystemprivilege.BydefaultDBAandIMP_FULL_DATABASErolehasthisprivileges.Alsosysuserhasthisprivilegesbydefault.In12.2+,directgrantof“administerdatabasetrigger”isneededforthetriggerSysSysAlterusersys修改密码只会更新本实例的pwfile,和mrp所在实例的pwfile,RAC的其他实例需要scp密码文件.或放到ASM中共享pwfileSYSSYSCheckPASSWORD_CHANGE_DATEPTIMEfromdba_userswhereusername='SYS'select'USER$',NAME,NULL,NULL,to_char(ASTATUS),LTIME,EXPTIME,PTIMEfromuser$whereUSER$NOPRIVIEGEBYDBAorSELECTANY For12c-Forbettersecurity,theSELECTANYDICTIONARYsystemprivilegenolongerpermitsyoutoquerytheSYSschemasystemtablesDEFAULT_PWD$,ENC$,LINK$,USER$,USER_HISTORY$,CDB_LOCAL_ADMINAUTH$,andXS$VERIFIERS.OnlyuserSYShasaccesstothesetables.Wesuggestuserstousedictionaryviewsuchasdba_usersinsteadofsys.user$.SysSysOrOrapwfilein3,3,orapwfiledifferencebetweenLocalLocal4,scporapwdtoanotherinstance,and2,synctoorapwfile1,ModifySYSpwdonAlterusersysidentifiedbyOrapwfileinOrapwfileinAHFAHFtfactlsetreposizeMB=50000（all使用root用户给oracle和grid赋权，不用每次使用roottfactlaccesspromote-useroracle;tfactlaccesspromote-usertfactlstoptfactlstartoswbb107219c19ctracekjgcr_SlaveReqBegin:failedtoqueuemessagetoslave,status4kjgcr_SlaveEmancipatedCall:Failedtocallslavekjgcr_SlaveEmancipatedCall:Failedtofreewmsg0x273ac0f30LMHBtracefileinevery2Bugkjgcr_DoAction:Ranaction-id:38enabledrm(cpu)ranwithstatus:0clr:1Bug30028649LMHBGenerateskjgcr_doactionTraceMessagesAfterApplyingPatch29328092BugTomanyDRMmessagesintheLMHBlogkjgcr_DoAction:Ranaction-id:38enabledrm(cpu)ranwithstatus:0clr:1kjgcr_DoAction:Ranaction-id:37enabledrmranwithstatus:0clr:1kjgcr_DoAction:Ranaction-id:38enabledrm(cpu)ranwithstatus:0TurnoffactionmessagesforenableBugThefixofBUG30914674affectstotherelevantfixedcodesofBug29694869andsimilarissueisoccredagain.removeLMHBtracefilemessage:"krsw_get_redo_info:cannotfindSRLforsequence“"*tt*"tracefilesgrowBugDBW0traceiop-3:nfr=0,buf=0x13cf5801b0,dba=0xff0159f5,nwr=3307,tim=1625564461,dfr=0,dlt=4,slt0x7f0892b6a46Dbwrtracefileval=0BUGtracetrace find$ORACLE_BASE/diag-typef-size+10000-name"*.trc"-execls-l{}ls-l|awk $6$7""$5}'|awk'{arr[$1]+=$2}END{for(iinarr){printi,arr[i]}}'Oct13Oct14Oct15Oct16Oct17Oct1819c19cOracle19c上，Oracle引入了有关listener管理的新参数。我们可以在Oracle内配置日志轮换，而无需在OSshell脚本上进行任何额外配置，实现logrotate功能。该特性叫做“OracleNetworkLogFileSegmentation”listener.ora中。 19c19c11g慢了10DocID2730374.1Logon/logoffIn19cEEDatabaseIsMoreThan10xSlowerThanInThisparameterisavailablestartingwithOracleDatabaserelease19c,version19.1.BydefaultCLIENT_STATISTICS_LEVELissettoTYPICAL.CLIENT_STATISTICS_LEVELcontrolswhetherdatabaseclientsreportnetworkstatisticstotheTYPICAL:Thissettingenablesclientstocollectnetworkstatisticsandperiodicallysendthemtothedatabase.Disablethefeaturebysettingtheparameterclient_statistics_levelvaluetoOFF.Clientswillnotsendnetworkstatisticstothedatabase.altersystemsetclient_statistics_level=OFFscope=spfile;restartdatabase19c19cListenerWARNING:SubscriptionfornodedowneventstillpendinginThesemessagesarerelatedtotheOracleTNSListener'sdefaultsubscriptiontotheOracleNotificationService(ONS).CheckONSstatusonsctlcrsctlstatresora.onsiftheapplicationdoesn'tuseFCForFAN,thelistenersubscriptiontothetheONScanbedisabledusingeitheroftheAddSUBSCRIBE_FOR_NODE_DOWN_EVENT_<listener_name>=OFFinplsqlplsqldeveloper13JOBLibraryLibrarycachelock因为LastsuccessfulloginOracle12cNewFeatureforceupdate，影响12并发登录时WaiteventLibrarycachelockp3namespaceSQL:updateuser$setspare6=DECODE(to_char(:2,‘YYYY-MM-DD’),‘0000-00-00’,to_date(NULL),:2)whereuser#=:1user$.spare6ORACLE已经对12c和19c推出了bugPatch33121934引入_granularity_last_successful_login_time（30sdefault）UNLIMITEDInactiveAccountTime19c19cLMSnprocessRTrealtimemode安装时非1安装时非1root.sh软件bug登录CDBaltersessionsetSqlplusORACLE_PDB_SID(blog219CRU与one-off的选择319c的维护注意事项19.919.9ora-<=19.10版本，splitpartition递归SQLindpart$时，因为违反了bo#,part#bug32259535ORA-1/ORA-00001:uniqueconstraint(sys.i_indpart_bopart$)duringALTERTABLESPLIT安装one-offCaseCase19.9ora-600一个Oracle19c（19.9）拆分区时ora-600[kkpapDIPObjNum1]目前的分区检索SQL,分区列上只有全局oracle的已知Bug31667096，19.619.719.919.1119c19cWRH$_SYSMETRIC_HISTORY_INDEXWRH$_SYSMETRIC_HISTORY_INDEXIndexDoesNotGetPartitionedAfterDatabase11gglobal12cglobalupdateglobalindex19cgloballocalGlobalindex19.819.8InstanceOpenhangsBecauseOfSRL在做了DGSwitchoverFailoverPrimary打开时DBalertlog输出clearDBwait“rowcachecurrentsql:update/*+rule*/undo$set=:13whereBUG31747989oneoffPatchPatch31747989ORSET“_min_undosegs_for_parallel_fptr”=0，FIXED19.9Bug30931981oneoffPatchopen过程中产生的死锁，附加”cursor:pin”FIXED19.10Case.Case.19conAIXcommunicationGIalertlogohasd_orarootagent_root.trcCRS-7510:The'daemonthread'ofOracleGridInterprocesscommunication(GIPC)hasbeennon-responsivefor'10508'milliseconds.Additionaldiagnostics:processname:'ocssd'.FlushoftheARPcachemorefrequentlybysettingAIXOSparameter"arpt_killc"toalowervalue0or1,thedefaultvalueis20(minutes).--ORRun"arp-d<hostname>"tomanuallydeletearptfactlcollect没有收集oswarp,实际在AHFoswAIX:ARPCacheNotUpdatedAfterHAIPFailover(DocIDCase.Case.19Cora-600whenrowsets“rowsets”Oracle12c特性是SQL层执行计划相关altersystemsetscope=bothsid=’*’;CaseCaseORA-00979:notaGROUPBYORA-30481:GROUPINGfunctiononlysupportedwithGROUPBYCUBEorCaseCase 19.14DatabaseServiceFailstoStartWithCRS-RootscriptsduringGridInfrastructureupgrade(rootupgrade.sh)/patching(rootcrs.sh-postpatch)orrestartofdatabase/servicefailswithCRS-5037errorononeormorenodes.$srvctlstartservice-ddatabase_name-sCRS-5037:DatabasedoesnothavetherequiredfixforbugThisbugisonlyrelevantwhenusingRealApplicationClustersGridInfrastructureReleaseContainsdatabase(s)runningon"RDBMSReleases<=18c"and/or"RDBMSReleases19.3to19.10withoutPatch31143870"Hasoneormoreuser-definedservicesNOTcreatedonPDB.I.e.“crsctlstatresora.db_name.service_name.svc-f|grepPLUGGABLE_DATABSE”返回null值ALERT:DatabaseServiceFailstoStartWith"CRS-5037:Databasedoesnothavetherequiredfixforbug'31143870'"AFTERGridInfrastructurePatchingorUpgradeto19.14RU(DocID2835152.1)Case.19.13GIRUClusterNodeswillnotStartAutomaticallyAfteraNodeEvictionThisproblemoccursonlywhen19.13GIRUorhigherisappliedtoGIHOMEonLinux(x86-64))anddoesnotaffectpre19.13,whenLinuxkdumpisnotenabledandthekernel.panicparameterissetto0.在19.13CSSDagent使用“echoc”而不是“echob”。如果未配置kdump，系统将显示挂起并且不会重新启动，因为默认的“kernel.panic”0。1建议配置#systemctlis-activekdump#sysctl-wAlert:PostApplying19.13GIRUorHigher,ClusterNodeswillnotStartAutomaticallyAfteraNodeEviction(DocIDCaseCase.19.9SQLParseora-kkshhcdel–kernelcompilesharedobjects(cursor)cursorhashtablehashchainchildkksfbc–kerne