OGG-08221, OCI Error ORA-44004 invalid qualified SQL Name

created at 08-23-2021 views: 20

Problem description

My colleague used OGG 19 version at a customer site to register the extraction process integration mode of DB 19 and reported an error

OGG-08221,OCI Error ORA-44004 invalid qualified SQL Name

match MOS

OGG-08221 Cannot Register Or Unregister EXTRACT <extractname> Error: OCI Error ORA (Status = 44004-ORA-44004: Invalid Qualified SQL Name) (Doc ID 2514820.1)
Oracle GoldenGate Command Interpreter for Oracle
Version OGGCORE_12.
AIX 6, ppc, 64bit (optimized), Oracle 12c on Dec 10 2018 09:15:59
GGSCI > register extract <extract_name> database
2019-03-04 16:07:30  ERROR   OGG-08221  Cannot register or unregister EXTRACT <extract_name> because of the following SQL error: OCI Error ORA (status = 44004-ORA-44004: invalid qualified SQL name
ORA-06512: at "SYS.DBMS_ASSERT", line 315
ORA-06512: at "SYS.DBMS_LOGREP_UTIL", line 304
ORA-06512: at "SYS.DBMS_CAPTURE_ADM", line 151
ORA-06512: at line 1).

The issue is due to the value of "TEST.1DC.COM" for source_global_name
BINDS #4866688648:
  oacdty=96 mxl=08(08) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=873 siz=96 off=0
  kxsbbbfp=12211adb0 bln=08 avl=08 flg=05
  value="<extract name>"
  oacdty=96 mxl=15(15) mxlc=00 mal=00 scl=00 pre=00
  oacflg=01 fl2=1000000 frm=01 csi=873 siz=0 off=8
  kxsbbbfp=12211adb8 bln=15 avl=15 flg=01
  value="TEST.1DC.COM"     <=============== 
A database global name has other restrictions than normal sql objects.Several creative database names cause the register of Integrated Extract to fail as these names fail in dbms_assert.qualified_sql_name().
Example names are:
  ABC.COMPUTE-A1111.ORACLECLOUD.INTERNAL  # the Minus sign in the domain part
  TEST.1DC.COM                               # a part of the domain name starts with a number

Please do the following to change from TEST.1DC.COM to TEST.DC.COM and then redo the REGISTER
SQL> alter database rename global_name to TEST.DC.COM
Note: Above change may break database links that point to this instance if using global naming.
On multi-tenant environments OGG currently checks following to sql names. 
1: global name of the container db 
2: full global name of the pdb 
3: short dbname of the pdb. 
So in addition to above change following is also needed on PDB

Did actual customers use this special name? Parameter query does not set domain

select value$ from sys.props$ where name='GLOBAL_DB_NAME';

This confirms that there are special characters in the GLOBAL_DB_NAME of the registered PDB. The internal integrated registration of OGG obtains this name through the DB parameter, and subsequent operations cannot parse this special character and report an error!

Then the solution is to adjust the parameter name of PDB and delete special characters!

created at:08-23-2021
edited at: 08-23-2021: