• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2555
  • Last Modified:

Delete a oracle replication group facing a problem

Hi,

I have a 2 replication group as pre-below :-

SQL> select gname,master,status from dba_repgroup;

GNAME                          MASTER STATUS
------------------------------ ------ ---------
GROUP1                         Y      QUIESCED
KTCHANREP                      Y      NORMAL

After I issued a command to delete this 2 replication group:-

SQL> exec dbms_repcat.drop_master_repgroup(gname=>'GROUP1',all_sites => true);

begin dbms_repcat.drop_master_repgroup(gname=>'GROUP1',all_sites => true); end;

ORA-23312: not the masterdef according to ORCL.WIRES.CC
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 883
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2649
ORA-06512: at "SYS.DBMS_REPCAT", line 635
ORA-06512: at line 1

SQL> exec dbms_repcat.drop_master_repgroup(gname=>'KTCHANREP',all_sites => true);

begin dbms_repcat.drop_master_repgroup(gname=>'KTCHANREP',all_sites => true); end;

ORA-23312: not the masterdef according to ORCL.WIRES.CC
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 883
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2649
ORA-06512: at "SYS.DBMS_REPCAT", line 635
ORA-06512: at line 1

Remarks :- by the way this already on in my replciation salve  server, this 2 replication is on my master server.

Pls guide me how to solve this problem .

Thanks
K.T.Chan

0
ktchanhelp
Asked:
ktchanhelp
  • 22
  • 21
1 Solution
 
seazodiacCommented:
Chan,

can you try this query, and post back the output?

This simple query can be run on any one of your replication sites to identify which database is the Master Definition Site(MASTERDEF)  and which is the MASTER site for each repgroup.  
1) Connect as the replication administrator in sqlplus    
sqlplus repadmin/repadmin  
2) SQL> column masterdef format a10    
SQL> column master format a10    
SQL> column dblink format a20    
SQL> column gname format a12    
SQL> select gname, dblink, masterdef MASTERDEF, master MASTER        
 from sys.dba_repsites;  
Example output:  
GNAME        DBLINK               MASTERDEF  MASTER
------------ -------------------- ---------- ----------
SCOTT        VENUS.WORLD          N          Y
SCOTT        MARS.WORLD           Y          Y
REPUSER      VENUS.WORLD          N          Y
REPUSER      MARS.WORLD           Y          Y
0
 
ktchanhelpAuthor Commented:
Hi,

Connected to Oracle9i Enterprise Edition Release 9.0.1.4.0
Connected as repadmin


SQL> column masterdef format a10
SQL>  column master format a10
SQL> column dblink format a20
SQL> column gname format a12
SQL>
SQL> select gname, dblink, masterdef MASTERDEF, master MASTER
  2   from sys.dba_repsites;

GNAME        DBLINK               MASTERDEF  MASTER
------------ -------------------- ---------- ----------
finance      ORCL.WIRES.CC        Y          Y


This is my result.

Thanks
K.T.Chan
0
 
ktchanhelpAuthor Commented:
Hi,

This is a current result :-
SQL> select * from dba_repgroup;

SNAME                          MASTER STATUS    SCHEMA_COMMENT                                                                   GNAME                          FNAME                          RPC_PROCESSING_DISABLED OWNER
------------------------------ ------ --------- -------------------------------------------------------------------------------- ------------------------------ ------------------------------ ----------------------- ------------------------------
GROUP1                         Y      QUIESCED                                                                                   GROUP1                                                        N                       PUBLIC
KTCHANREP                      Y      NORMAL  


SQL> select gname, dblink, masterdef MASTERDEF, master MASTER from sys.dba_repsites;

GNAME                          DBLINK                                                                           MASTERDEF MASTER
------------------------------ -------------------------------------------------------------------------------- --------- ------

SQL>

SQL> exec dbms_repcat.drop_master_repgroup(gname=>'KTCHANREP',all_sites => false);

begin dbms_repcat.drop_master_repgroup(gname=>'KTCHANREP',all_sites => false); end;

ORA-23313: object group "PUBLIC"."KTCHANREP" is not mastered at ORCL.WIRES.CC
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_REPCAT_UTL4", line 2860
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2652
ORA-06512: at "SYS.DBMS_REPCAT", line 635
ORA-06512: at line 1

SQL>

Pls it a final result I get a error, pls advice.

Thanks
K.T.Chan

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
seazodiacCommented:
I think the message is clear:

This group is not MASTER so you cannot use drop_master_repgroup procedure.
0
 
ktchanhelpAuthor Commented:
Hi,

