Thinking Out Loud

April 28, 2017

Bug 18411339 – Low performance V$ARCHIVE_GAP (11.2.0.4) fix 12.2.0.1

Filed under: 11g,12c,Dataguard — mdinh @ 12:31 am

Just came across bug from 11.2.0.4 not fixed until 12.2 base release. Seriously Oracle?
In the test case below, it looks to have only affected 11.2.0.4 – 64bit for AIX Version 7.1 since I recall this was not an issues for Linux.

11.2.0.4.0
select * from v$archive_gap;
Elapsed: 00:01:48.93

12.1.0.2.0
select * from v$archive_gap;
Elapsed: 00:00:06.60

Work Around

select USERENV('Instance'), high.thread#, low.lsq, high.hsq
 from
  (select a.thread#, rcvsq, min(a.sequence#)-1 hsq
   from v$archived_log a,
        (select lh.thread#, lh.resetlogs_change#, max(lh.sequence#) rcvsq
           from v$log_history lh, v$database_incarnation di
          where lh.resetlogs_time = di.resetlogs_time
            and lh.resetlogs_change# = di.resetlogs_change#
            and di.status = 'CURRENT'
            and lh.thread# is not null
            and lh.resetlogs_change# is not null
            and lh.resetlogs_time is not null
         group by lh.thread#, lh.resetlogs_change#
        ) b
   where a.thread# = b.thread#
     and a.resetlogs_change# = b.resetlogs_change#
     and a.sequence# > rcvsq
   group by a.thread#, rcvsq) high,
 (select srl_lsq.thread#, nvl(lh_lsq.lsq, srl_lsq.lsq) lsq
   from
     (select thread#, min(sequence#)+1 lsq
      from
        v$log_history lh, x$kccfe fe, v$database_incarnation di
      where to_number(fe.fecps) <= lh.next_change# and to_number(fe.fecps) >= lh.first_change#
        and fe.fedup!=0 and bitand(fe.festa, 12) = 12
        and di.resetlogs_time = lh.resetlogs_time
        and lh.resetlogs_change# = di.resetlogs_change#
        and di.status = 'CURRENT'
      group by thread#) lh_lsq,
     (select thread#, max(sequence#)+1 lsq
      from
        v$log_history
      where (select min( to_number(fe.fecps))
             from x$kccfe fe
             where fe.fedup!=0 and bitand(fe.festa, 12) = 12)
      >= next_change#
      group by thread#) srl_lsq
   where srl_lsq.thread# = lh_lsq.thread#(+)
  ) low
 where low.thread# = high.thread#
 and lsq < = hsq and hsq > rcvsq;

December 9, 2016

GoldenGate Capture using Active DataGuard

Filed under: Dataguard,GoldenGate — mdinh @ 3:38 am

How to Configure Extract on Standalone Active Data Guard System if Primary is RAC Multipe Nodes (Doc ID 1962336.1)

Configuring Classic Capture in Oracle Active Data Guard Only Mode
http://docs.oracle.com/goldengate/1212/gg-winux/GIORA/classic_capture.htm#GIORA997

dgmgrl from OS command line

Filed under: 11g,Dataguard,dgmgrl — mdinh @ 3:32 am

Quick and dirty post from what I just learned.

Who said you can’t teach old dogs new tricks!

oracle@arrow1:HAWKA:/home/oracle
$ dgmgrl / "show database hawka"
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Database - hawka

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    HAWKA

Database Status:
SUCCESS

oracle@arrow1:HAWKA:/home/oracle
$ dgmgrl / "show database hawkb"
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Database - hawkb

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 2 seconds ago)
  Apply Rate:      1.11 MByte/s
  Real Time Query: ON
  Instance(s):
    HAWKB

Database Status:
SUCCESS

oracle@arrow1:HAWKA:/home/oracle
$

Example from 4 Nodes RAC DG Configuration:

Real name and password were replaced. All others are the same.

host=white01/02/03/04
db_name=hawk
db_unique_name=hawka
instance_name=hawk1/2/3/4
ORACLE_SID=hawk1/2/3/4

++++++++++

host=black01/02/03/04
db_name=hawk
db_unique_name=hawkb
instance_name=hawk1/2/3/4
ORACLE_SID=hawk1/2/3/4

$ dgmgrl / "show configuration"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawka - Primary database
    hawkb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

$ dgmgrl / "show database hawka"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Database - hawka

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    hawk1
    hawk2
    hawk3
    hawk4

Database Status:
SUCCESS

$ dgmgrl / "show database hawkb"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Database - hawkb

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       28 minutes 9 seconds
  Real Time Query: ON
  Instance(s):
    hawk1
    hawk2 (apply instance)
    hawk3
    hawk4

Database Status:
SUCCESS

$ dgmgrl / "show database verbose hawka"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Database - hawka

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    hawk1
    hawk2
    hawk3
    hawk4

  Properties:
    DGConnectIdentifier             = 'hawka'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '1200'
    LogArchiveMaxProcesses          = '16'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName(*)
    StaticConnectIdentifier(*)
    StandbyArchiveLocation(*)
    AlternateLocation(*)
    LogArchiveTrace(*)
    LogArchiveFormat(*)
    TopWaitEvents(*)
    (*) - Please check specific instance for the property value

Database Status:
SUCCESS

$ dgmgrl / "show database verbose hawkb"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Database - hawkb

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       15 minutes 53 seconds
  Real Time Query: ON
  Instance(s):
    hawk1
    hawk2 (apply instance)
    hawk3
    hawk4

  Properties:
    DGConnectIdentifier             = 'hawkb'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '60'
    NetTimeout                      = '180'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = 'hawk2'
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = '16'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '600'
    LogArchiveMaxProcesses          = '16'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = '+DATA/hawka, +DATA/hawkb'
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    SidName(*)
    StaticConnectIdentifier(*)
    StandbyArchiveLocation(*)
    AlternateLocation(*)
    LogArchiveTrace(*)
    LogArchiveFormat(*)
    TopWaitEvents(*)
    (*) - Please check specific instance for the property value

Database Status:
SUCCESS

$ dgmgrl / "show instance verbose hawk1 on database hawka"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Instance 'hawk1' of database 'hawka'

  Host Name: white01.local
  PFILE:     
  Properties:
    SidName                         = 'hawk1'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.27.42)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawka_DGMGRL)(INSTANCE_NAME=hawk1)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

$ dgmgrl / "show instance verbose hawk2 on database hawka"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Instance 'hawk2' of database 'hawka'

  Host Name: white02.local
  PFILE:     
  Properties:
    SidName                         = 'hawk2'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.27.44)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawka_DGMGRL)(INSTANCE_NAME=hawk2)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

$ dgmgrl / "show instance verbose hawk3 on database hawka"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Instance 'hawk3' of database 'hawka'

  Host Name: white03.local
  PFILE:     
  Properties:
    SidName                         = 'hawk3'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.27.46)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawka_DGMGRL)(INSTANCE_NAME=hawk3)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

$ dgmgrl / "show instance verbose hawk4 on database hawka"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Instance 'hawk4' of database 'hawka'

  Host Name: white04.local
  PFILE:     
  Properties:
    SidName                         = 'hawk4'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.27.48)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawka_DGMGRL)(INSTANCE_NAME=hawk4)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

$ dgmgrl / "show instance verbose hawk1 on database hawkb"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Instance 'hawk1' of database 'hawkb'

  Host Name: black01.local
  PFILE:     
  Properties:
    SidName                         = 'hawk1'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.80.27.42)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawkb_DGMGRL)(INSTANCE_NAME=hawk1)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

$ dgmgrl / "show instance verbose hawk2 on database hawkb"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Instance 'hawk2' of database 'hawkb'

  Host Name: black02.local
  PFILE:     
  Properties:
    SidName                         = 'hawk2'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.80.27.44)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawkb_DGMGRL)(INSTANCE_NAME=hawk2)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

$ dgmgrl / "show instance verbose hawk3 on database hawkb"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Instance 'hawk3' of database 'hawkb'

  Host Name: black03.local
  PFILE:     
  Properties:
    SidName                         = 'hawk3'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.80.27.46)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawkb_DGMGRL)(INSTANCE_NAME=hawk3)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

$ dgmgrl / "show instance verbose hawk4 on database hawkb"
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.

Instance 'hawk4' of database 'hawkb'

  Host Name: black04.local
  PFILE:     
  Properties:
    SidName                         = 'hawk4'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.80.27.48)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawkb_DGMGRL)(INSTANCE_NAME=hawk4)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '0'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Instance Status:
SUCCESS

Verify connectivity using DGConnectIdentifier

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 9 14:19:51 2016

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

@> connect sys/oracle@hawka as sysdba
Connected.
SQL> show parameter db%name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      
db_name                              string      hawk
db_unique_name                       string      hawka

