Home » Server Options » Streams & AQ » Error while installing Oracle Streams.
Error while installing Oracle Streams. [message #75651] Thu, 01 January 2004 20:44 Go to next message
Vidyanand More
Messages: 35
Registered: January 2003
Member
Hi All,

Environment details is as follows:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

I am planning to install oracle streams.

While creation of the APPLY process at the target schema, following error message was displayed.

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 75
ORA-06512: at "SYS.DBMS_STREAMS_ADM", line 21
ORA-06512: at line 2

Following is pl-sql block to create apply process.

BEGIN
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => '"SCOTT"."DEPT"',
streams_type => 'APPLY',
streams_name => 'STRMADMIN_LOCAL_2',
queue_name => '"SRC"."STREAMS_QUEUE"',
include_dml => true,
include_ddl => false,
source_database => 'ORCL');
/* Add Apply rules for DEMO.DEPT */
DBMS_STREAMS_ADM.ADD_TABLE_RULES(
table_name => '"SCOTT"."EMP"',
streams_type => 'APPLY',
streams_name => 'STRMADMIN_LOCAL_2',
queue_name => '"SRC"."STREAMS_QUEUE"',
include_dml => true,
include_ddl => false,
source_database => 'ORCL');
END;

Before this i followed following steps & all of them were successful.

Following were steps followed.

1> Created a new user for source & target schema respectively.

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


CREATE USER trg IDENTIFIED BY trg
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp
QUOTA UNLIMITED ON users

2> Granted new user necessary roles & privileges.

GRANT CONNECT,RESOURCE, EXP_FULL_DATABASE,IMP_FULL_DATABASE,SELECT_CATALOG_ROLE,AQ_ADMINISTRATOR_ROLE TO src;

GRANT CONNECT,RESOURCE, EXP_FULL_DATABASE,IMP_FULL_DATABASE,SELECT_CATALOG_ROLE,AQ_ADMINISTRATOR_ROLE TO trg;

3> Connected as sys for target schema.

conn sys/??????@orcl.world as sysdba

Executed following pl-sql block.

BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'ENQUEUE_ANY',
grantee => 'TRG',
admin_option => FALSE);
/* Grants STRMADMIN privilege to DEQUEUE any message from any queues in the database */
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'DEQUEUE_ANY',
grantee => 'TRG',
admin_option => FALSE);
/* Grants STRMADMIN privilege to run or execute DBMS_AQADM on any schemas in the database */
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'MANAGE_ANY',
grantee => 'TRG',
admin_option => TRUE);
END;


4> Connected as sys for source schema.

conn sys/??????@orcl.world as sysdba

Executed following pl-sql block.

/* Grants the STRMADMIN user, privilege to ENQUEUE any message to any queues in the database */
BEGIN
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'ENQUEUE_ANY',
grantee => 'SRC',
admin_option => FALSE);
/* Grants STRMADMIN privilege to DEQUEUE any message from any queues in the database */
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'DEQUEUE_ANY',
grantee => 'SRC',
admin_option => FALSE);
/* Grants STRMADMIN privilege to run or execute DBMS_AQADM on any schemas in the database */
DBMS_AQADM.GRANT_SYSTEM_PRIVILEGE(
privilege => 'MANAGE_ANY',
grantee => 'SRC',
admin_option => TRUE);

5> connected as sys for target schema.

Executed following pl-sql block.

BEGIN
/* Creates a new evaluation context in STRMADMIN's schema */
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
grantee => 'TRG',
grant_option => TRUE);
/* Creates a new rule set in STRMADMIN's schema */
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee => 'TRG',
grant_option => TRUE);
/* Creates a new rule in STRMADMIN's schema */
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE (
privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'TRG',
grant_option => TRUE);
/* Grants STRMADMIN privilege to create a new rule set in any schema. */
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE_SET,
grantee => 'TRG',
grant_option => TRUE);
/* Grants STRMADMIN privilege to alter any rule set owned by any user. */
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE_SET,
grantee => 'TRG',
grant_option => TRUE);
/* Grants STRMADMIN privilege to execute any rule set owned by any user. */
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,
grantee => 'TRG',
grant_option => TRUE);
/* Grants STRMADMIN privilege to create a new rule in any schema. */
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE,
grantee => 'TRG',
grant_option => TRUE);
/* Grants STRMADMIN privilege to alter any rule owned by any user. */
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE,
grantee => 'TRG',
grant_option => TRUE);
/* Grants STRMADMIN privilege to execute any rule owned by any user. */
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE,
grantee => 'TRG',
grant_option => TRUE);
/* Grants STRMADMIN privilege to execute any evaluation context owned by any user. */
DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ON_EVALUATION_CONTEXT,
object_name => 'SYS.STREAMS$_EVALUATION_CONTEXT',
grantee => 'TRG',
grant_option => FALSE );
END;

6> Executed following pl-sql block.

