How to upgrade Oracle database to 12c Pluggable database (Multitenant) using Transportable tablespace(TTS) method

Database upgrade to 12c can be done in multiple ways. Commonly used methods are DBUA (Database upgrade assistant) and Manual upgrade. Both methods will directly upgrade the database to a higher version but upgrade to 12c non-CDB. To convert the database to Multitenant architecture we require additional actions which add extra downtime. Transportable tablespace (TTS) is one of the methods commonly used to upgrade the database to higher version due to its advantage. In 12c TTS upgrade, it doesn’t need an intermediate non-CDB database to upgrade to 12c Multitenant architecture.

Advantages of using TTS for Database upgrade

Below are some of the benefits of using the Transportable tablespace (TTS) method of upgrading to a 12c pluggable database.

• Lesser downtime comparing to DBUA and Manual upgrade
• Source database remains intact
• Cross platform upgrade is possible from 10g
• Bit conversion is possible
• TTS doesn’t need intermediate non-CDB database to upgrade lower version database to 12c Multitenant architecture

Prerequisites

Make sure you check for the below pre-requisites before starting the upgrade process:

• Perform TTS dependency check. Tablespaces should be self-contained. In case tablespace has Materialized views, ensure base tables are also getting transported.
• Make sure high speed network bandwidth available between source and target server. It will reduce the downtime.
• Check compatibility like Characterset, Timezone version between Source and Target database.
• Verify that source and target database don’t have same tablespace name.
• Verify that target database has schema of objects reside in tablespace which is getting imported.
• Default Data pump directory will not work for 12c PDB. Create a explicit directory for expdp and impdp.
• Ensure there are no Invalid objects in database.

Upgrade Steps

The high-level steps in upgradation process are as follows:

• Check user tablespace status. The tablespace and its associated datafile which are about Transport shoule be ONLINE without RECOVER status.
• Verify tablespaces are self-contained.
• Check characterset of source and 12c database. It can be same or 12c database characterset should be superset of source database.
• Create logical directory at source and database.
• Place the tablespace in read only.
• Export the metadata. Specify the tablespaces whose metadata should be transported.
• Copy export dump and datafiles to target 12c database home.
• Import the dump to 12c pluggable database. Specify datafile names along with location.
• Make the tablespace read write at source and 12c pluggable database.

The setup

1. Check the application tablespaces, datafile and status:

SQL> select tablespace_name, file_name from dba_data_files;
TABLESPACE_NAME FILE_NAME
--------------------------------------------------------------------------------
USERS /oradata/tbsupg/users01.dbf
UNDOTBS1 /oradata/tbsupg/undotbs01.dbf
SYSAUX /oradata/tbsupg/sysaux01.dbf
SYSTEM /oradata/tbsupg/system01.dbf
TTS_TBS01 /oradata/tbsupg/TTS_TBS01.dbf
TTS_TBS02 /oradata/tbsupg/TTS_TBS02.dbf

2. For testing purpose create tables and insert records in TTS_TBS01 and TTS_TBS02 tablespace:

SYS:tbsupg> create table tts_table1(n number) tablespace TTS_TBS01;
Table created.
SYS:tbsupg> create table tts_table2( name varchar2(20)) tablespace TTS_TBS02;
Table created.

SYS:tbsupg> declare
n number;
begin
for n in 1..1000 loop
insert into tts_table1 values(n);
insert into tts_table2 values ('Number is '||n);
end loop;
commit;
end; 
/
PL/SQL procedure successfully completed.

SYS:tbsupg> select count(*) from tts_table1;

COUNT(*)
----------
1000

SYS:tbsupg> select count(*) from tts_table2;

COUNT(*)
----------
1000

3. Check tablespace dependency for chosen tablespaces before performing TTS:

SYS:tbsupg> execute sys.dbms_tts.transport_set_check('TTS_TBS01,TTS_TBS02', true);
PL/SQL procedure successfully completed.

SYS:tbsupg> select * from sys.transport_set_violations;
no rows selected

4. Create physical directory and logical directory for exporting dump. Perform this step at source database server and 12c database server:

$ mkdir -p /oradata/tbsupg/exp_dump

