Thinking Out Loud

June 28, 2019

DGMGRL Using Help To Learn About New Validate Features

Filed under: 18c,Dataguard,dgmgrl — mdinh @ 3:57 pm

Wouldn’t be nicer and much better if Oracle would add (NF) for new features to help syntax?

DGMGRL for Linux: Release 12.2.0.1.0

[oracle@db-fs-1 bin]$ ./dgmgrl /
DGMGRL for Linux: Release 12.2.0.1.0 - Production on Fri Jun 28 17:49:16 2019

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

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

Performs an exhaustive set of validations for a member

Syntax:

  VALIDATE DATABASE [VERBOSE] <database name>;

  VALIDATE DATABASE [VERBOSE] <database name> DATAFILE <datafile number>
    OUTPUT=<file name>;

  VALIDATE DATABASE [VERBOSE] <database name> SPFILE;

  VALIDATE FAR_SYNC [VERBOSE] <far_sync name>
    [WHEN PRIMARY IS <database name>];

DGMGRL>

DGMGRL for Linux: Release 18.0.0.0.0

[oracle@ADC6160274 GDS]$ dgmgrl /
DGMGRL for Linux: Release 18.0.0.0.0 - Production on Fri Jun 28 15:54:36 2019
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

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

Performs an exhaustive set of validations for a member

Syntax:

  VALIDATE DATABASE [VERBOSE] <database name>;

  VALIDATE DATABASE [VERBOSE] <database name> DATAFILE <datafile number>
    OUTPUT=<file name>;

  VALIDATE DATABASE [VERBOSE] <database name> SPFILE;

  VALIDATE FAR_SYNC [VERBOSE] <far_sync name>
    [WHEN PRIMARY IS <database name>];

  VALIDATE NETWORK CONFIGURATION FOR { ALL | <member name> }; [*** NF ***]

  VALIDATE STATIC CONNECT IDENTIFIER FOR { ALL | <database name> }; [*** NF ***]

DGMGRL>

validate network configuration

DGMGRL> validate network configuration for all;
Connecting to instance "sales" on database "sfo" ...
Connected to "sfo"
Checking connectivity from instance "sales" on database "sfo to instance "sales" on database "chi"...
Succeeded.
Connecting to instance "sales" on database "chi" ...
Connected to "chi"
Checking connectivity from instance "sales" on database "chi to instance "sales" on database "sfo"...
Succeeded.

Oracle Clusterware is not configured on database "sfo".
Connecting to database "sfo" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SLC02PNY.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sfo_DGMGRL)(INSTANCE_NAME=sales)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
Succeeded.
The static connect identifier allows for a connection to database "sfo".

Oracle Clusterware is not configured on database "chi".
Connecting to database "chi" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ADC6160274.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=chi_DGMGRL)(INSTANCE_NAME=sales)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
Succeeded.
The static connect identifier allows for a connection to database "chi".

validate static connect identifier

DGMGRL> validate static connect identifier for all;
Oracle Clusterware is not configured on database "sfo".
Connecting to database "sfo" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=SLC02PNY.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=sfo_DGMGRL)(INSTANCE_NAME=sales)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
Succeeded.
The static connect identifier allows for a connection to database "sfo".

Oracle Clusterware is not configured on database "chi".
Connecting to database "chi" using static connect identifier "(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ADC6160274.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=chi_DGMGRL)(INSTANCE_NAME=sales)(SERVER=DEDICATED)(STATIC_SERVICE=TRUE)))" ...
Succeeded.
The static connect identifier allows for a connection to database "chi".

DGMGRL>
Advertisements

May 11, 2018

DataGuard Convention

Filed under: Dataguard,dgmgrl — mdinh @ 11:58 am

Good convention and implementation make life and automation so much simpler and more time for golfing.

I have seen some really poor and really good implementation and here’s a good one.

Wish I can take credit for it and unfortunately I cannot.

The scripts were created by whoa.

Scripts an be run from primary or standby for any instances provided profile to source database environment exists on host.

