datapump跨数据库迁移数据时，提示”ORA-39002: invalid operation”错误

ORA-38818

[oracle@testos:/home/oracle]$ oerr ora 38818
38818, 00000, "illegal reference to editioned object %s.%s"
// *Cause:  An attempt was made to violate the rule "A noneditioned object
//          may not depend on an editioned object."
// *Action: Either make this object editioned; or do not make the illegal
//          reference.
[oracle@testos:/home/oracle]$

ORA-38818 iilegal reference to editioned object a.bbb

背景

数据库12.1

场景：
db 中2个用户： a和b。

目标：用户a要访问b中的视图，而且已经能够在a中通过 select * from b.bview查询到数据

此时想 在a中建同义词 create synonym bview for b.bview; 失败，报错 ORA-38818 iilegal reference to editioned object a.bbb

原因&解决

b用户启用了版本控制 (editions enabled)，而a没有启用。

故只需要对a同样启用版本控制:

alter user a enable editions;

参考

ORA-38818: illegal reference to editioned object APPS.PARAMOBJECT while creating nested table. — oracle-tech

ORA-39002: invalid operation during datapump TZ version Difference

最近有个datapump跨数据库迁移数据时，提示”ORA-39002: invalid operation”错误， 排除过目录文件权限和语法兼容问题，后来发现是目标库的Timezone Version低于源库的TZ version. 需要升级目标库的TZ VERSION，这里简单记录该问题.

$ impdp xxx@xxx directory=xxx  dumpfile=xxx.dmp logfile=xxx.log table_exists_action=replace 
Import: Release 18.0.0.0.0 - Production on Tue Mar 22 17:40:58 2022
Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
ORA-39002: invalid operation

官方文档Oracle Database Globalization Support Guide 对应的版本, 4.7 Upgrading the Time Zone File and Timestamp with Time Zone Data 章节有记录如何升级，可以使用utltz_* Scripts或DBMS_DST Package 完成升级。

-- cdb$root
SQL> SELECT * FROM v$timezone_file;

FILENAME		VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_31.dat 	     31 	 0

SQL> alter session set container=pdbanbob;
Session altered.

SQL> SELECT * FROM v$timezone_file;
FILENAME		VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_18.dat 	     18 	 0


SQL> col PROPERTY_VALUE for a30
SQL> col PROPERTY_NAME for a50
SQL> r
  SELECT property_name, property_value
   FROM database_properties
   WHERE  property_name LIKE 'DST_%'
   ORDER BY property_name

PROPERTY_NAME					   PROPERTY_VALUE
-------------------------------------------------- ------------------------------
DST_PRIMARY_TT_VERSION				   18
DST_SECONDARY_TT_VERSION			   0
DST_UPGRADE_STATE				   NONE

SQL> SELECT * FROM v$timezone_file;

FILENAME		VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_31.dat 	     31 	 0


SQL> alter session set container=pdbanbob;

Session altered.

SQL> SELECT * FROM v$timezone_file;

FILENAME		VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_18.dat 	     18 	 0


SQL> col PROPERTY_VALUE for a30
SQL> col PROPERTY_NAME for a50
SQL> r
  SELECT property_name, property_value
   FROM database_properties
   WHERE  property_name LIKE 'DST_%'
   ORDER BY property_name

PROPERTY_NAME					   PROPERTY_VALUE
-------------------------------------------------- ------------------------------
DST_PRIMARY_TT_VERSION				   18
DST_SECONDARY_TT_VERSION			   0
DST_UPGRADE_STATE				   NONE


SQL> SELECT DBMS_DST.get_latest_timezone_version FROM dual;

GET_LATEST_TIMEZONE_VERSION
---------------------------
			 31

  1  Select con_id,name,value$ from containers(props$) where name='DST_PRIMARY_TT_VERSION'
  2* order by 1

    CON_ID NAME 			  VALUE$
---------- ------------------------------ ----------
	 1 DST_PRIMARY_TT_VERSION	  31
	 5 DST_PRIMARY_TT_VERSION	  18
	 6 DST_PRIMARY_TT_VERSION	  18
	 8 DST_PRIMARY_TT_VERSION	  18
	 9 DST_PRIMARY_TT_VERSION	  18
	10 DST_PRIMARY_TT_VERSION	  18
...

Note:
注意CDB是31，但是PDB 还是18，之前的升级并不完整。

解决方法：
升级低版本的Timezone version

SQL> shutdown immediate
SQL> startup upgrade
SQL> exec dbms_dst.begin_upgrade(31)
SQL> shutdown immediate
SQL> startup

for pdb

Updating the RDBMS DST version of the CDB will not change the RDBMS_DST version of the PDB’s in this CDB.
Updating the RDBMS DST version of a PDB will not change the RDBMS_DST version of the other PDB’s or the CDB.
When creating a new PDB the RDBMS DST version of new PDB is the RDBMS DST version of PDB
SEED The RDBMS_DST version of PDBSEED is the RDBMS_DST version at the CDB creation time (default is DSTv18 for 12.1.0.2 and 12.1.0.1).
The RDBMS DST version of PDB$SEED can currently not be updated.
From 12cR2 onwards, timezone upgrade scripts are included in the target ORACLE_HOME under rdbms/admin directory.
Then set the below to login to PDB.
alter session set container = <pdb_name>;
If pdb name is pdb1, then check below.
alter session set container = pdb1;
Perform the Prepare Window and Upgrade Window in PDB.

Prepare Window

$ cd $ORACLE_HOME/rdbms/admin

SQL> Show con_name

SQL> show con_id

SQL>
SQL> @utltz_countstats.sql
SQL> @utltz_countstar.sql
exec dbms_scheduler.purge_log;
select count(*) from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
select count(*) from SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

-- check the data retention period of the stats
-- the default value is 31
select systimestamp - dbms_stats.get_stats_history_availability from dual;

-- disable stats retention
exec dbms_stats.alter_stats_history_retention(0);

-- remove all the stats
exec DBMS_STATS.PURGE_STATS(systimestamp);

-- check the result of the purge operation
select count(*) from SYS.WRI$_OPTSTAT_HISTGRM_HISTORY;
select count(*) from SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY;

exec dbms_stats.alter_stats_history_retention(31);

Upgrade Window

Run the utltz_upg_check.sql script from the $ORACLE_HOME directory:

spool utltz_upg_check.log 
@utltz_upg_check.sql 
spool off

Run the utltz_upg_apply.sql script from the $ORACLE_HOME directory after the utltz_upg_check.sql script is executed successfully: This script utltz_upg_apply.sql will shutdown and bring up the database two times.

spool utltz_upg_apply.log
 @utltz_upg_apply.sql 
spool off