Oracle 10g Triggers on Oracle10g Different Database.

Hi,
Could you guys help me to create oracle triggers, well I have two production oracle 10g servers RAC configured  now I need to build one DR for any unforeseen circumstances, im trying to map some tables or users on DR oracle machine which data very frequently change on production for example if any value change in USER_1 (on Production) it should update on DR database so I can save time to restoring dump file on DR machine and my DR Environment  not same like production that’s why I need to map some tables and user on DR because of same configuration tables and fields are there on production environment.

OS - Windows 2003 32bit
DB – Oracle 10g
Oracle knowledge - Level beginner  
xeondxbAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Muhammad KhanManager, ITCommented:
I am afraid.. Triggers won't help you in this scenario.. You better think about implementing a Logical Standby using Data Guard..
0
slightwv (䄆 Netminder) Commented:
>>so I can save time to restoring dump file

How do you see triggers helping if you plan on using exp/imp or expdp/impdp?  Or do you mean something else by 'dump file'?


I tend to agree that triggers probably are not the way to go.  For High Availability, Data Guard is the Oracle suggested method.

Personally, I use Oracle Streams Replication (Active Data Guard also uses Streams in the background).

You can probably come up with triggers but you can run into issues with production when your DR database is down.  The trigger will fail, then the production system will not be able to complete the transaction.

I encourage you to look into other DR options.
0
Wasim Akram ShaikCommented:
alternatively if there are not many tables which get changed, instead of triggers, i would suggest you to go for Materialized views,

you can run a job in the background to do a complete refresh/fast refresh whenever you need.

but as said in the earlier comments, they might be better if your frequency of replication is high and data is huge...
0
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

xeondxbAuthor Commented:
1st sorry guys i was late in reply i was busy one more issue anyway

@aiklamha:
do i need license for this ??? "Data Guard"

@slightwv:
Please suggest  me some thing which i can help me to  update  my data from from production to DR

@wasimibm:
do i have to create "Materialized views" on production side ??

im using documentum and BEA portal 10

Thanks
0
Wasim Akram ShaikCommented:
@xeondb, you have to create Materialized views on DR side, not on production...

no, you have to create materialized view on DR side and you have to create materialized view log on production side.

Oracle Standard Documentation for MV's can be found here:

http://docs.oracle.com/cd/B10500_01/server.920/a96567/repmview.htm

Inshort, if you want to use MV's

you have to do these things.

better login from sysdba to avoid grant issues.

1. Create a Materialized view log on production site which needs to be replicated in DR

CREATE MATERIALIZED VIEW LOG ON <TABLE_NAME>
TABLESPACE <TABLESPACE_NAME>
WITH PRIMARY KEY
INCLUDING NEW VALUES;

2. Create a db link between production and DR

CREATE DATABASE LINK <DB_LINK> CONNECT TO <schema_name> IDENTIFIED BY <password> USING '<TNS_NAME of Prod in tnsnames.ora of DR Site>';

3. Now create a MV on DR Site

CREATE MATERIALIZED VIEW <MVIEW_NAME>
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
AS
SELECT * FROM <TABLE_NAME>@<DB_LINK>;

After this you need to refresh the mview.

try to have a look in the below link for further details.

http://www.oracle-base.com/articles/8i/MaterializedViewReplication.php
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
xeondxbAuthor Commented:
@wasimibm:
so in this scenario i wont change anything on production isn't why im confirming i really don't want to touch my production coz, im not an  expert in oracle on DR side i can do anything coz, it'll be my baby.... for connection do i have to install listener on DR side to make a connection to production?  
0
Wasim Akram ShaikCommented:
@xeondb: Then I'm afraid, how can you achieve this in real time..
because the best possible scenarios are as mentioned earlier, oracle streams, data guard, golden gate, data mirrorig etc.. which are not of Free of Cost and do require a license

and the other ways to do these tasks are MV's, triggers, these will be almost replicatelike a real time scenario and which don't need a licensing fee.

also if you take an export from prod and import in DR it would also suffice your requirement, but this will not be like real time one..

@xeondb, if you create triggers, then also you have to create them on prod right..?

then you can actually do one thing, but this would get a little bit messy if you dont do the scripting right..

Identify all the tables where in data gets changed on frequent basis.

Identify the parent child relationships between those tables.

Schedule a job which will do these things in sequential order

Disable all constraints in DR site
truncate the existing tables in DR site.
import all data from DR site from required tables
enable the constraints..


0
xeondxbAuthor Commented:
@wasimibm: thats what i really wanted to do in the beginning now can you give me guidance how to do this ?

0
Wasim Akram ShaikCommented:
@xeondb: yes, will do.

You have to follow the steps i mentioned in the above comments.

1.create a database link from dr site to production site.
2. Identify all the tables where in data gets changed frequently.
3. Check the primary key and foreign key relation ships in those tables.
4.depending on the frequency you need you have to schedule a job in dr site(as you dont want to any thing on produciton).
this batch job should do the following tasks
a)
disable constraints on the tables or DR site and truncate those tables
and get the data from production again and enable the constraints
                                OR            
if you dont want to disable or enable the constraints then you can go for this approach

insert into dr_table
select * from dr_Table minus select * from prod_table
(based on the primary and foreign key relationship, table sequence should follow)




0
xeondxbAuthor Commented:
how create batch job to  start this insertion process on dr side ?
0
Wasim Akram ShaikCommented:
sorry. batch job is a database job

a database job can be a database procedure, package or a function which we want to run on our behalf by the database server

this can be achieved by doing this

declare
job integer;
begin
dbms_job.submit(
JOB       OUT BINARY_INTEGER,
WHAT      IN  VARCHAR2,
NEXT_DATE IN  DATE           DEFAULT SYSDATE,
INTERVAL  IN  VARCHAR2       DEFAULT 'NULL',
NO_PARSE  IN  BOOLEAN        DEFAULT FALSE,
INSTANCE  IN  BINARY_INTEGER DEFAULT 0,
FORCE     IN  BOOLEAN        DEFAULT FALSE);
dbms_output.put_line('job no':||job);
end;

what specifies the procedure or function name,
next_Date specifies when you want to schedule to job
interval specifies the delay between subsequent executions
other parameters will not have any effect, use the default one when you are using it for first time.

you can get more details of how to use database jobs on this link

http://psoug.org/reference/dbms_job.html

but before submitting a job you have to make a procedure iin databse which will do those steps which i mentioned in my earlier comment.
0
xeondxbAuthor Commented:
@wasimibm: Thanks sorry for reply i did not apply these things on my server coz, im rebuilding my server when it'll ready i'll try this...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.