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

15 comments:

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

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

24Layouts said...

Thanks For Sharing Such an Useful Info...

plots for sale in vizag

Anonymous said...

Keep sharing this kind of wonderful information.

Oracle Certification in Chennai | Oracle Training in Chennai

Unknown said...

Thanks For Sharing Valuable Info...

JNTU 99

Unknown said...

quite informative, thanks for sharing with us.Thanks for posting this useful content, Good to know about new things here,
Your post is really awesome. Your blog is really helpful for me to develop my skills in a right way. Thanks for sharing this unique information with us.
- Learn Digital Academy

devasuresh121@gmail.com said...

This blog is more informative step by step and i here by attached my site would you see this blog

7 tips to start a career in digital marketing

“Digital marketing is the marketing of product or service using digital technologies, mainly on the Internet, but also including mobile phones, display advertising, and any other digital medium”. This is the definition that you would get when you search for the term “Digital marketing” in google. Let’s give out a simpler explanation by saying, “the form of marketing, using the internet and technologies like phones, computer etc”.

we have offered to the advanced syllabus course digital marketing for available join now

more details click the link now

https://www.webdschool.com/digital-marketing-course-in-chennai.html

devasuresh121@gmail.com said...

Amazing blog very nice

Web designing trends in 2020

When we look into the trends, everything which is ruling today’s world was once a start up and slowly begun getting into. But Now they have literally transformed our lives on a tremendous note. To name a few, Facebook, Whats App, Twitter can be a promising proof for such a transformation and have a true impact on the digital world.


we have offered to the advanced syllabus course web design and development for available join now

more details click the link now

https://www.webdschool.com/web-development-course-in-chennai.html

Rakshitha said...

Superb Post..
SAP Training in Chennai
Java Training in Chennai
Selenium Training in Chennai
QTP Training in Chennai
Hardware and Networking Training in Chennai
CCNA Training in Chennai
MCITP Training in Chennai
Oracle Training in Chennai
PHP Training in Chennai
AWS Training in Chennai

Rakshitha said...

Great Stuff
SAP Training in Chennai
Java Training in Chennai
Software Testing Training in Chennai
Hardware and Networking Training in Chennai
Informatica Training in Chennai
CCNA Training in Chennai
Oracle Training in Chennai
Cloud Computing Training in Chennai
Azure Training in Chennai
AWS Training in Chennai

Rakshitha said...

Awesome Post...
SAP Training in Chennai
Java Training in Chennai
QTP Training in Chennai
Hardware and Networking Training in Chennai
SAP ABAP Training in Chennai
SAP FICO Training in Chennai
SAP SD Training in Chennai
SAP MM Training in Chennai
SAP PP Training in Chennai
AWS Training in Chennai

eswarigroup said...

Eswari Group offers high demanding services relating to businesses with 3 different companies like software solutions relating to digital technology with ASE Technologies. Construction activities building great projects and Ventures with ASE Infrastructures. Real Estate Services by having a large partnership with our builders and construction companies through Eswari Homes.

ASE Technologies said...

ASE Technologies is the Best Digital Marketing agency in Visakhapatnam providing all digital marketing services like SEO, SMM, Graphic Design, and Content writing.
Ase technologies are one of the Best Software Companies in Hyderabad we are expertise in providing SEO, SEM, SMM & Website Design & Development one-stop solution services, for More Services https://asetechnologies.in