?
Solved

Replication from Oracle 9i to Oracle 8.1.6, and OEM setup

Posted on 2003-02-25
44
Medium Priority
?
1,253 Views
Last Modified: 2007-12-19
Hi,

I have 2 servers on the network, with 2 different Oracle versions.
1) IBM AIX hosting Oracle 9i
2) HP-UX hosting Oracle 8.1.6

I would like to set up replication from Oracle 9i to Oracle 8.1.6.  I am a total newbie to Oracle (basically have all the theories, and have difficulties in implementing the theories into practical :( ). Details are as follows:

- Replicate data and indexes (advanced replication).
- No need to be able to update the replicated db - Oracle 8.1.6
- Replicate approximately 50 tables, with possibility of increase.
- Oracle 9i's data volume (> 100 or 200 tables) could reach 1GB in a single day.
- 1-min refresh is desirable, but would like to know if there would be challenges due to the huge data volume.
- Will work on replicating 1 DB first.  Subsequently need to replicate multiple masters to multiple destinations.

Please assist in guiding me on how to setup the above (step-by-step).  Also any additional materials available, pls also advise me.  

In addition to above, I also have not setup OEM, and thus have not been able to make good use of the features in OEM.  Pls kindly provide me with some guide on how to set this up.  Also, a tip on whether i should setup OEM in the AIX or HP-UX machine??  AIX is the production env with all DML, DDL performed.  HP is the replicated env with only queries allowed.  

Thanks n Brgds,
weeang
0
Comment
Question by:weeang
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 25
  • 10
  • 7
  • +1
44 Comments
 
LVL 1

Expert Comment

by:roberttran
ID: 8022072
I don't think I can post all the steps in here. But basically you need to:
1. setup the tablespaces (getting started guide will show you how)
2. Setup you init.ora (job queue etc)
3. Run repcat*.sql script.
4. Create repadmin user and db links. (you can use replication manger)
5. Use the DBMS_* API(s) to replicate tables. (you can use rep. manager here also).

For 9i, you may not need step 1 and 2.

Good luck.
0
 

Author Comment

by:weeang
ID: 8022566
Hi,

Thanks for your reply.. however, as I mentioned.. i am a newbie in this... n your brief info is not easy for me to digest and start working on the replication..

If possible, pls do help me in this.

Thanks n Brgds,
weeang
0
 
LVL 2

Expert Comment

by:tschofiled
ID: 8023925
I think there are a few other things you should consider here also :-

1) Do you have any insert triggers? - they may need re-writing. Update triggers possibly too.
2) Is your application replication friendly? -  e.g do forms insert into multiple tables and if there referenial integrity between them. Could these get "out of order"?
3) Why do you want to replicate? availability? hot standby? Is there a better way to acheive this?
4) You seem to have a fair amount of activity on the primary database, how long might you need to store inserts, deletes and updates? - Broken network, box down for upgrade etc...Bear in mind by default the repcat.sql code installs by default in the system tablespace. Well worth considering moving to its own tablespace. There is a documnet process - Dont hack the scripts!
5) I think you wish to "distribute out" from a single source? - consider snapshot replication - its simpler. However if not you may also need to consider conflict resolution.
6) When databases get out of kilter you may have to consider refreshing all the replicated tables (say) using export/import. There are packages provided but I have found them very slow.
7) Do all tables to be replicated have primary keys? They don't have to have them, but it makes it simpler.
8) Consider how you may want to monitor this? e.g. The replication jobs break after 16 attempted and failed connects, and they then need to be unbroken to re-start


It is possible to complete this with scripts, and they can be built sql from sql, but the Replication Manager is easier, as it steps through the process not allowing you to complete the steps in the wrong order, or to proceed if sometning has failed. I recommend installing OEM or just replication manager on a PC to install/configure this.

This is a good product and works well if selected as the right solution for the right problem.

Hope this helps. Have fun.

Tony
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 1

Expert Comment

by:roberttran
ID: 8030530
complete the following steps up to step 5.
http://metalink.oracle.com/metalink/plsql/ml2_gui.startup

After that, you can use Replication Manager to do all the work.

Have fun.
0
 
LVL 1

Expert Comment

by:roberttran
ID: 8030546
if you can't get there via the link, search for the following.
Doc ID:  Note:117434.1
Subject:  Initial steps required to a create Multi Master Replication environment v8.1 / v9.x
0
 