SQL> connect sys/oracle @hawkb as sysdba
Connected.

SQL> show parameter db%name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string      
db_name                              string      hawk
db_unique_name                       string      hawkb

SQL> exit

Verify connectivity using StaticConnectIdentifier

$ grep -i hawka_DGMGRL test_dg_static_connect_id.sql|grep HOST
connect sys/oracle@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.27.42)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawka_DGMGRL)(INSTANCE_NAME=hawk1)(SERVER=DEDICATED)))' as sysdba
connect sys/oracle@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.27.44)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawka_DGMGRL)(INSTANCE_NAME=hawk2)(SERVER=DEDICATED)))' as sysdba
connect sys/oracle@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.27.46)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawka_DGMGRL)(INSTANCE_NAME=hawk3)(SERVER=DEDICATED)))' as sysdba
connect sys/oracle@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.30.27.48)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawka_DGMGRL)(INSTANCE_NAME=hawk4)(SERVER=DEDICATED)))' as sysdba

$ grep -i hawkb_DGMGRL test_dg_static_connect_id.sql|grep HOST
connect sys/oracle@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.80.27.42)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawkb_DGMGRL)(INSTANCE_NAME=hawk1)(SERVER=DEDICATED)))' as sysdba
connect sys/oracle@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.80.27.44)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawkb_DGMGRL)(INSTANCE_NAME=hawk2)(SERVER=DEDICATED)))' as sysdba
connect sys/oracle@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.80.27.46)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawkb_DGMGRL)(INSTANCE_NAME=hawk3)(SERVER=DEDICATED)))' as sysdba
connect sys/oracle@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.80.27.48)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawkb_DGMGRL)(INSTANCE_NAME=hawk4)(SERVER=DEDICATED)))' as sysdba

3 out 4 failed for standby and all passed for primary

$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 9 14:44:44 2016

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

@> @test_dg_static_connect_id.sql

"hawkb_DGMGRL hawk1"
Connected.

"hawkb_DGMGRL hawk2"
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Warning: You are no longer connected to ORACLE.
SP2-0640: Not connected
SP2-0640: Not connected

"hawkb_DGMGRL hawk3"
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


SP2-0640: Not connected
SP2-0640: Not connected

"hawkb_DGMGRL hawk4"
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


SP2-0640: Not connected
SP2-0640: Not connected

"hawka_DGMGRL hawk1"
Connected.
"hawka_DGMGRL hawk2"
Connected.
"hawka_DGMGRL hawk3"
Connected.
"hawka_DGMGRL hawk4"
Connected.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

November 30, 2016

Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)

Filed under: 11g,Dataguard — mdinh @ 1:37 pm

This is a quick and dirty note to self about Doc ID 1387859.1 which may be incorrect for Single Instance Database with Oracle Restart.

Please share if you have configuration for Single Instance Database with Oracle Restart using DataGuard Broker and listener registered from Grid – TIA.

Oracle Data Guard Broker and Static Service Registration (Doc ID 1387859.1)	

+++ DO NOT BELIEVE THIS TO BE CORRECT - FAILED FOR TEST CASE.

Single Instance Database with Oracle Restart

Here there is no cluster, but clusterware has been installed to enable the Oracle Restart feature. 

The local listener LISTENER has its LISTENER.ORA located in the /network/admin directory of the Oracle Grid Infrastructure home. 

In this case the static service registration is:
SID_LIST_LISTENER=
  (SID_LIST=
    (SID_DESC=
     (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)
     (ORACLE_HOME=oracle_home)
     (SID_NAME=sid_name)
    )
  )

As with Single Instance databases, the SID_NAME value sid_name will default to the db_unique_name.

+++ SUCCESS
Data Guard & Oracle Restart in 11gR2 https://uhesse.com/2010/09/06/data-guard-oracle-restart-in-11gr2/

DEMO: Listener configured from DB Home and NOT GI Home which failed.

oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ ll
total 0
-rw-r-----. 1 oracle oinstall 0 Nov 30 05:13 alert_HAWKB.log
-rw-r-----. 1 oracle oinstall 0 Nov 30 05:13 drcHAWKB.log

oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ srvctl config listener -l listener11g
Name: LISTENER11G
Home: /u01/app/oracle/product/11.2.0.4/db_1
End points: TCP:1551

oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ lsnrctl status listener11g

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-NOV-2016 05:14:12

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1551)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER11G
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                30-NOV-2016 05:03:54
Uptime                    0 days 0 hr. 10 min. 17 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1551)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow2.localdomain)(PORT=1551)))
Services Summary...
Service "HAWKB.localdomain" has 2 instance(s).
  Instance "HAWKB", status UNKNOWN, has 1 handler(s) for this service...
  Instance "HAWKB", status READY, has 1 handler(s) for this service...
Service "HAWKB_DGB.localdomain" has 1 instance(s).
  Instance "HAWKB", status READY, has 1 handler(s) for this service...
Service "HAWKB_DGMGRL.localdomain" has 1 instance(s).
  Instance "HAWKB", status UNKNOWN, has 1 handler(s) for this service...
Service "HAWKB_SVC.localdomain" has 1 instance(s).
  Instance "HAWKB", status READY, has 1 handler(s) for this service...
Service "HAWK_SVC.localdomain" has 1 instance(s).
  Instance "HAWKB", status READY, has 1 handler(s) for this service...
The command completed successfully

oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ crs_stat
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER11G.lsnr
               ONLINE  ONLINE       arrow2                   STABLE
ora.ons
               OFFLINE OFFLINE      arrow2                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE                               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       arrow2                   STABLE
ora.hawkb.db
      1        ONLINE  ONLINE       arrow2                   Open,STABLE
ora.hawkb.hawk_svc.svc
      1        ONLINE  ONLINE       arrow2                   STABLE
--------------------------------------------------------------------------------

oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ dgmgrl sys/oracle@hawka
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawkb - Primary database
    hawka - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawkb

Database - hawkb

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    HAWKB

Database Status:
SUCCESS

DGMGRL> show database hawka

Database - hawka

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      0 Byte/s
  Real Time Query: ON
  Instance(s):
    HAWKA

Database Status:
SUCCESS

DGMGRL> switchover to hawka
Performing switchover NOW, please wait...
New primary database "hawka" is opening...
Operation requires startup of instance "HAWKB" on database "hawkb"
Starting instance "HAWKB"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "hawka"
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawka - Primary database
    hawkb - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawka

Database - hawka

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    HAWKA

Database Status:
SUCCESS

DGMGRL> show database hawkb

Database - hawkb

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       (unknown)
  Apply Rate:      (unknown)
  Real Time Query: ON
  Instance(s):
    HAWKB

Database Status:
SUCCESS

DGMGRL> exit

oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$ crs_stat
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER11G.lsnr
               ONLINE  ONLINE       arrow2                   STABLE
ora.ons
               OFFLINE OFFLINE      arrow2                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE                               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       arrow2                   STABLE
ora.hawkb.db
      1        ONLINE  ONLINE       arrow2                   Open,Readonly,STABLE
ora.hawkb.hawk_svc.svc
      1        ONLINE  ONLINE       arrow2                   STABLE
--------------------------------------------------------------------------------
oracle@arrow2:HAWKB:/u01/app/oracle/diag/rdbms/hawkb/HAWKB/trace
$

++++++++++

oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$ ll
total 0
-rw-r--r--. 1 oracle oinstall 0 Nov 30 05:12 alert_HAWKA.log
-rw-r-----. 1 oracle oinstall 0 Nov 30 05:12 drcHAWKA.log

oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$ srvctl config listener -l listener11g
Name: LISTENER11G
Home: /u01/app/oracle/product/11.2.0.4/db_1
End points: TCP:1551

oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$ lsnrctl status listener11g

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 30-NOV-2016 05:14:39

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1551)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER11G
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                30-NOV-2016 05:04:43
Uptime                    0 days 0 hr. 9 min. 55 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0.4/db_1/network/admin/listener.ora
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1551)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow1.localdomain)(PORT=1551)))
Services Summary...
Service "HAWKA.localdomain" has 2 instance(s).
  Instance "HAWKA", status UNKNOWN, has 1 handler(s) for this service...
  Instance "HAWKA", status READY, has 1 handler(s) for this service...
Service "HAWKA_DGB.localdomain" has 1 instance(s).
  Instance "HAWKA", status READY, has 1 handler(s) for this service...
Service "HAWKA_DGMGRL.localdomain" has 1 instance(s).
  Instance "HAWKA", status UNKNOWN, has 1 handler(s) for this service...
Service "HAWKA_SVC.localdomain" has 1 instance(s).
  Instance "HAWKA", status READY, has 1 handler(s) for this service...