BEGIN
/* Creates a new evaluation context in STRMADMIN's schema */
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_EVALUATION_CONTEXT_OBJ,
grantee => 'TRG',
grant_option => TRUE);
/* Creates a new rule set in STRMADMIN's schema */
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_RULE_SET_OBJ,
grantee => 'TRG',
grant_option => TRUE);
/* Creates a new rule in STRMADMIN's schema */
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE (
privilege => DBMS_RULE_ADM.CREATE_RULE_OBJ,
grantee => 'TRG',
grant_option => TRUE);
/* Grants STRMADMIN privilege to create a new rule set in any schema. */
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE_SET,
grantee => 'TRG',
grant_option => TRUE);
/* Grants STRMADMIN privilege to alter any rule set owned by any user. */
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE_SET,
grantee => 'TRG',
grant_option => TRUE);
/* Grants STRMADMIN privilege to execute any rule set owned by any user. */
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE_SET,
grantee => 'TRG',
grant_option => TRUE);
/* Grants STRMADMIN privilege to create a new rule in any schema. */
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.CREATE_ANY_RULE,
grantee => 'TRG',
grant_option => TRUE);
/* Grants STRMADMIN privilege to alter any rule owned by any user. */
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.ALTER_ANY_RULE,
grantee => 'TRG',
grant_option => TRUE);
/* Grants STRMADMIN privilege to execute any rule owned by any user. */
DBMS_RULE_ADM.GRANT_SYSTEM_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ANY_RULE,
grantee => 'TRG',
grant_option => TRUE);
/* Grants STRMADMIN privilege to execute any evaluation context owned by any user. */
DBMS_RULE_ADM.GRANT_OBJECT_PRIVILEGE(
privilege => DBMS_RULE_ADM.EXECUTE_ON_EVALUATION_CONTEXT,
object_name => 'SYS.STREAMS$_EVALUATION_CONTEXT',
grantee => 'TRG',
grant_option => FALSE );
END;

7> Created dblink from source to target

CREATE PUBLIC DATABASE LINK streamlink
CONNECT TO STRMADMIN IDENTIFIED BY STRMADMIN USING 'ORCL';

8> Created streams queue.

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_user => 'TRG');
END;

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_user => 'SRC');
END;

Also i understand for oracle streams, oracle database should be in archive mode & this may slow down response time of the oracle database. If it is so how we can tackle this issue? I am planning to install oracle stream on source database, where volume of data is likely to be very high.

Can someone please provide me more information?
Thanks in Advance.

Regards,
Vidyanand
Re: Error while installing Oracle Streams. [message #75654 is a reply to message #75651] Thu, 01 January 2004 22:12 Go to previous messageGo to next message
Frank Naude
Messages: 4417
Registered: April 1998
Senior Member
Hi,

You may want to try the following before creating the APPLY:

GRANT ALL ON SCOTT.DEPT TO SRC;
GRANT ALL ON SCOTT.EMP  TO SRC;

GRANT ALL ON SCOTT.DEPT TO TRG;
GRANT ALL ON SCOTT.EMP  TO TRG;


Best regards.

Frank
Re: Error while installing Oracle Streams. [message #75655 is a reply to message #75654] Thu, 01 January 2004 23:09 Go to previous messageGo to next message
Vidyanand More
Messages: 35
Registered: January 2003
Member
Hi Frank,
I tried re-runing of the pl-sql block after executing the command suggested by you, it displayed same error message.
I am wondering is this due to lower version of Oracle Database?
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

I got one more query which is as follows.
For oracle streams does oracle database need to be in Archive mode? Will that slow down response time for oracle database?

Can you please provide me more information?

Thanks in Advance.
Regards,
Vidyanand
Re: Error while installing Oracle Streams. [message #75656 is a reply to message #75655] Fri, 02 January 2004 02:48 Go to previous messageGo to next message
Frank Naude
Messages: 4417
Registered: April 1998
Senior Member
Hi,

I am wondering is this due to lower version of Oracle Database?

Difficult to tell. It might be best to upgrade before hitting all the bugs that are already fixed.

For oracle streams does oracle database need to be in Archive mode? Will that slow down response time for oracle database?

Unfortunately, yes! Quote from the Streams Manual (Chapter 2):

"A capture process reads online redo logs whenever possible and archived redo logs otherwise. For this reason, the database must be running in ARCHIVELOG mode when a capture process is configured to capture changes."

Archiving can slow down response time if incorrectly configured. If you archive to separate disks, and you backup archive log files timely, the overhead should be minimal.

Best regards.

Frank
Re: Error while installing Oracle Streams. [message #75657 is a reply to message #75651] Fri, 02 January 2004 03:04 Go to previous messageGo to next message
Frank Naude
Messages: 4417
Registered: April 1998
Senior Member
Hi,

Also try the following:

GRANT EXECUTE ON DBMS_AQ TO SRC;
GRANT EXECUTE ON DBMS_AQADM TO SRC;
GRANT EXECUTE ON DBMS_FLASHBACK TO SRC;
GRANT EXECUTE ON DBMS_STREAMS_ADM TO SRC;
GRANT EXECUTE ON DBMS_APPLY_ADM TO SRC;
GRANT EXECUTE ON DBMS_CAPTURE_ADM TO SRC;
GRANT EXECUTE ON DBMS_PROPAGATION_ADM TO SRC;
GRANT EXECUTE ON DBMS_RULE_ADM TO SRC;


Repeat the above grants for your TRG schema.

Best regards.

Frank
Re: Error while installing Oracle Streams. [message #75658 is a reply to message #75656] Tue, 06 January 2004 00:10 Go to previous messageGo to next message
Vidyanand More
Messages: 35
Registered: January 2003
Member
Hi Frank,
Thanks a lot for your reply.
I will try to install oracle streams on oracle 9.2.0.4.
Regards,
Vidyanand
Re: Error while installing Oracle Streams. [message #75679 is a reply to message #75656] Tue, 03 February 2004 09:11 Go to previous message
Alessandro
Messages: 3
Registered: February 2004
Junior Member
I'm need installation Oracle Streams.

How to??

thank you.
Previous Topic: Query about Oracle Streams.
Next Topic: Oracle to Non-Oracle Streams
Goto Forum:
  


Current Time: Wed Aug 20 00:53:01 CDT 2014

Total time taken to generate the page: 0.09531 seconds