Use ORACLE_UNQNAME for DataGuard Environment

====================================================================================================
+++ PRIMARY RACONENODE
====================================================================================================
SQL> show parameter db%name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      test
db_unique_name                       string      test
SQL> 

$ sysresv|tail -1
Oracle Instance alive for sid "test_1"

$ env|grep ORACLE

ORACLE_SID=test_1 (db_name)
ORACLE_UNQNAME=test (db_unique_name)

$ srvctl config database -d $ORACLE_UNQNAME
Database unique name: test
Database name: test
Oracle home: /u01/app/oracle/product/11g/db_1
Oracle user: oracle
Spfile: +FLASH/test/spfiletest.ora
Domain:
Start options: open
Stop options: immediate
Database role: PRIMARY
Management policy: AUTOMATIC
Server pools: test
Database instances:
Disk Groups: FLASH,DATA
Mount point paths:
Services: testsvc
Type: RACOneNode
Online relocation timeout: 30
Instance name prefix: test
Candidate servers: host01,host02
Database is administrator managed

====================================================================================================
+++ STANDBY NON-RAC
====================================================================================================
SQL> show parameter db%name
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      test
db_unique_name                       string      testdr
SQL> 

$ sysresv|tail -1
Oracle Instance alive for sid "test"

$ env|grep ORACLE
ORACLE_SID=test (db_name)
ORACLE_UNQNAME=testdr (db_unique_name)

$ srvctl config database -d $ORACLE_UNQNAME
Database unique name: testdr
Database name: test
Oracle home: /u01/app/oracle/product/11g/db_1
Oracle user: oracle
Spfile:
Domain:
Start options: open
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Database instance: test
Disk Groups: DATA,FLASH
Services:

====================================================================================================
DATAGUARD BROKER CONFIGURATION
====================================================================================================
DGMGRL> show configuration

Configuration - dg_test (db_name)

  Protection Mode: MaxPerformance
  Databases:
    test   - Primary database (db_unique_name)
    testdr - Physical standby database (db_unique_name)

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS

DGMGRL> show database test

Database - test

  Role:            PRIMARY
  Intended State:  TRANSPORT-OFF
  Instance(s):
    test_1
    test_2

Database Status:
SUCCESS

DGMGRL> show database testdr

Database - testdr

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

Database Status:
SUCCESS

DGMGRL> exit

====================================================================================================
ls -l dg*.sh
====================================================================================================
-rwxr-xr-x    1 oracle   dba             377 May 08 21:50 dg_lag.sh
-rwxr-x---    1 oracle   dba             445 May 08 20:12 dg_start.sh
-rwxr-xr-x    1 oracle   dba             337 May 08 20:05 dg_status.sh
-rwxr-x---    1 oracle   dba             447 May 08 20:12 dg_stop.sh

====================================================================================================
dg_lag.sh
====================================================================================================
#!/bin/sh -e
check_dg()
{
dgmgrl -echo << END
connect /
show database ${ORACLE_SID} SendQEntries
show database ${ORACLE_UNQNAME} RecvQEntries
show database ${ORACLE_UNQNAME}
exit
END
}
. ~/oracle_staging
check_dg
. ~/oracle_testing
check_dg
exit

====================================================================================================
cat dg_start.sh
====================================================================================================
#!/bin/sh -e
check_dg()
{
dgmgrl -echo << END
connect /
edit database ${ORACLE_SID} set state='TRANSPORT-ON';
edit database ${ORACLE_UNQNAME} set state='APPLY-ON';
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
}
. ~/oracle_staging
check_dg
. ~/oracle_testing
check_dg
exit

====================================================================================================
dg_status.sh
====================================================================================================
#!/bin/sh -e
check_dg()
{
dgmgrl -echo << END
connect /
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
}
. ~/oracle_staging
check_dg
. ~/oracle_testing
check_dg
exit

