Home » Server Options » Replication » Refresing Snapshot and DBlink  () 1 Vote
Refresing Snapshot and DBlink [message #75436] Mon, 29 December 2003 11:56 Go to next message
kochunni
Messages: 17
Registered: May 2003
Junior Member
1. I created the a snapshot log for a table:

CREATE SNAPSHOT LOG ON FLT_DERATE_PARM TABLESPACE flt_tables WITH primary key

2. I create a materialized view for the table in the same schema:

CREATE SNAPSHOT FLT_DERATE_PARM_MV TABLESPACE flt_tables REFRESH fast START WITH SYSDATE NEXT sysdate+1/(24*120) WITH primary key AS select * from FLT_DERATE_PARM

3. I update the base table’s data and the snapshot refreshed automatically

But when I created the snapshot in a remote instance with a Dblink, the snapshot is not refreshing in FAST refresh mode:

CREATE SNAPSHOT FLT_DERATE_PARM_MV TABLESPACE flt_tables REFRESH fast START WITH SYSDATE NEXT sysdate+1/(24*120) WITH primary key AS select * from FLT_DERATE_PARM@EVNODSD1.WORLD

Please advice what needs to be done for this. I assume that this is a DBLINK issue.

Thanks
kochunni
Re: Refresing Snapshot and DBlink [message #75437 is a reply to message #75436] Mon, 29 December 2003 22:07 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Hi,

You also need to create a MATERIALIZED VIEW LOG (SNAPSHOT LOG) on the table in the remote database.

Best regards.

Frank
Re: Refresing Snapshot and DBlink [message #75439 is a reply to message #75437] Tue, 30 December 2003 04:17 Go to previous messageGo to next message
kochunni
Messages: 17
Registered: May 2003
Junior Member
Frank,

I did the same.

My original post says:

1. I created the a snapshot log for a table (This is the base table)

CREATE SNAPSHOT LOG ON FLT_DERATE_PARM TABLESPACE flt_tables WITH primary key

I created the snapshot in a remote instance with a Dblink, the snapshot is not refreshing in FAST refresh mode:

CREATE SNAPSHOT FLT_DERATE_PARM_MV TABLESPACE flt_tables REFRESH fast START WITH SYSDATE NEXT sysdate+1/(24*120) WITH primary key AS select * from FLT_DERATE_PARM@EVNODSD1.WORLD

1. I have instances A and B
2. I have user FLT (same passwords) on both instances. There is a public DBlink in A to B
3. I created a snapshot log for the base table in A
4. I created a snapshot table in B using the DBlink. If I query the snapshot, I can see the data. But if I update the base table in A, the updated information is not reflecting in the snapshot in B. it should be in the snapshot in 30 seconds.

Am I missing something?

Thanks
velappan
Re: Refresing Snapshot and DBlink [message #75440 is a reply to message #75439] Tue, 30 December 2003 23:31 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Hi,

Have you set the following initialization parameters:

job_queue_processes > 0
job_queue_interval (for Oracle 8i and below)


Best regards.

Frank
Re: Refresing Snapshot and DBlink [message #75441 is a reply to message #75440] Mon, 05 January 2004 03:06 Go to previous messageGo to next message
kochunni
Messages: 17
Registered: May 2003
Junior Member
job_queue_processes is 4 in both the instances...Still no luck with it.

Kochunni
Re: Refresing Snapshot and DBlink [message #75443 is a reply to message #75441] Mon, 05 January 2004 18:48 Go to previous message
kochunni
Messages: 17
Registered: May 2003
Junior Member
Here is an interesting turn on this problem.

Here is the replay from an HP DBA who looked at this issue.

Hello,

Toy are using dbms_job to refresh a materialized view. The dbms_job select data from across a database link to evnodsd1 instance. When the code in the job is run manually it works. When the job executes manually it uses the users privileges, but when the job tries to run via the background SNP processes it fails with ORA-01005: null password given; logon denied.

Here is what MetaLink has to say:
· fact: Oracle Server - Enterprise Edition
·
· symptom: Connection using database link in a job fails
·
· symptom: ORA-12012: error on auto execute of job %s
·
· symptom: ORA-01005: null password given; logon denied
·
· symptom: ORA-02063: preceding %s%s from %s%s
·
· cause: SNP background processes execute jobs. To execute a job, the
· process creates a session to run the job. Because the Data Dictionary does not
· contain a username/password for the database link, as this is a connected user
· dblink, the SNP process is unable to succesfully connect at the remote database.
· When you force a job to run using the procedure DBMS_JOB.RUN, the job is run
· by your process. When your user process runs a job, it is run with your
· default privileges only. If you submit a job that uses a database link, the
· link must include a username and password. Anonymous database links will not
· succeed.
·
·


fix:

Create a database link that has a username and password.

Does this make sense to anybody here? I haven't see this in the oracle documentation -the fast refresh will only work with private DBlinks. Please share your experience.

Thanks
kochunni
Previous Topic: Materialized view cannot be used in OLTP
Next Topic: can't find Oracle Replication Manager
Goto Forum:
  


Current Time: Thu Mar 28 11:03:16 CDT 2024