Step 1: Determine if Platforms are Supported and Endianness
Just Ignore his step if you are transporting the tablespace set between same platform.
SELECT d.PLATFORM_NAME, ENDIAN_FORMAT
FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d
WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
Step 2: Pick a Self-Contained Set of Tablespaces
You can ignore this step if we are sure that tablespaces we import has no dependency to other tablespace we omit. In this context "self-contained" means that there are no references from inside the set of tablespaces pointing outside of the tablespaces. Such as,
An index inside the set of tablespaces is for a table outside of the set of tablespaces.
A partitioned table is partially contained in the set of tablespaces.
A referential integrity constraint points to a table across a set boundary.
A table inside the set of tablespaces contains a LOB column that points to LOBs outside the set of
tablespaces.
EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('data01,data02,indx01,data03,COLUMBIA01,DATA02_16K_TBS', TRUE);
After invoking this PL/SQL package, you can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the set of tablespaces is self-contained, this view is empty.
SELECT * FROM TRANSPORT_SET_VIOLATIONS;
Step 3: Generate a Transportable Tablespace Set
1.Make all tablespaces in the set you are copying read-only.
ALTER TABLESPACE DATA01 READ ONLY;
and …..
2.Invoke the Data Pump export utility on the host system and specify which tablespaces are in the transportable set.
EXPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir
TRANSPORT_TABLESPACES = data01,data02,indx01,data03,COLUMBIA01,DATA02_16K_TBS
Step 4: Transport the Tablespace Set
Transport both the datafiles and the export file of the tablespaces to a place that is accessible to the target database.We can use just scp command.
Step 5: Import the Tablespace Set
1.Import the tablespace metadata using the Data Pump Import utility, impdp:
IMPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir
TRANSPORT_DATAFILES=
/oradata2/1_101.dbf,/oradata2/2_201.dbf
We can use parameter file if lots of data files are here.
Like,
IMPDP system/password PARFILE='par.f'
where the parameter file, par.f contains the following:
DIRECTORY=dpump_dir
DUMPFILE=expdat.dmp
TRANSPORT_DATAFILES="'/oradata2/1_101.dbf','/oradata2/2.dbf'"
2.Put the tablespaces into read/write mode as follows:
ALTER TABLESPACE Data01 READ WRITE;
and ….
Script
set heading off
set verify off
set feedback off
set echo off
spool tablespace_readonly.sql
select 'alter tablespace ' || tablespace_name || ' read only ;' column_tablespace from dba_tablespaces where
tablespace_name not
in ('SYSTEM', 'SYSAUX','UNDOTBS1','TEMP');
/
spool off
set heading on
set verify on
set feedback on
set echo on
@tablespace_readonly.sql
create directory trans as '/oradata2';
EXPDP system/sistem directory=trans dumpfile=meta_data.dmp TRANSPORT_TABLESPACES =
data01,data02,indx01,data03,COLUMBIA01,DATA02_16K_TBS
select 'scp ' || file_name || ' oracle@&hostname:&pathname ' from dba_data_files where tablespace_name not
in ('SYSTEM', 'SYSAUX','UNDOTBS1','TEMP');
In destination,
create a par_imp.f and input it by,
DIRECTORY=dpump_dir
DUMPFILE=meta_data.dmp
TRANSPORT_DATAFILES="'/oradata2/1_101.dbf','/oradata2/2.dbf'"
Now invoke,
IMPDP system/sistem parfile=par_imp.f
No comments:
Post a Comment