Logical Standby is a wonderful underline of Oracle Database Enterprise Edition.
But, as with any product, it has its own set of limitations, a must be wakeful of.
To my opinion, the many limiting of all, nonetheless 11g reduces it a lot, is the paltry number of datatypes that the
SQL Apply routine can deal with.
The Dataguard Concept and Administration Guide tells that BFILE, Collections (including VARRAYS and nested tables), Multimedia information variety (including Spatial, Image, and Oracle Text), ROWID, UROWID, User-defined types, XMLType stored as Object Relational, Binary XML are unsupported datatypes for Logical Standby.
This means that, as shortly as such a datatype appears in a list definition, it cannot be rubbed by the SQL Apply routine and thus, the list cannot be segment of a set of replicated tables to the Logical Standby.
I frequently got stranded with that reduction that done the technology a really limiting one.
So, we was really gratified to learn that engaging underline of 11gR that might - in a few cases - help pull ahead of these limitations. This underline is the FIRE_ONCE skill of triggers.
This skill (TRUE/FALSE) simply tells either a trigger should glow when the bottom list gets altered by a user routine (FireOnce=true) or when it gets altered (FireOnce=False) . And this changes all !
By default (TRUE), trigger usually fires when a user routine creates changes on its bottom list . Obviously, when the trigger fires on the Primary Database, it performs changes that are converted to Change Vectors, that are rubbed by the SQL Applier, without the need of the trigger to be dismissed once again on the Standby side.
But, surroundings it to FALSE, that means the trigger will moreover glow on the standby side, may be really helpful.
This skill may be used to enable Complex Datatypes (user types, Spatial objects, etc) to be rubbed by a Logical Standby Database.
The pushing thought is the subsequent to :
1. Use a trigger to Flatten the intricate datatype (CD) in to a exemplary make up in a record table.
This is a exemplary trigger (FireOnce=True) that will glow usually on the Primary site
2. Use stadard SQL Apply to replicate this exemplary list to the Standby site
3. Use a trigger to reconstruct the intricate make up from the exemplary table.
This will be a FireOnce=False trigger that will take the work on the Standby Site.
A hurried e.g. :
Let us see how to do that by a elementary demo.
Consider the subsequent to structures :
emanate sort myComplexType as intent
( Name varchar2(20),
SurName varchar2(20),
BirthDate date,
Picture BLOB);
emanate list myComplexTable
(id number first key,
Typ myComplexType);
Because it contains a Complex Datatype, list myCompleTable cannot be rubbed by the SQL Applier.
So let's use the new technique.
0. Assumptions
11gR2 Enterprise Edition
An existing database DB11G
An existing Logical Standby DB11G_DR stopped (alter database end judicious standby apply;)
An existing schema : GILLES/HARO with link up and resource privileges.
Also give govern on sys.DBMS_LOGSTDBY. Also access to office data_pump_dir
1. Create a prosaic record list
emanate list myComplexLog
(action char(1),
logID number,
logName varchar2(20),
logSurName varchar2(20),
logBirthDate date,
logPicture blob);
4. Set the FireOnce skill to the trigger myComplexLog_trg so that it fires on both sides.
exec dbms_ddl.set_trigger_firing_property( -
trig_owner => 'GILLES', -
trig_name => 'MYCOMPLEXLOG_TRG', -
fire_once => FALSE);
5. Prepare the network is to operation.
If the list is void (but it might not), then the routine will be a lot simpler,
5.1 Lock the list
link up / as sysdba
close list gilles.myComplexTable in share mode;
5.2 Get the SCN number
adjust network switch logfile;
choose current_scn from v$database;
5.3 Retain this current_scn worth
we have 1188638
5.5 Use the SCN to run an expdp
!expdp gilles/haro tables=myComplexTable directory=data_pump_dir
dumpfile=myDumpFile.dmp REUSE_DUMPFILES=Y flashback_scn=1188638
5.6 Enable the trigger (remember step 2, trigger is combined but disabled) and transfer the bail out file to the standby.
adjust trigger gilles.myComplexTable_trg enable;
!scp myDumpFile.dmp oracle@oeldg3:/oracle/admin/DB11G_DR3/dpdump
5.7 On the Logical Standby side, import the list
emanate office myDir as '/oracle/admin/DB11G_DR3/dpdump';
!impdp gilles/haro tables=myComplexTable directory=MyDir dumpfile=myDumpFile.dmp
5.6 Restart the SQL Applier
adjust database beginning judicious standby request immediate;
From right away on, any change that would happen on the list myComplexTable will be copied on the Logical Standby.
Let's try it.
On the Primary
insert in to mycomplextable
values (1, mycomplextype('Haro', 'Gilles' , '11-APR-2010', null));
commit;
choose * from mycomplexlog;
--> Will lapse nothing as the trigger deleted the quarrel after having updated it.
Meantime, the quarrel went over the network to the Logical Standby.
On the Standby
choose * from mycomplextable;
authorization TYP(NAME, SURNAME, BIRTHDATE, PICTURE)
--- -----------------------
1 MYCOMPLEXTYPE('Haro', 'Gilles', '11-APR-10', '')
choose * from MYCOMPLEXLOG;
--> Will lapse nothing as the trigger deleted the quarrel and the info went over to the Logical Standby.
Conclusion :
Oracle Database 11g provides other meant to alternative route existing restriction concerning Logical Databases. Of course, the new meant is not a concept solution. It has its own limitations, also. For example, we would not scale effectively if it comes to outrageous list changes. Also, it does not attend to DDL. But this can save a lot of time to know this underline exists.
Addendum
I did not speak about other skill for triggers that would be an swap answer to the firce_once trigger. We can set a trigger so that it usually fires on the standby server. This skill is apply_server_only.
Using this, the trigger myComplexLog_trg would have been even simpler. No must be examine either it fires on the standby network or not. Only set the skill is to trigger using the subsequent to code.
Bibliography : Oracle Database 11gR2 - Managing a Logical Standby Database " Chapter 10, Section 10.6.4 presents the two properties of triggers. This post should be seen as a TestCase for this chapter.
Gilles Haro
Technical Expert - Core Technology, Oracle Consulting
No comments:
Post a Comment