Data level auditing for Oracle

Dear all,

Right now doing a project to capture the following on Oralce on data level:

1)      What is the type of action :BEFORE / AFTER
2)      What is the action: BELETE, INSERT/UPDATE
3)      Who did that.
4)      When he/she did that.
5)      What is the data changed.
6)       Is the column has been changed (column type, column length increase/decreased, column added/dropped)
7)      Who change the column from 6) ?

someone tell me that log miner can check all them out if we are on archive log mode, right?

If 6 happened, the archive log and log miner still can keep tracking of what was happened, right?

And can we restore any data using this information from log miner so that accidently deleted data can be restore individually?

What is the limit on the archive log mode?

when I tried to verify if archive log mode has bene enabled. I need to do:

archive log list

Open in new window


from SQL developer, I am using 4.0.2.xxx x64 version and the return is:

Database log mode                       No Archive Mode
Automatic archival                      Disabled
Archive destination                     USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence              49
Current log sequence                    50

Open in new window


So it is not been turned on, right?

To enable it, the oracle server needs to restart, right? What is the command to turn the archive log mode on?

What is the command from SQL developer can I restart that server?

someone talk about CDC in 11g/10g:

http://docs.oracle.com/cd/B28359_01/server.111/b28313/cdc.htm#i1025409, which expect to contain all information above, am I right?

anyone can suggest what is the limit on CDC mode?

I want to the requirement without any programming preferrably.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
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.

marrowyungSenior Technical architecture (Data)Author Commented:
I can restore any data in any data cell from the CDC record, right? What is the tools for that?
0
Mark GeerlingsDatabase AdministratorCommented:
It looks like your database is not in archivelog mode.  Yes, you will have to stop and restart the database to turn archivelog mode on.  Be aware that you will then need to manage the archived redo logs to avoid filling up a disk and bringing your database to a stop when that happens.  Usually RMAN is used to back up the archived redo logs to a different disk location, then automatically delete them from where the database writes them.

"someone tell me that log miner can check all them out if we are on archive log mode, right?"
No.  LogMIner can answer your questions 2,3,4, and 5.

"If 6 happened, the archive log and log miner still can keep tracking of what was happened, right?"
Yes, and no.  Yes, LogMiner will continue to track want happens after someone changes a table or a column.  But no, you will not be able to use LogMiner to see or restore any changes made on that table or column before the table ot column was modified.

"And can we restore any data using this information from log miner so that accidently deleted data can be restore individually?"
Sometimes.  As long as the tables and columns have not been modified, and as long as you have the archived redo logs available, then you can use LogMiner to help see and/or recover data that was changed or deleted in the past.

"What is the limit on the archive log mode?"
The maximim limit is the amount of disk space that have for keeping archived redo log files on disk.  In most organizations that is somewhere between a day and a week, sometimes as long as a month, but rarely longer than that.

The other limit on LogMiner is the fact that it is not designed or intended to make it easy or efficient to work with this data to find particular records.  This data is organized by timestamp, not by table,  and certianly not by the value in a particular column or record.

"I can restore any data in any data cell from the CDC record, right?"
Sometimes, yes.

"What is the tools for that?"
Log Miner, Flashback query and/or Flashback database can all be used to help with this.

"I want .. the requirement [for CDC] without any programming preferrably."
That is not possible.
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
marrowyungSenior Technical architecture (Data)Author Commented:
"Be aware that you will then need to manage the archived redo logs to avoid filling up a disk and bringing your database to a stop when that happens.  Usually RMAN is used to back up the archived redo logs to a different disk location, then automatically delete them from where the database writes them."

yes! I agree ! any good LInk you can suggset for the RMAN operation for that?

"No.  LogMIner can answer your questions 2,3,4, and 5.
"
excellent, so schema change can't be record, what can ?

"But no, you will not be able to use LogMiner to see or restore any changes made on that table or column before the table ot column was modified."

tks, this means log miner is very weak on this ! so not even data of a table which has no schema change ?

"But no, you will not be able to use LogMiner to see or restore any changes"

I don't understand, I can't even see it? how about we read ourselves and restore it by hand ?

"
 The other limit on LogMiner is the fact that it is not designed or intended to make it easy or efficient to work with this data to find particular records.  This data is organized by timestamp, not by table,  and certianly not by the value in a particular column or record."

is there any other free tools you can suggest for my requirement ?

"The maximim limit is the amount of disk space that have for keeping archived redo log files on disk. "

but we need to configure that, right? or it just expands as it can find space on that disk ?

"The other limit on LogMiner is the fact that it is not designed or intended to make it easy or efficient to work with this data to find particular records.  This data is organized by timestamp, not by table,  and certianly not by the value in a particular column or record."

yeah, not very good use. but anyway to export it to other tools so that other tools do this for us?

"Log Miner, Flashback query and/or Flashback database can all be used to help with this."

log miner can restore data from CDC ? by restore the whole row for example ?

""I want .. the requirement [for CDC] without any programming preferrably."
 That is not possible. "

so you mean only table trigger can do it? as I done the similar thing in MysQL and it is working only by doing table trigger!

i can then only see the change in row by row format, I can simply copy the value of the whole row.

Again, please suggest any free tools for that.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

marrowyungSenior Technical architecture (Data)Author Commented:
I read this :

http://www.dba-oracle.com/t_streams_change_data_capture.htm

Oracle 10g introduced Asynchronous Change Data Capture, which instead of using triggers uses the database log files to capture changes and apply them to collection tables. Asynchronous Change Data Capture therefore doesn't require changes to the table structure and doesn't impact on database performance.

any tools we need or this is a build in tools ?

any suggest reading to kick start that?

the article also said:

Asynchronous change data capture:

"Asynchronous change data capture is now adopted, in which change data is extracted from the redo logs without any negative performance implication on the source database. Furthermore, asynchronous change data capture can be described as a lightweight technology targeted towards change extraction and propagation in a data warehousing system and in which changes to the source tables are viewed as relational data for onward consumption by subscribers.

There is therefore no gain saying that asynchronous change data capture has greatly enhanced parallel log file processing and data transformation.
"

so I think this is good too, any comment ?

but one thing, one test server we are using is the express edition :

Oracle Database 11g Express Edition Release 11.2.0.2.0

so the archive log + data miner can still be use on that express edition?  CDC can't anyway ?

but table trigger programming can be done on express edition, right?

so can:
1) 10g's Asynchronous CDC can do my requirement and let me restore any data cell as we need?
2) 11g's Synchronous CDC can do my requirement and let me restore any data cell as we need?
3) 11g also use Asynchronous ? I want to make sure that I can use the same method across all platform OR I have to explain why !
4) When restoring data by Async and Sync CDC, any what tools is needed?
5) Can the Oracle 10g/11g's CDC can let us make decision on which DML (select/insert/update) and DDL operation to log?
0
Mark GeerlingsDatabase AdministratorCommented:
I haven't used or researched that tool: "Asynchronous Change Data Capture".  So, I don't know exactly how it works, or if it works well or not.  Some other Oracle features and/or utilities that I have tried did not always work so well in the first version they were available for.  LogMiner and DataPump export are two examples of this.  They both had significant problems when they were new.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Some other Oracle features and/or utilities that I have tried did not always work so well in the first version they were available for.

very like MS SQL product!
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.