Author Comment

by:weeang
ID: 8032070
Hi tschofiled,

1) no triggers now.
2) yes, it does insert into multiple tables, but doubt this wld get out of order..
3) replication is simply to reduce the workload on the primary db.. we have plans to develop interfacing apps to the primary db, and have decided that a replicated db shd take the work from the primary db wrt those interfacing apps.
4) basically we r hoping for almost real-time updates, but of cos will compromise if we cannot satisfy.. or it simply is out of the qn.. abt the tablespace.. i really hve no idea it uses system tablespace.. n hve not enough knowledge to install it..
5) yes, i want to distribute out from a single source, but the source has multiple db.. n will be distributed to a single server with multiple db as well.  snapshot cld be the way to go.. but does it take care of indexes as well??
7) most tables will have primary keys..
8) i have no idea.. hope someone can enlighten me more on this...

i have read that there is this tool replication manager... i already have OEM running on my pc.. but its connecting to stand-alone...

Can anyone guide me on how to setup OMS?  Also, can someone give me a hint on where to install OMS?? On AIX where my primary DB is, or on HP where i intend to have the replicated db.  Actually, i m thinking that HP is better, since i dun mess with the production env.

Pls provide me some info on how to setup OMS.  Greatly appreciated.

Thanks n Brgds,
0
 

Author Comment

by:weeang
ID: 8032111
Hi roberttran,

Just found the note... will read n digest to see if i can grasp anything out of it.  THANKS!!

Btw, 1 qn (even b4 i read the note).. do i need to setup OMS for OEM to connect, to complement the Replication Manager???  Pls advise, cos' i also will need help to setup OMS.  Now, my OEM only connects to stand-alone.

Do u think i shd have OMS on my AIX (prod) or HP (replication server)????

Thanks n Brgds,
weeang
0
 
LVL 2

Expert Comment

by:tschofiled
ID: 8032543
Where to start!!

4) Real time is kind of manageable, but it does depend on the quantitiy of insert, updates and deletes. The Oracle job which runs replication will keep going all the time data is there to transfer, so it depends on the number and frequency of transactions. Put a bit flood of transactions in and it will grind away to complete these.

5) You need to set up replication for each database to database flow. BTW the schema names must be identical (unless this changed in 9i). When you build the materialized views for snapshot replication, you then need to build any indexes. Also consider an analyze from time to time. These can be different to your source indexes, but you (obviously) should maintain a primary key.

7) You can use Replication manager, but it relies on someone looking. Patrol said they were bringing out a module to monitor replication - maybe other tools can too. Basically you need to keep an eye on the status of the jobs, look out for any entries in the table def_error, and I also monitor that records are being processed from def_tran.

I would build with Replication manager. You do not need the OMS. Just install separately from the cd, update your tnsnames and away you go. Its a step by step process which you can't get out of order, and won't let you proceed until each part appears correct.

Hope this helps!!

Tony
0
 
LVL 1

Expert Comment

by:roberttran
ID: 8035434
I agree with Tony, just install Replication Manager separately on a PC you should able to go from there.

Onething to note is, if you use older ver. of Replication Manager it may not compatible with 9i. I'd use DBA studio.

Robert
0
 

Author Comment

by:weeang
ID: 8077869
Hi there,

I am using the Replication within OEM for Oracle 9i.

Going thru the Notes 117343.1,  at point 7, I am supposed to create the Repadmin user, etc etc.

Actually, I am kinda confused now.  Cos' using the Replication in 9i, the instructions is to create a Master Site, which will at the same time, create the user 'repadmin', and a propagator (if u want), etc.  However, I need to clarify the following:

1) Do I create the master site on both machines??  i.e. on AIX, the db is called db1, and I will create a master site for db1.  On HP, the db is call db2, and I will create a master site for db2????  Or is it created on only the AIX?  
2) If I only need to create on the AIX, do I still need to create the user 'repadmin' on the HP box?  But can I do it using OEM w/o creating the master site??  

Thanks for ur help again!

Brgds..
0
 
LVL 1

Expert Comment

by:roberttran
ID: 8084287
1. I'd suggest you to start the intelligent agent on the HP box and use OEM to discover the db2.

2. Once you can connect to two databases, then create db1 (AIX) as master definition site and db2 as master site. You should be able to do this in one task using RM.

Good luck.
0
 

