Upgrade from 11g to 12c

Upgrade database from 11g to 12c manually

Current ORACLE_HOME= /apps/oracle/product/11.2.0.3

Target ORACLE_HOME=/apps/oracle/product/12.1.0.2

Startdb ASR:

Export ORACLE_SID=ASR

Echo $ORACLE_SID

 

cd $ORACLE_HOME check.

 

PRECHECKS:

1.CHECK THE INVALID OBJECTS( ALL SHOULD BE VALID)

select * from dba_objects where status='INVALID';

 

 

2. 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';

 

 

 

 

3.Check the integrity of the database by running http://dbaclass.com/wp-content/uploads/2016/12/dbupgdiag-2.txt

 

C:\Users\171092tj\Desktop\Cold Backup Clone\db Upgrade

/home/oraascp /DBUPGDIAG.SQL

 

 

4. Run utlrp.sql to validate invalid objects

/ORADB/app/orauat/11.2.0/rdbms/admin/utlrp.sql

/uatdb/app/oraascp/db/11.2.0/

Function created.

PL/SQL procedure successfully completed.

Function dropped.

 

5. Run preupgrade tool

How to Download and Run Oracle's Database Pre-Upgrade Utility (Doc ID 884522.1)

 

preupgrd.sql and utluppkg.sql

i.e /apps/oracle/product/12.1.0.2/rdbms/admin   to /apps/oracle/product/11.2.0.3/rdbms/admin

 

SQL> @ /apps/oracle/cfgtoollogs/PROD/preupgrade/preupgrade_fixups.sql

If still, changes are not reflecting  Check the preupgrade_fixups.sql script and do the changes manually.

In my case, it recommended changing parameters as below.

– Alter system set PROCESSES=300 SCOPE=SPFILE
– Execute dbms_preup.purge_recyclebin_fixup;
– Alter system set JOB_QUEUE_PROCESSES= 100
– Execute dbms_stats.gather_dictionary_stats;

So let’s do the above changes before proceeding with the next step.

 

6. Dependencies on Network Utility Packages:

 

Select * from DBA_DEPENDENCIES WHERE

Referenced_name IN (‘UTL_TCP’,’UTL_SMTP’,’UTL_MAIL’,’UTL_HTTP’)

AND owner NOT IN (‘SYS’,’PUBLIC’,’ORDPLUGINS’)

 

6. Take ddl backup of db_link backups:

During the upgrade to Oracle Database 12c any passwords in database links are encrypted.
To downgrade to the original release, all of the database links with encrypted passwords must be dropped prior to the downgrade.

 

7. Check the timezone version:

Select version from v$timezone_file;

For 12c, the new timezone is 18. So after the db upgrade is completed, we will upgrade the dst timezone from 14 to 18.

 

8. Optimizer Statistics:

SQL>EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

 

 

9. 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;

 

10. Ensure That No Files Need Media Recovery Before Upgrading:

Select * from v$recover_file;

 

11. Ensure That No Files Are in Backup Mode Before Upgrading

Select * from v$backup where status!=’NOT ACTIVE’

12. Resolve Outstanding Distributed Transactions Before Upgrading;

select * from dba_2pc_pending;

 

13. Purge the Database Recycle Bin Before Upgrading :

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

 

 

14. Synchronize the Standby Database with the Primary Database When Upgrading

 

SELECT SUBSTR (VALUE, INSTR (VALUE, '=', INSTR (UPPER (VALUE), 'SERVICE')) + 1) FROM v$parameter WHERE NAME LIKE 'log_archive_dest%' AND UPPER (VALUE) LIKE 'SERVICE%';

 

15. Disable cronjob,

Take backup of crontab and comment the same

 

 

16. Disable dbms_schduler jobs:

Select owner,job_name,enabled,state from dba_scheduler_jobs;

---disable the scheduled jobs by using below command;

Execute dbms_scheduler.disable(‘DRA_REEVALUATE_OPEN_FAILURES’);

BEGIN dbms_scheduler.disable('dra_reevaluate_open_failures'); END;

Error at line 1

ORA-27476: "APPS.DRA_REEVALUATE_OPEN_FAILURES" does not exist

ORA-06512: at "SYS.DBMS_ISCHED", line 4452

ORA-06512: at "SYS.DBMS_SCHEDULER", line 2737

ORA-06512: at line 1

 

Script Terminated on line 35.

 

 

17. Verify system and sys default tablespace.(Both should be system tablespace);

Select username,default_tablespace from

Dba_users

Where username in (‘SYS’,’SYSTEM’);

 

 

18. Check whether database has any externally authenticated SSL users:

Select name from sys.user$ where ext_username IS NOT NULL

AND password = ‘GLOBAL’;

 

 

19. Remove EM repository:

 

 

 

20. Review and Remove any unnecessary hidden/underscore parameters:

Select name,value from SYS.V$PARAMETER WHERE name LIKE ‘\_%’ ESCAPE ‘\’

ORDER BY NAME;

 

 

 

 

With this our precheck completes.

Before starting the upgrade, enable flashback and create restore point, so that in case upgrade fails, we can restore it using guarantee restore point.

Enable flashback

Now create restore point:

 

 

 

UPGRADE:

21. stop the listener and shutdown the database.

22.Update the ORACLE_HOME,PATH pointing to 12C Home.

23. Move the spfile and password file from 11g oracle_home/dbs location to 12cHome/dbs location.

24. start the database in upgrade mode.

25.Run catupgrade script from os level with paralle=6 as below.

Open another window and check the log.

cd /uv1172/apps/oracle/product/12.1.0.2/diagnostics
tail -100f catupgrd0.log

26. Run the Post-Upgrade Status Tool

27.Run Catuppst.sql

The catuppst.sql script is run as part of the upgrade process unless the upgrade returns errors during the process. Check the log file for “BEGIN catuppst.sql” to verify that catuppst.sql ran during the upgrade process. If catuppst.sql has not run, then proceed to run catuppst.sql as shown in this step. Warning messages are also displayed when running catctl.pl indicating that catuppst.sql was not run during the upgrade.

Now upgrade is completed. Now Proceed with POST CHECK.

 

27. UPGRADE DST TIME ZONE:

Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12c database . (Doc ID 1585343.1)

Run upg_tzv_apply.sql ( It will do the actual dst upgrade)

Once dst upgrade is successful , validate the time_zone(It should be 18)

1. SQL>@/apps/oracle/cfgtoollogs/PROD/preupgrade/postupgrade_fixup.sql

2. Change the ORACLE_HOME to 12c in listener.ora file.

3. Uncomment the crontab

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

5. Run utluiobj.sql from $ORACLE_HOME/rdbms/admin/ to identify/compare any new invalid objects due to the upgrade.

6. Run $ORACLE_HOME/rdbms/admin/utlrp.sql script

7. Update the compatible parameter and restart database.

With this our upgrade activity completes.

 

IF UPGRADE FAILS

If you have done the prechecks properly, then chances are very less that, upgrade will fail. But even if it fails for any other reasons like server crash during upgrade, then follow below steps to revert to back to 11g version .

1. Shutdown immediate;

2. set ORACLE_HOME to 11g

3. Start up mount ( with the 11g spfile)

4. select * from v$restore_point;

5. flashback database to restore point bef_upgrade. ( this restore point was created before upgrade)

6. alter database open resetlogs;

 

http://dbaclass.com/article/upgrade-database-from-11g-to-12c-manually/