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@cr|
|         |           owley.com        |
|         |                            |
|         |           13/06/2007 13:34 |
|         |                            |
|---------+---------------------------->
  
>--------------------------------------------------------------------------------------------------------------|
  |                                                                             
                                 |
  |       To:       Ian Cary/ONS@ONS, binhpham15@xxxxxxxxxxx                    
                                 |
  |       cc:       oracle-l@xxxxxxxxxxxxx, oracle-l-bounce@xxxxxxxxxxxxx, 
tim@xxxxxxxxx                         |
  |       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@xxxxxxxxxxx
x72546
904  727-2546

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Ian Cary
Sent: Wednesday, June 13, 2007 5:23 AM
To: binhpham15@xxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx; oracle-l-bounce@xxxxxxxxxxxxx; tim@xxxxxxxxx
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@hotmail|
|         |           .com              |
|         |           Sent by:          |
|         |           oracle-l-bounce@fr|
|         |           eelists.org       |
|         |                             |
|         |                             |
|         |           13/06/2007 02:23  |
|         |           Please respond to |
|         |           binhpham15        |
|         |                             |
|---------+----------------------------->

>-----------------------------------------------------------------------
---------------------------------------|
  |
|
  |       To:       tim@xxxxxxxxx
|
  |       cc:       oracle-l@xxxxxxxxxxxxx
|
  |       Subject:  Re: Migrating TOOLS tablespace to ASSM
|

>-----------------------------------------------------------------------
---------------------------------------|





Tim,

Thanks, however, the "alter table ... move..." does not work when they
have

"Long" column types, and hence the question.


>From: Tim Gorman <tim@xxxxxxxxx>
>Reply-To: tim@xxxxxxxxx
>To: binhpham15@xxxxxxxxxxx
>CC: oracle-l@xxxxxxxxxxxxx
>Subject: Re: Migrating TOOLS tablespace to ASSM
>Date: Mon, 11 Jun 2007 20:05:50 -0600
>
>Sure!  Use "ALTER TABLE ... MOVE TABLESPACE <another-tablespace>" to
move
>'em out.
>
>...don't forget to ALTER INDEX ... REBUILD on any indexes associated
with
a
>moved table...
>
>
>
>Binh Pham wrote:
>>We are trying to migrate this TOOLS tablespace to ASSM and
encountering
an
>>issue.  There are objects (tables,indexes, materialized views, etc...)
>>owned
>>by SYSTEM that prevents us from changing this TOOLS tablespace to
ASSM.
>>
>>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
Live Hotmail.
http://imagine-windowslive.com/hotmail/?locale=en-us&ocid=TXT_TAGHM_migr
ation_HM_mini_pcmag_0507


--
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-l

Other related posts: