2024Oracle 19c經(jīng)驗分享指南

Oracle19c219CRU與one-off的選擇319c的維護注意事項OracleOracleOracleOracleBaseorOracleLongtermrelease:提供最高級別的穩(wěn)定性和最長的糾錯支持,53Innovationrelease:在Oracle數(shù)據(jù)庫長期版本之間,至少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場景中關(guān)鍵patch.如wrongresult、219CRU與one-off的選擇319c的維護注意事項Linux7最小化安裝注意MissingLinux7最小化安裝注意MissingcommandopatchRequirementsforRequirementsforInstallingOracleDatabase19conOL7orRHEL764-bit(x86-64)(DocIDLinuxLinux7推薦順序chronyd $cluvfycompclocksync-nall-19csqlnet.ora文件中此參數(shù)的值。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賦權(quán)，不用每次使用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引入了有關(guān)listener管理的新參數(shù)。我們可以在Oracle內(nèi)配置日志輪換，而無需在OSshell腳本上進行任何額外配置，實現(xiàn)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并發(fā)登錄時WaiteventLibrarycachelockp3namespaceSQL:updateuser$setspare6=DECODE(to_char(:2,‘YYYY-MM-DD’),‘0000-00-00’,to_date(NULL),:2)whereuser#=:1user$.spare6ORACLE已經(jīng)對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）拆分區(qū)時ora-600[kkpapDIPObjNum1]目前的分區(qū)檢索SQL,分區(qū)列上只有全局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，F(xiàn)IXED19.9Bug30931981oneoffPatchopen過程中產(chǎn)生的死鎖，附加”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”O(jiān)racle12c特性是SQL層執(zhí)行計劃相關(guān)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值A(chǔ)LERT:DatabaseServiceFailstoStartWith"CRS-5037:Databasedoesnothavetherequiredfixforbug'31143870'"AFTERGridInfrastructurePatchingorUpgradeto19.14RU(DocID2835152.1)Case.19.13GIRUClusterNodeswillnotStartAutomaticallyAfteraNodeEvictionThisproblemoccursonlywhen19.13GIRUorhigherisappliedtoGIHOMEonLinux(x86-64))anddoesnotaffectpre19.13,whenLinuxkdumpisnotenabledandthekernel.panicparameterissetto0.在19.13CSSDagent使用“echoc”而不是“echob”。如果未配置kdump，系統(tǒng)將顯示掛起并且不會重新啟動，因為默認的“kernel.panic”0。1建議配置#systemctlis-activekdump#sysctl-wAlert:PostApplying19.13GIRUorHigher,ClusterNodeswillnotStartAutomaticallyAfteraNodeEviction(DocIDCaseCase.19.9SQLParseora-kkshhcdel–kernelcompilesharedobjects(cursor)cursorhashtablehashchainchildkksfbc–kerne