How to recover from a DROP or TRUNCATE table by using RMAN.
-----------------------------------------------------------
There are three options available:
1. Restore and recover the primary database to a point in time before the drop.
This is an extreme measure for one table as the entire database goes back in
time.
2. Restore and recover the tablespace to a point in time before the drop.
This is a better option, but again, it takes the entire tablespace back in
time.
3. Restore and recover a subset of the database as a DUMMY database to export
the table data and import it into the primary database. This is the best
option
as only the dropped table goes back in time to before the drop.
For the first, see information on performing incomplete recovery on a database.
The second and third options are virtually the same, it depends if the entire
tablespace data should be taken back in time, or just recovering the one table.
The tablespace point in time recovery (TSPITR) may be useful if there are
dependencies between the dropped/truncated table and other tables in the
database. For the second option, see RMAN documentation on TSPITR and/or
Note 180436.1
RMAN Tablespace Point in Time Recovery Example. Both procedures
for the second and third options are very much the same. The differences are
that the TABLE PITR has to be exported/imported manually while the TABLESPACE
PITR is fully automated by RMAN.
This article will deal with the third option.
General overview of procedure to recover from a DROP or TRUNCATE table by using
RMAN.
--------------------------------------------------------------------------------
----
To recover from a dropped or truncated table, a dummy database (copy of primary)
will be restored and recovered to point in time so the table can be exported.
Once the table export is complete, the table can be imported into the primary
database. This dummy database can be a subset of the primary database.
However,
the 'dummy' database must include the SYSTEM, UNDO (or ROLLBACK), and the
tablespace(s) where the dropped/truncated table resides.
The simpliest method to create this 'dummy' database is to use the RMAN
duplicate command. See:
Note 228257.1
RMAN Duplicate Database in Oracle9i
Note 73912.1
RMAN Creating a Duplicate Database -- Oracle8i
The difference between the two versions is that Oracle9i duplicate command
allows for a 'SKIP TABLESPACE' option. Thus Oracle9i allows for a duplication
of a subset of the database.
In Oracle8i, you cannot 'skip' tablespaces when using duplicate, so you must
duplicate the entire database. If this is not a desired option, or you must
restore the original database and thus cannot use the rman DUPLICATE.
NOTE: The remainder of this information is for users who cannot use the DUPLICATE command in Oracle8i.
I.e., you want to restore only a subset of the Oracle8i database.Requirements :
--------------
a) RMAN backup of the primary database should be available to the
host where it has to be restored on.
b) Use of an RMAN-catalog database
c) Auxiliary instance created and started in NOMOUNT
(See
Note 180436.1
step I. Create the auxiliary initSID.ora.)
Create this from the init.ora of the primary database and:
!!!!! IMPORTANT !!!!!!!!
If using the same host as the primary, be VERY careful as you do not want to
restore on top of existing files being used by the primary (production
database).
Doing so can corrupt and crash the production database!!!!!!
- be sure all paths for this AUX instance are different than primary.
- be sure CONTROL_FILES parameter has different location but more
importantly DIFFERENT NAME.
- add LOCK_NAME_SPACE to any value other than the primary database name.
- change/add SERVICE_NAME=AUX1.
- use the SAME DB_NAME as for the production database
- BE SURE you include the 'alter database rename file' command at the end
of the script. This changes the location and/or name of the online
redo log files.
d) Set ORACLE_HOME and ORACLE_SID set to the auxiliary instance
e.q set ORACLE_SID=AUX1
set ORACLE_HOME=<....>
!!!!! IMPORTANT !!!!!!!!
1: Restore and recover the tablespace
-------------------------------------
The restore and recovery is done by RMAN. Create a file
of the script below (e.q table_pitr.cmd) and execute the following
command :
$ rman cmdfile=table_pitr.cmd
NOTE: ORACLE_HOME and ORACLE_SID set to the auxiliary instance
NOTE: The Auxiliary instance IS the target to rman at this point.
The RMAN-script :
-----------------
connect catalog rman/rman@catalog
connect target /
run
{
allocate channel t1 type sbt_tape
parms='SBT_LIBRARY=/home/usupport/liblsm.so';
set until time "to_date( '09-10-2005 06:00', 'DD-MM-RRRR HH24:MI')";
restore controlfile;
sql "alter database mount clone database";
set newname for datafile 1 to '/fs01/oradata/tspitr/system01.dbf';
set newname for datafile 2 to '/fs01/oradata/tspitr/undotbs01.dbf';
set newname for datafile 4 to '/fs01/oradata/tspitr/tools01.dbf';
restore tablespace system, undotbs1, tools;
switch datafile all;
sql "alter database datafile 1,2,4 online";
recover database skip forever tablespace TEMP,INDX,USERS,OLTS_ATTRSTORE,
OLTS_CT_DN,OLTS_CT_CN, OLTS_CT_OBJCL,OLTS_CT_STORE,OLTS_DEFAULT,
OLTS_TEMP,OLTS_IND_ATTRSTORE,
OLTS_IND_CT_DN,OLTS_IND_CT_CN,OLTS_IND_CT_OBJCL,OLTS_IND_CT_STORE,
P1TS_ATTRSTORE,P1TS_IND_STORE;
sql "alter database rename file ''/fs01/oradata/primary/REDO01.LOG'' to
''/fs01/oradata/tspitr/REDO01.LOG''";
sql "alter database rename file ''/fs01/oradata/primary/REDO02.LOG'' to
''/fs01/oradata/tspitr/REDO02.LOG''";
sql "alter database rename file ''/fs01/oradata/primary/REDO03.LOG'' to
''/fs01/oradata/tspitr/REDO03.LOG''";
/* NOTE: Syntax within rman is two single quotes around each name, this may be
operating system specific. */
release channel t1;
}
Explanation :
-------------
- Tape channel allocated, but could also be a disk channel, depending
on where the backups are.
- SET UNTIL TIME
User specified time, just before the DROP/TRUNACTE table
- MOUNT CLONE DATABASE.
This forces all datafiles to be put OFFLINE. Just for safety reasons.
- SET NEWNAME
New path for the datafile to be restored. Keep in mind that this is
done on the auxiliary instance and should NOT interfere/overwrite the
prodution database.
- ALTER DATABASE RENAME FILE
This is required to change the location of the online log files. When the
'resetlogs' is issued, Oracle will create online logs based on
specification
in the controlfile. This command changes the location and/or name. If
this is being performed on the SAME server, not issuing a rename will
cause Oracle to reset the production online log files. This will corrupt
and crash the production database!!!!!!
- RESTORE TABLESPACE ...;
Restore the tablespaces which need to be recoverd.
This includes always the SYSTEM, rollback/undo tablespace and
the tablespace(s)where the dropped/truncated table resides.
The SYSTEM tablespace is always included as it containts most / all of
the objects owned by SYS and SYSTEM. Some other tablespaces might be
included as well when they contain objects owned by SYS and SYSTEM.
SQL> select distinct tablespace_name
from dba_segments where owner in ('SYS', 'SYSTEM');
- SWITCH DATAFILE ALL;
Make the changes in the pathname (set by SET NEWNAME) active in the
controlfile.
- ALTER DATABASE DATAFILE ... ONLINE
Online the datafiles which are restored and have to be recovered.
- RECOVER DATABASE SKIP FOREVER TABLESPACE ......;
You need to specify the complete list of tablespaces which will not be
recovered. Else the recovery fails, that it cannot dentify/file the
tablespace datafile. The SKIP FOREVER clause causes RMAN to take the
datafiles offline using the DROP option. Only use skip forever when the
specified tablespaces will be dropped after opening the database. I.e.,
all tablespaces except the one which contains your data.
The recovery of RMAN, checks the datafileheaders, to get a starting point
for the recovery.
2: Open auxiliary database with RESETLOGS
-----------------------------------------
$ sqlplus /
SQL> alter database open resetlogs;
This step should ALWAYS be executed outside RMAN via SQL*Plus. If the open
is executed in RMAN it may effect the primary database's entry in the RMAN
catalog. Backups will fail with messages like:
RMAN-20011 "target database incarnation is not current in recovery catalog"
3: Export the table
-------------------
The database is recovered and open, so it can be used by export.
Example:
$ exp userid=system/<password> file=table.dmp
tables=(<owner>.<tablename>, ...) rows=Y
4: Import the export-dump
-------------------------
Import the data of the dropped table back into the primary/production
database.
Example:
$ imp userid=system/<password> file=table.dmp ignore=Y
5: Remove this AUX/DUMMY database
---------------------------------
Shutdown and remove all files associated with this database. It has
satisfied
your purpose. I.e., to give you an export of this table.
RELATED DOCUMENTS
-----------------
Note 62385.1
: Oracle7 Recovery Scenarios and Equivalent RMAN Techniques
Note 180436.1
: RMAN Tablespace Point in Time Recovery Windows Example
Note 109979.1
: RMAN Tablespace Point In Time Recovery (TSPITR) Procedure on
Unix.
Note 228257.1
: RMAN Duplicate Database in Oracle9i
Note 73912.1
: RMAN Creating a Duplicate Database -- Oracle8i
.
