How to Upgrade Database From 11g To 12c Manually

Old database version: /ebs121/oracle/DEV121/db/tech_st/11.1.0

New database version: /ebs121/oracle/DEV121/db/tech_st/12.1.0

PRECHECKS:

1.CHECK THE INVALID OBJECTS and components

SQL> select comp_id,status from dba_registry;

COMP_ID STATUS

------------------------------ -----------

CATALOG VALID

CATPROC VALID

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

OBJECT_NAME OBJECT_TYPE

--------------------------------------------------- -------------------

DBMS_REPCAT_AUTH PACKAGE BODY

AQ$_SCHEDULES_PRIMARY INDEX

AQ$_SCHEDULES TABLE

DBMS_REPCAT_AUTH PACKAGE

If you found any other objects other than these four, then those need to be cleaned up.

3. Run utlrp.sql to validate invalid objects

SQL>@/ebs121/oracle/DEV121/db/tech_st/11.1.0/rdbms/admin/utlrp.sql

4. Run preupgrade tool

Copy the Pre-Upgrade Information Tool script preupgrd.sql and utluppkg.sql from the Oracle Database 12c Release 1 (12.1) i.e /ebs121/oracle/DEV121/db/tech_st/12.1.0/rdbms/admin to /ebs121/oracle/DEV121/db/tech_st/11.1.0/rdbms/admin

SQL> @$ORACLE_HOME/rdbms/admin/preupgrd.sql

----It will generate below files

preupgrade.log

preupgrade_fixups.sql

postupgrade_fixups.sql

Execute the preupgrade_fixup.sql and check whether changes are reflecting or not.

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

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

5. Dependencies on Network Utility Packages

SQL> 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');

no rows selected

6. Take ddl backup of db_link backups:

7. Check the timezone version:

SQL> SELECT version FROM v$timezone_file;

VERSION

----------

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. Gather Optimizer Statistics

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

9. Verify That Materialized View Refreshes Have Completed Before Upgrading

SQL> 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; 2

no rows selected

10. Ensure That No Files Need Media Recovery Before Upgrading

SQL> SELECT * FROM v$recover_file;

no rows selected

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

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

no rows selected

12. Check Outstanding Distributed Transactions Before Upgrading

SQL> SELECT * FROM dba_2pc_pending;

no rows selected

13. Purge the Database Recycle Bin Before Upgrading :

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

14. Disable cronjob,

Take backup of crontab and comment the same.

15. Disable dbms_schduler jobs:

SQL> set pagesize 2000

SQL> set lines 2000

SQL> set long 99999

SQL> select owner,JOB_NAME,ENABLED,state from dba_scheduler_jobs;

OWNER JOB_NAME ENABL STATE

------------------------------ ------------------------------ ----- ---------------

SYS SM$CLEAN_AUTO_SPLIT_MERGE FALSE DISABLED

SYS RSE$CLEAN_RECOVERABLE_SCRIPT FALSE DISABLED

SYS BSLN_MAINTAIN_STATS_JOB FALSE DISABLED

SYS DRA_REEVALUATE_OPEN_FAILURES TRUE SCHEDULED

SYS ORA$AUTOTASK_CLEAN FALSE DISABLED

SYS FILE_WATCHER FALSE DISABLED

SYS HM_CREATE_OFFLINE_DICTIONARY FALSE DISABLED

SYS AUTO_SPACE_ADVISOR_JOB FALSE DISABLED

SYS GATHER_STATS_JOB FALSE DISABLED

SYS FGR$AUTOPURGE_JOB FALSE DISABLED

SYS PURGE_LOG FALSE DISABLED

ORACLE_OCM MGMT_STATS_CONFIG_JOB FALSE DISABLED

13 rows selected.

--- Disable the scheduled jobs by using below command

