• Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
  • Home
  • DBA Scripts
    • Oracle Scripts
    • SQL Server Scripts
  • Knowledge Base
    • Oracle Database
    • MS SQL Server
    • MongoDB
    • MariaDB
  • Troubleshoot
    • Oracle Database Issues
    • SQL Server Issues
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • Courses
    • Oracle Database
    • Oracle DBA L1
    • Oracle DBA L2
    • Oracle DBA L3
home/Knowledge Base/Oracle Database/Oracle Database Upgrade/Upgrade Oracle Database from 11.2.0.3 to 12.1.0.2
Popular Search:Oracle, SQL Server, MongoDB

Upgrade Oracle Database from 11.2.0.3 to 12.1.0.2

1601 views 2 September 7, 2019 April 2, 2021 admin

Upgrade Oracle Database from 11.2.0.3 to 12.1.0.2

A. Prerequisites:

1. Check for the Invalid Components in the database using query DBA_REGISTRY.
select name, open_mode from v$database;
SELECT comp_name, version, status FROM dba_registry;

2. Check invalid objects:

set pages 999
col c1 heading ‘owner’ format a15
col c2 heading ‘name’ format a40
col c3 heading ‘type’ format a10
ttitle ‘Invalid|Objects’
select owner c1, object_type c3, object_name c2 from dba_objects where status != ‘VALID’ order by owner, object_type;

3. Compile invalid if any:
@?/rdbms/admin/utlrp.sql

4. Note down list of invalids:
set pages 999
col c1 heading ‘owner’ format a15
col c2 heading ‘name’ format a40
col c3 heading ‘type’ format a10
ttitle ‘Invalid|Objects’
select owner c1, object_type c3, object_name c2 from dba_objects where status != ‘VALID’ order by owner, object_type;

5. Check duplicate objects owned by system and sys:
select object_name, object_type
from dba_objects
where object_name||object_type in
(select object_name||object_type
from dba_objects
where owner = ‘SYS’)
and owner = ‘SYSTEM’;

Note: Any other objects other than below four, then cleanup** objects owned by sys and system.

OBJECT_NAME                                                   OBJECT_TYPE
—————————————————   ——————-
DBMS_REPCAT_AUTH                                     PACKAGE BODY
AQ$_SCHEDULES_PRIMARY                         INDEX
AQ$_SCHEDULES                                               TABLE
DBMS_REPCAT_AUTH                                      PACKAGE

****How to Cleanup of objects
(Script to generate the DDL)
set pause off
set heading off
set pagesize 0
set feedback off
set verify off
spool dropsys.sql
select ‘DROP ‘ || object_type || ‘ SYSTEM.’ || object_name || ‘;’
from dba_objects
where object_name||object_type in
(select object_name||object_type
from dba_objects
where owner = ‘SYS’)
and owner = ‘SYSTEM’;
spool off
exit

6. Dependencies on Network Utility Packages:
SELECT * FROM DBA_DEPENDENCIES
WHERE referenced_name IN (‘UTL_TCP’,’UTL_SMTP’,’UTL_MAIL’,’UTL_HTTP’,’UTL_
INADDR’,’DBMS_LDAP’)
AND owner NOT IN (‘SYS’,’PUBLIC’,’ORDPLUGINS’);

7. Check the timezone version:
SELECT version FROM v$timezone_file;

8. Verify That Materialized View Refreshes Have Completed Before Upgrading
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. Ensure That No Files Need Media Recovery Before Upgrading:
SELECT * FROM v$recover_file;

10. Ensure That No Files Are in Backup Mode Before Upgrading:
SELECT * FROM v$backup WHERE status != ‘NOT ACTIVE’;

11. Purge the Database Recycle Bin Before Upgrading:
PURGE DBA_RECYCLEBIN;

12. Disable dbms_schduler jobs:
set pagesize 2000
set lines 2000
set long 99999
select owner,JOB_NAME,ENABLED,state from dba_scheduler_jobs;
execute dbms_scheduler.disable(‘<job name from above list>’);
–Check the status again–
select owner,JOB_NAME,ENABLED,state from dba_scheduler_jobs;

13. Verify system and sys default tablespace (Both should be system tablespace):
SELECT username, default_tablespace
FROM dba_users
WHERE username in (‘SYS’,’SYSTEM’);

14. Review and Remove any unnecessary hidden/underscore parameters
SELECT name, value from SYS.V$PARAMETER WHERE name LIKE ‘\_%’ ESCAPE ‘\’ order by name;

B. Upgradation procedure:

1. Copy the preupgrd.sql and utluppkg.sql script from the new 12c home to current 11g Home.
cp /<12c Oracle Home>/rdbms/admin/preupgrd.sql  /<11g Oracle Home>/rdbms/admin/
cp /<12c Oracle Home>/rdbms/admin/utluppkg.sql  /<11g Oracle Home>/rdbms/admin/

export ORACLE_HOME=<11g Oracle Home>
export ORACLE_SID=<DB SID>
export PATH=$ORACLE_HOME/bin:$PATH
SQL>@$ORACLE_HOME/rdbms/admin/preupgrd.sql