Author Comment

by:weeang
ID: 8085672
OOps...

I think I got all confused... Pardon my stupidity pls..

Now, Replication Manager, where is it available?? I am sure that we have the enterprise edition on our servers.  N I also know that it comes with the Enterprise Edition.

However, all along, I had thought that in OEM, under each DB, there is a Replication Tree, and that it is the Replication Manager?  Did I get it wrong??  Otherwise, where cld I initiate it?

Actually, I am trying to simulate the above by creating 2 DB on my local Win2000 machine running Oracle Personal Edition...  I know that it wld be different when I were to do it on the servers, but I jus wanted to get the steps rite...  Do u think i m doing it the right way???

Pls help!
0
 
LVL 1

Expert Comment

by:roberttran
ID: 8091467
hi, as my insructors said, there is no such thing called stupit question. anyway, you asked good questions and i enjoy helping beginners and learning from them.

actually, you should use the RM under DBA studio. It gives you better management then running it under OEM console. It also comes with step-by-step links that you can configure replication.

i think you are on the right track, it is always good to practice on the development machine then production.

robert.
0
 

Author Comment

by:weeang
ID: 8101280
hi,

thanks for being so kind...

DBA Studio is only avail thru the Oracle installation cd, is that so??? Cos' i have a problem with locating the cd.  The DB was installed by some consultants that my company engaged, and its kinda 'lost' somewhere...  OR is it avail via some other source?? esp, since we do have the enterprise edition??

thanks again for ur kindest help...

weeang
0
 

Author Comment

by:weeang
ID: 8101483
hi,

thanks for being so kind...

DBA Studio is only avail thru the Oracle installation cd, is that so??? Cos' i have a problem with locating the cd.  The DB was installed by some consultants that my company engaged, and its kinda 'lost' somewhere...  OR is it avail via some other source?? esp, since we do have the enterprise edition??

thanks again for ur kindest help...

weeang
0
 

Author Comment

by:weeang
ID: 8101574
hi,

thanks for being so kind...

DBA Studio is only avail thru the Oracle installation cd, is that so??? Cos' i have a problem with locating the cd.  The DB was installed by some consultants that my company engaged, and its kinda 'lost' somewhere...  OR is it avail via some other source?? esp, since we do have the enterprise edition??

thanks again for ur kindest help...

weeang
0
 
LVL 1

Expert Comment

by:roberttran
ID: 8107136
i'd install 9i ee on your w2k and try it there.

hope this helps
0
 

Author Comment

by:weeang
ID: 8243853
hi there,

i have another question.

my AIX box contains 3 different databases.
can i replicate tables from these 3 different databases into 1 single database on a HP target server?

if yes, do i need to create 1 repadmin user for each of the 3 different databases?  or creating 1 repadmin on any database suffice??

m having challenges with the dbastudio.. cos' seems like i cant start dbastudio.. keep getting xlib, java.nullpointer errors....

thanks n brgds,
weeang

p.s: finally got to do this proj again.. was taken off to do some other development... hope to receive ur help again!! thanks!!
0
 

Author Comment

by:weeang
ID: 8243899
hi again,

managed to get into dbastudio finally..
:)

pls do advise me on the repadmin part... tks!

brgds,
weeang
0
 
LVL 2

Expert Comment

by:tschofiled
ID: 8244183
Not tried what you are thinking of doing but :-

1) Replication can only be completed withing schemas with the same owner. You will need 3 schemas.

2) You will need a repadmin account on all 3 donor systems. Not sure if one on the target will be OK. It should be but having not completed this I cannot be certain.

Tony
0
 

Author Comment

by:weeang
ID: 8259731
Hi,

Thks tschofiled for the above info..

Now, I have done the following:

1) On my AIX, Oracle 9.0.1, created a master site for DB1
2) On my HP, for DB2, I tried to create materialized view site.. but failed!

Basically, I cant seem to be able to create the database link from HP to AIX.  Btw, when I create the master site, it did not ask to create db link to HP???

Can someone advised me how to create the database link??  On HP, whenever i create a db link to AIX, it always appears as DB1.US.ORACLE.COM... but i've already set the DB_DOMAIN as '' (empty)!!

Actually, I thot i can automate the above using Replication Manager.  Steps that I did are:
Under Replication -> Materialized View Site -> Create Materialized view.. but during the process, i get the error:
"ORA-02085: database link DB1.US.ORACLE.COM connects to DB1"

