For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Wanting to use the files window to manage version control but when I expand folders, it seems to move the folder I expanded to the top or bottom of the window. Is this normal?
Hi,
some lines later the documentation states
"Data definition language (DDL) operations on global temporary tables (for example, CREATE and DROP) must still be issued from the primary database. DDL changes are visible on the standby when it catches up with the primary database."
CREATE
DROP
so, obviously, the documentation is a little bit contradictory
Regards
Markus
Unfortunately yes, that is what I am trying to clarify.
Oracle support does not say that it is not possible. Their answer is the following:
"Found this internal Bug 27548539 : CREATE & DROP OF GLOBAL TEMP TABLE ON ADG IS NOT ENABLED BY DEFAULT
Please provide the answer for below in order to get a backport for thsi "
But still waiting for the patch for my version.
mariam.kupa wrote:Unfortunately yes, that is what I am trying to clarify. Oracle support does not say that it is not possible. Their answer is the following:"Found this internal Bug 27548539 : CREATE & DROP OF GLOBAL TEMP TABLE ON ADG IS NOT ENABLED BY DEFAULT Please provide the answer for below in order to get a backport for thsi "But still waiting for the patch for my version.
mariam.kupa wrote:
I have tested initially in 12.1. We have to create DDLs only on production and DML allowed on standby any time.
Here the main concern is, based on application demand manually we have to create GTT every time and this feature should be allowed to create on standby, that will be piece of cake for sure.
@CKPT thank you for your answer. Oracle in 18c new features mentioned that they extended that feature by allowing DDL on standby. In 12.1 and 12.2 you are able to run DML on standby on GTT and not DDL, but with 18c it should be different. We should be able to run DDL on 18c standby. Because Oracle says like that
Yes, i read the link you mentioned in your post. so everytime user try to create DDL then the request will be sent to Primary.
So if there is any disconnection or LAG then this feature may be not going to work.
As per below info, Yes its definitely BUG.
Global temporary tables can be created on, and dropped from, Active Data Guard standby databases. The DDL for these operations is transparently redirected to the primary database. The Active Data Guard session then waits until the corresponding changes are shipped and applied to the Active Data Guard standby. The following is an example of creating a global temporary table:
But wait wait.... There is another note and ti says DDL still we have to execute on Primary. This is really not good, two statements in Oracle documentation.
Data definition language (DDL) operations on global temporary tables (for example, CREATE and DROP) must still be issued from the primary database. DDL changes are visible on the standby when it catches up with the primary databas
Dear@"Markus.Michalewicz-Oracle" ,
I hope it was an excellent OOW18. Can you please check the question and let us know your inputs?
Thank you @"CKPT" for agreeing with me and tagging Markus, I appreciate your interest in this topic.
I have read one book about 18c new features and the author created GTT on primary and inserted data from standby(not 18c feature, was available in 12c also), I think he also got confused.
I was asked to set the following parameters, to make this work:
1. set _alter_adg_redirect_behavior to -> allow_altersystem: Allow redirection of ALTER SYSTEM, when issued from PDB with both scope=SPFILE and db_unique_name specified. 2. alter system set ADG_REDIRECT_DML=true scope=spfile;
1. set _alter_adg_redirect_behavior to -> allow_altersystem: Allow redirection of ALTER SYSTEM, when issued from PDB with both scope=SPFILE and db_unique_name specified.
2. alter system set ADG_REDIRECT_DML=true scope=spfile;
But unfortunately, it does not work so. After changing these parameters I was not able to start instance:
SYS @ shcat > startup pfile ='/tmp/mypfile'; LRM-00101: unknown parameter name 'ADG_REDIRECT_DML' LRM-00101: unknown parameter name '_alter_adg_redirect_behavior' ORA-01078: failure in processing system parameters
SYS @ shcat > startup pfile ='/tmp/mypfile';
LRM-00101: unknown parameter name 'ADG_REDIRECT_DML'
LRM-00101: unknown parameter name '_alter_adg_redirect_behavior'
ORA-01078: failure in processing system parameters
I will keep you updated about this topic. I think it may not be a 18c feature but 19c. Need to confirm that.
the same scenario is happening also in 19c
also after changing the same init parameters
Too bad. It seems not available in 19c also.
Thank you for informing us!
After some extensive checks, I managed to get this feature working on 19c
Facts:
It works also in Protecton Mode = Maximum Performance (i.e. asynchronous replication)
@"Yossi Nixon" Great!
Could you please write example scripts if you don't mind?
Thank you...
The current Active Data guard configuration is MaxAvailability, but the same behavior is when configuring MaxPerformance
DGMGRL> show configuration Configuration - PRODCONF Protection Mode: MaxAvailability Members: orcl - Primary database fdb - Far sync instance sdb - Physical standby database Fast-Start Failover: DisabledConfiguration Status:SUCCESS (status updated 49 seconds ago)DGMGRL> show database orcl RedoRoutes RedoRoutes = '(LOCAL : ( FDB PRIORITY=1, SDB PRIORITY=2 ) )'DGMGRL> show far_sync fdb RedoRoutes RedoRoutes = '( ORCL : SDB ASYNC)'DGMGRL> show far_sync fdb LogXptMode LogXptMode = 'SYNC'DGMGRL> show database sdb LogXptMode LogXptMode = 'ASYNC'DGMGRL> show database sdbDatabase - sdb Role: PHYSICAL STANDBY Intended State: APPLY-ON Transport Lag: 0 seconds (computed 1 second ago) Apply Lag: 0 seconds (computed 1 second ago) Average Apply Rate: 4.00 KByte/s Real Time Query: ON Instance(s): orclDatabase Status:SUCCESS
DGMGRL> show configuration
Configuration - PRODCONF
Protection Mode: MaxAvailability
Members:
orcl - Primary database
fdb - Far sync instance
sdb - Physical standby database
Fast-Start Failover: Disabled
Configuration Status:
SUCCESS (status updated 49 seconds ago)
DGMGRL> show database orcl RedoRoutes
RedoRoutes = '(LOCAL : ( FDB PRIORITY=1, SDB PRIORITY=2 ) )'
DGMGRL> show far_sync fdb RedoRoutes
RedoRoutes = '( ORCL : SDB ASYNC)'
DGMGRL> show far_sync fdb LogXptMode
LogXptMode = 'SYNC'
DGMGRL> show database sdb LogXptMode
LogXptMode = 'ASYNC'
DGMGRL> show database sdb
Database - sdb
Role: PHYSICAL STANDBY
Intended State: APPLY-ON
Transport Lag: 0 seconds (computed 1 second ago)
Apply Lag: 0 seconds (computed 1 second ago)
Average Apply Rate: 4.00 KByte/s
Real Time Query: ON
Instance(s):
orcl
Database Status:
SUCCESS
== PRIMARY ==
$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 27 13:15:51 2018Version 19.1.0.0.0Copyright (c) 1982, 2018, Oracle. All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - BetaVersion 19.1.0.0.0SYS@orcl:SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB READ WRITE NOSYS@orcl:SQL> alter session set container=pdb;Session altered.SYS@orcl:SQL> create user test_user identified by test_user;User created.SYS@orcl:SQL> grant connect, create session, create table to test_user;Grant succeeded.SYS@orcl:SQL> alter user test_user quota unlimited on users;User altered.SYS@orcl:SQL> connect test_user/test_user@orcl.axxana.local[oracle@primary_host(orcl) ~]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 27 13:19:34 2018Version 19.1.0.0.0Copyright (c) 1982, 2018, Oracle. All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - BetaVersion 19.1.0.0.0SYS@orcl:SQL> alter session set container=pdb;Session altered.SYS@orcl:SQL> connect test_user/test_user@pdb_svc[oracle@primary_host(orcl) ~]$ sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 27 13:19:47 2018Version 19.1.0.0.0Copyright (c) 1982, 2018, Oracle. All rights reserved.Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - BetaVersion 19.1.0.0.0SYS@orcl:SQL> alter session set container=pdb;Session altered.SYS@orcl:SQL> connect test_user/test_user@primary_host/pdb_svcConnected.TEST_USER@primary_host/pdb_svc:SQL> create table regular_table(col1 number);Table created.TEST_USER@primary_host/pdb_svc:SQL> create global temporary table global_tt1(col1 number);Table created.
$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 27 13:15:51 2018
Version 19.1.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta
SYS@orcl:SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB READ WRITE NO
SYS@orcl:SQL> alter session set container=pdb;
Session altered.
SYS@orcl:SQL> create user test_user identified by test_user;
User created.
SYS@orcl:SQL> grant connect, create session, create table to test_user;
Grant succeeded.
SYS@orcl:SQL> alter user test_user quota unlimited on users;
User altered.
SYS@orcl:SQL> connect test_user/test_user@orcl.axxana.local
[oracle@primary_host(orcl) ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 27 13:19:34 2018
SYS@orcl:SQL> connect test_user/test_user@pdb_svc
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 27 13:19:47 2018
SYS@orcl:SQL> connect test_user/test_user@primary_host/pdb_svc
Connected.
TEST_USER@primary_host/pdb_svc:SQL> create table regular_table(col1 number);
Table created.
TEST_USER@primary_host/pdb_svc:SQL> create global temporary table global_tt1(col1 number);
== STANDBY ==
$ sqlplus test_user/test_user@standby_host:1521/pdb_svc SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 27 13:24:28 2018Version 19.1.0.0.0Copyright (c) 1982, 2018, Oracle. All rights reserved.Last Successful login time: Thu Dec 27 2018 13:20:05 +02:00Connected to:Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - BetaVersion 19.1.0.0.0TEST_USER@standby_host:1521/pdb_svc:SQL> set timing onTEST_USER@standby_host:1521/pdb_svc:SQL> insert into regular_table(col1) values(1);1 row created.Elapsed: 00:00:01.23TEST_USER@standby_host:1521/pdb_svc:SQL> select * from regular_table; COL1---------- 1Elapsed: 00:00:00.03TEST_USER@standby_host:1521/pdb_svc:SQL> commit;Commit complete.Elapsed: 00:00:01.06TEST_USER@standby_host:1521/pdb_svc:SQL> select * from regular_table; COL1---------- 1Elapsed: 00:00:00.00TEST_USER@standby_host:1521/pdb_svc:SQL> commit;Commit complete.Elapsed: 00:00:00.00TEST_USER@standby_host:1521/pdb_svc:SQL> select * from global_tt1;no rows selectedElapsed: 00:00:00.00TEST_USER@standby_host:1521/pdb_svc:SQL> create global temporary table global_tt2(col1 number);Table created.Elapsed: 00:00:01.07TEST_USER@standby_host:1521/pdb_svc:SQL> insert into global_tt2(col1) values(1);1 row created.Elapsed: 00:00:00.01
$ sqlplus test_user/test_user@standby_host:1521/pdb_svc
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 27 13:24:28 2018
Last Successful login time: Thu Dec 27 2018 13:20:05 +02:00
TEST_USER@standby_host:1521/pdb_svc:SQL> set timing on
TEST_USER@standby_host:1521/pdb_svc:SQL> insert into regular_table(col1) values(1);
1 row created.
Elapsed: 00:00:01.23
TEST_USER@standby_host:1521/pdb_svc:SQL> select * from regular_table;
COL1
----------
1
Elapsed: 00:00:00.03
TEST_USER@standby_host:1521/pdb_svc:SQL> commit;
Commit complete.
Elapsed: 00:00:01.06
Elapsed: 00:00:00.00
TEST_USER@standby_host:1521/pdb_svc:SQL> select * from global_tt1;
no rows selected
TEST_USER@standby_host:1521/pdb_svc:SQL> create global temporary table global_tt2(col1 number);
Elapsed: 00:00:01.07
TEST_USER@standby_host:1521/pdb_svc:SQL> insert into global_tt2(col1) values(1);
Elapsed: 00:00:00.01
TEST_USER@primary_host/pdb_svc:SQL> desc global_tt2 Name Null? Type ----------------------------------------------------------------------------------------------- -------- ---------------------------------------------------------------- COL1 NUMBER
TEST_USER@primary_host/pdb_svc:SQL> desc global_tt2
Name Null? Type
----------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------
COL1 NUMBER
@Yossi Nixon Thank you very much for your informative answer!
I am not able to identify if you set parameter temp_undo_enabled is it necessary ?
On the system I tested, this parameter is false in all of the databases participated in the configuration.
Thank you for asking.
Sorry for the late reply, but I seem not to have received a notification (or too many - not sure) on this request.
Please, note that in the section that you linked: https://docs.oracle.com/en/database/oracle/oracle-database/18/sbydb/managing-oracle-data-guard-physical-standby-database… there is no talk about DDL anymore. Instead it seems to - more correctly - talk about DML Operations:
DML Operations
When a global temporary table is changed by a DML operation, the change itself does not generate redo because it is only a temporary table. But the undo generated for the change does in turn generate redo. Redo generation on a read-only database (such as an Active Data Guard standby) is not allowed.
In which case, there is no contradiction to the note below, as the DDL is still to be issues as stated in the note.
Thus, I guess this was a DOC BUG and got fixed in the meantime.
Please, let me know, if I misread. Otherwise, thank you for bringing this to my attention.
Thanks, Markus
Thank you @"Markus.Michalewicz-Oracle" for you answer and time.
Yes, it seems documentation does not contain this sentence.
What do you think is not the following sentence also confusing ?
"10.2.2.1 Global Temporary Tables on Active Data Guard Instances
DML and DDL operations are allowed on temporary tables on Oracle Active Data Guard instances.
"
and at the end, we see this note:
Note:
Data definition language (DDL) operations on global temporary tables (for example, CREATE and DROP) must still be issued from the primary database. DDL changes are visible on the standby when it catches up with the primary database."