SYS:tbsupg> create directory exp_dir as '/oradata/tbsupg/exp_dump';
Directory created.

5. If there are no violations, places the tablespaces in read only mode. The actual downtime starts here.

SQL> alter tablespace TTS_TBS01 read only;
Tablespace altered.

SQL> alter tablespace TTS_TBS02 read only;
Tablespace altered.

6. Export the tablespace metadata. Specify tablespace as argument:

$ expdp userid=\'sys/ as sysdba\' directory=exp_dir file=tbs_exp.dmp log=tba_exp.log transport_tablespace=y tablespaces=TTS_TBS01,TTS_TBS02

You will get below message at the end of expdp:

******************************************************************************
Dump file set for SYS.SYS_EXPORT_TRANSPORTABLE_01 is:
/oradata/tbsupg/exp_dump/tbs_exp.dmp
******************************************************************************
Datafiles required for transportable tablespace TTS_TBS01:
/oradata/tbsupg/TTS_TBS01.dbf
Datafiles required for transportable tablespace TTS_TBS02:
/oradata/tbsupg/TTS_TBS02.dbf

$ ls /oradata/tbsupg/exp_dump/
tbs_exp.dmp
tba_exp.log

7. Copy datafile, export dump to Target 12c server (Directory /u01/app/oracle/oradata/CDBTTS/datafile)

$ cp /oradata/tbsupg/TTS_TBS01.dbf /u01/app/oracle/oradata/CDBTTS/datafile/
$ cp /oradata/tbsupg/TTS_TBS02.dbf /u01/app/oracle/oradata/CDBTTS/datafile/

$ mkdir /u01/app/oracle/oradata/CDBTTS/exp_dmp
$ cp /oradata/tbsupg/exp_dump/* /u01/app/oracle/oradata/CDBTTS/exp_dmp/

$ ls -lrt /u01/app/oracle/oradata/CDBTTS/exp_dmp/
tbs_exp.dmp
tba_exp.log

8. Oracle 12c will have CDB and Pluggable database PDBTTS. Confirm that PDB has all Prerequisites in place

SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2         PDB$SEED MOUNTED
3         PDBTTS READ WRITE NO

9. Create Logical directory at database level for dump file. This step can be performed before placing datafile into read only at source database, so that downtime can be reduced.

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

SQL> create directory exp_dmp as '/u01/app/oracle/oradata/CDBTTS/exp_dmp'; 
Directory created.

10. Import the dump:

$ impdp userid=\'sys/sys@PDBTTS as sysdba\' directory=exp_dmp file=tbs_exp.dmp log=tba_imp.log transport_tablespace=y datafiles='/u01/app/oracle/oradata/CDBTTS/datafile/TTS_TBS01.dbf','/u01/app/oracle/oradata/CDBTTS/datafile/TTS_TBS02.dbf'

11. Verify the tablespace is imported into pluggable database:

$ sqlplus "/ as sysdba"
SQL> alter session set container=PDBTTS;
Session altered.

USERS SQL> select tablespace_name, file_name from dba_data_files;

TABLESPACE_NAME     FILE_NAME
--------------------------------------------------------------------------------
SYSTEM      /u01/app/oracle/oradata/CDBTTS/datafile/system01.dbf
SYSAUX      /u01/app/oracle/oradata/CDBTTS/datafile/sysaux01.dbf
USERS       /u01/app/oracle/oradata/CDBTTS/datafile/users01.dbf
TTS_TBS01 /u01/app/oracle/oradata/CDBTTS/datafile/TTS_TBS01.dbf
TTS_TBS02 /u01/app/oracle/oradata/CDBTTS/datafile/TTS_TBS02.dbf

12. Make the tablespaces are in read write mode. The Downtime ends here.

SQL> alter tablespace TTS_TBS01 read write;
Tablespace altered.

SQL> alter tablespace TTS_TBS02 read write;
Tablespace altered.

13. Verify the data in the 2 tables:

SQL> select count(*) from tts_table1;
COUNT(*)
----------
1000

SQL> select count(*) from tts_table2;
COUNT(*)
----------
1000