FYI, i have already tried to rename global_name on the HP from DB2.US.ORACLE.COM to DB2... but it still remains as DB2.US.ORACLE.COM...

What is wrong??????
0
 

Author Comment

by:weeang
ID: 8259769
Hi,

Then again, I also tried to create Materialized View Master Site on the HP...since Master Site on AIX is already created... but also encounter the following:
"Statement: ------------------------
/*Oracle OEM*/CREATE PUBLIC DATABASE LINK "MKASI" USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxxxxxxx)PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DB1)))'
Stack Trace: -----------------------
ORA-02011: duplicate database link name"

However, if i were to manually delete the db link in HP, and perform the steps above again... the same error will appear!!

Pls help!!  Thanks...

Actually, does creation of materialized view suffice with the master site.. or do i need to create materialized view master site... as in this step???
0
 
LVL 2

Expert Comment

by:tschofiled
ID: 8259840
I am a bit puzzled by some of your db link problem; and will think this one through.

The way materialised views work is :-

1) SOurce site has logs created table account would have a view MLOG$_ACCOUNT. This stores inserts, updates and deletes.
2) Target site has materialized views created. Effectively these become copies of the tables on source, but you can't update, insert and delete. No indexes are created its a separate job.
3) An Oracle job is created to refresh the MV's on a regular basis.

So this is a pull to target from source, and  as such you need a DB Link only from Target to Source. As far as I am aware Oracle does no source-> target communication.

Tony
0
 

Author Comment

by:weeang
ID: 8260047
Hi,

Thanks... pls do help me on the db link problem....

Oic.. so, i only need the db link from my HP to AIX.. Thanks!!

Btw, jus want to clarify if my master site (AIX) is correctly configured..

On AIX:
- I set it up as Master Site. It is also the master definition site.
- Created 1 master group, which has all the tables i want to replicate specified under objects.
- Now, this group appears under 'Topology' and has some 'DBMS Jobs' under Replication -> Administration.

Is this correct??? However, as per ur point number 1, my source site (AIX) does not have a view MLOG$_ACCOUNT..... Seems that when i set up the master site n group, it did not create it... How shd I set it up???

About ur point 2, if I include the indexes as objects in the master group, does it mean that it will not be replicated, and I will need to do it manually??

About ur point 3, the oracle job shd b instantiated from AIX (source) or HP (target)???

Thanks for ur kind help!  :)
0
 
LVL 2

Expert Comment

by:tschofiled
ID: 8262302
I am a bit puzzled by some of your db link problem; and will think this one through.

The way materialised views work is :-

1) SOurce site has logs created table account would have a view MLOG$_ACCOUNT. This stores inserts, updates and deletes.
2) Target site has materialized views created. Effectively these become copies of the tables on source, but you can't update, insert and delete. No indexes are created its a separate job.
3) An Oracle job is created to refresh the MV's on a regular basis.

So this is a pull to target from source, and  as such you need a DB Link only from Target to Source. As far as I am aware Oracle does no source-> target communication.

Tony
0
 
LVL 2

Expert Comment

by:tschofiled
ID: 8262406
Snapshot replication is different to Master definition site replication. You are creating the full beast!

Again I haven't completed from 9i to 8i, but have from 8i to 8.0

Steps needed are :-
Source system

Create snapshot logs for all tables to be replicated. This creates a table MLOG$_<table_name> for each.

e.g. create snapshot log on account tablespace rate_d1 storage (initial 1M next 1M pct increase 0);

Target system

Create a database link to Source system
Create snapshots for each replicated table.

e.g. create snapshot ACCOUNT as select * from ACCOUNT@@dblinkname';

Create a refresh job for the snapshot tables.

e.g. exec dbms_refresh.make('snp_group','ACCOUNT', sysdate,'sysdate+1/(24*4)');

NOTE this package would normally be for all table delimited by ‘,’ and all enclosed in quotes. There are versions of the package to add tables, remove tables and also drop the whole group.

The objects created are materialised views, which may have additional indexes, be analyzed etc. Treat as normal tables and indexes in a cost-based optimiser database.

Hope this helps.

Tony
0
 
LVL 2

Expert Comment

by:tschofiled
ID: 8262442
On the subject of your database links.

