Home » Server Options » Replication » Problem in configuration of replication (oracle 10.2.0.3.0, windows xp.)
Problem in configuration of replication [message #329572] Wed, 25 June 2008 22:13 Go to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member
Hi Friends,

I am new to replication. I have studied the concepts and then tried to do it practically, But i am facing problem in very initial face of configuration. I searched a lot for solution, but couldn't. Please help/suggest me for going forward.

I did following.

CREATE TABLESPACE streams_tbs DATAFILE '/data/oradata/dev/streams_tbs.dbf' 
	SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

REATE USER strmadmin IDENTIFIED BY strmadminpw
	DEFAULT TABLESPACE streams_tbs
   	QUOTA UNLIMITED ON streams_tbs;

GRANT DBA TO strmadmin;

BEGIN
   DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
     grantee          => 'strmadmin',    
     grant_privileges => true);
   END;
  /
  
  Error while this statement..
  ERROR at line 1:
  ORA-04063: package body "SYS.DBMS_STREAMS_AUTH" has errors
  ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STREAMS_AUTH"
  ORA-06512: at line 2

  [B]For that we execute package utlrep.sql and catrep.sql package.[/B]  
  SQL>@c:\oracle\ora92\rdbms\admin\catrep.sql
  
  SQL>@c:\oracle\ora92\rdbms\admin\utlrp.sql
  
[B]problem not solved yet.[/B]

CREATE TABLESPACE streams_tbs_d DATAFILE '/data/oradata/dev/streams_tbs_d.dbf' 
  SIZE 25M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

CREATE USER strmadmin_d IDENTIFIED BY strmadminpw
    DEFAULT TABLESPACE streams_tbs_d
     QUOTA UNLIMITED ON streams_tbs_d;

GRANT DBA TO strmadmin_d;

EGIN
  DBMS_STREAMS_AUTH.GRANT_ADMIN_PRIVILEGE(
    grantee          => 'strmadmin',    
    grant_privileges => true);
  END;
/


error:

BEGIN
*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_STREAMS_AUTH" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STREAMS_AUTH"
ORA-06512: at line 2

create directory dir_s  as '/data/rep_dir_s';

create directory dir_d as '/data/rep_dir_d';

BEGIN
DBMS_STREAMS_ADM.MAINTAIN_SCHEMAS(
schema_names => 'strmadmin_d',
source_directory_object => 'dir_s',
destination_directory_object => 'dir_d',
source_database => 'dev',
destination_database => 'dev',
perform_actions => true,
dump_file_name => 'export_rep.dmp',
capture_queue_table => 'rep_capture_queue_table',
capture_queue_name => 'rep_capture_queue',
capture_queue_user => NULL,
apply_queue_table => 'rep_dest_queue_table',
apply_queue_name => 'rep_dest_queue',
apply_queue_user => NULL,
capture_name => 'capture_rep',
propagation_name => 'prop_rep',
apply_name => 'apply_rep',
log_file => 'export_rep.clg',
bi_directional => false,
include_ddl => true,
instantiation => DBMS_STREAMS_ADM.INSTANTIATION_SCHEMA);
END;
/

error: 

BEGIN
*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_STREAMS_ADM" has errors
ORA-06508: PL/SQL: could not find program unit being called: "SYS.DBMS_STREAMS_ADM"
ORA-06512: at line 2


The status of both DBMS_STREAMS_ADM and DBMS_STREAMS_AUTH are shown invalid when i queried status column dba_objects view.
Even when i tried to compile them using alter package.. recompile option, it is giving me 'Compiled with error' message.

Please, suggest me the solution, if anybody have some idea...

Regards..
Dipali..
Re: Problem in configuration of replication [message #329879 is a reply to message #329572] Thu, 26 June 2008 21:19 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Hello,


Quote:
Problem in configuration of replication


This is not Replication related topic.

Try to compile invaild objects using UTLPRP.SQL

Babu

[Updated on: Thu, 26 June 2008 21:20]

Report message to a moderator

Re: Problem in configuration of replication [message #329895 is a reply to message #329879] Fri, 27 June 2008 00:10 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member
yes babu,
I have tried to compile these both packages (manually using alter package statement as well as using utility catrep.sql and utlrp.sql, but compilation error is returned.

When i executed the utilities (above both), some statements done by utility returned errors, last of which is
Elapsed: 00:00:02.00
BEGIN dbms_registry.validate_components; END;

                    *
ERROR at line 1:
ORA-06550: line 1, column 21:
PLS-00302: component 'VALIDATE_COMPONENTS' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


When i tried to compiled both packages using alter package statement, following errors were retured.
  1* alter package DBMS_STREAMS_AUTH compile body
11:07:13 SQL> /

Warning: Package Body altered with compilation errors.

Elapsed: 00:00:00.00
11:07:14 SQL> show error
Errors for PACKAGE BODY DBMS_STREAMS_AUTH:

LINE/COL|ERROR
--------|-----------------------------------------------------------------
1178/5  |PL/SQL: Statement ignored
1179/24 |PLS-00302: component 'DB_COMPATIBLE100' must be declared
1228/5  |PL/SQL: Statement ignored
1229/24 |PLS-00302: component 'DB_COMPATIBLE100' must be declared
1269/5  |PL/SQL: Statement ignored
1270/24 |PLS-00302: component 'DB_COMPATIBLE100' must be declared
1286/5  |PL/SQL: Statement ignored
1287/24 |PLS-00302: component 'DB_COMPATIBLE100' must be declared
11:07:18 SQL> 


-------------------------------------------------


  1* alter package DBMS_STREAMS_ADM compile body
11:09:05 SQL> /

Warning: Package Body altered with compilation errors.

Elapsed: 00:00:00.02
11:09:06 SQL> show error
Errors for PACKAGE BODY DBMS_STREAMS_ADM:

LINE/COL|ERROR
--------|-----------------------------------------------------------------
39/5    |PL/SQL: Statement ignored
40/24   |PLS-00302: component 'DB_COMPATIBLE100' must be declared
147/5   |PL/SQL: Statement ignored
148/24  |PLS-00302: component 'DB_COMPATIBLE100' must be declared
1994/5  |PL/SQL: Statement ignored
1995/24 |PLS-00302: component 'DB_COMPATIBLE100' must be declared
2198/5  |PL/SQL: Statement ignored
2199/24 |PLS-00302: component 'DB_COMPATIBLE100' must be declared
2266/5  |PL/SQL: Statement ignored
2267/24 |PLS-00302: component 'DB_COMPATIBLE100' must be declared
2316/5  |PL/SQL: Statement ignored

LINE/COL|ERROR
--------|-----------------------------------------------------------------
2317/24 |PLS-00302: component 'DB_COMPATIBLE100' must be declared
2372/5  |PL/SQL: Statement ignored
2373/24 |PLS-00302: component 'DB_COMPATIBLE100' must be declared
11:09:10 SQL> 


Thanks and Regards..
Dipali.
Re: Problem in configuration of replication [message #331088 is a reply to message #329895] Wed, 02 July 2008 05:19 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

Hellow again,

I can't understand your problem.

1. Which user your using try to compile? SYS Or System?

2. Need output of below query

SQL> select parameter,value from v$option where parameter like '%Streams%';


Select * from v$version;


Babu

[Updated on: Wed, 02 July 2008 05:52] by Moderator

Report message to a moderator

Re: Problem in configuration of replication [message #331182 is a reply to message #331088] Wed, 02 July 2008 08:42 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member
Hi Babu,

19:05:55 SQL> select parameter,value from v$option where parameter like '%Streams%';

PARAMETER                                                       |VALUE
----------------------------------------------------------------|-----------------------------------
Streams Capture                                                 |TRUE

Elapsed: 00:00:00.00
19:05:56 SQL> Select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

Elapsed: 00:00:00.00
19:06:02 SQL> 




The root of problem is that the package DBMS_STREAMS_AUTH and DBMS_STREAMS_ADM itself contains errors as specified above and cannot be compiled.. Even when i am trying the utility catrep.sql and utlrp.sql, some of the object creation/compilation are done by it with error.


The oracle database was once patched by other team member.
Can this problem be because of the patch?

Thanks,
Dipali..
Re: Problem in configuration of replication [message #331216 is a reply to message #331182] Wed, 02 July 2008 11:16 Go to previous messageGo to next message
babuknb
Messages: 1736
Registered: December 2005
Location: NJ
Senior Member

>>The oracle database was once patched by other team member.

This is exactly I expected.

Please once again install patch.

Babu

Re: Problem in configuration of replication [message #331298 is a reply to message #331216] Wed, 02 July 2008 23:30 Go to previous message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member
Hope this will work...

I will update you once complete the patch reinstallation..

Thanks and Regards,
Dipali Vithalani.....
Previous Topic: Fast Refresh of materialized view too slow
Next Topic: what is the difference between materialized view and snapshot
Goto Forum:
  


Current Time: Thu Mar 28 18:24:42 CDT 2024