Upgrade with unplug and plug Pluggable Database PDB in Oracle
Upgrade the pluggable database from 12.1.0.1 to 12.2.0.1 by unplug and plug into new version of Database.
Note: For Pre-upgrade utility i included the oracle java path in environment variables
--New Home 12.2 version
Set ORACLE_HOME=C:\oracle12c\product\12.2.0\dbhome_1
SET PATH=%ORACLE_HOME%\bin;%ORACLE_HOME%\java\bin;%PATH%
SET ORACLE_SID=ORCL122
--Old Home 12.1 version
Set ORACLE_HOME=C:\oracle\12.1.0\dbhome_1
SET PATH=%ORACLE_HOME%\bin;%ORACLE_HOME%\java\bin;%PATH%
SET ORACLE_SID=ORCL
Fix the error before starting upgrade of PDB on new version
ERROR at line 1: ORA-65346: The PDB version is lower and components (APEX) are missing in CDB.
Remove apex from the PDB database before going to start the plug and unplug upgrade process with CDB.
In 12.2.0.1 is not having installed apex as older version. So you remove the apex before unplug the PDB from 12.1.0 version. otherwise while creating the PDB on 12.2 version you will get this error.
Remove process as follow the link: Remove APEX from CDB
Steps for upgrade the PDB database by unplug and plug
1. Run the Pre-Upgrade Information Tool
%ORACLE_HOME%\jdk\bin\java -jar %ORACLE_HOME%\rdbms\admin\preupgrade.jar dir C:\script -c "PDB2"
2. Run the Pre-Upgrade the sql on Source database.
CONNECT / AS SYSDBA
SQL> ALTER SESSION SET CONTAINER=PDB2;
SQL> @C:\script\preupgrade_fixups_pdb2.sql
3. Close the Pluggable database.
alter pluggable database pdb2 close;
4. Login with the CDB$ROOT and unplug the database
CONNECT / AS SYSDBA
SQL> ALTER SESSION SET CONTAINER=CDB$ROOT;
alter pluggable database PDB2 unplug into 'C:\script\pdb2.xml';
5. Drop the pluggable database with keep files.
drop pluggable database pdb2 keep datafiles;
6. Connect to the new release database 12.2.0 version.
Note: ORCL122 is new released CDB database 1220 version.
Set ORACLE_HOME=C:\oracle12c\product\12.2.0\dbhome_1
SET PATH=%ORACLE_HOME%\bin;%PATH%
SET ORACLE_SID=ORCL122
Note:
1. Before plug the database into new environment edit the XML Configuration file.
2. Change the location of PDB database files to new location.
3. Copy Paste the PDB oradata folder of PDB2 from old location to updated location.
7. Plug the pluggable database PDB2 in new CDB.
create pluggable database pdb2 using 'C:\script\pdb2.xml' NOCOPY;
8. Open the Pluggable database in upgrade mode.
alter session set container= PDB2;
alter pluggable database open upgrade;
9. Upgrade the PDB using the Parallel Upgrade Utility command catctl.pl.
%ORACLE_HOME%\perl\bin\perl %ORACLE_HOME%\rdbms\admin\catctl.pl -d %ORACLE_HOME%\rdbms\admin -c "PDB2" -l C:\script catupgrd.sql
10. Check the log file of Upgrade process because i specify the location with l parameter in upper command.
C:\scripts\upgrade20180525120001\upg_summary.log
11. Run the postupgrade script produced in first step.
%ORACLE_HOME%\perl\bin\perl catcon.pl –c "PDB2" -n 1 -e -b postfixups -d '''.''' C:\script\postupgrade_fixups.sql
12. Recompile the invalid object of Pluggable database.
%ORACLE_HOME%\perl\bin\perl catcon.pl –c "PDB2" -n 1 -e -b comp -d '''.''' C:\oracle12c\product\12.2.0\dbhome_1\rdbms\admin\utlrp.sql