Solved

Oracle Change Data Capture Asynchronous Hotlog

Posted on 2014-09-12
23
444 Views
Last Modified: 2014-09-29
right now I am reading this :

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

any concept wise of Oracle Change Data Capture Asynchronous Hotlog  to let me know why teh script build like this?
0
Comment
Question by:marrowyung
  • 13
  • 8
  • 2
23 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40326195
I'm not a CDC user but from reading about it I understand the concepts.

I'm only posting here because of a request from one of the Moderators.

What is your question about it?  What you are after isn't clear from your original post.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40327271
slightwv,

yes, they found you . you must be a very expertist on Oracle.

I want to deploy Oracle Change Data Capture Asynchronous in Hotlog mode as autolog mode involve Data Guard and we need to pay for that.

I found this from the web: http://psoug.org/reference/cdc_demo2.html it seems step by step to it but I don't understand what each step means before I go further.

any information can  I do it.

in Summary, I get a project that need to do data level auditing by no tools, someone suggseted archivelog mode + log miner but this can't restore any data if lost and it also depends on the retention period of the archivelog mode, if someone changed it to only keep 3 months, I can't change it to keep 7 years for SOX compliance.

on MySQL I do CDC throught programming which take care the
1) BEFORE DELETE/AFTER INSERT/AFTER UPDATE
2) if any of the field prolong.
3) if any of the field shortened.
4) if field change type.
5) if field removed/added.
6) record who change it, what he/she changed, when she/he do it.

so someone cna only suggest archivelog mode + log miner for that one can't take care schema change and can't record it.

someone suggest the Oracle Change Data Capture as this is exactly what I want and MS SQL also has this!

and I found Oracle Change Data Capture Asynchronous in Hotlog mode, and I also found the link in my post.

so I decided to go this way and TRYING to setup a POC for that.

Can you help to guide me?
0
 
LVL 28

Assisted Solution

by:Ryan McCauley
Ryan McCauley earned 200 total points
ID: 40329178
I'm also not entirely clear what your goal is - are you trying to replicate to a second site without using Dataguard? Or are you trying to implement CDC specifically, and if so, can you share the reason you've chosen that technology? I want to make sure we're getting you where you're going and not leading you down a path of implementing the wrong technology for your needs.

I'm not a licensing expect, but as I understand it, you don't need to pay for Passive Dataguard as long as you've got Enterprise Edition licenses - you can use it to replicate to a second database for free within your Oracle license. What you're paying for when you add Dataguard as a feature is actually the "Active" version - this allows you to open the second database for read-only transactions (like backups and reporting), so you can offload some of your read traffic. If you're replicating to a second server for DR purposes, I don't believe you need a Dataguard license to do that. However, Standard (and Standard One) licenses don't include any form of Dataguard - see page 1-2 in this PDF for some more details on specifics:

http://docs.oracle.com/cd/B28359_01/license.111/b28287.pdf
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40329657
"I'm also not entirely clear what your goal is - are you trying to replicate to a second site without using Dataguard?"

no! setup the data level auditing feature:
1) BEFORE DELETE/AFTER INSERT/AFTER UPDATE
 2) if any of the field prolong.
 3) if any of the field shortened.
 4) if field change type.
 5) if field removed/added.
 6) record who change it, what he/she changed, when she/he do it.

ONLY data level but not action/query level.

"Or are you trying to implement CDC specifically, and if so, can you share the reason you've chosen that technology?"

yes, CDC only as CDC is design for that and the performance is not a big issue as the async hotlog mode only read the redo log, but not the table itself! if we use table trigger, it can slow down everything and we need to do trigger on ALL table by managment's instruction.

when I am doing MySQL CDC, I can ONLY do table trigger ! nothing else can help. it slow down the operation by 6 time for one table only ! and we need to do for 9xx tables !


"you don't need to pay for Passive Dataguard as long as you've got Enterprise Edition licenses - you can use it to replicate to a second database for free within your Oracle license"

tks and I just know the passice don't need license as we consider active one because of the same reason ! Read only copies to offload production. some one will buy it!

it seems now only CD can help as it is on data level, not query/action level (what query has been submit and whne is it and who did it !), we only want data level so we can see what has been changed.
0
 
LVL 28

Expert Comment

by:Ryan McCauley
ID: 40330711
Is this "walk-through" (I use the term loosely as it's a demo, but doesn't provide much context for the commands) what you're looking for?

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

There are some comments - not many, but it gets the job done and seems to accomplish exactly what you're looking for.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 300 total points
ID: 40331041
Ryan,
Isn't that the same link marrowyung provided in the original question and again in another post?

