Home » RDBMS Server » Backup & Recovery » RESTORE DATABASE SKIP TABLESPACE (Oracle 11203 AIX 61)
RESTORE DATABASE SKIP TABLESPACE [message #549626] Mon, 02 April 2012 09:41 Go to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
I have a full backup of my PROD DB (dev1) and want to make a copy of it to another name (dev2) on another machine. In addition, I only want to recover the "users" TS.

will RESTORE DATABASE SKIP TABLESPACE do the trick? If so can
somebody provide an example..

Thanks



Re: RESTORE DATABASE SKIP TABLESPACE [message #549629 is a reply to message #549626] Mon, 02 April 2012 09:46 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Er.... can you say that again? It sounds as though you want to restore that entire database, then apply recovery to only one tablespace? You can't do that.
Re: RESTORE DATABASE SKIP TABLESPACE [message #549632 is a reply to message #549629] Mon, 02 April 2012 10:12 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
In a nutshell, I want to only recover the "users" TS from my PROD DB to another DB from rman full backup.
Re: RESTORE DATABASE SKIP TABLESPACE [message #549634 is a reply to message #549632] Mon, 02 April 2012 10:17 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Would that be what they call TSPITR,
file:///C:/reference/docs/DB11gR2/E11882_01/backup.112/e10642/rcmtspit.htm#i1014116

Or if you actually want to end up with a working database that contains just one user tablespace, I suppose you could restore the controlfile, startup mount, take all tablespaces offline except SYSTEM, the UNDO TS, and the TS you want, then restore just those datafiles. It should work.

[update: this url might be a bit more use,
http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmtspit.htm#i1014116
sorry]

[Updated on: Mon, 02 April 2012 10:24]

Report message to a moderator

Re: RESTORE DATABASE SKIP TABLESPACE [message #549641 is a reply to message #549634] Mon, 02 April 2012 12:41 Go to previous messageGo to next message
BeefStu
Messages: 208
Registered: October 2011
Senior Member
I just listed the TS I wanted therefore "users" got
copied and "tools" did not.

I copied from (dev1) to (dev2). Note that the necessary Tablespaces such as (system, sysaux, undo, temp) are automaically copied.



rman catalog=rman/rman@dev1 target=sys/sys@dev1 << EOT
connect auxiliary sys/sys@dev2
duplicate target database to dev2 nofilenamecheck
 TABLESPACE users
 pfile=/u01/app/oracle/product/11.2.0.3/dbs/initdev2.ora
 until time="TO_DATE('04/02/12,13:13:05','MM/DD/YY,HH24:MI:SS')";
        exit
EOT




$ cat /u03/oracle/log/rman_clone_from_dev1_tsonly_dev2.log

SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 2 13:32:06 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> ORACLE instance shut down.
SQL> ORACLE instance started.

Total System Global Area 3156877312 bytes
Fixed Size                  2225568 bytes
Variable Size            1056967264 bytes
Database Buffers         2080374784 bytes
Redo Buffers               17309696 bytes
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Apr 2 13:32:11 2012

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to target database: DEV1 (DBID=3388471294)
connected to recovery catalog database

RMAN>
connected to auxiliary database: DEV2 (not mounted)

RMAN> 2> 3> 4>
Starting Duplicate Db at 04-02-2012 13:32:15
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=576 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=767 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=958 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=1149 device type=DISK

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script

sql statement: create spfile from memory

contents of Memory Script:
{
   shutdown clone immediate;
   startup clone nomount;
}
executing Memory Script

Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area    3156877312 bytes

Fixed Size                     2225568 bytes
Variable Size               1056967264 bytes
Database Buffers            2080374784 bytes
Redo Buffers                  17309696 bytes

contents of Memory Script:
{
   set until scn  224971;
   sql clone "alter system set  db_name =
 ''DEV1'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   sql clone "alter system set  db_unique_name =
 ''DEV2'' comment=
 ''Modified by RMAN duplicate'' scope=spfile";
   shutdown clone immediate;
   startup clone force nomount
   restore clone primary controlfile;
   alter clone database mount;
}
executing Memory Script

executing command: SET until clause

sql statement: alter system set  db_name =  ''DEV1'' comment= ''Modified by RMAN duplicate'' scope=spfile

sql statement: alter system set  db_unique_name =  ''DEV2'' comment= ''Modified by RMAN duplicate'' scope=spfile

Oracle instance shut down

Oracle instance started

Total System Global Area    3156877312 bytes

Fixed Size                     2225568 bytes
Variable Size               1056967264 bytes
Database Buffers            2080374784 bytes
Redo Buffers                  17309696 bytes

Starting restore at 04-02-2012 13:32:43
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=576 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=767 device type=DISK
allocated channel: ORA_AUX_DISK_3
channel ORA_AUX_DISK_3: SID=958 device type=DISK
allocated channel: ORA_AUX_DISK_4
channel ORA_AUX_DISK_4: SID=1149 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u10/oradata/dev1/spfile_control_files/c-3388471294-20120402-04
channel ORA_AUX_DISK_1: piece handle=/u10/oradata/dev1/spfile_control_files/c-3388471294-20120402-04 tag=TAG20120402T131253
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u04/oradata/dev2/control_01.ctl
output file name=/u05/oradata/dev2/control_02.ctl
output file name=/u04/oradata/dev2/control_03.ctl
Finished restore at 04-02-2012 13:32:47

database mounted
Automatically adding tablespace SYSTEM
Automatically adding tablespace SYSAUX
Automatically adding tablespace UNDOTBS01
Checking that duplicated tablespaces are self-contained
Skipping tablespace TOOLS

contents of Memory Script:
{
   set until scn  224971;
   set newname for datafile  1 to
 "/u04/oradata/dev2/system01.dbf";
   set newname for datafile  2 to
 "/u05/oradata/dev2/sysaux01.dbf";
   set newname for datafile  3 to
 "/u04/oradata/dev2/undotbs01.dbf";
   set newname for datafile  4 to
 "/u04/oradata/dev2/user_01.dbf";
   restore
   clone database
   skip forever tablespace  "TOOLS"   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 04-02-2012 13:32:59
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u05/oradata/dev2/sysaux01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u10/oradata/dev1/rman/full_12_0402_1312/rman_DEV1_t779548364_s32_p1
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00001 to /u04/oradata/dev2/system01.dbf
channel ORA_AUX_DISK_2: restoring datafile 00004 to /u04/oradata/dev2/user_01.dbf
channel ORA_AUX_DISK_2: reading from backup piece /u10/oradata/dev1/rman/full_12_0402_1312/rman_DEV1_t779548364_s33_p1
channel ORA_AUX_DISK_3: starting datafile backup set restore
channel ORA_AUX_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_3: restoring datafile 00003 to /u04/oradata/dev2/undotbs01.dbf
channel ORA_AUX_DISK_3: reading from backup piece /u10/oradata/dev1/rman/full_12_0402_1312/rman_DEV1_t779548364_s34_p1
channel ORA_AUX_DISK_1: piece handle=/u10/oradata/dev1/rman/full_12_0402_1312/rman_DEV1_t779548364_s32_p1 tag=TAG_1204021312
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_2: piece handle=/u10/oradata/dev1/rman/full_12_0402_1312/rman_DEV1_t779548364_s33_p1 tag=TAG_1204021312
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_3: piece handle=/u10/oradata/dev1/rman/full_12_0402_1312/rman_DEV1_t779548364_s34_p1 tag=TAG_1204021312
channel ORA_AUX_DISK_3: restored backup piece 1
channel ORA_AUX_DISK_3: restore complete, elapsed time: 00:00:15
Finished restore at 04-02-2012 13:33:15

contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 1 switched to datafile copy
input datafile copy RECID=7 STAMP=779549595 file name=/u04/oradata/dev2/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=8 STAMP=779549595 file name=/u05/oradata/dev2/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=9 STAMP=779549595 file name=/u04/oradata/dev2/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=10 STAMP=779549595 file name=/u04/oradata/dev2/user_01.dbf

contents of Memory Script:
{
   set until time  "to_date('APR 02 2012 13:13:05', 'MON DD YYYY HH24:MI:SS')";
   recover
   clone database
   skip forever tablespace  "TOOLS"    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 04-02-2012 13:33:15
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2
using channel ORA_AUX_DISK_3
using channel ORA_AUX_DISK_4

Executing: alter database datafile 5 offline drop
starting media recovery

archived log for thread 1 with sequence 11 is already on disk as file /u02/arch/dev1/dev1_11_1_779530558.arc
archived log for thread 1 with sequence 12 is already on disk as file /u02/arch/dev1/dev1_12_1_779530558.arc
archived log file name=/u02/arch/dev1/dev1_11_1_779530558.arc thread=1 sequence=11
archived log file name=/u02/arch/dev1/dev1_12_1_779530558.arc thread=1 sequence=12
media recovery complete, elapsed time: 00:00:01
Finished recover at 04-02-2012 13:33:18
Oracle instance started

Total System Global Area    3156877312 bytes

Fixed Size                     2225568 bytes
Variable Size               1056967264 bytes
Database Buffers            2080374784 bytes
Redo Buffers                  17309696 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DEV2" RESETLOGS ARCHIVELOG
  MAXLOGFILES    255
  MAXLOGMEMBERS      3
  MAXDATAFILES     1024
  MAXINSTANCES     1
  MAXLOGHISTORY    36517
 LOGFILE
  GROUP   1 ( '/u04/oradata/dev2/redo_01a.log', '/u05/oradata/dev2/redo_01b.log' ) SIZE 512 M  REUSE,
  GROUP   2 ( '/u04/oradata/dev2/redo_02a.log', '/u05/oradata/dev2/redo_02b.log' ) SIZE 512 M  REUSE,
  GROUP   3 ( '/u04/oradata/dev2/redo_03a.log', '/u05/oradata/dev2/redo_03b.log' ) SIZE 512 M  REUSE
 DATAFILE
  '/u04/oradata/dev2/system01.dbf'
 CHARACTER SET AL32UTF8


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u05/oradata/dev2/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u05/oradata/dev2/sysaux01.dbf",
 "/u04/oradata/dev2/undotbs01.dbf",
 "/u04/oradata/dev2/user_01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to /u05/oradata/dev2/temp01.dbf in control file

cataloged datafile copy
datafile copy file name=/u05/oradata/dev2/sysaux01.dbf RECID=1 STAMP=779549607
cataloged datafile copy
datafile copy file name=/u04/oradata/dev2/undotbs01.dbf RECID=2 STAMP=779549607
cataloged datafile copy
datafile copy file name=/u04/oradata/dev2/user_01.dbf RECID=3 STAMP=779549607

datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=779549607 file name=/u05/oradata/dev2/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=779549607 file name=/u04/oradata/dev2/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=779549607 file name=/u04/oradata/dev2/user_01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Dropping offline and skipped tablespaces
Executing: drop tablespace "TOOLS" including contents cascade constraints
Finished Duplicate Db at 04-02-2012 13:33:54

RMAN>



Re: RESTORE DATABASE SKIP TABLESPACE [message #549642 is a reply to message #549641] Mon, 02 April 2012 12:52 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Well, that was easy! I didn't know RMAN could do that. Thanks.
Previous Topic: tablespace resize
Next Topic: Backup & Recovery
Goto Forum:
  


Current Time: Thu Mar 28 07:09:34 CDT 2024