Partially restore database
                                                 Last update (2011-04-26 21:29:43)
                                                                                                                   Date added (2008-05-27 14:31:52)

Summary
When dealing with Terabyte databases may have to restore and recover only a small part of the database, for example one or two tablespaces.

Imagine the following scenario
You have a production 10TB database and want to create an identical copy of the database, but with only some tablespaces taking approximately 500GB. So the restriction is to use only the space really needed, and not 10TBytes. For Backup and Recovery purposes lets say that you use the RMAN.

Solution:
The solution is the same described as "create duplicate database with RMAN" Metalink Note:388424.1 with the difference that because you don't need the hole database you have to use the statement:
DUPLICATE TARGET DATABASE TO DUP SKIP TABLESPACE TABLESPACES_THAT_YOU_DONT WANT;

In the following example from the entire database you want to restore the HELPDESK tablespace, but to save space from not restoring the DATA and USERS tablespace. The clone database with have a name DUP (duplicate database)

Prepare the environment on the remote server
In order to make the clone on the new remote server you must set up:
1. A new unix user with the same privileges like the production unix user
2. Copy the $ORACLE_HOME binaries from the production server to the new remote server.
    This will be your new $ORACLE_HOME for the clone. Relink to make it valid!
3. Add alias entries in tnsnames.ora for RMAN database, production database and new clone on the remote server.
4. Add a new listener.ora on the remote server.
5. Create directory structured for dump_dest trace files, datafiles_new, controlfiles, 
    redologs_new on the remote server.
6. Create a new init.ora file for the process on the remote server at $ORACLE_HOME/dbs location
In the example the production database is ANDR, DUP will be the clone and RMAN is the rman database
ANDR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = crmcc)(PORT = 1521))
    (CONNECT_DATA =
      (SID = ANDR)))

DUP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rp1dev)(PORT = 1551))
    (CONNECT_DATA =
      (SID = DUP)))

RMAN =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = bserver)(PORT = 1521))
    (CONNECT_DATA =
      (SID = RMAN)))
the new listener.ora file on the remote server is
# LISTENER.ORA Network Configuration File: /ora1/androm/9.2.0/network/admin/listener.ora
# Generated by Oracle configuration tools.

DUP =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rp1dev)(PORT = 1551)))

SID_LIST_DUP =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = DUP)
      (ORACLE_HOME = /orandr/ANDR/9.2.0)
      (SID_NAME = DUP)))

Start the new listener!
$lsnrctl start DUP
Now you have to create the initDUP.ora, at $ORACLE_HOME/dbs - here is the example
db_name = "DUP"
instance_name = DUP
control_files = (/orandr/ANDR/controlfiles/control01.ctl, /orandr/ANDR/controlfiles/control02.ctl, 
/orandr/ANDR/controlfiles/control03.ctl)
db_file_name_convert=(/orandr/ANDR/datafiles/,/orandr/ANDR/datafiles_new/)
log_file_name_convert=(/orandr/ANDR/redologs/,/orandr/ANDR/redologs_new/)
db_block_size = 8192
compatible = 9.2.0.0.0
background_dump_dest = '/orandr/ANDR/9.2.0/admin/DUP/bdump/'
user_dump_dest = '/orandr/ANDR/9.2.0/admin/DUP/udump/'
core_dump_dest = '/orandr/ANDR/9.2.0/admin/DUP/cdump/'
control_files is where the new controlfiles will be created
db_file_name_convert are the matching pairs of datafiles filesystems
log_file_name_convert are the matching pairs of redolog filesystems

After setting new $ORACLE_HOME, $ORACLE_SID, $PATH for the new clone execute on the remote server the following:
export NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI'
sqlplus "/ as sysdba"
STARTUP nomount PFILE=$ORACLE_HOME/dbs/initDUP.ora
exit sqlplus and run from the shell
rman TARGET sys/*****@ANDR CATALOG rman/****@RMAN AUXILIARY / 
from the RMAN command line execute (line by line )
RUN { 
allocate auxiliary channel ch01 TYPE 'SBT_TAPE';
allocate auxiliary channel ch02 TYPE 'SBT_TAPE';
allocate auxiliary channel ch03 TYPE 'SBT_TAPE';
send 'NB_ORA_CLIENT=crmcc';
SET UNTIL TIME 'SYSDATE-7';
DUPLICATE TARGET DATABASE TO DUP SKIP TABLESPACE DATA, USERS;
}
If the script is too long and don't want to execute it line-by-line, then put all info in an sql script(for example dup.sql) and run it from RMAN command line with:
RMAN>@dup.sql
Other links
Clone database with RMAN
Reviews
Filters
Search