UTF8 Migration STEP02.CHECK.TABLESPACE.ORA.DST ends with ORA-12154

Products

• CA Automic Workload Automation - Automation Engine
• CA Automic One Automation

Issue/Introduction

When running the PCK.AUTOMIC_UTF-8-MIG.START workflow the first time through, the step 2 script, PCK.AUTOMIC_UTF-8.MIG-STEP02.CHECK.TABLESPACE.DST, fails running against Oracle.  Diving deeper into the details, the following error shows:

ORA-12154: TNS:could not resolve the connect identifier specified

This script gathers the results of the Oracle SQL statement in PCK.AUTOMIC_UTF-8.MIG.SQL.CHECK.TABLESPACE.ORA.DST.  Attempting to preview this comes up with the following error:

Query could not be executed: 'ORA-12154: TNS:could not resolve the connect identifier specified (Client: '0300', PREVIEW, Variable: '')'

 

Environment

Source AE: 12.3 or 21.0
Database: Oracle

Cause

The uc4source link is not set up correctly

Resolution

A test of the following can be done on the destination Oracle database:

with mytbs as
(select distinct tablespace_name from user_tablespaces)
select tablespace_name, sum(bytes)/1024/1024/1024 as GB 
 from user_segments@uc4source us
 WHERE not exists (select 1 from mytbs t where us.tablespace_name=t.tablespace_name)
group by tablespace_name

If this still comes back with a TNS error, the uc4source link is not set up correctly.  There is an example of how to set up this link in the documentation under Migrating the AE DB to UTF-8 - Recommendations (step 10, 2) 

To resolve this, a DBA should be consulted on setting up the link correctly.  For example, the documentation points to the example:

CREATE DATABASE LINK uc4source CONNECT TO <SOURCE_DATABASE_USERNAME> IDENTIFIED BY '<SOURCE_DATABASE_PASSWORD>' USING '<SOURCE_DATABASE_HOST>:<SOURCE_DATABASE_PORT>/<SOURCE_DATABASE_NAME>;

However, it may be that the <SOURCE_DATABASE_HOST>:<SOURCE_DATABASE_PORT> is unnecessary.  In such a case, it may be enough to use the following:

CREATE DATABASE LINK uc4source CONNECT TO <SOURCE_DATABASE_USERNAME> IDENTIFIED BY '<SOURCE_DATABASE_PASSWORD>' USING '<SOURCE_DATABASE_NAME>;

A DBA should help in creating this link and verifying it's being set up correctly.  To confirm this, the following should run successfully when queried on the destination database:

with mytbs as
(select distinct tablespace_name from user_tablespaces)
select tablespace_name, sum(bytes)/1024/1024/1024 as GB 
 from user_segments@uc4source us
 WHERE not exists (select 1 from mytbs t where us.tablespace_name=t.tablespace_name)
group by tablespace_name