A database can be upgraded either using DBUA or manual method. But for major production databases, it is better to do it manually, so that troubleshooting will be easy. Here we will provide steps for upgrading from 11gr2 to 12c database manually.
Before starting the upgrade make sure Oracle 12c database binary already installed on the database server.
You can refer the Installation steps here: https://dbaclass.com/article/how-to-install-oracle-12c-on-linux/
SEE ALSO : Upgrade database from 11g to 12c using OEM cloud control
SEE ALSO: Upgrade database from 11g to 12c using DBUA ( GUI method)
Current ORACLE_HOME= /apps/oracle/product/11.2.0.3
Target ORACLE_HOME=/apps/oracle/product/12.1.0.2
PRECHECKS:
1.CHECK THE INVALID OBJECTS( ALL SHOULD BE VALID)
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';SP2-0734: unknown command beginning "olumn obje..." - rest of line ignored.
SQL> SQL> 2 3 4 5 6 7
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.Check the integrity of the database by running dbupgdiag-2
SQL >@dbupgdiag.sql *** Start of LogFile *** Oracle Database Upgrade Diagnostic Utility 05-13-2015 09:09:57 =============== Hostname =============== primary-host =============== Database Name =============== PROD =============== Database Uptime =============== 22:31 10-MAY-15 ================= Database Wordsize ================= This is a 64-bit database ================ Software Version ================ Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production PL/SQL Release 11.2.0.2.0 - Production CORE 11.2.0.2.0 Production TNS for Solaris: Version 11.2.0.2.0 - Production NLSRTL Version 11.2.0.2.0 - Production ============= Compatibility ============= Compatibility is set as 11.2.0 ================ Archive Log Mode ================ Database log mode No Archive Mode Automatic archival Disabled Archive destination /uv1172/apps/oracle/product/11.2.0.2.2013Q4/dbs/arch Oldest online log sequence 15143 Current log sequence 15145 ================ Auditing Check ================ NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /uv1172/ofaroot/PROD/adump audit_sys_operations boolean TRUE audit_syslog_level string audit_trail string NONE ================ Cluster Check ================ NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ cluster_database boolean FALSE cluster_database_instances integer 1 DOC>################################################################ DOC> DOC> If CLUSTER_DATABASE is set to TRUE, change it to FALSE before DOC> upgrading the database DOC> DOC>################################################################ DOC># =========================================== Tablespace and the owner of the aud$ table =========================================== OWNER TABLESPACE_NAME ------------ ------------------------------ SYS SYSTEM ============================================================================ count of records in the sys.aud$ table where dbid is null- Standard Auditing ============================================================================ 0 ============================================================================================ count of records in the system.aud$ when dbid is null, Std Auditing with OLS or DV installed ============================================================================================ select count(*) from system.aud$ where dbid is null * ERROR at line 1: ORA-00942: table or view does not exist ====================================================== List of Invalid Database Objects Owned by SYS / SYSTEM ====================================================== Number of Invalid Objects ------------------------------------------------------------------ There are 2 Invalid objects ================ Component Status ================ Comp ID Component Status Version Org_Version Prv_Version ------- ---------------------------------- --------- -------------- -------------- -------------- CATALOG Oracle Database Catalog Views VALID 11.2.0.2.0 10.2.0.1.0 10.2.0.1.0 CATALOG Oracle Database Catalog Views VALID 11.2.0.2.0 10.2.0.1.0 10.2.0.1.0 Oracle Multimedia/interMedia is installed and listed with the following version: 11.2.0.2.0 and status: VALID . Checking for installed Database Schemas... ORDSYS user exists. ORDPLUGINS user exists. MDSYS user exists. SI_INFORMTN_SCHEMA user exists. ORDDATA user exists. . Checking for Prerequisite Components... JAVAVM installed and listed as valid XDK installed and listed as valid XDB installed and listed as valid Validating Oracle Multimedia/interMedia...(no output if component status is valid) ORDIM INVALID OBJECTS: CARTRIDGE - 5 - 11 ORDIM DICOM repository has 0 documents. The following default DICOM repository documents are not installed: ordcman.xml ordcmcmc.xml ordcmcmd.xml ordcmct.xml ordcmmp.xml ordcmpf.xml ordcmpv.xml ordcmsd.xml ordcmui.xml PL/SQL procedure successfully completed. *** End of LogFile ***
Check the output log for invalid objects and make a note of them.
4. Run utlrp.sql to validate invalid objects
SQL>@/apps/oracle/product/11.2.0.3/utlrp.sql
5. 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 /apps/oracle/product/12.1.0.2/rdbms/admin to /apps/oracle/product/11.2.0.3/rdbms/admin
SQL> @$ORACLE_HOME/rdbms/admin/preupgrd.sql
----It will generate below files $ cd /apps/oracle/cfgtoollogs/PROD/preupgrade/ $ ls -ltr total 28 -rw-r--r-- 1 oracle dba 7068 Apr 21 03:56 preupgrade.log -rw-r--r-- 1 oracle dba 3568 Apr 21 03:56 preupgrade_fixups.sql -rw-r--r-- 1 oracle dba 2637 Apr 21 03:56 postupgrade_fixups.sql
Execute the preupgrade_fixup.sql and check whether changes are reflecting or not.
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
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'); 2 3 4
no rows selected
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:
SQL> SELECT version FROM v$timezone_file; VERSION ---------- 14
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; 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. Resolve 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. Synchronize the Standby Database with the Primary Database When Upgrading
SQL> SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1) FROM v$parameter WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%'; 2 3 no rows selected
15. Disable cronjob,
Take backup of crontab and comment the same.
16. 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);
17. 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
18. Check whether database has any externally authenticated SSL users
SQL> SELECT name FROM sys.user$ WHERE ext_username IS NOT NULL AND password = 'GLOBAL'; 2 3 no rows selected
19. Remove EM repository
----Enterprise Manager Database Control is superseded in 12c by Oracle Enterprise Manager Express . Therefore no repository is needed anymore . ---Run the below script $emctl stop dbcontrol SQL> @ ?/rdbms/admin/emremove.sql
20. 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
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
SQL> alter system set db_recovery_file_dest_size=10G scope=both; System altered. SQL> alter system set db_recovery_file_dest='/dumparea/FRA/B2BRBMT3' scope=both; System altered. SQL> alter database flashback on; Database altered. SQL> startup force
Now create restore point:
CREATE RESTORE POINT BEF_UPGRADE GUARANTEE FLASHBACK DATABASE; Restore point created.
UPGRADE:
21. stop the listener and shutdown the database.
lsnrctl stop LISTENER_PROD SQL>shutdown immediate
22.Update the ORACLE_HOME,PATH pointing to 12C Home.
export ORACLE_HOME= /apps/oracle/product/12.1.0.2 export PATH=$ORACLE_HOME/bin:$PATH export ORACLE_BASE= /apps/oracle
23. Move the spfile and password file from 11g oracle_home/dbs location to 12cHome/dbs location.
24. start the database in upgrade mode.
$ cd $ORACLE_HOME/rdbms/admin $ pwd /apps/oracle/product/12.1.0.2.2015PSUQ2/rdbms/admin $ sqlplus "/ as sysdba" SQL> startup UPGRADE SQL> exit
25.Run catupgrade script from os level with paralle=6 as below.
cd $ORACLE_HOME/rdbms/admin $ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l $ORACLE_HOME/diagnostics catupgrd.sql
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
---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
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.
SQL>@$ORACLE_HOME/rdbms/admin/catuppst.sql
Now upgrade is completed. Now Proceed with POST CHECK.
27. 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*Plus: Release 12.1.0.2.0 Production on Thu Apr 23 09:13:24 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> spool countstatsTSTZ.log SQL> @countstatsTSTZ.sql . Amount of TSTZ data using num_rows stats info in DBA_TABLES. . For SYS tables first... Note: empty tables are not listed. Stat date - Owner.Tablename.Columnname - num_rows 09/11/2012 - SYS.AQ$_ALERT_QT_S.CREATION_TIME - 4 09/11/2012 - SYS.AQ$_ALERT_QT_S.DELETION_TIME - 4 09/11/2012 - SYS.AQ$_ALERT_QT_S.MODIFICATION_TIME - 4 09/11/2012 - SYS.AQ$_AQ$_MEM_MC_S.CREATION_TIME - 3 09/11/2012 - SYS.AQ$_AQ$_MEM_MC_S.DELETION_TIME - 3 09/11/2012 - SYS.AQ$_AQ$_MEM_MC_S.MODIFICATION_TIME - 3 09/11/2012 - SYS.AQ$_AQ_PROP_TABLE_S.CREATION_TIME - 1 09/11/2012 - SYS.AQ$_AQ_PROP_TABLE_S.DELETION_TIME - 1 09/11/2012 - SYS.AQ$_AQ_PROP_TABLE_S.MODIFICATION_TIME - 1 09/11/2012 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.CREATION_TIME - 1 09/11/2012 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.DELETION_TIME - 1 09/11/2012 - SYS.AQ$_SCHEDULER$_EVENT_QTAB_S.MODIFICATION_TIME - 1 09/11/2012 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.CREATION_TIME - 1 09/11/2012 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.DELETION_TIME - 1 09/11/2012 - SYS.AQ$_SCHEDULER$_REMDB_JOBQTAB_S.MODIFICATION_TIME - 1 09/11/2012 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.CREATION_TIME - 1 09/11/2012 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.DELETION_TIME - 1 09/11/2012 - SYS.AQ$_SCHEDULER_FILEWATCHER_QT_S.MODIFICATION_TIME - 1 15/04/2015 - SYS.AQ$_SUBSCRIBER_TABLE.CREATION_TIME - 1 15/04/2015 - SYS.AQ$_SUBSCRIBER_TABLE.DELETION_TIME - 1 15/04/2015 - SYS.AQ$_SUBSCRIBER_TABLE.MODIFICATION_TIME - 1 23/04/2015 - SYS.KET$_AUTOTASK_STATUS.ABA_START_TIME - 1 23/04/2015 - SYS.KET$_AUTOTASK_STATUS.ABA_STATE_TIME - 1 23/04/2015 - SYS.KET$_AUTOTASK_STATUS.MW_RECORD_TIME - 1 23/04/2015 - SYS.KET$_AUTOTASK_STATUS.MW_START_TIME - 1 23/04/2015 - SYS.KET$_AUTOTASK_STATUS.RECONCILE_TIME - 1 09/11/2012 - SYS.KET$_CLIENT_CONFIG.FIELD_2 - 7 09/11/2012 - SYS.KET$_CLIENT_CONFIG.LAST_CHANGE - 7 23/04/2015 - SYS.KET$_CLIENT_TASKS.CURR_WIN_START - 3 23/04/2015 - SYS.KET$_CLIENT_TASKS.LG_DATE - 3 23/04/2015 - SYS.KET$_CLIENT_TASKS.LT_DATE - 3 22/04/2015 - SYS.OPTSTAT_HIST_CONTROL$.SPARE6 - 18 22/04/2015 - SYS.OPTSTAT_HIST_CONTROL$.SVAL2 - 18 25/03/2015 - SYS.SCHEDULER$_EVENT_LOG.LOG_DATE - 26865 22/04/2015 - SYS.SCHEDULER$_GLOBAL_ATTRIBUTE.ATTR_TSTAMP - 11 23/04/2015 - SYS.SCHEDULER$_JOB.END_DATE - 13 23/04/2015 - SYS.SCHEDULER$_JOB.LAST_ENABLED_TIME - 13 23/04/2015 - SYS.SCHEDULER$_JOB.LAST_END_DATE - 13 23/04/2015 - SYS.SCHEDULER$_JOB.LAST_START_DATE - 13 23/04/2015 - SYS.SCHEDULER$_JOB.NEXT_RUN_DATE - 13 23/04/2015 - SYS.SCHEDULER$_JOB.START_DATE - 13 09/04/2015 - SYS.SCHEDULER$_JOB_RUN_DETAILS.LOG_DATE - 4869 09/04/2015 - SYS.SCHEDULER$_JOB_RUN_DETAILS.REQ_START_DATE - 4869 09/04/2015 - SYS.SCHEDULER$_JOB_RUN_DETAILS.START_DATE - 4869 09/11/2012 - SYS.SCHEDULER$_SCHEDULE.END_DATE - 3 09/11/2012 - SYS.SCHEDULER$_SCHEDULE.REFERENCE_DATE - 3 23/04/2015 - SYS.SCHEDULER$_WINDOW.ACTUAL_START_DATE - 9 23/04/2015 - SYS.SCHEDULER$_WINDOW.END_DATE - 9 23/04/2015 - SYS.SCHEDULER$_WINDOW.LAST_START_DATE - 9 23/04/2015 - SYS.SCHEDULER$_WINDOW.MANUAL_OPEN_TIME - 9 23/04/2015 - SYS.SCHEDULER$_WINDOW.NEXT_START_DATE - 9 23/04/2015 - SYS.SCHEDULER$_WINDOW.START_DATE - 9 22/04/2015 - SYS.SCHEDULER$_WINDOW_DETAILS.LOG_DATE - 30 22/04/2015 - SYS.SCHEDULER$_WINDOW_DETAILS.REQ_START_DATE - 30 22/04/2015 - SYS.SCHEDULER$_WINDOW_DETAILS.START_DATE - 30 23/04/2015 - SYS.STATS_TARGET$.END_TIME - 718 23/04/2015 - SYS.STATS_TARGET$.START_TIME - 718 23/04/2015 - SYS.WRI$_ALERT_HISTORY.CREATION_TIME - 6 23/04/2015 - SYS.WRI$_ALERT_HISTORY.TIME_SUGGESTED - 6 23/04/2015 - SYS.WRI$_ALERT_OUTSTANDING.CREATION_TIME - 4 23/04/2015 - SYS.WRI$_ALERT_OUTSTANDING.TIME_SUGGESTED - 4 22/04/2015 - SYS.WRI$_OPTSTAT_IND_HISTORY.SAVTIME - 6147 22/04/2015 - SYS.WRI$_OPTSTAT_IND_HISTORY.SPARE6 - 6147 23/04/2015 - SYS.WRI$_OPTSTAT_OPR.END_TIME - 681 23/04/2015 - SYS.WRI$_OPTSTAT_OPR.SPARE6 - 681 23/04/2015 - SYS.WRI$_OPTSTAT_OPR.START_TIME - 681 22/04/2015 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SAVTIME - 6534 22/04/2015 - SYS.WRI$_OPTSTAT_TAB_HISTORY.SPARE6 - 6534 Total numrow of SYS TSTZ columns is : 70672 There are in total 145 non-SYS TSTZ columns. . For non-SYS tables ... Note: empty tables are not listed. Stat date - Owner.Tablename.Columnname - num_rows Total numrow of non-SYS TSTZ columns is : 0 There are in total 5 non-SYS TSTZ columns. Total Minutes elapsed : 0 SQL> spool off
Purge the scheduler jobs
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 2147483648 bytes Fixed Size 2916104 bytes Variable Size 1677721848 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>@/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
SQL> SELECT version FROM v$timezone_file; VERSION ---------- 18
SQL> drop restore point BEF_UPGRADE;
7. Update the compatible parameter and restart database.
NOTE- Database cannot be downgraded once 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.
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;
credit: https://dbaclass.com/article/upgrade-database-from-11g-to-12c-manually/