Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to use Export Transportable Tablespaces

RE: How to use Export Transportable Tablespaces

From: Tim Sawmiller <sawmillert_at_state.mi.us>
Date: Wed, 07 Feb 2001 05:17:09 -0800
Message-ID: <F001.002AD72C.20010207043615@fatcity.com> 






Filtering attachments is a reasonable precaution, I guess.  Here is the pasted script:



#!/bin/ksh

# This script is used to transport tablespaces from STAGE database to CARS.

# It needs two parameters as "From DB" and "To DB".

#

# Created :  2/16/2000  by Pawan Sanwal (Whittman-Hart)

#

# This script need to run from the same machine where both databases exist.

#

# Exit if not all the arguments supplied.




if [ "$1x" = "x" ] || [ "$2x" = "x" ]; then
  echo ;


  echo "Usage :  transport.sh <From Stage Oracle SID> <To Cars Oracle SID>";
  echo ;


  exit 1;


fi



#

# The tablespaces should always be transported from Stage to Cars databases.

#




if [ `echo $1|grep stage >/tmp/log 2>/tmp/err; echo $?` -eq 1 ] ||


   [ `echo $2|grep cars >/tmp/log 2>/tmp/err; echo $?` -eq 1 ]; then
  echo ;


  echo "Usage :  transport.sh <From Stage Oracle SID> <To Cars Oracle SID>";
  echo ;


  exit 1;


fi
  


export ORACLE_SID=$2


#

# Ask for system user passwords on both databases.

#



echo 