Service "HAWK_SVC.localdomain" has 1 instance(s).
  Instance "HAWKA", status READY, has 1 handler(s) for this service...
The command completed successfully

oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$ crs_stat
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER11G.lsnr
               ONLINE  ONLINE       arrow1                   STABLE
ora.ons
               OFFLINE OFFLINE      arrow1                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE                               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       arrow1                   STABLE
ora.hawka.db
      1        ONLINE  ONLINE       arrow1                   Open,Readonly,STABLE
ora.hawka.hawk_svc.svc
      1        ONLINE  ONLINE       arrow1                   STABLE
--------------------------------------------------------------------------------

oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$ crs_stat
--------------------------------------------------------------------------------
Name           Target  State        Server                   State details
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.LISTENER11G.lsnr
               ONLINE  ONLINE       arrow1                   STABLE
ora.ons
               OFFLINE OFFLINE      arrow1                   STABLE
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ora.cssd
      1        OFFLINE OFFLINE                               STABLE
ora.diskmon
      1        OFFLINE OFFLINE                               STABLE
ora.evmd
      1        ONLINE  ONLINE       arrow1                   STABLE
ora.hawka.db
      1        ONLINE  ONLINE       arrow1                   Open,STABLE
ora.hawka.hawk_svc.svc
      1        ONLINE  ONLINE       arrow1                   STABLE
--------------------------------------------------------------------------------
oracle@arrow1:HAWKA:/u01/app/oracle/diag/rdbms/hawka/HAWKA/trace
$

May 15, 2016

Automating DG Broker

Filed under: 11g,awk,Dataguard,oracle,shell scripting — mdinh @ 2:11 am

I have been applying PSU lately and what’s so hard out it?

Four+ databases running on Primary with DG Broker for standby.

There are no conventions, as some standby databases have dr appended to primary name while others have 2 appended to primary name.

I wanted to view the DG configuration for currently active instances and show_dg_config.sh will show me this.

Next, I want a faster way to shutdown DG by having syntax generated and  gen_dg_cmd.sh does this.

Guess I could have taken it further by creating a shell script to create shell scripts to shutdown DG.

One day when I am really bore, I might OR may be you are so nice to complete my mission.

Tested on AIX 7.1

Note: the ps -ef syntax is for AIX and will not work with Linux.

See below for the Linux alternative.

$ ps -ef -o args|grep ora_smon|grep -v grep|awk -F"_smon_" '{print $2}'
Warning: bad syntax, perhaps a bogus '-'? See /usr/share/doc/procps-3.2.8/FAQ

$ ps -eo args|grep ora_smon|grep -v grep|awk -F"_smon_" '{print $2}'
thor
hulk

show_dg_config.sh

#!/bin/sh -e
ps -ef -o args|grep ora_smon|grep -v grep|awk -F"_smon_" '{print $2}'
export ORAENV_ASK=NO
for SID in ps -ef -o args|grep ora_smon|grep -v grep|awk -F"_smon_" '{print $2}'`
do
export ORACLE_SID=$SID
. /usr/local/bin/oraenv
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
echo "+++: " $ORACLE_SID $ORACLE_HOME
sysresv
dgmgrl -echo << END
connect /
show configuration
exit
END
done
exit

gen_dg_cmd.sh

#!/bin/sh -e
for XB in `egrep 'Primary|Physical' /tmp/dg.log |sort |awk -F" " '{print $3 $1}'`
do
#echo $XB
#echo $XB|awk '{print substr($1,1,7)}'
if [ "`echo $XB|awk '{print substr($1,1,7)}'`" == "Primary" ]
then
PRI=`echo $XB|awk '{print substr($1,8)}'`
echo "edit database $PRI set state='LOG-TRANSPORT-OFF';"
echo "show database $PRI"
echo "edit database $PRI set state='ONLINE';"
echo "show database $PRI"
fi
if [ "`echo $XB|awk '{print substr($1,1,8)}'`" == "Physical" ]
then
SBY=`echo $XB|awk '{print substr($1,9)}'`
echo "edit database $SBY set state='APPLY-OFF';"
echo "show database $SBY"
echo "edit database $SBY set state='APPLY-ON';"
echo "show database $SBY"
fi
done
exit

./show_dg_config.sh > /tmp/dg.log

egrep ‘Primary|Physical’ /tmp/dg.log |sort |awk -F” ” ‘{print $3 $1}’

Primarydb02
Physicaldb02dr
Primarydb01
Physicaldb01dr
Primarystageqa
Physicalstageqa2
Primarytest
Physicaltestdr

./gen_dg_cmd.sh

edit database db01 set state='LOG-TRANSPORT-OFF';
show database db01
edit database db01 set state='ONLINE';
show database db01
edit database db01dr set state='APPLY-OFF';
show database db01dr
edit database db01dr set state='APPLY-ON';
show database db01dr
edit database db02 set state='LOG-TRANSPORT-OFF';
show database db02
edit database db02 set state='ONLINE';
show database db02
edit database db02dr set state='APPLY-OFF';
show database db02dr
edit database db02dr set state='APPLY-ON';
show database db02dr
edit database stageqa set state='LOG-TRANSPORT-OFF';
show database stageqa
edit database stageqa set state='ONLINE';
show database stageqa
edit database stageqa2 set state='APPLY-OFF';
show database stageqa2
edit database stageqa2 set state='APPLY-ON';
show database stageqa2
edit database test set state='LOG-TRANSPORT-OFF';
show database test
edit database test set state='ONLINE';
show database test
edit database testdr set state='APPLY-OFF';
show database testdr
edit database testdr set state='APPLY-ON';
show database testdr
oracle:/home/oracle/working/dinh$

January 30, 2016

Not Another Standby Monitoring Script

Filed under: 11g,Dataguard — mdinh @ 3:55 am

I know what you are thinking. Not another standby monitoring script, right?

There’s plenty of them out there already and what makes this one any different?

You are probably correct and there’s probably nothing different about this.

Having to work with environments that are inconsistent such as manual standby, dataguard without broker, dataguard with broker,
I have been obsessed to create standby SQL which can accommodate any environment and can be run from primary or standby.

From PRIMARY: Recovery is IDLE

oracle@arrow:hawklas:/media/sf_working/dataguard
$ . oraenv <<< hawklas
ORACLE_SID = [hawklas] ? The Oracle base remains unchanged with value /u01/app/oracle
oracle@arrow:hawklas:/media/sf_working/dataguard
$ sysdba @stby

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:04:05 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Session altered.

*** v$database ***

DB_UNIQUE_NAME OPEN_MODE            DATABASE_ROLE    REMOTE_A SWITCHOVER_STATUS    DG_BROKER PRIMARY_DB_UNIQUE_NAME
-------------- -------------------- ---------------- -------- -------------------- --------- ----------------------
hawklas        READ WRITE           PRIMARY          ENABLED  TO STANDBY           ENABLED   hawksan

*** gv$archive_dest ***

 THREAD#  DEST_ID DESTINATION               STATUS       TARGET  SCHEDULE PROCESS         MID
-------- -------- ------------------------- ------------ ------- -------- ---------- --------
       1        1 USE_DB_RECOVERY_FILE_DEST VALID        PRIMARY ACTIVE   ARCH              0
       1        2 hawksan                   VALID        STANDBY ACTIVE   LGWR              0

*** gv$archive_dest_status ***

 DEST_ID STATUS       DATABASE_MODE   RECOVERY_MODE           GAP_STATUS      ERROR
-------- ------------ --------------- ----------------------- --------------- --------------------------------------------------
       1 VALID        OPEN            IDLE                                    NONE
       2 VALID        OPEN_READ-ONLY  IDLE                    NO GAP          NONE

*** v$thread ***

 THREAD# CURRENT LOG SEQUENCE STATUS
-------- -------------------- ------------
       1                  887 OPEN

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       2        1 NO             886 29-JAN-2016 17:50:04         4      3.7   --- STANDBY LAG
       2        1 YES            882 29-JAN-2016 17:46:22

*** v$archive_gap ***

no rows selected

*** GAP can also be verified using RMAN from STANDBY ***

RMAN1
------------------------------------------------------------
list archivelog from sequence 886 thread 1;

*** v$dataguard_stats ***

no rows selected

*** gv$managed_standby ***

     PID  INST_ID  THREAD# PROCESS   CLIENT_P STATUS       SEQUENCE#   BLOCK# DELAY_MINS
-------- -------- -------- --------- -------- ------------ --------- -------- ----------
    2659        1        1 LNS       LNS      WRITING            887    10153          0

ARROW:(SYS@hawklas):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

From STANDBY: Recovery is IDLE

oracle@arrow:hawklas:/media/sf_working/dataguard
$ . oraenv <<< hawksan
ORACLE_SID = [hawklas] ? The Oracle base remains unchanged with value /u01/app/oracle
oracle@arrow:hawksan:/media/sf_working/dataguard
$ sysdba @stby

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:04:11 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Session altered.

*** v$database ***

DB_UNIQUE_NAME OPEN_MODE            DATABASE_ROLE    REMOTE_A SWITCHOVER_STATUS    DG_BROKER PRIMARY_DB_UNIQUE_NAME
-------------- -------------------- ---------------- -------- -------------------- --------- ----------------------
hawksan        READ ONLY            PHYSICAL STANDBY ENABLED  NOT ALLOWED          ENABLED   hawklas

*** gv$archive_dest ***

 THREAD#  DEST_ID DESTINATION               STATUS       TARGET  SCHEDULE PROCESS         MID
-------- -------- ------------------------- ------------ ------- -------- ---------- --------
       1        1 USE_DB_RECOVERY_FILE_DEST VALID        LOCAL   ACTIVE   ARCH              0
       1       32 USE_DB_RECOVERY_FILE_DEST VALID        LOCAL   ACTIVE   RFS               0

*** gv$archive_dest_status ***

 DEST_ID STATUS       DATABASE_MODE   RECOVERY_MODE           GAP_STATUS      ERROR
-------- ------------ --------------- ----------------------- --------------- --------------------------------------------------
       1 VALID        OPEN_READ-ONLY  IDLE                                    NONE

*** v$thread ***

 THREAD# CURRENT LOG SEQUENCE STATUS
-------- -------------------- ------------
       1                  887 OPEN

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       1        1 NO             886 29-JAN-2016 17:50:04         4      3.7   --- STANDBY LAG
       1        1 YES            882 29-JAN-2016 17:46:22

*** v$archive_gap ***

no rows selected

*** GAP can also be verified using RMAN from STANDBY ***

RMAN1
------------------------------------------------------------
list archivelog from sequence 882 thread 1;

*** v$dataguard_stats ***

NAME                      VALUE              UNIT
------------------------- ------------------ ------------------------------
transport lag             +00 00:00:00       day(2) to second(0) interval
apply lag                 +00 01:14:48       day(2) to second(0) interval

*** gv$managed_standby ***

no rows selected

ARROW:(SYS@hawksan):PHYSICAL STANDBY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

From DG Broker: SET STATE=’APPLY-ON’

Note: Apply Lag is NOT really 1 hour.

Look at the time difference between SQL*Plus sessions.

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:04:11 2016
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:07:28 2016
oracle@arrow:hawksan:/media/sf_working/dataguard
$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
Connected.
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawklas - Primary database
    hawksan - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawksan

Database - hawksan

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-OFF
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       1 hour(s) 17 minutes 16 seconds (computed 1 second ago)
  Apply Rate:      (unknown)
  Real Time Query: OFF
  Instance(s):
    hawksan

Database Status:
SUCCESS

DGMGRL> EDIT DATABASE hawksan SET STATE='APPLY-ON';
Succeeded.
DGMGRL> show database hawksan

Database - hawksan

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 2 seconds ago)
  Apply Lag:       1 hour(s) 17 minutes 47 seconds (computed 2 seconds ago)
  Apply Rate:      465.00 KByte/s
  Real Time Query: ON
  Instance(s):
    hawksan

Database Status:
SUCCESS

DGMGRL> exit

From PRIMARY: Recovery is STILL IDLE

oracle@arrow:hawksan:/media/sf_working/dataguard
$ . oraenv <<< hawklas
ORACLE_SID = [hawksan] ? The Oracle base remains unchanged with value /u01/app/oracle
oracle@arrow:hawklas:/media/sf_working/dataguard
$ sysdba @stby

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:07:20 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Session altered.

*** v$database ***

DB_UNIQUE_NAME OPEN_MODE            DATABASE_ROLE    REMOTE_A SWITCHOVER_STATUS    DG_BROKER PRIMARY_DB_UNIQUE_NAME
-------------- -------------------- ---------------- -------- -------------------- --------- ----------------------
hawklas        READ WRITE           PRIMARY          ENABLED  TO STANDBY           ENABLED   hawksan

*** gv$archive_dest ***

 THREAD#  DEST_ID DESTINATION               STATUS       TARGET  SCHEDULE PROCESS         MID
-------- -------- ------------------------- ------------ ------- -------- ---------- --------
       1        1 USE_DB_RECOVERY_FILE_DEST VALID        PRIMARY ACTIVE   ARCH              0
       1        2 hawksan                   VALID        STANDBY ACTIVE   LGWR              0

*** gv$archive_dest_status ***

 DEST_ID STATUS       DATABASE_MODE   RECOVERY_MODE           GAP_STATUS      ERROR
-------- ------------ --------------- ----------------------- --------------- --------------------------------------------------
       1 VALID        OPEN            IDLE                                    NONE
       2 VALID        OPEN_READ-ONLY  IDLE                    NO GAP          NONE

*** v$thread ***

 THREAD# CURRENT LOG SEQUENCE STATUS
-------- -------------------- ------------
       1                  887 OPEN

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       2        1 NO             886 29-JAN-2016 17:50:04         4      3.7   --- STANDBY LAG
       2        1 YES            882 29-JAN-2016 17:46:22

*** v$archive_gap ***

no rows selected

*** GAP can also be verified using RMAN from STANDBY ***

RMAN1
------------------------------------------------------------
list archivelog from sequence 886 thread 1;

*** v$dataguard_stats ***

no rows selected

*** gv$managed_standby ***

     PID  INST_ID  THREAD# PROCESS   CLIENT_P STATUS       SEQUENCE#   BLOCK# DELAY_MINS
-------- -------- -------- --------- -------- ------------ --------- -------- ----------
    2659        1        1 LNS       LNS      WRITING            887    10402          0

ARROW:(SYS@hawklas):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

From STANDBY: Recovery is MANAGED REAL TIME APPLY

oracle@arrow:hawklas:/media/sf_working/dataguard
$ . oraenv <<< hawksan
ORACLE_SID = [hawklas] ? The Oracle base remains unchanged with value /u01/app/oracle
oracle@arrow:hawksan:/media/sf_working/dataguard
$ sysdba @stby

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:07:28 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Session altered.

*** v$database ***

DB_UNIQUE_NAME OPEN_MODE            DATABASE_ROLE    REMOTE_A SWITCHOVER_STATUS    DG_BROKER PRIMARY_DB_UNIQUE_NAME
-------------- -------------------- ---------------- -------- -------------------- --------- ----------------------
hawksan        READ ONLY WITH APPLY PHYSICAL STANDBY ENABLED  NOT ALLOWED          ENABLED   hawklas

*** gv$archive_dest ***

 THREAD#  DEST_ID DESTINATION               STATUS       TARGET  SCHEDULE PROCESS         MID
-------- -------- ------------------------- ------------ ------- -------- ---------- --------
       1        1 USE_DB_RECOVERY_FILE_DEST VALID        LOCAL   ACTIVE   ARCH              0
       1       32 USE_DB_RECOVERY_FILE_DEST VALID        LOCAL   ACTIVE   RFS               0

*** gv$archive_dest_status ***

 DEST_ID STATUS       DATABASE_MODE   RECOVERY_MODE           GAP_STATUS      ERROR
-------- ------------ --------------- ----------------------- --------------- --------------------------------------------------
       1 VALID        OPEN_READ-ONLY  MANAGED REAL TIME APPLY                 NONE

*** v$thread ***

 THREAD# CURRENT LOG SEQUENCE STATUS
-------- -------------------- ------------
       1                  887 OPEN

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       1        1 IN-MEMORY      886 29-JAN-2016 17:50:04         1 .0333333   --- STANDBY LAG
       1        1 YES            885 29-JAN-2016 17:50:02

*** v$archive_gap ***

no rows selected

*** GAP can also be verified using RMAN from STANDBY ***

RMAN1
------------------------------------------------------------
list archivelog from sequence 886 thread 1;

*** v$dataguard_stats ***

NAME                      VALUE              UNIT
------------------------- ------------------ ------------------------------
transport lag             +00 00:00:00       day(2) to second(0) interval
apply lag                 +00 00:00:00       day(2) to second(0) interval

*** gv$managed_standby ***

     PID  INST_ID  THREAD# PROCESS   CLIENT_P STATUS       SEQUENCE#   BLOCK# DELAY_MINS
-------- -------- -------- --------- -------- ------------ --------- -------- ----------
    4463        1        1 MRP0      N/A      APPLYING_LOG       887    10409          0

ARROW:(SYS@hawksan):PHYSICAL STANDBY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:hawksan:/media/sf_working/dataguard
$

Let’s check again.

From PRIMARY: Recovery is MANAGED REAL TIME APPLY

oracle@arrow:hawksan:/media/sf_working/dataguard
$ . oraenv <<< hawklas
ORACLE_SID = [hawksan] ? The Oracle base remains unchanged with value /u01/app/oracle
oracle@arrow:hawklas:/media/sf_working/dataguard
$ sysdba @stby

SQL*Plus: Release 11.2.0.4.0 Production on Fri Jan 29 19:09:14 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Session altered.

*** v$database ***

DB_UNIQUE_NAME OPEN_MODE            DATABASE_ROLE    REMOTE_A SWITCHOVER_STATUS    DG_BROKER PRIMARY_DB_UNIQUE_NAME
-------------- -------------------- ---------------- -------- -------------------- --------- ----------------------
hawklas        READ WRITE           PRIMARY          ENABLED  TO STANDBY           ENABLED   hawksan

*** gv$archive_dest ***

 THREAD#  DEST_ID DESTINATION               STATUS       TARGET  SCHEDULE PROCESS         MID
-------- -------- ------------------------- ------------ ------- -------- ---------- --------
       1        1 USE_DB_RECOVERY_FILE_DEST VALID        PRIMARY ACTIVE   ARCH              0
       1        2 hawksan                   VALID        STANDBY ACTIVE   LGWR              0

*** gv$archive_dest_status ***

 DEST_ID STATUS       DATABASE_MODE   RECOVERY_MODE           GAP_STATUS      ERROR
-------- ------------ --------------- ----------------------- --------------- --------------------------------------------------
       1 VALID        OPEN            IDLE                                    NONE
       2 VALID        OPEN_READ-ONLY  MANAGED REAL TIME APPLY NO GAP          NONE

*** v$thread ***

 THREAD# CURRENT LOG SEQUENCE STATUS
-------- -------------------- ------------
       1                  887 OPEN

*** gv$archived_log ***

 DEST_ID  THREAD# APPLIED    MAX_SEQ MAX_TIME             DELTA_SEQ DETA_MIN
-------- -------- --------- -------- -------------------- --------- --------
       2        1 NO             886 29-JAN-2016 17:50:04         1 .0333333   --- STANDBY LAG
       2        1 YES            885 29-JAN-2016 17:50:02

*** v$archive_gap ***

no rows selected

*** GAP can also be verified using RMAN from STANDBY ***

RMAN1
------------------------------------------------------------
list archivelog from sequence 886 thread 1;

*** v$dataguard_stats ***

no rows selected

*** gv$managed_standby ***

     PID  INST_ID  THREAD# PROCESS   CLIENT_P STATUS       SEQUENCE#   BLOCK# DELAY_MINS
-------- -------- -------- --------- -------- ------------ --------- -------- ----------
    2659        1        1 LNS       LNS      WRITING            887    10512          0

ARROW:(SYS@hawklas):PRIMARY>

SQL can be download from my Public Google Drive.

Look all the way to the right under Menu or search page for PublicGoogleDrive

January 16, 2016

Validate DG Broker Config for Switchover

Filed under: 11g,Dataguard,oracle — mdinh @ 6:08 pm

Primary and Standby databases are running on the same server using OMF with listening on port 1530/1531

Note I have – TraceLevel = ‘SUPPORT’

+++ Check listener for DGMGRL service from PRIMARY and STANDBY.

oracle@arrow:hawksan:/media/sf_working/dataguard
$ lsnrctl status listener_las|grep DG -A 1
Service "hawklas_DGB" has 1 instance(s).
  Instance "hawklas", status READY, has 1 handler(s) for this service...
Service "hawklas_DGMGRL" has 1 instance(s).
  Instance "hawklas", status UNKNOWN, has 1 handler(s) for this service...

oracle@arrow:hawksan:/media/sf_working/dataguard
$ lsnrctl status listener_san|grep DG -A 1
Service "hawksan_DGB" has 1 instance(s).
  Instance "hawksan", status READY, has 1 handler(s) for this service...
Service "hawksan_DGMGRL" has 1 instance(s).
  Instance "hawksan", status UNKNOWN, has 1 handler(s) for this service...

Get into habit of using instance versus database where applicable for RAC compatibility.

DGMGRL> show database hawklas

Database - hawklas

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    hawklas

Database Status:
SUCCESS

DGMGRL> show database hawklas DGConnectIdentifier
  DGConnectIdentifier = 'hawklas'
DGMGRL> show instance hawklas DGConnectIdentifier
  DGConnectIdentifier = 'hawklas'
DGMGRL>

+++ Check DG Configuration

oracle@arrow:hawklas:/media/sf_working/dataguard
$ ./check_dg.sh
***** Checking Data Guard Broker Configuration ....
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect /
Connected.
DGMGRL> show configuration verbose

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawklas - Primary database
    hawksan - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show configuration TraceLevel
  TraceLevel = 'SUPPORT'
DGMGRL> show database hawklas

Database - hawklas

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    hawklas

Database Status:
SUCCESS

DGMGRL> show database hawksan

Database - hawksan

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      45.00 KByte/s
  Real Time Query: ON
  Instance(s):
    hawksan

Database Status:
SUCCESS

DGMGRL> show instance hawklas DGConnectIdentifier
  DGConnectIdentifier = 'hawklas'
DGMGRL> show instance hawksan DGConnectIdentifier
  DGConnectIdentifier = 'hawksan'
DGMGRL> show instance hawklas StaticConnectIdentifier
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=arrow)(PORT=1530))(CONNECT_DATA=(SERVICE_NAME=hawklas_DGMGRL)(INSTANCE_NAME=hawklas)(SERVER=DEDICATED)))'
DGMGRL> show instance hawksan StaticConnectIdentifier
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=arrow)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=hawksan_DGMGRL)(INSTANCE_NAME=hawksan)(SERVER=DEDICATED)))'
DGMGRL> show instance hawklas InconsistentProperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

DGMGRL> show instance hawksan InconsistentProperties
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE

DGMGRL> show instance hawklas LogArchiveMaxProcesses
  LogArchiveMaxProcesses = '4'
DGMGRL> show instance hawksan LogArchiveMaxProcesses
  LogArchiveMaxProcesses = '4'
DGMGRL> show instance hawklas DelayMins
  DelayMins = '0'
DGMGRL> show instance hawksan DelayMins
  DelayMins = '0'
DGMGRL> show instance hawklas LogArchiveTrace
  LogArchiveTrace = '0'
DGMGRL> show instance hawksan LogArchiveTrace
  LogArchiveTrace = '0'
DGMGRL> show instance hawklas statusreport
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL> show instance hawksan statusreport
STATUS REPORT
       INSTANCE_NAME   SEVERITY ERROR_TEXT

DGMGRL> exit
oracle@arrow:hawklas:/media/sf_working/dataguard
$

+++ Test connectivity to database using StaticConnectIdentifier from DG Broker

oracle@arrow:hawksan:/media/sf_working/dataguard
$ sqlplus /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Sat Jan 16 08:10:31 2016

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

@> connect sys/oracle@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=arrow)(PORT=1530))(CONNECT_DATA=(SERVICE_NAME=hawklas_DGMGRL)(INSTANCE_NAME=hawklas)(SERVER=DEDICATED)))' as sysdba
Connected.
ARROW:(SYS@hawklas):PRIMARY> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      hawk
db_unique_name                       string      hawklas
global_names                         boolean     FALSE
instance_name                        string      hawklas
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                        string      hawk,hawklas
ARROW:(SYS@hawklas):PRIMARY> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ WRITE           PRIMARY

ARROW:(SYS@hawklas):PRIMARY> connect sys/oracle@'(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=arrow)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=hawksan_DGMGRL)(INSTANCE_NAME=hawksan)(SERVER=DEDICATED)))' as sysdba
Connected.
ARROW:(SYS@hawksan):PHYSICAL STANDBY> show parameter name

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name               string
db_file_name_convert                 string
db_name                              string      hawk
db_unique_name                       string      hawksan
global_names                         boolean     FALSE
instance_name                        string      hawksan
lock_name_space                      string
log_file_name_convert                string
processor_group_name                 string
service_names                        string      hawk,hawksan
ARROW:(SYS@hawksan):PHYSICAL STANDBY> select open_mode, database_role from v$database;

OPEN_MODE            DATABASE_ROLE
-------------------- ----------------
READ ONLY WITH APPLY PHYSICAL STANDBY

ARROW:(SYS@hawksan):PHYSICAL STANDBY>

+++ switchover to hawksan (STANDBY)

Must connect as sys@tns (typically same as DGConnectIdentifier) for switchover.

oracle@arrow:hawklas:/media/sf_working/dataguard
$ ./clearlog.sh
oracle@arrow:hawklas:/media/sf_working/dataguard
$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@hawklas
Connected.
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawklas - Primary database
    hawksan - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawklas

Database - hawklas

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    hawklas

Database Status:
SUCCESS

DGMGRL> show database hawksan

Database - hawksan

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      19.00 KByte/s
  Real Time Query: ON
  Instance(s):
    hawksan

Database Status:
SUCCESS

DGMGRL> switchover to hawksan
Performing switchover NOW, please wait...
Operation requires a connection to instance "hawksan" on database "hawksan"
Connecting to instance "hawksan"...
Connected.
New primary database "hawksan" is opening...
Operation requires startup of instance "hawklas" on database "hawklas"
Starting instance "hawklas"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "hawksan"
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawksan - Primary database
    hawklas - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawksan

Database - hawksan

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    hawksan

Database Status:
SUCCESS

DGMGRL> show database hawklas

Database - hawklas

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      0 Byte/s
  Real Time Query: ON
  Instance(s):
    hawklas

Database Status:
SUCCESS

DGMGRL> exit

++++ Save logs for reference

oracle@arrow:hawklas:/media/sf_working/dataguard
$ ./savelog.sh
`/u01/app/oracle/product/11.2.0/dbhome_1/network/log/listener_las.log' -> `/tmp/listener_las.log'
`/u01/app/oracle/product/11.2.0/dbhome_1/network/log/listener_san.log' -> `/tmp/listener_san.log'
`/u01/app/oracle/diag/rdbms/hawklas/hawklas/trace/alert_hawklas.log' -> `/tmp/alert_hawklas.log'
`/u01/app/oracle/diag/rdbms/hawklas/hawklas/trace/drchawklas.log' -> `/tmp/drchawklas.log'
`/u01/app/oracle/diag/rdbms/hawksan/hawksan/trace/alert_hawksan.log' -> `/tmp/alert_hawksan.log'
`/u01/app/oracle/diag/rdbms/hawksan/hawksan/trace/drchawksan.log' -> `/tmp/drchawksan.log'

