Sunday, August 31, 2008

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).

8 comments:

Lunameadow said...

This blog is the general information for the feature. You got a good work for these blog.We have a developing our creative content of this mind.Thank you for this blog. This for very interesting and useful.

Oracle Training in Chennai

Anonymous said...

Hi,
This blog is the general information for the feature.This very useful and interesting..

Weblogic Application Server training

seo said...

I Cleared All My doubts And Also Developed my Basics and learned new concepts through this blog.Coming to Our self, We are the top most Providers For Urgent Care In Chicago.really ,Post as many As you Can Because You Are Sharing Lot of Valuable Information.Have A Nice Day.

Peter Johnson said...

Really Awe! Great And Valuable Information Provided by the blog.Very Useful and helpful.Coming to our self,we are the leading providers for Restaurant Equipment Parts In Us.Great Job.Really Thanks For Posting.

Unknown said...

Awe! What An Blog Very Helpful and interesting Really A great center for acquiring knowledge. Very Helpful Post And Explained Very Clearly About All the things. Very Helpful. Coming To Our Self We Provide US Medical Residency Really Thankful For the blogger providing such a great information.Thank you. Have a Nice Day.

Lathika Honey said...

Really Thanks For Posting Such a Useful and informative article. I would like to share this blog with my friends and Colleagues medical residency in USA

svr online Training said...

Really A great informative blog post this blog provides only valuable information on Salesforce Certification Training.Thanks For Sharing.

24Layouts said...

Thanks For Sharing Such an Useful Info...

plots for sale in vizag