Do you have a sqlnet.ora file in $ORACLE_HOME/network/admin (target side).  If so post its contents - I think this may be where the problem lies.
You could try renameing the file to something else, drop and re-create the link and see if it works.

Tony
0
 

Author Comment

by:weeang
ID: 8266525
Huh!!

And i thot i read the Oracle 9i Replication manual.. n it says to implement Read-only Materialized Views is done like tt... errrmmm.. jus double-checked n it did mentioned tt the views do not need to belong to a replication grp.... now, i m stumped!

do u happen to know of any document that will tell me how to do snapshot rep step by step.. since i m totally clueless abt this... i m truly worried abt missing steps w/o a document.. n eventually, if i fail.. it wld definitely make me miserable trying to figure out where it had gone wrong.... :(

thanks so much,
weeang
0
 

Author Comment

by:weeang
ID: 8266537
Database links...

Target side sqlnet.ora contents:
---------------------------------------------------
# SQLNET.ORA Configuration File:/oracle/product/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.

# NAMES.DEFAULT_DOMAIN = pac

NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
---------------------------------------------------

Are you saying to rename the sqlnet.ora file to something else... w/o replacing with a new sqlnet.ora file... n then drop n re-create the link???

brgds,
weeang
0
 

Author Comment

by:weeang
ID: 8267735
Ok...

now.. i tried to create materialised view site using RM..
n guess what.. realised tt the script that the setup is generating actually is trying to create database links twice which is y i keep getting the error "ORA-02011 duplicate database link name"...

how to get by this???  also if i were to save the script.. it only saves it up to the pt where it got the error... subsequent steps to perform r not included!

how how?? i m getting flustered... boss is chasing!!! :'(
0
 

Author Comment

by:weeang
ID: 8267963
Now,

I also tried to create snapshots as per:

On AIX:
- "create materialized view log on db1.table1 with rowid;"

On HP:
- "CREATE PUBLIC DATABASE LINK "DB1" USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<ip>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DB1)))';"
- "create database link db1 connect to <schema_owner> identified by <schema_owner_pwd> using 'db1';"
- "create materialized view <schema_owner>.table1 refresh fast with rowid start with sysdate next sysdate + 1/(24*60) as select * from <schema_owner>.table1@db1"

But on the HP, at the last step above, i get the error:
ERROR at line 1:
ORA-01005: null password given; logon denied
ORA-02063: preceding line from DB1

What did I miss??

If I view dba_db_links, I have the following:
OWNER      DB_LINK              USERNAME   HOST                 CREATED
---------- -------------------- ---------- -------------------- ---------
SYS        MKASI.US.ORACLE.COM  MKPRC      mkasi                04-APR-03
PUBLIC     MKASI.US.ORACLE.COM             (DESCRIPTION=(ADDRES 04-APR-03
                                           S=(PROTOCOL=TCP)(HOS
                                           T=10.65.8.249)(PORT=
                                           1521))(CONNECT_DATA=
                                           (SERVICE_NAME=mkasi)
                                           ))
0
 

Author Comment

by:weeang
ID: 8268001
oopps.. wrong info... DBA_DB_LINKS contains:

OWNER  DB_LINK           UNAME HOST                 CREATED
------ ----------------- ----- ---------            ---------
SYS    DB1.US.ORACLE.COM TB1   mkasi                04-APR-03
PUBLIC DB1.US.ORACLE.COM       (DESCRIPTION=(ADDRES 04-APR-03
                               S=(PROTOCOL=TCP)(HOS
                               T=<ip>)(PORT=
                               1521))(CONNECT_DATA=
                               (SERVICE_NAME=DB1)
                               ))

Actually, i added another entry in tnsnames.ora DB1.US.ORACLE.COM, so that it will recognise the db_link since I still do not know y the US.ORACLE.COM is always appended to the end.... :(

Any idea wat went wrong?
0
 
LVL 2

Expert Comment

by:tschofiled
ID: 8268555
Still puzzled by the extention you get added to the db link name?

I expected this would ne in the sqlnet.ora file.

Try this on HP side

create database link <yourchoice> connect to <schemaowner> identified by <schemaownerpassword> using <descriptor_in_single_quotes>;

descriptor is from your tnsnames.ora like :-

e.g.  'db1.world'
where tns entry looks like :-
db1.world=
(DESCRIPTION=
(ADDRESS=(PROTOCOL=TCP)(HOST=<ip>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DB1))
)

On the DB1 (IBM) database, create a public synonym for a table and public grant too.

From HP :
select * from <table_name>@<dblinkname> where rownum <5;

This should create and test the db link in the simplest form.

As to the problem from RM, It assumes that it needs to do the whole job, including the DB links, and fails at this point because they exist - correct action.

On the failure you have with the db link usage ( create materialised view) - you have a problem here with some security issue, and I am not at my desk and have manuals in from of me. If the above fails (create and test db link) then the security settings may be the problem. Whether this is an init.ora parameter, or something in the way 9i sets up security I am not sure.

Hope this helps!! Although it seems simple replication is quite complex! - it took me a good 3-4 months to get to grips with it all in test a few years ago.

Let us know how you get on!

Tony
0
 

Author Comment

by:weeang
ID: 8282422
Hi Tony,

:'(... it still doesnt work!

i did wat u mentioned above for both HP n IBM..

1)On HP, tnsnames.ora:
  db1 =
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = <ip>)(PORT = 1521))
      )
      (CONNECT_DATA =
        (SID = db1)
      )
    )

  execute the following to create db link:
  create database link db1 connect to usertest identified by usertest using 'db1';

