Transportable Tablespace
I am going to migrate some data from 8i to 10g. Using export and import seems a reasonable choice. However, considering that there are 70GB of data. The time to unload and reload the data, and the temporary disk space to hold the dump file seems to be avoided.
So, Transportable Tablespace seems a good idea…
Transportable Tablespace Export
-
Prepare the parameter file for Transportable Tablespace export
oracle@server8i:~/tts/schema_tts_export$ cat params_schema_tts_export.dat
userid=”internal as sysdba”
transport_tablespace=y
tablespaces=tablespace1,tablespace2
file=schema_tts_export.dmp
log=schema1_tts_export.log -
Set the tablespaces readonly
-
Perform Transportable Tablespace export
oracle@server8i:~/tts/schema_tts_export$ exp parfile=params_schema_tts_export.dat
Export: Release 8.1.7.0.0 - Production on Thu Jul 20 16:38:37 2006
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Password:
Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
JServer Release 8.1.7.0.0 - Production
Export done in UTF8 character set and UTF8 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata…
For tablespace TABLESPACE1 …
. exporting cluster definitions
. exporting table definitions
. . exporting table TABLE1
…
. end transportable tablespace metadata export
Export terminated successfully without warnings. -
Files generated
-
Transfer the data files to the intended location of the file
scp schema_tts_export.dmp oracle@server10g:/u03/dbupgrade/tts
scp /oracle/u03/oradata/sid8i/tablespace10* oracle@server10g:/u03/oradata/sid10g
scp /oracle/u04/oradata/sid8i/tablespace20* oracle@server10g:/u04/oradata/sid10g -
Reset the tablespaces to read write mode (optional)
-
Now that all segments (tables, indexes and LOB related objects) are transferred to the 10g database. Other objects must also be transferred.
-
Prepare the following parameter file to export all the objects, but not the data
oracle@server8i:~/tts/schema_tts_export$ cat params_schema_tts_other_export.dat
userid=system@sid8i
owner=schema1
indexes=n
rows=n
file=schema_tts_other_export.dmp
log=schema_tts_other_export.log -
Export the data and transfer the file schema_tts_other_export.dmp to 10g server
SQL> alter tablespace tablespace1 read only;
Tablespace altered.
SQL> alter tablespace tablespace2 read only;
Tablespace altered.
oracle@server8i:~/tts/schema_tts_export$ ls -l
total 320
-rw-r–r– 1 oracle oinstall 311296 Jul 20 16:38 schema_tts_export.dmp
-rw-r–r– 1 oracle oinstall 6889 Jul 20 16:38 schema_tts_export.log
-rw-r–r– 1 oracle oinstall 141 Jul 20 16:36 params_schema_tts_export.dat
Transportable Tablespace Import
-
Full backup the 10g database
-
If the tablespaces use different block sizes, configure additional pools
alter system set db_2k_cache_size=50m scope=both;
-
Create the users in the 10g database. Note that since the tablespaces are not available yet, no default tablespace and tablespace quota are defined.
SQL> CREATE USER SCHEMA1 IDENTIFIED BY SCHEMA1;
已建立使用者.
SQL> GRANT CREATE SESSION TO SCHEMA1;
順利授權.
SQL> GRANT CREATE TABLE TO SCHEMA1;
… -
Prepare the parameter file
[oracle@server10g tts]$ cat params_schema_tts_import.dat
userid=”sys@sid10g as sysdba”
tablespaces=tablespace1,tablespace2
transport_tablespace=y
datafiles=(
/u03/oradata/sid10g/tablespace101.dbf,
/u03/oradata/sid10g/tablespace102.dbf,
/u04/oradata/sid10g/tablespace201.dbf,
/u04/oradata/sid10g/tablespace202.dbf,
)
file=schema_tts_export.dmp
log=schema_tts_import.log -
Perform Transportable Tablespace import
[oracle@server10g tts]$ imp parfile=params_schema_tts_import.dat
Import: Release 10.2.0.1.0 - Production on Fri Jul 21 10:09:13 2006
Copyright © 1982, 2005, Oracle. All rights reserved.
Password:
Connected to: Oracle Database 10g Release 10.2.0.1.0 - Production
Export file created by EXPORT:V08.01.07 via conventional path
About to import transportable tablespace(s) metadata…
import done in UTF8 character set and UTF8 NCHAR character set
. importing SYS's objects into SYS
…
. importing SCHEMA's objects into SCHEMA
Import terminated successfully without warnings. -
Check and make sure all the segments are transferred to the 10g database. The following SQL should return the same result in both source and target tablespace,
select owner, segment_type, count(*) from dba_segments
where owner in ('SCHEMA')
group by rollup(owner, segment_type)
order by owner, segment_type -
Define the default tablespace and tablespace quota for the users:
SQL> ALTER USER SCHEMA
2 DEFAULT TABLESPACE TABLESPACE1
3 QUOTA UNLIMITED ON TABLESPACE1
4 QUOTA UNLIMITED ON TABLESPACE2;
已更改使用者. -
Import the other objects using the following paramter file. Error about duplicated objects (e.g., ORA-02264 and IMP-00015) can be ignored
[oracle@server10g tts]$ cat params_schema_tts_other_import.dat
userid=system@sid10g
full=y
rows=n
ignore=y
file=schema_tts_other_export.dmp
log=schema_tts_other_import.log -
Check that all objects are created and make sure everything is valid. Run the following SQL in both DB and make sure the result matches.
select owner, object_type, status, count(*)
from dba_objects
where owner in ('SCHEMA')
group by owner, rollup(object_type), status
order by owner, object_type, status - The imported tablespace is read only. But when you turn it read write…
SQL> alter tablespace tablespace1 read write;
alter tablespace tablespace1 read write
*
ERROR 在行 1:
ORA-12915: Cannot alter dictionary managed tablespace to read write - Never mind, it is possible to change a DMT to LMT…
SQL> exec dbms_space_admin.tablespace_migrate_to_local('TABLESPACE1');
BEGIN dbms_space_admin.tablespace_migrate_to_local('TABLESPACE1'); END;
*
ERROR 在行 1:
ORA-03245: Tablespace has to be dictionary managed, online and permanent to be
able to migrate
ORA-06512: at “SYS.DBMS_SPACE_ADMIN”, line 227
ORA-06512: at line 1 - OH NO!!! To turn the tablespace to be a LMT to change to Read Write. However, it also needs to be Read Write to be a LMT
-
Certified death…
(0)