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_
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#,
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/
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_
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.