Site icon IT Tutorial

Oracle 12c Upgrade | Manual Upgrade Oracle Database From 11gR2 to 12cR2 without DBUA

Mehmet Salih Deveci

I will explain how to Manual Upgrade Oracle Database From 11.2.0.4 to 12.2.0.1 without DBUA in this article. Let’s review the Oracle 12c Upgrade | Manual Upgrade Oracle Database.

 

 

Oracle 12c Upgrade | Manual Upgrade Oracle Database

 

If you don’t know Upgrade Guide and How to Upgrade Oracle Database, you can read following article.

Oracle Database Upgrade Guide and How to Upgrade Oracle Database

 

 

Manual Upgrade Method – Oracle 12c

I will explain Manual Upgrade from 11.2.0.4 to 12.2.0.1 in this article. If you want to learn Upgrade using DBUA ( Database Upgrade Assistant ), You can read following article.

How to Upgrade Oracle Database 11g to Oracle 12c using DBUA

 

Oracle 12c Manual upgrade steps

Manual upgrade steps should be as follows.

  1. Take Full backup
  2. Gather dictionary stats
  3. Purge Recycle bin
  4. Run Preupgrade
  5. Shutdown 11g Database and Open 12c instance in upgrade mode
  6. Upgrade database
  7. Run Post Upgrade steps
  8. Compile Invalid Objects.
  9. Check Oracle 12c instances and database

 

Steps -1 :  Take a Full backup via RMAN Tool. If you don’t know RMAN Backup, read following article.

Full Backup in Online and Offline Mode via RMAN | Oracle RMAN ( Recovery Manager ) Backup Tutorials -3

 

 

 

 

 

 

Steps -2: Gather dictionary stats like following.

 

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

 

 

Steps -3: Purge recycle bin.

 

SQL> PURGE DBA_RECYCLEBIN ;

DBA Recyclebin purged.

 

 

Preupgrade Oracle 12c

Steps -4: Run Preupgrade tool as follows.

 

/oracle/app/oracle/product/11.2.0/db_4/jdk/bin/java -jar /oracle/app/oracle/product/12.2.0/db_1/rdbms/admin/preupgrade.jar FILE DIR /oracle




oracle@MehmetSalih ~DEVECI(/home/oracle)$  /oracle/app/oracle/product/11.2.0/db_4/jdk/bin/java -jar /oracle/app/oracle/product/12.2.0/db_1/rdbms/admin/preupgrade.jar FILE DIR /oracle
Preupgrade generated files:
/oracle/preupgrade.log
/oracle/preupgrade_fixups.sql
/oracle/postupgrade_fixups.sql
oracle@MehmetSalih ~DEVECI(/home/oracle)$

 

After execution of preupgrade.jar, you should run following sql to complete preupgrade task.

SQL> @preupgrade_fixups.sql
Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script
Version: 12.2.0.1.0 Build: 20
Generated on: 2019-10-03 10:20:58

For Source Database: DEVECI
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 12.2.0.1.0

Fixup
Check Name Status Further DBA Action
---------- ------ ------------------
two_pc_txn_exist Failed Manual fixup required.
job_queue_process_0 Failed Manual fixup required.
invalid_sys_tabledata Passed None
purge_recyclebin Passed None
em_present Failed Manual fixup recommended.
invalid_objects_exist Failed Manual fixup recommended.
amd_exists Failed Manual fixup recommended.
network_acl_priv Failed Manual fixup recommended.
exclusive_mode_auth Failed Manual fixup recommended.
mv_refresh Failed Manual fixup recommended.
hidden_params Failed Manual fixup recommended.
dictionary_stats Passed None
trgowner_no_admndbtrg Passed None
rman_recovery_version Failed Manual fixup recommended.

PL/SQL procedure successfully completed.

SQL>

 

 

Step -5:  Shutdown 11g Database and Open 12c instance in upgrade mode

 

oracle@MehmetSalih ~DEVECI(/home/oracle)$  sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Oct 3 10:51:24 2019

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup upgrade
ORACLE instance started.

Total System Global Area 3.6507E+10 bytes
Fixed Size 8659072 bytes
Variable Size 3892318080 bytes
Database Buffers 3.2481E+10 bytes
Redo Buffers 125554688 bytes
Database mounted.
Database opened.
SQL>

 

 

 

 

Upgrade Oracle 12c from 11g

 

Step -6:  Upgrade database

 

Run catctl.pl and catupgrd.sql with nohup to upgrade Oracle database from 11.2.0.4 to 12.2.0.1 as follows.

 

nohup $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -d $ORACLE_HOME/rdbms/admin -l /home/oracle/ -n 8 catupgrd.sql &

 

oracle@MehmetSalih ~DEVECI(/home/oracle)$  nohup $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catctl.pl -d $ORACLE_HOME/rdbms/admin -l /home/oracle/ -n 8 catupgrd.sql &

Argument list for [/oracle/app/oracle/product/12.2.0/db_1/rdbms/admin/catctl.pl]
For Oracle internal use only A = 0
Run in c = 0
Do not run in C = 0
Input Directory d = /oracle/app/oracle/product/12.2.0/db_1/rdbms/admin
Echo OFF e = 1
Simulate E = 0
Forced cleanup F = 0
Log Id i = 0
Child Process I = 0
Log Dir l = /home/oracle/
Priority List Name L = 0
Upgrade Mode active M = 0
SQL Process Count n = 8
SQL PDB Process Count N = 0
Open Mode Normal o = 0
Start Phase p = 0
End Phase P = 0
Reverse Order r = 0
AutoUpgrade Resume R = 0
Script s = 0
Serial Run S = 0
RO User Tablespaces T = 0
Display Phases y = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0

catctl.pl VERSION: [12.2.0.1.0]
STATUS: [production]
BUILD: [RDBMS_12.2.0.1.0DBJUL2019RU_HPUX.IA64_190624]

/oracle/app/oracle/product/12.2.0/db_1/rdbms/admin/orahome = [/oracle/app/oracle/product/12.2.0/db_1]
/oracle/app/oracle/product/12.2.0/db_1/bin/orabasehome = [/oracle/app/oracle/product/12.2.0/db_1]
catctlGetOraBaseLogDir = [/oracle/app/oracle/product/12.2.0/db_1]

Analyzing file /oracle/app/oracle/product/12.2.0/db_1/rdbms/admin/catupgrd.sql

Log file directory = [/home/oracle/]

