Solved

Oracle Replication: Invalid username/password on RESUME_MASTER_ACTIVITY

Posted on 2006-11-17
6
1,590 Views
Last Modified: 2007-12-19
Hi everyone,
I'm trying to set up multi-master replication on Oracle 10.2.0.1.0 by using steps listed on http://www.akadia.com/services/ora_replication_guide.html . Database names are QS10DB0 and QS10DB1 and they are on different servers in same LAN. Replication group is called REPG which I created on QS10DB0 and added another master database which is QS10DB1. Looking into DBA_REPSITES everything looks ok by me.

SQL> select gname,DBLINK from dba_repsites;

GNAME
------------------------------
DBLINK
----------------------------------------------------

REPG
QS10DB0

REPG
QS10DB1

I've came to the point where DBA_REPCATLOG has cleared and I try to execute RESUME_MASTER_ACTIVITY on QS10DB0.

When I do this I get:

SQL> BEGIN
  2     DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
  3       gname => 'REPG');
  4  END;
  5  /
BEGIN
*
ERROR at line 1:
ORA-04052: error occurred when looking up remote object SYS.SYS@QS10DB1
ORA-00604: error occurred at recursive SQL level 2
ORA-01017: invalid username/password; logon denied
ORA-02063: preceding line from QS10DB1
ORA-06512: at "SYS.DBMS_REPCAT_UTL4", line 119
ORA-06512: at "SYS.DBMS_REPCAT_UTL4", line 554
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 3533
ORA-06512: at "SYS.DBMS_REPCAT", line 826
ORA-06512: at line 2

I guess QS10DB0 can't connect to QS10DB1 because of  user account problems but I don't know how to fix it. Both databases were created as clones from a cold copy so they should be same.

Any help would be appreciated.
0
Comment
Question by:milan_novkovic
  • 4
6 Comments
 
LVL 47

Assisted Solution

by:schwertner
schwertner earned 200 total points
ID: 17964904
Edit the tnsnames.ora in the <QS10DB0_db_home>/network/admin directory, or if $TNS_ADMIN is set, edit the tnsnames.ora in that directory and add an alias for the master OID database e.g.

ASDB.UK.ORACLE.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = oidmasterserver.uk.oracle.com)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = asdb.uk.oracle.com)
)
)
0
 
LVL 16

Accepted Solution

by:
MohanKNair earned 300 total points
ID: 17964948
Check the database link. Create the dblink as SYS user and grant privileges to repadmin user or create public dblinks.
Try sample queries using sql*plus and dblink.
0
 

Author Comment

by:milan_novkovic
ID: 17964952
I think tnsnames.ora is ok since I can connect to one database from other server and vice versa using sqlplus and TOAD.

It looks like this (I changed server names):


QS10DB1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = QS10DB1)
    )
  )

QS10DB0 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = server0)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = QS10DB0)
    )
  )
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:milan_novkovic
ID: 17964981
@MohanKNair

I've created database links using steps from tutorial I listed in my initial post. I did the following:

connect sys/pass@QS10DB0;
CREATE PUBLIC DATABASE LINK QS10DB1 USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY=TCP)(PROTOCOL=TCP)(Host=server1)(Port=1521)))(CONNECT_DATA=(SID=QS10DB1)))';
CONNECT repadmin/repadmin@QS10DB0;
CREATE DATABASE LINK QS10DB1 CONNECT TO repadmin IDENTIFIED BY repadmin USING 'QS10DB1';

CONNECT sys/pass@QS10DB1 as sysdba;
CREATE PUBLIC DATABASE LINK QS10DB0 USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(COMMUNITY=TCP)(PROTOCOL=TCP)(Host=server1)(Port=1521)))(CONNECT_DATA=(SID=QS10DB0)))';
CONNECT repadmin/repadmin@QS10DB1;
CREATE DATABASE LINK QS10DB0 CONNECT TO repadmin IDENTIFIED BY repadmin USING 'QS10DB0';
0
 

Author Comment

by:milan_novkovic
ID: 17965113
I tries creating public database links, it seems I haven't created it correctly previosly. So now I'm getting follow error when trying to execute RESUME_MASTER_ACTIVITY:

SQL> BEGIN
  2     DBMS_REPCAT.RESUME_MASTER_ACTIVITY (
  3       gname => 'REPG');
  4  END;
  5  /
BEGIN
*
ERROR at line 1:
ORA-23358: invalid remote user
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_REPCAT_RPC", line 12
ORA-06512: at "SYS.DBMS_REPCAT_RPC", line 1783
ORA-06512: at "SYS.DBMS_REPCAT_UTL4", line 119
ORA-06512: at "SYS.DBMS_REPCAT_UTL4", line 554
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 3533
ORA-06512: at "SYS.DBMS_REPCAT", line 826
ORA-06512: at line 2

0
 

Author Comment

by:milan_novkovic
ID: 17965259
Ok, it working now! I've recreated dblinks, recreated replication support and RESUME_MASTER_ACTIVITY executed without errors. Data is being replicated between two databases now.

I'll split points between schwertner and MohanKNair but give MohanKNair more because he's answer helped me more to solve my problem.

Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
oracle query help 18 99
Pl/SQL Query 31 76
How to count the number of rows in multiple Oracle Tables 10 63
Oracle - SQL Parse String 5 20
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious sideā€¦
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now