marrowyung,
I understand you want to track/audit ALL changes to a database.

What is your question about the hotlog link you posted?

Have you looked at the Change Management Pack for OEM?
http://docs.oracle.com/cd/E11882_01/license.112/e47877/options.htm#DBLIC160
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40331997
slightwv,
What is your question about the hotlog link you posted?

I don't konw what is that and I need some guideline on , for example:

1) waht is the command for ?
2) what should i expect to see by each of the command

but the login is, I need some theory behind to justify why the link show me that command and why should I think all command are correct so I can simply apply the script!

"Have you looked at the Change Management Pack for OEM?
http://docs.oracle.com/cd/E11882_01/license.112/e47877/options.htm#DBLIC160 "

that one only focus on schema change, but not data change.

From my CDC programming for MysQL myself:
1) When field prolonged, it will give me a SQL statment to alter the struture of the audit target table.
2) when field shortened, my program with change the name of hte existing target audit table and recreate the target table as one of the field shortened. has to take care of the data lost by field change.
3) when field added or removed, basically similiar operation of 1 and 2) will do and a script will generate to add new filed or recreate table that have that field removed.

so I hope these also take care by the  "Oracle Change Data Capture Asynchronous Hotlog "? am I right?


ryanmccauley,

so you agree that the link give me what I am looking for ? but I need some guide why the script construct in this way.

instead of I don't know what it is but just apply approach.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40332003
e.g. by that link, what is the meaning of :

col log_min format a7
 col log_pk format a6
 col log_pk format a6
 col log_ui format a6
 col log_fk format a6
 col log_all format a7
 col force_log format a9

Open in new window


I just ond't konw what it is, this is my concern.

and
Setup As SYS - Create Streams Administrators 
conn / as sysdba

 SELECT *
 FROM dba_streams_administrator;

 CREATE TABLESPACE cdc_tbsp
 datafile 'c: emp\cdctbsp01.dbf' SIZE 50M
 AUTOEXTEND OFF
 BLOCKSIZE 8192
 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K;

 CREATE USER cdcadmin
 IDENTIFIED BY cdcadmin
 DEFAULT TABLESPACE cdc_tbsp
 TEMPORARY TABLESPACE temp
 QUOTA UNLIMITED ON cdc_tbsp;

-- system privs 
 GRANT create session TO cdcadmin;
 GRANT create table TO cdcadmin;
 GRANT create sequence TO cdcadmin;
 GRANT create procedure TO cdcadmin;
 GRANT create any job TO cdcadmin 

Open in new window


why we need CREATE USER cdcadmin, sth like that.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40332005
I even can't find the author and ask ! did anyone see who write that?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40332398
As far as me validating the script in the link you posted, I cannot.  I've never set up CDC let alone async hotlogging.  So, I don't know what outputs you are expected to see.

I would set up a small test database just for testing this process.  If something goes wrong, just drop and rebuild the test database and try again.

>>that one only focus on schema change, but not data change.

I do not see anywhere in your requirements when you want to track data changes.  I only see you wanting schema changes.

>>why should I think all command are correct so I can simply apply the script!

You are correct to be cautious when locating scripts on the web.  That said, I tend to trust psoug.org.  They are a pretty decent Oracle Users Group.  I use their site for reference/sample code a lot.

That said:  Before running any command you are unfamiliar with, go to the online docs to find out what it does.

>>e.g. by that link, what is the meaning of :

That is simple column formatting for sqlplus.  The online docs have all the information you need:
COL is a shortcut for the COLUMN command.

http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_six.htm#i1081036

>>why we need CREATE USER cdcadmin, sth like that.

From what I read about async hotlog CDC is that is sits on top of Oracle Streams.  If you check out the Streams documentation it is recommended it has its own tablespace and schema owner to do its magic.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40335978
slightwv,

"As far as me validating the script in the link you posted, I cannot.  I've never set up CDC let alone async hotlogging.  So, I don't know what outputs you are expected to see."

yeah, I knew hard to find one person done that before!

but one thing, what data audit solution you use for 10g and 11g enterprise ? I am not talking about action level.

"I would set up a small test database just for testing this process.  If something goes wrong, just drop and rebuild the test database and try again.
"

already got one but by that link, how can I start it with ??? I am new to oracle ! but I need to start this project ASAP.

"I do not see anywhere in your requirements when you want to track data changes.  I only see you wanting schema changes."

Change Data Capture is all about data level change as previosuly in the database histroy everything like that has to be done on table trigger level, but it slow everything down! so Change Data Capture invented for this purpose.

