Cloud Control. Migration of passwords and targets

GPS Open Source News

Hello everyone!! Times of recollection are coming and what better way to spend time than to plan our next migration to Oracle Cloud Control 13cr4. We know that it can be tedious and that it does not always work well, so today we will show you how to export all the passwords and be able to recreate the targets in another Cloud Control.

Where is the data located in Cloud Control?

Information regarding user names, passwords, and roles are stored in table em_nc_cred_columns. When we see this table we realize that the information is encrypted and we receive the following error:

 ORA-28239: No key provided
 ORA-06512: in "SYS. DBMS_CRYPTO_FFI", line 67
 ORA-06512: in "SYS. DBMS_CRYPTO", line 44
 ORA-06512: in "SYSMAN. EM_CRYPTO", line 250
 ORA-06512: Online 1
  28239. 00000 - "no key provided"
 *Cause: A NULL value was passed in as an encryption or decryption key.
 *Action: Provide a non-NULL value for the key. 

We received the error because Cloud Control does not have the master key on-premises.

That column is encrypted with the package em_crypto. The encryption algorithm uses a secret key that is stored in the “Administrator Credential Wallet” and a salt (random data for added security). The wallet file is located at $MIDDLEWARE_HOME/gc_inst/em/EMGC_OMS1/sysman/config/adminCredsWallet/cwallet.sso

We have to export the credential with the following command. It will ask us for the sysman password.

 [oracle@host01 ~]$ emctl config emkey -copy_to_repos
 Oracle Enterprise Manager Cloud Control 13c Release 4
 Copyright (c) 1996, 2020 Oracle Corporation. All rights reserved.
 Enter Enterprise Manager Root (SYSMAN) Password :
 The EMKey has been copied to the Management Repository. This operation will cause the EMKey to become unsecure.
 After the required operation has been completed, secure the EMKey by running "emctl config emkey -remove_from_repos".
 [oracle@host01 ~]$ 

We already have our EMKey ready to be able to read the information from the tables.

We can read the information with the next query:

SELECT c.cred_owner, c.cred_name, c.target_type, (SELECT em_crypto.decrypt(p.cred_attr_value, p.cred_salt) FROM em_nc_cred_columns p WHERE c.cred_guid = p.cred_guid AND lower(P.CRED_ATTR_NAME) LIKE ‘%user%’) username, (SELECT em_crypto.decrypt(p.cred_attr_value, p.cred_salt) FROM em_nc_cred_columns p WHERE c.cred_guid = p.cred_guid AND lower(P.CRED_ATTR_NAME) LIKE ‘%role%’) rolename, (SELECT em_crypto.decrypt(p.cred_attr_value, p.cred_salt) FROM em_nc_cred_columns p WHERE c.cred_guid = p.cred_guid AND lower(P.CRED_ATTR_NAME) LIKE ‘%password%’) password FROM em_nc_creds c WHERE c.cred_owner <> ‘‘ ORDER BY cred_owner;

Now we can read the information.

Credentials Oracle Cloud Control

The passwords are in plain text. Now that we have the passwords, we can extract the creation statements through dynamic scripts.

Creating a migration script

select 'emcli add_target -name="'|| TARGETNAME || '" -type="oracle_database" -host="' || HOSTNAME || '" -credentials="UserName:DBSNMP;password:' || password || '; Role:normal" -properties="SID:' || || INSTANCE '; Port:' || PORT || ';OracleHome:' || ORACLEHOME || '; MachineName:' || upper(SUBSTR(MACHINENAME, 1, LENGTH(MACHINENAME) - 23)) || '"'
 from (
 Select 
 (SELECT property_value FROM mgmt$target_properties where property_name='OracleHome' and target_type='oracle_database' and target_guid=t.target_guid ) ORACLEHOME,
 (select target_name from MGMT$AGENTS_MONITORING_TARGETS where target_type='oracle_database' and target_guid=t.target_guid) TARGETNAME, 
 (select agent_host_name from MGMT$AGENTS_MONITORING_TARGETS where target_type='oracle_database' and target_guid=t.target_guid) HOSTNAME,
 (SELECT property_value FROM mgmt$target_properties where property_name='Port' and target_guid=t.target_guid) PORT 
 (SELECT property_value FROM mgmt$target_properties where property_name='InstanceName' and target_guid=t.target_guid) INSTANCE
  (select agent_host_name from MGMT$AGENTS_MONITORING_TARGETS where target_type='oracle_database' and target_guid=t.target_guid) MACHINENAME,
 (SELECT em_crypto.decrypt(p.cred_attr_value, p.cred_salt) FROM em_nc_cred_columns p WHERE c.cred_guid = p.cred_guid AND lower(P.CRED_ATTR_NAME) LIKE '%password%') password,
 (SELECT em_crypto.decrypt(p.cred_attr_value, p.cred_salt) FROM em_nc_cred_columns p WHERE c.cred_guid = p.cred_guid AND lower(P.CRED_ATTR_NAME) LIKE '%user%') username
 from MGMT$AGENTS_MONITORING_TARGETS m join mgmt$target_properties t on m.target_guid=t.target_guid
 JOIN em_nc_creds c ON m.target_guid=c.target_guid
 where m.target_type='oracle_database'
  )
 where username='dbsnmp' 
 group by ORACLEHOME, TARGETNAME, HOSTNAME, PORT, INSTANCE, MACHINENAME,password,username 
export_all_targets_script.sql

We hope this helps you with the migration!! If you continue with problems or doubts do not think about it anymore and contact us that we will be happy to help you!

See you next week where we will continue with topics of Cloud control 13cr4, until then, a lot of encouragement with the migrations !!!!

If you do not want to miss these tricks and news related to databases and Open Source, do not hesitate to subscribe to our newsletter. You can unsubscribe whenever you want and we only send one email per month.

More info: https://docs.oracle.com/cd/E24628_01/index.htm

Comments are closed.