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.
Current ORACLE_HOME= /apps/oracle/product/11.2.0.3
Target ORACLE_HOME=/apps/oracle/product/12.1.0.2
PRECHEKS:
1.CHECK THE INVALID OBJECTS( ALL SHOULD BE VALID)
SQL> select comp_id,status from dba_registry;
COMP_ID STATUS
------------------------------ -----------
CATALOG VALID
CATPROC VALID2. 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 PACKAGEIf 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.sql5. 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.sqlExecute 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 selected6. 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
----------
14For 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 selected10. Ensure That No Files Need Media Recovery Before Upgrading
SQL> SELECT * FROM v$recover_file;
no rows selected11. Ensure That No Files Are in Backup Mode Before Upgrading
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
no rows selected12. Resolve Outstanding Distributed Transactions Before Upgrading
SQL> SELECT * FROM dba_2pc_pending;
no rows selected13. 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 selected15. 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 SYSTEM18. 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 selected19. 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.sql20. 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 selectedWith 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 forceNow 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 immediate22. 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/oracle23. 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> exit25.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.sqlOpen 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.sqlNow 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 offPurge 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.logRun 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;
startupNOTE – 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;