my topic about change of field just say when something change on the schema side, the CDC has to keep going ! it can't stop and the change data audit feature should take care of this kind of schema change and make sure that CDC keep going and audit even the new column's data.

this is the most time consuming part.

"You are correct to be cautious when locating scripts on the web.  That said, I tend to trust psoug.org.  They are a pretty decent Oracle Users Group.  I use their site for reference/sample code a lot."

very good !! tks !! so I prefer execution command one by one  by that link and see if there are error message and ask you all by another question

"From what I read about async hotlog CDC is that is sits on top of Oracle Streams"

no, distrbuted hotlog mode and autolog mode based on this, which is part of data guard, right? that's why I don't prefer that.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:marrowyung
ID: 40336208
by this:

http://docs.oracle.com/cd/E11882_01/server.112/e25554/cdc.htm#i1027067

HotLog

Asynchronous HotLog and Distributed HotLog read the source database online redo log files whenever possible and the archived redo log files otherwise.

•AutoLog

Asynchronous AutoLog Change Data Capture reads redo log files that have been copied from the source database to the staging database by redo transport services.

When using the AutoLog online option, the destination attribute must be LGWR ASYNC. Redo transport services copies redo data from the online redo log at the source database to the standby redo log at the staging database. Change Data Capture obtains the change data after the source database transaction commits.

When using the AutoLog archive option, the destination attribute can be ARCH or LGWR ASYNC. In ARCH mode, redo transport services copies archived redo log files to the staging database after a log switch occurs on the source database. In LGWR mode, redo transport services copies redo data to the staging database while it is being written to the online redo log file on the source database.

Open in new window


so hotlog mode only read redo log and auto log require more.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40336560
>>but one thing, what data audit solution you use for 10g and 11g enterprise ? I am not talking about action level.

I don't have the requirement to track all data changes so I don't use anything.

>>already got one but by that link, how can I start it with ??? I am new to oracle ! but I need to start this project ASAP.

Nothing like starting off with a pretty advanced area!  The script in the first link you posted seems to be a step by step example.  I would just use it.

>>no, distrbuted hotlog mode and autolog mode based on this, which is part of data guard, right? that's why I don't prefer that.

The link from psoug has the page titled: Streams Change Data Capture.  A lot of the commands in the example are all Streams commands.  I use Streams Replication and the samples in the link seem very familiar.

I know there is a part of Data Guard that is also build on top of Streams.  Going from memory, I think it is called 'Active Data Guard'.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40338429
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40338933
There is no need to ask Experts to look at new questions.  Most of us receive emails when new questions are posted in our areas of interest.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40340825
you know from time to time I need rise attention on this kind of advanced topic ! just because....

"Nothing like starting off with a pretty advanced area!  The script in the first link you posted seems to be a step by step example.  I would just use it"

yeah, if finally no more input then I have no choice on it.

"The link from psoug has the page titled: Streams Change Data Capture.  A lot of the commands in the example are all Streams commands.  I use Streams Replication and the samples in the link seem very familiar."

what is the meaning of stream ? only data guard and repliatin use stream?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40341413
>>what is the meaning of stream ? only data guard and repliatin use stream?

Many things can use streams.  Anything that needs to enqueue and dequeue messages across systems can take advantage of it.  Oracle just leverages their own technology in replication and active data guard.

As far as a meaning:  I need something at pointA to make it to pointB.  So, processA enqueues a message, sends it over to processB that dequeues it and takes the appropriate action.

As far as streams replication, that is a Logical Change Record (LCR).
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40343433
" Oracle just leverages their own technology in replication and active data guard."

so stream is the building blocks of replication and active data guard ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40343751
Not ALL replication solutions are built on Streams.

I cannot find this stated anywhere (and I didn't look all that hard) but I was told by an Oracle employee once that Active Data Guard was basically Streams Replication.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40345619
"Active Data Guard was basically Streams Replication. "


that's why I prefer hotlog mode, which don't use streams at all.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40345668
but the "redo transport services" is just about stream replication ?

Autolog mode use that.

but I also see this :

"For HotLog and AutoLog modes, each change set contains a Streams capture process, queue, queue table and apply process."
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40345855
>>that's why I prefer hotlog mode, which don't use streams at all.
>>"For HotLog and AutoLog modes, each change set contains a Streams capture process, queue, queue table and apply process."

That is how I read it.  It looks like CDC uses pieces of streams.
0
 
LVL 1

Author Comment

by:marrowyung
ID: 40349471
ok!
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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 how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now