Skip to Main Content

SQL Developer

Announcement

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!

SQL Developer 17.2.0.188.1159-no-jre: Secondary monitor having problems...

Wolf_22Sep 29 2017 — edited Oct 2 2017

I'm not sure what's going on here but my Windows 7 workstation has 2 monitors whereby SQL Developer works fine on the primary but when I drag the SQL Developer window over to the secondary monitor, the entire window "glitches" and doesn't appear to refresh the presented contents of the window and, well, basically freezes the last image of what the window showed. From what I've read in my attempts to find a solution, people refer to this repainting problems?

That being said, I have SQL Developer 17.2.0.188.1159-no-jre and I'm using JRE 1.8.0_144. The video card in the system appears to be an ATI Radeon HD 2400 XT.

Does anyone know what might be happening here or how to fix it?

Comments

Markus Flechtner

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."

so, obviously, the documentation is a little bit contradictory

Regards

Markus

mariam.kupa

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.

CKPT

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.

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.

mariam.kupa

@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

CKPT

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?

mariam.kupa

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. 

mariam.kupa

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;

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

I will keep you updated about this topic. I think it may not be a 18c feature but 19c. Need to confirm that.

Yossi Nixon

Hi,

the same scenario is happening also in 19c

also after changing the same init parameters

mariam.kupa

Too bad.   It seems not available in 19c also.

Thank you for informing us!

Yossi Nixon

Hi,

After some extensive checks, I managed to get this feature working on 19c

Facts:

  1. Create regular table on standby - does not work
  2. Create global temporary table on standby - works
  3. Insert into global temporary table on standby - works
  4. Insert into regular table - works with latency (sending the command to the primary, running in the primary, sending the redo to the standby, apply in the standby)

It works also in  Protecton Mode = Maximum Performance (i.e. asynchronous replication)

mariam.kupa

@"Yossi Nixon" Great!

Could you please write example scripts if you don't mind?

Thank you...

Yossi Nixon

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:  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 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

Version 19.1.0.0.0

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

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

Version 19.1.0.0.0

SYS@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 sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu Dec 27 13:19:47 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

Version 19.1.0.0.0

SYS@orcl:SQL> alter session set container=pdb;

Session altered.

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);

Table created.

== 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 2018

Version 19.1.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

Last Successful login time: Thu Dec 27 2018 13:20:05 +02:00

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Beta

Version 19.1.0.0.0

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

TEST_USER@standby_host:1521/pdb_svc:SQL> select * from regular_table;

      COL1

----------

      1

Elapsed: 00:00:00.00

TEST_USER@standby_host:1521/pdb_svc:SQL> commit;

Commit complete.

Elapsed: 00:00:00.00

TEST_USER@standby_host:1521/pdb_svc:SQL> select * from global_tt1;

no rows selected

Elapsed: 00:00:00.00

TEST_USER@standby_host:1521/pdb_svc:SQL> create global temporary table global_tt2(col1 number);

Table created.

Elapsed: 00:00:01.07

TEST_USER@standby_host:1521/pdb_svc:SQL>  insert into global_tt2(col1) values(1);

1 row created.

Elapsed: 00:00:00.01

== PRIMARY ==

TEST_USER@primary_host/pdb_svc:SQL> desc global_tt2

Name                                                             Null?       Type

----------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------

COL1                                                                   NUMBER

mariam.kupa

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

Yossi Nixon

On the system I tested, this parameter is false in all of the databases participated in the configuration.

Markus.Michalewicz-Oracle
Answer

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

Marked as Answer by mariam.kupa · Sep 27 2020
mariam.kupa

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."

1 - 16
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 30 2017
Added on Sep 29 2017
2 comments
488 views