+++ switchover to hawklas (STANDBY)

Must connect as sys@tns (typically same as DGConnectIdentifier) for switchover.

oracle@arrow:hawklas:/media/sf_working/dataguard
$ dgmgrl
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/oracle@hawklas
Connected.
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawksan - Primary database
    hawklas - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> switchover to hawklas
Performing switchover NOW, please wait...
New primary database "hawklas" is opening...
Operation requires startup of instance "hawksan" on database "hawksan"
Starting instance "hawksan"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "hawklas"
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawklas - Primary database
    hawksan - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawklas

Database - hawklas

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    hawklas

Database Status:
SUCCESS

DGMGRL> show database hawksan

Database - hawksan

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      0 Byte/s
  Real Time Query: ON
  Instance(s):
    hawksan

Database Status:
SUCCESS

DGMGRL> exit
oracle@arrow:hawklas:/media/sf_working/dataguard
$

January 11, 2016

ORA-12514: TNS:listener during switchover using DGMGRL

Filed under: 11g,Dataguard,oracle — mdinh @ 2:02 am

Not sure what I am doing wrong because DEFAULT StaticConnectIdentifier does not seem to work.

Google and MOS were not much help.

Using StaticConnectIdentifier as created by dmgrl would fail with the following error during switchover.
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

The error can be found in the listener.log

