1 2 Previous Next 16 Replies Latest reply on Jun 27, 2017 8:12 AM by Gaz in Oz

    SQL Developer 4.1.5 not connected to an oracle dg 11.2.4.0 physical standby database?

    Quanwen Zhao

      Why my  SQL Developer 4.1.5 long times not connected to oracle dg physical standby database? But with PL/SQL Developer connected to it,and very fast.

      SQL Developer and PL/SQL Developer with the same tnsnames.ora file,

      now ,with sql developer to connect,and in listener.log file show as follows:

       

      Fri Jun 23 12:07:31 2017

      23-JUN-2017 12:07:31 * (CONNECT_DATA=(CID=(PROGRAM=SQL Developer)(HOST=__jdbc__)(USER=zqw))(SERVER=DEDICATED)(SERVICE_NAME=standby28)) * (ADDRESS=(PROTOCOL=tcp)(HOST=10.100.60.61)(PORT=63076)) * establish * standby28 * 0

      Fri Jun 23 12:08:16 2017

      23-JUN-2017 12:08:16 * service_update * standby28 * 0

       

      that indicate connect to oracle database with SQL Developer successfully,but why connect progress bar is running?

       

      and I connect another oracle database with SQL Developer sucessfully, or I change other SQL Developer client version connect unsuccessfully too!

       

      Thanks you! Please give some advice.

        • 1. Re: SQL Developer 4.1.5 not connected to an oracle dg 11.2.4.0 physical standby database?
          Gaz in Oz

          What does your sql developer connection string look like?

          • 2. Re: SQL Developer 4.1.5 not connected to an oracle dg 11.2.4.0 physical standby database?
            Quanwen Zhao

            STANDBY28 =

              (DESCRIPTION =

                (ADDRESS = (PROTOCOL = TCP)(HOST = standby28)(PORT = 1521))

                (CONNECT_DATA =

                  (SERVER = DEDICATED)

                  (SERVICE_NAME = standby28)

                )

              )

            • 3. Re: SQL Developer 4.1.5 not connected to an oracle dg 11.2.4.0 physical standby database?
              Gaz in Oz

              That is a tnsnames entry.

              In sql developer, what does your connection "proporties" look like?

              Connections tab-> Right-mouse-click connection -> properties.

              • 5. Re: SQL Developer 4.1.5 not connected to an oracle dg 11.2.4.0 physical standby database?
                Gaz in Oz

                OK, so the Test button confirms details are good, Check "Save password"

                Does that work?

                • 6. Re: SQL Developer 4.1.5 not connected to an oracle dg 11.2.4.0 physical standby database?
                  Quanwen Zhao

                  Yeah,test is successfully,but really when connect to,progress bar is running as like my first screenshot.

                  • 7. Re: SQL Developer 4.1.5 not connected to an oracle dg 11.2.4.0 physical standby database?
                    Gaz in Oz

                    ...download latest and try.

                    1 person found this helpful
                    • 8. Re: SQL Developer 4.1.5 not connected to an oracle dg 11.2.4.0 physical standby database?
                      Quanwen Zhao

                      Now,I try to download latest version of SQL Developer (Version 4.2.0.17.089.1709, Updated April 12, 2017) ,and problem still exists.

                      Then I decide to reboot dataguard physical standby database and retry it,I use 'oradebug 10046 events' to trace my operation(include 'shutdown immediate'),

                       

                      SYS@standby28> oradebug setmypid;

                      Statement processed.

                      SYS@standby28> oradebug event 10046 trace name context forever,level 12;

                      Statement processed.

                      SYS@standby28> oradebug tracefile_name;

                      /u01/app/oracle/diag/rdbms/standby28/standby28/trace/standby28_ora_23943.trc

                      SYS@standby28> alter database recover managed standby database cancel;

                       

                      Database altered.

                       

                      SYS@standby28> select open_mode from v$database;

                       

                      OPEN_MODE

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

                      READ ONLY

                       

                      SYS@standby28> shutdown immediate;

                       

                      but wait long time,and I found that there are some wait event inside file '/u01/app/oracle/diag/rdbms/standby28/standby28/trace/standby28_ora_23943.trc' .

                      eg.

                      *** 2017-06-26 10:35:16.462

                      WAIT #0: nam='SQL*Net message from client' ela= 21567051 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1498444516462879

                      XCTEND rlbk=0, rd_only=1, tim=1498444516464326

                      =====================

                      PARSING IN CURSOR #140202713873512 len=54 dep=0 uid=0 oct=35 lid=0 tim=1498444516465654 hv=1340289429 ad='251ff20e60' sqlid='g2d5pw97y6ccp'

                      alter database recover managed standby database cancel

                      END OF STMT

                      PARSE #140202713873512:c=1999,e=2499,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1498444516465647

                      WAIT #140202713873512: nam='Disk file operations I/O' ela= 238 FileOperation=2 fileno=0 filetype=1 obj#=-1 tim=1498444516466758

                      WAIT #140202713873512: nam='Disk file operations I/O' ela= 53 FileOperation=2 fileno=1 filetype=1 obj#=-1 tim=1498444516466888

                      WAIT #140202713873512: nam='control file sequential read' ela= 42 file#=0 block#=1 blocks=1 obj#=-1 tim=1498444516467067

                      WAIT #140202713873512: nam='control file sequential read' ela= 11 file#=0 block#=16 blocks=1 obj#=-1 tim=1498444516467199

                      WAIT #140202713873512: nam='control file sequential read' ela= 15 file#=0 block#=18 blocks=1 obj#=-1 tim=1498444516467297

                      Managed Recovery: Cancel posted.

                      *** 2017-06-26 10:35:24.855

                      WAIT #140202713873512: nam='SQL*Net message from client' ela= 7386954 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1498444524855733

                      CLOSE #140202713873512:c=0,e=23,dep=0,type=0,tim=1498444524855998

                      =====================

                      PARSING IN CURSOR #140202713873512 len=32 dep=0 uid=0 oct=3 lid=0 tim=1498444524881202 hv=3556441076 ad='255fe7f5c0' sqlid='a5xcbum9zpyzn'

                      select open_mode from v$database

                      END OF STMT

                      PARSE #140202713873512:c=5000,e=25062,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=735420252,tim=1498444524881199

                      EXEC #140202713873512:c=0,e=87,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=735420252,tim=1498444524881454

                      WAIT #140202713873512: nam='SQL*Net message to client' ela= 4 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1498444524881522

                      WAIT #140202713873512: nam='control file sequential read' ela= 25 file#=0 block#=1 blocks=1 obj#=-1 tim=1498444524881692

                      WAIT #140202713873512: nam='control file sequential read' ela= 11 file#=0 block#=16 blocks=1 obj#=-1 tim=1498444524881745

                      WAIT #140202713873512: nam='control file sequential read' ela= 10 file#=0 block#=18 blocks=1 obj#=-1 tim=1498444524881777

                      WAIT #140202713873512: nam='control file sequential read' ela= 13 file#=0 block#=1 blocks=1 obj#=-1 tim=1498444524882128

                      WAIT #140202713873512: nam='control file sequential read' ela= 8 file#=0 block#=16 blocks=1 obj#=-1 tim=1498444524882189

                      WAIT #140202713873512: nam='control file sequential read' ela= 6 file#=0 block#=18 blocks=1 obj#=-1 tim=1498444524882218

                      WAIT #140202713873512: nam='control file sequential read' ela= 10 file#=0 block#=281 blocks=1 obj#=-1 tim=1498444524882250

                      FETCH #140202713873512:c=0,e=755,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=735420252,tim=1498444524882343

                      WAIT #140202713873512: nam='SQL*Net message from client' ela= 442 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1498444524882855

                      FETCH #140202713873512:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=735420252,tim=1498444524882950

                      STAT #140202713873512 id=1 cnt=1 pid=0 pos=1 obj=0 op='MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=727 us cost=0 size=2600 card=100)'

                      STAT #140202713873512 id=2 cnt=1 pid=1 pos=1 obj=0 op='FIXED TABLE FULL X$KCCDI (cr=0 pr=0 pw=0 time=312 us cost=0 size=26 card=1)'

                      STAT #140202713873512 id=3 cnt=1 pid=1 pos=2 obj=0 op='BUFFER SORT (cr=0 pr=0 pw=0 time=408 us cost=0 size=0 card=100)'

                      STAT #140202713873512 id=4 cnt=1 pid=3 pos=1 obj=0 op='FIXED TABLE FULL X$KCCDI2 (cr=0 pr=0 pw=0 time=347 us cost=0 size=0 card=100)'

                      *** 2017-06-26 10:35:30.718

                      WAIT #140202713873512: nam='SQL*Net message from client' ela= 5830213 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=1498444530718105

                      CLOSE #140202713873512:c=0,e=18,dep=0,type=0,tim=1498444530718284

                      WAIT #0: nam='rdbms ipc reply' ela= 152 from_process=28 timeout=120 p3=0 obj#=-1 tim=1498444530718821

                      WAIT #0: nam='control file sequential read' ela= 16 file#=0 block#=1 blocks=1 obj#=-1 tim=1498444530727679

                      WAIT #0: nam='control file sequential read' ela= 13 file#=1 block#=1 blocks=1 obj#=-1 tim=1498444530727722

                      WAIT #0: nam='control file sequential read' ela= 7 file#=0 block#=16 blocks=1 obj#=-1 tim=1498444530727743

                      WAIT #0: nam='control file sequential read' ela= 6 file#=0 block#=18 blocks=1 obj#=-1 tim=1498444530727761

                      WAIT #0: nam='Disk file operations I/O' ela= 45 FileOperation=2 fileno=201 filetype=2 obj#=-1 tim=1498444530727866

                      WAIT #0: nam='Disk file operations I/O' ela= 17 FileOperation=2 fileno=202 filetype=2 obj#=-1 tim=1498444530727907

                      WAIT #0: nam='Disk file operations I/O' ela= 16 FileOperation=2 fileno=203 filetype=2 obj#=-1 tim=1498444530727938

                      WAIT #0: nam='rdbms ipc reply' ela= 106 from_process=23 timeout=120 p3=0 obj#=-1 tim=1498444530728114

                      *** 2017-06-26 10:35:30.728 1201 krsv.c

                      krsv_fg_so_init: Cleanup action 1

                      WAIT #0: nam='class slave wait' ela= 2 slave id=0 p2=0 p3=0 obj#=-1 tim=1498444530747908

                      WAIT #0: nam='class slave wait' ela= 1 slave id=0 p2=0 p3=0 obj#=-1 tim=1498444530753939

                      WAIT #0: nam='class slave wait' ela= 0 slave id=0 p2=0 p3=0 obj#=-1 tim=1498444530753984

                      WAIT #0: nam='class slave wait' ela= 0 slave id=0 p2=0 p3=0 obj#=-1 tim=1498444530753999

                      WAIT #0: nam='class slave wait' ela= 0 slave id=0 p2=0 p3=0 obj#=-1 tim=1498444530754031

                      WAIT #0: nam='class slave wait' ela= 0 slave id=0 p2=0 p3=0 obj#=-1 tim=1498444530754041

                      WAIT #0: nam='class slave wait' ela= 1 slave id=0 p2=0 p3=0 obj#=-1 tim=1498444530786286

                       

                      *** 2017-06-26 10:35:30.786

                      Stopping background process MMNL

                       

                      *** 2017-06-26 10:35:31.787

                      WAIT #0: nam='process shutdown' ela= 1000901 type=1 process#=294 waited=10 obj#=-1 tim=1498444531787587

                      WAIT #0: nam='class slave wait' ela= 5 slave id=0 p2=0 p3=0 obj#=-1 tim=1498444532788806

                      WAIT #0: nam='opishd' ela= 8 p1=0 p2=0 p3=0 obj#=-1 tim=1498444532831532

                       

                      *** 2017-06-26 10:35:33.841

                      WAIT #0: nam='opishd' ela= 1000690 p1=0 p2=0 p3=0 obj#=-1 tim=1498444533841630

                       

                      *** 2017-06-26 10:35:34.843

                      WAIT #0: nam='opishd' ela= 1000316 p1=0 p2=0 p3=0 obj#=-1 tim=1498444534843623

                       

                      *** 2017-06-26 10:35:35.845

                      WAIT #0: nam='opishd' ela= 1000996 p1=0 p2=0 p3=0 obj#=-1 tim=1498444535845634

                       

                      *** 2017-06-26 10:35:36.847

                      WAIT #0: nam='opishd' ela= 1000430 p1=0 p2=0 p3=0 obj#=-1 tim=1498444536847617

                       

                      *** 2017-06-26 10:35:37.849

                      WAIT #0: nam='opishd' ela= 1001057 p1=0 p2=0 p3=0 obj#=-1 tim=1498444537849643

                       

                      *** 2017-06-26 10:35:38.851

                      WAIT #0: nam='opishd' ela= 1001005 p1=0 p2=0 p3=0 obj#=-1 tim=1498444538851638

                      ......

                       

                      At this time,I've no idea! I cancel this operation about 'shutdown immediate',

                      I feel like one problem is not resolved,but also found another problem,please help me,My idol !

                      • 9. Re: SQL Developer 4.1.5 not connected to an oracle dg 11.2.4.0 physical standby database?
                        Gaz in Oz

                        How long did you leave the db to shutdown? It may take a few moments...

                         

                        Your original issue was about connecting to the standby db using sqldeveloper. I suggest you use what ever tool you used to shutdown the db as your preferred tool to administer the db and forget about using sqldeveloper.

                        • 10. Re: SQL Developer 4.1.5 not connected to an oracle dg 11.2.4.0 physical standby database?
                          Quanwen Zhao

                          Ok,Thanks!

                          But how to solve two or more about wait events that found in the process of shutdown immediate

                          • 11. Re: SQL Developer 4.1.5 not connected to an oracle dg 11.2.4.0 physical standby database?
                            Gaz in Oz

                            How long did you leave the db to shutdown? It may take a few moments...

                            Any messages in the alert log relating to the shutdown?

                            • 12. Re: SQL Developer 4.1.5 not connected to an oracle dg 11.2.4.0 physical standby database?
                              Quanwen Zhao

                              Mon Jun 26 10:35:30 2017

                              Shutting down instance (immediate)

                              Shutting down instance: further logons disabled

                              Stopping background process MMNL

                              Stopping background process MMON

                              License high water mark = 98

                              Mon Jun 26 10:40:32 2017

                              Active call for process 91190 user 'oracle' program 'oracle@orcl26 (PR00)'

                              Active call for process 29005 user 'oracle' program 'oracle@orcl26'

                              SHUTDOWN: waiting for active calls to complete.

                              • 13. Re: SQL Developer 4.1.5 not connected to an oracle dg 11.2.4.0 physical standby database?
                                Gaz in Oz

                                The alert log tells you why it is waiting.

                                SHUTDOWN: waiting for active calls to complete.

                                Oracle is waiting for transaction/s to either rollback or commit.

                                 

                                To force the db to shutdown login to db: sqlplus / as sysdba", force shutdown, bring it up in restricted mode and then do a "clean" shutdown:

                                SQL> shutdown abort;

                                ...

                                SQL> startup restrict;

                                ...

                                SQL> shutdown normal;

                                ...

                                1 person found this helpful
                                1 2 Previous Next