[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Data level auditing for Oracle

Posted on 2014-07-13
6
Medium Priority
?
576 Views
Last Modified: 2014-11-27
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.
0
Comment
Question by:marrowyung
  • 4
  • 2
6 Comments
 
LVL 1

Author Comment

by:marrowyung
ID: 40193778
I can restore any data in any data cell from the CDC record, right? What is the tools for that?
0
 
LVL 35

Accepted Solution

by:
Mark Geerlings earned 2000 total points
ID: 40194342
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40196234
"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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:marrowyung
ID: 40196402
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
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 40197992
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
 
LVL 1

Author Comment

by:marrowyung
ID: 40198521
"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

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

834 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