When using tnsnames for StaticConnectIdentifier, switchover works like a charm.

I keep thinking, there must be something wrong with my configuration but as you can see from the test case, the only changes were to StaticConnectIdentifier.

Both databases are on the same host with each listening on different ports.

Updated: Just found this note
DGMGRL>switchover to Fails with ORA-12514 (Doc ID 1582927.1)
Oracle Database – Enterprise Edition – Version 12.1.0.1 to 12.1.0.1 [Release 12.1]

Interesting to see if this will work.

oracle@arrow:hawklas:/home/oracle
$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> connect sys/oracle@dc_las
Connected.
DGMGRL> connect sys/oracle@dc_san
Connected.
DGMGRL> show configuration verbose

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawklas - Primary database
    hawksan - Physical standby database

  Properties:
    FastStartFailoverThreshold      = '30'
    OperationTimeout                = '30'
    FastStartFailoverLagLimit       = '30'
    CommunicationTimeout            = '180'
    ObserverReconnect               = '0'
    FastStartFailoverAutoReinstate  = 'TRUE'
    FastStartFailoverPmyShutdown    = 'TRUE'
    BystandersFollowRoleChange      = 'ALL'
    ObserverOverride                = 'FALSE'
    ExternalDestination1            = ''
    ExternalDestination2            = ''
    PrimaryLostWriteAction          = 'CONTINUE'

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database verbose hawklas

Database - hawklas

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    hawklas

  Properties:
    DGConnectIdentifier             = 'dc_las'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'optional'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '300'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '1800'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    SidName                         = 'hawklas'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawklas_DGMGRL)(INSTANCE_NAME=hawklas)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '8191'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

DGMGRL> show database verbose hawksan

Database - hawksan

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 0 seconds ago)
  Apply Lag:       0 seconds (computed 0 seconds ago)
  Apply Rate:      32.00 KByte/s
  Real Time Query: ON
  Instance(s):
    hawksan

  Properties:
    DGConnectIdentifier             = 'dc_san'
    ObserverConnectIdentifier       = ''
    LogXptMode                      = 'ASYNC'
    DelayMins                       = '0'
    Binding                         = 'OPTIONAL'
    MaxFailure                      = '0'
    MaxConnections                  = '1'
    ReopenSecs                      = '30'
    NetTimeout                      = '30'
    RedoCompression                 = 'DISABLE'
    LogShipping                     = 'ON'
    PreferredApplyInstance          = ''
    ApplyInstanceTimeout            = '0'
    ApplyParallel                   = 'AUTO'
    StandbyFileManagement           = 'AUTO'
    ArchiveLagTarget                = '1800'
    LogArchiveMaxProcesses          = '4'
    LogArchiveMinSucceedDest        = '1'
    DbFileNameConvert               = ''
    LogFileNameConvert              = ''
    FastStartFailoverTarget         = ''
    InconsistentProperties          = '(monitor)'
    InconsistentLogXptProps         = '(monitor)'
    SendQEntries                    = '(monitor)'
    LogXptStatus                    = '(monitor)'
    RecvQEntries                    = '(monitor)'
    ApplyLagThreshold               = '0'
    TransportLagThreshold           = '0'
    TransportDisconnectedThreshold  = '30'
    SidName                         = 'hawksan'
    StaticConnectIdentifier         = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=hawksan_DGMGRL)(INSTANCE_NAME=hawksan)(SERVER=DEDICATED)))'
    StandbyArchiveLocation          = 'USE_DB_RECOVERY_FILE_DEST'
    AlternateLocation               = ''
    LogArchiveTrace                 = '8191'
    LogArchiveFormat                = '%t_%s_%r.dbf'
    TopWaitEvents                   = '(monitor)'

Database Status:
SUCCESS

DGMGRL> show database hawksan

Database - hawksan

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      32.00 KByte/s
  Real Time Query: ON
  Instance(s):
    hawksan

Database Status:
SUCCESS
DGMGRL> connect sys/oracle@dc_las
Connected.
DGMGRL> switchover to hawksan
Performing switchover NOW, please wait...
Operation requires a connection to instance "hawksan" on database "hawksan"
Connecting to instance "hawksan"...
Connected.
New primary database "hawksan" is opening...
Operation requires startup of instance "hawklas" on database "hawklas"
Starting instance "hawklas"...
Unable to connect to database
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Failed.
Warning: You are no longer connected to ORACLE.

Please complete the following steps to finish switchover:
        start up instance "hawklas" of database "hawklas"

DGMGRL> exit
oracle@arrow:hawklas:/home/oracle
$ sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 10 17:36:20 2016

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

Connected to an idle instance.

SYS@hawklas> startup
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             385876872 bytes
Database Buffers          675282944 bytes
Redo Buffers                5517312 bytes
Database mounted.
Database opened.
SYS@hawklas> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:hawklas:/home/oracle
$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawksan - Primary database
    hawklas - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
ORA-16534: switchover, failover or convert operation in progress
DGM-17017: unable to determine configuration status

DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawksan - Primary database
    hawklas - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> edit database hawksan set property StaticConnectIdentifier='dc_san';
Property "staticconnectidentifier" updated
DGMGRL> edit database hawklas set property StaticConnectIdentifier='dc_las';
Property "staticconnectidentifier" updated'
DGMGRL> connect sys/oracle@dc_san
Connected.
DGMGRL> switchover to hawklas
Performing switchover NOW, please wait...
Operation requires a connection to instance "hawklas" on database "hawklas"
Connecting to instance "hawklas"...
Connected.
New primary database "hawklas" is opening...
Operation requires startup of instance "hawksan" on database "hawksan"
Starting instance "hawksan"...
ORACLE instance started.
Database mounted.
Database opened.
Switchover succeeded, new primary is "hawklas"
DGMGRL> show configuration