echo "Enter system user password for "$2" :\c"
echo  [8m"\c"


read secondpwd


echo  [0m


echo "Enter system user password for "$1" :\c"
echo  [8m"\c"


read firstpwd


echo  [0m



#

# Gather datafiles information for the transportable tablespaces, viz.

# tablespaces stage_haines_data and stage_haines_index.

#



ls -1 /u0??/oradata/$1/stage_haines*data*dbf >/tmp/temp1 2>/tmp/err
ls -1 /u0??/oradata/$1/stage_haines*index*dbf >>/tmp/temp1 2>/tmp/err
ls -1 /u0??/oradata/$2/stage_haines*data*dbf >/tmp/temp2 2>/tmp/err
ls -1 /u0??/oradata/$2/stage_haines*index*dbf >>/tmp/temp2 2>/tmp/err





#

# Exit if this script is being run from different machine than where databases

# exist.

#




if [ `cat /tmp/temp1|grep "stage_haines" >/tmp/log 2>/tmp/err; echo $?` -eq 1 ] ||


   [ `cat /tmp/temp2|grep "stage_haines" >/tmp/log 2>/tmp/err; echo $?` -eq 1 ]; then
   echo "The databases do not exist on this machine. Please run the script";
   echo "from the machine where databases exist.";
   echo ;


   exit 1;


fi


paste /tmp/temp1 /tmp/temp2 >/tmp/copy_temp.sh
sed 's/^/cp /' /tmp/copy_temp.sh >/tmp/copy_files.sh
sqlplus -s  system/$secondpwd@$2 <<!


set pages 500


set lines 200


set head off


set feedback off


set verify off


set term off


drop tablespace stage_haines_data including contents;
drop tablespace stage_haines_index including contents;
!


export ORACLE_SID=$1


sqlplus -s  system/$firstpwd@$1 <<!


set pages 500


set head off


set term off


execute sys.dbms_tts.transport_set_check('STAGE_HAINES_DATA,STAGE_HAINES_INDEX',TRUE);
spool /tmp/violations


select * from sys.transport_set_violations;
spool off


!


nohup grep "no rows selected" /tmp/violations.lst >>/dev/null
if [ $? = 1 ]; then


  echo "Tablespaces are not self-contained... exiting...";
  exit 1;


fi



#

# Alter the tablespaces in STAGE database to read only mode.

#




export ORACLE_SID=$1


sqlplus -s  system/$firstpwd@$1 <<!


set pages 500


set head off


set term off


set verify off


set feedback off


alter tablespace stage_haines_data read only;
alter tablespace stage_haines_index read only;
!



# 

# Export the tablespaces metadata from the Stage database.

#




exp userid=stage_user/stage_user@$1 file=/tmp/stage.dmp transport_tablespace=y 
tablespaces=stage_haines_data, stage_haines_index triggers=n constraints=n



#

# Copy the datafiles for the transportable tablespaces to the CARS directoies.

#




chmod 777 /tmp/copy_files.sh


/tmp/copy_files.sh



#

# Alter the tablespaces in STAGE database read write.

#




sqlplus -s  system/$firstpwd@$1 <<!


set pages 500


set head off


set term off


set verify off


set feedback off


alter tablespace stage_haines_data read write;
alter tablespace stage_haines_index read write;
!



# 

# Import the tablespaces metadata into the CARS database.

#




export ORACLE_SID=$2


imp userid=system/$secondpwd@$2 file=/tmp/stage.dmp transport_tablespace=y 
datafiles=`cat /tmp/temp2`


#

# As stage user grant SELECT privilege to stage_user_role on the transported 

# objects in the cars database.

#



sqlplus -s  system/$secondpwd@$2 <<!


set pages 500


set head off


set term off


set verify off


set feedback off


col password new_value oldpswd noprint


col tmppassword new_value tmppswd noprint
col loweruser new_value lower_user noprint



select lower(username) loweruser, password
from dba_users


where username = UPPER('stage');



create user "&lower_user" identified by temp123;
select password tmppassword from dba_users
where username = '&lower_user';


drop user "&lower_user";


alter user stage identified by values '&tmppswd';



connect stage/temp123@$2

grant select on stage.haines_building_dta to stage_user_role;
grant select on stage.haines_res_bus_dta to stage_user_role;
grant select on stage.haines_transfer_dta to stage_user_role;
grant select on stage.haines_work_site_land_dta to stage_user_role;


alter user stage identified by values '&&oldpswd';
!




>>> Glenn.Travis_at_wcom.com 02/06/01 04:38PM >>>
Could you include the file in your message.  The list strips non-text
attachments.


> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Tim
> Sawmiller
> Sent: Tuesday, February 06, 2001 3:47 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: How to use Export Transportable Tablespaces
>
>
> Exporting Tablespaces is not achieved using fromuser/touser.  The
> entire tablespace is transported, no matter whose objects are in
> it.  Actually, just the meta data is exported and imported.  The
> other step is to physically copy the files supporting the
> tablespace to a new location and attaching them to the the target
> database.  See attached file for an example.
>
>
>
> >>> Jim.Conboy_at_trw.com 02/06/01 02:10PM >>>
> There are straightforward examples in the 8.1.7 documentation.  I
> don't know if that feature is available in 8.1.6.  But
> regardless, you should investigate WHY the fromuser/touser didn't
> achieve the desired results or I fear you'll be disappointed again.
>
> Jim
>
> >>> ltaylor_at_iq.com 02/06/01 12:22PM >>>
>
> Does anyone have an example of how to use transportable tablespaces with
> export/import.
>
> I want export a user (8.1.6 database) source and import fromuser
> touser(8.1.6 database) target.
>
> I tried to import fromuser/ touser, but many contraints were not
> created and
> lost some data
> so I was told to try transportable tables. Can't find any good examples on
> how to use this new
> feature.
>
> Can someone please help me.
>
> Thanks
> Larry
>
>
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com 
> --
> Author: Larry Taylor
>   INET: ltaylor_at_iq.com 
>
> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California        -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com 
> --
> Author: Jim Conboy
>   INET: Jim.Conboy_at_trw.com 
>
> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California        -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
>
>




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Glenn Travis
  INET: Glenn.Travis_at_wcom.com 

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Sawmiller
  INET: sawmillert_at_state.mi.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).


Received on Wed Feb 07 2001 - 07:17:09 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US