ANBOB™

前几日在做oracle 迁移升级使用FTTS 从11.2.0.4 升级到19c pdb时，因为对象包含数据类型TimeStamp with Local Time Zone(TSLTZ )， 导入matadata过程中报错ORA-39360而终止，报错如下:
Full Transportable tablespace import fails with:
ORA-39360: Table “<<owner>>”.”<<<table name>>” was skipped due to transportable import and TSLTZ issues resulting from time zone mismatch.

传输表空间(TTS)没有办法hold住不同的timezone 版本之间的数据库导入，通过 V$TIMEZONE_FILE.version可以查询timezone版本。可传输表空间模式下时区文件版本的注意事项 ：

Considerations for Time Zone File Versions in Transportable Tablespace Mode

Restriction : Jobs performed in transportable tablespace mode have the following requirements concerning time zone file versions: If the source is Oracle Database 11g release 2 (11.2.0.2) or later and there are tables in the transportable set that use TIMESTAMP WITH TIMEZONE (TSTZ) columns, then the time zone file version on the target database must exactly match the time zone file version on the source database.

If the source is earlier than Oracle Database 11g release 2 (11.2.0.2), then the time zone file version must be the same on the source and target database for all transportable jobs regardless of whether the transportable set uses TSTZ columns.

If these requirements are not met, then the import job aborts before anything is imported. This is because if the import job were allowed to import the objects, there might be inconsistent results when tables with TSTZ columns were read.

ORA-39339: Table string was skipped due to transportable import and TSTZ issues resulting from time zone version mismatch.
Cause: The source database is at a different time zone version than the target database and this table contains TIMESTAMP WITH TIME ZONE data.

Action: Convert the target database to the same time zone version as the source database or use Oracle Data Pump with conventional data movement to export then import this table.

上面是关于TSTZ 列类型，本地TTS过程发现是TSTZ导入顺利，但TSLTZ的对象未导入，需要单独处理。可以尝试exclude这些带TSLTZ的对象，而当时时间紧认为是FXTTS存在缺陷，重导metadata改用TTS， 但是TTS再次直接导入时提示表空间已存在，因为上次的FXTTS impdp时已导入了部分数据，如何再继续呢？

执行下面的命令删除表空间继续导入tablespace metadata.:

drop tablespace ttstbs including contents keep datafiles;

上面的命令只会从目标数据库中删除表空间元数据条目，以便可以重新导入。请注意，此命令不会从目标位置删除数据文件。

再次执行导入

Oracle Development declared that it as an “Expected Behavior”
Starting from 12.1, during the TTS import operation, the tablespaces (datafile headers) are put into read-write mode intermittently in order to fix up TSTZ table columns and clean up unused segments in the datafiles.
This functionality was implemented based on many Customer’s request.  And, hence, this cannot be reversed.  Note that, it intermittently only changes the status to “read-write” and the final status will still be “read-only” only.

Note:

in TTS Import, the final status of tablespace is set to READ-ONLY though it intermittently changes the status to READ-WRITE.
Whereas, in FULL-TTS Import, Oracle by default sets the final status of tablespaces to READ-WRITE once the import operation is completed.

检查带有TIME ZONE的对象

select c.owner || '.' || c.table_name || '(' || c.column_name || ') -' || c.data_type || ' ' col 
from dba_tab_cols c, dba_objects o 
where c.data_type like '%TIME ZONE' 
and c.owner=o.owner 
and c.table_name = o.object_name 
and o.object_type = 'TABLE' 
order by col
/

打赏

微信扫一扫，打赏作者吧～