Configuration - dg_hawk

  Protection Mode: MaxPerformance
  Databases:
    hawklas - Primary database
    hawksan - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database hawksan

Database - hawksan

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds (computed 1 second ago)
  Apply Lag:       0 seconds (computed 1 second ago)
  Apply Rate:      0 Byte/s
  Real Time Query: ON
  Instance(s):
    hawksan

Database Status:
SUCCESS

DGMGRL> exit
oracle@arrow:hawklas:/home/oracle
$
oracle@arrow:hawklas:/home/oracle
$ tns
oracle@arrow:hawklas:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
$ tnsping dc_las

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 10-JAN-2016 17:50:08

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (SDU=32767) (SEND_BUF_SIZE=32767) (RECV_BUF_SIZE=32767) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = arrow)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = hawklas)))
OK (0 msec)
oracle@arrow:hawklas:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
$ tnsping dc_san

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 10-JAN-2016 17:50:14

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (SDU=32767) (SEND_BUF_SIZE=32767) (RECV_BUF_SIZE=32767) (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = arrow)(PORT = 1531))) (CONNECT_DATA = (SERVICE_NAME = hawksan)))
OK (0 msec)
oracle@arrow:hawklas:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
$ lsnrctl status listener_las

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 10-JAN-2016 17:50:26

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow)(PORT=1521)(SEND_BUF_SIZE=32767)(RECV_BUF_SIZE=32767)))
STATUS of the LISTENER
------------------------
Alias                     listener_las
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                10-JAN-2016 16:15:51
Uptime                    0 days 1 hr. 34 min. 34 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/dbhome_1/network/log/listener_las.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1521)))
Services Summary...
Service "foo2" has 1 instance(s).
  Instance "foo2", status UNKNOWN, has 1 handler(s) for this service...
Service "hawk" has 1 instance(s).
  Instance "hawklas", status READY, has 1 handler(s) for this service...
Service "hawklas" has 2 instance(s).
  Instance "hawklas", status UNKNOWN, has 2 handler(s) for this service...
  Instance "hawklas", status READY, has 1 handler(s) for this service...
Service "hawklas_DGB" has 1 instance(s).
  Instance "hawklas", status READY, has 1 handler(s) for this service...
The command completed successfully
oracle@arrow:hawklas:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
$ lsnrctl status listener_san

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 10-JAN-2016 17:50:29

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow)(PORT=1531)(SEND_BUF_SIZE=32767)(RECV_BUF_SIZE=32767)))
STATUS of the LISTENER
------------------------
Alias                     listener_san
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                10-JAN-2016 16:15:54
Uptime                    0 days 1 hr. 34 min. 35 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/dbhome_1/network/log/listener_san.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1531)))
Services Summary...
Service "hawk" has 1 instance(s).
  Instance "hawksan", status READY, has 1 handler(s) for this service...
Service "hawksan" has 2 instance(s).
  Instance "hawksan", status UNKNOWN, has 2 handler(s) for this service...
  Instance "hawksan", status READY, has 1 handler(s) for this service...
Service "hawksan_DGB" has 1 instance(s).
  Instance "hawksan", status READY, has 1 handler(s) for this service...
The command completed successfully
oracle@arrow:hawklas:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
$

It seems that static registration is not working for db_unique_name_DMGRL

oracle@arrow:hawksan:/home/oracle
$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.4.0 - 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show database hawklas StaticConnectIdentifier
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawklas_DGMGRL)(INSTANCE_NAME=hawklas)(SERVER=DEDICATED)))'
DGMGRL> show database hawksan StaticConnectIdentifier
  StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=hawksan_DGMGRL)(INSTANCE_NAME=hawksan)(SERVER=DEDICATED)))'
DGMGRL> exit
oracle@arrow:hawksan:/home/oracle
$ lsnrctl status listener_las

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 10-JAN-2016 22:16:09

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1521)(SEND_BUF_SIZE=32767)(RECV_BUF_SIZE=32767)))
STATUS of the LISTENER
------------------------
Alias                     listener_las
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                10-JAN-2016 22:02:05
Uptime                    0 days 0 hr. 14 min. 3 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/dbhome_1/network/log/listener_las.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1521)))
Services Summary...
Service "foo2" has 1 instance(s).
  Instance "foo2", status UNKNOWN, has 1 handler(s) for this service...
Service "hawk" has 1 instance(s).
  Instance "hawklas", status READY, has 1 handler(s) for this service...
Service "hawklas" has 2 instance(s).
  Instance "hawklas", status UNKNOWN, has 2 handler(s) for this service...
  Instance "hawklas", status READY, has 1 handler(s) for this service...
Service "hawklas_DGB" has 1 instance(s).
  Instance "hawklas", status READY, has 1 handler(s) for this service...
The command completed successfully
oracle@arrow:hawksan:/home/oracle
$ lsnrctl status listener_san

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 10-JAN-2016 22:16:12

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1531)(SEND_BUF_SIZE=32767)(RECV_BUF_SIZE=32767)))
STATUS of the LISTENER
------------------------
Alias                     listener_san
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                10-JAN-2016 22:02:03
Uptime                    0 days 0 hr. 14 min. 9 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/11.2.0/dbhome_1/network/log/listener_san.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1531)))
Services Summary...
Service "hawk" has 1 instance(s).
  Instance "hawksan", status READY, has 1 handler(s) for this service...
Service "hawksan" has 2 instance(s).
  Instance "hawksan", status UNKNOWN, has 2 handler(s) for this service...
  Instance "hawksan", status READY, has 1 handler(s) for this service...
Service "hawksan_DGB" has 1 instance(s).
  Instance "hawksan", status READY, has 1 handler(s) for this service...
The command completed successfully
oracle@arrow:hawksan:/home/oracle
$ sqlplus sys@'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawklas_DGMGRL)(INSTANCE_NAME=hawklas)(SERVER=DEDICATED)))' as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 10 22:17:09 2016

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

Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name: ^C
oracle@arrow:hawksan:/home/oracle
$ sqlplus sys@'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=hawksan_DGMGRL)(INSTANCE_NAME=hawksan)(SERVER=DEDICATED)))' as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 10 22:17:20 2016

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

Enter password:
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect
descriptor


Enter user-name: ^C
oracle@arrow:hawksan:/home/oracle
$ sqlplus sys@'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=hawklas_DGB)(INSTANCE_NAME=hawklas)(SERVER=DEDICATED)))' as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 10 22:17:48 2016

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ARROW:(SYS@hawklas):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:hawksan:/home/oracle
$ sqlplus sys@'(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=arrow.localdomain)(PORT=1531))(CONNECT_DATA=(SERVICE_NAME=hawksan_DGB)(INSTANCE_NAME=hawksan)(SERVER=DEDICATED)))' as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 10 22:17:55 2016

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

Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ARROW:(SYS@hawksan):PHYSICAL STANDBY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:hawksan:/home/oracle
$

$ tns
oracle@arrow:hawksan:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
$ grep -i dg listener.ora
        (GLOBAL_NAME = hawklas_DGB)
        (GLOBAL_NAME = hawklas_DGMGRL)
        (GLOBAL_NAME = hawksan_DGB)
        (GLOBAL_NAME = hawksan_DGMGRL)
oracle@arrow:hawksan:/u01/app/oracle/product/11.2.0/dbhome_1/network/admin
$

UPDATED Jan 16, 2015.

