Databases Reference
In-Depth Information
How It Works
You can use LogMiner to find SCNs associated with DML and DDL statements. LogMiner requires supplemental
logging to be enabled to display information about DML statements. Enable supplemental logging by issuing the
following SQL:
$ sqlplus / as sysdba
SQL> alter database add supplemental log data;
By default, Oracle is not enabled with supplemental logging. LogMiner requires that supplemental logging
be enabled prior to the log files' being created so it can extract SCNs associated with DML commands. Once
supplemental logging is enabled, you can use LogMiner to analyze and retrieve information such as an SCN
associated with a particular DML statement.
You can analyze redo logs based on a time range, schema, SCN range, and so on. See the Oracle Database
Utilities Guide for full details on how to use LogMiner. You can download all of Oracle's documentation from the
http://otn.oracle.com
website.
■
Consider using flashback drop, flashback table, flashback query, and/or restore table to restore and recover an
erroneously dropped table or deleted data. Chapter 13 describes these techniques in full detail.
Tip
Performing Change/SCN-Based Recovery
Problem
You want to perform an incomplete database recovery to a particular database SCN.
Solution
After establishing the SCN to which you want to restore, use the
until scn
clause to restore up to, but not including,
the SCN specified. The following example restores all transactions that have an SCN that is less than 1399500:
$ rman target /
RMAN> startup mount;
RMAN> restore database until scn 1399500;
RMAN> recover database until scn 1399500;
RMAN> alter database open resetlogs;
If everything went well, you should now see output similar to this:
Statement processed
You can also use
set until scn
within a
run{}
block to perform SCN-based incomplete database recovery
without having to repeat the SCN number for each command:
$ rman target /
RMAN> startup mount;
RMAN> run{
set until scn 1399500;
