Search In The Blog

Wednesday 15 March 2017

Oracle Grid & Database Upgradation from 11.2.0.3.0 to 12.1.0.2.0


Oracle Grid & Database Upgradation from 11.2.0.3.0 to 12.1.0.2.0



high level upgrade Plan -

1) install RDBMS 12.1.0.2 software only (as a pre-requisites)
2) apply patch (P17432124) on 12.1.0.2 Oracle Home for timezone upgradation. (You can download it from Applying the DSTv21 update for the Oracle Database (Doc ID 1583376.1))
3) upgrade grid from 11.2.0.3 to 12.1.0.2
4) upgrade database from 11.2.0.3 to 12.1.0.2 - using DBUA
5) update DST on 12c Oracle Home - by using Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database . (Doc ID 1585343.1)
6) Apply OJVM Patch (17443054 which is DST 21 OJVM patch)
7) apply proactive bundle Patch 24968615: DATABASE PROACTIVE BUNDLE PATCH 12.1.0.2.170117


Note - 

From the above list you can list down couple of things as a pre-requisites such as Database backup, install database software only, running pre checks etc. By this you can minimize your production downtime. 

You must apply patch (P17432124) on 12.1.0.2 on 12c home before upgrading the database.


Grid Upgrade 

Grid pre checks -

1. Check health of cluster before upgrade crsctl check cluster -all and crsctl check has for standalone.

2. Run Cluvfy.exe to verify all the prerequisites for the 12c GI upgrade are in place
   runcluvfy.sh stage -pre crsinst -upgrade -rolling -src_crshome /u01/app/11.2.0/grid - dest_crshome /u02/app/12.1.0/grid -dest_version 12.1.0.2.0

3. Backup oracle Software/Binary before upgrade.
   cd /product/DBHome
   tar cf - * | gzip > /product/archive/DBHome.tar.gz
   cd $ORACLE_HOME, tar –cvf /home/orahome.tar .

4. Run ORAchk (using latest version)

5. Unset ORACLE_HOME/ ORACLE_BASE / GI_HOME / TNS_ADMIN / ORA_NLS10

6. Start installer ./runinstaller












































Once grid installation completed successfully crosscheck the status with below useful commands.

crsctl check cluster -all
crsctl check has  --- In case of standalone

crsctl query crs releaseversion

crsctl query crs softwareversion

crsctl query crs activeversion -f

ps -ef |grep pmon
Should have +ASM & MGMTDB

srvctl status mgmtdb -verbose




Database Upgrade -

Notes : 
utlu121s.sql script has been replaced with the preupgrd.sql. 
The previous upgrade script catupgrd.sql has been replaced with the new catctl.pl Parallel Upgrade Utility script which provides the ability to run upgrade scripts in parallel taking advantage of spare CPU capacity which can potentially reduce upgrade times.

   If Flashback database is enabled, DBUA creates a guaranteed restore point which can be used in case we have a  failure in the upgrade process. We can also restart DBUA to recover from any failures during the upgrade.

   We can now use an existing RMAN backup which DBUA is aware of in case we need to restore the database to the pre-12c upgrade state

Kindly apply apply patch (P17432124) on 12.1.0.2 Oracle Home for timezone upgradation.


Database Upgrade Pre checks -

1. preupgrade.sql script 
cd /opt/oracle/product/12.1.0.1/dbhome_1/rdbms/admin
copy and run from 11g home @preupgrd.sql
check /opt/oracle/cfgtoollogs/ora11g/preupgrade/preupgrade.log
check Pre-Upgrade Fixup Script

2. As pre-requisites 24 Hrs before: Stats Gather SYS and Dictionary Objects

3. Remove invalid objects as much as possible as prerequisites

4. Purge Recycle Bin 
EXECUTE dbms_preup.purge_recyclebin_fixup;

5. Truncate Audit table 

6. Export Optimizer statistics, SQL Baseline, SQL Profile.

7. Take DDL backup for DB Links.

8. Backup ACL

9. Indoubt Tx should be null, MV Refresh should not be running, Disable all scheduler/Cron/batch Jobs.

10. Change the concurrent statistics to FALSE
      SQL> SELECT dbms_stats.get_prefs('CONCURRENT') from dual;
When 'concurrent statistics gathering' is not not set to 'FALSE', change the value to 'FALSE before the upgrade. 
BEGIN 
DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','FALSE'); 
END; 
/

11. em remove
      EM is no longer supported in 12.1 and will be removed post upgrade while executing catuppst.sql. To save time, this action may be completed BEFORE the upgrade by invoking $ORACLE_HOME/rdbms/admin/emremove.sql.

Copy 12c $ORACLE_HOME/rdbms/admin/emremove.sql to different location and change the location to copied folder

Connect to source database as sysuser
sql> connect / as sysdba

sql> spool emremoval.log

sql>@emremove.sql

sql> spool off

12. Initiate DBUA
cd /opt/oracle/product/12.1.0.1/dbhome_1/bin
./dbua 


Please note that some of the pre requisite check handled by oracle if you use dbua for database upgrade but if you go with manual upgrade you have to manually do it for yourself.





































After successful database upgradation, perform the post checks -

1. Check for Invalid objects.  Utluiobj.sql
select substr(owner,1,12) owner,substr(object_name,1,30) object,substr(object_type,1,30) type, status from dba_objects where status <> 'VALID'order by owner, type;
Run Utlrp.sql


2. check DB LINKS

3. check ACL


Once you verified and done the sanity check now you can go ahead with proactive bundle patch apply and then timezone upgradation.


I hope this exercise will help you!!!