2) On IBM,
   create public synonym test1 for usertest.table1;
   grant public to usertest;

3) On HP,
   select * from test1@db1 where rownum < 5;

BUT, i got the error:ERROR at line 1:
ORA-02085: database link DB1.US.ORACLE.COM connects to DB1

it is still the same error!!!  


For what RM is doing... think it cld be a bug?  Cos', if i were to delete all exisitng db links from both IBM n HP, and restart the materialized view... it will still try to create the db links twice, causing the wizard setup to terminate!!  Do u hve any good resolution for me????  

U r lucky tt u get 3-4mths to do this... my bosses wanted me to do this within a week, which is so not realistic.. but yet, they dun believe me when i mention tt it is jus not as simple as they thot!!! :(

brgds,
0
 

Author Comment

by:weeang
ID: 8289445
Hi Tony!

I managed to replicate 1 table!!!! *HOORAY*

To solve the above extension problem, I had to define the same db_domain for both IBM and HP for them to recognize each other!!!  Now, the link works!!

So, now, I managed to create a materialized view using the following:

On AIX:
- "create materialized view log on db1.table1 with rowid;"

On HP:
- "CREATE PUBLIC DATABASE LINK "DB1" USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<ip>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=DB1)))';"
- "create database link db1 connect to <schema_owner> identified by <schema_owner_pwd> using 'db1';"
- "create materialized view <schema_owner>.table1 refresh fast with rowid start with sysdate next sysdate + 1/(24*60) as select * from <schema_owner>.table1@db1"

Now, I have a few questions:
1) How do I query the log on the AIX??
2) How steps should I follow in monitoring the replication?
3) Do u think per min refresh is too much??  How do I specify the refresh to occur only on every commit?  How does per min compare to per commit?
4) Your advice on additional stuff tt I shd take note of??

You hve been a great help to me!!  Hope to hear from u again..

Thanks n Brgds,
weeang
0
 

Author Comment

by:weeang
ID: 8289454
Oh...

actually, for the above comment, pls ignore the "CREATE PUBLIC DATABASE LINK ....".  I did not perform this step n it works...
0
 
LVL 2

Expert Comment

by:tschofiled
ID: 8289842
Well at least its all working. I didn't think it was a domain problem from the sqlnet.ora entry. Anyway all water under the bridge.

There will be a table called MLOG$_TABLE1. This contanns details of the changes. Have not looked at 9i, but on 8.0 it held info about PK, type of change and a timestamp (from memory).

Your questions :-

1) How do I query the log on the AIX??
There will be a table called MLOG$_TABLE1. This contanns details of the changes. Have not looked at 9i, but on 8.0 it held info about PK, type of change and a timestamp (from memory).  
Select * (or count(*)) from MLOG$_table1;

2) How steps should I follow in monitoring the replication?

The job on the target is the easiest, just run
select job,what,broken from dba_jobs;
If broken comes back 'Y' then replication won't be working. basically the job will try 16 times to run and then break itself if it fails at all attempts.

On the Source, I guess the best way is to check for the existance of a record in Mlog$_table1, and then check after the refresh interval if its still there.

