Oracle英文版培训课件之Implement Streams：les21

TroubleshootingOracleStreamsObjectivesAftercompletingthislesson,youshouldbeabletodothefollowing:DescribeandresolvecommonconfigurationproblemsforaStreamsdatabaseDescribeandresolvethetypicalproblemsthatoccurduring:CapturePropagationApplyTroubleshootsecurequeueaccessTroubleshootingCaptureChecklist:Whatisthestateofthecaptureprocess?Isthedatabaseconfigurationcorrect?Aretherulesconfiguredcorrectly?Istransformationbeingused?Arethereanyerrormessages?CaptureProcessStatusCheckthestateofthecaptureprocess:Abortedordisabled?Checktheappropriatetracefileformessages.Enabled?Isthecaptureprocesscapturingcurrentchanges?Whenwasthelastchangemadeavailableforcapture?Whatisthecaptureprocessdoing?SELECTCAPTURE_NAME,((SYSDATE-CAPTURE_MESSAGE_CREATE_TIME)*86400)

"Redoscanninglatency",CAPTURE_MESSAGE_CREATE_TIMEFROMV$STREAMS_CAPTURE;***CaptureprocessstatNotesDeterminingEventEnqueuingLatencyALTERSESSIONSETnls_date_format='HH24:MI:SSMM/DD/YY';COLUMNLAST_POSTHEADING'Secssince|lastpost'SELECTcapture_name,total_messages_capturedSCANNED,total_messages_enqueuedENQUEUED,(SYSDATE-capture_time)*86400LAST_POST,(enqueue_time-enqueue_message_create_time)*86400"Latency(secs)",enqueue_message_create_time"LastQueuedMsgTime",enqueue_timeFROMV$STREAMS_CAPTURE;***EventEnqueueLatencyNotesCaptureDatabaseConfigurationIsCOMPATIBLEsettotheappropriateversion?IsthedatabaseinARCHIVELOGmode?Hasthearchiveprocessstarted?IstheStreamspoolorthesharedpoolsizedlargeenough?NotesSupplementalLoggingTablelevelloggingforprimaryanduniquekeys?SELECTowner,table_name,log_group_typeFROMDBA_LOG_GROUPS;SELECTtable_name,scn,timestamp,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_UI,SUPPLEMENTAL_LOG_DATA_FK,SUPPLEMENTAL_LOG_DATA_ALLFROMDBA_CAPTURE_PREPARED_TABLES;SELECTtable_name,column_name,log_group_name,

positionFROMDBA_LOG_GROUP_COLUMNS;Enabledatsourcedatabaselevelloggingforprimaryanduniquekeys?Checkthecolumnsinsupplementalloggroups:ChecktheCaptureProcessRulesCheckDBA_STREAMS_RULESorasimilarview.Queryfornegativerulesets.Checkforemptyrulesets.Determineifthesystem-createdruleconditionhasbeenmodifiedwithDBMS_RULE_ADM.SELECTstreams_name,rule_owner,rule_name,

rule_condition,rule_set_type"TYPE",

streams_rule_type"LEVEL",

schema_name,object_name,subsetting_operation,

dml_condition,same_rule_condition"Orig?"FROMDBA_STREAMS_RULESWHEREstreams_type='CAPTURE';CheckforTransformationsChecktoseeifthereisatransformationspecifiedforarule.Arule-basedtransformationcanmodifyaneventwhenaruleinapositiverulesetevaluatestoTRUEforthatevent.SELECTrule_owner,rule_name,user_function_nameFROMDBA_STREAMS_TRANSFORMATIONS;ViewforMonitoringTransactions

SELECTstreams_name,streams_type,cumulative_message_count,first_message_time,XIDUSN,XIDSLT,XIDSQN,last_message_time,total_message_countFROMv$streams_transaction;UseV$STREAMS_TRANSACTIONtomonitortransactionsprocessedbyapplyorcaptureprocess:NotesCommonCaptureErrorsORA-00258ORA-00902ORA-01291andORA-01323ORA-1280ORA-01291andORA-01323ORA-01291:"missinglogfile"ORA-01323:"invalidstate" IndicatesthatanarchivedlogfilecannotbeopenedbythecaptureprocessFirst

SCNStart

SCNRequired

checkpoint

SCNApplied

SCN92489095799210258681053CheckforTraceFilesOnmostsystems,thefilenameformatissid_xxxx_iiiii.trc,where:sidistheSystemIdentifierofthedatabasexxxxisoneofthefollowing:Captureprocess=c001,c002,…JobQueueCoordinatorprocess=cjq0JobQueueprocess=j000,j001,...Applyprocess=a001,a002,…Parallelexecutionserverprocessusedbycaptureorapply=p000,p001,p002…iiiiiistheoperatingsystemprocessnumberTroubleshootingPropagationChecklist:Hasthepropagationbeenspecifiedbetweenthecorrectsitesandqueues?Isqueue_to_queuepropagationparametersettoTRUEorFALSEinDBA_PROPAGATIONview?Doesthedatabaselinkexist?Isitworking?Isthepropagationenabledandscheduledproperly?IstheparameterJOB_QUEUE_PROCESSES>0?Arethereanytracefilesoralertlogmessages?Dorulesexistforthepropagation?Arethereanytransformationsspecifiedforthepropagationrules?Isthepropagationgettingerrormessages?CheckPropagationConfigurationCheckthepropagationname,source,anddestinationqueuenames.SELECTpropagation_name,source_queue_owner||'.'||source_queue_nameSRC,destination_queue_owner||'.'||