Pls advice how to sovle this problem ?

I also need to clear a below object together with a group <KTCHANREP>
SQL> select * from dba_repobject;

SNAME                          ONAME                          TYPE             STATUS     GENERATION_STATUS         ID OBJECT_COMMENT                                                                   GNAME                          MIN_COMMUNICATION REPLICATION_TRIGGER_EXISTS INTERNAL_PACKAGE_EXISTS GROUP_OWNER                    NESTED_TABLE
------------------------------ ------------------------------ ---------------- ---------- ----------------- ---------- -------------------------------------------------------------------------------- ------------------------------ ----------------- -------------------------- ----------------------- ------------------------------ ------------
CITYLINK                       ACKNOWLEDGE_OUT                TABLE            VALID      GENERATED              31791                                                                                  KTCHANREP                      Y                 Y                          Y                       PUBLIC                         N
CITYLINK                       ACKNOWLEDGE_OUT$RP             PACKAGE          VALID                             37556 SYSTEM-GENERATED: REPLICATION                                                    KTCHANREP                                                                                           PUBLIC                        
CITYLINK                       ACKNOWLEDGE_OUT$RP             PACKAGE BODY     VALID                             37557 SYSTEM-GENERATED: REPLICATION                                                    KTCHANREP                                                                                           PUBLIC                        
CITYLINK                       AGENT_MASTER                   TABLE            VALID      GENERATED              31800                                                                                  KTCHANREP                      Y                 N                          N                       PUBLIC                         N
CITYLINK                       DRS_MASTER                     TABLE            VALID      GENERATED              31917                                                                                  KTCHANREP                      Y                 Y                          Y                       PUBLIC                         N
CITYLINK                       DRS_MASTER$RP                  PACKAGE          VALID                             37558 SYSTEM-GENERATED: REPLICATION                                                    KTCHANREP                                                                                           PUBLIC                        
CITYLINK                       DRS_MASTER$RP                  PACKAGE BODY     VALID                             37559 SYSTEM-GENERATED: REPLICATION                                                    KTCHANREP                                                                                           PUBLIC                        
CITYLINK                       EM_MASTER                      TABLE            VALID      GENERATED              31931                                                                                  KTCHANREP                      Y                 N                          N                       PUBLIC                         N
CITYLINK                       KTCHAN_TEST                    TABLE            VALID      GENERATED              36073                                                                                  KTCHANREP                      Y                 N                          Y                       PUBLIC                        
CITYLINK                       KTCHAN_TEST$RP                 PACKAGE          VALID                             37371 SYSTEM-GENERATED: REPLICATION                                                    KTCHANREP                                                                                           PUBLIC                        
CITYLINK                       KTCHAN_TEST$RP                 PACKAGE BODY     VALID                             37555 SYSTEM-GENERATED: REPLICATION                                                    KTCHANREP                                                                                           PUBLIC                        
CITYLINK                       POD_MASTER                     TABLE            VALID      GENERATED              32043                                                                                  KTCHANREP                      Y                 N                          N                       PUBLIC                         N

12 rows selected

SQL>



Pls advice me a step.

Thanks.
K.T.Chan
0
 
seazodiacCommented:
try this , I assume you just have a basic replication group:

execute dbms_refresh.destroy('KTCHANREP');

0
 
ktchanhelpAuthor Commented:
Hi,

I alaready try it out, this is a result I get

SQL> execute dbms_refresh.destroy('KTCHANREP');

begin dbms_refresh.destroy('KTCHANREP'); end;

ORA-23404: refresh group "REPADMIN"."KTCHANREP" does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_REFRESH", line 23
ORA-06512: at "SYS.DBMS_REFRESH", line 170
ORA-06512: at line 1

SQL>

pls advice.

Thanks
K.T.Chan
0
 
seazodiacCommented:
Yes, because the group_owner is CITYLINK.
you should log in as CITYLINK or SYS to drop that group
0
 
ktchanhelpAuthor Commented:
Hi,

This is a result I login citylink :-
Connected to Oracle9i Enterprise Edition Release 9.0.1.4.0
Connected as citylink


SQL>  execute dbms_refresh.destroy('KTCHANREP');

begin dbms_refresh.destroy('KTCHANREP'); end;

ORA-23404: refresh group "CITYLINK"."KTCHANREP" does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_REFRESH", line 23
ORA-06512: at "SYS.DBMS_REFRESH", line 170
ORA-06512: at line 1

SQL>


This is a result I login sys :-
Connected to Oracle9i Enterprise Edition Release 9.0.1.4.0
Connected as SYS


