cancel
Showing results for 
Search instead for 
Did you mean: 

Move tablespace out of the db

SOLVED
Go to solution
Arturo Galbiati
Esteemed Contributor

Move tablespace out of the db

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
10 REPLIES
Zinky
Honored Contributor

Re: Move tablespace out of the db

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...
Hakuna Matata

Favourite Toy:
AMD Athlon II X6 1090T 6-core, 16GB RAM, 12TB ZFS RAIDZ-2 Storage. Linux Centos 5.6 running KVM Hypervisor. Virtual Machines: Ubuntu, Mint, Solaris 10, Windows 7 Professional, Windows XP Pro, Windows Server 2008R2, DOS 6.22, OpenFiler
Steven E. Protter
Exalted Contributor

Re: Move tablespace out of the db

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Graham Cameron_1
Honored Contributor

Re: Move tablespace out of the db

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
Computers make it easier to do a lot of things, but most of the things they make it easier to do don't need to be done.
Hein van den Heuvel
Honored Contributor

Re: Move tablespace out of the db

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
Esteemed Contributor

Re: Move tablespace out of the db

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
Honored Contributor
Solution

Re: Move tablespace out of the db

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
fiat lux
Printaporn_1
Esteemed Contributor

Re: Move tablespace out of the db

you just make it readonly tablespace.
enjoy any little thing in my life
Yogeeraj_1
Honored Contributor

Re: Move tablespace out of the db

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Move tablespace out of the db

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Arturo Galbiati
Esteemed Contributor

Re: Move tablespace out of the db

Thx Jan-Luc & Yogeeraj