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
Advertisements
