Wednesday, December 24, 2008

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.


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

# Cursors and Library Cache

# Diagnostics and Statistics

# Control File Configuration

# Archive

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

# Miscellaneous

# Distributed, Replication and Snapshot

# Network Registration

# Pools

# Processes and Sessions

# Redo Log and Recovery

# Resource Manager

# Sort, Hash Joins, Bitmap Indexes

# Automatic Undo Management

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
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
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
UNTIL TIME 'SYSDATE-1' # specifies incomplete recovery
SKIP TABLESPACE cmwlite, drsys, example # skip desired tablespaces
PFILE = ?/dbs/initdup.ora
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
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.

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

Oracle Certified Associate

I recently received the 10g OCA certificate, it's the first step towards achieving the OCP credential. I thought it would be wise to start documenting my experiences with Oracle Database in order to become better known in the Oracle Community. I hope you find this blog helpful as I have many others around.

Guillermo Alan Bort
Oracle Certified Associate - Database 10g