Move tablespace out of the db

[Arturo Galbiati]

Hi Gurus,
is it possible to move the tablespace to a read only device (CD-ROM, DVD) and reput them online in case of need in few time?
I need to move out the db the archived data but in case of request, put them available in 24 hours.
Any other ideas/suggestions?
TIA,
Art

[Zinky]

Artur,
Form a UNIX standpoint, any file(s) or directory trees could be stored on on-line storage (discs - hdd/dasd, optical media - DVD/CDR) or near-line storage (optical media - CDROM/DVD, tapes or virtual tape subsystems).. so it is up to your application if they support such -- in your case Oracle. I believe it will work as you can bring a Tablespace online in R/O mode but as far as speed goes -- that is suspect...

You can try most certainly and let us know how it goes...

[Steven E. Protter]

If this is oracle, you can set up control files to look at a cd-rom. Speed is likely to be slow,and obviously any write transactions will fail. The application needs to be bullet proof, or the database will crash very hard.

Most people don't bother with this because disk has become pretty cheap over the years.

We have a tape library system that lets us take databases offline. We an either restore the, or with a hardware upgrade have the data come online automatically when the request comes through. Obviously a big database will take some time to lay down.

SEP

[Graham Cameron_1]

I would suggest you look at the transportable tablespace feature of Oracle.
This allows you to switch tablespaces in and out of the database.
We use this (albeit not with CD-ROM) and it works fine.

-- Graham

[Hein van den Heuvel]

Hi Arturo,

I see you gave Graham '3 points'. Now I don't care how many points you give any or all of us, but I hope you realize the importance of Graham's suggestion! Please re-read.

Transportable tablespaces are likely to be the ultimate solution for your problem.
Unlike just putting a tablespace (file) 'offline' in Oracle, you can actaully hook them up back to the origianl DB, or connect them to a different DB. Powerfull stuff.

Other replies comment on the (lack of) speed on CDrom/DVD. The way I read the original quesion one possibel usage is to use the optical media (or tape) to roll out to a mountpoint, use it (at disk speeds), and loose it when done.

hth,
Hein.

[Arturo Galbiati]

The suggestion is good, but I've 8.1.7 and the trasportable tablespace are not allowed in thsi release if I weel remember.
This is teh reason why i assigned 3 point.
The suggestion is good, but not useful for me.

[Jean-Luc Oudart · Accepted solution]

Well,

Transportable tablespaces are available on 8.1.7.
I tried it in our environment.I don't have 9i it had to be 8i !

cf. metalink note 100693.1
"Getting Started with Transportable Tablespaces"

Rgds,
Jean-Luc

[Printaporn_1]

you just make it readonly tablespace.

[Yogeeraj_1]

hi,

The procedure will be something like:

create tablespace tbs_lmt_medium
datafile '/tmp/tts_test1.dbf' size 2m reuse autoextend off
uniform size 256k
/

Tablespace created.

alter tablespace tbs_lmt_medium
add datafile '/tmp/tts_test2.dbf' size 2m reuse autoextend off
/

alter tablespace tbs_lmt_medium READ ONLY;

exp transport_tablespace=y tablespaces=tbs_lmt_medium

drop tablespace tbs_lmt_medium including contents;

mv /tmp/tts_test1.dbf /readonly/better_name1.dbf

mv /tmp/tts_test2.dbf /readonly/better_name2.dbf

imp transport_tablespace=y 'datafiles=(/readonly/better_name1.dbf,/readonly/better_name2.dbf)'

alter tablespace tbs_lmt_medium read write;


hope this helps too!

regards
Yogeeraj

[Yogeeraj_1]

hi,

The procedure will be something like:

create tablespace tbs_lmt_medium
datafile '/tmp/tts_test1.dbf' size 2m reuse autoextend off
uniform size 256k
/



alter tablespace tbs_lmt_medium
add datafile '/tmp/tts_test2.dbf' size 2m reuse autoextend off
/

alter tablespace tbs_lmt_medium READ ONLY;

exp transport_tablespace=y tablespaces=tbs_lmt_medium

drop tablespace tbs_lmt_medium including contents;

mv /tmp/tts_test1.dbf /readonly/better_name1.dbf

mv /tmp/tts_test2.dbf /readonly/better_name2.dbf

imp transport_tablespace=y 'datafiles=(/readonly/better_name1.dbf,/readonly/better_name2.dbf)'

alter tablespace tbs_lmt_medium read write;


hope this helps too!

regards
Yogeeraj