SQL> execute dbms_refresh.destroy('KTCHANREP');

begin dbms_refresh.destroy('KTCHANREP'); end;

ORA-23404: refresh group "SYS"."KTCHANREP" does not exist
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.DBMS_REFRESH", line 23
ORA-06512: at "SYS.DBMS_REFRESH", line 170
ORA-06512: at line 1

SQL>

Pls advice.

Thanks
K.T.Chan
0
 
seazodiacCommented:
can you do this and post the results back?
Your output up there is so mingled up I cannot tell which col is which

SQL> select sname, oname, group_owner from dba_repobject;
0
 
ktchanhelpAuthor Commented:
Hi

SQL>  select sname, oname, group_owner from dba_repobject;

SNAME                          ONAME                          GROUP_OWNER
------------------------------ ------------------------------ ------------------------------
CITYLINK                       ACKNOWLEDGE_OUT                PUBLIC
CITYLINK                       ACKNOWLEDGE_OUT$RP             PUBLIC
CITYLINK                       ACKNOWLEDGE_OUT$RP             PUBLIC
CITYLINK                       AGENT_MASTER                   PUBLIC
CITYLINK                       DRS_MASTER                     PUBLIC
CITYLINK                       DRS_MASTER$RP                  PUBLIC
CITYLINK                       DRS_MASTER$RP                  PUBLIC
CITYLINK                       EM_MASTER                      PUBLIC
CITYLINK                       KTCHAN_TEST                    PUBLIC
CITYLINK                       KTCHAN_TEST$RP                 PUBLIC
CITYLINK                       KTCHAN_TEST$RP                 PUBLIC
CITYLINK                       POD_MASTER                     PUBLIC

12 rows selected

SQL>

This is a result I get.

pls advice.

Thanks
K.T.Chan
0
 
seazodiacCommented:
strange, how come they belong to public?

try this :
log in as sysdba;


SQL> select owner, name from dba_rgroup;
0
 
ktchanhelpAuthor Commented:
Hi,

I login into sysdba :-

Connected to Oracle9i Enterprise Edition Release 9.0.1.4.0
Connected as SYS


SQL>  select owner, name from dba_rgroup;

OWNER                          NAME
------------------------------ ------------------------------

SQL>

Thanks
K.T.Chan
0
 
seazodiacCommented:
ok, Ktchanhelp:
then you are done. there is really no replication group existing in the database any more.

every replication group will have an entry in this table, since it's empty, there is none therefore.
0
 
ktchanhelpAuthor Commented:
Hi,

Thanks for advice,

But when I login a repadmin user :-
Connected to Oracle9i Enterprise Edition Release 9.0.1.4.0
Connected as repadmin


SQL> select gname,master,status from dba_repgroup;

GNAME                          MASTER STATUS
------------------------------ ------ ---------
GROUP1                         Y      QUIESCED
KTCHANREP                      Y      NORMAL

SQL>

SQL> select sname,oname,type,status from dba_repobject;

SNAME                          ONAME                          TYPE             STATUS
------------------------------ ------------------------------ ---------------- ----------
CITYLINK                       ACKNOWLEDGE_OUT                TABLE            VALID
CITYLINK                       ACKNOWLEDGE_OUT$RP             PACKAGE          VALID
CITYLINK                       ACKNOWLEDGE_OUT$RP             PACKAGE BODY     VALID
CITYLINK                       AGENT_MASTER                   TABLE            VALID
CITYLINK                       DRS_MASTER                     TABLE            VALID
CITYLINK                       DRS_MASTER$RP                  PACKAGE          VALID
CITYLINK                       DRS_MASTER$RP                  PACKAGE BODY     VALID
CITYLINK                       EM_MASTER                      TABLE            VALID
CITYLINK                       KTCHAN_TEST                    TABLE            VALID
CITYLINK                       KTCHAN_TEST$RP                 PACKAGE          VALID
CITYLINK                       KTCHAN_TEST$RP                 PACKAGE BODY     VALID
CITYLINK                       POD_MASTER                     TABLE            VALID

12 rows selected

SQL>

I still get a Replication group and object. I need to clear it out. How to do it ? pls advice.

Thanks
K.T.Chan
0
 
seazodiacCommented:
Do you need to use REPADMIN schema at all?

check if it's ok to drop the REPADMIN completely , this will eradicate the whole REPADMIN schema including replication groups.
0
 
ktchanhelpAuthor Commented:
hi,

I need to user repadmin as a replication administrator.

After I user a sysdba to drop a repadmin schema, the replicaion group and replication object still have.

Pls advice.

