This is a pain in the ass. You try to import something from a newer database and you hit the error that your timestamp is not good enough! Even tho you have no timestamp datatypes in the dump…
[oracle@vrick19 ~]$ impdp system/oracle@localhost:1521/orclpdb directory=dir_oracle dumpfile=awranalyzer.dmp version=19.3
Import: Release 19.0.0.0.0 - Production on Sat Feb 10 19:12:07 2024
Version 19.19.0.0.0
Copyright (c) 1982, 2023, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
ORA-39002: invalid operation
ORA-39405: Oracle Data Pump does not support importing from a source database with TSTZ version 35 into a target database with TSTZ version 32.The whole case was covered by Mike Dietrich:
Let me show you my – a little bit unsupported – workaround 🙂
First of all we need to understand which process is actually opening our Data Pump file and does operations on it. We can do that, using bpftrace:
[root@vrick19 ~]# cat fopen.bpf
tracepoint:syscalls:sys_enter_openat {
if(str(args->filename) == "/home/oracle/awranalyzer.dmp") {
printf("%d %s \n", pid, comm);
}
}Now when we start this bpftrace script and run impdp in the different terminal, you will see the following result:
Attaching 1 probe...
53677 ora_dm00_orcl
53677 ora_dm00_orcl
53677 ora_dm00_orcl
53677 ora_dm00_orcl
53679 ora_dw00_orcl
53679 ora_dw00_orcl
53679 ora_dw00_orcl
53679 ora_dw00_orcl
According to our script, two processes are opening the file: DM00 which is a Data Pump master process and DW00 which is Data Pump worker process.
Both of those processes are not active all the time. They are created when the import starts, so we will have to catch their execution PIDs dynamically.
Let’s trace the processes with the following bpftrace script (this script was written on aarch64 architecture, so you will have to adjust the register name to your architecture):
uprobe:/u01/app/oracle/product/19.19/dbhome_1/bin/oracle:kksParseCursor /comm == "ora_dw00_orcl"/ {
printf("[SQL %s]: %s\n", comm, str(reg("r10")));
}
uprobe:/u01/app/oracle/product/19.19/dbhome_1/bin/oracle:kksParseCursor /comm == "ora_dm00_orcl"/ {
printf("[SQL %s]: %s\n", comm, str(reg("r10")));
}During the execution of this trace, you will see a query, being executed by a master process that starts with:
[SQL ora_dm00_orcl]: SELECT mta.guid,mta.job_mode,mta.start_time,mtb.start_time,NVL(Based on that we can find the actual SQL in V$SQL:
SELECT
mta.guid,
mta.job_mode,
mta.start_time,
mtb.start_time,
nvl(mta.object_path_seqno, 0),
mta.platform,
mtb.platform,
mta.value_t,
mtb.value_t,
mta.scn,
mta.degree,
mta.db_version,
mtb.db_version,
mta.job_version,
mta.timezone,
mtb.timezone,
nvl(mta.property, 0),
nvl(mtb.property, 0),
bitand(mta.flags, :1),
bitand(mtb.flags, :2),
mta.remote_link,
mtc.name,
mtd.name,
mtc.object_name,
mtd.object_name
FROM
"SYSTEM"."SYS_IMPORT_FULL_01" mta,
"SYSTEM"."SYS_IMPORT_FULL_01" mtb,
"SYSTEM"."SYS_IMPORT_FULL_01" mtc,
"SYSTEM"."SYS_IMPORT_FULL_01" mtd
WHERE
mta.process_order = :3
AND mtb.process_order (+) = mta.process_order + 1
AND mtc.process_order = :4
AND mtd.process_order (+) = mtc.process_order + 1If you want to check the actual values in the table, you have to stop worker or master process during its execution, because the table is being dropped when Data Pump process ends.
[SQL ora_dm00_orcl]: DROP TABLE "SYSTEM"."SYS_IMPORT_FULL_01" PURGEIn order to do that, we will use GDB to stop the process after it starts – we will choose a worker process, because we want a part of the job to be already done 😉
#/bin/bash
test pids && rm pids
mkfifo pids
while [ true ]; do
pid=`ps aux | grep ora_dw00_orcl | grep -v grep | awk '{print $2}'`
if [[ $pid -gt 0 ]]; then
echo $pid >> pids
exit 0
fi
doneTo above script will create a FIFO file and loop until it finds a worker process to put it into FIFO file.
From another session we will prepare GDB to catch the PID and attach to the worker process, stoping it.:
[oracle@vrick19 ~]$ p=`cat pids`; gdb -p $pThen you start impdp from the third session, "cat" will read a fifo file, passing PID to GDB and – in result – stoping the process.
Thanks to that, we will be able to examine the contents of SYS_IMPORT_FULL_01 table, and actually execute the query we found before, by passing bind variables found in V$SQL_BIND_CAPURE.
After a glance you will see, that timezone version is stored in a column, named: PROPERTY.
Let’s change the column to the desired value 😉
SQL> update system.SYS_IMPORT_FULL_01
2 set property=35
3 where property=32;
1 row updated.
SQL> commit;
Commit complete.So what will happen with our import, when we quit GDB, letting worker to continue?
[oracle@vrick19 ~]$ impdp system/oracle@localhost:1521/orclpdb directory=dir_oracle dumpfile=awranalyzer.dmp version=19.3
Import: Release 19.0.0.0.0 - Production on Sat Feb 10 20:56:47 2024
Version 19.19.0.0.0
Copyright (c) 1982, 2023, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@localhost:1521/orclpdb directory=dir_oracle dumpfile=awranalyzer.dmp version=19.3
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "AWRANALYZER"."TMP_DBA_HIST_OSSTAT" 3.959 MB 78624 rows
. . imported "AWRANALYZER"."PERF_INSTANCE_ACTIVITY" 2.540 MB 47756 rows
. . imported "AWRANALYZER"."PERF_FORGROUND_EVENTS" 359.5 KB 6368 rows
. . imported "AWRANALYZER"."TMP_DBA_HIST_SNAPSHOT" 257.9 KB 3024 rows
. . imported "AWRANALYZER"."PERF_TIME_MODEL" 120.3 KB 2022 rows
. . imported "AWRANALYZER"."PERF_LOAD_PROFILE" 110.7 KB 2205 rows
. . imported "AWRANALYZER"."CPU_DATA" 0 KB 0 rows
. . imported "AWRANALYZER"."PERF_HOST_CPU" 0 KB 0 rows
. . imported "AWRANALYZER"."PERF_WAIT_CLASS" 0 KB 0 rows
. . imported "AWRANALYZER"."SEGMENTS_PHYSICAL_READS" 0 KB 0 rows
. . imported "AWRANALYZER"."SQL_BY_CPU" 0 KB 0 rows
. . imported "AWRANALYZER"."SQL_BY_ELA" 0 KB 0 rows
. . imported "AWRANALYZER"."SQL_BY_IO_WAIT" 0 KB 0 rows
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
ORA-39082: Object type VIEW:"AWRANALYZER"."V_TMP_ASH" created with compilation warnings
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 1 error(s) at Sat Feb 10 21:03:55 2024 elapsed 0 00:07:08And voilà!