catcon: ALL catcon-related output will be written to [/home/oracle//catupgrd_catcon_22278.lst]
catcon: See [/home/oracle//catupgrd*.log] files for output generated by scripts
catcon: See [/home/oracle//catupgrd_*.lst] files for spool files, if any

Number of Cpus = 4
Database Name = DEVECI
DataBase Version = 11.2.0.4.0
Parallel SQL Process Count = 8
Components in [DEVECI]
Installed [APS CATALOG CATJAVA CATPROC CONTEXT EM JAVAVM ODM ORDIM OWM SDO XDB XML XOQ]
Not Installed [APEX DV MGW OLS RAC WK]

Time: 885s
***************** Post Upgrade *****************
Serial Phase #:112 [DEVECI] Files:1 Time: 213s
**************** Summary report ****************
Serial Phase #:113 [DEVECI] Files:1 Time: 2s
Serial Phase #:114 [DEVECI] Files:1 Time: 0s
Serial Phase #:115 [DEVECI] Files:1 Time: 57s

------------------------------------------------------
Phases [0-115] End Time:[2019_10_03 12:17:23]
------------------------------------------------------

Grand Total Time: 4975s

LOG FILES: (/home/oracle//catupgrd*.log)

Upgrade Summary Report Located in:
/home/oracle//upg_summary.log

Grand Total Upgrade Time: [0d:1h:22m:55s]

 

 

 

Post Upgrade Oracle 12c

Step -7:  Run Post Upgrade steps.

 

Run following SQL to complete post upgrade step.

 

SQL> @postupgrade_fixups.sql
Session altered.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.

Package created.
No errors.
Package body created.
No errors.
Package created.
No errors.
Package body created.
No errors.
Executing Oracle POST-Upgrade Fixup Script
Auto-Generated by: Oracle Preupgrade Script
Version: 12.2.0.1.0 Build: 20
Generated on: 2019-10-03 10:28:59
For Source Database: DEVECI
Source Database Version: 11.2.0.4.0
For Upgrade to Version: 12.2.0.1.0
Fixup
Check Name Status Further DBA Action
---------- ------ ------------------
depend_usr_tables Passed None
old_time_zones_exist Passed None
post_dictionary Passed None
fixed_objects Passed None
upg_by_std_upgrd Passed None

PL/SQL procedure successfully completed.


Session altered.

SQL>





oracle@MehmetSalih ~DEVECI(/oracle/app/oracle/product/12.2.0/db_1/rdbms/admin)$sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Thu Oct 3 13:08:03 2019

Copyright (c) 1982, 2016, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> @utlu122s.sql

Oracle Database 12.2 Post-Upgrade Status Tool 10-03-2019 13:08:07

Component Current Version Elapsed Time
Name Status Number HH:MM:SS

Oracle Server UPGRADED 12.2.0.1.0 00:27:42
JServer JAVA Virtual Machine VALID 12.2.0.1.0 00:08:35
Oracle Real Application Clusters UPGRADED 12.2.0.1.0 00:00:00
Oracle Workspace Manager UPGRADED 12.2.0.1.0 00:01:34
OLAP Analytic Workspace UPGRADED 12.2.0.1.0 00:00:20
OLAP Catalog OPTION OFF 11.2.0.4.0 00:00:00
Oracle OLAP API UPGRADED 12.2.0.1.0 00:00:27
Oracle XDK UPGRADED 12.2.0.1.0 00:01:01
Oracle Text UPGRADED 12.2.0.1.0 00:01:12
Oracle XML Database UPGRADED 12.2.0.1.0 00:04:19
Oracle Database Java Packages UPGRADED 12.2.0.1.0 00:00:20
Oracle Multimedia UPGRADED 12.2.0.1.0 00:02:39
Spatial UPGRADED 12.2.0.1.0 00:06:17
Final Actions 00:08:04
Post Upgrade 00:03:31

Total Upgrade Time: 01:06:51


13:08:07 SQL>
13:08:07 SQL>



SQL> @catuppst.sql
Session altered.
Session altered.


TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP DBRESTART 2019-10-03 13:09:15
DBUA_TIMESTAMP DBRESTART FINISHED 2019-10-03 13:09:15
DBUA_TIMESTAMP DBRESTART NONE 2019-10-03 13:09:15
TIMESTAMP
--------------------------------------------------------------------------------
DBUA_TIMESTAMP CATUPPST STARTED 2019-10-03 13:09:15

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_BGN 2019-10-03 13:09:15
DBUA_TIMESTAMP POSTUP_BGN FINISHED 2019-10-03 13:09:15
DBUA_TIMESTAMP POSTUP_BGN NONE 2019-10-03 13:09:15

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_BGN 2019-10-03 13:09:15
DBUA_TIMESTAMP CATREQ_BGN FINISHED 2019-10-03 13:09:15
DBUA_TIMESTAMP CATREQ_BGN NONE 2019-10-03 13:09:15

PL/SQL procedure successfully completed.

catrequtlmg: b_StatEvt = TRUE
catrequtlmg: b_SelProps = FALSE
catrequtlmg: b_UpgradeMode = FALSE
catrequtlmg: b_InUtlMig = FALSE

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATREQ_END 2019-10-03 13:09:15
DBUA_TIMESTAMP CATREQ_END FINISHED 2019-10-03 13:09:15
DBUA_TIMESTAMP CATREQ_END NONE 2019-10-03 13:09:15

catuppst: Dropping library DBMS_DDL_INTERNAL_LIB

PL/SQL procedure successfully completed.

..................................................................

..................................................................
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP POSTUP_END 2019-10-03 13:09:19
DBUA_TIMESTAMP POSTUP_END FINISHED 2019-10-03 13:09:19
DBUA_TIMESTAMP POSTUP_END NONE 2019-10-03 13:09:19

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP CATUPPST 2019-10-03 13:09:19
DBUA_TIMESTAMP CATUPPST FINISHED 2019-10-03 13:09:19
DBUA_TIMESTAMP CATUPPST NONE 2019-10-03 13:09:19

Session altered.

SQL>

 

 

 

Step -8: Compile Invalid Objects using utlrp.sql.

 

SQL> @utlrp.sql

 

 

Step -9: Check Oracle 12c instances and database.

 

Check all components’ status and their version using dba_registry view.

 

You can now use new Oracle 12.2.0.1 database after upgrade.

 

 

Do you want to learn Oracle Database 18c Upgrade then Click this link and read the articles

 

Exit mobile version