I’m currently working in a project to migrate a Datawarehouse-database from Solaris to Linux Intel (Endian-change). We do use the cross platform incremental backups method as described in My Oracle Support Note 1389592.1 for that.
\nI.e. incremental backups are applied to database files and during the migration the recovered datafiles are attached to the target database via the transportable tablespace method. When testing the transportable tablespace metadata import I got an error for a table:<\/p>\n
I.e. metadata of all tables in the tablespace were successfully imported except one. The reason for the error was a default-defintion of column “PROCESSING_TM” without a TO_DATE-casting. I.e. it relied on the NLS_DATE_FORMAT-setting. E.g.<\/p>\n There are different possibilities on why a metadata import fails for specific tables (see e.g. My Oracle Support Note 1082116.1 for another reason). The question was: How to restart the import without going through the whole process of copying datafiles from the source-database again? As we imported the metadata through a network-link the tablespace on the source database and the tablespace on the target database were still set to “READ ONLY”.<\/p>\n So first of all I fixed the error in the table definition on the source database:<\/p>\n That was possible even having the associated tablespace of the table on READ ONLY as it is just a metadata update and does not touch the tablespace.<\/p>\n On the target database I cleaned up the tablespace of the failed table metadata import:<\/p>\n It’s of course important to NOT specify “AND DATAFILES” when dropping the tablespace to avoid having to copy all files again.<\/p>\n In my case I actually transported 146 tablespaces and modified the impdp-parameterfile xttplugin.par to contain only the single tablespace, which contained failing tables. Of course you have to drop and include all tablespaces, which have tables with dependencies between each other. In my case that was not an issue as my tables had no dependencies to tables in other tablespaces.<\/p>\n Afterwards I could just restart my metadata-import with the single tablespace:<\/p>\n So to restart a failed transportable tablespace metadata import, just fix the root cause, drop the associated tablespace on the target database without dropping the datafiles and restart the import again. That becomes handy especially in cross platform incremental backup migration scenarios.<\/p>\n","protected":false},"excerpt":{"rendered":" I’m currently working in a project to migrate a Datawarehouse-database from Solaris to Linux Intel (Endian-change). We do use the cross platform incremental backups method as described in My Oracle Support Note 1389592.1 for that. I.e. incremental backups are applied to database files and during the migration the recovered datafiles are attached to the target […]<\/p>\n","protected":false},"author":35,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229,59],"tags":[1110,1403,96,940],"type_dbi":[],"class_list":["post-11456","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","category-oracle","tag-cross-platform-migration","tag-impdp","tag-oracle","tag-transportable-tablespaces"],"acf":[],"yoast_head":"\n
\nProcessing object type TRANSPORTABLE_EXPORT\/TABLE
\nORA-39083: Object type TABLE:\"DWHT\".\"TDWHTAB\" failed to create with error:
\nORA-01843: not a valid month
\nFailing sql is:
\nCREATE TABLE \"DWHT\".\"TDWHTAB\" (...,\"PROCESSING_TM\" DATE DEFAULT '01-01-0001' NOT NULL ENABLE,...
\n<\/code><\/p>\n
\nSQL> create table t1 (\"PROCESSING_TM\" DATE DEFAULT '01-01-0001' NOT NULL ENABLE);
\ncreate table t1 (\"PROCESSING_TM\" DATE DEFAULT '01-01-0001' NOT NULL ENABLE)
\n *
\nERROR at line 1:
\nORA-01843: not a valid month
\n
\nSQL> alter session set nls_date_format='dd-mm-yyyy';
\n
\nSession altered.
\n
\nSQL> create table t1 (\"PROCESSING_TM\" DATE DEFAULT '01-01-0001' NOT NULL ENABLE);
\n
\nTable created.
\n<\/code><\/p>\n
\nalter table \"DWHT\".\"TDWHTAB\" modify (\"PROCESSING_TM\" DATE DEFAULT TO_DATE('01-01-0001', 'DD-MM-YYYY'));
\n<\/code><\/p>\n
\ndrop tablespace DWH_DATA including contents;
\n<\/code><\/p>\n
\n$ cat xttplugin2.par
\ndirectory=DATA_PUMP_DIR
\nlogfile=tts_imp2.log
\nnetwork_link=ttslink
\ntransport_full_check=no
\ntransport_tablespaces=DWH_DATA
\ntransport_datafiles='\/u01\/DWH\/oradata\/DWH_DATA_001.dbf','\/u01\/DWH\/oradata\/DWH_DATA_002.dbf'
\n$ imdp parfile=.\/xttplugin2.par
\nPassword: \/ as sysdba
\n<\/code><\/p>\n