Home » Server Options » Replication » Add_Master_Database fails (Oracle 11g under Windows)
icon5.gif  Add_Master_Database fails [message #349531] Mon, 22 September 2008 02:42 Go to next message
PayoRanger
Messages: 7
Registered: September 2008
Junior Member
Hi Everybody,

thanks in advance for your help. I'm trying to configure and run replication. I have two PC's running as Oracle servers, having both of them the same configuration:


    Windows XP Pro
    Oracle 11g Enterprise Edition


First server is called Oracle1, and database name is orcl1. Second server name is Oracle2 and database name is orcl2

I'm running the following code:


SYSTEM/oracle1

alter system set global_names=true;

alter system set job_queue_processes=100;

ALTER DATABASE RENAME global_name TO orcl1.world;


SYSTEM/oracle2

alter system set global_names=true;

alter system set job_queue_processes=100;

ALTER DATABASE RENAME global_name TO orcl2.world;


SYSTEM/oracle1

CREATE PUBLIC DATABASE LINK orcl2.world USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=Oracle2)(Port=1521)))(CONNECT_DATA=(SID=orcl2.world)))';

CREATE USER repadmin IDENTIFIED BY clave
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

EXECUTE Dbms_Defer_Sys.Register_Propagator(username=>'REPADMIN');

GRANT EXECUTE ANY PROCEDURE TO repadmin;

EXECUTE Dbms_Repcat_Admin.Grant_Admin_Any_Repgroup('REPADMIN');

EXECUTE Dbms_Repcat_Admin.Grant_Admin_Any_Schema (username => 'REPADMIN');

GRANT LOCK ANY TABLE TO repadmin;

GRANT COMMENT ANY TABLE TO repadmin;


SYSTEM/oracle2

CREATE PUBLIC DATABASE LINK orcl1.world USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=Oracle1)(Port=1521)))(CONNECT_DATA=(SID=orcl1.world)))';

CREATE USER repadmin IDENTIFIED BY clave
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users;

EXECUTE Dbms_Defer_Sys.Register_Propagator(username=>'REPADMIN');

GRANT EXECUTE ANY PROCEDURE TO repadmin;

EXECUTE Dbms_Repcat_Admin.Grant_Admin_Any_Repgroup('REPADMIN');

EXECUTE Dbms_Repcat_Admin.Grant_Admin_Any_Schema (username => 'REPADMIN');

GRANT LOCK ANY TABLE TO repadmin;

GRANT COMMENT ANY TABLE TO repadmin;


REPADMIN/oracle1

CREATE DATABASE LINK orcl2.world CONNECT TO repadmin IDENTIFIED BY clave USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=Oracle2)(Port=1521)))(CONNECT_DATA=(SID=orcl2.world)))';

EXECUTE Dbms_Defer_Sys.Schedule_Push(destination => 'orcl2.world', interval => 'sysdate+1/24/60', next_date => sysdate+1/24/60, stop_on_error => FALSE, delay_seconds => 0, parallelism => 1);

EXECUTE Dbms_Defer_Sys.Schedule_Purge(next_date => sysdate+1/24, interval => 'sysdate+1/24');


REPADMIN/oracle2

CREATE DATABASE LINK orcl1.world CONNECT TO repadmin IDENTIFIED BY clave USING
'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=Oracle1)(Port=1521)))(CONNECT_DATA=(SID=orcl1.world)))';

EXECUTE Dbms_Defer_Sys.Schedule_Push(destination => 'orcl1.world',interval => 'sysdate+1/24/60', next_date => sysdate+1/24/60, stop_on_error => FALSE, delay_seconds => 0, parallelism => 1);

EXECUTE Dbms_Defer_Sys.Schedule_Purge(next_date => sysdate+1/24, interval => 'sysdate+1/24');


REPADMIN/oracle1

EXECUTE Dbms_Repcat.Create_Master_Repgroup('REP_01');

SELECT * FROM dba_repsites WHERE gname = 'REP_01';

EXECUTE Dbms_Repcat.Add_Master_Database(gname => 'REP_01', master => 'orcl2.world', propagation_mode => 'SYNCHRONOUS');



At this point I got the following error message:

ORA-04052: error occurred when looking up remote object REPADMIN.SYS@ORCL2.WORLD
ORA-00604: error occurred at recursive SQL level 2
ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
ORA-06512: at "SYS.DBMS_REPCAT_UTL", line 4279
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2156
ORA-06512: at "SYS.DBMS_REPCAT", line 146
ORA-06512: at line 1


It looks like database orcl1 can't access orcl2. Any Idea?

Thanks

Report message to a moderator

Re: Add_Master_Database fails [message #349535 is a reply to message #349531] Mon, 22 September 2008 02:46 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
 
ORA-12505: TNS:listener does not currently know of SID given in connect descriptor
 *Cause:  The listener received a request to establish a connection to a
 database or other service. The connect descriptor received by the listener
 specified a SID for an instance (usually a database instance) that either
 has not yet dynamically registered with the listener or has not been
 statically configured for the listener. This may be a temporary condition
 such as after the listener has started, but before the database instance
 has registered with the listener.
 *Action:
  - Wait a moment and try to connect a second time.
  - Check which instances are currently known by the listener by executing:
    lsnrctl services <listener name>
  - Check that the SID parameter in the connect descriptor specifies
    an instance known by the listener.
  - Check for an event in the listener.log file.

Regards
Michel

Report message to a moderator

Re: Add_Master_Database fails [message #349876 is a reply to message #349535] Tue, 23 September 2008 06:40 Go to previous messageGo to next message
PayoRanger
Messages: 7
Registered: September 2008
Junior Member
As you proposed, the problem is with the links. I've been checking the links and I've found the following:



    Whith parameter global_names set to value true, private and public links don't work.
    If this parameter is set to false links and replication works perfectly.


Why links don't work when global_names is set to true?

Any idea?

Thanks in advance

Report message to a moderator

Re: Add_Master_Database fails [message #350036 is a reply to message #349876] Tue, 23 September 2008 17:43 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member
If there is no problem in your listener & TNS Entry.

Please let us know below output from your master & materialized view site.



1 select * from global_name

2 SQL> show parameter global


Babu

[Updated on: Tue, 23 September 2008 17:44]

Report message to a moderator

Re: Add_Master_Database fails [message #350092 is a reply to message #350036] Wed, 24 September 2008 01:47 Go to previous messageGo to next message
PayoRanger
Messages: 7
Registered: September 2008
Junior Member
Now it looks fine, it works. What I've tried is the following:

alter system set global_names=false;

and now everything is OK. Is this a problem or replication can work with this value for this parameter?

Report message to a moderator

Re: Add_Master_Database fails [message #350340 is a reply to message #350092] Wed, 24 September 2008 15:26 Go to previous message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member
Quote:
Is this a problem or replication can work with this value for this parameter?


This is replication related only.

Once you configure advanced/multi-master replication in your evn. you need to configure global_name is true.

But you need to change you global_name using



alter database rename global_name to 'SID.SERVICE_NAME'


This is the best configuration.

Babu

[Updated on: Wed, 24 September 2008 15:27]

Report message to a moderator

Previous Topic: Oracle replication between different oracle versions?
Next Topic: Pb package DBMS_SNAP_INTERNAL invalid in replication 9.2.0.1
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Feb 10 22:51:12 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.