Thanks
K.T.Chan
0
 
seazodiacCommented:
try this as one last resort:

log in as sysdba

SQL> execute dbms_refresh.destroy('PUBLIC.KTCHANREP');
0
 
ktchanhelpAuthor Commented:
hi,

I use sysdba :-
SQL>  execute dbms_refresh.destroy('PUBLIC.KTCHANREP');

begin dbms_refresh.destroy('PUBLIC.KTCHANREP'); end;

ORA-00931: missing identifier
ORA-06512: at "SYS.DBMS_UTILITY", line 105
ORA-06512: at "SYS.DBMS_IREFRESH", line 62
ORA-06512: at "SYS.DBMS_REFRESH", line 15
ORA-06512: at "SYS.DBMS_REFRESH", line 170
ORA-06512: at line 1

SQL>

this is error message.

Thanks
K.T.Chan
0
 
seazodiacCommented:
sorry, Chan,
your comments--->After I user a sysdba to drop a repadmin schema, the replicaion group and replication object still have.

can you tell exactly how you did that? I suspect you might do sth incorrectly..
0
 
ktchanhelpAuthor Commented:
Hi,

I use a sysdba and isseud a command :-

Drop user repadmin cascade
Drop user repdba cascade


Thanks
K.T.Chan
0
 
seazodiacCommented:
then where do you see the replication groups ?
0
 
ktchanhelpAuthor Commented:
Hi,

On the sysdba I use a command :-

Connected to Oracle9i Enterprise Edition Release 9.0.1.4.0
Connected as SYS


SQL> select gname,master,status from dba_repgroup;

GNAME                          MASTER STATUS
------------------------------ ------ ---------
GROUP1                         Y      QUIESCED
KTCHANREP                      Y      NORMAL

SQL>

SQL> select sname,oname,type from dba_repobject;

SNAME                          ONAME                          TYPE
------------------------------ ------------------------------ ----------------
CITYLINK                       ACKNOWLEDGE_OUT                TABLE
CITYLINK                       ACKNOWLEDGE_OUT$RP             PACKAGE
CITYLINK                       ACKNOWLEDGE_OUT$RP             PACKAGE BODY
CITYLINK                       AGENT_MASTER                   TABLE
CITYLINK                       DRS_MASTER                     TABLE
CITYLINK                       DRS_MASTER$RP                  PACKAGE
CITYLINK                       DRS_MASTER$RP                  PACKAGE BODY
CITYLINK                       EM_MASTER                      TABLE
CITYLINK                       KTCHAN_TEST                    TABLE
CITYLINK                       KTCHAN_TEST$RP                 PACKAGE
CITYLINK                       KTCHAN_TEST$RP                 PACKAGE BODY
CITYLINK                       POD_MASTER                     TABLE

12 rows selected

SQL>

Thanks
K.T.Chan
0
 
seazodiacCommented:
Can you log in as "CITYLINK" and drop those individual repobjects like this:

SQL>connect citylink/******

SQL>drop table ACKNOWLEDGE_OUT;
0
 
seazodiacCommented:
do the same thing for other rep objects....
0
 
ktchanhelpAuthor Commented:
hi,

On the previus replicaiton table is my production database table , impossible to drop, any way to solve this problem ?


Thanks
K.T.Chan
0
 
seazodiacCommented:
sorry, then what do you want to get rid of here?
you have two replications groups :

then a bunch of replication objects?

what do you want to achieve here , sorry, this post has gone farther than i thought, i have lost the sight of it...
0
 
ktchanhelpAuthor Commented:
hi,

Can I create another replicaiton group to user replication a object like ACKNOWLEDGE_OUT ?

Thanks
K.T.Chan
0
 
seazodiacCommented:
I am a bit confused about the replication groups now in your case:
give out the output from the following queries;

log in as sysdba;


SQL> select sname, gname, fname, rpc_processing_disabled, owner from dba_repgroups;

SQL>select sname, oname, gname, group_owner from dba_repobject;
0
 
ktchanhelpAuthor Commented:
hi,

SQL> select sname, gname, fname, rpc_processing_disabled, owner from dba_repgroup;

SNAME                          GNAME                          FNAME                          RPC_PROCESSING_DISABLED OWNER
------------------------------ ------------------------------ ------------------------------ ----------------------- ------------------------------
GROUP1                         GROUP1                                                        N                       PUBLIC
KTCHANREP                      KTCHANREP                                                     N                       PUBLIC

SQL> select sname, oname, gname, group_owner from dba_repobject;

SNAME                          ONAME                          GNAME                          GROUP_OWNER
------------------------------ ------------------------------ ------------------------------ ------------------------------
CITYLINK                       ACKNOWLEDGE_OUT                KTCHANREP                      PUBLIC
CITYLINK                       ACKNOWLEDGE_OUT$RP             KTCHANREP                      PUBLIC
CITYLINK                       ACKNOWLEDGE_OUT$RP             KTCHANREP                      PUBLIC
CITYLINK                       AGENT_MASTER                   KTCHANREP                      PUBLIC
CITYLINK                       DRS_MASTER                     KTCHANREP                      PUBLIC
CITYLINK                       DRS_MASTER$RP                  KTCHANREP                      PUBLIC
CITYLINK                       DRS_MASTER$RP                  KTCHANREP                      PUBLIC
CITYLINK                       EM_MASTER                      KTCHANREP                      PUBLIC
CITYLINK                       KTCHAN_TEST                    KTCHANREP                      PUBLIC
CITYLINK                       KTCHAN_TEST$RP                 KTCHANREP                      PUBLIC
CITYLINK                       KTCHAN_TEST$RP                 KTCHANREP                      PUBLIC
CITYLINK                       POD_MASTER                     KTCHANREP                      PUBLIC

12 rows selected

SQL>


Thanks
K.T.Chan
0
 
seazodiacCommented:
Do you have a PUBLIC user account in your database?
I know there is a hidden PUBLIC role(group) in oracle database, but it cannot be a owner of replication table?

can you post this?

SQL>select username from dba_users where username like 'PUB%';
0
 
ktchanhelpAuthor Commented:
hi

SQL> select username from dba_users where username like 'PUB%';

USERNAME
------------------------------

SQL>


Thanks
K.T.Chan
0
 
seazodiacCommented:
can you drop these two schema ?

GROUP1   and KTCHANREP
0
 
ktchanhelpAuthor Commented:
hi

I use a sysdba to drop

Connected to
Connected as SYS


SQL> exec dbms_repcat.drop_master_repgroup(gname=>'group1',all_sites => false);

begin dbms_repcat.drop_master_repgroup(gname=>'group1',all_sites => false); end;

ORA-23313: object group "PUBLIC"."GROUP1" is not mastered at ORCL.WIRES.CC
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_REPCAT_UTL4", line 2860
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2652
ORA-06512: at "SYS.DBMS_REPCAT", line 635
ORA-06512: at line 1

SQL>  exec dbms_repcat.drop_master_repgroup(gname=>'KTCHANREP',all_sites => false);

begin dbms_repcat.drop_master_repgroup(gname=>'KTCHANREP',all_sites => false); end;

ORA-23313: object group "PUBLIC"."KTCHANREP" is not mastered at ORCL.WIRES.CC
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.DBMS_REPCAT_UTL4", line 2860
ORA-06512: at "SYS.DBMS_REPCAT_MAS", line 2652
ORA-06512: at "SYS.DBMS_REPCAT", line 635
ORA-06512: at line 1

SQL>

Thanks
K.T.Chan
0
 
seazodiacCommented:
No, I meant to drop the schema of these two.
0
 
ktchanhelpAuthor Commented:
Hi,

Also can't
SQL> drop user group1 cascade;

drop user group1 cascade

ORA-01918: user 'GROUP1' does not exist

SQL> drop user KTCHANREP cascade;

drop user KTCHANREP cascade

ORA-01918: user 'KTCHANREP' does not exist

SQL>

Are me use a wrong command to drop a this 2 schema ?

Thanks
k.T.Chan
0
 
seazodiacCommented:
Another question, did you change your databsae parameter files such as init.ora file?

for example GLOBAL_NAME?
0
 
ktchanhelpAuthor Commented:
Hi,

Not change .

Thanks
K.T.Chan
0
 
seazodiacCommented:
is this a clone of a database?

what database does ORCL.WIRES.CC point to?

is ORCL.WIRES.CC another MASTER replication site?
0
 
ktchanhelpAuthor Commented:
hi,

orcl.wires.cc is a master database -------------> ktorcl.wires.cc

Thanks
k.t.chan
0
 
ktchanhelpAuthor Commented:
Hi,

I still find a orcl.wires.cc run a replication to ktorcl.wires.cc


Thanks
K.T.Chan
0
 
ktchanhelpAuthor Commented:
Hi.

I use a catrepm.sql to solve all a problem

Thanks
K.T.Chan
0
 
seazodiacCommented:
thanks for the update.

all the best
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 22
  • 21
Tackle projects and never again get stuck behind a technical roadblock.
Join Now