Note: Please note down preupgrade_fixups.sql & postupgrade_fixups.sql script paths for further use.

2. Now run preupgrade_fixups.sql, path will be provided from step 1.
SQL> @<>/preupgrade/preupgrade_fixups.sql

3. Backup the parameter, password and network files from the current 12c home.
cp $ORACLE_HOME/dbs/*.ora $ORACLE_HOME/dbs/*.ora_<date>
cp $ORACLE_HOME/dbs/orapw* $ORACLE_HOME/dbs/orapw*_<date>
cp -rp $ORACLE_HOME/network $ORACLE_HOME/network_<date>

4. Change the recomended parameters of init.ora as per preupgrade.log file:
$ vi initprod.ora

5. Set the environment to 12c home and start db in upgrade mode:
export ORACLE_HOME=<12c Oracle Home>
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus / as sysdba
SQL> STARTUP UPGRADE
SQL>exit

cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql

6. Startup the upgraded database & Run utlu121s.sql script to check the summary of upgrade
SQL> @?/rdbms/admin/utlu121s.sql
Check upg_summary.log for errors.

7. Run catuppst.sql if error found in above step 6.
SQL> @?/rdbms/admin/catuppst.sql

8. UPGRADE DST TIME ZONE:
Conn / as sysdba
SELECT version FROM v$timezone_file;
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE ‘DST_%’
ORDER BY PROPERTY_NAME;

==Via Script==
– Download and unzip DBMS_DST_scriptsV1.9 from MOS DOC ID 1585343.1
– sqlplus / as sysdba
spool countstatsTSTZ.log
@countstatsTSTZ.sql
spool off

spool upg_tzv_check.log
@upg_tzv_check.sql
spool off

spool upg_tzv_apply.log
@upg_tzv_apply.sql
spool off
– SELECT version FROM v$timezone_file;

9. Run Post Upgrade fix up script, path will be provided from step 1:
SQL> @<path to>/postupgrade_fixups.sql

10. Check for the invalid objects:
set pages 999
col c1 heading ‘owner’ format a15
col c2 heading ‘name’ format a40
col c3 heading ‘type’ format a10
ttitle ‘Invalid|Objects’
select owner c1, object_type c3, object_name c2 from dba_objects where status != ‘VALID’ order by owner, object_type;

select count(*) from dba_objects where status like ‘%INVALID%’;

11. Recompile invalid objects:
SQL> @?/rdbms/admin/utlrp.sql
SQL> @?/rdbms/admin/utluiobj.sql
SQL> @?/rdbms/admin/utlu121s.sql

12. Check the upgraded database:
SQL> SELECT name, open_mode FROM v$database;
SQL> select banner from v$version;
SQL> select version, comp_id, comp_name, status from dba_registry;

13. Enable the jobs in dba_scheduler_jobs which we disabled before the upgrade.

Tags:upgrade

Was this helpful?

2 Yes  No
Related Articles
  • Upgrade from 11g to 12c using Oracle Goldengate
Leave A Comment Cancel reply

Oracle Database Upgrade
  • Upgrade Oracle Database from 11.2.0.3 to 12.1.0.2
  • Upgrade from 11g to 12c using Oracle Goldengate
Popular Articles
  • Upgrade Oracle Database from 11.2.0.3 to 12.1.0.2
  • Upgrading MariaDB on Windows
  • Oracle DBA Basic Interview Questions Part A
  • Purging Oracle Sysaux Tablespace
  • Client – Server Architecture
KB Categories
  • Oracle Database
    • Oracle RAC
    • Oracle ASM
    • Oracle GoldenGate
    • Oracle Tuning
    • Oracle 11g Database
    • Oracle Database Upgrade
    • Oracle 12c Database
    • ALL KB Oracle
    • Oracle 18c Database
    • Oracle Standby Database
  • MongoDB
  • MS SQL Server
  • MySQL
  • Interview Questions
    • AWS Interview Questions
    • Oracle DBA Interview Questions
    • SQL Server Interview Questions
  • MariaDB
Database Organization

Database Organization (DB ORG) is knowledge base for DBA to learn and execute the fundamental of different databases under one website. DB ORG is a non-profit initiative. ORACLE, MS SQL Server, MongoDB, MariaDB, Couchbase

Join Our Community
  • KnowledgeBase
  • Documentation
  • Troubleshoot
  • FAQ
Information Links
  • About DBOrg
  • Licenses
  • Terms
  • Privacy Policy
Contact Us
    DB ORG - Database Administration,
    Knowledge Base for DBA
    Mail: support@databaseorg.com
    WhatsApp: (+91) 9306440957
    Monday to Friday: EST - 11:30 AM to 06:30 PM (IST - 9:00 PM to 4:00 AM)
  • © 2023 Database Organization - DB ORG. All Rights Reserved.

Popular Search:Oracle, SQL Server, MongoDB

WhatsApp DB Org