Thanks to Shivananda Rao (http://shivanandarao-oracle.com/) for pointing out the obvious which I was blind to.

IT’S NOT GLOBAL_NAME = hawklas_DGMGRL !!!

It’s GLOBAL_DBNAME = hawklas_DGMGRL (Missing DB)

R.I.P.

 

January 10, 2016

How Reliable is v$archive_dest_status?

Filed under: 11g,Dataguard,oracle — mdinh @ 7:23 pm

v$archive_dest_status

Any DG Experts out there who knows more about the reliability for v$archive_dest_status as it has been many years since I have written scripts to monitor DG.

Don’t want to reinvent the wheel if I cannot make it better.

Note: gap_status is only available from 11gR2 +++

SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 10 11:06:55 2016

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ARROW:(SYS@hawklas):PRIMARY> select
  2    arch.dest_id,
  3    arch.thread# "thread",
  4    arch.sequence# "last sequence received",
  5    appl.sequence# "last sequence applied",
  6   (arch.sequence#-appl.sequence#) "difference"
  7  from
  8    (
  9      select dest_id,thread#,sequence# from v$archived_log
 10      where (thread#,first_time)
 11      in (select thread#,max(first_time) from v$archived_log where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#)
 12    ) arch,
 13    (
 14      select thread# ,sequence# from v$log_history
 15      where (thread#,first_time)
 16      in (select thread#,max(first_time) from v$log_history where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#)
 17    ) appl
 18  where arch.thread#=appl.thread#
 19  order by 1
 20  ;

DEST_ID thread last sequence received last sequence applied difference
------- ------ ---------------------- --------------------- ----------
      1      1                    234                   234          0
      2      1                    234                   234          0

Elapsed: 00:00:07.99
ARROW:(SYS@hawklas):PRIMARY> select
  2  DEST_ID,STATUS,TYPE,DATABASE_MODE,RECOVERY_MODE,STANDBY_LOGFILE_COUNT srl_ct,STANDBY_LOGFILE_ACTIVE srl_active,
  3  ARCHIVED_THREAD# thrd,ARCHIVED_SEQ# seq,APPLIED_SEQ# applied,SRL,
  4  GAP_STATUS, decode(ERROR,null,'NONE','ERROR') error
  5  from v$archive_dest_status
  6  where status!='INACTIVE'
  7  ;

DEST_ID STATUS    TYPE           DATABASE_MODE   RECOVERY_MODE           SRL_CT SRL_ACTIVE THRD  SEQ APPLIED SRL GAP_STATUS               ERROR
------- --------- -------------- --------------- ----------------------- ------ ---------- ---- ---- ------- --- ------------------------ -----
      1 VALID     LOCAL          OPEN            IDLE                         0          0    1  234       0 NO                           NONE
      2 VALID     PHYSICAL       MOUNTED-STANDBY MANAGED REAL TIME APPLY      4          0    1  234     233 YES NO GAP                   NONE

Elapsed: 00:00:00.01
ARROW:(SYS@hawklas):PRIMARY>
SQL*Plus: Release 11.2.0.4.0 Production on Sun Jan 10 11:06:36 2016

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ARROW:(SYS@hawksan):PHYSICAL STANDBY> select
  2    arch.dest_id,
  3    arch.thread# "thread",
  4    arch.sequence# "last sequence received",
  5    appl.sequence# "last sequence applied",
  6   (arch.sequence#-appl.sequence#) "difference"
  7  from
  8    (
  9      select dest_id,thread#,sequence# from v$archived_log
 10      where (thread#,first_time)
 11      in (select thread#,max(first_time) from v$archived_log where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#)
 12    ) arch,
 13    (
 14      select thread# ,sequence# from v$log_history
 15      where (thread#,first_time)
 16      in (select thread#,max(first_time) from v$log_history where resetlogs_change#=(select resetlogs_change# from v$database) group by thread#)
 17    ) appl
 18  where arch.thread#=appl.thread#
 19  order by 1
 20  ;

DEST_ID thread last sequence received last sequence applied difference
------- ------ ---------------------- --------------------- ----------
      1      1                    234                   234          0

Elapsed: 00:00:00.01
ARROW:(SYS@hawksan):PHYSICAL STANDBY> select
  2  DEST_ID,STATUS,TYPE,DATABASE_MODE,RECOVERY_MODE,STANDBY_LOGFILE_COUNT srl_ct,STANDBY_LOGFILE_ACTIVE srl_active,
  3  ARCHIVED_THREAD# thrd,ARCHIVED_SEQ# seq,APPLIED_SEQ# applied,SRL,
  4  GAP_STATUS, decode(ERROR,null,'NONE','ERROR') error
  5  from v$archive_dest_status
  6  where status!='INACTIVE'
  7  ;

DEST_ID STATUS    TYPE           DATABASE_MODE   RECOVERY_MODE           SRL_CT SRL_ACTIVE THRD  SEQ APPLIED SRL GAP_STATUS               ERROR
------- --------- -------------- --------------- ----------------------- ------ ---------- ---- ---- ------- --- ------------------------ -----
      1 VALID     LOCAL          MOUNTED-STANDBY MANAGED REAL TIME APPLY      0          0    1  234       0 NO                           NONE
      2 VALID     UNKNOWN        UNKNOWN         IDLE                         0          0    0    0       0 NO                           NONE
     32 VALID     UNKNOWN        UNKNOWN         IDLE                         0          0    1  234     234 NO                           NONE

Elapsed: 00:00:00.00
ARROW:(SYS@hawksan):PHYSICAL STANDBY>

May 29, 2015

Warning: standby redo logs not configured for thread – BUG

Filed under: 12c,Dataguard,oracle — mdinh @ 12:12 am

Surprise to find the following error for single instance Data Guard environment:

    Warning: standby redo logs not configured for thread 3 on boston

 

This will be fixed with an upcoming patch for the existing issue in Oracle Database 12.1.0.1.0 – good reason to stay up to date on patching?

Also, the number of Standby Redo Log Groups do not match since thread = 1 is being used.

Not *ALL* SRL have thread 1 assigned – not sure if this is a bug as well.

[oracle@host01 trace]$ dgmgrl /

DGMGRL for Linux: Version 12.1.0.1.0 - 64bit Production

Copyright (c) 2000, 2012, Oracle. All rights reserved.

Welcome to DGMGRL, type "help" for information.
Connected as SYSDG.
DGMGRL> validate database boston

  Database Role:    Primary database

  Ready for Switchover:  Yes

  Flashback Database Status:
    boston:  Off

DGMGRL> validate database london

  Database Role:     Physical standby database
  Primary Database:  boston

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)

  Flashback Database Status:
    boston:  Off
    london:  Off

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups   Standby Redo Log Groups  
              (boston)                 (london)                 
    1         3                        2                        

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups   Standby Redo Log Groups  
              (london)                 (boston)                 
    1         3                        0                        
    Warning: standby redo logs not configured for thread 3 on boston

DGMGRL> validate database london2

  Database Role:     Logical standby database
  Primary Database:  boston

  Ready for Switchover:  Yes
  Ready for Failover:    Yes (Primary Running)
    Warning: Physical and snapshot standby databases will
    be disabled if a role change is performed to this database

  Flashback Database Status:
    boston:   Off
    london2:  Off

  Current Log File Groups Configuration:
    Thread #  Online Redo Log Groups   Standby Redo Log Groups  
              (boston)                 (london2)                
    1         3                        2                        

  Future Log File Groups Configuration:
    Thread #  Online Redo Log Groups   Standby Redo Log Groups  
              (london2)                (boston)                 
    1         3                        0                        
    Warning: standby redo logs not configured for thread 3 on boston

DGMGRL> 
HOST01:(SYS@boston):PRIMARY> select group#,thread#,sequence#,bytes,used,status from v$standby_log;

    GROUP#    THREAD#  SEQUENCE#      BYTES       USED STATUS
---------- ---------- ---------- ---------- ---------- ----------
         4          0          0   52428800          0 UNASSIGNED
         5          0          0   52428800          0 UNASSIGNED
         6          0          0   52428800          0 UNASSIGNED
         7          0          0   52428800          0 UNASSIGNED

HOST01:(SYS@boston):PRIMARY> select count(*) from v$standby_log where thread#=1;

  COUNT(*)
----------
         0

HOST01:(SYS@boston):PRIMARY> 
HOST03:(SYS@london2):LOGICAL STANDBY> select group#,thread#,sequence#,bytes,used,status from v$standby_log;

    GROUP#    THREAD#  SEQUENCE#      BYTES       USED STATUS
---------- ---------- ---------- ---------- ---------- ----------
         4          1         67   52428800    4803072 ACTIVE
         5          1          0   52428800          0 UNASSIGNED
         6          0          0   52428800          0 UNASSIGNED
         7          0          0   52428800          0 UNASSIGNED

HOST03:(SYS@london2):LOGICAL STANDBY> select count(*) from v$standby_log where thread#=1;

  COUNT(*)
----------
         2

HOST03:(SYS@london2):LOGICAL STANDBY> 
HOST03:(SYS@london):PHYSICAL STANDBY> select group#,thread#,sequence#,bytes,used,status from v$standby_log;

    GROUP#    THREAD#  SEQUENCE#      BYTES       USED STATUS
---------- ---------- ---------- ---------- ---------- ----------
         4          1         67   52428800    4816896 ACTIVE
         5          1          0   52428800          0 UNASSIGNED
         6          0          0   52428800          0 UNASSIGNED
         7          0          0   52428800          0 UNASSIGNED

HOST03:(SYS@london):PHYSICAL STANDBY> select count(*) from v$standby_log where thread#=1;

  COUNT(*)
----------
         2

HOST03:(SYS@london):PHYSICAL STANDBY> 
Next Page »

Create a free website or blog at WordPress.com.