| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Migrating TOOLS tablespace to ASSM
Mainly because I originally used this technique to transport a set of tablespaces from one non-ASM instance to another ASM one - in those circumstances I didn't have to take the tablespace offline at all. However by only taking the datafile offline at the point I did does minimize the downtime for anyone who might want to use the tablespace rather than having it offline for the duration of the copy. I may even have got away with an "alter tablespace .... begin backup" rather than "alter table read only" but as I said I was adapting a working example. In fact I still think the rman copy , convert, switch to backup approach is probably the "correct" method but I didn't have the syntax to hand.
Cheers,
Ian
|---------+---------------------------->
| | Joel.Patterson_at_cr|
| | owley.com |
| | |
| | 13/06/2007 13:34 |
| | |
|---------+----------------------------> >--------------------------------------------------------------------------------------------------------------| | | | To: Ian Cary/ONS_at_ONS, binhpham15_at_hotmail.com | | cc: oracle-l_at_freelists.org, oracle-l-bounce_at_freelists.org, tim_at_evdbt.com | | Subject: RE: Migrating TOOLS tablespace to ASSM | >--------------------------------------------------------------------------------------------------------------|
Why don't you take the tablespace offline before you move the datafile?
Joel Patterson
Database Administrator
joel.patterson_at_crowley.com
x72546
904 727-2546
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ian Cary
Sent: Wednesday, June 13, 2007 5:23 AM
To: binhpham15_at_hotmail.com
Cc: oracle-l_at_freelists.org; oracle-l-bounce_at_freelists.org; tim_at_evdbt.com
Subject: Re: Migrating TOOLS tablespace to ASSM
I didn't see you get an answer to this but the steps below seem to work.
I
believe another alternative would be to use the rman convert command.
Cheers,
Ian
OTSTST10> create tablespace move_test datafile '/u02/oradata/otstst10/move_test.dbf' size 100m;
Tablespace created.
OTSTST10> create table long_test (x long) tablespace move_test;
Table created.
OTSTST10> create directory source_dir as '/u02/oradata/otstst10';
Directory created.
create directory target_dir as '+data/otstst10';
Directory created.
OTSTST10> alter tablespace move_test read only;
Tablespace altered.
OTSTST10> exec
dbms_file_transfer.copy_file('SOURCE_DIR','move_test.dbf','TARGET_DIR','
move_test.dbf');
PL/SQL procedure successfully completed.
OTSTST10> alter tablespace move_test offline;
Tablespace altered.
alter database rename file '/u02/oradata/otstst10/move_test.dbf' to '+data/otstst10/move_test.dbf';
Database altered.
OTSTST10> alter tablespace move_test online;
Tablespace altered.
|---------+----------------------------->|
| | binhpham15_at_hotmail|
| | .com |
| | Sent by: |
| | oracle-l-bounce_at_fr|
| | eelists.org |
| | |
| | |
| | 13/06/2007 02:23 |
| | Please respond to |
| | binhpham15 |
| | |
|---------+-----------------------------> >----------------------------------------------------------------------- ---------------------------------------|
>----------------------------------------------------------------------- ---------------------------------------|
Tim,
Thanks, however, the "alter table ... move..." does not work when they have
"Long" column types, and hence the question.
>From: Tim Gorman <tim_at_evdbt.com> >Reply-To: tim_at_evdbt.com >To: binhpham15_at_hotmail.com >CC: oracle-l_at_freelists.org >Subject: Re: Migrating TOOLS tablespace to ASSM >Date: Mon, 11 Jun 2007 20:05:50 -0600 > >Sure! Use "ALTER TABLE ... MOVE TABLESPACE <another-tablespace>" tomove
>'em out. > >...don't forget to ALTER INDEX ... REBUILD on any indexes associatedwith
>moved table... > > > >Binh Pham wrote: >>We are trying to migrate this TOOLS tablespace to ASSM andencountering
>>issue. There are objects (tables,indexes, materialized views, etc...) >>owned >>by SYSTEM that prevents us from changing this TOOLS tablespace toASSM.
>> >>Objects that are in the TOOLS for example: >> >>SYSTEM.USER_PROFILE >>SYSTEM.SQLPLUS_PRODUCT_PROFILE >> >> >>Any suggestion? Thanks. >> >>-- >>http://www.freelists.org/webpage/oracle-l >> >> >> >> _________________________________________________________________PC Magazine's 2007 editors' choice for best Web mail?award-winning Windows
-- http://www.freelists.org/webpage/oracle-l This email was received from the INTERNET and scanned by the Government Secure Intranet Anti-Virus service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2006/04/0007.) In case of problems, please call your organisation's IT Helpdesk. Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes. For the latest data on the economy and society consult National Statistics at http://www.statistics.gov.uk ************************************************************************ ********* Please Note: Incoming and outgoing email messages are routinely monitored for compliance with our policy on the use of electronic communications ************************************************************************ ********* Legal Disclaimer : Any views expressed by the sender of this message are not necessarily those of the Office for National Statistics ************************************************************************ ********* The original of this email was scanned for viruses by the Government Secure Intranet Anti-Virus service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2006/04/0007.) On leaving the GSi this email was certified virus free. Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes. -- http://www.freelists.org/webpage/oracle-l This email was received from the INTERNET and scanned by the Government Secure Intranet Anti-Virus service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2006/04/0007.) In case of problems, please call your organisation's IT Helpdesk. Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes. The original of this email was scanned for viruses by the Government Secure Intranet Anti-Virus service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2006/04/0007.) On leaving the GSi this email was certified virus free. Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes. -- http://www.freelists.org/webpage/oracle-lReceived on Wed Jun 13 2007 - 08:01:19 CDT
 |
 |