Home » RDBMS Server » Backup & Recovery » How to create tablespace from backup (RHEL 4 X86_64, Oracle 10g R2)
How to create tablespace from backup [message #279964] Mon, 12 November 2007 01:52 Go to next message
bbmonster
Messages: 47
Registered: June 2007
Location: UB
Member

Hi all,

I need to create tablespace from backup. I previously backuped it.

Please help
Re: How to create tablespace from backup [message #279967 is a reply to message #279964] Mon, 12 November 2007 02:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you want to restore and recover a backed tablespace?
What kind of backup do you make (rman, os...)?

Regards
Michel
Re: How to create tablespace from backup [message #279971 is a reply to message #279967] Mon, 12 November 2007 02:28 Go to previous messageGo to next message
bbmonster
Messages: 47
Registered: June 2007
Location: UB
Member

Thanks for your reply.

Yes, I've backuped it with RMAN without catalog.
When restoring the tablespace with restore tablespace tek_bak1101 the following error throws:

Starting restore at 12-NOV-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=94 devtype=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/12/2007 17:03:29
RMAN-20202: tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "tek_bak1101"


So where should I specify tablespace backup file location in restore command?
Re: How to create tablespace from backup [message #279973 is a reply to message #279971] Mon, 12 November 2007 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Either not use catalog to restore it or register your backup in the catalog.

Regards
Michel
Re: How to create tablespace from backup [message #279975 is a reply to message #279964] Mon, 12 November 2007 02:52 Go to previous messageGo to next message
bbmonster
Messages: 47
Registered: June 2007
Location: UB
Member

Sorry Michel, I don't understand what you mean.

I tried the following way but couldn't restore the tablespace.
RMAN> run {
2> allocate channel ch1 type disk format '/disk6/backup/tek_backup/tek_bak1101';
3> restore tablespace tek_bak1101;
4> }

command throws the following exception:

Starting restore at 12-NOV-07
released channel: ch1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/12/2007 17:13:48
RMAN-20202: tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "tek_bak1101"


How can I create tablespace from the backup?
Re: How to create tablespace from backup [message #279981 is a reply to message #279975] Mon, 12 November 2007 03:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 12 November 2007 09:41

Either not use catalog to restore it or register your backup in the catalog.

Regards
Michel


Re: How to create tablespace from backup [message #279985 is a reply to message #279964] Mon, 12 November 2007 04:01 Go to previous messageGo to next message
bbmonster
Messages: 47
Registered: June 2007
Location: UB
Member

Could you tell me some idea to restore it without using catalog?

Thanks

BB
Re: How to create tablespace from backup [message #279993 is a reply to message #279985] Mon, 12 November 2007 04:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rman nocatalog target /

Regards
Michel
Re: How to create tablespace from backup [message #280013 is a reply to message #279964] Mon, 12 November 2007 05:14 Go to previous messageGo to next message
bbmonster
Messages: 47
Registered: June 2007
Location: UB
Member

I still didn't solved the problem. I am posting test code here.

Please help

##### 1. create tablespace & insert some data to it.

[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 12 19:14:50 2007

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

SQL> conn /as sysdba
Connected.
SQL> CREATE SMALLFILE TABLESPACE "TS07" DATAFILE '/disk6/TS07_1' SIZE 100M LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

SQL> create table tbl01_1 tablespace TS07 as SELECT * FROM uid02.tbl01 WHERE M105 = 3
2 ;

Table created.

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

##### 2. backup TS07 with RMAN
[oracle@localhost ~]$ /home/oracle/db/bin/rman target / nocatalog

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Nov 12 19:19:38 2007

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

connected to target database: UCCS (DBID=1480994533)
using target database control file instead of recovery catalog

RMAN> run {
2> allocate channel c1 device type disk;
3> set command id to 'BBBackup';
4> configure backup optimization on;
5> sql "alter tablespace TS07 offline normal";
6> backup format '/disk6/backup/TS07_bak_%T_%U'
7> (tablespace TS07);
8> release channel c1;
9> sql "alter tablespace TS07 online";
10> }

allocated channel: c1
channel c1: sid=136 devtype=DISK

executing command: SET COMMAND ID

old RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored

sql statement: alter tablespace TS07 offline normal

Starting backup at 12-NOV-07
channel c1: starting full datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00052 name=/disk6/TS07_1
channel c1: starting piece 1 at 12-NOV-07
channel c1: finished piece 1 at 12-NOV-07
piece handle=/disk6/backup/TS07_bak_20071112_0uj0srn6_1_1 tag=TAG20071112T193502 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:01
Finished backup at 12-NOV-07

Starting Control File and SPFILE Autobackup at 12-NOV-07
piece handle=/disk6/backup/controlfile/c-1480994533-20071112-05 comment=NONE
Finished Control File and SPFILE Autobackup at 12-NOV-07

released channel: c1

sql statement: alter tablespace TS07 online

RMAN> quit


Recovery Manager complete.

##### 3. drop tablespace including data files
[oracle@localhost ~]$
[oracle@localhost ~]$
[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 12 19:37:38 2007

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

SQL> conn /as sysdba
Connected.
SQL> drop tablespace TS07 including contents and datafiles;

Tablespace dropped.

SQL> commit;

Commit complete.

SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options


##### 4. restore deleted tablespace from backup
[oracle@localhost ~]$ /home/oracle/db/bin/rman nocatalog target /

Recovery Manager: Release 10.2.0.1.0 - Production on Mon Nov 12 19:39:59 2007

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

connected to target database: UCCS (DBID=1480994533)
using target database control file instead of recovery catalog

RMAN> run {
2> allocate channel c1 type disk format '/disk6/backup/TS07_bak_20071112_0uj0srn6_1_1';
3> restore tablespace TS07;
4> release channel c1;
5> }

allocated channel: c1
channel c1: sid=76 devtype=DISK

Starting restore at 12-NOV-07
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/12/2007 19:41:48
RMAN-20202: tablespace not found in the recovery catalog
RMAN-06019: could not translate tablespace name "TS07"



Regards BB
Re: How to create tablespace from backup [message #280109 is a reply to message #279964] Mon, 12 November 2007 08:57 Go to previous messageGo to next message
varu123
Messages: 754
Registered: October 2007
Senior Member
Are you in archive log mode?
If yes
try alter database recover tablespace ts07;
Re: How to create tablespace from backup [message #280149 is a reply to message #280109] Mon, 12 November 2007 11:32 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You can not restore/recover a DROPPED tablespace like that with RMAN.

Your only options are to restore & recover the entire database back to a time BEFORE the tablespace was dropped or duplicate the entire database to another location and perform PITR so you can export the tablespace in question.

If you have flashback enabled, you can consider Point-in-Time Recovery and Flashback Features.
Re: How to create tablespace from backup [message #280170 is a reply to message #280013] Mon, 12 November 2007 12:35 Go to previous messageGo to next message
varunvir
Messages: 363
Registered: November 2007
Senior Member
YoU have to take tablespace in offline mode before restoring it at RMAN promt.
Re: How to create tablespace from backup [message #280171 is a reply to message #280170] Mon, 12 November 2007 12:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Tablespace can't be put in offline mode, it was DROPPED.

Regards
Michel
Re: How to create tablespace from backup [message #280226 is a reply to message #279964] Mon, 12 November 2007 19:41 Go to previous messageGo to next message
bbmonster
Messages: 47
Registered: June 2007
Location: UB
Member

Entire database size is very huge, so it's very difficult to backup entire database. So I grouped the data to the tablespaces comprising one or more datafiles. Then backup it & when it needs restore it from the backup. I thought it may be the same as deattaching & attaching database in MS-SQL Server. But restoring the tablespace from the backup doesn't work for me.


Please help me how can I maintain backuping & restoring of tablespaces?


Thanks,

BB
Re: How to create tablespace from backup [message #280244 is a reply to message #280226] Mon, 12 November 2007 23:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you want to be able to restore a tablespace after a disk failure or to archive a non-used tablespace and be able to "reattach" it later?

Regards
Michel
Re: How to create tablespace from backup [message #280306 is a reply to message #280244] Tue, 13 November 2007 02:22 Go to previous messageGo to next message
bbmonster
Messages: 47
Registered: June 2007
Location: UB
Member

Yes, I want to archive non-used tablespace to the storage such as tape or another server. And then delete the archived tablespace to free disk space. When it needs "reattach" it from the backup to the database.

What should I do to accomplish this kind of operation? Should I practice with TSPITR?


Thanks,

BB
Re: How to create tablespace from backup [message #280313 is a reply to message #280306] Tue, 13 November 2007 03:14 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case you have to use "transportable tablespace" (search for this) and not backup.

Regards
Michel
Previous Topic: faq
Next Topic: Cloning a database from one m/c to another
Goto Forum:
  


Current Time: Sun Dec 04 19:01:26 CST 2016

Total time taken to generate the page: 0.05417 seconds