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@xxxxxxxxx> 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@xxxxxxxxx> 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
