Re: GGS_DDL_SEQ on ADG env - weird (new) issue

From: Chris Taylor <christopherdtaylor1994_at_gmail.com>
Date: Tue, 30 Apr 2019 10:51:47 -0500
Message-ID: <CAP79kiQp1h1pmdcXL45R=fkxaisPQAQ1VNH+nPTRo_AjJ+U_Xg_at_mail.gmail.com>

Interesting factoid:

If I run this pl/sql block in the standby, I get NULL for dbRole/dbOpenMode

DECLARE
stmt VARCHAR2(31700);
currSeq NUMBER;
currUserId NUMBER;
errorMessage VARCHAR2(32767);
objstatus VARCHAR2(100);
errorVal VARCHAR2(10);

real_ora_dict_obj_name VARCHAR2(4000);
real_ora_dict_obj_owner VARCHAR2(400);
real_ora_dict_obj_type VARCHAR2(130);

otype VARCHAR2(400);
disallowDDL NUMBER;
baseTabCount NUMBER := 0;
indexUnique VARCHAR2(130);
moduleNameInfo VARCHAR2(400);
oldModuleNameInfo VARCHAR2(400);
oldActionInfo VARCHAR2(400);

binObject NUMBER;

dbRole VARCHAR2(130);
dbOpenMode VARCHAR2(130);

seqCache NUMBER;
seqIncrementBy NUMBER;
pieceStmt VARCHAR2(31700);
outMessage VARCHAR2(32767);
toIgnore VARCHAR2(130);
journalId NUMBER;
journalIndexOwner VARCHAR2(400);
journalType VARCHAR2(130);

objectTemporary varchar2(130);
objectGenerated varchar2(130);
objectSecondary varchar2(130);

objectType varchar2(130);
userCancelSimulate EXCEPTION;
userCancelNestedSimulate EXCEPTION;
PRAGMA EXCEPTION_INIT (userCancelSimulate, -1013); PRAGMA EXCEPTION_INIT (userCancelNestedSimulate, -1017); isTypeTable varchar2(130);
isNestedTable varchar2(130);
isValidTimeTable varchar2(130);
isIdentityColTable varchar2(130);
ntype NUMBER;
BEGIN

• IMPORTANT: this check must happen BEFORE ANY PROCESSING
• perform check for role of this database
• if database is not PRIMARY/LOGICAL STANDBY, and if it's not READ WRITE
• then this trigger cannot (and should not) operate
• In case of not wanting trigger on LOGICAL STANDBY (or with READ WRITE),
• ddl_disable script can be used on standby to disable the trigger BEGIN IF "GGATE".DDLReplication.dbQueried IS NULL THEN SELECT database_role, open_mode INTO dbRole, dbOpenMode FROM v$database; "GGATE".DDLReplication.dbQueried := TRUE; END IF; dbms_output.put_line('dbRole = '||dbRole); dbms_output.put_line('dbOpenMode = '||dbOpenMode); IF NOT ( (dbRole = 'PRIMARY' OR dbRole = 'LOGICAL STANDBY') AND dbOpenMode = 'READ WRITE' ) THEN -- do not write any trace even though it should work as this is standby "GGATE" .DDLReplication.setCtxInfo(-1,-1,-1,-1,-1);

RETURN; -- do not use trigger if not read/write and primary/logical_standby END IF; EXCEPTION

        WHEN OTHERS THEN -- this should never happen
        IF instr ("GGATE" .DDLReplication.raisable_errors, to_char
(SQLCODE, 'S00000')) > 0 THEN
            RAISE;
        END IF;
        "GGATE" .trace_put_line ('DDL', 'Error in obtaining dbrole, open
mode, error ['
                                || SQLERRM || ']');
raise_application_error (-20782,
                                         "GGATE"

.DDLReplication.triggerErrorMessage || ':' || SQLERRM);

END;
END;
/

Looking into "GGATE".DDLReplication.dbQueried , the only reference I see to that is this line:

• query from v$DATABAE dbQueried boolean := FALSE;

In the spec of the package.

So... does that mean that query will always SKIP properly setting the dbRole/dbOpenMode - if dbQueried is *never *null ? Am I reading that correctly?

Chris

On Tue, Apr 30, 2019 at 10:27 AM Chris Taylor < christopherdtaylor1994_at_gmail.com> wrote:

> Although, the trigger itself has this block of code which seems like it
> would not try to update the sequence:
>
> IF NOT (
> (dbRole = 'PRIMARY' OR dbRole = 'LOGICAL STANDBY')
> AND dbOpenMode = 'READ WRITE'
> )
> THEN
> -- do not write any trace even though it should work as this is standby
>                         "GGATE" .DDLReplication.setCtxInfo(-1,-1,-1,-1,-1);
>
> RETURN; -- do not use trigger if not read/write and primary/logical_standby
> END IF;
>
> But then, the Oracle support says this:
>
> *"Also, recommended to have the parameter set in a database role
> trigger."*
>
> That seems to indicate I could have a database role trigger that disables
> system triggers on the standby.   Very puzzling.
>
> Anyone know the impact of disabling system triggers on a Physical Standby?
>
> Chris
>
>
> On Tue, Apr 30, 2019 at 10:17 AM Chris Taylor <
> christopherdtaylor1994_at_gmail.com> wrote:
>
>> env: 12.1.0.2
>>
>> So I've got a weird issue and seems related to this doc:
>>
>> *ORA-00604 And ORA-16000 In ADG Due to the Internal Updates for Sequence
>> GGS_DDL_SEQ (Doc ID 2503143.1) *
>>
>> *BACKGROUND:*
>> *------------------------------------------------------------*
>>
>>  On Friday & today our standby threw an error like this:
>>
>> ORA-00600: internal error code, arguments: [6817], [110546404], [], [],
>> [], [], [], [], [], [], [], []
>>
>> 6817 related to global sequence problem.
>>
>> The incident file shows this:
>> dbkedDefDump(): Starting incident default dumps (flags=0x2, level=3,
>> mask=0x0)
>> [TOC00003]
>> ----- Current SQL Statement for this session (sql_id=gj5cb8uz35v3h) -----
>> SELECT "GGATE" . "GGS_DDL_SEQ" .NEXTVAL FROM DUAL
>>
>>
>> The workaround in the support document talks about disabling the trigger
>> which I don't really want to do on the primary.
>>
>> Would it be reasonable to disable the trigger the on standby for this?
>> Seems like it should be disabled on the standby anyway (or ignored) but
>> doesn't seem to be happening.
>>
>> I'm thinking about coming up with a solution to disable the trigger on
>> the standby unless that doesn't make sense?
>>
>> Chris
>>
>>
>>

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Apr 30 2019 - 17:51:47 CEST