I'm using Oracle 11g database in archivelog mode and auditing is not enabled.
Two days before, a few lines from a child table in my database got deleted. As the schema password was shared, I'm unable to track how it happened.I checked the trace files as well as queried tables v$log_history, V$SQL, DBA_HIST_SQLTEXT to get the history, but couldn't find any relevant information. Also, I reviewed the data by restoring the backup on this same date. But, since this data was created and got deleted on the same date, I'm unable to track this data even from the backup.
As I'm a newbie, I don't know how to tackle this issue. Are there any other methods/queries by which I can check if any delete operation had been performed in my database?
Way to track data deleted from database
auditoracleoracle-11g
Related Solutions
Don't bother tracking audit data on a per-field basis. It's much easier to have an audit table with the same structure as the main table and timestamp and user information tracked on the table.
This architecture has a few advantages:
- You can easily create a view across the main table and audit table to show a complete history includint current versions.
- It is relatively simple to implement the audit triggers - in fact if you hae a large number of tables you can write a utility to generate them from the system data dictionary.
- The audit tables can be put on a separate disk to reduce the I/O load on the disk with the main tables.
- It's very easy to reconstruct an as-at version of the record, rather than having to apply a list of field-level changes in reverse.
Audit information should include at least: user who created/changed the record, date/time of the creation/change, nature of change (insert, update, delete). You may want to use logical deletion (i.e. a 'Deleted' flag) if you have the option of doing so. Otherwise you need to capture the user and date/time from the session and put these on the deletion record, probably along with the last state of the record. If this is not available from the connection (often the case on N-tier apps where the user is not being impersonated at the database level) then you need to find some other way to get it.
Currently we are using Oracle 11g database in noarchivelog mode. As far as I know, running a database in archivelog mode, backup is taken at short intervals, which I don't prefer. I need the backup to be taken only once in a day, that too without shutting down my database. Is that possible.
First of all i would like to say that without shutting down the database. There is possible to take backup through RMAN.
What is RMAN?
Recovery Manager (RMAN) is an Oracle Database client that performs backup and recovery tasks on your databases and automates administration of your backup strategies. It greatly simplifies backing up, restoring, and recovering database files.
How Many components Required for RMAN Environment?
The RMAN environment consists of the utilities and databases that play a role in backing up your data. At a minimum, the environment for RMAN must include the following components:
A target database
The RMAN client
A flash recovery area
A media manager
A recovery catalog
When you are taking the database through RMAN without shutting down your database that process is called 'Hot Backup'.
A hot backup can be done even as users access the database, but if the data is altered during the backup, it can be inconsistent. A hot backup can also impact database performance because it uses compute resources. Hot backup servers generally receive ongoing updates from the production server and are ready to take over as soon as a failover event takes down the production server.
In a warm backup, the server is powered on, but not performing any work, or it is turned on from time to time to get updates from the server being backed up. Warm backups are usually used for mirroring or replication.
And when you taking the database backup with shut down the database through RMAN that process is called 'Cold Backup'.
A cold backup, also called an offline backup, is a database backup during which the database is offline and not accessible to update. This is the safest way to back up because it avoids the risk of copying data that may be in the process of being updated. However, a cold backup involves downtime because users cannot access the database during the backup.
A cold backup ensures a consistent backup, but cannot but used for any systems that require continuous, 24/7 operation. No users should be logged in and no activity should take place to ensure that files are not changed in any way during the backup. If you back up data to an off-site facility, cold backups can be performed from a copy of the data. Data files do not change during a cold backup process, ensuring the database is in a consistent state when it returns to normal operation.
Which is better option, database to be run in archive mode or noarchive mode?
Preparing to Use the Oracle Suggested Backup Strategy
that is ARCHIVELOG mode.
Backing Up an ARCHIVELOG Database
If you run your database in ARCHIVELOG mode, then the archiver archives groups of online redo log files. Therefore, the archived redo log coupled with the online redo log and datafile backups can protect the database from a disk failure, providing for complete recovery from a disk failure to the instant that the failure occurred (or, to the desired noncurrent time).
Common backup strategies for a database operating in ARCHIVELOG mode
Back up the entire database after you create it. This initial whole database backup is the foundation of your backups because it provides backups of all datafiles and the control file of the associated database.
Note: When you perform this initial whole database backup, make sure that the database is in ARCHIVELOG mode first. Otherwise, the backup control files will contain the NOARCHIVELOG mode setting.
Make backups of tablespaces when the database is open or closed to keep the database backups up-to-date. So long as you have the necessary archived logs to recover the backup, you never have to shut down the database to make a backup.
In particular, back up the datafiles of extensively used tablespaces frequently to reduce database recovery time. If a more recent datafile backup restores a damaged datafile, then you need to apply less redo (or incremental backups) to the restored datafile to roll it forward to the time of the failure.
You can also use a datafile copy taken while the database is open and the tablespace is online to restore datafiles. You must apply the appropriate redo log files to these restored datafiles to make the data consistent and bring it forward to the specified point in time.
Back up the control file every time you make a structural change to the database. If you run in ARCHIVELOG mode and the database is open, then use either RMAN or the SQL statement ALTER DATABASE BACKUP CONTROLFILE. Back up archived logs frequently. It is strongly recommended that you keep at least two copies of archived logs: one on disk and another on off-line storage (tape, optical disks, and so forth). Keep the logs on disk as long as possible but back them up as soon as possible.
Backing Up a NOARCHIVELOG Database
If you run the database in NOARCHIVELOG mode, Oracle does not archive filled groups of online redo log files. Therefore, the only protection against a disk failure is the most recent whole backup of the database.
Following are common backup strategies for a database operating in NOARCHIVELOG mode:
Make whole database backups regularly, according to the amount of work that you can afford to lose. For example, if you can afford to lose the amount of work accomplished in one week, then make a consistent whole database backup once every week. If you can afford to lose only a day's work, then make a consistent whole database backup every day. For large databases with a high amount of activity, you usually cannot afford to lose work. In this case, you should operate the database in ARCHIVELOG mode.
Whenever you alter the physical structure of a database operating in NOARCHIVELOG mode, immediately take a consistent whole database backup. A whole database backup fully reflects the new structure of the database.
Also, if I'm going for noarchive mode, do I need to delete the backups manually or, does it happen automatically?
I would like to say that first you check the retention policy of the RMAN Backup.
How you shall check the retention policy?
You can see the retention policy through this RMAN command
show all;
For Example, so many options comes like that
RMAN configuration parameters for database with db_unique_name PROD1 are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE SBT_TAPE TO '%F'; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE 'SBT_TAPE' PARALLELISM 2 BACKUP TYPE TO COMPRESSED BACKUPSET;
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE SBT_TAPE TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE 'SBT_TAPE' PARMS 'ENV=(OB_DEVICE=tape1)';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/disk1/oracle/dbs/snapcf_ev.f'; # default
Or you can also see any of the above options through 'Show' command.
suppose that you want to see your retention policy of database through RMAN command then you just type
SHOW RETENTION POLICY;
it will show your retention policy of database, such as like that
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
As above shows your retention policy to recovery windows is 3 days. So, here you backup will only remain upto 3 days. You can also increase the Retention day value through RMAN command more than 3 days.
what is retention Policy?
A user-defined policy for determining how long backups and archived logs need to be retained for media recovery. You can define a retention policy in terms of backup redundancy or a recovery window. RMAN retains the datafile backups required to satisfy the current retention policy, and any archived redo logs required for complete recovery of those datafile backups.
For Your Further Ref : Getting Started with RMAN , Configuring the RMAN Environment & Here
Related Question
- Recovery using Archive redo log files
- Oracle 11g – Recovering Database After Losing Data File
- Restore/import tables from unsuccessful database dump file
- SYSTEM tablespace getting filled up
- Oracle – Archivelog File Deleted, Resulting in ORA-01113 Error
- Oracle 11g – How to Recover Database Without New Data
Best Answer
Provided Minimal Supplemental Logging was turned on before the incident occurred that you want to research (it is off by default since there is a minor performance hit by having it on) which you can check via:
select supplemental_log_data_min from v$database;
, then Logminer is the tool for this.Start logminer:
Load the archive logs that you want to view into Logminer:
The above assumes that the ArchiveLogs are still on disk. If you've backed them up, you'll have to unpack them from the backup and register them with the database first.
Query the Redo
End your logminer session
You can read about Logminer from the docs.
If supplemental Logging is turned off, you may still be able to get the information you need by determining when the incident occurred using flashback queries to narrow down the exact time of the incident (provided you have a large enough Undo and retention policies are adequate):
Then query the audit trail to find out when the shared schema user logged on around the above timeframe:
The above assumes that you are logging audit information to the DB (
show parameter audit_trail;
). You can read about auditing from the docs.