SQL> execute dbms_scheduler.disable('DRA_REEVALUATE_OPEN_FAILURES);

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

SQL> SELECT username, default_tablespace

FROM dba_users

WHERE username in ('SYS','SYSTEM'); 2 3

USERNAME DEFAULT_TABLESPACE

------------------------------ ------------------------------

SYSTEM SYSTEM

SYS SYSTEM

17. Review and Remove any unnecessary hidden/underscore parameters

SQL> SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;

no rows selected

-----------------------------------------------------------------------

Enable flashback

SQL> alter system set db_recovery_file_dest_size=10G scope=both;

System altered.

SQL> alter system set db_recovery_file_dest='/oradump/himanshu' scope=both;

System altered.

SQL> alter database flashback on;

Database altered.

SQL> startup force

Now create restore point:

CREATE RESTORE POINT BEFORE_UPGRADE GUARANTEE FLASHBACK DATABASE;

Restore point created.

UPGRADE:

18. stop the listener and shutdown the database.

lsnrctl stop DEV121

SQL>shutdown immediate

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

export ORACLE_HOME=/ebs121/oracle/DEV121/db/tech_st/12.1.0

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export CLASS_PATH=$ORACLE_HOME/jlib

export ORACLE_SID=DEV121

export PERL5LIB=$ORACLE_HOME/perl/lib/5.14.1:$ORACLE_HOME/perl/lib/site_perl/5.14.1

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

21. start the database in upgrade mode.

$ cd $ORACLE_HOME/rdbms/admin

$ pwd

/ebs121/oracle/DEV121/db/tech_st/12.1.0/rdbms/admin

$ sqlplus "/ as sysdba"

SQL> startup UPGRADE

SQL> exit

22.Run catupgrade script from os level with parallel=4 as below.

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catctl.pl -n 4 -l $ORACLE_HOME/diagnostics catupgrd.sql

Monitor the log.

cd /ebs121/oracle/DEV121/db/tech_st/12.1.0/diagnostics

tail -100f catupgrd0.log

23. Run the Post-Upgrade Status Tool

---Run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu121s.sql which provides a summary of the upgrade

$ sqlplus "/as sysdba"

SQL> STARTUP

SQL> @utlu121s.sql

24.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.

SQL>@$ORACLE_HOME/rdbms/admin/catuppst.sql

This completes our upgrade

25. UPGRADE DST TIME ZONE:

Download the dst upgrade script from oracle

--- unzip the file in

$ unzip DBMS_DST_scriptsV1.9.zip

Archive: DBMS_DST_scriptsV1.9.zip

creating: DBMS_DST_scriptsV1.9/

inflating: DBMS_DST_scriptsV1.9/countstarTSTZ.sql

inflating: DBMS_DST_scriptsV1.9/countstatsTSTZ.sql

inflating: DBMS_DST_scriptsV1.9/upg_tzv_apply.sql

inflating: DBMS_DST_scriptsV1.9/upg_tzv_check.sql

----

$ cd DBMS_DST_scriptsV1.9

-- run countstatsTSTZ.sql script.

SQL> spool countstatsTSTZ.log

SQL> @countstatsTSTZ.sql

Purge Scheduler job

SQL> exec dbms_scheduler.purge_log;

PL/SQL procedure successfully completed.

Run upg_tzv_check.sql ( it will detect the highest installed DST patch automatically)

SQL> spool upg_tzv_check.log

SQL> @upg_tzv_check.sql

INFO: Starting with RDBMS DST update preparation.

INFO: NO actual RDBMS DST update will be done by this script.

INFO: If an ERROR occurs the script will EXIT sqlplus.

INFO: Doing checks for known issues ...

INFO: Database version is 12.1.0.2 .

INFO: Database RDBMS DST version is DSTv14 .

INFO: No known issues detected.

INFO: Now detecting new RDBMS DST version.

A prepare window has been successfully started.

INFO: Newest RDBMS DST version detected is DSTv18 .

INFO: Next step is checking all TSTZ data.

INFO: It might take a while before any further output is seen ...

A prepare window has been successfully ended.

INFO: A newer RDBMS DST version than the one currently used is found.

INFO: Note that NO DST update was yet done.

INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update.

INFO: Note that the upg_tzv_apply.sql script will

INFO: restart the database 2 times WITHOUT any confirmation or prompt.

SQL> spool off

SQL> spool upg_tzv_apply.log

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

SQL> spool upg_tzv_apply.log

SQL> @upg_tzv_apply.sql

INFO: If an ERROR occurs the script will EXIT sqlplus.

INFO: The database RDBMS DST version will be updated to DSTv18 .

WARNING: This script will restart the database 2 times

WARNING: WITHOUT asking ANY confirmation.

WARNING: Hit control-c NOW if this is not intended.

INFO: Restarting the database in UPGRADE mode to start the DST upgrade.

Database closed.

Database dismounted.

ORACLE instance shut down.

ORACLE instance started.

Total System Global Area 2147483648 bytes

Fixed Size 2916104 bytes

Variable Size 1677721848 bytes

Database Buffers 452984832 bytes

Redo Buffers 13860864 bytes

Database mounted.

Database opened.

INFO: Starting the RDBMS DST upgrade.

INFO: Upgrading all SYS owned TSTZ data.

INFO: It might take time before any further output is seen ...

An upgrade window has been successfully started.

INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.

Database closed.

Database dismounted.

ORACLE instance shut down.

ORACLE instance started.

Total System Global Area 3247483648 bytes

Fixed Size 3016104 bytes

Variable Size 2477721848 bytes

Database Buffers 452984832 bytes

Redo Buffers 13860864 bytes

Database mounted.

Database opened.

INFO: Upgrading all non-SYS TSTZ data.

INFO: It might take time before any further output is seen ...

INFO: Do NOT start any application yet that uses TSTZ data!

INFO: Next is a list of all upgraded tables:

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"

Number of failures: 0

Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"

Number of failures: 0

INFO: Total failures during update of TSTZ data: 0 .

An upgrade window has been successfully ended.

INFO: Your new Server RDBMS DST version is DSTv18 .

INFO: The RDBMS DST update is successfully finished.

INFO: Make sure to exit this sqlplus session.

INFO: Do not use it for timezone related selects.

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

1. SQL>@/ebs121/oracle/cfgtoollogs/DEV121/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

SQL> SELECT version FROM v$timezone_file;

VERSION

----------

SQL> drop restore point BEF_UPGRADE;

7. Update the compatible parameter and restart the database.

NOTE- Database cannot be downgraded once a compatible parameter is updated.

--- MAKE SURE TO DROP THE RESTORE POINT:

SQL> drop restore point BEF_UPGRADE;

Restore point dropped.

-- Now alter the compatible parameter

SQL>alter system set compatible='12.1.0.2' scope=spfile;

shutdown immediate;

startup

NOTE – Once compatible is set to higher version, downgrade is not possible.

Upgrade is completed.

In case UPGRADE FAILS

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 before_upgrade. ( this restore point was created before upgrade)

6. alter database open resetlogs;

If you like please follow and comment

How to Upgrade Database From 11g To 12c Manually