Roy and I blogged about Full Transportable Export/Import in the past:
- Full Transportable Export/Import White Paper
- Full Transportable Export/Import – Things to Know
- Full Transportable Export/Import – Migrating an 11.2.0.4 database to Oracle Database 12c- into the Oracle Cloud
- Exclude Stats differently for TTS and FTEX
If you haven’t heard of this database feature, it allows you to migrate a full database by using Transportable Tablespaces as a base technology but letting Data Pump do all the manual steps for you in a one-command migration. And if needed, it works with RMAN Incremental Backups as well in order to decrease the downtime for copying larger databases. It works cross-platform, cross-Endianness – and as early as with a source version of Oracle Database 11.2.0.3 – but only towards Oracle Database 12c.
Often there are questions regarding the correct syntax for the parameter files. As I have to look it up from time to time as well I thought I’d share some example par files as there are two different approaches: With or without NETWORK_LINK.
With expdp/impdp – without NETWORK_LINK
expdp.par
directory=oradump dumpfile=wspmst_ftex_exp.dmp logfile=wspmst_ftex_exp.log transportable=always full=y metrics=y logtime=all exclude=table_statistics,index_statistics
impdp.par
directory=oradump dumpfile=wspmst_ftex_exp.dmp logfile=wspmst_ftex_imp.log metrics=y logtime=all TRANSPORT_DATAFILES='/oradata/wspmst/data/WSPMSTCON/3ABC7F153DB55A4DE0539ED0C80A102D/datafile/advsecmon.331.880913493' TRANSPORT_DATAFILES='/oradata/wspmst/data/WSPMSTCON/3ABC7F153DB55A4DE0539ED0C80A102D/datafile/advsecmon.555.880923697'
Only impdp (no expdp) – using NETWORK_LINK
2 examples here – one dealing with an Oracle 11.2 source database, the other moving directly from Oracle 12c non-CDB into a pluggable database (PDB).
impdp1.par
impdp mike/passwd@v121 NETWORK_LINK=v112 FULL=Y TRANSPORTABLE=ALWAYS VERSION=12 METRICS=Y exclude=table_statistics,index_statistics LOGTIME=ALL LOGFILE=ftex_dir:v112fullimp.log TRANSPORT_DATAFILES='/oracle/DQ1/sapdata50/ts1.dbf' TRANSPORT_DATAFILES='/oracle/DQ1/sapdata50/ts2.dbf'
impdp2.par
impdp system/oracle@pdb2
network_link=sourcedb
full=y
transportable=always
metrics=y
exclude=table_statistics,index_statistics
directory=mydir logfile=pdb2.log
transport_datafiles='/u02/oradata/CDB2/pdb2/users01.dbf'
transport_datafiles='/u02/oradata/CDB2/pdb2/users02.dbf'
transport_datafiles='/u02/oradata/CDB2/pdb2/users03.dbf'
Please note:
- The VERSION=12 option must be used only when an Oracle 11.2 database is the source as in this case Data Pump on the 11.2 side must be triggered to access this 12c feature and create an export in a form the 12c Data Pump can understand.
- There’s no expdp necessary when using Data Pump over a database link (NETWORK_LINK). But in this case you will need the keywords FULL=Y and TRANSPORTABLE=ALWAYS as export parameters as the export portion of Data Pump on the source side will be triggered underneath the covers.
–Mike
Hi Mike,
i’ve used, full=y and VERSION=12 during export, but import is failing at
Processing object type DATABASE_EXPORT/PASSWORD_VERIFY_FUNCTION
ORA-31684: Object type PASSWORD_VERIFY_FUNCTION already exists
ORA-39083: Object type PROFILE:”PROFILE_CMPLX” failed to create with error:
ORA-07443: function VERIFY_FUNCTION_11G not found
Failing sql is:
CREATE PROFILE “PROFILE_CMPLX” LIMIT COMPOSITE_LIMIT UNLIMITED SESSIONS_PER_USER UNLIMITED CPU_PER_SESSION UNLIMITED CPU_PER_CALL UNLIMITED LOGICAL_READS_PER_SESSION UNLIMITED LOGICAL_READS_PER_CALL UNLIMITED IDLE_TIME UNLIMITED CONNECT
_TIME UNLIMITED PRIVATE_SGA UNLIMITED FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LIFE_TIME 7776000/86400 PASSWORD_REUSE_TIME 15552000/86400 PAS
ORA-39083: Object type PROFILE:”SESSION_LIMIT_FSP” failed to create with error:
ORA-07443: function PASSWD_VARIFICATION_–snipped– not found
the function existed starts with “PASSWORD”, whereas function not found is starting with “PASSWD”.
source is 11203, target is 12102.
source is used for goldengate bi-directional replication, is there anything else we need to consider during import ? target will also be used for bi-directional replication post cutover.
Regards,
Samad
Hi Samad,
could you please log an SR for this, upload the logs and share the SR number with me?
Cheers,
Mike