14 Oct 08 Import New Schemas With Transportable Tablespaces
Unlike the other options provided by Oracle’s import and export utlity, Transportable Tablespace (TTS) import and export are more a physical copy than a logical one. It provides a fast transportation of a whole set of large tablespaces across databases. When we transport a whole schema into aother database, however, using TTS alone is not enough. Other steps must be taken to make sure the schema meta data as well as physical segments are imported into the target database.
We’ll use Oracle 10g’s data pump utility to demonstrate the steps and considerations when importing a brand new schema into a database.
Steps on source database
Identify tablespaces to be transported
In my databases, different schemas use different set of tablespaces. I know which tablespaces my schema uses, but for demonstration purpose, we can check if they’re all we need:
select tablespace_name, count(*) from dba_segments
where owner = ''
group by tablespace_name
order by 1;
It’s recommended to have a transport set check before exporting the tablespaces. We can do this with sys.DBMS_TTS package.
exec sys.dbms_tts.transport_set_check('ts1, ts2, ..., ts10', true)
select * from sys.transport_set_violations;
The second query above reports any violations you may have. A more strict check if done by specifying “true” for the third parameter in the transport_set_check procedure, as
exec sys.dbms_tts.transport_set_check('ts1, ts2, ..., ts10', true, true)
This third parameter instructs the procdure to do a full check, which is equivalent to specifying ‘Y’ for the expdp utility’s TRANSPORT_FULL_CHECK parameter. In my case, I got many errors after running the query on sys.transport_set_violations:
VIOLATIONS
--------------------------------------------------------------------------------
Constraint MYTAB1_FK between table MYUSER.MYTAB1 in tablespace TS1 and table MYUSER.MYTAB2 in tablespace SYSTEM
Constraint MYTAB3_FK between table MYUSER.MYTAB3 in tablespace TS1 and table MYUSER.MYTAB4 in tablespace SYSTEM
…
All of above violations were on my partitioned tables and materialized views, whose tablespaces are actually included in my transport set. Metalink Note:114915.1 details various situations when these errors will occur and how to correct it, but since I know I have a full set of tablespaces, I just ignored these errors. When I do the real export of the transportable tablespaces, I skipped the TRANSPORT_FULL_CHECK parameter, otherwise the export will quit on error
ORA-29341: The transportable set is not self-contained.
Export TTS and meta data
Now we actually start the process by exporting the transportable tablespaces from the source database.
The following are the steps to export the transportable tablespaces
alter tablespace ts1 read only;
alter tablespace ts2 read only;
...
While the tablespaces are in READ ONLY status, export the tablespaces with expdp utility on command line:
expdp exp_user@mydb dumpfile=mydb_ts.dmp logfile=mydb_ts.log transport_tablespaces=ts1,ts2,...
In addition to the tablespace export, I also export the schema’s meta data as I’ll need it to build the user’s data dictionary objects in the target database later.
expdp exp_user@mydb dumpfile=mydb_meta.dmp logfile=mydb_meta.log content=metadata_only schemas=myuser
Backup data files
While the tablespaces remain in READ ONLY status, backup the underlying data files to a backup machine or directly to the target location. In my case, since the schema I’m going to transfer occupies 90% of the total space of the source DB, I just did a cold backup of the database with all its files. One thing to note is that the cold backup must be taken while the tablespaces are still in the READ ONLY status. If you turn the tablespaces to be READ WRITE after exporting the TTS, and then take the cold backup, you’ll get
ORA-19722: datafile is an incorrect version
even if you didn’t make any write (you think) to the tablespace.
After you take the TTS and meta data dump, and after you get the data files backed up or copied out, you can turn the tablespaces READ WRITE on the source database:
alter tablespace ts1 read write;
alter tablespace ts2 read write;
...
Transport the Files
Now you need to transport the dump files and backed up data files to the target location. In my case, I transferred the dump files to the default DATA_PUMP_DIR location. I also restored only the data files for the transported tablespaces to the target database file directories.
On the target database
Create the user on the target database
We don’t have the schema user on the target database yet and it must be created first before the TTS import. As this user’s default tablespace as well as other tablespaces are not in there yet, I created it on the target database with default tablespace on an existing “USERS” tablespace with quota only on it.
create user myuser identified by mypass
default tablespace users temporary tablespace temp;
Then I granted all necessary system privileges and roles to it.
Import sequences
Many of the tables that are in my transportable tablespaces have triggers which refer to sequences. Sequences are data dictionary objects and are not included in the TTS export. So to avoid trigger compilcation errors when importing the TTS, I imported the sequences first:
impdp imp_user@tgtdb dumpfile=mydb_meta.dmp include=sequence schemas=myuser
Import TTS
Now is the core step, importing all the transportable tablespace datafiles:
impdp imp_user@mydb parfile=impdp_mydb_ts.par
The parameter file content is shown here:
# import.par
dumpfile=mydb_ts.dmp
logfile=impdp_mydb_ts.log
exclude=TABLE_STATISTICS
transport_datafiles=’/path1/ts1_01.dbf’,
‘/path1/ts1_02.dbf’,
…
‘/path5/ts10_03.dbf’
This step is actually fast, which is the beauty of transportable tablespace export and import. I excluded table statistics because my schema size is more than 100GB and importing statistics would take too long. I’d rather collect the stats after I finish everything else.
During this step, Oracle imports the tablespaces and segments on them into the target database. It also creates the triggers on the tables.
Change the user and tablespaces
Now we change the users default tablespace and quotas:
alter user myuser
default tablespace ts1
quota unlimited on ts1
quota unliimted on ts2
...
;
Change the tablespaces to be read write:
alter tablespace ts1 read write;
alter tablespace ts2 read write;
...
Re-import Foreign Keys
I encountered some errors with the foreign keys due to some settings on the source database. A workaround is to generate the SQL files from the meta data dump files:
impdp imp_user@mydb dumpfile=mydb_meta.dmp sqlfile=myuser_fk.sql include=ref_constraint schemas=myuser
The sql file myuser_fk.sql can be found in the default DATA_PUMP_DIR. I modified the sql file and recreated the foreign keys without problem.
Import other data dictionary objects
Now I import other data dictionary objects owned by the transported schema with my meta data dump:
impdp imp_user@mydb dumpfile=mydb_meta.dmp schemas=myuser include=package,view,type,synonym,dimension,db_link logfile=impdp_myuser_meta.log
If you have standalone procedures and functions, add ‘function’ and ‘procedure’ to the include parameter (case insensitive). Note, use DB_LINK instead of DATABASE_LINK to import database links. Also you must add tnsname entries on your target machine if you do have database link imported.
Import materialized views
When I import the TTS, all table and index segments, including those for materialized views, are imported into the target database. However, materialized views are by themselves data dictionary objects, which need to be imported too with the meta data dump:
impdp imp_user@mydb dumpfile=mydb_meta.dmp schemas=myuser include=materialized_view logfile=impdp_myuser_mviews.log
The import process may take long, but certainly faster than building the mview from scratch.
Import RUPD$ tables
So far, it looks like I have completed all objects import. However, comparing with the source database I found out the new schema still misses some RUPD$ tables, like RUPD$_MYTAB1. These tables are created when materialized view logs are created on primary keys together with the MLOG$ tables. Unlike MLOG$ tables, RUPD$ tables are stored in data dictionary than on the user tablespaces. To make it complete, I imported them like this:
impdp imp_user@mydb dumpfile=mydb_meta.dmp schemas=myuser include=table:\" like \'RUPD$%\'\" logfile=impdp_myuser_rupd_tab.log
Tags: Oracle data pump, transportal tablespace
Leave a Comment
