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