OraFAQ Forum: Streams & AQ » Error while installing Oracle Streams.

Home » Server Options » Streams & AQ » Error while installing Oracle Streams.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Error while installing Oracle Streams. [message #75651]

Thu, 01 January 2004 20:44

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

Report message to a moderator

Re: Error while installing Oracle Streams. [message #75654 is a reply to message #75651]

Thu, 01 January 2004 22:12

Frank Naude
Messages: 4579
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

Report message to a moderator

Re: Error while installing Oracle Streams. [message #75655 is a reply to message #75654]

Thu, 01 January 2004 23:09

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

Report message to a moderator

Re: Error while installing Oracle Streams. [message #75656 is a reply to message #75655]

Fri, 02 January 2004 02:48

Frank Naude
Messages: 4579
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

Report message to a moderator

Re: Error while installing Oracle Streams. [message #75657 is a reply to message #75651]

Fri, 02 January 2004 03:04

Frank Naude
Messages: 4579
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

Report message to a moderator