Upgrade the Multitenant PDB and CDB Oracle Database to higher version.

Upgrade is divide into three parts:
Pre-Upgrade steps: For upgrade precheck.
Upgrade Steps: For actually upgrade the database.
Post-Upgrade Steps: Verify all are good.

Pre-Upgrade steps:

1. Set the Old Oracle home and verified that all Oracle Component is valid.

col comp_id for a10
col version for a10
col status for a7
col comp_name for a50
select comp_id,comp_name,version,status from dba_registry;

2. PDB database must be open in read write mode.

select name, open_mode,cdb,i.con_id, version from V$database,v$instance i;
NAME  OPEN_MODE  CDB CON_ID  VERSION
----- ---------- --- ------- -----------
ORCL  READ WRITE YES 0       12.1.0.1.0

select CON_ID, NAME, OPEN_MODE from V$PDBS;
CON_ID NAME      OPEN_MODE
------ --------- ----------
     2 PDB$SEED  READ ONLY
     3 PDB1      READ WRITE 

3. Disable the DDL triggers used in Oracle Database.

Select * from dba_triggers;
ALTER TRIGGER trigger_name DISABLE;

4. Check the invalid objects and recompile in all PDBs or CDBROOT database.

select count(*) from dba_objects where status = 'INVALID';
 
select owner,object_type,count(*) from dba_objects where status='INVALID' group by owner,object_type order by owner,object_type ;

select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects where status='INVALID' order by owner,object_type;

--Run UTLRP for compile all objects:
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

5. Gather dictionary Stats.

exec dbms_stats.gather_dictionary_stats;

6. Take full RMAN or COLD backup of database.

run {
allocate channel ch00 type disk;
allocate channel ch01 type disk;
allocate channel ch02 type disk;
backup format '/u01/upgradebackup/rman/full_db_%t_%sp%p' filesperset 10 database plus archivelog;
release channel ch00;
release channel ch01;
release channel ch02;
allocate channel ch00 type disk;
backup format '/u01/upgradebackup/rman/cntrl_%s_%p_%t' CURRENT CONTROLFILE;
backup format '/u01/upgradebackup/rman/spfile_%s_%p_%t' spfile;
release channel ch00;
}

7. Run the preupgrade utility present in higher version(new oracle home) RDBMS folder.

Note: Need to install the new Oracle home and check the script at location: $ORACLE_HOME/rdbms/admin/preupgrade.jar.

--Check pdbs and all should be in open state.
show pdbs;
alter pluggable database <PDB_Name> open;

-- Run the Preupgrade utility on all pdbs.
Note: If no parameters are passed to the preupgrade.jar , it's run against all the container databases ( CDB and PDB's )
--Windows: 
-- For PDBS:
cd %ORACLE_HOME%\rdbms\admin
%ORACLE_HOME%\jdk\bin\java -jar %ORACLE_HOME_12.2%\rdbms\admin\preupgrade.jar –c "pdb1 pdb2"
--Run for all PDBS and CDB
%ORACLE_HOME%\jdk\bin\java -jar %ORACLE_HOME_12.2%\rdbms\admin\preupgrade.jar


--UNIX:
-- For PDBS:
cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME_12.2/rdbms/admin/preupgrade.jar –c 'pdb1 pdb2'
--Run for all PDBS and CDB
$ORACLE_HOME/jdk/bin/java -jar $ORACLE_HOME_12.2/rdbms/admin/preupgrade.jar 

8. Check the refreshable materialized view has been completed before upgrade.

select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#, bitand(s.mflags, 8) from obj$ o, sum$ s 
where o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8;

9. Check any file in media recovery mode or in backup mode.

-- Check backup mode
select * from  v$backup WHERE status != 'NOT ACTIVE';

--Check file need media recovery
select * from v$recover_file;

10. Solve Pending distributed transaction before upgrade.

Select * from dba_2pc_pending;
If any row return by first query then execute the following commands:
select  local_tran_id FROM dba_2pc_pending;
execute  dbms_transaction.purge_lost_db_entry('');
commit;

11. Purge the dba recycle bin.

purge dba_recyclebin;

12, Check the hidden parameter in all ROOT and PDBs database and remove it before upgrade.

alter session set container=<PDB_Name>;
SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;
Or 
Create pfile from spfile or remove hidden parameters
CREATE PFILE=<location> FROM SPFILE;

13. Apply latest bundle patches to target Oracle Home if available.

