📋Steps in this guide1/3

Upgrade database from 11g to 12c manually - DBACLASS DBACLASS

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 […]

oracle clusteringintermediate

by OracleDba

13 views

PRECHECKS:

1.CHECK THE INVALID OBJECTS( ALL SHOULD BE VALID) 2. Check duplicate objects owned by system and sys 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 Check the output log for invalid objects and make a note of them. 4. Run utlrp.sql to validate invalid objects 5. Run preupgrade tool Copy the Pre-Upgrade Information Tool script and from the Oracle Database 12c Release 1 (12.1) i.e to Execute the 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 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 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: 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 9. Verify That Materialized View Refreshes Have Completed Before Upgrading 10. Ensure That No Files Need Media Recovery Before Upgrading 11. Ensure That No Files Are in Backup Mode Before Upgrading 12. Resolve Outstanding Distributed Transactions Before Upgrading 13. Purge the Database Recycle Bin Before Upgrading : 14. Synchronize the Standby Database with the Primary Database When Upgrading 15. Disable cronjob, Take backup of crontab and comment the same. 16. Disable dbms_schduler jobs: 17. Verify system and sys default tablespace.(Both should be system tablespace) 18. Check whether database has any externally authenticated SSL users 19. Remove EM repository 20. Review and Remove any unnecessary hidden/underscore parameters 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:

Code/Command (click line numbers to comment):

SQL>  select comp_id,status from dba_registry;

COMP_ID                        STATUS
------------------------------ -----------
CATALOG                        VALID
CATPROC                        VALID

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

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 ***

SQL>@/apps/oracle/product/11.2.0.3/utlrp.sql

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

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

SQL> SELECT version FROM v$timezone_file;

VERSION
----------
14

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

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

SQL> SELECT * FROM v$recover_file;

no rows selected

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

no rows selected

SQL> SELECT * FROM dba_2pc_pending;

no rows selected

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

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

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

SQL> SELECT username, default_tablespace
FROM dba_users
WHERE username in ('SYS','SYSTEM'); 2 3

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYSTEM SYSTEM
SYS SYSTEM

SQL> SELECT name FROM sys.user$
WHERE ext_username IS NOT NULL
AND password = 'GLOBAL';
2 3
no rows selected

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

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

no rows selected

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

CREATE RESTORE POINT BEF_UPGRADE GUARANTEE FLASHBACK DATABASE;

Restore point created.

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: Download the dst upgrade script from oracle Purge the scheduler jobs Run upg_tzv_check.sql ( it will detect the highest installed DST patch automatically) 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 file. 3. Uncomment the 4.Enable the jobs in which we disabled before the upgrade. 5. Run 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> drop restore point BEF_UPGRADE; 7. Update the compatible parameter and restart database. > NOTE- Database cannot be downgraded once compatible parameter is updated. NOTE- Database cannot be downgraded once compatible parameter is updated. > NOTE – Once compatible is set to higher version, downgrade is not possible. NOTE – Once compatible is set to higher version, downgrade is not possible. With this our upgrade activity completes.

Code/Command (click line numbers to comment):

lsnrctl stop LISTENER_PROD

SQL>shutdown immediate

export ORACLE_HOME= /apps/oracle/product/12.1.0.2
export PATH=$ORACLE_HOME/bin:$PATH
export ORACLE_BASE= /apps/oracle

$ cd $ORACLE_HOME/rdbms/admin
$ pwd
/apps/oracle/product/12.1.0.2.2015PSUQ2/rdbms/admin

$ sqlplus "/ as sysdba"
SQL> startup UPGRADE
SQL> exit

cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl -n  6 -l $ORACLE_HOME/diagnostics catupgrd.sql

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

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

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

SQL> exec dbms_scheduler.purge_log;

PL/SQL procedure successfully completed.

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

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.

SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        18

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

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;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!