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
