HOL 19c Migrate FTEX

« PREVIOUS «

In this part of the Hands-On Lab you will migrate the FTEX database directly into a PDB2 using Full Transportable Export/Import, an extension of Transportable Tablespaces where Data Pump takes over the manual work you’ve had to with plain TTS. It works cross-platform and cross-Endianness with at least an Oracle 11.2.0.3 source database.

In the case of the lab, you will move an Oracle 11.2.0.4 database, FTEX, directly into an 19c PDB2 which is part of CDB2.

It is important to mention that this feature works cross-platform and cross-Endianness!

Index

1. Create a new PDB2
2. Prepare FTEX
3. Migrate FTEX into PDB2
4. Migration to Oracle Cloud or Cloud Machine

1. Create a new PDB2

At first, as with every other Transportable Tablespace migration, we need to create a database – or in our case, a PDB – as target as first.

. cdb2
sqlplus / as sysdba

create pluggable database PDB2 admin user adm identified by adm file_name_convert=('pdbseed', 'pdb2');

The admin user needs to exist when a PDB gets created. You can delete it later on if necessary.

Once the PDB2 is created you need to start it up and create some additional objects for the migration:

alter pluggable database PDB2 open;
alter pluggable database PDB2 save state;
alter session set container=PDB2;

create directory mydir as '/u02/oradata/CDB2/mydir';
grant read, write on directory mydir to system;
create public database link SOURCEDB connect to system identified by oracle using 'FTEX';
exit

We will use the database link to allow Data Pump pulling all information via the database link. The task can be done without the database link but then two operations are necessary, an expdp and an impdp.

2. Prepare FTEX

Before we can transport anything, we need to prepare the FTEX database.

First of all, you need to start it up. Then you will switch the one tablespace we’ll migrate to Read-Only:

. ftex
sqlplus / as sysdba

startup
alter tablespace USERS read only;
exit

3. Migrate FTEX into PDB2

At first of course you need to transfer the file as well to the new environment:

cp /u02/oradata/FTEX/users01.dbf /u02/oradata/CDB2/pdb2

Then you need to initiate the transport operation. In this case Data Pump will take over the usual manual steps from Transportable Tablespaces. The keywords TRANSPORTABLE=ALWAYS and FULL=Y advice Data Pump to use the Full Transportable Export/Import feature. VERSION=12 is needed as we use an 11g database as source.

. cdb2

impdp system/oracle@pdb2 network_link=sourcedb version=12 full=y transportable=always metrics=y exclude=statistics directory=mydir logfile=pdb2.log transport_datafiles='/u02/oradata/CDB2/pdb2/users01.dbf'

Once the operation is completed (it takes between 2 and 3 minutes) you can shutdown FTEX.

You’ll find some error messages. This particular one can be safely ignored as the object belongs to Advanced Replication:

W-1 Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
ORA-39083: Object type PROCACT_SYSTEM failed to create with error:
ORA-04042: procedure, function, package, or package body does not exist

Failing sql is:
BEGIN
SYS.DBMS_UTILITY.EXEC_DDL_STATEMENT('GRANT EXECUTE ON DBMS_DEFER_SYS TO "DBA"');COMMIT; END;

W-1      Completed 4 PROCACT_SYSTEM objects in 25 seconds

ONLY in case copy/paste does not work correctly, there’s a prepared file with all the parameters in /home/oracle/IMP. Use “impdp parfile=/home/oracle/IMP/ft.par” instead.

. ftex
sqlplus / as sysdba

alter tablespace users read write;
exit

You can now connect to the migrated PDB with:

. cdb2 sqlplus "system/oracle@PDB2"

show con_id
show con_name
exit

Note:
You have to switch into the 19c environment to do this. If you’ll execute the same “show” commands from the 11.2 SQL*Plus, you will receive errors.

4. Migration to Oracle Cloud or Cloud Machine

This feature can be used of course to migrate to the Oracle Cloud or ExaCC machines. See a real-time video here:
How to migrate an entire database with Full Transportable Export/import to the Oracle DBCS Cloud