3) Do u think per min refresh is too much??  How do I specify the refresh to occur only on every commit?  How does per min compare to per commit?

The only place I have this running we are set to 15 minutes. there's abour 18 Gbytes of replicated data, with (I guess) 2-3 million updates per day. The decision is yours really depends on what latency you can suffer. Remember one thing, that oracle will not start another job running when one already in progress. I suggest looking in the dba_jobs table at (from memory) last_time and Next_time to see how long a snapshot takes to run. Next_time is set at the end of the run to the interval defined. e.g. 15 mins interval and last run was at 08:10 next will be at 08:25. However if the run took 16 minutes it will be reset for 08:40. Bit crude but It;ll give you indications. I should also say you can monitor from Replication manager, although unless you set up jobs withing EM there is no automation.

4) Your advice on additional stuff tt I shd take note of??

Once running provided there are no problems which cause the job to break (e.g. network!!!) then its pretty fault free.

Do remember to build indexes and analyze regularly to ensure reporting is as efficient as possible.

From experience I would not use the packages that check and report if the data gets out of step they are really slow. I find it quicker to set up a maintenance slot, destroy the refresh job and snapshots and re-build the lot.

Good luck - mention me to your boss he may reccommend a pay rise!!!

Tony


0
 

Author Comment

by:weeang
ID: 8297948
Hi Tony,

Hmmm... 9i doesnt seem to have the MLOG$_TABLE1.....
But, as u mentioned, I can see the jobs under dba_jobs.

For all indexes on the tables, I will need to build them manually right?

Thanks a million for all these help!!

Ha ha... well.. from where i m based, doubt my boss will recommend a pay rise... he has the perception that replication is a service that can be ON, n will miraculously work.... *$%#@
0
 

Author Comment

by:weeang
ID: 8324878
Hi Tony,

I have another question...

If for example, the replication job failed.. How do I initiate it to restart???  Or what is the correct procedure to enable replication again???

Pls advise...

As usual, thank u very much for ur great help!

brgds,
weeang
0
 

Author Comment

by:weeang
ID: 8324983
Hi Tony,

Oops... found the MLOG$_table log tables....
its in the source and under the respective schema...
i had actually tried as user 'sys' tt's y it doesnt exist!!

Thanks... n hope to get ur advise to above...

brgds,
weeang
0
 
LVL 2

Accepted Solution

by:
tschofiled earned 140 total points
ID: 8325734
Sorry but haven't been on much for a few weeks now - busy times at work - mustn't complain...

1st update
Indexes you build on materialised views in the same way as for tables. PK is important in my opinion, also any index you are likely to continually use for queries. Of course you can have different ones, if work on one database is different to the replicated one.

Another piece of advise, is analyze the materialised views and their indexes regularly. Obviously not required if you run a rule based optimizer.

2nd update
To re-start a job you need to log in as the job owner, and use the DBMS_JOB package :-

at sql> prompt

exec dbms_job.broken(false);

commit;

exit

The reason for the commit is its a table change! caught a dba out here a couple of years ago where he still had the sql screen open (update uncommitted), replication was not working and it was 8 hours on!!!!

On the how to/whether to restart question, it depends really on how much you are replicating - sorry but its no answer really! IMHO if its well out of date - thousands of updates queued, I'd destroy and re-build, its quicker usually. You can just restart the job and see how long it takes to recover. You could even plan and execute an outage as part of testing - say at 1 hour, 2 hours.....etc.

3rd update
Sorry I wasn't clear about where you should find the MLOG$ tables - I did think I said on source, but they are under the same schema as table owner - anyway you know that now.

Tony

0
 

Author Comment

by:weeang
ID: 8377762
hi tony,

sorry to troube u yet again...
but it seems tt i might hve setup something wrongly for the replication... it keeps getting broken.. dun understand y...

currently, i create the materialized views using the following statement:
"create materialized view <schema_owner>.table1 refresh fast with rowid start with sysdate next sysdate + 1/(24*60) as select * from <schema_owner>.table1@db1"

the statement above actually performs refreshes every min.  i hve a feeling tt the problem might lies in this.. how do i change it such tt it will refresh only on commit??

or do u hve a different take on what my problem wld b??

tks again for u kind help,
weeang
0
 
LVL 3

Expert Comment

by:patelgokul
ID: 9927561
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: tschofiled {http:#8244183}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

patelgokul
EE Cleanup Volunteer
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question