marrowyung
asked on
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:
from SQL developer, I am using 4.0.2.xxx x64 version and the return is:
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.
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 listfrom 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 50So 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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"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.
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.
ASKER
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?
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?
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.
ASKER
"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!
very like MS SQL product!
ASKER