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

So since it sets a context for the session, a new session has this value as null but then doesn't check it again for the session once it gets set to TRUE after executing once.

Thanks,
Chris

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

> 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