ORA-23375 when adding new master site in multi-master environment

If your database global name contains a word that belongs to the list of Oracle Database Reserved Words and you are planning to add this database as a new master site in multi-master environment, you may want to consider changing the database global name so you can avoid ORA-23375 when executing DBMS_REPCAT.ADD_MASTER_DATABASE procedure.

This is especially important for those databases that have their global name containing country internet code of any of the following countries: Austria (AT), Belarus (BY), India (IN), Iceland (IS), etc. AT, BY, IN, IS are among the database reserved words.

You may experience problems even before you try to add the new master site. The creation of the database link to the new master site may fail with:

ORA-02084: database name is missing a component

However, you may not get this error in some cases, for instance when AT keyword is used; or if you put the database link name in double-quotes.

(the environment used in the examples below is DB1.MYDOMAIN.COM(master definition site, release 10.2.0.2) and DB11G.MYDOMAIN.IN (master site, release 11.1.0.6))

SQL>  create database link DB11G.MYDOMAIN.IN 
  2  connect to rep_admin
  3  identified by “rep_password”
  4  using 'DB11G'; 
 create database link DB11G.MYDOMAIN.IN 
                      * 
ERROR at line 1: 
ORA-02084: database name is missing a component

SQL>  create database link "DB11G.MYDOMAIN.IN" 
  2  connect to rep_admin
  3  identified by "rep_password" 
  4  using 'db11g'; 

Database link created.

SQL> create database link DB11G.MYDOMAIN.AT             
  2  connect to rep_admin
  3  identified by "rep_password" 
  4  using 'DB11G'; 

Database link created. 

But, even if the database link creation succeeds, the attempt to add the new master site would definitely fail with:

ORA-23375: feature is incompatible with database version at global database name

SQL> begin 
  2    dbms_repcat.add_master_database( 
  3    gname=>'TEST_GROUP', 
  4    master=>'"DB11G.MYDOMAIN.IN"', 
  5    use_existing_objects =>true, 
  6    copy_rows =>false, 
  7    propagation_mode=>'ASYNCHRONOUS'); 
  8  end;  
  9  / 
begin 
* 
ERROR at line 1: 
ORA-23375: feature is incompatible with database version at DB11G.MYDOMAIN.IN 
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2159 
ORA-06512: at "SYS.DBMS_REPCAT", line 146 
ORA-06512: at line 2 


SQL>     

SQL> begin 
  2 dbms_repcat.add_master_database( 
  3 gname=>'TEST_GROUP', 
  4 master=>'DB11G.MYDOMAIN.AT', 
  5 use_existing_objects =>true, 
  6 copy_rows =>false, 
  7 propagation_mode=>'ASYNCHRONOUS'); 
  8 end; 
  9  / 

begin 
* 
ERROR at line 1: 
ORA-23375: feature is incompatible with database version at DB11G.MYDOMAIN.AT 
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86 
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2159 
ORA-06512: at "SYS.DBMS_REPCAT", line 146 
ORA-06512: at line 2 

If you turn 10046 trace event you can notice that the error occurs when DBMS_REPCAT_RPC.REPLICATION_VERSION_RC and DBMS_REPCAT_UTL2.REPLICATION_VERSION procedures are invoked against the new master database.

PARSE ERROR #39:len=73 dep=1 uid=61 oct=47 lid=61 tim=1191209094695404 err=6550 
BEGIN sys.dbms_repcat_rpc.replication_version_rc@DB11G.MYDOMAIN.IN (:rv);END; 
===================== 
PARSE ERROR #39:len=72 dep=1 uid=61 oct=47 lid=61 tim=1191209094702737 err=6550 
BEGIN  sys.dbms_repcat_utl2.replication_version@DB11G.MYDOMAIN.IN (:rv);END; 

To solve this problem the global name of the new master database should be changed. Once the global name is changed to value that does not contain any reserved word, you should be able to add the database to the replication environment.

In the example below, I will try to add a new master site which global name was changed to DB11G.FOO.BAR

SQL>conn sys/***@DB11G as sysdba
Connected.
SQL> alter database rename global_name to DB11G.FOO.BAR; 

Database altered.

SQL> conn rep_admin/rep_password@DB1
Connected.
SQL>
SQL> create database link DB11G.FOO.BAR 
  2  connect to rep_admin
  3  identified by "rep_password" 
  4  using 'DB11G'; 

Database link created.

SQL> begin 
  2    dbms_repcat.add_master_database( 
  3    gname=>'TEST_GROUP', 
  4    master=>'DB11G.FOO.BAR', 
  5    use_existing_objects =>true, 
  6    copy_rows =>false, 
  7    propagation_mode=>'ASYNCHRONOUS'); 
  8  end; 
  9  / 

PL/SQL procedure successfully completed. 


SQL> column dblink format a30 
SQL> select DBLINK, MASTERDEF, MASTER             
  2  from dba_repsites; 

DBLINK                         M M 
------------------------------ - - 
DB1.MYDOMAIN.COM               Y Y 
DB11G.FOO.BAR                  N Y

There are few ways to find Oracle PL/SQL reserved words:

1. V$RESERVED_WORDS


2. Oracle documentation (PL/SQL Reserved Words and Keywords). Check your version specific documentation available on http://tahiti.oracle.com


3. Issue HELP RESERVED WORDS (PL/SQL) from Sql*Plus (if Sql*Plus help is installed).