destination_queue_nameDEST,

destination_dblinkDBLINK,QUEUE_TO_QUEUEFROMDBA_PROPAGATION;CheckPropagationScheduleCheckthatthepropagationisenabledandassociatedwithajobqueueprocess.Determineifthereareanyfailuresorerrorsreceived.Querythedateandtimewhenthepropagationschedulewillbestarted.Determinethenumberofmessagessentorreceivedandthenumberofmessagesthathavebeenacknowledged.**PropagationScheduleNotesCheckthePropagationRulesCheckDBA_STREAMS_RULESorasimilarview.Checkforrule-basedtransformations.SELECTstreams_name,rule_owner,rule_name,

rule_condition,rule_set_type"TypeofRule",

streams_rule_type"RuleLevel",

schema_name,object_name,subsetting_operation,

dml_condition,same_rule_condition"Orig?"FROMdba_streams_rulesWHEREstreams_type='PROPAGATION';SELECTs.rule_name,t.transform_function_nameFROMDBA_STREAMS_RULESs,

DBA_STREAMS_TRANSFORM_FUNCTIONtWHEREs.rule_name=t.rule_nameANDs.rule_owner=t.rule_ownerANDs.streams_type='PROPAGATION';CommonPropagationErrorsORA-12154:TNS:couldnotresolveservicename.ORA-12505:TNS:listenerdoesnotcurrentlyknowofservicerequestedinconnectdescriptor.ORA-12514:TNS:listenerdoesnotcurrentlyknowofservicerequestedinconnectdescriptor.ORA-12541:TNS-12541TNS:nolistenerORA-02082:Aloopbackdatabaselinkmusthaveaconnectionqualifier.ORA-25307:Enqueueratetoohigh.Enableflowcontrol.ORA-25315unsupportedconfigurationforpropagationofbufferedmessages.TroubleshootingApplyChecklist:Whatisthestateoftheapplyprocess?Istheapplyprocesscurrent?Istheapplyprocessconfiguredcorrectly?RulesSourcedatabaseTransformationsApplyhandlersApplyofcapturedeventsoruser-enqueuedeventsArethereanyerrorsintheerrorqueueorapplyprocesserrors?CheckApplyProcessStateandConfigurationConfirmthattheapplyprocessisENABLEDandconfiguredtoapplycapturedeventscorrectly:SELECTa.apply_name,a.apply_captured,p.source_databaseSOURCE_DB,a.status,a.rule_set_name,a.negative_rule_set_name,a.error_number,a.error_messageFROMDBA_APPLYa,DBA_APPLY_PROGRESSpWHEREa.apply_name=p.apply_name;APPLY_NAMEAPPLY_CAPTSOURCE_DBSTATUS-------------------------------------RULE_SET_NAMENEGATIVE_RULE_SET_NAME------------------------------------ERROR_NUMBERERROR_MESSAGE--------------------------------APPLY_SITE1_LCRSYESSITE1.NETENABLEDRULESET$_19***ApplyProcessStatesNotesIstheApplyProcessCurrent?Ifanapplyprocessappliescapturedevents,youcanquerydictionaryviewstodeterminelatencyandactivity:V$STREAMS_APPLY_COORDINATORDBA_APPLY_PROGRESSSELECTapply_nameNAME,hwm_time"ApplyTime",

hwm_message_create_time"EventCreation",(hwm_time-hwm_message_create_time)*86400

"LatencyinSeconds",hwm_message_number"AppliedMessage#"FROMV$STREAMS_APPLY_COORDINATOR;**ApplyLatencyDeterminingtheScopeoftheProblemEvent

isnotapplied

atallsites?YesNoChecktheeventsource:1. Capturerulesatsourcesite.2. Propagationrulesat:SourcesiteAllintermediatesites1. Checktheeventdestination:ApplyprocessesandrulesInstantiationSCNsMissingdictionaryerrors2. Checkeventsourceandallintermediatesites.CheckforApplyProcessRulesCheckDBA_STREAMS_RULESorasimilarview.Checkforrule-basedtransformations.SELECTstreams_name,rule_owner,rule_name,

rule_condition,rule_set_type"TYPE",

streams_rule_type"LEVEL",

schema_name,object_name,subsetting_operation,

dml_condition,same_rule_condition"Orig?"FROMdba_streams_rulesWHEREstreams_type='APPLY';SELECTs.rule_name,t.user_function_nameFROMDBA_STREAMS_RULESs,

DBA_STREAMS_TRANSFORMATIONStWHEREs.rule_name=t.rule_nameANDs.rule_owner=t.rule_ownerANDs.streams_type='APPLY';CheckforCustomApplyorErrorHandlersCheckwhetherthereisacustomapplyprocedureoranerrorhandlerspecifiedfortheobject.DMLorerrorhandlersDDL,message,orprecommithandlersSELECTobject_owner,object_name,

operation_name,user_procedure,

apply_name,error_handlerFROMDBA_APPLY_DML_HANDLERS;SELECTapply_name,ddl_handler,

precommit_handler,message_handlerFROMDBA_APPLY;ChecktheErrorQueueQueryDBA_APPLY_ERRORtodetermineifthereareerrorsintheerrorqueue.SELECTapply_name,source_database,local_transaction_id,message_numbererror_messageFROMDBA_APPLY_ERROR;APPLY_NAMESOURCE_DATABASE--------------------------.LOCAL_TRANSACTION_ID.MESSAGE_NUMBERERROR_MESSAGE------------------------------------------------.

.APPLY_SITE1_LCRSSITE1.NET1.7.22031ORA-00001:uniqueconstraint(HR.COUNTRY_C_ID_PK_NOIOT)violated

NotesCommonApplyErrorsORA-01031:insufficientprivilegesORA-01403:nodatafoundORA-06550:linex,columny:ORA-23416:tabledoesnotcontainaprimarykeyconstraintORA-23607:invalidcolumnORA-26687:noinstantiationSCNprovidedORA-26688:missingkeyinLCRORA-26688:metadatamismatchORA-26689:columndatatypemismatchinLCRORA-01031:InsufficientPrivilegesThedesignatedapplyusermusthavetheprivilegestoperformSQLonthereplicatedobjects.

GrantprivilegesexplicitlytotheapplyuserusingtheGRANTcommand.AdditionalprivilegesforDDLcommandsmaybeneeded.Toresolvetheerror:1. Determinethemissingprivilege.2. Granttheprivilegesdirectlytotheapplyuser.3. Reexecutethetransactionfromtheerrorqueue.***ORA-01013NotesORA-06550ErroronApplyAnORA-06550error:Indicatesanerrorwithinanapplyhandlerortransformationfunction.Typicallycausestheapplyprocesstoabortwithnoerrorsintheerrorqueue.Thetracefilefortheapplycoordinatorwillreportthefullerrorstack.ORA-12801inSTREAMSprocessORA-12801:errorsignaledinparallelqueryserverP000ORA-06550:line1,column15:PLS-00201:identifier'HR.HR_TO_DEMO'mustbedeclared…***Ora-6550notes***ORA-23416:

Table

Does

Not

Include

aPrimary

Key

ConstraintConfirmthatthetablehasanexplicitprimarykeyconstraint:P=primarykeyC=checkconstraintU=uniquekeyR=referentialintegritySELECTconstraint_name,constraint_typeFROMDBA_CONSTRAINTSWHEREtable_name='JOBS'ANDowner='HR';CONSTRAINT_NAMEC-------------------JOB_ID_PKPJOB_TITLE_NNCORA-23607:InvalidColumnThiserrorisgeneratedwhenaninvalidcolumnisspecifiedinafunctionthataccessesthecolumnlistcontainedintheLCR.Checkthecolumnsintheobjectandspecifythecorrectcolumnname.lcr.delete_column('DETPNO','*');ORA-26688:

missingkeyinlcrIndicatesthatoneormoreofthekeycolumnsrequiredfortheapplyprocessaremissingCanbecausedby:Notsupplementallyloggingoneormoreindexedcolumnsofthetargettablewhenapply_parallelism>1Supplementallyloggingallcolumnsofamaterializedviewwhenapply_parallelism>1Supplementallyloggingtheprimarykeycolumnsofamaterializedviewwhenapply_parallelism=1ORA-26688:metadatamismatchSignaledwhenthechangedataintheLCRcannotbeappliedtoatableatthedestinationsite.Canbecausedby:MissingtableatthedestinationsiteNoprimarykeyenabledforthetargettableIfnoprimarykeyhasbeenspecified:UseDBMS_APPLY_ADM.SET_KEY_COLUMNStosetthekeycolumnsatthedestinationsite.Ensurethatkeycolumnsarebeingloggedatthesourcesite.SELECTowner,table_name,log_group_typeFROMDBA_LOG_GROUPSWHEREtable_name='DEPARTMENTS'ANDowner='HR';***ORA-26688NotesORA-26689:

Column

Type

MismatchRaisedwhenthedatatypesofcolumnsintheLCRarenotthesameasthedatatypesinthedatabaseobjectPossiblecauses:Columnnameisvalidbutthedatatypesdonotmatch.TheLCRcontainsextracolumns.TroubleshootingSecureQueueAccessStreamsqueuesaresecurequeues.Securitymustbeconfiguredproperlyforuserstobeabletoperformoperationsonthem.CommonsecurityerrorsforaStreamsqueue:ORA-24093:AQagent<name>notgrantedprivilegesofdatabaseuser<user>ORA-24033:norecipientsformessageORA-25224:sendernamemustbespecifiedforenqueueintosecurequeuesORA-24093:

AQ

Agent

Not

GrantedPrivile