By William Sescu
I would like to make my DataGuard environment more secure, by eliminating the typing of “connect sys/Manager1” for my DGMGRL commands. Especially the ones, that I have in my scripts. For example:
oracle@dbidg01:/home/oracle/ [DBIT122] dgmgrl <<-EOF > connect sys/Manager1 > show configuration verbose; > EOF
or something like that:
oracle@dbidg01:/u01/app/oracle/local/dg/ [DBIT122] cat show_config.dg connect sys/Manager1; show configuration; oracle@dbidg01:/u01/app/oracle/local/dg/ [DBIT122] dgmgrl @show_config.dg DGMGRL for Linux: Release 12.2.0.1.0 - Production on Wed Jan 4 12:54:11 2017 Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. Connected to "DBIT122_SITE1" Connected as SYSDG. Configuration - DBIT122 Protection Mode: MaxAvailability Members: DBIT122_SITE1 - Primary database DBIT122_SITE2 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 39 seconds ago)
HINT: Be aware that “dgmgrl [<options>] @script_file_name” is a new feature with the Broker in Oracle 12.2. It was not possible to use “dgmgrl @script” beforehand.
Ok. So how can I make my scripts more secure? Of course, by using wallets, like we did already with the observer configuration. See http://dbi-services.com/blog/oracle-12cr2-how-to-setup-dataguard-observer-with-oracle-wallets/
However, I want to do also the switchover and other operations with wallets.
So, lets create the necessary wallets for the SYS user on the Primary and the Standby.
-- Primary oracle@dbidg01:/u01/app/oracle/network/admin/ [DBIT122] mkstore -wrl /u01/app/oracle/admin/wallets -createCredential DBIT122_SITE1 SYS Oracle Secret Store Tool : Version 12.2.0.1.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved. Your secret/Password is missing in the command line Enter your secret/Password: Re-enter your secret/Password: Enter wallet password: oracle@dbidg01:/u01/app/oracle/network/admin/ [DBIT122] mkstore -wrl /u01/app/oracle/admin/wallets -createCredential DBIT122_SITE2 SYS Oracle Secret Store Tool : Version 12.2.0.1.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved. Your secret/Password is missing in the command line Enter your secret/Password: Re-enter your secret/Password: Enter wallet password: -- Standby oracle@dbidg02:/u01/app/oracle/network/admin/ [DBIT122] mkstore -wrl /u01/app/oracle/admin/wallets -createCredential DBIT122_SITE1 SYS Oracle Secret Store Tool : Version 12.2.0.1.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved. Your secret/Password is missing in the command line Enter your secret/Password: Re-enter your secret/Password: Enter wallet password: oracle@dbidg02:/u01/app/oracle/network/admin/ [DBIT122] mkstore -wrl /u01/app/oracle/admin/wallets -createCredential DBIT122_SITE2 SYS Oracle Secret Store Tool : Version 12.2.0.1.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved. Your secret/Password is missing in the command line Enter your secret/Password: Re-enter your secret/Password: Enter wallet password:
And of course, we have to test the connections to see if everything is working correctly.
-- Primary sqlplus /@DBIT122_SITE1 as sysdba sqlplus /@DBIT122_SITE2 as sysdba DGMGRL> connect /@DBIT122_SITE1 DGMGRL> connect /@DBIT122_SITE2 -- Standby sqlplus /@DBIT122_SITE1 as sysdba sqlplus /@DBIT122_SITE2 as sysdba DGMGRL> connect /@DBIT122_SITE1 DGMGRL> connect /@DBIT122_SITE2
So far, so good. My connections with the wallets work from the Primary to the Standby and the other way around. Now, lets try to do a DataGuard switchover with wallets.
DGMGRL> connect /@DBIT122_SITE1 Connected to "DBIT122_SITE1" Connected as SYSDBA. DGMGRL> DGMGRL> DGMGRL> show configuration; Configuration - DBIT122 Protection Mode: MaxAvailability Members: DBIT122_SITE1 - Primary database DBIT122_SITE2 - (*) Physical standby database Fast-Start Failover: ENABLED Configuration Status: SUCCESS (status updated 58 seconds ago) DGMGRL> SWITCHOVER TO 'DBIT122_SITE2'; Performing switchover NOW, please wait... Operation requires a connection to database "DBIT122_SITE2" Connecting ... Connected to "DBIT122_SITE2" Connected as SYSDBA. New primary database "DBIT122_SITE2" is opening... Operation requires start up of instance "DBIT122" on database "DBIT122_SITE1" Starting instance "DBIT122"... ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. Please complete the following steps to finish switchover: start up instance "DBIT122" of database "DBIT122_SITE1" DGMGRL>
Oppsssssss … doesn’t look good. It says “invalid username/password”, but everything worked beforehand. Ok. That output does not give me too much information. Lets try the whole thing again with the Debug mode … dgmgrl -debug
oracle@dbidg01:/u01/app/oracle/network/admin/ [DBIT122] dgmgrl -debug DGMGRL for Linux: Release 12.2.0.1.0 - Production on Wed Jan 4 11:04:21 2017 Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect /@DBIT122_SITE2 [W000 01/04 11:04:34.32] Connecting to database using DBIT122_SITE2. [W000 01/04 11:04:34.33] Attempt logon as SYSDG [W000 01/04 11:04:35.42] Attempt logon as SYSDBA [W000 01/04 11:04:35.47] Executing query [select db_unique_name from v$database]. [W000 01/04 11:04:35.47] Query result is 'DBIT122_SITE2' Connected to "DBIT122_SITE2" [W000 01/04 11:04:35.47] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;]. [W000 01/04 11:04:35.47] Oracle database version is '12.2.0.1.0' Connected as SYSDBA. DGMGRL> show configuration; Configuration - DBIT122 Protection Mode: MaxAvailability Members: DBIT122_SITE1 - Primary database DBIT122_SITE2 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 49 seconds ago) DGMGRL> switchover to 'DBIT122_SITE2'; Performing switchover NOW, please wait... New primary database "DBIT122_SITE2" is opening... Operation requires start up of instance "DBIT122" on database "DBIT122_SITE1" Starting instance "DBIT122"... [W000 01/04 11:05:04.99] Connecting to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBIT122_SITE1_DGMGRL)(UR=A)(INSTANCE_NAME=DBIT122)(SERVER=DEDICATED))). [W000 01/04 11:05:04.99] Attempt logon as SYSDG [W000 01/04 11:05:06.04] Attempt logon as SYSDBA ORA-01017: invalid username/password; logon denied Warning: You are no longer connected to ORACLE. Please complete the following steps to finish switchover: start up and mount instance "DBIT122" of database "DBIT122_SITE1" DGMGRL>
What is happening here? The Broker is not using the connect string “DBIT122_SITE1”, it is using the description list “(DESCRIPTION=(ADDRESS …..)”, and when I look up my credentials in the wallet, I see only credentials for “DBIT122_SITE1 SYS” and “DBIT122_SITE2 SYS”.
oracle@dbidg01:/home/oracle/ [DBIT122] mkstore -wrl /u01/app/oracle/admin/wallets -listCredential Oracle Secret Store Tool : Version 12.2.0.1.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved. Enter wallet password: List credential (index: connect_string username) 3: DBIT122_SITE2 SYS 2: DBIT122_SITE1 SYS 1: rcat rcat
The solution here is, to add the description list from the property StaticConnectIdentifier.
DGMGRL> show database 'DBIT122_SITE1' StaticConnectIdentifier; StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBIT122_SITE1_DGMGRL)(UR=A)(INSTANCE_NAME=DBIT122)(SERVER=DEDICATED)))' DGMGRL> show database 'DBIT122_SITE2' StaticConnectIdentifier; StaticConnectIdentifier = '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBIT122_SITE2_DGMGRL)(UR=A)(INSTANCE_NAME=DBIT122)(SERVER=DEDICATED)))'
Ok. Lets add the new credentials to our wallet. Be careful that you specify it exactly like they show up in the StaticConnectIdentifier.
-- Primary oracle@dbidg01:/home/oracle/ [DBIT122] mkstore -wrl /u01/app/oracle/admin/wallets -createCredential '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBIT122_SITE1_DGMGRL)(UR=A)(INSTANCE_NAME=DBIT122)(SERVER=DEDICATED)))' SYS Oracle Secret Store Tool : Version 12.2.0.1.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved. Your secret/Password is missing in the command line Enter your secret/Password: Re-enter your secret/Password: Enter wallet password: oracle@dbidg01:/home/oracle/ [DBIT122] mkstore -wrl /u01/app/oracle/admin/wallets -createCredential '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBIT122_SITE2_DGMGRL)(UR=A)(INSTANCE_NAME=DBIT122)(SERVER=DEDICATED)))' SYS Oracle Secret Store Tool : Version 12.2.0.1.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved. Your secret/Password is missing in the command line Enter your secret/Password: Re-enter your secret/Password: Enter wallet password: oracle@dbidg01:/home/oracle/ [DBIT122] mkstore -wrl /u01/app/oracle/admin/wallets -listCredential Oracle Secret Store Tool : Version 12.2.0.1.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved. Enter wallet password: List credential (index: connect_string username) 5: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBIT122_SITE2_DGMGRL)(UR=A)(INSTANCE_NAME=DBIT122)(SERVER=DEDICATED))) SYS 4: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBIT122_SITE1_DGMGRL)(UR=A)(INSTANCE_NAME=DBIT122)(SERVER=DEDICATED))) SYS 3: DBIT122_SITE2 SYS 2: DBIT122_SITE1 SYS 1: rcat rcat -- Standby oracle@dbidg02:/home/oracle/ [DBIT122] mkstore -wrl /u01/app/oracle/admin/wallets -createCredential '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBIT122_SITE1_DGMGRL)(UR=A)(INSTANCE_NAME=DBIT122)(SERVER=DEDICATED)))' SYS Oracle Secret Store Tool : Version 12.2.0.1.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved. Your secret/Password is missing in the command line Enter your secret/Password: Re-enter your secret/Password: Enter wallet password: oracle@dbidg02:/home/oracle/ [DBIT122] mkstore -wrl /u01/app/oracle/admin/wallets -createCredential '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBIT122_SITE2_DGMGRL)(UR=A)(INSTANCE_NAME=DBIT122)(SERVER=DEDICATED)))' SYS Oracle Secret Store Tool : Version 12.2.0.1.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved. Your secret/Password is missing in the command line Enter your secret/Password: Re-enter your secret/Password: Enter wallet password: oracle@dbidg02:/home/oracle/ [DBIT122] mkstore -wrl /u01/app/oracle/admin/wallets -listCredential Oracle Secret Store Tool : Version 12.2.0.1.0 Copyright (c) 2004, 2016, Oracle and/or its affiliates. All rights reserved. Enter wallet password: List credential (index: connect_string username) 5: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBIT122_SITE2_DGMGRL)(UR=A)(INSTANCE_NAME=DBIT122)(SERVER=DEDICATED))) SYS 4: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg01)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBIT122_SITE1_DGMGRL)(UR=A)(INSTANCE_NAME=DBIT122)(SERVER=DEDICATED))) SYS 3: DBIT122_SITE2 SYS 2: DBIT122_SITE1 SYS 1: rcat rcat
After everything is setup and done, lets try again the switchover in debug mode.
oracle@dbidg01:/home/oracle/ [DBIT122] dgmgrl -debug DGMGRL for Linux: Release 12.2.0.1.0 - Production on Wed Jan 4 11:22:38 2017 Copyright (c) 1982, 2016, Oracle and/or its affiliates. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL> connect /@DBIT122_SITE1 [W000 01/04 11:22:47.94] Connecting to database using DBIT122_SITE1. [W000 01/04 11:22:47.94] Attempt logon as SYSDG [W000 01/04 11:22:49.02] Attempt logon as SYSDBA [W000 01/04 11:22:49.06] Executing query [select db_unique_name from v$database]. [W000 01/04 11:22:49.06] Query result is 'DBIT122_SITE1' Connected to "DBIT122_SITE1" [W000 01/04 11:22:49.06] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;]. [W000 01/04 11:22:49.06] Oracle database version is '12.2.0.1.0' Connected as SYSDBA. DGMGRL> switchover to 'DBIT122_SITE1'; Performing switchover NOW, please wait... New primary database "DBIT122_SITE1" is opening... Operation requires start up of instance "DBIT122" on database "DBIT122_SITE2" Starting instance "DBIT122"... [W000 01/04 11:23:18.07] Connecting to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBIT122_SITE2_DGMGRL)(UR=A)(INSTANCE_NAME=DBIT122)(SERVER=DEDICATED))). [W000 01/04 11:23:18.07] Attempt logon as SYSDG [W000 01/04 11:23:19.15] Attempt logon as SYSDBA [W000 01/04 11:23:20.23] Executing query [select db_unique_name from v$database]. ORA-01034: ORACLE not available Process ID: 0 Session ID: 0 Serial number: 0 ORACLE instance started. [W000 01/04 11:23:36.03] Connecting to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBIT122_SITE2_DGMGRL)(UR=A)(INSTANCE_NAME=DBIT122)(SERVER=DEDICATED))). [W000 01/04 11:23:36.03] Attempt logon as SYSDG [W000 01/04 11:23:37.13] Attempt logon as SYSDBA [W000 01/04 11:23:37.17] Executing query [select db_unique_name from v$database]. ORA-01507: database not mounted [W000 01/04 11:23:37.20] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;]. [W000 01/04 11:23:37.20] Oracle database version is '12.2.0.1.0' [W000 01/04 11:23:37.20] Executing statement [alter database mount]. [W000 01/04 11:23:42.66] Statement [alter database mount] executed successfully. Database mounted. [W000 01/04 11:23:42.66] Checking for bootstrap done... [W000 01/04 11:23:42.67] Connecting to database using (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dbidg02)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DBIT122_SITE2_DGMGRL)(UR=A)(INSTANCE_NAME=DBIT122)(SERVER=DEDICATED))). [W000 01/04 11:23:42.67] Attempt logon as SYSDG [W000 01/04 11:23:43.77] Attempt logon as SYSDBA [W000 01/04 11:23:43.82] Executing query [select db_unique_name from v$database]. [W000 01/04 11:23:43.83] Query result is 'DBIT122_SITE2' Connected to "DBIT122_SITE2" [W000 01/04 11:23:43.83] Checking broker version [BEGIN :version := dbms_drs.dg_broker_info('VERSION'); END;]. [W000 01/04 11:23:43.83] Oracle database version is '12.2.0.1.0' [W000 01/04 11:23:55.85] Done waiting for bootstrap after 0 retries Switchover succeeded, new primary is "DBIT122_SITE1" DGMGRL> DGMGRL> show configuration; Configuration - DBIT122 Protection Mode: MaxAvailability Members: DBIT122_SITE1 - Primary database DBIT122_SITE2 - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 60 seconds ago) DGMGRL>
Now it worked perfectly, I can run now my switchover operations with wallets and I can run my scripts now without a password in clear text, like the following.
oracle@dbidg01:/home/oracle/ [DBIT122] dgmgrl <<-EOF > connect /@DBIT122_SITE1 > show configuration verbose; > EOF
Conclusion
Doing DataGuard switchovers with wallets work perfectly, if the setup was done correctly, and besides that, you can eliminate a lot of passwords in clear text that you might have laying around.
Cheers,
William