====================================================================================================
dg_stop.sh
====================================================================================================
#!/bin/sh -e
check_dg()
{
dgmgrl -echo << END
connect /
edit database ${ORACLE_SID} set state='TRANSPORT-OFF';
edit database ${ORACLE_UNQNAME} set state='APPLY-OFF';
show configuration
show database ${ORACLE_SID}
show database ${ORACLE_UNQNAME}
exit
END
}
check_dg
. ~/oracle_staging
check_dg
. ~/oracle_testing
check_dg
exit


February 19, 2018

How Do You Create Data Guard Configuration?

Filed under: 12c,Dataguard,dgmgrl — mdinh @ 11:30 pm

I have taken for granted to create Data Guard Configuration the same way most of the time that I don’t know what goes wrong when done differently.

oracle@racnode-dc1-1:hawk1:/home/oracle
$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Mon Feb 19 23:41:49 2018

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


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

23:41:49 SYS @ hawk1:>show parameter db%name

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

23:42:04 SYS @ hawk1:>alter system set dg_broker_start=true sid='*' scope=memory;

System altered.

23:42:40 SYS @ hawk1:>


+++ CREATE CONFIGURATION USING UPPER CASE WITHOUT QUOTES

oracle@racnode-dc1-1:hawk1:/home/oracle
$ dgmgrl /
DGMGRL for Linux: Version 12.1.0.2.0 - 64bit Production

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

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

--- NO QUOTES USE AND ALL UPPERCASE - EASIEST METHOD
--- Broker convert database to match that of db_unique_name

DGMGRL> CREATE CONFIGURATION DG_CONFIG AS PRIMARY DATABASE IS HAWK CONNECT IDENTIFIER IS HAWK;
Configuration "dg_config" created with primary database "hawk"

DGMGRL> show configuration

Configuration - dg_config

  Protection Mode: MaxPerformance
  Members:
  hawk - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> show database hawk

Database - hawk

  Role:               PRIMARY
  Intended State:     OFFLINE
  Instance(s):
    hawk1
    hawk2

Database Status:
DISABLED

DGMGRL> remove configuration
Removed configuration

--- CONFIGURATION IS UPPERCASE 
--- Does it look better in uppercase?
DGMGRL> CREATE CONFIGURATION 'DG_CONFIG' AS PRIMARY DATABASE IS 'hawk' CONNECT IDENTIFIER IS HAWK;
Configuration "DG_CONFIG" created with primary database "hawk"
DGMGRL> show configuration

Configuration - DG_CONFIG

  Protection Mode: MaxPerformance
  Members:
  hawk - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> show database hawk

Database - hawk

  Role:               PRIMARY
  Intended State:     OFFLINE
  Instance(s):
    hawk1
    hawk2

Database Status:
DISABLED

DGMGRL> remove configuration
Removed configuration

+++ ISSUES OCCUR WHEN USING UPPERCASE WITH QUOTES FOR DATABASE
+++ Not sure if this will work as I have not tested end to end. Why create it this way to begin with?
DGMGRL> CREATE CONFIGURATION 'DG_CONFIG' AS PRIMARY DATABASE IS 'HAWK' CONNECT IDENTIFIER IS HAWK;
Configuration "DG_CONFIG" created with primary database "HAWK"
DGMGRL> show configuration

Configuration - DG_CONFIG

  Protection Mode: MaxPerformance
  Members:
  HAWK - Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> show database hawk
Object "hawk" was not found

DGMGRL> show database HAWK
Object "hawk" was not found

DGMGRL> show database 'HAWK';

Database - HAWK

  Role:               PRIMARY
  Intended State:     OFFLINE
  Instance(s):
    hawk1
    hawk2

Database Status:
DISABLED

DGMGRL>

REFERENCE:

CREATE CONFIGURATION

CREATE CONFIGURATION configuration_name AS PRIMARY DATABASE IS database-name CONNECT IDENTIFIER IS connect-identifier;

database-name
The name that will be used by the broker to refer to the primary database. 
It must match (case-insensitive) the value of the primary database DB_UNIQUE_NAME initialization parameter.

December 9, 2016

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

Create a free website or blog at WordPress.com.