In Oracle How Do You Clone One Tablespace to Another Database Through RMAN?

ironicdba 9:42 pm on February 3, 2010 | Answers: 1    
Tags: clone (5), database (51), oracle (136), oracle rman restore, rman, tablespace (2)

Rate Knowledge: (1 votes)

 Loading ...

• ironicdba 9:42 pm on February 3, 2010
  

  To restore specific TBS in another location you will need to restore all undo, sys tablespaces for the database it is on. The following dynamic sql will setup the basis for your RMAN script. Run the following script on your Source database:

  spool restoreTBS.rcv
  set feedback off
  set echo off
  set pages 0
  prompt connect target system/passwd@targetDB
  prompt connect auxiliary sys/passwd_of target
  prompt run
  prompt {
  prompt allocate auxiliary channel t1 type ‘SBT_TAPE’;
  prompt allocate auxiliary channel t2 type ‘SBT_TAPE’;
  prompt allocate auxiliary channel t3 type ‘SBT_TAPE’;
  prompt duplicate target database to newdb skip tablespace
  with mr as (
  select max(rownum) maxrow from dba_tablespaces where tablespace_name not in (’SYSTEM’,'SYSAUX’,'TEMPTS’,'UNDOTS’,'TBS1′,’TBS2′)
  )
  select decode(rownum,1,null,’,')||tablespace_name||decode(rownum,(select maxrow from mr),’;',null) cmd
  from dba_tablespaces where tablespace_name not in (’SYSTEM’,'SYSAUX’,'TEMPTS’,'UNDOTS’,'TBS1′,’TBS2′);
  prompt UNTIL TIME “to_date(’15 JAN 2010 17:31:00′,’DD MON YYYY hh24:mi:ss’)”;;

  This script is not ready yet the following will still need to be done:
  Remove the tablespaces you want to keep.Remove it for any issuesAdd in the redolog information. The following script that can be ran on the source can help with this:select group#, member from v$logfile;
  to add something to the bottom of the RMAN script that looks like:

  LOGFILE
  GROUP 103 ( ‘+DATA01/…/onlinelog/group_103.263.701860557′,
  ‘+FLASH01…/onlinelog/group_103.259.701860561′) size 10M reuse,
  GROUP 102 ( ‘+DATA01/…/onlinelog/group_102.262.701860549′,
  ‘+FLASH01/…/onlinelog/group_102.258.701860553′) size 10M reuse,
  …
  In the end you should have a script that looks something like:

  connect target system/passwd@targetDB
  connect auxiliary sys/passwd
  run
  {
  allocate auxiliary channel t1 type ‘SBT_TAPE’;
  allocate auxiliary channel t2 type ‘SBT_TAPE’;;
  duplicate target database to targetDB skip tablespace
  USERS
  ,SCOTT
  …
  LOGFILE
  GROUP 103 ( ‘+DATA01/…/onlinelog/group_103.263.701860557′,
  ‘+FLASH01/…onlinelog/group_103.259.701860561′) size 10M reuse,
  GROUP 102 ( ‘+DATA01/…/onlinelog/group_102.262.701860549′,
  ‘+FLASH01/…/onlinelog/group_102.258.701860553′) size 10M reuse
  UNTIL TIME “to_date(’15 JAN 2010 17:31:00′,’DD MON YYYY hh24:mi:ss’)”;
  }
  On the source side make sure your db_file_Name_convert and logfile_name_convert parameters are setup correctly

  alter system set db_file_name_convert=”+DATA01/…”,”+DATA01/…”,
  “+INDEX01/…”,”+DATA01/…”,
  …
  scope=spfile sid=’SOURCEDB’;
  alter system set log_file_name_convert=”/opt/oracle/admin/…”,”/opt/oracle/admin/..”
  scope=spfile sid=’SOURCEDB’;
  On the source side put the DB in nomount mode and run your script in rman.

  rman catalog=rmanrepository/passwd@RMANREPDB cmdfile restoreTBS.rcv log restore.log