Partial incomplete recovery 9i

This works on 10g as well, but it should not be necessary if Flashback is in use.

This is the scenario
====================

Single database (doesn't matter if it's RAC or single instance). Multiple applications on the same database or at least multiple modules of the same application. Due to user error and or application logic error, some data got corrupted but nobody noticed until another application or module committed a lot of changes, so a restore of the whole database would mean loosing data and that is not the best scenario.

Database backup is as follows:

1. Daily full hot backup including archivelogs
2. Hourly archive backup.

Alternatives
============

After analyzing the situation for a while, I found two alternatives. 1) exporting the modified data that needs to be saved, restoring the database to a previous Point in Time (incomplete recovery) and then importing the appropriate data. This had the downside of keeping both application unavailable during export, restore and recover and the second application during import.

The actual solution I finally implemented was duplicating the database to a previous point in time and (before corruption), exporting the tables before corruption. Truncating corrupted production tables, importing them. As a side note, if you are performing this on 10g, you can use datapump and skip a few steps, however as this happened on a 9i database I will show the steps I took in this particular case.

Actual Solution
===============

The main steps involved in the solution are the following:

1. Preparing an auxiliary database for duplication
2. Connecting with RMAN to target database, auxiliary database and catalog (optional, recomended)
3. Duplicating the database to a previous Point in Time
4. Opening the duplicate database
5. Variable steps (export/import)

Breakdown of solution
=====================
1. This step differs a little from unix/linux to windows, I'll only mention the unix version here (it's the same on linux) and probably will make another entry for manual database/instance creation on Windows (I currently don't have a win2k3 environment to toy arround).

We can break down this step in a few actions:
a. create needed files and directories
We will basically need a parameter file (pfile or spfile) a passwordfile and the directory structure for dump destinations and datafiles.

• First, we create the passwordfile with orapwd

orapwd file=$ORACLE_HOME/dbs/orapwdup password=manager entries=5

• Then, we create the pfile and add the following

For the sake of simplicity we will create the pfile in $ORACLE_HOME/dbs/initdup.ora
From the 9.2 database administration guide, here is a sample init file, modify it as necessary.

# Cache and I/O
DB_BLOCK_SIZE=4096
DB_CACHE_SIZE=20971520

# Cursors and Library Cache
CURSOR_SHARING=SIMILAR
OPEN_CURSORS=300

# Diagnostics and Statistics
BACKGROUND_DUMP_DEST=/vobs/oracle/admin/mynewdb/bdump
CORE_DUMP_DEST=/vobs/oracle/admin/mynewdb/cdump
TIMED_STATISTICS=TRUE
USER_DUMP_DEST=/vobs/oracle/admin/mynewdb/udump

# Control File Configuration
CONTROL_FILES=("/recovery_path/control01.ctl",
"/recovery_path/control02.ctl",
"/recovery_path/control03.ctl")

# Archive
LOG_ARCHIVE_DEST_1='LOCATION=/vobs/oracle/oradata/mynewdb/archive'
LOG_ARCHIVE_FORMAT=%t_%s.dbf
LOG_ARCHIVE_START=TRUE

# Shared Server
# Uncomment and use first DISPATCHES parameter below when your listener is
# configured for SSL
# (listener.ora and sqlnet.ora)
# DISPATCHERS = "(PROTOCOL=TCPS)(SER=MODOSE)",
# "(PROTOCOL=TCPS)(PRE=oracle.aurora.server.SGiopServer)"
DISPATCHERS="(PROTOCOL=TCP)(SER=MODOSE)",
"(PROTOCOL=TCP)(PRE=oracle.aurora.server.SGiopServer)",
(PROTOCOL=TCP)

# Miscellaneous
COMPATIBLE=9.2.0
DB_NAME=dup

# Distributed, Replication and Snapshot
DB_DOMAIN=us.oracle.com
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

# Network Registration
INSTANCE_NAME=mynewdb

# Pools
JAVA_POOL_SIZE=31457280
LARGE_POOL_SIZE=1048576
SHARED_POOL_SIZE=52428800

# Processes and Sessions
PROCESSES=150

# Redo Log and Recovery
FAST_START_MTTR_TARGET=300

# Resource Manager
RESOURCE_MANAGER_PLAN=SYSTEM_PLAN

# Sort, Hash Joins, Bitmap Indexes
SORT_AREA_SIZE=524288

# Automatic Undo Management
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=UNDOTBS01

Note that you might want to use PGA_TARGET instead of SORT_AREA_SIZE, and in 10g SGA_TARGET instead of DB_CACHE_SIZE and the various auto-tunable pools. (I think 11g supports MEMORY_TARGET or something of the sort)
b. create directory structure to support this new instance
The directories for all the ****_dump_dest must exist and so does the directory structure for the database files (archive, controlfiles, datafiles), so you must create them, as rman will not create them automatically. Use the -p option of mkdir to make your task easier.

c. start the instance (we will not create control files as that will be automatically done with rman's duplicate)
from a shell run the following

ORACLE_SID=dup; export ORACLE_SID
sqlplus "/ as sysdba"
startup nomount

you may use the pfile=FULL_PATH_TO_PFILE parameter to indicate where the pfile is, in case it's not in the default path ($ORACLE_HOME/dbs/initdip.ora)

2. We need to connect with rman to both the target database (the one we want to duplicate) and the auxiliary database (the one we just created), we do so this way

rman target system/manager@orig auxiliary / nocatalog

we can use an rman catalog, though it's not strictly necessary.

3. Once we are conected to the database, we simply need run the duplicate command in this way

run{
allocate auxiliary channel d1 type disk;
--Here we have various options, however we will use db_file_name_convert as it is a lot simpler than the other. Check the documentation for alternatives
UPLICATE TARGET DATABASE TO dup
DB_FILE_NAME_CONVERT=('/u01/oracle/dbs/orig/','/u01/oracle/oradata/dup/')
UNTIL TIME 'SYSDATE-1' # specifies incomplete recovery
SKIP TABLESPACE cmwlite, drsys, example # skip desired tablespaces
PFILE = ?/dbs/initdup.ora
lOGFILE
GROUP 1 ('/u01/oracle/oradata/redo01_1.log') SIZE 200K,
GROUP 2 ('/u01/oracle/oradata/redo02_1.log') SIZE 200K,
GROUP 3 ('/u01/oracle/oradata/redo03_1.log') SIZE 200K REUSE;
}

the auxiliary channel may be allocated to a tape device, but that depends on the setup. We are using disk here for the sake of simplicity. We may also allocate multiple channels to obtain better performance.

4. To open the new database we simply connect to it and open it using resetlogs option

ORACLE_SID=dup; export ORACLE_SID
sqlplus "/ as sysdba"
alter database open resetlogs;

5. We may now begin exporting the necessary data and importing it to production.

I hope you find this useful, please feel free to comment anything and note that as I find new things I will be modifying this post. This method has worked for me a few times already, so I'm confident it works.

HTH
Guillermo Alan Bort
Oracle Certified Associate - Database 10g.-