CDC for Oracle Databases using SQL Server Integration Services 2012

The video demonstrates how to perform CDC for Oracle databases using the new features introduced in SQL Server 2012 Integration Services (SSIS). You'll learn how CDC for Oracle works, how to create a CDC service and administer the service, as well as monitor the logs and traces to troubleshoot the service.

---

Closed Caption:

hi this is our case i work here at
Microsoft on the engineering team force
equal to integration services also known
as SS to the use of this video i'll be
showing you how to perform CDC for
oracle databases using the new features
introduced in sequence of education
services 22 in this video we'll be
talking about how you can use the new
series of oracle features to capture
change data from Abel stored on the
oracle database
first we talked about how the concept of
cdc42 works then you learn about how you
can create a CD service administrator as
well as monitor the logs are traces so
as to troubleshoot the service so let's
answer the essential question as to how
the whole thing works
the CDC for oracle leverages the native
CDC functionality that is available in
sequel server it essentially create
creates the sequel server mirror tables
of the tables located on your source
oracle database for instance it creates
a meditation called human resources
start employee from the source table
which is called him one resource such an
employee from your source oracle
database and then enables sequence of
Assisi on those Miller tables after that
it just sinks the transactions between
the source oracle database to the city
destination sequel server database and
uses the sequence of Assisi to capture
changes from the matter tables you can
just consume that changes from the
change tables that were created by the
sequence of Assisi just as just as any
other change people from the sequel
server database CD service as shown here
is basically a window service which runs
on one of your systems
this service is responsible for running
the entire CDC operations such as
creating the source oracle database for
changes thinking the changes based on
transactions to one of your secrets or
dealer middle tables which has been in
trouble for
ocbc sec instance which you'll see data
will hold information such as what is
the source oracle database and the
connection information that you'll be
using to connect to the source oracle
database which tables and columns do i
select from the source which CC capture
instance of the CDC ruined that i should
be using on the secrets of a CDC on the
destinations equal so now let's jump
into a demo in this demo let's start by
creating a service which you'll be used
using for waiting changes from the
source
oracle database but this will start
something called change data capture
service configuration for oracle and
this is AMC which you can use the first
step is to prepare the sequel server on
which you'll be hosting your mirror
tables as well as your metadata database
so i have our sequence over that i am
going to prepare right now when I
prepared the sequel server it will
basically create this database called
ms.xdg see which contains all the
metadata information about all the
services that are learning as well as it
will create the mirror database and the
mirror table once that is done we are
now ready to clear actual service so
let's say the first service that we
create is all called Oracle CD service 1
i'm going to use my local account to to
be used as for running the service i'm
going to connect to the same sequence of
instance I just prepared
and i'm going to create a master
password which will be used to create
the symmetric key
once the service is prepared it is not
available for creating instances on so
multiple instance series instances can
be created on a single article CDC
service now let's see how we can create
an instance for creating an instance i
go to the other component that is
available to me called the Oracle CDC
design and configuration MMC when I
invoke this MC I would be asked about
the sequel server instance which is
associated with the CDC services so i
give the same sequence of instance that
had prepared in the previous step
now once i connect to the sequel server
it'll show p all the services which are
created on the sequel server so i select
oracle CDC's of this one let's create a
news article CBC instance here when i
create the first instance the first
thing it has to do is it has to create
this bitter database on the destination
sequel server so let's create that
database once the database is created
I need to both mention which is the
connection string for my source oracle
database as well as the username and
password so let's do that
test the connection so in this step the
visit asks me that which are the tables
for which I want to capture CDC changes
so let's start by adding some tables in
this table selection wizard you can
actually filter all your tables by
schemas so let's say I want to select a
child . employees so i'll select the
scheme ihr i can do a search and I can
see all the tables are not the ski my
job
let's say I did I just wanted to look at
only employees table i can filter all
the tables available by the table in
patent and I get fully charge applies
let's select that now let's talk about
the two other columns that we can see
here the capture instance is a capture
instance used to name the instant
specific change data capture objects on
my sequel server database a captain
instance is a system on your sequel so
and it cannot be not if it is not
specify the name is derived from the
source schema plus the name of the
source table in the format as shown here
similarly a seriously getting rule is
the name of the database at all that is
used to get access to the change dinner
roll name is assist name and must be
specified if explicitly set to null
which is happening here right now
no getting role will be used to limit
access to the teens data however i'm
going to use a getting gold for my
purpose here after that if i wanted to
select only a couple of columns from the
stable i can do so using that as you can
see by default all the columns are
selected for cbc let's say I did not
want to gag get the calculated columns
such as commission percentage or
department manager ID for capturing CBC
changes so i can unselect them here and
they will not be in my series instead
which is used for capturing the changes
once that is done now let's go to the
next step where we would perform the art
supplemented logging so using this
Oracle supplement logon script we add
supplement log into all the tables and
columns that we are going to capture in
the CDC instance note that the script
can be executed even after this visit
has completed and we can as well as it
gives you a button for executing it
right now so let's execute it right now
this has failed for me as i have
executed the script once before so it is
not a problem for me in the final step
of the visit i would run all the steps
basically what the visit will do is
validate the Oracle environment it will
do steps such as verifying that I have
the required privileges to query from
the audience oracle databases it would
make sure that i enable supplement and
logging on all my tables
it should make sure that the oracle
database that i'm connecting to is of
required version and it does this extra
check of making sure that the article
database has archivelog second now let's
run this while it will also prepare the
CDC database which I mentioned in the
first step and will create the middle
tables on the CDC database and will
create the drop and create the capture
jobs
as you can see some of these databases
will create now let's look at the
database which was there so as you can
see there are no database is present on
the sequence of instance and let me do a
refresh and I can see there are two new
database is created one is called MSX
BBC the other one being exercised demo
which i just created
I click next and finish the visit now
our instance is ready for operation so
we'll right-click . operation and we'll
say we want to start this instance when
I start this instance let's look at the
status field the status field talks
about the state that the service is it
basically your service will be in four
major states i did caused processing
order if you stare service is in idle
are processing it is one of the ideal
States but if it is it starts paused or
edit then your service has some
troubleshooting to be done at CDC
instance can be an in an arrow straight
if the series instance is not running
because it could not be in the running
state after multiple retrace it can be
also in the poor state when the city's
instance is stunning but the processing
is suspended because of an error the
help file for the designer describes
more in detail about the statuses the
status message here is also associated
associated with detailed status message
which can describe the error at the CBC
instance is an error is in an error or
prostate it also lists the timestamp for
the UTC time when the CDC state of the
series instance was last raid from the
state table now we can see this services
in the processing status
let's look at the currently processing
fields the last transaction x times
shown here describes the local time
stamp at the sequel server of the last
transaction written to the change tables
the last change timestamp describes the
local time stamp of most recent change
seen by the Oracle CDC instance in the
source oracle database transaction logs
the transaction log head sequence number
of the head change number denotes the
most recent sequence change number that
was red from the article transit
action well as the log-in change number
in order for sequence change number in
which the article series instance will
reposition to in the event of a restart
or another type of failure active
transactions are the current number of
particles transactions that are being
processed by the article see this
instance and and are not yet committed
or rolled back violence the state's
transaction the note and current number
of source oracle transaction that our
state's to a table called CC XD pc
States transactions table this is an
intermediate table with the transactions
are recorded before being actually
committed to the destination sequence
table the counter section here described
the number of changes being great and
written by the CDC instance the other
tabs here will tell you about the
article that Pru the article source
database that you're using and the
tables will tell you which are the
tables you're capturing in this capture
instance now let's look at some of the
queries you can query from the MS xdcc
database
let's look at all the databases that
there for us XD pc as you can see it
records that there is a database called
ssis table which is corresponding to
this particular service we can also see
what all these services you can see the
service team the login that has been
used and which machine is actually been
hosting the service that is interesting
cable called the place table the trace
table records all the trace information
that the service runs on while you can
also see this trace information in a tab
called collect diagnostics when you
click on collect diagnostics it will
save all the diagnostic information from
the article environment as well as the
xdcc triste which you just saw into a
trace file let's try to save something
what nutrients file in the meantime to
just have fun with the service let's
insert a new row into the source
database and see how it how the service
reacts to it so a new role was created
for the service
let's see how the CDC service reacts to
it as you can see it has started reading
the number of changes change here being
a single change that was made by us
right now and it soon it will commit
back to the destination database
so finally let's conclude on what you
just learned in the demo video so you
just learned how to set up your CC
features for capturing changes from your
oracle database you learn how to create
a service as well as an instance you
also learn how to operate your CDC
instance as well as monetary
transactions collect diagnostics you
want to learn how to monitor their miss
xdcc please note that all the
functionalities that you are performing
on the UI can be performed through a
utility called xdcc sec . x c which is
in the same folder as your components
for more information look at the helm
fight for txt bc as we see . XE thank
you for your time and hope you have
enjoyed this video