14. Check time Zone.

Note: The changes might affect existing data of the TIMESTAMP WITH TIME ZONE data type 
SELECT version FROM v$timezone_file;
 
-- check also
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

check the note id 
Note 1665676.1 Actions For DST Updates When Upgrading To Or Applying The 12.1.0.2 Patchset
Note id : 1585343.1 for upgrade the timezone.

15. Check aud$ table before upgraded if you need backup take otherwise truncate them.

select count(*) from sys.aud$;
Select count(*) from sys.fga_log$;
If you need audit Do the following s:
1.  Export AUD$ data and/or FGA_LOG$ data from the source database
2.  Clean up AUD$ and/or FGA_LOG$
3.  Re-run catupgrd.sql
4.  Import AUD$ data and/or FGA_LOG$ data into 11.2.0.1
truncate table sys.aud$;
truncate table sys.fga_log$

16. Check local listener parameter if it set then remove it value.

Show parameter local

17. Verify SYS and SYSTEM Default tablespace.

SELECT username, default_tablespace
 FROM dba_users
 WHERE username in ('SYS','SYSTEM');
-- If not default then make it.
SQL> ALTER user SYS default tablespace SYSTEM;
SQL> ALTER user SYSTEM default tablespace SYSTEM;

Upgrade Steps:

1. Shutdown the database from old home.

Shutdown immediate;

2. If Windows platform then you need to handle service in Services.msc or if linux then make changes in /etc/oratab.

Windows: 
--Stop the Service in services.msc
NET STOP OracleServiceORCL
--Delete the SID service from old home.
ORADIM -DELETE -SID ORCL
--Create the SID service from new home.
ORADIM -NEW -SID ORCL
OR 
ORADIM -NEW -SID SID -INTPWD PASSWORD  -STARTMODE AUTO -PFILE %ORACLE_HOME%\DATABASE\INIT<SID>.ORA

Linux:
Make entry of new Oracle Home 12.2:
orcl:/opt/oracle/product/12.2.0.1/db_1:N
--comment old one entry
#orcl:/opt/oracle/product/12.1.0.1/db_1:N

3. Set the Oracle Home environment variable to new version.

- ORACLE_BASE
- ORACLE_HOME
- PATH, LD_LIBRARY_PATH and SHLIB_PATH

4. Create SPFILE at new Oracle home from PFILE backup.

sqlplus / as sysdba
create spfile from pfile = 'location';

5. Start the database in upgrade mode from New Oracle Home.

--Start database in upgrade.
startup upgrade 
--Start all PDB in upgrade mode
alter pluggable database all open upgrade;
SQL> exit;

6. Run the dbupgrade for all CDB or PDB database from new home.

cd $ORACLE_HOME/rdbms/admin
dbupgrade -d $ORACLE_HOME/rdbms/admin
Note: catupgrade logs generated for Seed database and each PDB

7. Check the log file and search “BEGIN catuppst.sql” if it executed then okay otherwise you need to execute manually with following steps:

cd $ORACLE_HOME/rdbms/admin
--Run catuppst if not run 
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b catuppst -d '''.''' catuppst.sql

--Run UTLRP for compile all objects:
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

--Run for verify that all issues have been fixed
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlu122s -d '''.''' utlu122s.sql

8. Shutdown and start the database in normal mode.

shutdown immediate;
Startup

Post-Upgrade steps:

1. Start the database in normal state and open the PDBs.

Startup

alter pluggable database all open;

2. Run the catcon.pl script and the postupgrade_fixups.sql script from new Oracle Database.

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -n 1 -d \
$ORACLE_HOME/cfgtoollogs/cdbupgr/preupgrade -l /home/oracle/upgrdDBA -b \
postupgrade_fixups postupgrade_fixups.sql

3. Run the utlrp script for compile all invalid objects.

cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

4. Run the SQL script to verify that all issues have been fixed.

@rdbms/admin/postupgrade_fixups.sql

@rdbms/admin/utlu122s.sql

5. Upgrade the timezone with note id : 1585343.1 If not done in precheck steps.

6. upgrade the stats tables.

EXECUTE DBMS_STATS.UPGRADE_STAT_TABLE('SYS','dictstattab');

7. Enable the trigger which disabled.

select owner,trigger_name,table_name,status from dba_triggers where status= 'DISABLE';

ALTER TRIGGER trigger_name ENABLE;

8. Restore aud$ tables if you took backup with Import/Export utility.